CODEWITHSUNDEEP
c#sqlsql-server-express
Ash Burlaczenko
Ash Burlaczenko

Reputation: 25465

SQL/C# - Best method for executing a query

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.

Upvotes: 17

Views: 82487

Answers (5)

Shaun Mason
Shaun Mason

Reputation: 797

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

Upvotes: 8

Justin Niessner
Justin Niessner

Reputation: 245449

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

Upvotes: 32

Darin Dimitrov
Darin Dimitrov

Reputation: 1039100

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

Upvotes: 4

Steve Danner
Steve Danner

Reputation: 22158

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.

Upvotes: 0

Adam Lear
Adam Lear

Reputation: 38778

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.

Upvotes: 0

Related Questions