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.

I'm not sure what you're trying to accomplish here...I see you pulling a string ID out of the database in the usual ADO.NET way. How would you like to write this code differently? Pull the rest of the data out of MyTable and hydrate the rest of the object? Get rid of the boilerplate SqlClient stuff? I really suspect using an ORM may be worth your time. Code-first EF can be a really lightweight solution, and quick to set up. — Justin Morgan
– Justin Morgan, Commented Dec 4, 2012 at 22:28
Got some base problems. If more than one specID then will return the last. If no specID then no default. Heck specID is not even declared. — paparazzo
– paparazzo, Commented Dec 4, 2012 at 22:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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);
            }
        }
    }
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 4, 2012 at 22:25

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cdub Over a year ago

wait where do the usings go?

cdub Over a year ago

why are there 2 return statements in the second example?

cdub Over a year ago

yeah i'm still a little confused on how the second example works

StriplingWarrior Over a year ago

@chris: The using statements all end up in the ExecuteQuery helper method. I accidentally left the second return statement due to a copy/paste error, but it's fixed now. Let me know if my update makes sense.

Olivier Jacot-Descombes · Accepted Answer · 2012-12-04 22:57:24Z

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");

@chris: yield return lets you specify the items to be returned by your IEnumerable one at a time. msdn.microsoft.com/en-us/library/vstudio/9k7k7cf0.aspx

matt-dot-net · Accepted Answer · 2012-12-04 22:19:34Z

0

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.

answered Dec 4, 2012 at 22:19

matt-dot-net

4,24423 silver badges24 bronze badges

Comments

Mike Trusov · Accepted Answer · 2012-12-04 22:29:34Z

0

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
    }
}

answered Dec 4, 2012 at 22:29

Mike Trusov

2,0181 gold badge16 silver badges25 bronze badges

Comments

Greg Oks · Accepted Answer · 2012-12-05 21:00:33Z

0

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.

edited Dec 5, 2012 at 21:00

answered Dec 4, 2012 at 22:30

Greg Oks

2,7304 gold badges35 silver badges41 bronze badges

1 Comment

Greg Oks Over a year ago

Pass the connection as a parameter to myFunc

Collectives™ on Stack Overflow

C# abstraction and database layer

5 Answers 5

Update

4 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Update

4 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related