Executing SQL Stored Procedure and Processing Results

Question

In VB.NET, how can I do the following?

• Running stored procedure
• Reading the returned DataTable

Answer 1

At the top of your .vb file:

Imports System.data.sqlclient 

In 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 execution of SqlCommand Execution in a function.

Here is a common function that I use in one form or another in different 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 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 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 

---

After that, your SQL Execution + execution 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))) '----------------------------------------------------------------------' 

Answer 2

From MSDN

Executing a stored procedure that returns strings programmatically using a command object

 Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "StoredProcedureName" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. ' Use Read method (true/false) to see if reader has records and advance to next record ' You can use a While loop for multiple records (While reader.Read() ... End While) If reader.Read() Then someVar = reader(0) someVar2 = reader(1) someVar3 = reader("NamedField") End If sqlConnection1.Close() 

Answer 3

The easiest way? It is working.:)

  Dim queryString As String = "Stor_Proc_Name " & data1 & "," & data2 Try Using connection As New SqlConnection(ConnStrg) connection.Open() Dim command As New SqlCommand(queryString, connection) Dim reader As SqlDataReader = command.ExecuteReader() Dim DTResults As New DataTable DTResults.Load(reader) MsgBox(DTResults.Rows(0)(0).ToString) End Using Catch ex As Exception MessageBox.Show("Error while executing .. " & ex.Message, "") Finally End Try 

Answer 4

My stored procedure requires 2 parameters, and I need my function to return the data given here, this is 100% working code

Make sure your procedure returns multiple rows

 Public Shared Function Get_BillDetails(AccountNumber As String) As DataTable Try Connection.Connect() debug.print("Look up account number " & AccountNumber) Dim DP As New SqlDataAdapter("EXEC SP_GET_ACCOUNT_PAYABLES_GROUP '" & AccountNumber & "' , '" & 08/28/2013 &"'", connection.Con) Dim DST As New DataSet DP.Fill(DST) Return DST.Tables(0) Catch ex As Exception Return Nothing End Try End Function 

Answer 5

 Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand cmd.CommandText = "StoredProcedureName" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open() Dim adapter As System.Data.SqlClient.SqlDataAdapter Dim dsdetailwk As New DataSet Try adapter = New System.Data.SqlClient.SqlDataAdapter adapter.SelectCommand = cmd adapter.Fill(dsdetailwk, "delivery") Catch Err As System.Exception End Try sqlConnection1.Close() datagridview1.DataSource = dsdetailwk.Tables(0)