执行SQL存储过程并处理结果 [英] Execute a SQL Stored Procedure and process the results

问题描述

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

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

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

推荐答案

在 .vb 文件的顶部:

At the top of your .vb file:

Imports System.data.sqlclient

在您的代码中:

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

我强烈建议将实际的 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

<小时>

一旦你有了它，你的 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)))
'----------------------------------------------------------------------'

这篇关于执行SQL存储过程并处理结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！