Programming in ADO.NET

Programming in C# with ADO.Net is much easier than it sounds. Microsoft has made it simpler for anyone to connect. ADO.NET has the ADOData class and if you are using a SQL Server Database you can use SQLDataSet classed for betrer speed and performance.

The first thing you need to do is add the following code to the top of the code form.

using System.Data;

The next thing you need to do is to add a ADO connection resource and an ADO Command resource. You can do this two ways. The first way is from the form.cs(design) open up the toolbox click on the Data components and drag and drop an adoConnection and adoDataSetCommand objects into the form.

The following code will be added to your code form.

private System.Data.ADO.ADODataSetCommand adoDataSetCommand1;
private System.Data.ADO.ADOConnection adoConnection1;

Next you need to make a connection to the database using the adoConnection and th adoDataSetCommand objects. The connection and command string will look like this:

string sConn = "server=localhost;uid=sa;pwd=;database=northwind";
string sCommand ="Select FirstName, LastName FROM Employees";

You then need to connect to the database. This is is done by calling a new ADODataSetCommand object using the command and connection string as the parameters.

ADODataSetCommand DSCmd = new ADODataSetCommand(sCommand, sConn);

So far the ADO.NET has look a lot like the ADO we have come to love and adore. Once we have made the database connection and have queried the Database we are ready for something new. The DataSet Object. The DataSet object. The DataSet Object is more than just an expanded RecordSet. With the DataSet Object you connect to a copy of a database table(s) or even an entire database. Also you are able to read and write to a XML files.

By calling FillDataSet() of the ADODataSetCommand you are able to load a DataSet with data from a database.

DataSet ds = new DataSet();
DSCmd.FillDataSet(ds);

Next you need to create a new DataTable and connecting it to your DataSet, by using the method Tables of the DataSet class. DataTable class has several other methods two of which are Columns and Constraints, which returns a collection of columns and constraints respectively.

DataTable dt = ds.Tables["EMPLOYEES"];

You are now ready to get the data from the database. You can use the foreach function along with a DataRow object to access the data quite easily.

foreach(DataRow dr in dt.Rows)
{
listBox1.Items.Add(dr["FirstName"] + ":" +dr["LastName"]};
}

To put the data into a grid all you need to do is to create a data grid in the design mode and then after the DSCmd.FillDataSet(ds, "Employees"); use the following code

Datagrid1.DataSource=ds.Tables["Employees"].DefaultView;

This will put the data from the DataSet directly into the Datagrid.

The Complete Code:.

namespace WindowsApplication4
{
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.WinForms;
using System.Data;
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.WinForms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components;
private System.Data.ADO.ADODataSetCommand adoDataSetCommand1;
private System.Data.ADO.ADOConnection adoConnection1;
private System.WinForms.ListBox listBox1;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
DataSet ds = new DataSet();
adoDataSetCommand1.FillDataSet(ds);
DataTable dt = ds.Tables["TREASURY"];
foreach(DataRow dr in dt.Rows)
{
listBox1.Items.Add(dr["BASE_LAST_UPDATED"] + ":" +dr["BASE_YIELD"] + " " +dr["COUPON"]);
}
}