Using ADO.NET for Beginners

• Download source files - 28.4 KB

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 (First, Previous, Next, Last) to navigate through the records.

After getting lots of responses and suggestions from the users, I changed some points and made the code more readable. Like everyone else, I also searched for a most requested method in MSDN Library to trap the keystrokes (Up, Down, Esc...) in Windows Forms and included it in the code because some users asked me desperately for that. You can find the other requested methods as well to get the contents of a cell/row in a DataGrid.

I chose Microsoft Access database (as reflected in the ADO.NET OleDb objects) because it's easy to use and you don't need to have Microsoft SQL Server running. But ADO.NET is highly optimized if you are working with Microsoft SQL Server databases (as reflected in the ADO.NET SQL objects).

I have now added a second part to this project (Personal Address Book) on Database Manipulation with ADO.NET for beginners where you can do data manipulation in TextBoxes (Delete, Save/Update, Add).

Contents

1. What is ADO.NET?
2. Connection to an ADO.NET Database
3. Use of a DataSet to Fill with Records
4. Use of a DataAdapter to Load Data into the DataSet
5. Display Data in a DataGrid / Data Relationship between Two Tables
6. DataBindings for TextBoxes
7. Using the CurrencyManager
8. Navigation through Records with Next, Previous, Last, First buttons
9. How to Trap Keystrokes in the DataGrid (Up, Down, Esc, ...)

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

1. What is ADO.NET?

ADO.NET is the new database technology of the .NET (Dot Net) platform, and it builds on Microsoft ActiveX® Data Objects (ADO).

ADO is a language-neutral object model that is the keystone of Microsoft's Universal Data Access strategy.

ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.

ADO.NET defines DataSet and DataTable objects which are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. It also includes 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 some form of data access most of the time.

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.NET classes are found in System.Data.dll and are integrated with the 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 Model

top

2. Connection to an 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;" + 
            @" DataSource=..\\..\\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 then pass 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);

Here is the code snippet for 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);
// open the database connection with the property settings
// specified by the ConnectionString "conString"
con.Open();

In many earlier applications, the tendency was to open a connection when you start the application and not close the connection until the application terminates. It is an expensive and time-consuming operation to open and close a database connection. Most databases have a limit on the number of concurrent connections that they allow.

For example: each connection consumes a certain amount of resources on the database server and these resources are not infinite. Most modern OLE DB providers (including SQL Server provider) implement connection pooling. If you create database connections, they are held in a pool. When you want a connection for an application, the OLE DB provider extracts the next available connection from the pool. When your application closes the connection, it returns to the pool and makes itself available for the next application that wants a connection.

This means that opening and closing a database connection is no longer an expensive operation. If you close a connection, it does not mean you disconnect from the database. It just returns the connection to the pool. If you open a connection, it means it's simply a matter of obtaining an already open connection from the pool. It's recommended in many ADO.NET books not to keep the connections longer than you need to. Therefore, you should:

• Open a connection when you need it, and
• Close it as soon as you have finished with it

For example: here is another way to get a connection to a database:

// setup the global SqlConnection object and constr in your class
  private SqlConnection con = null;
  private string constr ="Integrated Security=SSPI;" +
  "Initial Catalog=Northwind;" +
  "Data Source=SONY\\MYSQLSERVER;";
  
  private void fnGetConnection()
  {
    try
    {
       // setup the database connection
       con = new SqlConnection(constr);
       con.Open();
     }catch (Exception ex) {
        MessageBox.Show("Error in connection : "+ex.Message);
     }finally {
        // dispose of open objects
        if (con != null)
          con.Close();
     } //finally
 }

For example: you want to open the connection, fill the DataSet, and close the connection. If the connection fails, you want to get the error message.

try
{
  con.Open();
  dadapter.Fill(dataset1);
  con.Close();
} catch (Exception ex) {
   MessageBox.Show("Error in retrieving data: " + ex.Message);
}

For example: if you want to save the data you changed, then you just open the connection, update the data, and close the connection and accept the changes. If it fails, display an error message, reject the changes, and close the connection.

try
{
  DataSet changes = dataset.GetChanges();
  con.Open();
  datapter.Update(changes);
  con.Close();
  dataset1.AcceptChanges();
}catch (Exception ex) {
  MessageBox.Show("ErrorR: " + ex.Message);
  dataset1.RejectChanges();
  con.Close();
}

top

3. 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 an extracted data being downloaded and cached in the client system.

The DataSet object is made up of two objects:

• DataTableCollection object containing null or multiple DataTable objects (Columns, Rows, Constraints).
• DataRelationCollection object containing null or multiple DataRelation objects which establish 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

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. Therefore, 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;

Create a Typed DataSet without Designer - Manually

• Call the command prompt (cmd) at the location of the XSD schema file.
• Use the XSD.EXE utility to create the class for the typed DataSet.

xsd.exe /d /l:cs mydataset.xsd /n:mynamespace
 /d : you create a DataSet. 
 /l:cs - set the language as C#.
 /n:mynamespace - the class should use the namespace "mynamespace".

The output of XSD.EXE with these arguments will be a .cs class file (mydataset.cs).

Use csc.exe to compile the class.

csc.exe /t:library mydataset.cs /r:System.dll /r:System.Data.dll
  /r:System.XML.dll /out:bin/mydataset.dll /t:library

Compile as a library component (DLL).

• /r: - specifies assemblies you need to reference
• /out: - saves the compiled assembly in the bin subdirectory of the current directory

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. Typical scenario: if you don't know in advance what the structure of your program is that is 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 DataAdapter objects and Fill method

  For example:

  string strCon = @"Data Source=SONY\MYSQLSERVER;" +
    "Initial Catalog=Northwind;Integrated Security=SSPI";
    string strSql="select * from customers";
    SqlConnection con=new SqlConnection(strCon); 
    con.Open();
    
    SqlDataAdapter dadapter=new SqlDataAdapter();
    dadapter.SelectCommand=new SqlCommand(strSql,con);
    DataSet dset=new DataSet();
    dadapter.Fill(dset);
    con.Close();
    
    this.dataGrid1.DataSource=dset;

• by creating DataTable, DataColumn and DataRow objects programmatically

  After you create a DataTable and define its structure using columns and constraints, you can add new rows of data to the table.

  For example:

  DataSet dset;
  DataTable dtbl;
  DataRow drow;
  //create a new row
  drow=dtbl.NewRow();
  //manipulate the newly added row using an index or the column name
  drow["LastName"]="Altindag";
  drow[1]="Altindag";
  //After data is inserted into the new row, the Add method is used
  //to add the row to the DataRowCollection 
  dtbl.Rows.Add(drow);
  //You can also call the Add method to add a new row by passing in an
  //array of values, typed as Object
  dtbl.Rows.Add(new object[] {1, "Altindag"});

• Read an XML document or stream into the DataSet

  The following code creates a SqlConnection object that opens a connection to the Pubs database, creates a SQL query to get the data of the Authors table as XML, and it creates a new SqlCommand object. After creating a new DataSet, it uses the ExecuteXmlReader method to pass an XmlReader object to the DataSet's ReadXml method, which allows the DataSet to populate itself from the XmlReader. Finally, the code sets the DocumentContent property to the result of the GetXml method of the DataSet. XML uses the XSL Transformation document authors.xsl (included in the project) to format the XML content displayed by the XML control.

  For example:

  protected System.Web.UI.WebControls.Xml XmlDisplay;
    
  string strCon = @"Data Source=SONY\MYSQLSERVER;" +
  "Initial Catalog=pubs;Integrated Security=SSPI";
  SqlConnection con=new SqlConnection(strCon);
    
  con.Open();
  try
  {
    string strSql="select * from FROM authors FOR XML AUTO, XMLDATA";
    SqlCommand cmd=new SqlCommand(strSql, con);
    DataSet dset=new DataSet();
    dset.ReadXml(cmd.ExecuteXmlReader(),XmlReadMode.Fragment);
    XmlDisplay.DocumentContent = dset.GetXml();
  }finally {
    con.Close();
  }

• Merge (copy) the contents of another DataSet, with the Merge method

  You can merge two DataSet objects that have largely similar schemas. You can use a merge typically on a client application to incorporate the latest changes from a data source into an existing DataSet. This allows the client application to have a refreshed DataSet with the latest data from the data source.

  For example:

  dataset1.Merge(dataset2);

top

4. 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 DataSet. 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
• DeleteCommand

The four properties are set to Command objects that are used when data is manipulated.

The DataAdapter includes three 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 properties)

For example:

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 a data connection, DataSet and DataAdapter. You can find this method 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;
}

top

5. Display Data in a DataGrid / Data Relationship Between Two Tables

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 a 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. If 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 the DataGrid control.

For example:

dset.Relations.Add("CustomerOrders", 
       dset.Tables["customers"].Columns["CustomerID"], 
       dset.Tables["orders"].Columns["CustomerID"]);
//now 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.

Here is a typical example of how to use the parent-child relationship between the tables "Customers" and "Orders" on a DataGrid control.

The DataRelation in this example allows you to navigate from one DataTable ("Customers") to another DataTable ("Orders") within a DataSet. The DataSet class can contain null or many DataTable objects. "Customers" and "Orders" DataTables contain a column named "CustID", which is a link between these two DataTable objects.

To run and test this example, create a new project, drag/drop a Button (here: button1) and a DataGrid (here: dataGrid1) on the Form and copy the following code snippets (fnGetConnectionString(), button1_Click) and you additionally need SQL Server 2000 running or MS-Access.

public string fnGetConnectionString()
{
  //it gives back the connection string :
  //    change for you the Data Source=.... accordingly
  //for MS-Access 
  //return "Provider=Microsoft.Jet.OLEDB.4.0;
  //        Data Source=..\\..\\Northwind.mdb";
  //for SQLSERVER2000
  return "data source=SONY\\MYSQLSERVER;initial" + 
         " catalog=Northwind;integrated security=SSPI;"; 
}

private void button1_Click(object sender, System.EventArgs e)
{
  // for SQLServer2000
  DataSet dset=new DataSet();
  string strCustomers="select * from customers";
  string strOrders="select * from orders";

  SqlConnection sqlcon=new SqlConnection(fnGetConnectionString()); 

  SqlDataAdapter dadapter=new SqlDataAdapter(strCustomers,sqlcon);
  dadapter.Fill(dset,"Customers");

  dadapter=new SqlDataAdapter(strOrders,sqlcon);
  dadapter.Fill(dset,"Orders");
  // Add the relation to the DataSet.
  dset.Relations.Add("Customer Orders",
  dset.Tables["Customers"].Columns["CustomerID"],
  dset.Tables["Orders"].Columns["CustomerID"]);

  //Display data in the DataGrid
  //both work fine
   this.dataGrid1.DataSource=dset.Tables["Customers"];
  //this.dataGrid1.SetDataBinding(ds,"Customers");

  //for MS-Access
  /*

  //create a DataSet object which will contain the following 2 DataTables
  DataSet dset=new DataSet();

  string strCustomers="select * from customers";
  string strOrders="select * from orders";

  OleDbConnection con=new OleDbConnection(fnGetConnection());

  OleDbDataAdapter dadapter=new OleDbDataAdapter(strCustomers,con);
  //fill the DataSet with the records from the Customers table
  dadapter.Fill(dset,"Customers");

  dadapter=new OleDbDataAdapter(strOrders,con);
  //fill the DataSet with the records from the Orders table
  dadapter.Fill(dset,"Orders");

  //establish the relation between the 2 DataTable objects
  dset.Relations.Add("Customer Orders",
                      dset.Tables["Customers"].Columns["CustomerID"],
                      dset.Tables["Orders"].Columns["CustomerID"]);

  //both work fine
  this.dataGrid1.DataSource=dset.Tables["Customers"]; //show the data in DataGrid
  //this.dataGrid1.SetDataBinding(ds,"Customers");

  */
}

Now 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 DataGrid and its table styles and column styles have the ReadOnly property set to false. There are four most typical valid data sources for the DataGrid:

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

The first time this application was published, I got e-mails from users asking me how to get the contents of a DataGrid cell you clicked, or how to get the DataGrid row contents you clicked. So now, I've one method to do that and didn't want to withhold it from you.

// you click in the cell or the rows
// of the DataGrid and get the content of it

private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)
{
  /* be warned: if you click the last cell on the Datagrid, you get
    an unhandled exception of type 'System.ArgumentOutOfRangeException.
    because there is no further columns after the last column(Country). 
    To avoid this, I tried a different way: in a try-catch, get the right
    cell content. if the last column cell clicked, display the exception
    and the cell content one before. But you can comment 
    the MessageBox.Show line if not wished */

  //get the row number on the DataGrid
  int iRownr=this.dataGrid1.CurrentCell.RowNumber;
  //get the column number on the DataGrid
  int iColnr=this.dataGrid1.CurrentCell.ColumnNumber;
  //get the content of the cell in the clicked cell on the Datagrid
  object cellvalue1=this.dataGrid1[iRownr, iColnr];
  object cellvalue2=null;
  //get the next cell content in the same row
  try {
        cellvalue2=this.dataGrid1[iRownr, iColnr+1];
        //display (cellvalue1+cellvalue2) in TextBox "textBox1"
        this.textBox1.Text=cellvalue1.ToString()+" "+cellvalue2.ToString();
  } catch(Exception ex) {
    //the exception occurs here because we increment iColnr+1
    MessageBox.Show("No further columns after the last " + 
        "column(Country) -->> "+ex.Message,"STOP");
    cellvalue2=this.dataGrid1[iRownr, iColnr-1];
    //display this time (cellvalue2+cellvalue1) in TextBox "textBox1"
    this.textBox1.Text=cellvalue2.ToString()+" "+cellvalue1.ToString();
  }//catch
}

top

6. 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 Forms is bound by calling DataBindings. Windows Forms allows you to bind easily to almost any structure that contains data.

Windows Forms Controls support two types of data binding:

• Simple Data Binding
• 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 dynamically 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.

For example:

// Simple DataBinding for TextBox "textBox1" 
  textBox1.DataBindings.Add("Text", dataset, "studentTable.studentID");

The control "textBox1" above 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.

Typical scenario:

You want to display the names of products in a list box and then retrieve in a TextBox the ProductID of a product which you selected.

For example:

You could add complex data binding by using the DataSource and DataMember properties.

datagrid1.DataSource = dSet;
// Use the DataMember property to specify the DataTable.
datagrid1.DataMember = "PersonTable";

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");
}

top

7. Using the CurrencyManager

You use the CurrencyManager object if you want to keep data-bound controls synchronized with each other which means showing data from the same record. For example: if you want to add a TextBox control to a form and bind it to a column of a table (e.g., Customers.FirstName) in a DataSet (e.g., dSet), the control is going to communicate with the BindingContext object for this form. In turn, the BindingContext object is going to talk to the specific CurrencyManager object for the data the TextBox control is binding.

Every Windows Form has a BindingContext object keeping track of all the CurrencyManager objects on the Windows Form. CurrencyManager keeps 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.

In a normal case where you are using an ADO.NET database (connecting and closing database) and displaying the records, e.g., in a DataGrid, you never need the CurrencyManager object. But if you want to know the exact position within a data structure (e.g., table in your database) as I did, you have to use the CurrencyManager object because the CurrencyManager has the Position property for this purpose. You can, for example, manipulate the Position property in a Next or Previous or First or Last button which I did in my program as well.

For example:

If you want to know how many records are in a DataTable, 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;

After data binding, you call and initialize CurrencyManager for your table. 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"]] ;
}

top

8. 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 I haven't implemented it, 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. You can find this method in section 9.

I also included two new methods in order to highlight the records in the DataGrid by using Next, Previous, Last or First buttons because you don't normally get the row highlighted in the DataGrid if you click such a button. By default, if you click the DataGrid row, the row will be highlighted with BackColor and ForeColor.

First method:

fnSelectUnselectLastFirstRow(int posi)

If you click First or Last button, the first or last record will be selected and highlighted in the DataGrid. You invoke for that the fnSelectUnselectLastFirstRow() method and pass as parameter 0 (zero) for the first record (fnSelectUnselectLastFirstRow(0);), and (this.currManager.Count-1) for the last record.

fnSelectUnselectLastFirstRow(this.currManager.Count-1);

Here is the first method I used for this purpose:

private void fnSelectUnselectLastFirstRow (int posi)
{
  //unselect the last selected/highlighted row
  this.dataGrid1.UnSelect(this.dataGrid1.CurrentRowIndex);
  //select the last or first row
  this.dataGrid1.Select(posi);
}

Second method:

fnSelectUnselectCurrentRow(int num1, int num2)

If you click Next or Previous button, the next or previous record will be selected and highlighted in the DataGrid. You call for that the method fnSelectUnselectCurrentRow(); and pass as parameter (1,-1) for the next record (fnSelectUnselectCurrentRow(1,-1);), or (-1,1) for the previous record (fnSelectUnselectCurrentRow(-1,1);).

Here is the second method used for it:

private void fnSelectUnselectCurrentRow(int num1, int num2)
{
  //get the current row index
  this.iRowIndex=this.dataGrid1.CurrentRowIndex;
  //increment or decrement the index by (num1,bum2)1,-1 or -1,1 depending on
  //Next or Previous button because we want to select next or previous row
  //if num1 is +1 you clicked Next so select next row
  //if num1 -1 you clicked Previous so select previous row
  //I use in both select and unselect plus(+)
  // it´s like in math: e.g.7+(-1)=7-1= 6 or7+(+1)=7+1=8
  this.iRowIndex=this.iRowIndex+num1;
  //select the current row
  this.dataGrid1.Select(this.iRowIndex);
  //increment or decrement the index by -1 or 1 
  //so that we can unselect the previous row
  this.iRowIndex=this.iRowIndex+num2;
  //unselect the previous row
  this.dataGrid1.UnSelect(this.iRowIndex);
}

Now back to enabling and disabling 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 forward.

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 forward 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/method fnEnableDisableButtons with four parameters (two Buttons, string for StatusBar, bool for true=enabling, false=disabling).

private void fnEnableDisableButtons(Button bt1, Button bt2, string str, bool b)
{
  bt1.Enabled=b;
  bt2.Enabled=b;
  this.statusBar1.Text=str;
}

top

9. How to Trap Keystrokes (Up, Down, Esc, NumLock...) in the DataGrid

Every time you press the keys Up, Down, NumLock and Esc in the DataGrid, I display text in the statusBarPanel1 and statusBarPanel2, but you don't get record numbers displayed because I thought it would be a bit confusing and too much coding.

Like many users, I also looked for a method to catch the keystrokes in a DataGrid, and encountered it first in MSDN Library. So I decided to include it in the code so that users can make use of it. For most purposes, the standard KeyUp, KeyDown, and KeyPress events can capture and handle keystrokes. However, not all controls raise the standard KeyUp, KeyDown events for all keystrokes under all conditions. The DataGrid control is one of them.

If no data was assigned to the grid, the arrow keys (LEFT, RIGHT, UP, and DOWN) raise only the KeyUp event. If the DataGrid displays data, none of the standard keyboard events are raised for the navigation keys. The DataGrid is the control for which this feature is most frequently requested. You also can intercept key combinations, including CTRL and ALT. This technique does not capture the Print Screen key. In order to trap keystrokes in a Windows Forms control, you can override the ProcessCmdKey method in which I changed only StatusBarPanel Text.

protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
{
  const int WM_KEYDOWN = 0x100;
  const int WM_SYSKEYDOWN = 0x104;

  if ((msg.Msg == WM_KEYDOWN) || (msg.Msg == WM_SYSKEYDOWN))
  {
     switch(keyData)
     {
       case Keys.Down:
          this.statusBarPanel2.Text="Down";
          this.statusBarPanel1.Text = "Trapped keystrokes on DataGrid...";
          break;

       case Keys.Up:
          this.statusBarPanel2.Text="Up";
          this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid...";
          break;

      case Keys.NumLock:
          this.statusBarPanel2.Text="NumLock";
          this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid...";
          break;

      case Keys.Escape:
          this.statusBarPanel2.Text="Escape";
          this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid...";
          //invoke the method "fnExitUniversal" from the class "ExitClass"
          ExitClass ec=new ExitClass();
          ec.fnExitUniversal();
          break;

  /*
    case Keys.Tab:
    this.statusBarPanel1.Text="Tab Key Captured";
    break;
    case Keys.Control | Keys.M:
    this.statusBarPanel1.Text="<CTRL>+ M Captured";
    break;
    case Keys.Alt | Keys.Z:
    this.statusBarPanel1.Text="<ALT> + Z Captured";
    break; 
  */
    } //switch
  } //if

  return base.ProcessCmdKey(ref msg,keyData);
}

top

In Conclusion

I tried to show the basics of ADO.NET to the beginners, and how to use ADO.NET in a database application and also keep the code as readable as possible. In addition, I also tried to show some interesting tips and methods for the DataGrid control (on users' request).

There is now a second part to this project (Personal Address Book): Database Manipulation with ADO.NET for beginners, where you can do data manipulation in TextBoxes (Delete, Save/Update, Add).

I hope it can help you understand (a bit) of what ADO.NET is, and you can find something useful here for your projects.

Good coding!

History

• 12^th September, 2005: Initial version

License

This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.

A list of licenses authors might use can be found here.