Executing a SQL Server Stored Procedure By Using ActiveX Data Objects

Thực hiện một SQL Server lưu trữ Thủ tục Bởi Sử dụng ActiveX Data Objects Nếu bạn đang làm một phát triển ADO với máy chủ của khách hàng đối với hậu phương, sau đó bạn có thể gọi thủ tục lưu trữ.
Nội dung trích xuất từ tài liệu:
Executing a SQL Server Stored Procedure By Using ActiveX Data Objects Executing a SQL Server Stored Procedure By Using ActiveX Data ObjectsIf you are doing an ADO development with client server for backends, then you probablycall stored procedures. In doing so, you will use the ADO Command object, as well asthe Parameter object if you are passing parameters.You will create a Command object and supply the command text, which in this case willbe the name of the stored procedure, called CustOrdersHist. You can see the T-SQL forCustOrderHist in Listing A.7. This stored procedure returns product names and the totalquantity purchased of those products for a given customer.Listing A.7 Northwind SQL Server Database: T-SQL for the Stored ProcedureCalled CustOrdersHistALTER PROCEDURE CustOrderHist @CustomerID nchar(5)ASSELECT ProductName, Total=SUM(Quantity)FROM Products P, [Order Details] OD, Orders O, Customers CWHERE C.CustomerID = @CustomerIDAND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND _ OD.ProductID = P.ProductIDGROUP BY ProductNameYou will then specify the type of Command object you are creating-in this case by usingthe type of ADODB.CommandTypeEnum.adCmdStoredProc.The next step is to create a parameter that the Command object will use. This parameterwill match the one specified in CustOrdersHist, called CustomerID. You can see theactual code for this routine, called UseAStoredProcedureWithAParameter, in Listing A.8.Listing A.8 basCommandExamples.vb: Calling a Stored Procedure By UsingParametersSub UseAStoredProcedureWithAParameter(ByVal txtResults As TextBox) Dim cnn As New ADODB.Connection() Dim rstCurr As New ADODB.Recordset() Dim cmd As New ADODB.Command() Dim prm As ADODB.Parameter Try cmd.CommandText = CustOrderHist cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc prm = cmd.CreateParameter(CustomerID, ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 5) cmd.Parameters.Append(prm) prm.Value = CHOPS OpenNorthwindADOConnection(cnn) cmd.ActiveConnection = cnn rstCurr.Open(cmd) txtResults.Text = rstCurr.GetString Catch excp As Exception MessageBox.Show(excp.Message) End Try End SubThe last thing that this routine does is open a recordset based on the Command object.This is to the use just those records that are needed. In this case, the GetString method isused to assign it to the results text box. If you are using a bulk query, shown in the nextsection, you would use the Execute method. To see the routine in A.8 executed, click onthe button with the caption Stored Procedure with Parameter, located on the frmMainform for this Appendix project.