When I first began working with ADO.NET I found many great tutorials about basic usage. I learned to create Connection objects, DataAdapters, and DataSets for each form in my application. As I began applying these principals to larger projects I quickly became frustrated with having to maintain separate Connection objects, DataAdapters, and DataSets in each form. As program specifications would fluctuate, it was annoying that any change to the underlying database necessitated changes in each form. On top of this, maintaining a separate DataSet for each form in my application was a tremendous waste of resources, and keeping all DataSets in synch was nearly impossible.
I was able to overcome these frustrations through the use of a centralized data component which could be passed to all forms in my applications. The data component stores any Connection object(s), DataAdapters, Command objects, and even the DataSet for use throughout the program.
This tutorial will demonstrate the technique of creating and using an application-wide data component. The program itself is trivial and serves no real purpose. My goal is to demonstrate the use of an ADO.NET data component, not to create a marketable application. I hope to suggest some practices that may enable you to develop and maintain applications more accurately and rapidly.
In presenting this tutorial, I assume that the reader is familiar with C#, Visual Studio .NET 2003, and SQL Server (MSDE). I also assume familiarity with basic ADO.NET components and terminology (i.e. DataSet, DataAdapter, Connection object, etc.).
This tutorial is based around the pubs database located in any standard installation of SQL Server or MSDE. All code is created using Visual Studio .NET 2003 in C#.
To begin we will create a "Visual C# Project" and "Windows Application" named pubs. The first thing I always do when creating a new project is to rename the default Form1.cs to frmMain.cs to help me keep track of my MDI Parent. When you do this, make sure that you do not forget to change the name of the class and constructor names in the code.
To setup our program's framework, we will add two additional Windows Forms to our project: frmStores1.cs and frmStores2.cs.
The paradigm I am suggesting involves passing, by reference, a data component to each form which requires its use. We will create an MDI application with the MDI parent maintaining the data component. There are many different ways to accomplish this, but I believe this to be the most concise for demonstration. Accordingly, set the IsMdiContainer property of frmMain equal to true. Add a menu with two menu items to this form which will open our two MDI child forms. Add code to the "Click" event of each to open the appropriate child window (see below). In each of the child forms, frmStores1 and frmStores2, add a DataGrid (which will allow us to monitor our data component) and a button (which will allow us to interact with our data component).
private void mnu_frmStores1_Click(object sender, System.EventArgs e)
{
frmStores1 fs1 = new frmStores1 ();
fs1.MdiParent = this;
fs1.Show();
}
private void mnu_frmStores2_Click(object sender, System.EventArgs e)
{
frmStores2 fs2 = new frmStores2 ();
fs2.MdiParent = this;
fs2.Show();
}
At this point, we are ready to begin the construction of our data component. Add to the project a "Component Class" named DataComponent.cs. This component class will contain our database Connection object, all DataAdapters, and the DataSet.
Using the Server Explorer in Visual Studio, drag the stores table from the pubs database onto the design view of DataComponent.cs. A SQLConnection and a SQLDataAdapter are created. I have renamed these cnPubs and daStores, respectively. By right-clicking on the daStores you are able to "Generate Dataset". You should choose to add the DataSet to the designer and name the instantiated DataSet dsPubs.
dsPubs should be made public, but everything else should be left private to protect your data access. In the code of our data component, we will add the following public method through which we can access the DataAdapter.
public void fill_Stores ()
{
try
{
daStores.Fill (dsPubs.stores);
}
catch (System.Exception exp)
{
throw exp;
}
}
When creating a real-world application, you would naturally add, fill, and update methods for each of your DataAdapters. Typically there would also be many overloaded methods to allow the DataSet to be filled selectively (unless you want to work with the entire database!). I rarely explicitly use the Delete() method of the DataAdapters, finding it more intuitive to make any changes or deletions to my DataSet than invoking the Update() method for the appropriate table.
One change should be made to your DataSet (DSPubs.xsd). When you open the design view, you will see a representation of the stores table. For the "stor_id" row you should change the AutoIncrementSeed and AutoIncrementStep both to "-1". This will cause any rows you create in your DataSet to have an auto-increment number beginning with -1 and counting down. This ensures that you will never have a primary key conflict as you (and possibly many other users simultaneously) add rows to the database. When a new row is added, its key will be updated from the negative value to the database assigned value. I will not discuss this any more, but if you are interested there is an excellent article available on this subject in the MSDN library by William Vaughn titled "Managing an @@IDENTITY Crisis" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp).
Now for the truly exciting implementation of our data component! In each of your three Windows forms, you should declare a DataComponent object. There are ways to accomplish this through the designer, but I will focus on the code. This object should only be instantiated in frmMain:
private DataComponent data = new DataComponent();
You should also change the default constructors of your two child forms to accept, as a parameter, a reference to a DataComponent object. Inside the constructor you will set the declared data component to the reference of the object which was passed. This will allow the object to maintain scope as long as the form. Also, set the DataSouce property of each data grid so that we can view the contents of the DataSet.
private DataComponent data;
public frmStores1(ref DataComponent dataComponent)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
data = dataComponent;
dataGrid1.DataSource = data.dsPubs.stores;
}
You will need to alter the lines of code in frmMain where your child windows are created so that our data component is passed into each:
private void mnu_frmStores1_Click(object sender, System.EventArgs e)
{
frmStores1 fs1 = new frmStores1 (ref data);
fs1.MdiParent = this;
fs1.Show();
}
private void mnu_frmStores2_Click(object sender, System.EventArgs e)
{
frmStores2 fs2 = new frmStores2 (ref data);
fs2.MdiParent = this;
fs2.Show();
}
Now, in each of the child forms set the DataGrid to display the table from the data component and have the Click event of the button fill the DataTable.
private void button1_Click(object sender, System.EventArgs e)
{
try
{
data.fill_Stores();
}
catch (System.Exception exp)
{
MessageBox.Show (exp.ToString());
}
}
Try running the program. Open frmStores1 and click the button. You can see that the DataSet is filled. Open frmStores2, and you will see the same data. Both child forms are displaying information from the same DataSet. If you make changes to any of the rows you will see that as soon as you leave that row (and the EndCurrentEdit method is automatically invoked) the changes are immediately visible in the other window. Note that since we have not implemented any Update() commands, none of the changes you make will be persisted to the database. But, changes will remain in memory for as long as frmMain has scope.
You have created a data component which can be passed by reference throughout your program! Now, you may say that this is a fine idea, but it would be impractical for me to programmatically bind hundereds or thousands of controls to the DataSet. This problem is easily overcome by the use of a "placeholder" DataSet and DataViews.
Go back into the designer for frmStores1.cs. Create a typed DataSet in the designer as well as a DataView whose Table property is set to the stores table in that DataSet. You should, in the designer, set the DataSource property of the DataGrid to dvStores and remove from your code the line:
dataGrid1.DataSource = data.dsPubs.stores;
In its place, programmatically set the Table Property of the DataView to the DataTable in your data component and you are done!
dvStores.Table = data.dsPubs.stores;
You can now bind any controls to the DataView from within the designer. The DataView will be populated at run time by the DataSet in your data component.
Both problems which were listed at the beginning of this article have been overcome. We have only one set of DataAdapters and one filled DataSet that can be used throughout the program. Additionally, because we do not allow direct access to any of the DataAdapters, it is possible to change any of the implementation details in the data component without necessitating changes in any other part of the program. That means that you could easily change even the database itself from SQL Server to Oracle or MySQL without necessitating major changes to the application. The only constraint would be that the table and column names remain consistent.
