- What Is a Database?
- Getting Data Out of Databases
- Kinds of Databases
- ODBC
- Database Structure
- Using ADO.NET
- Adding Rows to Database Tables Using ADO.NET
- Building the Façade Classes
- Making the ADO.NET Façade
- Creating Classes for Each Table
- Building the Price Table
- Loading the Database Tables
- The Final Application
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using ADO.NET
ADO.NET as implemented in C# consists of a fairly large variety of interrelated objects. Since the operations we want to perform are still the same relatively simple ones, the Façade pattern will be an ideal way to manage them.
OleDbConnection This object represents the actual connection to the database. You can keep an instance of this class available but open and close the connection as needed. You must specifically close it when you are done, before it is garbage collected.
OleDbCommand This class represents an SQL command you send to the database, which may or may not return results.
OleDbDataAdapter Provides a bridge for moving data between a database and a local DataSet. You can specify an OleDbCommand, a Dataset, and a connection.
DataSet A representation of one or more database tables or results from a query on your local machine.
DataTable A single data table from a database or query.
DataRow A single row in a DataTable.
Connecting to a Database
To connect to a database, you specify a connection string in the constructor for the database you want to use. For example, for an Access database, your connection string would be the following.
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + dbName;
The following makes the actual connection.
OleDbConnection conn =
new OleDbConnection(connectionString);
You actually open that connection by calling the open method. To make sure that you don't reopen an already open connection, you can check its state first.
private void openConnection() {
if (conn.State == ConnectionState.Closed){
conn.Open ();
}
}
Reading Data from a Database Table
To read data in from a database table, you create an ADOCommand with the appropriate Select statement and connection.
public DataTable openTable (string tableName) {
OleDbDataAdapter adapter = new OleDbDataAdapter ();
DataTable dtable = null;
string query = "Select * from " + tableName;
adapter.SelectCommand = new OleDbCommand (query, conn);
Then you create a dataset object into which to put the results.
DataSet dset = new DataSet ("mydata");
Then you simply tell the command object to use the connection to fill the dataset. You must specify the name of the table to fill in the FillDataSet method, as is shown here.
try {
openConnection();
adapter.Fill (dset);
}
catch(Exception e) {
Console.WriteLine (e.Message );
}
The dataset then contains at least one table, and you can obtain it by index or by name and examine its contents.
//get the table from the dataset dtable = dset.Tables [0];
Executing a Query
Executing a Select query is exactly identical to the preceding code, except the query can be an SQL Select statement of any complexity. Here we show the steps wrapped in a try block in case there are SQL or other database errors.
public DataTable openQuery(string query) {
OleDbDataAdapter dsCmd = new OleDbDataAdapter ();
DataSet dset = new DataSet ();
//create a dataset
DataTable dtable = null; //declare a data table
try {
//create the command
dsCmd.SelectCommand =
new OleDbCommand(query, conn);
//open the connection
openConnection();
//fill the dataset
dsCmd.Fill(dset, "mine");
//get the table
dtable = dset.Tables[0];
//always close it
closeConnection();
//and return it
return dtable;
catch (Exception e) {
Console.WriteLine (e.Message);
return null;
}
}
Deleting the Contents of a Table
You can delete the contents of a table using the Delete * from Table SQL statement. However, since this is not a Select command, and there is no local table to bridge to, you can simply use the ExecuteNonQuery method of the OleDb Command object.
public void delete() {
//deletes entire table
conn = db.getConnection();
openConn();
if (conn.State == ConnectionState.Open ) {
OleDbCommand adcmd =
new OleDbCommand("Delete * from " + tableName, conn);
try{
adcmd.ExecuteNonQuery();
closeConn();
}
catch (Exception e) {
Console.WriteLine (e.Message);
}
}