Executing Batch Updates with ADO and SQL Server

Thực hiện cập nhật hàng loạt với ADO và SQL Server Sự khác biệt lớn nhất giữa phần này và một trong những trang trước là một thực tế là trong phần cuối, một recordset đang được dân cư sử dụng một đối tượng Command.
Nội dung trích xuất từ tài liệu:
Executing Batch Updates with ADO and SQL Server Executing Batch Updates with ADO and SQL ServerThe biggest difference between this section and the previous one is the fact that in the lastsection, a recordset being populated using a Command object. With the Command object,no data was affected. That is one type of use for a Command object. What if you want toperform bulk tasks against a recordset? This is the major use for using the Commandobject because you dont necessarily need it for populating a recordset. The Parameterobject is also not used. It was not necessary for the example, although you would want touse it if criteria were being implemented.To learn how to perform a bulk operation using ADO in Visual Basic .NET, see thissimple Update SQL statement, shown here:Update Orders Set ShippedDate = DeliveryDate+1This statement adds a day to the date in the ShippedDate column for all the records in theOrders table.This statement is being assigned to the CommandText property of the Command objectinstead of to the name of a stored procedure. Another important task is setting theCommandType property to be ADODB.CommandTypeEnum.adCmdText. This tellsADO that you are performing a bulk operation. Last, the Execute method is called fromthe Command object. This routine, called ExecuteABatchCommand, can be seen inListing A.9.Listing A.9 basCommandExamples.vb: Creating and Executing a Bulk QuerySub ExecuteABatchCommand(ByVal txtResults As TextBox) Dim cnn As New ADODB.Connection() Dim cmd As New ADODB.Command() Dim prm As ADODB.Parameter Dim rstOld As New ADODB.Recordset() Dim rstNew As New ADODB.Recordset() -- In .NET, we can assign values as we declare variables Dim strSQL As String = Update Orders Set ShippedDate = ShippedDate+1 Dim strDispSQL As String = _ Select OrderID, ShippedDate From Orders Where OrderID < 10251 -- Open the connection OpenNorthwindADOConnection(cnn) -- Open the Orders table and display the ShippedDate as they were. rstOld.Open(strDispSQL, cnn) txtResults.Text = Old Values: & vbCrLf & vbCrLf & rstOld.GetString -- Set up the Command object to use the SQL string. cmd.ActiveConnection = cnn cmd.CommandText = strSQL cmd.CommandType = ADODB.CommandTypeEnum.adCmdText -- Execute the command cmd.Execute() -- Reopen the Orders table and redisplay the ShippedDate Field rstNew.Open(strDispSQL, cnn) txtResults.Text = txtResults.Text & vbCrLf & vbCrLf & New Values: & vbCrLf & vbCrLf & rstNew.GetStringEnd SubFor this example recordset, objects were used merely to display the before and after data,as seen in Figure A.6. Figure A.6. Although theyre not pretty, you can see the values of the OrderID and DeliveryDate before and after the routine has been executed.You can also use the Insert and Delete statements to perform other bulk operations withthe Command object. One of the last tasks that is useful to perform using ADO with SQLServer is to create objects such as tables on-the-fly.