Esegui una stored procedure SQL ed elabora i risultati

In VB.NET, come faccio a seguire?

  1. Esegui una stored procedure
  2. Leggere il DataTable restituito

Nella parte superiore del tuo file .vb:

Imports System.data.sqlclient 

All’interno del tuo codice:

 '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 

Ora che le basi sono fuori strada,

Consiglio vivamente di astrarre l’effettiva esecuzione di SqlCommand in una funzione.

Ecco una funzione generica che uso, in qualche forma, su vari progetti:

 ''' Executes a SqlCommand on the Main DB Connection. Usage: Dim ds As DataSet = ExecuteCMD(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'''' 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 

Una volta ottenuto ciò, il codice di lettura di SQL Execution + è molto semplice:

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

Da MSDN

Per eseguire una stored procedure restituendo le righe a livello di codice utilizzando un object comando

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

Modo più semplice? Funziona. 🙂

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

La mia stored procedure richiede 2 parametri e ho avuto bisogno della mia funzione per restituire un datatable qui è il 100% del codice di lavoro

Assicurati che la tua procedura restituisca alcune righe

 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