By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How To Use A Database In Your Program Part II

Curtis Rutland
Expert 2.5K+
P: 3,256
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.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up the connection string
  3. string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;";
  4. //set up the select statement
  5. string SelectCommand = "SELECT * FROM test";
  6. //set up the connection object using the conn string
  7. OleDbConnection Connection = new OleDbConnection(ConnectionString);
  8. //set up the data adapter using the select statement and the connection object
  9. OleDbDataAdapter Adapter = new OleDbDataAdapter(SelectCommand, Connection);
  10. //a new empty dataset
  11. DataSet ds = new DataSet();
  12. //fill the dataset with a new datatable of all the results
  13. Adapter.Fill(ds, "test");//string is the DataTable name, can be anything
  14. //now, let "Table" point to the datatable with our results
  15. DataTable Table = ds.Tables["test"];
  16. //and you can use the table as needed
  17. dataGridView1.DataSource = Table;
  18.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up the connection string
  3. Dim ConnectionString As String
  4. ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;"
  5. 'set up the select command
  6. Dim SelectCommand As String
  7. SelectCommand = "SELECT * FROM test"
  8. 'set up the connection using the conn string
  9. Dim Connection As New OleDbConnection(ConnectionString)
  10. 'set up the adapter using select command and connection object
  11. Dim Adapter As New OleDbDataAdapter(SelectCommand, Connection)
  12. 'a new blank data set
  13. Dim ds As New DataSet()
  14. 'fill the dataset with a datatable of our results
  15. Adapter.Fill(ds, "test") 'test is the datatable name, can be anything
  16. '//now, let "Table" point to the datatable with our results
  17. Dim Table As DataTable
  18. Table = ds.Tables("test")]
  19. 'use the data as you see fit
  20. DataGridView1.DataSource = Table
  21.  
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.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up the command builder using the data adapter
  3. OleDbCommandBuilder Builder = new OleDbCommandBuilder(Adapter);
  4. //use the builder to create update, insert, and delete commands
  5. Adapter.UpdateCommand = Builder.GetUpdateCommand();
  6. Adapter.InsertCommand = Builder.GetInsertCommand();
  7. Adapter.DeleteCommand = Builder.GetDeleteCommand();
  8.  
  9. //manipulate the data in the table
  10. Table.Rows[0]["FirstName"] = "Jimmy";
  11.  
  12. //commit changes of table "test" in the dataset back to the database 
  13. Adapter.Update(ds, "test");
  14.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up the command builder using the data adapter
  3. Dim Builder As New OleDbCommandBuilder(Adapter)
  4. 'use the builder to generate update, insert, and delete commands
  5. Adapter.UpdateCommand = Builder.GetUpdateCommand()
  6. Adapter.InsertCommand = Builder.GetInsertCommand()
  7. Adapter.DeleteCommand = Builder.GetDeleteCommand()
  8.  
  9. 'manipulate the data in the table
  10. Table.Rows(0)("FirstName") = "Jimmy"
  11.  
  12. 'commit changes of table "test" in the dataset back to the database 
  13. Adapter.Update(ds, "test")
  14.  
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.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up select scalar command
  3. string ScalarQuery = "SELECT COUNT(*) FROM test";
  4. //set up command object using query and previous connection object
  5. OleDbCommand Command = new OleDbCommand(ScalarQuery, Connection);
  6. //a variable to hold our results:
  7. int count = -1;
  8. //attempt the query
  9. try
  10. {
  11.     //open the connection
  12.     Command.Connection.Open();
  13.     //execute the query and assign the result to "count"
  14.     count = Convert.ToInt32(Command.ExecuteScalar());
  15.     //close the connection
  16.     Command.Connection.Close();
  17. }
  18. //if it didn't work...
  19. catch (OleDbException exc)
  20. {
  21.     MessageBox.Show(String.Format("OleDb Error: {0}", exc.Message));
  22. }
  23. finally
  24. {
  25.     //always double check to make sure your connection is closed
  26.     if (Command.Connection.State != ConnectionState.Closed)
  27.         Command.Connection.Close();
  28. }
  29. //show the results
  30. MessageBox.Show(String.Format("Scalar Result: {0}", count));
  31.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up select scalar command
  3. Dim ScalarQuery As String
  4. ScalarQuery = "SELECT COUNT(*) FROM test"
  5. 'set up command object using query and previous connection object
  6. Dim Command As New OleDbCommand(ScalarQuery, Connection)
  7. 'a variable to hold our results:
  8. Dim count As Integer
  9. count = -1
  10. 'attempt the query
  11. Try
  12.     'open the connection
  13.     Command.Connection.Open()
  14.     'execute the query and store the result in count
  15.     count = Convert.ToInt32(Command.ExecuteScalar())
  16.     'close the connection
  17.     Command.Connection.Close()
  18. 'if it fails...
  19. Catch ex As OleDbException
  20.     MessageBox.Show(String.Format("OleDb Error: {0}", ex.Message))
  21. Finally
  22.     'always make sure your connection is closed
  23.     If Command.Connection.State <> ConnectionState.Closed Then
  24.         Command.Connection.Close()
  25.     End If
  26. End Try
  27. 'display results:
  28. MessageBox.Show(String.Format("Scalar Result: {0}", count))
  29.  
Jul 2 '08 #1
Share this Article
Share on Google+
1 Comment


Curtis Rutland
Expert 2.5K+
P: 3,256
Updated to include comments in the code.
Jul 8 '08 #2