SQL/C# - Best method for executing a query

Ash Burlaczenko picture Ash Burlaczenko · Sep 17, 2010 · Viewed 75.1k times · Source

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.

Answer

Justin Niessner picture Justin Niessner · Sep 17, 2010

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