How to directly execute SQL query in C#?

c# sql select query example
c# execute sql query and get result
how to execute sql query in c# using linq
how to execute a query from c#
c# sql query with parameters
execute dynamic sql in c#
c# execute sql command with parameters
writing sql queries in c#

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.

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

Executing SQL query from C# code, There are multiple ways to get Data out of your Database. ExecuteScalar. if you have one result field you can use string Command = "SELECT  Those that cannot be produced generate run-time exceptions. For more information, see SQL-CLR Type Mapping. In cases where a LINQ to SQL query is insufficient for a specialized task, you can use the ExecuteQuery method to execute a SQL query, and then convert the result of your query directly into objects. Example. In the following example, assume that the data for the Customer class is spread over two tables (customer1 and customer2). The query returns a sequence of Customer objects.

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

  }

}

Executing a SQL query with C#, The simplest way to insert into a SQL Server database: string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB  using ( var ctx = new SchoolDBEntities ()) { var student = ctx.Students .SqlQuery ( "Select * from Students where StudentId=@id", new SqlParameter ( "@id", 1 )) .FirstOrDefault (); } You can also use Database.ExecuteSqlCommand () insted of SqlQuery () in executing database commands, such as the Insert, Update and Delete command.

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

How to: Directly Execute SQL Queries, How to: Directly Execute SQL Queries. 03/30/ Northwnd db = new Northwnd(@​"c:\northwnd.mdf"); IEnumerable<Customer> results = db. You need to specify the type to map to from the query results. You can use a System.Type object instead of statically specifying it as a generic type parameter: var results = db.ExecuteQuery(typeof(Customer), "sql query ");

SqlCommand.ExecuteNonQuery Method (System.Data.SqlClient , Use C# to query data from a SQL Server database using ADO. may break your code and, worse, give hackers an opportunity to get direct access to your database! Person table and use ExecuteReader to get our results. While the apostrophe is all good in C# it ends a string in SQL. So the query you’ll be sending to SQL is SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = D'Artgnan. Go to SQL server Management Studio, open a new query window and try to run that exact query.

Using C# to connect to and query from a SQL database, NET code for executing the above query against the database In this demo, the project directory is "C:\temp\demos\TestApp2-SELECT". While the apostrophe is all good in C#, it ends a string in SQL. So the query you’ll be sending to SQL is SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = D'Artgnan. Go to SQL server Management Studio, open a new query window and try to run that exact query.

Querying SQL Server Tables from .NET, You will have to split the query and execute command one by one. It is not that hard. Essentially, you accumulate lines until you find a line with  I am working on a ASP.net Core project. In that project, I need to execute an SQL string query and retrieve the result to a custom model. I tried to retrieve the query result using context.Database.

Comments
  • 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, 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 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]
  • 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.
  • Is there any reason for the using on SqlConnection but not on SqlDataReader?
  • @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
  • Wow that was quick! And it's exactly what I was looking for!
  • 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 - in the case you mentioned above, you could just concatenate the %name% value from the .Text property of the textbox.
  • I wouldn't suggest directly concatenating user input unless you want to be vulnerable to SQL injection.
  • 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!