VB.NET、EXCEL调用SQL Server带参数的存储过程并返回数据集

VB.NET版本：

    Private Function GetOrdersByShip( _
        ByVal Param1 As String, _
        ByVal Param2 As String) As DataSet

 

        Dim connString As String
        connString = "Data Source=192.168.0.1;Initial Catalog=abk;User ID=abk;Password=abk"

        Dim conn As New SqlClient.SqlConnection(connString)

        Dim myCmd As New SqlClient.SqlCommand

        myCmd.CommandType = CommandType.StoredProcedure
        myCmd.CommandText = "_sp_GetOrders"
        myCmd.Connection = conn

 

        Dim myDap As New SqlClient.SqlDataAdapter(myCmd)

        myDap.SelectCommand.Parameters.Add(("@FromShip"), SqlDbType.VarChar, 7).Value = Param1
        myDap.SelectCommand.Parameters.Add(("@ToShip"), SqlDbType.VarChar, 7).Value = Param2

 

        Dim myDs As New DataSet

        Try
            myDap.Fill(myDs)
        Catch ex As Exception

        End Try

 

        Return myDs
    End Function

 

 

   '调用时需要提供两个参数

   Me.DataGridView1.DataSource = GetOrdersByShip("2010111", "2010112").Tables(0)

 

 

EXCEL版本：

Public Sub CallStoredProcedureWithExcel(Param1 As String, Param2 As String)

    Dim mStr As String
    mStr = "Provider=SQLOLEDB.1;"
    mStr = mStr & "Password=abk;"
    mStr = mStr & "Persist Security Info=True;"
    mStr = mStr & "User ID=abk;"
    mStr = mStr & "Data Source=192.168.0.1;"
    mStr = mStr & "Use Procedure for Prepare=1;"
    mStr = mStr & "Auto Translate=True;"
    mStr = mStr & "Packet Size=4096;"
    mStr = mStr & "Use Encryption for Data=False;"
    mStr = mStr & "Tag with column collation when possible=False;"
    mStr = mStr & "Initial Catalog=abk"

    Dim Command As Command
    Set Command = New Command
   
    Command.ActiveConnection = mStr
    Command.CommandText = "[_SP_GetOrders]"
    Command.CommandType = CommandTypeEnum.adCmdStoredProc
   
    Dim FromShip As ADODB.Parameter
    Dim ToShip As ADODB.Parameter
   
    Set FromShip = Command.CreateParameter("@FromShip", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 7, Param1)
    Set ToShip = Command.CreateParameter("@ToShip", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 7, Param2)
   
    Call Command.Parameters.Append(FromShip)
    Call Command.Parameters.Append(ToShip)
   
    Dim Recordset As ADODB.Recordset
    Set Recordset = Command.Execute
    Call Sheet1.Range("A1").CopyFromRecordset(Recordset)
 
End Sub

 

   '调用时需要提供两个参数

   Call CallStoredProcedureWithExcel("2010111", "2010111")