Let's use the following simple program to demonstrate how C# uses datasets to examine the information in a database.
In this program, a dataset is created and then populated from a Microsoft Access database called EasyNomad. The information in the dataset is then displayed in a list box.
Because we are using an OLE database, we must use the System.Data.OleDb namespace.
using System.Data.OleDb;
The following line creates the connection string that specifies the provider as Microsoft.JET.OLEDB.4.0 and the data source as EasyNomad.mdb.
string connection =
"provider=Microsoft.JET.OLEDB.4.0; "
+ "data source = c:\\EasyNomad.mdb";
We create the Select statement, which determines which records will be pulled from the database to the dataset.
string command =
"Select FirstName, LastName from Customers";
The following line of code creates a new OleDbDataAdapter called DataAdapter.
OleDbDataAdapter DataAdapter =
new OleDbDataAdapter(command, connection);
The connection and command strings must be passed in to the new data adapter.
(command, connection);
The data extracted by the DataAdapter object will be fed into a new DataSet object called CustomerDataSet, which is created in this line.
// connect to EasyNomad Access Database
string connection = "Provider=Microsoft.JET.OLEDB.4.0; " + "data source = c:\\EasyNomad.mdb";
// get records from the customers table
string command = "Select FirstName, LastName from Customers";
// create the data set command object
// and the DataSet
OleDbDataAdapter DataAdapter = new OleDbDataAdapter(command, connection);
DataSet CustomerDataSet = new DataSet();
The Fill method of the DataAdapter class fills the CustomerDataSet object with the data from the Customers table in the EasyNomad database.
Let's use the list box to display the information that has been copied to the CustomerDataSet object.
This code creates a new DataTable object, which contains the information in the first table of the dataset.
You use the Tables property of the DataSet object to get the collection of tables contained in the dataset. Because we added only one table, we know it is at index 0.
The data is now transferred to the list box, by iterating through each data row in the data tables Rows collection and adding a new item to the list box.
DataSet CustomerDataSet = new DataSet ();
// fill the data set object
DataAdapter.Fill(CustomerDataSet,"Customers");
// Get the one table from the DataSet
DataTable dataTable = CustomerDataSet.Tables[0];
// for each row in the table, display the info
foreach (DataRow dataRow in dataTable.Rows)
{
ListOfCustomers.Items.Add(dataRow["FirstName"] + " " + dataRow["LastName"]);
}
You now know how to connect to an OLE DB managed provider. To connect to a SQL managed provider, you need to make a few small changes to the code.
First you use the System.Data.SqlClient namespace.
Then you need to change the connection string to specify the SQL database.
And change the data adapter to a SqlDataAdapter object.
From then on, for every class member whose name begins with "OleDb," there is a corresponding one that begins with "Sql."