Introduction to SqlDatabaseCommand

Since some years, I’ve tried to write some performing SQL Queries but also with easy way. In the .NET beginning, I’ve tried System.Data.SqlClient with DataAdapter and DataSets. Next, I’ve tried the Linq to SQL project. And next, I’ve tried Entity Framework. This last framework is the most used at this moment, but when I need some performing requests, it’s very difficult to optimize SQL requests generated by EF.

So, I decided to create a very light toolkit to use the full SQL features and to retrieve quickly all data: SqlDatabaseCommand. This class inherits from a base class called DatabaseCommandBase that contains all main features using only System.Data namespace (DbCommand, DbConnection, …). So, you can inherit this base class to extend the toolkit to other providers like Oracle, SqlLite, etc.

This project is Open Source and hosted on Github: https://github.com/Apps72/Dev.Data

Samples

For example, if you have already wrote a class (Employee) with all properties mapped to the EMP table. You can use this code and the ExecuteTable</strong> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP ");
    var emps = cmd.ExecuteTable<Employee>();
}

The following code can be used to retrieve only the first data row, via the ExecuteRow</strong> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE EMPNO = 7369 ");
    var emp = cmd.ExecuteRow<Employee>();
}

If you know that your request returns only one item, use the ExecuteScalar method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
    int count = cmd.ExecuteScalar<int>();
}

You can also add parameters in your queries and send parameters easily.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE HIREDATE = @HireDate ");
    cmd.Parameters.AddValues(new { HireDate = new DateTime(1980, 12, 17) });
    var emps = cmd.ExecuteTable<Employee>();
}

Find more features to manage Transactions, Logging, customization of your data results, extensions, data injection for Unit Tests and Best practices on https://github.com/Apps72/Dev.Data.

NuGet

To use this toolkit in your .NET projects, add a NuGet Reference to Apps72.Dev.Data.

• First, search SqlDatabaseCommand in the NuGet Package Manager.
• Next, select Apps72.Dev.Data (the second NuGet package is to include in a SQL Server CLR Project).