ADO.NET 2.0

Sunday, August 26, 2007 1:10:12 PM (Mountain Standard Time, UTC-07:00)

Here it is my final assignment for CMPP298 - Database Programming ADO.NET. Assignment is as follows:

"Create a Windows Forms application to support data maintenance (CRUD functionality) of Invoices per Vendor in the Payables database.
Make use of the Connection, Transaction, Command, and DataReader objects of the ADO.NET SQLClient Provider,
not DataSets, DataTables, DataAdapters or the Configuration Wizard.

The user has to select a Vendor and then view, add, edit or delete Invoice records for the selected vendor.
Changes have to be saved before the user can select another vendor.

Feel free to make use of a combination of UI controls such as Grids, TextBoxes, ComboBoxes, etc. "

The focus of the assignment was on ADO.NET 2.0. So my implementation of validation, UI, and domain is rather weak. I wanted to focus my attention on learning the core of ADO.NET 2.0 by learning more about...

• IDbConnection: The interface the the abstract DbConnection type implements that all connection sub classes inherit from for each ado.net provider. (OleDb, Odbc, Oracle, SQL Server, MySQL)
• IDbCommand: The interface that the abstract DbCommand type implements that all command objects inherit from.
• DataTable: An in memory type used to store data in a tabular format.
• IDataReader: A forward only, read-only type that allows you to iterate through  a result set.
• IDataParameter: This type represents a parameter for a IDbCommand object.
• DbProviderFactories:  Provides a factory method to return a DbProviderFactory abstract factory with methods such as CreateConnection.

IDbConnection

The IDbConnection interface demands a set of very simple methods and properties. I found "CreateCommand()" and "Open()" to be the most useful. Working against the interface as opposed to the concrete implementation simplified learning for myself. I was able to focus on the most important traits and behaviors that all Connection types share.

The "CreateCommand()" method returns a "IDbCommand" type that has it's connection property set to the the IDbConnection type that created it. Further reducing the amount of code needed to be written.

In my assignment I created and ConnectionFactory that has a single factory method that returns IDbConnection types. This separates where the connection string settings are retrieved from, from where the connection object is actually used.

            using ( IDbConnection connection = _connectionFactory.Create( ) ) {
                IDbCommand command = connection.CreateCommand( );
                command.CommandText = sqlQuery;
                connection.Open( );
                IDataReader reader = command.ExecuteReader( );
                DataTable table = new DataTable( );
                table.Load( reader );
                return table;
            }

You might have also noticed that IDbConnection also implements the IDisposable interface which allows me to use the type in a "using" block, which implicitly calls the Dispose method on the type. The Dispose method will then close the connection and clean up any other unmanaged resources.

 IDbCommand

Types that implement the IDbCommand interface are used to execute "commands" against a database. This uses the Command pattern with 3 types of execute methods.

• ExecuteReader: will execute a SQL command against the data source and return and IDataReader type that allows you to read through a result set.
• ExecuteNonQuery: will execute a SQL command against the data source and returns the number of rows affected by the command.
• ExecuteScalar: will execute a SQL command against the data source and returns the value in the first column of the first row.

The CommandText property is either the name of the stored procedure or the raw SQL to execute against the data source.

                IDbCommand command = connection.CreateCommand( );
                connection.Open( );
                command.CommandText = query;
                command.ExecuteNonQuery( );

DataTable

A DataTable is an in memory container for tabular data. It has a method named "Load()" that takes in an IDataReader type and will load the data table with the entire result set from the IDataReader. The overloaded Load method looks like this in Lutz Reflector:

A client usage of the Load method looks like this:

                IDataReader reader = command.ExecuteReader( );
                DataTable table = new DataTable( );
                table.Load( reader );

 IDataReader

Types that implement the IDataReader interface allow for forward-only reading through 1 or more result sets.

The IDataReader type also inherits from IDataRecord which exposes a set of get methods like "GetDecimal()", "GetBoolean()", "GetOrdinal()", "GetString()" to read out the value from each column as you iterate through the result set. This can definitely lead to some sloppy code such as...

        private void SloppyReader( IDataReader reader ) {
            string customerFirstName = !reader.IsDBNull( 1 ) ? reader.GetString( 1 ) : "Unknown";
            string customerLastName = !reader.IsDBNull( 2 ) ? reader.GetString( 2 ) : "Unknown";            
        }

IDataParameter

The IDataParameter is a parameter that is used by command objects. For example in the following SQL syntax @FirstName and @LastName are parameters.

INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName);

The following C# will insert the values "Mo" and "Khan" as the @FirstName, and @LastName parameters.

            IDbCommand command = connection.CreateCommand( );
            command.CommandText = "INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName);";

            IDataParameter commandParameter = command.CreateParameter( );
            commandParameter.ParameterName = "@FirstName";
            commandParameter.Value = "Mo";
            command.Parameters.Add( commandParameter );

            commandParameter = command.CreateParameter( );
            commandParameter.ParameterName = "@LastName";
            commandParameter.Value = "Khan";
            command.Parameters.Add( commandParameter );

DbProviderFactories

This type has a factory method called "GetFactory()" which returns an abstract factory for creating connections, commands, adapters and parameters.

To construct a DbProviderFactory you need to specify the ADO.NET provider to use. For example for the SQL Server Provider, you would use the invariant provider name of "System.Data.SqlClient".

This works great with the ConnectionStringSettings section of the *.config file. Using the ConnectionStringSettings type you can extract the connection string as well as the provider name and construct a DbProviderFactory without a re-compile.

With a quick change to the *.config I can switch from a SQL Server provider to an Oracle provider or MySQL provider, or an OleDb provider or and Odbc provider.

    <connectionStrings>
        <add name="PayablesConnection" 
             connectionString="data source=(local);Integrated Security=SSPI;Initial Catalog=Payables;" 
             providerName="System.Data.SqlClient" />
   </connectionStrings>

 The DbProviderFactory allows you to create database agnostic ADO.NET types through factory methods. 

        public DatabaseConnectionFactory( ConnectionStringSettings connectionStringSettings ) {
            _settings = connectionStringSettings;
        }

        public IDbConnection Create( ) {
            IDbConnection connection = DbProviderFactories.GetFactory( _settings.ProviderName ).CreateConnection( );
            connection.ConnectionString = _settings.ConnectionString;
            return connection;
        }

ADO.NET seems intimidating at first, but when you break it down piece by piece it's not such a scary beast. Now none of the examples provided use stored procedures, but using similar techniques discussed you could transition to stored procedures with ease and confidence.

Now as for DataSets, DataAdapters, CodeGen and Wizards... that's a whole other story!

• ADO.NET | CSharp | SAIT

Prioritizing Your Threads

Monday, June 04, 2007 7:21:06 PM (Mountain Standard Time, UTC-07:00)

Here is my latest assignment for my CMPP297 class at SAIT.

"Write a program to demonstrate that, as a high-priority thread executes, it will delay the execution of all lower-priority threads."

It took me a while to figure out a way to do this. At first I was thinking I would try to create a circular buffer with a consumer and producer object that was reading/writing to a shared data buffer. I was going to give one a higher priority then the other using the Priority property of the thread class.

This seemed like a lot of work, and I was wondering if I would be able to capture the actual requirements by doing this. I later thought back to the example we got in class about me and a friend running. I thought that if I could count the number of laps we each ran this would tell me who ran "faster".

My final solution I decided to just increment a counter in 2 different threads, giving each thread a different priority hoping that the higher priority thread would run "faster". Turns out it did, even on my P4 with hyper threading.

My Solution: (It's not elegant, but hopefully it conveys the message!)

    public class ThreadCounter {
        #region Constructors

        public ThreadCounter( ) {
            _continue = true;
        }

        #endregion

        #region Public Properties

        public Boolean Continue {
            set { _continue = value; }
        }

        #endregion

        #region Public Methods

        public void Count( ) {
            Int32 i__ = 0;

            while ( _continue ) {
                i__++;
            }
            Console.WriteLine( "{0} has a count = {1}", Thread.CurrentThread.Name, i__ );
        }

        #endregion

        #region Private Fields

        private bool _continue;

        #endregion
    }

    internal class Program {
        private static void Main( ) {
            const Int32 TimeoutMilliSeconds = 5000;
            ThreadCounter priorityTest = new ThreadCounter( );
            ThreadStart startDelegate = new ThreadStart( priorityTest.Count );

            Thread threadOne = new Thread( startDelegate );
            threadOne.Name = "Highest Priority Thread";
            Thread threadTwo = new Thread( startDelegate );
            threadTwo.Name = "Lowest Priority Thread";

            threadOne.Priority = System.Threading.ThreadPriority.Highest;
            threadTwo.Priority = System.Threading.ThreadPriority.Lowest;
            threadOne.Start( );
            threadTwo.Start( );

            Console.WriteLine( "Putting thread to sleep for 5 seconds... Please wait!" );
            Thread.Sleep( TimeoutMilliSeconds );
            priorityTest.Continue = false;
            Console.ReadLine( );
        }
    }

The results look something like this... the counts will be different based on the speed of you CPU, whether you have a single core, duo core or hyper threading!

• CSharp | SAIT

Deadlock

Wednesday, May 30, 2007 7:20:18 PM (Mountain Standard Time, UTC-07:00)

A deadlock occurs when two or more threads are trying to access the same data but are blocking each other from getting at the resources necessary to continue.

    internal class Program {
        private static void Main( ) {
            Deadlocker deadlock = new Deadlocker( );

            Thread first = new Thread( new ThreadStart( deadlock.First ) );
            Thread second = new Thread( new ThreadStart( deadlock.Second ) );
            first.Start( );
            second.Start( );

            first.Join( );
            second.Join( );
        }
    }

    /// <summary>
    /// 1. First thread starts and locks resourceA
    /// 2. Second thread starts and locks resourceB
    /// 3. First thread blocks waiting for resourceB to be freed.
    /// 4. Second thread blocks waiting for resourceA to be freed.
    /// 5. The application stops in it's tracks.
    /// </summary>
    internal class Deadlocker {
        #region Public Methods

        public void First( ) {
            lock ( _resourceA ) {
                lock ( ( _resourceB ) ) {
                    Console.WriteLine( "First" );
                }
            }
        }

        public void Second( ) {
            lock ( _resourceB ) {
                lock ( ( _resourceA ) ) {
                    Console.WriteLine( "Second" );
                }
            }
        }

        #endregion

        #region Private Fields

        private object _resourceA = new object( );
        private object _resourceB = new object( );

        #endregion
    }

Remember that lock implicitly translates too...

    lock ( _resourceB ) {
        
    }
    
    // translates too
    Monitor.Enter( _resourceB);
    try {
        
    }
    finally {
        Monitor.Exit( _resourceB );
    }

Instead of using Monitor.Enter(), you can use Monitor.TryEnter();

    if ( !Monitor.TryEnter( _resourceB, TimeSpan.FromSeconds( 5 ) ) ) {
        throw new TimeoutException( );
    }
    try {
        
    }
    finally {
        Monitor.Exit( _resourceB );
    }

For more info check out...

MCTS Self-Paced Training Kit (Exam 70-536): Microsoft .NET Framework 2.0 Application Development Foundation by Tony Northrup, Shawn Wildermuth, Bill Ryan Read more about this title...

• Books | CSharp | SAIT

Asterisk Patterns

Saturday, May 26, 2007 11:32:16 AM (Mountain Standard Time, UTC-07:00)

So in class this week were given the following problem to solve:

"Write an application that displays the following patterns separately, one below the other. Use for loops to generate the patterns. All asterisks (*) should be printed by a single statement of the form Console.Write( "*" ); which causes the asterisks to print side by side. A statement of the form Console.WriteLine(); can be used to move to the next line. A statement of the form Console.Write( " " ); can be used to display a space for the last two patterns. There should be no other output statements in the application. [Hint: the last two patterns require that each line begin with an appropriate number of blank spaces.]"

Here is my solution:

    #region Public Methods

    public static void Main( ) {
        OutputAC( delegate( Int32 xAxis, Int32 i ) { return ( xAxis < i + 1 ); } );

        OutputBD( delegate( Int32 xAxis, Int32 i ) { return ( xAxis < i ); } );

        OutputAC( delegate( Int32 xAxis, Int32 i ) { return ( xAxis >= i ); } );

        OutputBD( delegate( Int32 xAxis, Int32 i ) { return ( i <= xAxis + 1 ); } );
        Console.ReadLine( );
    }

    #endregion

    #region Private Fields

    private delegate Boolean CheckCondition( Int32 xAxis, Int32 charactersPerRow );

    #endregion

    #region Private Methods

    private static void OutputAC( CheckCondition condition ) {
        Int32 charactersPerRow = 0;
        // loop through the y axis from row 0 to 10
        for ( int yAxis = 0; yAxis < 10; yAxis++ ) {
            // loop through x axis from column 0 to 10 for each row.
            for ( int xAxis = 0; xAxis < 10; xAxis++ ) {
                // write the character for the current position
                Console.Write( condition( xAxis, charactersPerRow ) ? "*" : " " );
            }
            ++charactersPerRow;
            Console.WriteLine( );
        }
    }

    private static void OutputBD( CheckCondition condition ) {
        Int32 charactersPerRow = 10;
        for ( int yAxis = 0; yAxis < 10; yAxis++ ) {
            for ( int xAxis = 0; xAxis < 10; xAxis++ ) {
                Console.Write( condition( xAxis, charactersPerRow ) ? "*" : " " );
            }
            --charactersPerRow;
            Console.WriteLine( );
        }
    }

    #endregion

• CSharp | SAIT