SQL/C# - Best method for executing a query

Question

I need to execute a sql query from within a c# class. I have thought of 2 options

1. Starting a process of sqlcmd.
2. Using a SqlCommand object.

My question is which would be the better way? It's important that the solution only holds a connection to the server for a short time.

I'm open to other ideas if the above aren't good.

Thanks in advance.

Solution 1

Use a SqlCommand. This code will only keep the connection alive for a very short period of time (as long as your query is performant):

DataTable results = new DataTable();

using(SqlConnection conn = new SqlConnection(connString))
    using(SqlCommand command = new SqlCommand(query, conn))
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
           dataAdapter.Fill(results);

Solution 2

From MSDN:

The following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. The example reads through the data, writing it to the console. Finally, the example closes the SqlDataReader and then the SqlConnection as it exits the Using code blocks.

private static void ReadOrderData(string connectionString)
{
    string queryString = 
        "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(
            queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

Solution 3

It depends. If you don't care about the result of the query forking a process which uses sqlcmd might be OK. If on the other hand you need to control the results it would be better to use ADO.NET. To avoid keeping the connection open for a long time make sure you disable ADO.NET connection pooling by adding Pooling=false to your connection string:

using (var conn = new SqlConnection("Data Source=server;Initial Catalog=somedb;User Id=foo;Password=secret;Pooling=false"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "DELETE FROM foo";
    var result = cmd.ExecuteNonQuery();
}