• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Retrieve SP Params through VB.NET

Hi,

I want the user to execute SP through VB.NET and see the return results in the grid.

For this I will display all SPs in the combo box. Once the user selects the SP, how do I find out how many parameter values to take input from user? Also after finding the number of params, I also need to validate the user input for each of the param based on its data type. So how do I find:
a. Number of params for that SP
b. Data type of each param.

Regards,
MI
0
vj_mi
Asked:
vj_mi
  • 4
2 Solutions
 
manchCommented:
Code To Fetch Data from SP and display on Grid :

        Dim pObjSQLConnection As SqlConnection      'Connection Object
        Dim psConStr As String                      'Connection String
        Dim pObjSQLCommand As SqlCommand            'Command Object
        Dim pObjSQLAdapter As SqlDataAdapter        'Adapter Object
        Dim pdsDataSet As DataSet                   'DataSet
        Dim pdtData As DataTable                    'Data Table
        Dim pObjSQLCommandBuilder As SqlCommandBuilder

        'Write Connection String Here
        psConStr = ""
        'Instantiate the Connectin Object
        pObjSQLConnection = New SqlConnection(psConStr)

        'Open the connection
        pObjSQLConnection.Open()

        'Command Object Instancing
        pObjSQLCommand = New SqlCommand("sp_test", pObjSQLConnection)
        pObjSQLCommand.CommandType = CommandType.StoredProcedure

        'Instancing the Adapter Object
        pObjSQLAdapter = New SqlDataAdapter(pObjSQLCommand)

        'Instantiate the Command Builder
        pObjSQLCommandBuilder = New SqlCommandBuilder(pObjSQLAdapter)

        'Instantiate the Dataset and Table
        pdsDataSet = New DataSet
        pdtData = New DataTable

        'Use the Adapter to fill the Data Set
        pObjSQLAdapter.Fill(pdsDataSet)

        'U Can Have the Table for ur furthur use
        pdtData = pdsDataSet.Tables(0)

        DataGrid1.DataSource = pdtData

       'Dispose all objects after use
0
 
pradeepsudharsanCommented:
Use the SqlCommandBuilder and its DeriveParameters method.
0
 
pradeepsudharsanCommented:
 SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "sp_testProc"
        SqlCommand2.Connection = SqlConnection1

        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        cboValues1.DataSource = SqlCommand2.Parameters
        cboValues1.DataBind()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Nandakumar SakthivelTechnology LeadCommented:
Hi,
   As per my understanding of the question, i think that you had a drop down box which shows the list of stored procedures on selecting a s tored procedure you need to dynamically bring the parameters and their corresponding data types in the screen
    There is a procedure named "sp_sproc_columns" which will return all the details about the stored procedure .Executing the stored procedure will return nearly 20 columns.Among them returned columns column number 3 will hold the name of the parameter and column number 6 will hold the data type of the parameter.
   You can use a datareader to loop thro' the values

            Dim sqlCmd As SqlCommand
            Dim SqlCon As SqlConnection
            Dim trans As SqlTransaction
            Dim dr As SqlDataReader
            SqlCon = New SqlConnection(<conn string>)
            SqlCon.Open()
            sqlCmd = New SqlCommand
            trans = SqlCon.BeginTransaction
            sqlCmd.CommandTimeout = 0
            sqlCmd.Connection() = SqlCon
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Transaction = trans
            sqlCmd.CommandText = "sp_sproc_columns"
            sqlCmd.Parameters.Add("@procedure_name", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@procedure_owner", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@procedure_qualifier", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@column_name ", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@ODBCVer ", SqlDbType.Int)
            dr = sqlCmd.ExecuteReader

            SqlCon.close()
 
You can test the procedure in the query analyser itself

        exec  sp_sproc_columns '<name of the procedure>','<owner>',Null,NUll,2

which will return you all the details abt the sp

Thanks,
Nanda

     
0
 
pradeepsudharsanCommented:
SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "sp_testProc"
        SqlCommand2.Connection = SqlConnection1
        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        'cboValues1.DataSource = SqlCommand2.Parameters
        'cboValues1.DataBind()
        Dim count1 As Integer
        Dim input As New Hashtable
        Dim output As New Hashtable
        Dim inputoutput As New Hashtable
        For count1 = 1 To SqlCommand2.Parameters.Count - 1
            If (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Input) Then
                input.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Output Or SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.ReturnValue) Then
                output.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.InputOutput) Then
                inputoutput.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            End If
        Next count1
        cboValues1.DataSource = input.Values
        cboValues1.DataBind()
        cboValues2.DataSource = output.Values
        cboValues2.DataBind()
        cboValues.DataSource = inputoutput.Values
        cboValues.DataBind()

0
 
pradeepsudharsanCommented:
'Consider this only
        'This will display the stored procedure parameters and their data types
        SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "spDOC_DocDiscussionReplyInsert"
        SqlCommand2.Connection = SqlConnection1
        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        Dim count1 As Integer
        Dim input As New Hashtable
        Dim output As New Hashtable
        Dim inputoutput As New Hashtable
        For count1 = 1 To SqlCommand2.Parameters.Count - 1
            If (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Input) Then
                input.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Output Or SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.ReturnValue) Then
                output.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.InputOutput) Then
                inputoutput.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            End If
        Next count1
        cboValues1.DataSource = input.Values
        cboValues1.DataBind()
        cboValues2.DataSource = output.Values
        cboValues2.DataBind()
        cboValues.DataSource = inputoutput.Values
        cboValues.DataBind()
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now