David Hayden [MVP C#]

.NET Tutorials, Patterns, and Practices

Review of Codus and Data Access Object Pattern - GetOrdinal Performance with IDataReader

It's been a few weeks since my last post. Unfortunately, someone very special to me passed away very suddenly and unexpectedly a few weeks ago, and I just haven't felt up to blogging. Life is returning back to normal, however, aside from catching up on some projects, so it's good to be back.

One of the interesting tools I have been reviewing for a new project is Codus. To be brief, Codus is a free O/R Mapper by Adapdev Technologies. And although I have a number of code generation tools and O/R Mappers in my toolbox already, it's always fun to try out new tools.

I downloaded Codus v1.0.2.0, and quite frankly, it is about as simple to use as you can get. The client portion is a winform application that consists of 3 tabs: Setup, Tables, and Generate. On the Setup Tab you essentially point Codus to to a database. On the Tables Tab, you choose the particular tables and views to be mapped to objects. And last, on the Generate Tab, you choose a number of options to be generated, such as a Visual Studio 2003 solutions file, web services, NUnit and Zanebug unit tests, stored procedures, etc. Although the GUI interface isn't sexy and could use a number of new features as well as at least 1 quirk fixed that I experienced, it works and is fairly intuitive.

The interesting part of Codus, however, is the code it generates. Sean will have to correct me here if I am wrong, but Codus essentially creates a Data Access Object and a Transfer Object for each table or view chosen in the Table Tab of the winform application. The Sun Developer Network has a very nice catalog of Core J2EE Patterns, and in particular, a very good description of the Data Access Object Pattern. Here are couple of diagrams, all credit goes to the Sun Developer Network, that shows the relationships quite nice. I recommend you read the pattern description here as well, because it is a good read.

The Transfer Object is about what you would expect. Just a set of private fields with public properties and an override of ToString() that essentially displays the values of the private fields for debugging purposes. No behavior. Just data.

Example Transfer Object

namespace Test {    

    /// 
    /// An object representation of
    /// the test Address table
    /// 
    [Serializable]
    public class AddressEntity{
    
        private System.String _Firstname = "";
        private System.Int32 _ID = 0;
        private System.String _Lastname = "";
        private System.String _Title = "";

        public System.String Firstname {
            get {
                return this._Firstname;
            }
            set {
                this._Firstname = value;
            }
        }            


        public System.Int32 ID {
            get {
                return this._ID;
            }
            set {
                this._ID = value;
            }
        }        


        public System.String Lastname {
            get {
                return this._Lastname;
            }
            set {
                this._Lastname = value;
            }
        }            
        

        public System.String Title {
            get {
                return this._Title;
            }
            set {
                this._Title = value;
            }
        }            
        
        /// 
        /// Returns a string representation of the object,
        /// displaying all property and field names and values.
        /// 
        public override string ToString() {
            return StringUtil.ToString(this);
        }
    }
}

The Data Access Object is a bit more involved, but not much, as each DAO created per table derives from an abstract class that is part of the Adapdev Framework, called Adapdev.Data.AbstractDAO. Yeah, you get the source code for that baby, which has a number of cool things that I won't talk about. Anyway, most of the code is found in the abstract class and the only thing in the generated DAO for each table or view is essentially "CRUD related methods" that create insert, update, select one, and delete one commands as well as a method that maps an IDataReader to the associated transfer object.

Example Data Access Object

namespace Test {

    /// 
    /// Base Data Access Object for the Address table.
    /// 
    public class AddressDAOBase : Adapdev.Data.AbstractDAO {
        
            public AddressDAOBase() : 
               base(DbConstants.DatabaseProviderType, DbConstants.DatabaseType,
                       "Address", DbConstants.ConnectionString) {
            }
        
            protected override object MapObject(System.Data.IDataReader r) {

                AddresstEntity entity = new AddressEntity();
                
    
                try{
                    int ordinal = r.GetOrdinal("ID");
                    if (!r.IsDBNull(ordinal)) entity.ID =
                            ((System.Int32)(r.GetValue(ordinal)));
                }
                catch(Exception ex){}
            
    
                try{
                    int ordinal = r.GetOrdinal("Firstname");
                    if (!r.IsDBNull(ordinal)) entity.Firstname =
                              ((System.String)(r.GetValue(ordinal)));
                }
                catch(Exception ex){}
            
    
                try{
                    int ordinal = r.GetOrdinal("Lastname");
                    if (!r.IsDBNull(ordinal)) entity.Lastname =
                               ((System.String)(r.GetValue(ordinal)));
                }
                catch(Exception ex){}
            
    
                try{
                    int ordinal = r.GetOrdinal("Title");
                    if (!r.IsDBNull(ordinal)) entity.Title =
                               ((System.String)(r.GetValue(ordinal)));
                }
                catch(Exception ex){}
            
    
                return entity;
            }
 
            protected override System.Data.IDbCommand
                               CreateInsertCommand(object o) {
            
            // Snip...
        
            }

            protected override System.Data.IDbCommand
                                CreateUpdateCommand(object o) {
    
            // Snip...
            
            }


            protected override System.Data.IDbCommand
                            CreateDeleteOneCommand(object id) {
    
                                
                System.Data.IDbCommand cmd =
                     this.CreateCommand("DELETE FROM [Address] WHERE  ID = @ID");
                                
                IDataParameterCollection cmdParams = cmd.Parameters;
            
                System.Data.IDbDataParameter par = cmd.CreateParameter();
                par.ParameterName = "@ID";
                par.Value = id;
                cmdParams.Add(par);
                
                return cmd;
        
            }
    
   
            protected override System.Data.IDbCommand
                            CreateSelectOneCommand(object id) {
        
                                
                System.Data.IDbCommand cmd = this.CreateCommand("SELECT  [ID],
                      [Firstname], [Lastname], [Title] FROM [Address] WHERE  ID = @ID");
                                
                IDataParameterCollection cmdParams = cmd.Parameters;
            
                System.Data.IDbDataParameter par = cmd.CreateParameter();
                par.ParameterName = "@ID";
                par.Value = id;
                cmdParams.Add(par);
            
                return cmd;
        
            }
    }
}

There is a bit more to each DAO, but I snipped off a number of pieces to keep things simple. For the most part, however, this is about it. As I mentioned above, most of the detail is hidden in the AbstractDAO class, which keeps things nice and clean.

The only thing I question above is the use of GetOrdinal on the IDataReader as shown above. If you are not familiar with GetOrdinal, it basically returns the index of a given column name in the IDataReader. There is a performance hit when accessing a datareader by column name. When looping through records in a datareader, you may want to grab the index for each column name prior to going into a read loop and then using the indexes (as opposed to the column names) to access data from the datareader within the loop. Because the AbstractDAO class probably calls that MapObject method within a while loop when filling a collection of transfer objects, a call to GetOrdinal might be a good thing. However, in this case, the call to GetOrdinal is inside the loop so I would think this strategy actually hurts performance. I haven't tested it, but it seems logical at first glance. If I totally missed the mark here, please let me know.

Anyway, I have to say the code created by Codus is pretty cool and very easy to grasp and maintain. I haven't run it through the paces yet, but it seems very clean and a good place to start for those who have not used an O/R Mapper before. I mean, seriously, how easier can it get to create and save an address in the address table:

Save an Address

AddressEntity Address = new AddressEntity();

Address.FirstName = "Joe";
Address.LastName = "Schmoe";
Address.Title = "President";

AddressDAO dao = new AddressDAO();
dao.Save(address);

I can't do Codus justice as to all the functionality that is packed into the DAO. Read the documentation to see all the methods. If you get a chance, I recommend downloading Codus and reviewing the code it generates. I also recommend checking out the sourcecode to the Adapdev Framework as there are some gems in there as well. By the way, did I mention that if you wanted to change the output by Codus, like remove the GetOrdinal in the mapping method, all you need to do is change the templates. Cool stuff, and FREE!

Published May 08 2005, 06:55 PM by David Hayden

Comments

CsOver said:

Useful for all!It can connect database with DAO!Different from ADO.NET a big!he...he..

# May 9, 2005 3:10 AM

Sean McCormack said:

David,

Thanks for the great post!! You're correct...I'm using the TO/DAO pattern. If you're creating a simple app, you could treat the TO as a Business Object, but it was meant primarily just to shuttle data around (especially for the generated web services).

You're right about the GetOrdinal issue...there is a slight performance impact. The impact is pretty minimal (still less time than a DataSet to retrieve the same number of records)...but I'll try to fix it in the next release.

You mentioned that you have a few ideas for UI improvement, etc. Please feel free to send over any ideas and I'll try to work them in!

Thanks again for a great post!!

Sean

# May 9, 2005 1:18 PM

David Hayden said:

Hey Sean,

You're welcome, but I should be thanking you for providing such a good tool for free as well as providing the source code to learn from. I will probably write a couple more posts to point out some other cool coding examples.

I want to spend a few more days trying it out and then I will send you a support email with all the feedback.

Thanks!

# May 10, 2005 3:14 PM

Mischa Kroon said:

Have you tried out mygeneration yet ?

Thats a great codegenerator with a very nice O/R mapper interface called dOOdads

http://www.mygenerationsoftware.com/

http://www.mygenerationsoftware.com/dOOdads/dOOdads.aspx

You might like it better then what your currently using.

# May 11, 2005 4:07 AM