Updating a Database Using a DataSet

Cập nhật một cơ sở dữ liệu Sử dụng một DataSet Trong các bài tập cho đến nay trong chương này, bạn đã thấy làm thế nào để lấy dữ liệu từ cơ sở dữ liệu. Bây giờ là lúc để cho bạn thấy làm thế nào để cập nhật dữ liệu
Nội dung trích xuất từ tài liệu:
Updating a Database Using a DataSet Updating a Database Using a DataSetIn the exercises so far in this chapter, you have seen how to fetch data from a database.Now its time to show you how to update data. First, however, we need to consider somepotential problems and how using a DataSet can overcome them.Databases are intended to support multiple concurrent users, but resources such as thenumber of concurrent connections allowed might be limited. In an application thatfetches and displays data, you never know quite how long the user will be browsing thedata, and it is not a good practice to keep a database connection open for an extendedperiod of time. Instead, a better approach is to connect to the database, fetch the data intoa DataSet object, and then disconnect again. The user can browse the data in the DataSetand make any changes required. After the user finishes, the program can reconnect to thedatabase and submit any changes. Of course, there are complications that you need toconsider, such as what happens if two users have queried and updated the same data,changing it to different values. Which value should be stored in the database? We willcome back to this problem shortly.Managing ConnectionsIn earlier exercises, you have seen that when you define a DataSet you can specify aconnection to use for communicating with the database. This information is embeddedinto the TableAdapter used to retrieve the data and fill the DataSet. When you execute theFill or GetData methods, the code generated by Visual Studio 2005 examines the state ofthe connection first. If the connection to be used is already open, it is used to retrieve thedata, and is left open at the end of the operation. If the connection is closed, the Fill andGetData methods open it, fetch the data, and then close it again. The DataSet in this caseis referred to as a disconnected DataSet as it doesnt maintain an active connection to thedatabase. Disconnected DataSet objects act as a data cache in applications. You canmodify the data in the DataSet, and later reopen the connection and send the changesback to the database.You can manually open a connection to a database by creating a SqlConnection object,setting its ConnectionString property, and then calling its Open method as shown inChapter 23. You can associate an open connection with a TableAdapter by setting theConnection property. The following code shows how to connect to the database and fillthe Suppliers DataTable. In this case, the database connection will remain open after theFill method completes:SqlConnection dataConnection = new SqlConnection();dataConnection.ConnectionString = Integrated Security=true; + Initial Catalog=Northwind; + Data Source=YourServer\SQLExpress;dataConnection.Open();suppliersTableAdapter.Connection = dataConnection;suppliersTableAdapter.Fill(northwindDataSet.Suppliers);Unless you have a good reason to do so, you should avoid maintaining connectionslonger than needed; let the Fill and GetData methods open and close the connection foryou and create a disconnected DataSet.Handling Multi-User UpdatesEarlier in this chapter, we mentioned the problem that arises if two users try and updatethe same data at the same time. There are at least two possible approaches you can adoptto solve this problem. Each approach has its benefits and disadvantages.The first technique involves the Use optimistic concurrency option in the the AdvancedOptions dialog box in the TableAdapter Configuration Wizard.If you deselect this option, the rows retrieved into the DataSet will be locked in thedatabase to prevent other users from changing them. This is known as pessimisticconcurrency. It guarantees that any changes you make will not conflict with changesmade by any other users at the expense of blocking those other users. If you retrieve alarge number of rows and only update a small proportion of them, you have potentiallyprevented other users from modifying any of the rows that you have not changed. Thereis one other drawback—locking data requires that the connection used to retrieve the dataremains open, therefore if you use pessimistic concurrency you also run the risk ofconsuming a large number of connection resources. The principal advantage ofpessimistic concurrency, of course, is simplicity. You dont have to write code thatchecks for updates made by other users before modifying the database.If you select the “Use optimistic concurrency” option, data is not locked, and theconnection can be closed after the data has been fetched. The disadvantage is that youhave to write code that ascertains whether any updates made by the user conflict withthose made by other users, and this code can be quite difficult to write and debug.However, the TableAdapter object generated by the TableAdapter Configuration Wizardhides much of this c ...