How to directly execute SQL query in C#?

Tuesday, February 11, 2014 by Nate Bross

Ok, I have an old batch file that does exactly what I need. However, with out new administration we can’t run the batch file anymore so I need to start up with C#.

I’m using Visual Studio C# and already have the forms set up for the application I need to build. (I’m learning as I go)

Here is what I need to accomplish in C# (This is the batch guts)
sqlcmd.exe -S .\PDATA_SQLEXPRESS -U sa -P 2BeChanged! -d PDATA_SQLEXPRESS  -s ; -W -w 100 -Q "SELECT tPatCulIntPatIDPk, tPatSFirstname, tPatSName, tPatDBirthday  FROM  [dbo].[TPatientRaw] WHERE tPatSName = '%name%' "
Basically it uses SQLCMD.exe with the already existing datasource called PDATA_SQLExpress.
I’ve searched and gotten close but I’m still at a loss on where to start.

I posted the following answer, which was chosen as the accepted answer and received 184 upvotes:

To execute your command directly from within C#, you would use the SqlCommand class.

Quick sample code using paramaterized SQL (to avoid injection attacks) might look like this:

string queryString = "SELECT tPatCulIntPatIDPk, tPatSFirstname, tPatSName, tPatDBirthday  FROM  [dbo].[TPatientRaw] WHERE tPatSName = @tPatSName";
string connectionString = "Server=.\PDATA_SQLEXPRESS;Database=;User Id=sa;Password=2BeChanged!;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
            reader["tPatCulIntPatIDPk"], reader["tPatSFirstname"]));// etc
        }
    }
    finally
    {
        // Always call Close when done reading.
        reader.Close();
    }
}

Notable comments

Nate (5 upvotes): @Fa773NM0nK No good reason beyond its a sample and I forgot. For anyone wondering, here’s a good read on why its a good idea: stackoverflow.com/questions/3386770/using-on-sqldatareader

Fa773N M0nK (6 upvotes): Is there any reason for the using on SqlConnection but not on SqlDataReader?

Nate (0 upvotes): @Anjali Yes you can call SqlCmd.exe directly from C#, you would need to look into Process.Start()

Nate (0 upvotes): @Redracer68 Why would you want OdbcCommand? If your database is SQL Server, the SqlCommand (and SqlConnection) are the best classes to use; while you can access SQL Server via OdbcCommand and even OleDbCommand the SqlCommand is probably best. What’s your usecase for using ODBC instead? That said, you could switch the above code to OdbcConnection and OdbcCommand setup an ODBC DSN and I don’t see why it would not work.

Nate (1 upvotes): @Redracer68 I suspect an issue with the SQL query. Try using the name of the table in your query, TPatientRaw instead of the full [dbo].[Table]

Originally posted on Stack Overflow — 184 upvotes (accepted answer). Licensed under CC BY-SA.

How to directly execute SQL query in C#?

Top Posts