vb.net 执行 SQL 存储过程并处理结果

Execute a SQL Stored Procedure and process the results

提问by Jan

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

在 VB.NET 中，如何执行以下操作？

1. Execute a Stored Procedure
2. Read through the DataTable returned

1. 执行存储过程
2. 通读返回的数据表

回答by Brian Webster

At the top of your .vb file:

在 .vb 文件的顶部：

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.

我强烈建议将实际的 SqlCommand 执行抽象为一个函数。

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

---

---

Once you have that, your SQL Execution + reading code is very simple:

一旦你有了它，你的 SQL 执行 + 阅读代码就非常简单了：

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

回答by Wayne In Yak

From MSDN

来自MSDN

To execute a stored procedure returning rows 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()

回答by Valtteri

Simplest way? It works. :)

最简单的方法？有用。:)

    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

回答by ebeker

My Stored Procedure Requires 2 Parameters and I needed my function to return a datatable here is 100% working code

我的存储过程需要 2 个参数，我需要我的函数来返回一个数据表，这里是 100% 工作代码

Please make sure that your procedure return some 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

回答by ray

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)