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.Odb c
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, OdbcCommandBuil der
Ole DB
Namespace: System.Data.Ole Db
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, OleDbDataAdapte r, OleDbCommandBui lder
Note: Please note that from here on out I will use the objects from the System.Data.Ole Db 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 OleDbDataAdapte r to fill a DataTable. OleDbDataAdapte rs contain the Fill(dataSetIns tance, “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 OleDbCommandBui lders 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 OleDbDataAdapte r’s Update() method to save the changes back to the database.
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.
Note: Your data will be loaded into memory, so don’t use this method if you have very large datasets.
ExecuteScalar
Namespace: System.Data.<pr otocol> (OleDb,Odbc,Sql Client)
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.
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.Odb c
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, OdbcCommandBuil der
Ole DB
Namespace: System.Data.Ole Db
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, OleDbDataAdapte r, OleDbCommandBui lder
Note: Please note that from here on out I will use the objects from the System.Data.Ole Db 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 OleDbDataAdapte r to fill a DataTable. OleDbDataAdapte rs contain the Fill(dataSetIns tance, “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 OleDbCommandBui lders 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 OleDbDataAdapte r’s Update() method to save the changes back to the database.
Code:
//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;
Code:
'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
Code:
//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");
Code:
'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")
ExecuteScalar
Namespace: System.Data.<pr otocol> (OleDb,Odbc,Sql Client)
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.
Code:
//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));
Code:
'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))
Comment