create and Execute On-the-Fly Batch Updates by Using ADO.NET

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:3

Thêm vào BST

Báo xấu

87
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

4,4 Tạo và Execute On-the-Fly cập nhật hàng loạt khi sử dụng ADO.NET Đôi khi trong các ứng dụng cơ sở dữ liệu, bạn muốn tạo ra và thực hiện các thủ tục được lưu trữ mà hiện tại không tồn tại.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: create and Execute On-the-Fly Batch Updates by Using ADO.NET

4.4 Create and Execute On-the-Fly Batch Updates by Using ADO.NET Sometimes in database applications, you want to create and execute stored procedures that don't currently exist. When you have a situation in which you need to use highly dynamic stored procedures that might use criteria that is entirely created at runtime, you might need to create those stored procedures on-the-fly. This How-To shows you how to create and execute these stored procedures. It's great that you can execute stored procedures that are already created, but what if you need to generate one at runtime? How do you do this? Technique To perform this How-To, you will be utilizing the OleDBCommand object, and feeding in the CommandText property from a text box. The text box is set to "Update Employees Set City = 'Redmond' Where City = 'Seattle'" to give you something to start with. Steps Open and run the VB.NET-Chapter 4 solution. From the main form, click on the command button with the caption How-To 4.4. When the form loads, you will see an example update statement in a text box. Click on the Execute button to execute the update statement. A TextBox control is then displayed on the bottom of the form showing the number of records that are affected. You can the form in Figure 4.3. Figure 4.3. This form uses the Command object with a SQL statement passed to execute the specified action. Note
The number of records affected might be different on your system depending on what you have been doing with the Northwind data. 1. Create a new Windows Form. 2. Add the following controls, setting the properties as listed in Table 4.6. Table 4.6. Controls Property Settings Object Property Setting Label Name Label1 Caption Update Statement to Execute: Button Name btnExecute TextBox Name txtSQL Text Update Employees Set City = 'Redmond' Where City = 'Seattle' MultiLine True Label Name Label2 Caption Records Affected: TextBox Name txtRecsAffected 3. Enter the following code to the Click event btnExecute. When the command is instantiated in this case, the string in the txtSQL text box is passed as the CommandText. The CommandType is set as CommandType.Text. The connection is then open. Finally, the command is executed with the ExecuteNonQuery method, with the ToString passing back the number of records that were affected to the Text property of the txtRecsAffected text box. 4. Private Sub btnExecute_Click(ByVal sender As System.Object, _ 5. ByVal e As System.EventArgs) Handles btnExecute.Click 6. 7. Dim ocnn As New OleDb.OleDbConnection(BuildCnnStr("(local)", "Northwind"))
8. Dim ocmdPhoneUp As New OleDb.OleDbCommand(Me.txtSQL.Text, ocnn) 9. 10. Try 11. '-- Specify the name of the stored procedure 12. ocmdPhoneUp.CommandType = CommandType.Text 13. 14. '-- Open the connection object. 15. ocnn.Open() 16. 17. Me.txtRecsAffected.Text = ocmdPhoneUp.ExecuteNonQuery.ToString 18. 19. Catch excpData As Exception 20. MessageBox.Show("Error Occurred: " & excpData.Message) 21. 22. End Try 23. End Sub Tip Use a Try…Catch…End Try block to trap any exceptions that might occur when working with ADO.NET. In this case, the error is trapped and a message box displays the error. Remember that exceptions that are not trapped will cause the application to fail. How It Works When a valid SQL statement is entered into the text box with the label Update Statement to Execute: and the Execute button is clicked, the command entered is executed, and the number of records that were affected is returned. Comments The Command object is a real workhorse when it comes to performing bulk operations, whether working with store procedures already created or when using statements that have been created on-the-fly.