Intermediate OLE DB Consumer with C#

• Download source files - 76 Kb

Introduction

This is the second of the C# articles looking at OLE DB. This article does exactly what the second C++ article does "Intermediate OLE DB Consumer in C++" with the exception that the C# implementation encourages the use of separate individual Command objects the data is then accessed through the OleDbReader class that inherits from IAccessor which from its behavior I would say works generically in the same way that a CDynamicAccessor would in C++.

The Demo code was written on Windows 2000 with Dev Studio 7 beta 2

Using an Adapter

The C# implementation accesses the database through an OleDataAdapter. This is accessed from the tool bar menu and it asks you to select a connection that has been used before or you can use the wizard to specify a new connection. It generates six private members in your class.

private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
        

The OleDbDataAdapter variable is not really used by the code as all the necessary commands are carried out using the OleDbCommands that have been generated by the wizard. It is essentially the bridge between the code and the data. The oleDbDataAdapter1 member has references set to the Select, Insert, Update and the Delete OleDbCommands so that any commands that are executed can be channeled through to the database correctly. This is why in the code examples you will see the line.

oleDbDataAdapter1.DeleteCommand = oleDbDeleteCommand2;
        

This changes the Delete Command that the wizard has set up to a new one that has been set up by the code.

The Default Code

One thing that should be specified straight away is the idea that the default code generated by the wizard should be viewed more as a guideline than as a definitive way of working. The reason for this is that the default code is in my opinion harder to use than it is for the programmer to specify a Command variable of there own. There are two major things to be wary of when looking at the default code.

1. The Following is the generated code for the Insert Command.

   this.oleDbInsertCommand1.CommandText = 
       "INSERT INTO Books([Author First Name], [Author Last Name], Category, ID, price," +
       " Title) VALUES (?, ?, ?, ?, ?, ?); SELECT [Author First Name], [Author Last Name]," +
       " Category, ID, price, Title FROM Books WHERE (ID = ?)";
           

   The first problem if we ignore the row of question marks for a while is the Select statement immediately following the Insert statement. Not only is it the case that if you insert a row into a database that you don't necessarily want to look at only the inserted row afterwards but not all databases will take this syntax. Access databases are an example of this in that if you use the wizard generated code to try and insert or delete something the code will throw an exception that basically informs you that there is further text after the end of the SQL statement.

2. The second problem, and to a certain extent this is understandable in that the wizard when generating the code has to be written to generate code for any database table that you choose, is the generated codes insistence on parameterized queries. I have nothing against parameterized queries as such but in this case using them just makes things more complicated than they need to be.

   For those that don't know the idea of a parameterized query is that you can set up an SQL statement and put question marks within the statement as place holders for values that you will fill out later. Taking the generated Update Command as our example gives us the following SQL code,

   this.oleDbUpdateCommand1.CommandText = 
       @"UPDATE Books SET [Author First Name] = ?, [Author Last Name] = ?, Category = ?," + 
       @"ID = ?, price = ?, Title = ? WHERE (ID = ?) AND ([Author First Name] = ?) AND " +
       @"([Author Last Name] = ?) AND (Category = ?) AND (Title = ?) AND (price = ?); " +
       "SELECT [Author First Name], [Author Last Name], Category, ID, price, Title " +
       "FROM Books WHERE (ID = ?)";

   This is the default Command generated for the code to Update a record. For each question mark in this command a placeholder must be set so that it can be filled later. This is done through the use of,

   this.oleDbUpdateCommand1.Parameters.Add(
       new System.Data.OleDb.OleDbParameter("Author_First_Name", 
                                                 System.Data.OleDb.OleDbType.Char, 50, 
                                                 System.Data.ParameterDirection.Input, 
                                                 false, 
                                                 ((System.Byte)(0)), 
                                                 ((System.Byte)(0)), 
                                                 "Author First Name", 
                                                 System.Data.DataRowVersion.Current, 
                                                 null)
                                           );
           

   Each Placeholder is an OleDbParameter held in the Parameters member of the collection class. The Parameters member is a variable of type OleDbParameterCollection. The parameter is placed into the connection with all the information that is needed about the parameter such as its name and size and in what direction it will be written. This has to be done for every single question mark that is in the SQL statement. Rather than write the code to fill all of them and because I wanted to demonstrate how to fill out the parameters in a generated query I edited the SQL statement to,

   this.oleDbUpdateCommand1.CommandText = "UPDATE Books SET [Author First Name] = ?, " +
                                          "[Author Last Name] = ?, Category = ?, " +
                                          "price = ?, Title = ? WHERE (ID = ?)"

   This does exactly what I need it to do in that it Updates all the variables that you can update. The ID value cannot be updated because it is an auto number controlled by the database. It means that in order to do an update all I have to do is fill out the values of the row and tell the database which ID or row it is that I want to update. The code to fill out the parameters is,

   OleDbParameterCollection colParams = oleDbUpdateCommand1.Parameters;
   
   try
   {
       IEnumerator enumParams = colParams.GetEnumerator();
       
       enumParams.MoveNext();
       OleDbParameter param = ( OleDbParameter )enumParams.Current;
       if( param.ParameterName == "Author_First_Name" )
           param.Value = m_AuthorFirstName.Text;
   
           

   This fills out the first parameter. It starts by getting the OleDbParameterCollection from the OleDbUpdateCommand1 variable and from that it gets the IEnumerator interface from the collection which allows it to move through the collection one item at a time. As with the OleDbDataReader that we will encounter later the enumerator can move forwards only and through the collection and when it is first retrieved it is at a position before the beginning of the collection which is the reason for the call to MoveNext before it gets the OleDbParameter from the enumerator. Once it has the OleDbParameter the code checks that it has the ParameterName that we are expecting the first parameter to have and if it is the OleDbParameter makes its Object member value equal to it. As all types inherit from Object in C# this is not a problem. The biggest possibility for error here lies in the code knowing what type the object is before it writes it to the database. This is taken care of by the generated codes setting up if the OleDbParameter in that it stores not only the objects type but if the object happens to be a string as here it will store its maximum length as well.

   Once the parameters have been filled in the next thing you must do is close any OleDbReaders that you currently have accessing the table that you want to update. If you forget to do this the code will throw an exception to remind you that it's a good idea. The code then calls

   int nAffected = oleDbUpdateCommand1.ExecuteNonQuery();

   which is the function to be called when you are not expecting a returning result from the function

   When the query has been executed the OleDbDataReader needs to be reset,

   oleDataReader.Close();
   
   oleDataReader = oleDbSelectCommand1.ExecuteReader();
   
   for( int i=0; i<nRecordCount-2; i 
   {
       oleDataReader.Read();
   }
   
   MoveNext();
   
   nRecordCount -=1; 
           

   The above shows the contents of the Prev_Click method which is a bit of a fudge but allows the code to maintain an appearance of consistency by keeping track of the number of the record that the OleDbDataReader is currently up to and then repositioning the rows when something is changed through the use of the Update, Delete and Insert buttons or even the View Authors and View Categories buttons.

Using Custom Queries

I stated above how the generated code should be viewed as a template for what you want to do rather than as a strict set of rules and showed how to change the generated code around a little to make the task easier. The next step in this process is of course to make it even easier the way I do this is to build the SQL statements on the fly and then execute them. Both the Insert_Click and the Delete_Click functions do this.

string strSQL = "DELETE * FROM Books WHERE ID = ";

strSQL += m_ID.Text;

The Delete_Click method simply deletes everything with the passed ID, while the Insert_Click function has to ensure that the field names within the row are correct. Note that there is no check on the length of the text that has been added here. If the string is too big there will be an OleDbException just waiting to tell you about it.

/// set the sql in the command
oleDbDeleteCommand2.CommandText = strSQL;

oleDbDataAdapter1.DeleteCommand = oleDbDeleteCommand2;
/// no connection exception if you dont do this
oleDbDeleteCommand2.Connection = oleDbConnection1;

oleDbDeleteCommand2.ExecuteNonQuery();

oleDataReader = oleDbSelectCommand1.ExecuteReader();
        

The code to carry out the query is fairly simple, first of all the SQL string that has been set up is passed to the CommandText Member of the OleDBCommand that is being used. In this case the OleDbDeleteCommand2. Then the Command is set as the OleDbDataAdapter members default DeleteCommand. Finally the Connection that we are using is set to be the Executing Commands connection.

Once this is done all there is left to do is execute the command and then reset the reader so that everything is as it was.

And Finally

One thing to note is that C# will allow you to add a variable with an important function name and then refuse to compile because it can't work out what you are trying to do. I crossed this one adding the button called update which clashed with the System.Update method that the button class inherits. This meant I had to go through and change all references to the Update variable to m_bUpdate using the MFC style syntax to distinguish it.

Right now that we've covered the basics we can start to get down to the serious stuff. Although I have to admit that I am expecting no major problems from C# in this regard because it has managed to cope with everything quite well so far. This is due mainly to its compatibility with the Component Object Model that allows it access to the powerful set of interfaces that the newer versions of windows are built on.