How to directly execute SQL query in C#?
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();
}
}
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);
}
}
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 ""