Another C# SQL class

• Download SqlMagic.zip - 23 KB

Introduction

Like most other business developers, I've written my fair share of programs that do some form or another of database interaction. Load this chunk of data, transform it, shove it over here. Or, the other, load this chunk of data with these filters, render it for editing and save the changes back. Every business developer, at some point, will do some form or variation of these steps.

However, how we go about doing it seems to be a little too different. I mean .NET has the awesome System.Data.SqlClient namespace to really handle our SQL Server interaction needs. However, it is really low level to me. It requires a lot of setup JUST to get something going:

// I mean, look at the setup JUST to get this going:

var con = new SqlConnection("connectionString");
var cmd = new SqlCommand()
{
    CommandText = "SELECT col_one FROM tb_table WHERE col_id = @id",
    CommandType = CommandType.Text,
    CommandTimeout = 30, // Optional, but included for the sake of argument
    Connection = con
};

var datareader = cmd.ExecuteReader(true); // close the connection

// Now have fun converting the Reader to a DataSet

To me, that just seems like a lot of work upfront to try and take care of just querying for some information. However, just like me, I'm pretty sure most developers have some sort of class that abstracts some of this away. Well, today, I'm presenting to you all my solution: SQLMagic.

What is SQLMagic?

SQLMagic started as a small project to simplify my C# code so I can focus on writing the queries once (in your flavor of editor. I prefer SQL Server Management Studio), drop them into my application, execute them, and keep going. I don't want to spend too much time setting up my application environment to interact with my SQL Server. I want my simple class that I reference, create, and use forever. Thus, SQLMagic was born.

Goals

SQLMagic was designed to be simple. There should be no guesswork as to what SQLMagic is doing, ever. It should be able to handle anything I need it to without raising all sorts of fussiness other than what SQL Server would normally throw back. As such, SQLMagic has a rather large collection of ways to achieve that goal. 99% of you will end up using this in a simple manner without the need to go further. However, this article will have a more advanced section, just in case.

Using the code

SQLMagic is simple. That was the point. As such, using it is simple:

// Declare once, keep using
var nTimeout = 30; // timeout
var bLog = true;

// Create with a timeout of 30 seconds and logging as true
var oSql = new Sql("Connection String", nTimeout, bLog);

// Now let's roll!
SqlResultWithDataSet oResult = oSql.Open("SELECT * FROM tbl");

That's it. That's all you need to do to open a DataSet!

But, if you listen to most DBA's (and developers with common sense), you'll realize you really should be using parameters in your queries. Well, don't worry, SQLMagic has you covered:

// I'm going to re-use oSql and oResult from above ^^^

oResult = oSql.Open("SELECT * FROM tbl WHERE tbl_id = @id",
    CommandType.Text,
    new SqlParameter("id", 100)
);

SQLMagic makes it incredibly simple to write proper queries:

oResult = oSql.Open("SELECT * FROM tbl WHERE tbl_id IN (@id1, @id2, @id3)",
    CommandType.Text,
    new SqlParameter("id1", 1),
    new SqlParameter("id2", 2),
    new SqlParameter("id3", 3)
);

It doesn't matter how many parameters you need. SQLMagic makes it easy and effortless.

That covers most of the use cases. However, SQLMagic, since it utilizes the SqlClient namespace, allows for so much more:

// Stored Procedures with parameters?
oSql.Execute("sp_GoDoSomethingWithParameters",
    CommandType.StoredProcedure,
    new SqlParameter("param1", "value!")
);

// What about return values?
var datetime = oSql.Execute<DateTime>("SELECT GETDATE()");
var intBack = oSql.Execute<Int32>("SELECT 1");

// Stored Procedure that has a return value?
var intBackAgain = oSql.Execute<Int32>("sp_GiveNumber", CommandType.StoredProcedure);

SQLMagic makes all of this incredibly simple and easy to use.

Power Programmers: Using the code

This section helps expose a little more of the features that SQLMagic encompasses. This doesn't necessarily mean these subjects are difficult, but they are definitely what more advanced developers would be using.

Asynchronous Support

SQLMagic, with compilation flag "NET45" set, supports the async/await model of .NET, and implements it in a very clean, easy to use manner:

// Converting is VERY easy!
var oResult = await oSql.OpenAsync("SELECT * FROM aVeryLargeTable");

var nValue = await oSql.ExecuteAsync<Int32>("SELECT bigNumberComputationThatResultsInInteger");

var nProcedure = await oSql.ExecuteAsync<Int32>("sp_LongRunningProcedure",
    CommandType.StoredProcedure
);

Done! You are now using the async/await functionality of .NET 4.5+. SQLMagic supports the exact same overloads (in proper order) in synchronous or asynchronous, just by changing a few lines of code.

Transaction Support

SQLMagic gives you the ability to Begin, Commit, and RollBack transactions with and without asychronous capability:

// Start a Transaction:
SqlTransaction oTransaction = oSql.BeginTransaction();

// The asynchronous version:
SqlTransaction oTransaction = await oSql.BeginTransactionAsync();

// one of the overloads that SQLMagic has is the ability to specify a transaction!
// public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction)
// Statement is merely a SQLMagic struct that groups up some parameters from earlier:
Statement oStatement = new Statement
{
    Sql = "INSERT INTO tbl VALUES(@val1)",
    Type = CommandType.Text,
    Parameters = new List<SqlParameters>() { new SqlParameter("val1", "value") }
};
oSql.Open(oStatement, oTransaction.Connection, false, oTransaction);

// Open a DataSet of the given statement, connection, don't close it, and use that transaction

// Once you're all done, you end!
oSql.EndTransaction(oTransaction, true); // true or false indicates COMMIT or ROLLBACK!

// The asynchronous version:
await oSql.EndTransactionAsync(oTransaction);

Manual Connection Creation

SQLMagic lets you create connections yourself (remember that SQLMagic has overloads to indicate a SqlConnection object!):

// Synchronous
SqlConnection oConnection = oSql.CreateConnection(true);

// Asynchronous
SqlConnection oConnection = await oSql.CreateConnectionAsync(true);

// The boolean value indicates whether or not the connection is automatically opened when created

TryOpen/TryExecute/TryExecute<T>

SQLMagic can attempt to execute a query for you and, instead of throwing an exception, will simply return a Boolean value that indicates success, and use an out parameter to store your result:

// Variables
SqlResultWithDataSet oResult;
if (oSql.TryOpen("SELECT * FROM tbl WHER tbl.id = 1", out oResult))
{
    // This fails because of syntax   ^
}
else
{
    // Because it failed, you can handle the exception here!
    MessageBox.Show(oResult.Exception.ToString());
}

What's Next?

There's still a few things I want to accomplish with SQLMagic. For starters, a Fluent-like interface would be pretty nifty:

// This would be somewhat
var oResult = 
    new SqlMagic("connectionString")
    .Select("column1", "column2")
    .From("tbl")
    .Where("x", Operators.GreaterThan, "1")
    .And("y", Operators.LessThan, "2")
    .Execute();

However that hasn't been fully considered because I don't want to tread on or replace what LINQ-To-SQL offers.

I've also started on an observer class that records its observations of various commands and connection activity. It lives inside SQLMagic's main class as a private, static class. That is subject to change, though.

Breaking Changes

This library could undergo breaking changes to either incorporate new functionality or to normalize namespaces. This is just a small warning.

Points of Interest

async/await was a little weird to implement at first. I kept having programs lock up on me. Context capturing is difficult sometimes.

My co-worker has been a DBA before and he helped me make sure the public facing API made sense, and has been communicating with me what features I should add, or what seem useful.

SQLMagic also has a dependancy on a NuGet package: The immutable collections library by Microsoft.

History

4/1/2014: Initial Publish 1.0