How to directly execute SQL query in C#?

Question

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.

Answer 1

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