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:
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, Connection = con
};
var datareader = cmd.ExecuteReader(true);
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:
var nTimeout = 30; var bLog = true;
var oSql = new Sql("Connection String", nTimeout, bLog);
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:
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:
oSql.Execute("sp_GoDoSomethingWithParameters",
CommandType.StoredProcedure,
new SqlParameter("param1", "value!")
);
var datetime = oSql.Execute<DateTime>("SELECT GETDATE()");
var intBack = oSql.Execute<Int32>("SELECT 1");
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:
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:
SqlTransaction oTransaction = oSql.BeginTransaction();
SqlTransaction oTransaction = await oSql.BeginTransactionAsync();
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);
oSql.EndTransaction(oTransaction, true);
await oSql.EndTransactionAsync(oTransaction);
Manual Connection Creation
SQLMagic lets you create connections yourself (remember that SQLMagic has overloads to indicate a SqlConnection object!):
SqlConnection oConnection = oSql.CreateConnection(true);
SqlConnection oConnection = await oSql.CreateConnectionAsync(true);
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:
SqlResultWithDataSet oResult;
if (oSql.TryOpen("SELECT * FROM tbl WHER tbl.id = 1", out oResult))
{
}
else
{
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:
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