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

Starting a process of sqlcmd.
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.

are you expecting a record set to be returned to your application for use, or are you just trying to run a query and move on? — TheDevOpsGuru, Sep 17 '10 at 13:08
Do you want to specifically avoid connection pooling, or are you just trying to follow best practices? — D'Arcy Rittich, Sep 17 '10 at 13:18
Sorry for not being clear. No the query is an insert and the result is not needed. — Ash Burlaczenko, Sep 17 '10 at 13:18
Late response. sqlcmd.exe runs much faster than SqlCommand. sqlcmd.exe has option to create a csv file. From c# you can execute sqlcmd.exe using Process Class. The when Process completes you can read the csv file into c#. — jdweng, Feb 14 '18 at 11:56

score 31 · Accepted Answer · edited Nov 28 '11 at 10:03

31

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);

edited Nov 28 '11 at 10:03

SoftDev

1,084
9
13

answered Sep 17 '10 at 13:08

Justin Niessner

242,243
40
408
536

It depends whether it will keep the connection alive for a short time. If you are not careful it might keep the connection and even not a single connection but many of them open through the lifetime of the whole application. See my answer for details. – Darin Dimitrov Sep 17 '10 at 13:14
How can I achieve with what I am trying to do with your code: http://stackoverflow.com/questions/23617661/why-the-sql-command-is-not-executing – SearchForKnowledge May 12 '14 at 19:56

score 7 · Answer 2 · edited Oct 08 '22 at 10:12

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.

using System.Data.SqlClient

...

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]));
            }
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.StackTrace);
            Console.WriteLine(e.Message);
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

Darin Dimitrov · Answer 3 · 2010-09-17T13:15:52.887

4

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();
}

edited Sep 17 '10 at 13:15

answered Sep 17 '10 at 13:09

Darin Dimitrov

1,023,142
271
3,287
2,928

Nitpick: To the best of my knowledge, Windows has no process forking. – Sebastian Mach Sep 12 '17 at 12:52

score 0 · Answer 4 · answered Sep 17 '10 at 13:08

I think SqlCommand is a clear winner because you don't need to wire up a different process. You can close the database connection as soon as you're done with it.

And then you can also distribute the application to machines that don't have sqlcmd available.

score 0 · Answer 5 · answered Sep 17 '10 at 13:10

I think SqlCommand is a good idea, but keep in mind this class is only available when connecting to SQL Server. You'll need some other type of Command/Connection classes if you're dealing with Oracle, or an OleDb connection to some other database. All of the data command objects inherit from DbCommand, so I would read up on that.

SQL/C# - Best method for executing a query

5 Answers5

Linked