Using ADO.NET Database 1 for beginners
By Hüseyin Altindag

Introduction

This is a simple ADO.NET database application that returns results
from a database table, writes the output to a DataGrid and TextBoxes,
and uses Buttons to navigate through records.

It is including:
1. Connection to a ADO.NET database
2. Use of a DataSet to fill with records
3. Use of a DataAdapter to load data into the DataSet
4. Display data in a DataGrid
5. DataBindings for TextBoxes
6. Use of the CurrencyManager
7. Navigation through records with Next,Previous,Last,First buttons.

This project was developed using Visual Studio.NET 2003 and Microsoft Access
on Windows XP Pro

What is ADO.NET ?

ADO.NET is the new database technology of the .NET(DotNet) platform,
and it builds on ADO(ActiveX Data Objects).
ADO is a language-neutral object model that is
the keystone of Microsoft's Universal Data Access strategy.
ADO.NET defines DataSet and DataTable objects which are
optimized for moving disconnected sets of data across intranets and Internets,
including through firewalls. It´s also including the traditional Connection and
Command objects, as well as an object called a DataReader that resembles
a forward-only, read-only ADO recordset.

If you create a new application your application requires
most of the time some form of data access.
ADO.NET provides data access services in the Microsoft .NET Platform.
You can use ADO.NET to access data by using the new .NET Framework data providers which are
- Data Provider for SQL Server(System.Data.SqlClient).
- Data Provider for OLEDB(System.Data.OleDb).
- Data Provider for ODBC(System.Data.Odbc).
- Data Provider for Oracle(System.Data.OracleClient).

ADO.NET is a set of classes that expose data access services to the .NET developer.
The ADO.NETclasses are found in System.Data.dll and integrated with XML classes in System.Xml.dll.

There are two central components of ADO.NET classes:
the DataSet, and the .NET Framework Data Provider.

Data Provider is a set of components including
the Connection object(SqlConnection, OleDbConnection, OdbcConnection, OracleConnection),
the Command object(SqlCommand, OleDbCommand, OdbcCommand, OracleCommand),
the DataReader object(SqlDataReader, OleDbDataReader,OdbcDataReader, OracleDataReader),
and the DataAdapter object(SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter).

DataSet object represents a disconnected cache of data
which is made up of DataTables and DataRelations that
represent the result of the command.

The ADO.NET Object Modell



1. Connection to a ADO.NET database

Before working with a database, you have to add (here) the OleDb .NET Data Provider namespace,
by placing the following at the start of your code module:
using System.Data.OleDb;
Similarly for the SqlClient .NET Data Provider namespace:
using System.Data.SqlClient;
The using statement should be positioned first in your code.
Now we have to declare a connection string pointing to a MS Access database "PersonDatabase.mdb".
public string conString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\PersonDatabase.mdb";
The database should be in the specified path, otherwise you should change the path accordingly.

The next step is to create an OleDbConnection object. We pass then the connection string to this OleDbConnection object.
You can code now to create a new ADO.NET Connection object
in order to connect to an OLE DB provider database .

OleDbConnection con = new OleDbConnection(conString);
You can also explicitly reference declared objects if you don’t mind typing a lot.
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(conString);
//
//1.Connection to a database
//using declaration for OLE DB
using System.Data.OleDb;
//specify the ConnectionString property
public string conString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\PersonDatabase.mdb";
//Initializes a new instance of the OleDbConnection
OleDbConnection con = new OleDbConnection (conString);

2. DataSet

The DataSet is similar to an array of disconnected Recordset objects.
It supports disconnected data access and operations, allowing greater scalability
because you no longer have to be connected to the database all the time.
DataSet is a copy of a extracted data being downloaded and
cached in the client system.

DataSet object is made up of 2 objects:
-DataTableCollection Object containing null or multiple DataTable objects(Columns, Rows, Constrains)
-DataRelationCollection Object containing null or multiple DataRelation objects
which establishes a parent/child relation between two DataTable objects.
//Create a DataSet
DataSet dset = new DataSet();

There are two types of DataSets:
1. Typed dataset
2. Untyped dataset

1. Typed dataset is derived from the base DataSet class and then uses information
in an XML Schema file (.xsd file) in order to generate a new class.
Information from the schema (tables, columns, and so on) is generated and compiled
into this new dataset class as a set of first-class objects and properties.

Typed dataset is easier to read.It´s also supported by IntelliSense in the Visual Studio Code Editor.
At compile time, it has type checking so that there are less errors in assigning values to dataset members.
Using Typed dataset has many advantages.
Example:
The following code accesses the CustomerID column in the first row of the Customers table
string str;
str=dset.Customers[0].CustomerID;

2. Untyped dataset is not defined by a schema, instead, you have to add tables, columns, and other elements to it yourself,
either by setting properties at design time or by adding them at run time.
For example : scenario: if you don´t know in advance what the stucture of your program is
that interacting with a component that returns a dataset.
the equivalent code above for Untyped dataset is:
string str;
str=(string)dset.Tables["Customers"].Row[0].["CustomerID"];
A dataset is a container; therefore, you have to fill it with data.
You can populate a dataset in a variety of ways;
# by using Fill method
# by creating DataRow objects and adding them to the table's Rows collection(only at run time).
# Read an XML document or stream into the dataset
# Merge (copy) the contents of another dataset

3. DataAdapter

DataAdapter object is like a bridge that links the database
and a Connection object with the ADO.NET-managed DataSet object
through its SELECT and action query Commands.

It specifies what data to move into and out of the DataAdapter.
Often this takes the form of references to SQL statements or stored procedures
that are invoked to read or write to a database.
The DataAdapter provides four properties that allow us to control
how updates are made to the server:
SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand.
The four properties are set to Command objects that are used when data is manipulated.

The DataAdapter includes 3 main methods:
- Fill (populates a DataSet with data)
- FillSchema (queries the database for schema information that is necessary to update)
- Update (to change the database DataAdapter calls the DeleteCommand,
the InsertCommand and the UpdateCommand property)
For instance , when we call the DataAdapter's Fill method to retrieve
data from a data source and pour it into a DataSet, the Command object in the
SelectCommand property is used. The DataAdapter is the gatekeeper that sits
between our DataSet and the data source.

//Create an instance of a OleDbDataAdapter by passing OleDbConnection object and select.. query
OleDbDataAdapter dAdapter = new OleDbDataAdapter ("select * from PersonTable", con );
//fill the DataSet with records from the table "PersonTable"
dAdapter.Fill(dSet,"PersonTable");

Here is the method used in this project to get data connection, DataSet and DataAdapter.
This method is in the file "DataAccessTierClass.cs".

public bool fnGetDataConnection()
{
     try {
         con =new OleDbConnection(conString);
         dAdapter=new OleDbDataAdapter("select * from PersonTable", con);
         dSet=new DataSet();
         //refreshes rows in the DataSet
         dAdapter.Fill(dSet,"PersonTable");
      }catch(Exception ex) {
            MessageBox.Show("Error : "+ex.Message);
            //connectection failed
            return false;
      } //try-catch
      //connection ok!
      return true;
}

4. Display data in a DataGrid

The Windows Forms DataGrid control displays data in a series of rows
and columns. The Windows Forms DataGrid control provides a user interface
to ADO.NET datasets. It displays tabular data and allows
for updates to the data source.
When you set DataGrid control to a valid data source, the control
will be automatically populated, creating columns and rows based
on the shape of the data.
You can use the DataGrid control for displaying either a single table
or the hierarchical relationships between a set of tables.
Iy you want to work with the DataGrid control, DataGrid should be
bound to a data source by using
- the DataSource and
- DataMember properties at design time
or
- the SetDataBinding method at run time.

Here is the binding to the DataGrid control with DataSet I used in this project.
this . dataGrid1 . DataSource = datc . dSet . Tables["PersonTable"];

You can only show one table in the DataGrid at a time.
If you define a parent-child relationship between tables,
you can navigate between the related tables to select
the table you want to display in DataGrid control.
Example:
dset.Relations.Add("CustomerOrders",
dset.Tables["customers"].Columns["CustomerID"],
dset.Tables["orders"].Columns["CustomerID"]);
//here you can use one of the following
this.dataGrid1.DataSource=dset.Tables["customers"];
OR
this.dataGrid1.SetDataBinding(dset,"customers");
customers: Parent table
orders      : Child table
CustomerID in Orders is a foreign key referring to CustomerID primary key in Customers table.

If you update the data in the bound DataSet through any mechanism,
the DataGrid control reflects the changes.
You can update the data in the DataSet through the DataGrid control,
if the data grid and its table styles and column styles
have the ReadOnly property set to false.

There are 4 most typical valid data sources for the DataGrid

• DataTable class
• DataView class
• DataSet class
• DataViewManager class

5. DataBindings for TextBoxes

DataBinding is the ability to bind some elements of a data source with some graphical elements of an application.
The data in Windows Form is bound by calling DataBindings.
Windows Forms allow you to bind easily to almost any structure that contains data.
Windows Forms Controls support 2 types of data binding:
1.Simple Data Binding
2.Complex Data Binding

Simple Data Binding allows you to display a single data element,
such as a column value from a dataset table, in a control.
You can simple-bind any property of a control to a data value.
Simple Data Binding can be performed either at design time
using DataBindings property of a control OR dynamicall at run time.
This is the type of binding typical for controls such as a TextBox control
or Label control that displays typically only a single value.
Example:
// Simple DataBinding for TextBox "textBox1"
textBox1.DataBindings.Add("Text", dataset, "studentTable.studentID");
The control "textBox1" is bound to the "studentID" column of a table "studentTable"
on the DataSet(dataset) through the BindingContext object.

Complex data binding is the ability of a control to bind to more than one
data element, typically more than one record in a database,
or to more than one of any other type of bindable data element.
DataGrid, ListBox and ErrorProvider controls support Complex data binding.

Here is the method used in this project to bind all TextBoxes:
private void fnGetDataBindingForTextBoxes()
{
      this.textboxFirstname.DataBindings .Add("Text", datc.dSet.Tables["PersonTable"],"FirstName");
      this.textboxLastname.DataBindings .Add("Text", datc.dSet.Tables["PersonTable"],"LastName");
      this.textboxTitle.DataBindings .Add("Text", datc.dSet.Tables["PersonTable"],"Title");
      this.textboxCity.DataBindings .Add("Text", datc.dSet.Tables["PersonTable"],"City");
      this.textboxCountry.DataBindings .Add("Text", datc.dSet.Tables["PersonTable"],"Country");
}

6. Using of the CurrencyManager

Every Windows Form has a BindingContext object keeping track
of all the CurrencyManager objects on the Windows Form.
CurrencyManager keeeps track of the position in the data source.
When you bind a data object to a control(i.e TextBox), a CurrencyManager object
is automatically assigned. If you bind several controls to the same data source,
they share the same CurrencyManager.


Example:
* If you want to know how many records are in a data table, you simply
query the BindingContext object's Count property.
this.BindingContext[dataset1,"PersonTable"].Count - 1 ;
*If you want to get the current position from the BindingContext object
this.BindingContext[dataset1, "PersonTable"].Position + 1;

Here is the method I used to initialize the CurrencyManager for the table "PersonTable".
public void fnSetCurrencyManager()
{
     currManager = (CurrencyManager)this. BindingContext [ datc.dSet.Tables["PersonTable"] ] ;
}

7. Navigation through records with Next,Previous,Last,First buttons

As soon as you get the data populated in the DataGrid,
you can navigate through records by using Next,Previous,Last,First buttons
or by clicking the rows of the DataGrid or by using
the arrow keys (UP ARROW and DOWN ARROW).

If the DataGrid is currently displaying data, none of the
standard keyboard events are raised for the navigation keys.
You can still use Up and Down arrow keys to navigate in the DataGrid
but, because no keystrokes captured, you don´t get the record
position in the StatusBar.

In order to capture keystrokes on the DataGrid, you have to override
the ProcessCmdKey method that processes a command key.
I haven´t implemented that because it would be here a bit too much coding.
Maybe in the next second part where you manupulate data in TextBoxes and in the DataGrid.

Now back to the buttons:
When you click First button, position will be set to 0(zero) because the first row starts by zero.
currManager.Position=0; and

• Disable First and Previous buttons because there is no previous record in the data source.
• Enable Next and Last buttons because there are records forwards.

When you click Next button, position in the data is increased by 1 and moved to the next row.
currManager.Position +=1; and

• Enable First and Previous buttons as long as there are forwards records.
• Otherwise Disable Next and Last buttons which means you reached the end of the records.

When you click Previous button, position in the data is decreased by -1 and moved to the previous row.
currManager.Position -=1; and

• Enable Next and Last buttons as long as there are records backwards.
• Otherwise Disable First and Previous buttons which means you reached the beginning of the records.

When you click Last button, position in the data is set to the last record(row).
this.currManager.Position=this.currManager.Count-1; and

• Disable Next and Last buttons because there are no records forwards any more .
• Otherwise enable First and Previous buttons so that you can navigate backwards.

To enable and disable the buttons I use the function(fn)/method fnEnableDisableButtons
with 4 parameters(2 buttons, string for StatusBar, bool for true=enabling, false=disabling)

private

Download Source

In conclusion

I tried to show the basics of the ADO.NET Database to the beginner
how to use the ADO.NET in a database application
and to keep the code as readable as possible.
I intend to implement in a new next version the data manupulation
on the DataGrid and TextBoxes(Delete, Save/Update, Search, Add)

I hope it can help you understand a bit what ADO.NET is.

Good coding

Hüseyin Altindag