SQL Commands with C#

As a first post, I thought it would be fitting to discuss how to interact with vanilla SQL commands in C#. Firstly, I want to praise .NET (well technically ADO.NET) for the absolute ease of running SQL commands through its library.

Taking a quick look at .NET's reference source, we can see they that really value SQL integrations:

Without further adieu, let's get into the code. For our implementation, we will mainly be using System.Data.SqlClient.SqlCommand. This class is not included in the default .NET namespaces, so you will have to add its namespace yourself.

Add using the Dotnet CLI:

dotnet add package System.Data.SqlClient

Don't forget to include your using statements:

using System.Data;
using System.Data.SqlClient;

Let's also add using System; just to be safe.

Next, we have to connect to a database (obviously). Depending on the circumstances, the information needed for this step varies. But generally speaking, you will need the following fields:

• User ID
• Password/Pwd (they mean the same thing)
• Data Source/Server/Address/Addr/Network Address (they all mean the same thing too)

Using this information, we can specify a connection:

SqlConnection connection = new SqlConnection(@"user id=yourUsername;password=yourPassword;server=yourServer;database=yourDirectory;Trusted_Connection=True;");
//when specifying a directory, consider using a verbatim string literal to ignore escapes

A simple method will allow us to finish creating the connection:

connection.Open();

We have now reached the fun part. We can finally write SQL commands to the connection. I hope you are as excited as I am!

Initialization and field assignment of the command:

SqlCommand command = new SqlCommand();
command.CommandTimeout = 60;
//a command timeout of 60 is recommended
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "yourCommand";

Time to send the command on its way. (they grow up so fast!)

command.ExecuteScalar();

Reading the command response (if any) is as simple as:

command.ExecuteReader();

This returns a System.Data.SqlClient.SqlDataReader object, which we can then use to easily read values.

Now me being me, I wrote a class encapsulating this code for convenience:

using System;
using System.Data;
using System.Data.SqlClient;

public static class Command
{
    private static SqlCommand command = new SqlCommand();

    public static SqlDataReader Send(string commandtext, SqlConnection connection, int timeout = 60)
    {
        command.CommandTimeout = timeout;
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = commandtext;
        command.ExecuteScalar();
        return command.ExecuteReader();
    }
}

Now we can interact with SQL commands in a simple way. Feel free to copy this code for your own use.

Comments

[Zackere]

I dont think you should ever use this with Linq around, which gets translated to secure SQL queries on the fly

[William Olsen]

I find that the SQLCommand class is simpler to use than the LINQ to SQL API. Plus they both end up at the DataReader class nonetheless. Sounds like an interesting method to use SQL: will post about when finished researching.