DEV Community

William Olsen
William Olsen

Posted on

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:
SQL results

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
Enter fullscreen mode Exit fullscreen mode

Don't forget to include your using statements:

using System.Data;
using System.Data.SqlClient;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

connection.Open();
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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

command.ExecuteScalar();
Enter fullscreen mode Exit fullscreen mode

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

command.ExecuteReader();
Enter fullscreen mode Exit fullscreen mode

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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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

Discussion (2)

Collapse
zackere profile image
Zackere

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

Collapse
pixelatedlagg profile image
William Olsen Author

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.