Real Life SQL and .NET: Part XII

An Example with Several SQL Statements

This is the listing for the ClassMain.cs file, the main file of our little sample Console Application. A few of the tabs have been removed to try to fit all the code into the space of the pages here.

using System;
using System.Data;
using System.Data.SqlClient;
namespace Godel.ADONET_Connected
{
class ClassMain
{
static void Main(string[] args)
{
DataSet SampleDS = new DataSet( "SampleDS" );
string ConnectionString = "Data Source=localhost; InitialCatalog=Northwind; User id=sa; Password=;";
SqlConnection Connection = new SqlConnection( ConnectionString );
SqlDataAdapter SampleDA = new SqlDataAdapter( "SELECT RegionID,
RegionDescription FROM Region", Connection );
DataRow TempRow;
// now lets provide SQL statements that support the rest of the
// UPDATE, DELETE, and INSERT functionality.
// -- UPDATE --
qlCommand UpdateCommand = new SqlCommand( "UPDATE Region SET
RegionDescription = @RegionDescription WHERE RegionID =
@RegionID", Connection );
SqlParameter UpdateKeyParam = new SqlParameter( "@RegionID",
SqlDbType.Int );
UpdateKeyParam.SourceColumn = "RegionID";
UpdateKeyParam.SourceVersion = DataRowVersion.Original;
UpdateCommand.Parameters.Add( "@RegionDescription", SqlDbType.NChar,
50, "RegionDescription" );
UpdateCommand.Parameters.Add( UpdateKeyParam );
SampleDA.UpdateCommand = UpdateCommand;
// -- DELETE --
SqlCommand DeleteCommand = new SqlCommand( "DELETE Region WHERE RegionID
= @RegionID", Connection );
SqlParameter DeleteKeyParam = new SqlParameter( "@RegionID",
SqlDbType.Int );
DeleteKeyParam.SourceColumn = "RegionID";
DeleteKeyParam.SourceVersion = DataRowVersion.Original;
DeleteCommand.Parameters.Add( DeleteKeyParam );
SampleDA.DeleteCommand = DeleteCommand;
// -- INSERT --
SqlCommand InsertCommand = new SqlCommand( "INSERT INTO
Region(RegionDescription, RegionID) VALUES(@RegionDescription,
@RegionID)", Connection );
InsertCommand.Parameters.Add( "@RegionDescription", SqlDbType.NChar, 50,
"RegionDescription" );
InsertCommand.Parameters.Add( "@RegionID", SqlDbType.Int, 4,
"RegionID" );
SampleDA.InsertCommand = InsertCommand;
// now load the dataset with the results of our SQL Query.
// notice that we're not explicitly opening our conection. Our
// DataAdapter is doing all that work for us, closing it as soon as it
// completes its task.
SampleDA.Fill( SampleDS, "Region" );
// create a new row
// the Region table doesn't have an autonumbering identity,
// so we have to supply our own region ID.
DataRow NewRow;
NewRow = SampleDS.Tables["Region"].NewRow();
NewRow["RegionDescription"] = "Central";
NewRow["RegionID"] = 5;
Console.WriteLine("New Row Created using NewRow(), RowState is: {0}",NewRow.RowState );
SampleDS.Tables["Region"].Rows.Add( NewRow );
Console.WriteLine("New Row Added to Table RowState is: {0}",
NewRow.RowState );
// modify the first row
TempRow = SampleDS.Tables["Region"].Rows[0];
Console.WriteLine("Modifying First Row, Pre-Change State is: {0}",
TempRow.RowState );
TempRow["RegionDescription"] = "Reeeeeaalllly Far East";
Console.WriteLine("Modifying First Row, Post-Change State is: {0}",
TempRow.RowState );
// call the update method to save the new row and update the first
Console.WriteLine("Calling Update() to Commit New Row and First Row
Change.");
SampleDA.Update( SampleDS, "Region" );
// delete the second row
Console.WriteLine("Deleting Our New Row, Post-Delete State is: {0}",
NewRow.RowState );
NewRow.Delete();
Console.WriteLine("Deleting Our New Row, Post-Delete State is: {0}",
NewRow.RowState );
// now call the update method.
Console.WriteLine("Calling Update() - this will post ALL of our changes,
not just the update.");
SampleDA.Update( SampleDS, "Region" );
Console.WriteLine
("Region Table after Update()\n------------------------------");
foreach (DataRow tRow in SampleDS.Tables["Region"].Rows )
{
Console.WriteLine( tRow["RegionDescription"] );
SampleDS.Dispose();SampleDA. Dispose ();
}
}
}

There is a way to get VS to build all these commands for us, but it will only work if VS is connected directly to the source to interrogate it. If we were working on an n-tier solution where VS only sees derived classes, we'd have to do this manually anyway. It's worth noting here than when creating commands to commit changes for the DataAdapter, it is important to indicate the source column. You can do that by setting the SourceColumn property on the Parameter object, or by passing it as the argument immediately following the parameter's data type size.

For example, the parameter, @RegionDescription, when the DataAdapter attempted to invoke the Update command, couldn't decipher on its own that the value to be supplied for @RegionDescription was the RegionDescription column. By supplying the SourceColumn to the Parameters.Add () method on our own, we guarantee that the adapter will supply the right column values for the right arguments. The other thing to keep in mind in the code, both above and below is that instead of supplying raw SQL statements, we could easily be supplying stored procedures. For example, if we wanted to use a stored procedure for the above UpdateCommand, we would change the first line of code to the following two lines:

SqlCommand UpdateCommand =new SqlCommand("sp_UpdateRegion",Connection );
UpdateCommand.CommandType = CommandType.StoredProcedure;

Using this particular construct, we can create incredible amounts of functionality with very little work on our part and very few lines of code. After going through the source code above, you can take a look at what the output of the example produces. It is especially interesting to see how the RowState property changes with each line of code, and that it changes implicitly without the programmer having to keep track of changes on his/her own.

The code creates a new region called "Southern". Then it modifies the first region to change its name to "Reeeeaalllly Far East". Then the Update () method is called to commit both the new row, and the change to the name of the first region. After that, the code deletes the "Southern" region simply by calling the Delete () method on the DataRow object, and then Update () is called again to commit that change. If you do not believe what is going on in this code list, you can always add a breakpoint to this code immediately before the second Update () that deletes the new row and go and verify that it actually has been inserted into the database.

Summary

In this article, we first had a quick remainder and some additional information about ADO.NET data providers. After this, we looked at the basic principals of data binding in .NET and saw how the power of the .NET controls can work for us to create a very productive environment. We saw how simple binding works for single data items and single controls. We then learned how complex binding works for the DataGrid both in isolation with a locally created data table and using a full database through ADO.NET.

Finally, we looked at the more efficient and different methods, controls about data binding. We also talked about DataView, DataViewManager, DataMember for clear understand data binding, ADO.NET and .NET framework relationship. The next article, we will look inside data binding, bound term. We will have some more detailed information and source code.