92

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.

Liam
  • 27,717
  • 28
  • 128
  • 190
Redracer68
  • 958
  • 1
  • 7
  • 12
  • 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#? – Nate Feb 11 '14 at 17:56

3 Answers3

175

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();
    }
}
The Guy with The Hat
  • 10,836
  • 8
  • 57
  • 75
Nate
  • 30,286
  • 23
  • 113
  • 184
  • 1
    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'. – Redracer68 Feb 11 '14 at 18:49
  • 1
    @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]` – Nate Feb 11 '14 at 19:33
  • 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. – Redracer68 Feb 11 '14 at 21:13
  • 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? – Redracer68 Feb 11 '14 at 21:13
  • @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 Feb 11 '14 at 23:57
  • @Nate 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! – Redracer68 Feb 12 '14 at 01:34
  • @Nate without knowing a database can we execute sql Script using sqlcmd – Anjali Mar 07 '14 at 17:47
  • @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?"](http://meta.stackexchange.com/a/88633/238586): "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." – The Guy with The Hat Mar 28 '14 at 14:48
  • @Anjali Yes you can call SqlCmd.exe directly from C#, you would need to look into [Process.Start()](http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start(v=vs.110).aspx) – Nate Apr 09 '14 at 14:31
  • 6
    Is there any reason for the _using_ on SqlConnection but not on SqlDataReader? – Fa773N M0nK Apr 21 '16 at 11:29
  • 5
    @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: http://stackoverflow.com/questions/3386770/using-on-sqldatareader – Nate Apr 21 '16 at 13:37
  • 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. – Andrew Apr 12 '18 at 15:13
  • 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"`? – Zizzipupp Oct 14 '19 at 16:13
9

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

  }

}
Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
4

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 ""

Kaspars Ozols
  • 6,967
  • 1
  • 20
  • 33
  • 1
    Wow that was quick! And it's exactly what I was looking for! – Redracer68 Feb 11 '14 at 17:56
  • 1
    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. – Redracer68 Feb 11 '14 at 17:57
  • 1
    @Redracer68 - in the case you mentioned above, you could just concatenate the `%name%` value from the `.Text` property of the textbox. – Brian Feb 11 '14 at 17:59
  • 5
    I wouldn't suggest directly concatenating user input unless you want to be vulnerable to SQL injection. – Curtis Rutland Feb 11 '14 at 17:59
  • 1
    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! – Redracer68 Feb 11 '14 at 18:00
  • 1
    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.** – Kaspars Ozols Feb 11 '14 at 18:00
  • 1
    @CurtisRutland is right. You really souldn't do that. Especially if you are just starting using C#. Learn how to use ADO.NET ASAP. – Crono Feb 11 '14 at 18:01
  • 1
    Updated my answer with remark about security vulnerability. – Kaspars Ozols Feb 11 '14 at 18:04
  • 1
    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. – Redracer68 Feb 11 '14 at 18:06
  • 1
    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. – Blue Granny Mar 06 '18 at 12:15