Executing a Stored Procedure from SQL Server in VB.NET?

Question

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?

possible duplicate of [Execute a SQL Stored Procedure and process the results](http://stackoverflow.com/questions/5991877/execute-a-sql-stored-procedure-and-process-the-results) — Hoh, Apr 14 '14 at 07:54

score 4 · Accepted Answer · edited Dec 15 '16 at 15:43

4

Dim Command As New SQLCommand("usp_MyStoredProc", connection)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()

edited Dec 15 '16 at 15:43

Martin Verjans

4,675
1
21
48

answered Apr 14 '14 at 07:55

Richard

106,783
21
203
265

score 1 · Answer 2 · edited May 23 '17 at 10:30

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)))
 '----------------------------------------------------------------------'

SOURCE

Executing a Stored Procedure from SQL Server in VB.NET?

2 Answers2