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.
- 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.
- 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.
oleDbDeleteCommand2.CommandText = strSQL;
oleDbDataAdapter1.DeleteCommand = oleDbDeleteCommand2;
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.
