ADO.NET

• Download source - 280.67 KB

Introduction 

This paper is meant for the beginner to use as a reference in order to understand ADO.NET.

Getting to Know ADO.NET
Using Windows Forms to Understand DataSet and Table Relations

Sometimes the student of the .NET Framework will find it difficult to understand direct database access using requests to the appropriate SQL commands. Certain individuals might not have the experience in languages like T-SQL to query a database and therefore go on to learn about ADO.NET. ADO.NET is actually comprised of the classes contained in the System.Data namespace of the FCL. Based on the original ADO (ActiveX Data Objects), ADO.NET adds, amongst other things, the DataSet. The DataSet is a collection of all of the relevant database objects: tables, columns, relations, constraints, stored procedures, etc. These concepts will be explained shortly. This paper will focus on building a C# Windows Forms application that resides on top of the actual permanent database on the hard disk to avoid having the user access a database directly. The logic behind building an application that accesses a database is to simplify the database access process, avoid possible database access restrictions, and complex queries. This, of course, assumes that we are dealing with a monolithic application that resides on the same machine as the database access code. The utility will be a Windows Forms application that accesses a Microsoft Access database file through the System.Data.OleDb .NET data provider. In fact, there are four managed .NET data providers:

• System.Data.SqlClient
• System.Data.Oledb
• System.Data.Odbc
• System.Data.Oracle

These four .NET managed providers provide five main objects:

1. Connections: For connection to and managing transactions against a database.
2. Commands: For issuing SQL commands against a database. 
3. DataReaders: For reading a forward-only stream of data records from a SQL Server data source.
4. DataSets: For storing, remoting and programming against flat data, XML data and relational data.
5. DataAdapters: For pushing data into a DataSet, and reconciling data against a database.

Rather than access the database directly, the Windows Form will load and access the data. We begin by building a simple C# Windows Forms application, and calling it, say, "UserData".

Having built the form, we now want to choose the "Data" choice on the toolbar to click "Add New Data Source". This invokes the Data Source Configuration Wizard:

Then we click next to choose the data store (database), which is a Microsoft Access database file. Notice that the .NET Framework’s managed data provider is System.Data.OleDb. This is because we have not chosen to use the SQL Server nor any SQL database file. We are choosing an Access file to keep things simple:

Typical Microsoft Office files, such as Word, Excel, or Access are based on ActiveX technology, which in turn is based on the Component Object Model. COM is a set of programming standards based on around nine technologies that comprise OLE 2, or Object Embedding and Linking 2.0. The .NET Framework Data Provider is therefore for OLE DB. After we click OK, we see a dialog box that asks us to browse for that Access file. This file is included in the downloadable zip file and the contents should be extracted to the working directory where you keep project files. After we test the connection to the database file and succeed, we then save the connection string:

After we test the connection and it succeeds, we save the connection string:

At this point, we should stop and note the steps that we have taken. We first built a Windows Form. Anyone familiar with Windows Forms or even Web Forms knows that the toolbox has a rich set of controls that can be dragged and dropped onto the Forms' surface in order to set their properties. In this application, we are databinding data to user interface controls. We then choose to locate a new data source, a Microsoft Access file. Next we built a connection string in order to connect to the database. But our goal is to build an application that accesses the database. This means the UI must have controls that function as data access components, for we want to databind data to the UI controls. Databinding is a feature of the user interface controls that allow them to retrieve data from a data source, such as a database. This brings us to one of the most important features of AD0.NET: the data access components. Notice the dialog box shown after we click "Next":

Remember that our Windows Form application was meant to access the database and in order to accomplish this, our goal was to DataBind data onto user interface controls. That data binding means data referenced by your DataBindingSource and binding it to a control. In other words, the control will receive data from your data access components, and the data will be automatically displayed in the control for the user to see and manipulate.

Notice that we chose two tables, Departments and Instructors. These chosen database objects will constitute the DataSet. The difference between the DataSet and permanent database on hard disk in the DataSet is a cached or in-memory view of the database. When we have to make changes to the database, we do it to the in-memory copy of the database. That is, if we delete, modify, update, or add a record (row), these changes will copy onto the permanent database. But how can tabular data be temporarily stored in memory in the form of tables? Well, in addition to storing data in tables, the DataSet component is similar to a small engine that can store data in memory and be rich in metadata. This includes things like table and column names, data types, and the information needed to undo changes to data. All of the data is represented in memory in XML. A DataSet can be saved to an XML file and then loaded back into memory. It can be passed in XML format over networks including the Internet.

To reiterate, ADO.NET manages the connection to the database, in this case, a Microsoft Access file. Establishing a connection to the data source involves the process of location of that database, either by file system or network protocol. A DataSet is considered a disconnected recordset as it has no knowledge of the data store. Since we are building an application that has a user interface, we can keep the connected open to the DataSet while the application is running. That is, once the connection is established, the Windows Forms application can begin speaking with the database. As part of the managed platform of the .NET Framework, ADO.NET manages both the requests for the data and the responses from the data source. A DataAdapter object connects to the database and fills the DataSet object. Then it connects back to the database to update the database based on the operations performed while the DataSet held the data. While the DataSet has no knowledge of the data source, the managed provider has detailed and specific information. Its role is to connect, fill and persist the DataSet to and from the database. Persist means keeping the object's state persistent because it is accessed regularly. Persistence is accomplished through object serialization.

When we click finish, we see that the Students.mdb is the permanent database file. If we double-click the XML schema files, we get a designer view of the files, and also see the one to one correlation between the objects in the Database explorer and the XSD files:

Notice the XSD files have a TableAdapter component. This is the DataAdapter component stated about earlier. The TableAdapter contains the query that is used to select data from your data source as well as connection information for connecting to your database. Being a DataAdapter object, it is sort of at the center of the approach to provide a bridge to retrieve and save data between its DataSet and its source data store. Normally it accomplishes this by means of requests to the appropriate SQL commands made against the database. Notice the methods named immediately below the TableAdapter.

The BindingSource component acts like a bridge between your DataSet and your data-bound controls. The BindingNavigator provides a standard UI component that allows you to navigate through the rows that are in your data source. This data access component is bound to the BindingSource component much as the DataGridView component is. Arrows on both the Instructor and the Department components in the Database Explorer view should be clicked to give you the DataGrid view choice. After choosing that for both of them, you can drag and drop them onto the form: both tables will bind to the UI because they have relation. How? By choosing "Add Relation" from the Data selection on the toolbar. The relational dialog box is shown below: