Data.Compare

Home / Data

Compare DbManager with ADO.NET

This example contains two demos to demonstrate the difference between ADO.NET and BLToolkit. The AdoDemo represents a typical data access method which takes one parameter and returns a list of objects. All routine mapping work is done manually inside the method.

AdoDemo.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

using NUnit.Framework;

namespace HowTo.Data
{
    [TestFixture]
    public class AdoDemo
    {
        // Typified definition of the Gender database field.
        //
        public enum Gender
        {
            Female,
            Male,
            Unknown,
            Other
        }

        // Business object.
        //
        public class Person
        {
            public int    ID         { get; set; }
            public string FirstName  { get; set; }
            public string MiddleName { get; set; }
            public string LastName   { get; set; }
            public Gender Gender     { get; set; }
        }

        // ADO.NET data access method.
        //
        public List<Person> GetList(Gender gender)
        {
            // Map the typified parameter value to its database representation.
            //
            string paramValue = "";

            switch (gender)
            {
                case Gender.Female:  paramValue = "F"; break;
                case Gender.Male:    paramValue = "M"; break;
                case Gender.Unknown: paramValue = "U"; break;
                case Gender.Other:   paramValue = "O"; break;
            }

            // Read a database configuration string.
            //
            string cs = ConfigurationManager.ConnectionStrings["DemoConnection"].ConnectionString;

            // Create and open a database connection.
            //
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();

                // Create and initialize a Command object.
                //
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM Person WHERE Gender = @gender";
                    cmd.Parameters.AddWithValue("@gender", paramValue);

                    // Execute query.
                    //
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        List<Person> list = new List<Person>();

                        while (rd.Read())
                        {
                            Person person = new Person();

                            // Map a data reader row to a business object.
                            //
                            person.ID         = Convert.ToInt32 (rd["PersonID"]);
                            person.FirstName  = Convert.ToString(rd["FirstName"]);
                            person.MiddleName = Convert.ToString(rd["MiddleName"]);
                            person.LastName   = Convert.ToString(rd["LastName"]);

                            switch (rd["Gender"].ToString())
                            {
                                case "F": person.Gender = Gender.Female;  break;
                                case "M": person.Gender = Gender.Male;    break;
                                case "U": person.Gender = Gender.Unknown; break;
                                case "O": person.Gender = Gender.Other;   break;
                            }

                            list.Add(person);
                        }

                        return list;
                    }
                }
            }
        }

        [Test]
        public void Test()
        {
            List<Person> list = GetList(Gender.Male);
            Assert.Greater(list.Count, 0);
        }
    }
}

The DbManagerDemo does the same work performed by BLToolkit.DbManager.

DbManagerDemo.cs

using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;

namespace HowTo.Data
{
    [TestFixture]
    public class DbManagerDemo
    {
        // The MapValue attribute is used by BLToolkit.
        //
        public enum Gender
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        // Business object. Here we use C# 3.0 automatic properties,
        // however it can be public fields, regular or abstract properties.
        // The MapField attribute is used by BLToolkit to associate a database field
        // with a business object property if they have different names.
        //
        public class Person
        {
            [MapField("PersonID")]
            public int    ID         { get; set; }
            public string FirstName  { get; set; }
            public string MiddleName { get; set; }
            public string LastName   { get; set; }
            public Gender Gender     { get; set; }
        }

        // BLToolkit data access method.
        //
        public List<Person> GetList(Gender gender)
        {
            using (DbManager db = new DbManager("DemoConnection"))
            {
                return db
                    .SetCommand(
                        "SELECT * FROM Person WHERE Gender = @gender",
                        db.Parameter("@gender", Map.EnumToValue(gender)))
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void Test()
        {
            List<Person> list = GetList(Gender.Male);
            Assert.Greater(list.Count, 0);
        }
    }
}

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Create.sql script