C# abstraction and database layer

Question

I am wondering what's a better way to abstract some of this code, into a simple DAL. At this time, I'm just patching the code and don't have time or need yet to use EF, Linq2Sql or any ORM right now.

    public string GetMySpecId(string dataId)
    {
        using (SqlConnection conn = new SqlConnection(this.MyConnectionString))
        {

            conn.Open();

            // Declare the parameter in the query string
            using (SqlCommand command = new SqlCommand(@"select ""specId"" from ""MyTable"" where ""dataId"" = :dataId", conn))
            {
                // Now add the parameter to the parameter collection of the command specifying its type.
                command.Parameters.Add(new SqlParameter("dataId", SqlDbType.Text));

                command.Prepare();

                // Now, add a value to it and later execute the command as usual.
                command.Parameters[0].Value = dataId;


                using (SqlDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        specId = dr[0].ToString();
                    }
                }
            }
        }

        return specId;
    }

What's a good clean way to pull the connection, commands, and such out of the GetMySpecId() as I will have tons of these functions and don't want to write the using.... over and over again.

Answer 1

Well, you could write your own custom data-access helper that encapsulates all that stuff and returns a DataTable:

public string GetMySpecId(string dataId)
{
    DataTable result = _dbHelper.ExecuteQuery(
        @"select ""specId"" from ""MyTable"" where ""dataId"" = :dataId",
        new SqlParameter("dataId", dataId);
    return result.Rows[0][0].ToString();
}

Or if you are stuck on the idea of using a DataReader, you could pass a delegate to the helper, which gets invoked inside of the using statements:

public string GetMySpecId(string dataId)
{
    return _dbHelper.ExecuteQuery(
        dr => 
           {
               if(dr.Read())
               {
                   return dr[0].ToString();
               }
               // do whatever makes sense here.
           },
        @"select ""specId"" from ""MyTable"" where ""dataId"" = :dataId",
        new SqlParameter("dataId", dataId));
}

You could also use a lightweight tool like Dapper to simplify some of the syntax and take care of mapping to your data types. (You'd still need to deal with opening a connection and such.)

Update

Here's an example of how you could write the ExecuteQuery method used in the second example:

public T ExecuteQuery<T>(
    Func<IDataReader, T> getResult,
    string query,
    params IDataParameter[] parameters)
{
    using (SqlConnection conn = new SqlConnection(this.MyConnectionString))
    {
        conn.Open();
        // Declare the parameter in the query string
        using (SqlCommand command = new SqlCommand(query, conn))
        {
            foreach(var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            command.Prepare();
            using (SqlDataReader dr = command.ExecuteReader())
            {
                return getResult(dr);
            }
        }
    }
}

Answer 2

You could use the yield return statement in order to keep the connection, command and reader objects inside using statements.

public class ScalarReader<T>
{
    const string MyConnectionString = "...";

    private string _returnColumn, _table, _whereCond;
    private object[] _condParams;

    public ScalarReader(string returnColumn, string table, string whereCond,
                        params object[] condParams)
    {
        _returnColumn = returnColumn;
        _table = table;
        _whereCond = whereCond;
        _condParams = condParams;
    }

    public IEnumerator<T> GetEnumerator()
    {
        using (SqlConnection conn = new SqlConnection(MyConnectionString)) {
            conn.Open();
            string select = String.Format(@"SELECT ""{0}"" FROM ""{1}"" WHERE {2}",
                                          _returnColumn, _table, _whereCond);
            using (SqlCommand command = new SqlCommand(select, conn)) {
                for (int p = 0; p < _condParams.Length; p++) {
                    command.Parameters.AddWithValue("@" + (p+1), _condParams[p]);
                }
                using (SqlDataReader dr = command.ExecuteReader()) {
                    while (dr.Read()) {
                        if (dr.IsDBNull(0)) {
                            yield return default(T);
                        } else {
                            yield return (T)dr[0];
                        }
                    }
                }
            }
        }
    }
}

You would call it like this

var reader = new ScalarReader<string>("specId", "MyTable", "dataId=@1", "x");
foreach (string id in reader) {
    Console.WriteLine(id);
}

Note that I am using a convention for the parameter names. They are named @1, @2, @3 ....

var reader =
    new ScalarReader<DateTime>("date", "MyTable", "num=@1 AND name=@2", 77, "joe");

Answer 3

You would need to return an IDataReader from the middle of your using statement and as soon as you do that, you will lose the connection and the data. You can't really do what you are after.

Answer 4

You could do something like this, sorry for no actual code, but it will give you the idea. Of course it will have to be careful converting object[] back into something useful, but you're sort of doing that already with specId = dr[0].ToString();

class MyDb
{
    public MyDb()
    {
    }

    public void Initialize()
    {
        // open the connection
    }

    public void Finalize()
    {
        // close the connection
    }

    public List<object[]> Query(string command, List<SqlParameter> params)
    {
        // prepare command
        // execute reader
        // read all values into List of object[], and return it
    }
}

Answer 5

You can create a base abstract class that will have some base function with all the usings and base code like so:

public abstract class BaseClass
{
  public abstract void myFunc(SqlConnection conn);

  public void BaseFunc()
  {      
        using (SqlConnection conn = new SqlConnection(this.MyConnectionString))
        {
            conn.Open();
            myFunc(conn);
            ..any other base implementation...
        }
  }
}

each derived class will inheret the BaseClass and implement the abstract MyFunc with the specific query and all the specific stuff for each derived class. You will call from outside the BaseFunc function of the base abstract class.