Passing only values without parameters for Stored Procedure

SqlCommandBuilder.DeriveParameters
----------------------------------

The Code that discovers stored procedure parameters in the background is the SqlCommandBuilder.DeriveParameters method. You call the method passing in an SqlCommand object as such:

SqlCommandBuilder.DeriveParameters(command);

and it will populate the SqlParametersCollection on the command for you. Only thing you need to do is populate the input parameters with their appropriate values and execute the query.

SqlCommandBuilder.DeriveParameters Example


SqlConnection DBConn = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
DBConn.Open()
'Create command
Dim DBCommand As SqlCommand = DBConn.CreateCommand()
DBCommand.CommandType = CommandType.StoredProcedure

' Stored Procedure Name
DBCommand.CommandText = "StoredProcedureName"
DBCommand.CommandType = CommandType.StoredProcedure

Dim parameterValues As Object() = {Field Values to be added}

' Populate command.Parameters Collection.
' Causes Rountrip to Database.
SqlCommandBuilder.DeriveParameters(DBCommand)
' Initialize Index of parameterValues Array
Dim index As Integer = 0
' Populate the Input Parameters With Values Provided
For Each parameter As SqlParameter In DBCommand.Parameters
If parameter.Direction = ParameterDirection.Input OrElse parameter.Direction = ParameterDirection.InputOutput Then
parameter.Value = parameterValues(index)
index += 1
End If
Next
Dim DBAdapter As New SqlDataAdapter()
Dim DSValue As New DataSet()
DBAdapter.SelectCommand = DBCommand
DBAdapter.Fill(DSValue)
GridVew1.DataSource = DSValue
GridVew1.DataBind()


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: