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<T> 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<T> 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).

SqlDatabaseCommand-Nuget

Advertisements
Posted in General, SQL Server, SqlDatabaseCommand
2 comments on “Introduction to SqlDatabaseCommand
  1. […] with properties, methods and a live cycle. For these reasons, I’ve published the project SqlDatabaseCommand: you create an object SqlDatabaseCommand (eg. in a DataService), you define properties and you […]

  2. […] dernier toolkit présenté est SqlDatabaseCommand que j’ai déjà décrit dans un précédent article et que vous pouvez télécharger gratuitement via […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 565 other followers

Who I am?






%d bloggers like this: