Data Access Layer help. Need to connect to a SQL Server 2005 and ODBC database..

I know there about a couple hundred subjects on creating a data access layer, but I'm hoping to get  more tailored feedback in this question. The application is a simple blogging system. It's intended to be something simple to show other developers the basics of getting a web app up and running.

I am trying to use an N Tiered approach to. I know the standard three layers are the UI, Business, and Data Access Layer. My issue is that I am having trouble conceptually splitting up the layers. I have a UI Layer and a Data Access Layer, but I'm not sure what parts or pieces go into the business layer. Furthermore, there's the issue that this application needs to connect to SQL Server as well as ODBC (for DB2). At the moment, I would have to entirely rewrite my Data Access Layer. I'm hoping for a more generic approach where as little code as possible gets duplicated.

I've seen many suggestions on using the Microsoft Application Blocks. My issue with that is that I really want to know and understand what is going on behind the scenes so I think, in a way, using the Microsoft Application Blocks is just a cop out (for me...I don't mind using other code when I understand it...it's when I don't understand it that it bothers me).

Okay, well here's what I have now:

Data Objects:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Object that reflects tblBlog
/// </summary>
public class Blog
{

    private long blogId;
    private long userId;
    private String title;
    private String entryText;
    private DateTime createDate;
    private DateTime updateDate;
    private long statusId;

    public Blog()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public long BlogId
    {
        get { return blogId; }
        set { blogId = value; }
    }  

    public long UserId
    {
        get { return userId; }
        set { userId = value; }
    }  

    public String Title
    {
        get { return title; }
        set { title = value; }
    }  

    public String EntryText
    {
        get { return entryText; }
        set { entryText = value; }
    }    

    public DateTime CreateDate
    {
        get { return createDate; }
        set { createDate = value; }
    }  

    public DateTime UpdateDate
    {
        get { return updateDate; }
        set { updateDate = value; }
    }

    public long StatusId
    {
        get { return statusId; }
        set { statusId = value; }
    }

}


//DATA ACCESS OBJECTS
public class DBConnection
{
      public DBConnection()
      {
            //
            // TODO: Add constructor logic here
            //
       
      }

    /// <summary>
    /// Gets a database connection. Currently only works for an SqlServer database.
    /// </summary>
    /// <returns>A SqlConnection object pointing to a local Sql Server 2005 database myblogger.</returns>
    public SqlConnection getDBConnection()
    {
        String sqlConnStr = ConfigurationManager.ConnectionStrings    ["MyBloggerSqlServer"].ConnectionString;
        SqlConnection conn = new SqlConnection(sqlConnStr);
        return conn;
    }
}

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using dbconnection;

/// <summary>
/// Handles data management of all functionality
/// mostly pertaining to Blogs and tblBlogs.///
/// </summary>
public class BlogController
{
      public BlogController()
      {
            //
            // TODO: Add constructor logic here
            //
      }

    /// <summary>
    /// Saves a user's blog entry to tblBlogs
    /// </summary>
    /// <param name="blog"></param>
    public void SaveBlogEntry(Blog blog)
    {
        DBConnection dbconn = new DBConnection();
        SqlConnection conn = dbconn.getDBConnection();
        DateTime dt = DateTime.Now;
       
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(SqlConstants.SaveBlogEntrySqlServer,
                                conn);

            SqlParameter userId = new SqlParameter("@userId", blog.UserId);
            SqlParameter title = new SqlParameter("@title", blog.Title);
            SqlParameter entryText = new SqlParameter("@entryText", blog.EntryText);
            SqlParameter createDate = new SqlParameter("@createDate", dt);

            cmd.Parameters.Add(userId);
            cmd.Parameters.Add(title);
            cmd.Parameters.Add(entryText);
            cmd.Parameters.Add(createDate);  

            int rowsAffected = cmd.ExecuteNonQuery();

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// Get a list of the most recent blog entries
    /// for a given user. How many that will be retrieved
    /// is still undecided.
    /// </summary>
    /// <param name="userId"></param>
    public DataTable GetRecentBlogsByUserId(long userId)
    {
        List<Blog> blogList = new List<Blog>();
        DBConnection dbconn = new DBConnection();
        SqlConnection conn = dbconn.getDBConnection();
        SqlDataReader rdr = null;
        DataTable dataTable = null;

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(SqlConstants.GetRecentBlogsByUserIdSqlServer,
                                conn);
            SqlParameter pUserId = new SqlParameter("@userId", userId);
           
            cmd.Parameters.Add(pUserId);

                  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
                  dataTable = new DataTable();
                  sqlDataAdapter.Fill(dataTable);            
     
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return dataTable;
    }

    public Blog GetBlogByBlogId(Int64 blogId)
    {
        List<Blog> blogList = new List<Blog>();
        DBConnection dbconn = new DBConnection();
        SqlConnection conn = dbconn.getDBConnection();
        SqlDataReader rdr = null;
        Blog blog = new Blog();

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(SqlConstants.GetBlogByBlogIdSqlServer,
                                conn);
            SqlParameter pBlogId = new SqlParameter("@blogId", blogId);

            cmd.Parameters.Add(pBlogId);

            //Using the DataTable instead of a data reader
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                //Need to add code to check for nulls b/c a null field
                //results in an exception
                blog.Title = rdr.GetString(rdr.GetOrdinal("title"));
                blog.EntryText = rdr.GetString(rdr.GetOrdinal("entry_text"));
                blog.UserId = rdr.GetInt64(rdr.GetOrdinal("userId"));
                blog.BlogId = rdr.GetInt64(rdr.GetOrdinal("blogId"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("create_date")))
                {
                    blog.CreateDate = rdr.GetDateTime(rdr.GetOrdinal("create_date"));                }
               
                if(!rdr.IsDBNull(rdr.GetOrdinal("update_date")))
                {
                    blog.UpdateDate = rdr.GetDateTime(rdr.GetOrdinal("update_date"));
                }
               
                //blog.StatusId = rdr.GetInt64(rdr.GetOrdinal("statusId"));
            }
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return blog;
    }

    public void UpdateBlogEntry(Blog blog)
    {
        DBConnection dbconn = new DBConnection();
        SqlConnection conn = dbconn.getDBConnection();
        DateTime dt = DateTime.Now;

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(SqlConstants.UpdateBlogByBlogIdSqlServer,
                                conn);

            SqlParameter blogId = new SqlParameter("@blogId", blog.BlogId);
            SqlParameter title = new SqlParameter("@title", blog.Title);
            SqlParameter entryText = new SqlParameter("@entryText", blog.EntryText);
            SqlParameter updateDate = new SqlParameter("@updateDate", dt);
                       
            cmd.Parameters.Add(title);
            cmd.Parameters.Add(entryText);
            cmd.Parameters.Add(blogId);
            cmd.Parameters.Add(updateDate);

            int rowsAffected = cmd.ExecuteNonQuery();
           
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

}


So my UI Layer interacts with the database by making calls akin to:

        BlogController blogCont = new BlogController();
        Blog blog = populateBlogFromForm();      
        blogCont.UpdateBlogEntry(blog);

Okay so I know that I need to move the BlogController class to an interface of some sort. Also note that the DBConnection class is being used in the BlogController to get the DB Connection. I have another class that has a bunch of static readonly strings that are my Sql Statements to be used in the system. Making all methods Stored Procedures is not an option in my scenerio.

My issue is that I'm hitting a wall here conceptually. I cannot think of how to make jump from the concept of using Interfaces here to mask the data access layer. Can someone try to fill in the gaps in my understanding here? And tell me if this approach is just wrong. I want to know the right way to do this and I can handle criticism.
Start Free Trial