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.

Summarize

Do you want to execute your existing batch file, or are you looking to connect to the database and run your query directly in C#? – Lavoisier

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

Lavoisier

nate, I think I may be leaning this direction now. So far it seems like it will work, however I get an unhandled exception when running it. Invalid object namd 'dbo.TPatientRaw'. – Summarize

@Summarize 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] – Lavoisier

Got it! Had to give a database to use as there is more than one! Works like a charm. Even pointed it to output the result to a richtextbox. – Summarize

Another question on this though before I select it as the answer. Would this easily be adapted to an odbccommand? Or would that be a totally different subject? – Summarize

@Summarize 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. – Lavoisier

@Lavoisier Nevermind on that lol. The reason I wanted ODBCcommand instead was because I may need to run this from a machine that doesn't house the SQL server. But I found I can just use sqldatasourceenumerator and have it ask which sql location to use. That works just like I need it! Thanks for all the help guys! – Summarize

@Lavoisier without knowing a database can we execute sql Script using sqlcmd – Beggs

@FuzzyAmi I appreciate that you are trying to help by fixing someone else's code, but at Stack Exchange that is generally frowned upon. According to an answer to "How far can I refactor the code in someone else's question?": "reformatting the code so that it indents nicely and so on should be the end of the edit...changing code is a minefield...an editor could add an unexpected bug into the code listing...you could be inadertantly answering the question by your change." – Taxdeductible

@Beggs Yes you can call SqlCmd.exe directly from C#, you would need to look into Process.Start() – Lavoisier

Is there any reason for the using on SqlConnection but not on SqlDataReader? – Bosky

@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: https://mcmap.net/q/183317/-using-on-sqldatareader – Lavoisier

This works fine, except for that \P in connectionString: you should either use \\P or start the string with a @. I also changed the way to get the fields from the reader; I used GetInt32, GetString, etc., which saves you from casting and is a bit faster. – Daw

What if one of the parameters in the query comes form a web form created inside Visual Studio? Is there a way for you to assign string myparam = txtThisCell.Text and then to say string queryString = "SELECT * FROM Table WHERE key=myparam"? – Somerville

Answer 2

Something like this should suffice, to do what your batch file was doing (dumping the result set as semi-colon delimited text to the console):

// 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%' "

DataTable dt            = new DataTable() ;
int       rows_returned ;

const string credentials = @"Server=(localdb)\.\PDATA_SQLEXPRESS;Database=PDATA_SQLEXPRESS;User ID=sa;Password=2BeChanged!;" ;
const string sqlQuery = @"
  select tPatCulIntPatIDPk ,
         tPatSFirstname    ,
         tPatSName         ,
         tPatDBirthday
  from dbo.TPatientRaw
  where tPatSName = @patientSurname
  " ;

using ( SqlConnection connection = new SqlConnection(credentials) )
using ( SqlCommand    cmd        = connection.CreateCommand() )
using ( SqlDataAdapter sda       = new SqlDataAdapter( cmd ) )
{
  cmd.CommandText = sqlQuery ;
  cmd.CommandType = CommandType.Text ;
  connection.Open() ;
  rows_returned = sda.Fill(dt) ;
  connection.Close() ;
}

if ( dt.Rows.Count == 0 )
{
  // query returned no rows
}
else
{

  //write semicolon-delimited header
  string[] columnNames = dt.Columns
                           .Cast<DataColumn>()
                           .Select( c => c.ColumnName )
                           .ToArray()
                           ;
  string   header      = string.Join("," , columnNames) ;
  Console.WriteLine(header) ;

  // write each row
  foreach ( DataRow dr in dt.Rows )
  {

    // get each rows columns as a string (casting null into the nil (empty) string
    string[] values = new string[dt.Columns.Count];
    for ( int i = 0 ; i < dt.Columns.Count ; ++i )
    {
      values[i] = ((string) dr[i]) ?? "" ; // we'll treat nulls as the nil string for the nonce
    }

    // construct the string to be dumped, quoting each value and doubling any embedded quotes.
    string data = string.Join( ";" , values.Select( s => "\""+s.Replace("\"","\"\"")+"\"") ) ;
    Console.WriteLine(values);

  }

}

Tersanctus

Answer 3

IMPORTANT NOTE: You should not concatenate SQL queries unless you trust the user completely. Query concatenation involves risk of SQL Injection being used to take over the world, ...khem, your database.

If you don't want to go into details how to execute query using SqlCommand then you could call the same command line like this:

string userInput = "Brian";
var process = new Process();
var startInfo = new ProcessStartInfo();
startInfo.WindowStyle = ProcessWindowStyle.Hidden;
startInfo.FileName = "cmd.exe";
startInfo.Arguments = string.Format(@"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 = '{0}' """, userInput);

process.StartInfo = startInfo;
process.Start();

Just ensure that you escape each double quote " with ""

Griseldagriseldis

Wow that was quick! And it's exactly what I was looking for! – Summarize

Quick question though. How would I add user input to this? Say from a textbox named GFIDuserinput in the same form? In the actual sqlcmd.exe string %name% is what needs to be supplied. – Summarize

@Summarize - in the case you mentioned above, you could just concatenate the %name% value from the .Text property of the textbox. – Garris

I wouldn't suggest directly concatenating user input unless you want to be vulnerable to SQL injection. – Godparent

That isn't really a concern right now. These are closed-off networks with no real security threats like that. It's cool though. I should be able to take it from here. Thanks a ton! – Summarize

Updated my answer. Although you should concatenate SQL queries only if the user can be trusted and this is made for internal use for system administrators or something. – Griseldagriseldis

@CurtisRutland is right. You really souldn't do that. Especially if you are just starting using C#. Learn how to use ADO.NET ASAP. – Macswan

Updated my answer with remark about security vulnerability. – Griseldagriseldis

I know, This tool will be used by my partner and I in a closed environment as a troubleshooting tool. I'm learning all that I can about C# for the future but needed this quick and dirty :) You guys are amazing. – Summarize

I think the note should be moved to the start of the answer and not the end. Readers should first be told of the warning and then shown the solution once knowing it isn't safe. – Taker