Abstract
Visual Studio.NET's code generation features serve the purpose of creating trivial applications very well but when it comes time to create a more complicated application, a good programmer needs to know how to work with ADO.NET DataAdapters at the code level.
When should I write my own vs. have one generated?
Reasons To Use the Code Generator
- You need to create a quick and dirty solution.
Reasons To Write Your Own DataAdapter
- You need to be able to put your code into a DLL.
- You need clean, maintainable code.
- You need to be able to insert data into a table with an Autonumber primary key.
- You need to change the behaviour of the generated code in any way.
The situation with needing to update the Autonumbered primary key is very important and happens in many situations. The generated code will require you to set the value of your Autonumbered column resulting in an error when the statement is executed.
The Basic Structure Of A DataAdapter
The DataAdapter object itself is fairly simple. The 4 properties that really matter are: SelectCommand, UpdateCommand, InsertCommand, and the DeleteCommand. Each of these accepts an OleDbCommand as a parameter. Once set, these properties perform the needed operations on a database based upon the requirements placed on them.
The OleDbCommand object
The OleDbCommand object models a single SQL operation that can be performed on the database. Take a look at the code example below.
OleDbCommand uCmd = new OleDbCommand("UPDATE Property SET Name=? WHERE PropertyID=?", dbConnection);
uCmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("Name", System.Data.OleDb.OleDbType.Char,50);
uCmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("PropertyID", System.Data.OleDb.OleDbType.Numeric,0);
Take a look at lines 1 and 2. The SQL statement has '?' marks placed inside the statement to show where data should be dynamically inserted into the statement. The dbConnection is of course the connection to your database. (NOTE: Learning how to create database connections is outside the scope of this article.)
On lines 3 to 6 we are defining the data type that will be mapped into those question marks. We do this by adding a new OleDbParameter to the Parameters collection of our OleDbCommand object. The values passed to the OleDbParameter object are below (in order):
Column Name = The name of the parameter as you would like it set. It is generally best to stick with the column name.
Data Type = This is the OleDbType enumeration. There are a number of options here that are database specific, but in most cases you can get by quite well with Char, Numeric, and Date
Size = This is the size of the data type. For Char types, it is the number of characters, for numeric fields, leave it at 0 and it will get the size from the database driver you're using automagically
There are other constructors for OleDbParameter that include much more information but the additional data is in general not needed and will only complicate your code.
To call this command, take a look at the code below:
uCmd.Parameters["Name"]="David's Trailer Court";
uCmd.Parameters["PropertyID"]=1;
uCmd.ExecuteNonQuery();
One of the excellent things about using a OleDbCommand with proper parameters instead of generating "SQL Strings" with your data inside is the handling of special characters such as the apostrophe('). This command will execute against the database without an error.
Plugging your OleDbCommands into the OleDbDataAdapter
The code used to create our DataAdapter is below (Except of course the code required to acquire a dbConnection):
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT PropertyID, Name FROM Property", dbConnection);
OleDbCommand iCmd = new OleDbCommand("INSERT INTO Property (Name) VALUES (?)", dbConnection);
iCmd.Parameters.Add(new OleDbParameter("Name", OleDbType.Char, 50));
myAdapter.InsertCommand=iCmd;
OleDbCommand uCmd = new OleDbCommand("UPDATE Property SET Name=? WHERE PropertyID=?", dbConnection);
uCmd.Parameters.Add(new OleDbParameter("Name", OleDbType.Char, 50));
uCmd.Parameters.Add(new OleDbParameter("PropertyID", OleDbType.Numeric, 0));
myAdapter.UpdateCommand=uCmd;
OleDbCommand dCmd = new OleDbCommand("DELETE FROM Property WHERE PropertyID=?", dbConnection);
dCmd.Parameters.Add(new OleDbParameter("PropertyID", OleDbType.Numeric, 0));
myAdapter.DeleteCommand=dCmd;
Our table structure for this database is very simple:
Table:Property
PropertyID=Numeric
Name=Char(50)
Notice we placed our SELECT sql statement in the constructor of our adapter? This is the same as declaring an OleDbCommand and placing the select statement inside that then assigning it to myAdapter.SelectCommand. Now we have a complete OleDbDataAdapter that can be used for any data binding purpose from DataSets to DataGrids.
Conclusion
Visual Studio.NET's drag and drop code generation is very powerful at creating trivial applications quickly, but when it comes time to create maintainable code that works with a scalable database design, a custom DataAdapter must be created.