4
\$\begingroup\$

I end up writing a lot of the same code when I query a database I thought I would try to encapsulate that all in a class that could be used by any provider that implemented IDbConnection. i'm not looking to do much, return a DataTable when I want a result set, things like that. Here is my first attempt. I've tested that it works with SQL Server, but would like to know if there is any way to improve it or any issues I may not be aware of.

public class DataQuery<TConnection> where TConnection : IDbConnection, new()
{
    private string connectionString;
    private TConnection cnn;

    private TConnection NewConnection()
    {
        cnn = new TConnection();
        cnn.ConnectionString = connectionString;
        return cnn;
    }
    public DataQuery(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public int Execute(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            return cmd.ExecuteNonQuery();
        }
    }

    public DataTable QueryDataTable(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            var t = new DataTable();
            t.Load(cmd.ExecuteReader());
            return t;
        }
    }

    public T QueryValue<T>(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            return (T)cmd.ExecuteScalar();
        }
    }

    public IEnumerable<IDataRecord> QueryDataRecord(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return reader;
                }
            }
        }
    }

    public IEnumerable<string[]> QueryStringArray(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string[] vals = new string[reader.FieldCount];

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        vals[i] = reader.IsDBNull(i) ? "" : reader[i].ToString();
                    }

                    yield return vals;
                }
            }
        }
    }

    public IEnumerable<string> QueryString(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string[] vals = new string[reader.FieldCount];

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    yield return reader.IsDBNull(0) ? "" : reader[0].ToString();
                }
            }
        }
    }

    public bool Exists(IDbCommand cmd)
    {
        using (cnn = NewConnection())
        using (cmd)
        {
            cmd.Connection = cnn;
            cnn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                return reader.Read();
            }
        }
    }

}

Sample usage

var db = new Sandbox.DataQuery<SqlConnection>(@"Server=xps13\sqlexpress;Database=AdventureWorks2014;Trusted_Connection=True;");

var execute = db.Execute(new SqlCommand("update Person.Address set PostalCode = PostalCode"));
Console.WriteLine("db.Execute: {0}", execute.ToString());

var results = db.QueryString(new SqlCommand("select * from Person.Address")).ToList();
Console.WriteLine("db.QueryString: {0}", results.Count());

var exists = db.Exists(new SqlCommand("select * from Person.Address where 1=0"));
Console.WriteLine("db.Exists: {0}", exists ? "exists" : "does not exist");

//Output
//db.Execute: 19614
//db.QueryString: 176526
//db.Exists: does not exist
\$\endgroup\$
4
\$\begingroup\$
using (cnn = NewConnection()) {}

This is a very dangerous design. Especially the private TConnection cnn field that is shared by each method.

If you ever use it in paralell then those methods will overwrite each other connections. You should use them locally only:

using (var cnn = NewConnection()) {}

using (cmd)

This is also a no go. I'd be really surprised when I found that my command has been disposed by the method using it. It is however a good idea to maintain the command by the method. It would be better if you hadn't to create it outside.

I suggest this design instead:

public int Execute(Action<IDbCommand> configureCommand)
{
    using (var cnn = NewConnection())
    using (var cmd = cnn.CreateCommand())
    {
        configureCommand(cmd);
        cmd.Connection = cnn;
        cnn.Open();

        return cmd.ExecuteNonQuery();
    }
}

then you can use it like this:

var execute = db.Execute(cmd => cmd.CommandText = "update Person.Address set PostalCode = PostalCode");

This way you don't have to think about which IDbCommand you need to create.

or with parameters:

var execute = db.Execute(cmd =>
{
    cmd.CommandText = "update Person.Address set PostalCode = PostalCode";
    var p1 = new SqlParameter("@p1", SqlDbType.VarChar);
    p1.Value = "abc";
    cmd.Parameters.Add(p1);
});
\$\endgroup\$
  • \$\begingroup\$ Good catch on the shared connection variable. One question with your approach of the command, how can I pass parameters to it? Normally I would do cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = "abc"; but using this way I can only do this Execute(c => c.Parameters.Add("@p1")); That was one of the reasons why I was passing in the command object. I just don't know of a better way. \$\endgroup\$ – Jeff Sep 27 '16 at 13:45
  • \$\begingroup\$ @Jeff I've added another example. You just need to add the {} to be able to write more statements. It's a little bit inconvenient due to the interface but if you know that you are always be using the sql provider then you might switch to the specific type and add parameters like in your comment above. \$\endgroup\$ – t3chb0t Sep 27 '16 at 14:20
  • \$\begingroup\$ That's similar to how i currently do it, so that works for me. Thanks for the input! \$\endgroup\$ – Jeff Sep 27 '16 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.