How To Use A Database In Your Program Part II

[Curtis Rutland]

How To Use A Database In Your Program Part II

This article is intended to extend Frinny’s excellent article: How to Use a Database in Your Program. Frinny’s article defines the basic concepts of using databases very well and is prerequisite reading for this article.

Frinny’s article explains how to use a SQL Server in your program, but there are other databases as well. Some of them provide .NET connectors, but for those that don’t, or if you don't want to use them, there are two protocols that you can use to connect to them: ODBC and Ole DB. Note that the objects explained in the following section are interchangeable with their counterparts; they have the same methods and behave the same way.

ODBC
Namespace: System.Data.Odbc
ODBC stands for “Open Database Connectivity.” This protocol is the most generic. ODBC relies on DSNs: “Data Source Names.” These must be set up on the machine which the application will be run on: for ASP.NET, the server; for Windows apps, each client computer. DSNs can be set through the registry, but this is outside the scope of this article. The ODBC drivers are typically installed when the database or its management tools are installed. For example, the IBMDA400 (for connecting to an AS400/Series I) driver is installed when you install IBM’s Client Access. The drivers are required to add the DSN.
Useful objects: OdbcConnection, OdbcCommand, OdbcDataAdapter, OdbcCommandBuilder

Ole DB
Namespace: System.Data.OleDb
Ole DB stands for “Object Linking and Embedding, Database.” This protocol tends to be less generic than ODBC, because the connection string specifies the provider to use. This provider must be installed on the system that the application runs on, but no DSNs or external connections are required. One of the “pros” to using Ole DB is that it is easier to dynamically define a connection. For example, you can connect to an Excel spreadsheet using either ODBC or Ole DB, but ODBC requires a pre-defined DSN, or you must edit the registry to add one. You can simply change the “Data Source” property of the connection string if you use Ole DB.
Useful objects: OleDbConnection, OleDbCommand, OleDbDataAdapter, OleDbCommandBuilder

Note: Please note that from here on out I will use the objects from the System.Data.OleDb namespace for examples. Remember that they are fully interchangeable with their counterparts. For example, a SqlConnection behaves the same as an OleDbConnection and an OdbcConnection, so you can swap them out when you use different protocols.

DataAdapters
Namespace: System.Data (for DataSet and DataTable)
Frinny explained how to use a OleDbCommand to make a OleDbDataReader. Another method, slightly more automated, is to use an OleDbDataAdapter to fill a DataTable. OleDbDataAdapters contain the Fill(dataSetInstance, “tableName”) method, which will use your SELECT statement to fill a DataTable in a DataSet. This DataTable can be directly bound to controls such as DataGrids and ListBoxes. You can also use OleDbCommandBuilders to create INSERT, UPDATE, and DELETE commands based on your SELECT command. When the data in the DataTable has been modified and you want to commit the changes, you can use the OleDbDataAdapter’s Update() method to save the changes back to the database.

//C#
//set up the connection string
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;";
//set up the select statement
string SelectCommand = "SELECT * FROM test";
//set up the connection object using the conn string
OleDbConnection Connection = new OleDbConnection(ConnectionString);
//set up the data adapter using the select statement and the connection object
OleDbDataAdapter Adapter = new OleDbDataAdapter(SelectCommand, Connection);
//a new empty dataset
DataSet ds = new DataSet();
//fill the dataset with a new datatable of all the results
Adapter.Fill(ds, "test");//string is the DataTable name, can be anything
//now, let "Table" point to the datatable with our results
DataTable Table = ds.Tables["test"];
//and you can use the table as needed
dataGridView1.DataSource = Table;
 

'VB.NET
'set up the connection string
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;"
'set up the select command
Dim SelectCommand As String
SelectCommand = "SELECT * FROM test"
'set up the connection using the conn string
Dim Connection As New OleDbConnection(ConnectionString)
'set up the adapter using select command and connection object
Dim Adapter As New OleDbDataAdapter(SelectCommand, Connection)
'a new blank data set
Dim ds As New DataSet()
'fill the dataset with a datatable of our results
Adapter.Fill(ds, "test") 'test is the datatable name, can be anything
'//now, let "Table" point to the datatable with our results
Dim Table As DataTable
Table = ds.Tables("test")]
'use the data as you see fit
DataGridView1.DataSource = Table
 

Now, Table is pointing to a DataTable that contains all the data retrieved from your query. You can also modify this data, and easily reflect your changes back to the database.

//C#
//set up the command builder using the data adapter
OleDbCommandBuilder Builder = new OleDbCommandBuilder(Adapter);
//use the builder to create update, insert, and delete commands
Adapter.UpdateCommand = Builder.GetUpdateCommand();
Adapter.InsertCommand = Builder.GetInsertCommand();
Adapter.DeleteCommand = Builder.GetDeleteCommand();
 
//manipulate the data in the table
Table.Rows[0]["FirstName"] = "Jimmy";
 
//commit changes of table "test" in the dataset back to the database 
Adapter.Update(ds, "test");
 

'VB.NET
'set up the command builder using the data adapter
Dim Builder As New OleDbCommandBuilder(Adapter)
'use the builder to generate update, insert, and delete commands
Adapter.UpdateCommand = Builder.GetUpdateCommand()
Adapter.InsertCommand = Builder.GetInsertCommand()
Adapter.DeleteCommand = Builder.GetDeleteCommand()
 
'manipulate the data in the table
Table.Rows(0)("FirstName") = "Jimmy"
 
'commit changes of table "test" in the dataset back to the database 
Adapter.Update(ds, "test")
 

Note: Your data will be loaded into memory, so don’t use this method if you have very large datasets.

ExecuteScalar
Namespace: System.Data.<protocol> (OleDb,Odbc,SqlClient)
Frinny showed you how to execute a DataReader and a “NonQuery” (UPDATE/INSERT/DELETE statement). There is one more useful method to show: ExecuteScalar . ExecuteScalar() returns the first column of the first row of the query as an Object. This is useful when you want to select one value out of your database. This is especially useful for scalar functions like count, max, and sum, but isn’t required to be a scalar.

//C#
//set up select scalar command
string ScalarQuery = "SELECT COUNT(*) FROM test";
//set up command object using query and previous connection object
OleDbCommand Command = new OleDbCommand(ScalarQuery, Connection);
//a variable to hold our results:
int count = -1;
//attempt the query
try
{
    //open the connection
    Command.Connection.Open();
    //execute the query and assign the result to "count"
    count = Convert.ToInt32(Command.ExecuteScalar());
    //close the connection
    Command.Connection.Close();
}
//if it didn't work...
catch (OleDbException exc)
{
    MessageBox.Show(String.Format("OleDb Error: {0}", exc.Message));
}
finally
{
    //always double check to make sure your connection is closed
    if (Command.Connection.State != ConnectionState.Closed)
        Command.Connection.Close();
}
//show the results
MessageBox.Show(String.Format("Scalar Result: {0}", count));
 

'VB.NET
'set up select scalar command
Dim ScalarQuery As String
ScalarQuery = "SELECT COUNT(*) FROM test"
'set up command object using query and previous connection object
Dim Command As New OleDbCommand(ScalarQuery, Connection)
'a variable to hold our results:
Dim count As Integer
count = -1
'attempt the query
Try
    'open the connection
    Command.Connection.Open()
    'execute the query and store the result in count
    count = Convert.ToInt32(Command.ExecuteScalar())
    'close the connection
    Command.Connection.Close()
'if it fails...
Catch ex As OleDbException
    MessageBox.Show(String.Format("OleDb Error: {0}", ex.Message))
Finally
    'always make sure your connection is closed
    If Command.Connection.State <> ConnectionState.Closed Then
        Command.Connection.Close()
    End If
End Try
'display results:
MessageBox.Show(String.Format("Scalar Result: {0}", count))
 

[Curtis Rutland]

Updated to include comments in the code.