Binding Controls to Databases Using ADO.NET

I l @ ve RuBoard

ADO.NET employs a disconnected database model that uses data tables on the client machine to cache information while it is viewed or manipulated. Even when the database is stored on the same machine as the editing program, ADO.NET will copy a snapshot of data into a DataSet and pass changes back to the database later.

This model is used to allow better scaling of the database application over a network because it obviates the need for many, simultaneous, open connections to the database.

Data tables are very structured and hierarchical. This makes XML an ideal medium for transmitting and storing data. Indeed, when ADO makes a connection to a database, the information will be transported as XML. This means that your software can take advantage of intranet and Internet data stores just as easily as it can local databases.

It is important to note that transporting large amounts of data to and from databases becomes more demanding as the database becomes more disconnected from the client. Modern n- tier systems often employ many databases in different geographical locations and Web services to provide data on demand to many clients . This means that your client application should try to use small chunks of data that can be refreshed quickly and with as little strain as possible on network bandwidth. For this reason, many modern data systems employ a messaging architecture that deals with data transactions at a very granular level. It often has been the responsibility of the application programmer to devise and manage schemes for such transactions, but the high level abilities of ADO.NET hide much of that pain from you and allow you to be more productive with data.

Visual StudioVisual StudioData is copied to or from DataSet by a DataAdapter . ADO.NET provides two main DataAdapter types, the OleDBDataAdapter that connects DataSets to OLE DB data providers and the SqlDataAdapter , targeting SQL server connections. Both of these data adapter classes use a set of Command objects to perform selects, updates, inserts , and deletes in the databases to which they connect. The Select command gets data from the data source. The Update , Delete , and Insert commands all change the data source based on changes made to the DataSet.

Filling a DataSet with information from a database is fairly simple. The steps to perform are shown in the code snippet that follows :

 1: System.Data.OleDb.OleDbConnection connection =  2:   new System.Data.OleDb.OleDbConnection();  3: connection.ConnectionString=  4: @"Provider=Microsoft.Jet.OLEDB.4.0;"+  5: @"Data Source=northwind.mdb; ";  6: System.Data.OleDb.OleDbCommand SelectCommand=  7:   new System.Data.OleDb.OleDbCommand("SELECT * FROM Customers");  8: SelectCommand.Connection=connection;  9: System.Data.DataSet ds=new System.Data.DataSet("Customer Table"); 10: ds.Tables.Add("Customers"); 11: System.Data.OleDb.OleDbDataAdapter adapter = 12:    new System.Data.OleDb.OleDbDataAdapter(SelectCommand); 13: adapter.Fill(ds); 14: Console.WriteLine(ds.GetXml()); 

This snippet assumes that the NortWind database northwind.mdb is available in the local directory.

Lines 1 “5 create an OleDbConnection and set the connection string.

Lines 6 and 7 create the SelectCommand and populate the select string with a valid query.

Line 8 associates the database connection with the select command, while lines 9 and 10 create a DataSet object and add one table to it.

Lines 11 and 12 create a data adapter and add the select command. Line 13 fills the data set from the adapter and finally, to prove it actually did something, the last line dumps the whole northwing customer table from the DataSet as XML.

From the point of view of your own productivity, you will most likely perform such an operation using the Visual Studio.NET IDE to create the application. Databinding using Visual Studio generates some enormous blocks of code and does a very comprehensive job of connecting up your database. We have generally avoided using Visual Studio until now because the layout of applications created in this manner is not easily read and there are sometimes dire consequences when you edit the code generated by the studio. In this case, however, a good exercise will be to do a complete walk through of a database viewer using the IDE and then examine the resulting application to see what it does.

I l @ ve RuBoard