How would I execute a stored procedure in SQL server through VB.NET?
for a normal SQL command I would normally use:
Command = New SQLCommand("Select * From Table Where Column ='Value' ",Connection)
Command.ExecuteNonQuery '( Or scalar depending ).
Is it the same but you select the stored procedure?
Dim Command As New SQLCommand("usp_MyStoredProc", connection)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()
At the top of your .vb file:
Imports System.data.sqlclient
Within your code:
'Setup SQL Command'
Dim CMD as new sqlCommand("StoredProcedureName")
CMD.parameters("@Parameter1", sqlDBType.Int).value = Param_1_value
Dim connection As New SqlConnection(connectionString)
CMD.Connection = connection
CMD.CommandType = CommandType.StoredProcedure
Dim adapter As New SqlDataAdapter(CMD)
adapter.SelectCommand.CommandTimeout = 300
'Fill the dataset'
Dim DS as DataSet
adapter.Fill(ds)
connection.Close()
'Now, read through your data:'
For Each DR as DataRow in DS.Tables(0).rows
Msgbox("The value in Column ""ColumnName1"": " & cstr(DR("ColumnName1")))
next
Now that the basics are out of the way,
I highly recommend abstracting the actual SqlCommand Execution out into a function.
Here is a generic function that I use, in some form, on various projects:
''' <summary>Executes a SqlCommand on the Main DB Connection. Usage: Dim ds As DataSet = ExecuteCMD(CMD)</summary>'''
''' <param name="CMD">The command type will be determined based upon whether or not the commandText has a space in it. If it has a space, it is a Text command ("select ... from .."),'''
''' otherwise if there is just one token, it's a stored procedure command</param>''''
Function ExecuteCMD(ByRef CMD As SqlCommand) As DataSet
Dim connectionString As String = ConfigurationManager.ConnectionStrings("main").ConnectionString
Dim ds As New DataSet()
Try
Dim connection As New SqlConnection(connectionString)
CMD.Connection = connection
'Assume that it's a stored procedure command type if there is no space in the command text. Example: "sp_Select_Customer" vs. "select * from Customers"
If CMD.CommandText.Contains(" ") Then
CMD.CommandType = CommandType.Text
Else
CMD.CommandType = CommandType.StoredProcedure
End If
Dim adapter As New SqlDataAdapter(CMD)
adapter.SelectCommand.CommandTimeout = 300
'fill the dataset'
adapter.Fill(ds)
connection.Close()
Catch ex As Exception
' The connection failed. Display an error message.'
Throw New Exception("Database Error: " & ex.Message)
End Try
Return ds
End Function
One you have that, your SQL Execution + reading code is very simple:
'----------------------------------------------------------------------'
Dim CMD As New SqlCommand("GetProductName")
CMD.Parameters.Add("@productID", SqlDbType.Int).Value = ProductID
Dim DR As DataRow = ExecuteCMD(CMD).Tables(0).Rows(0)
MsgBox("Product Name: " & cstr(DR(0)))
'----------------------------------------------------------------------'
