How to Directly Execute SQL Query in C#

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

Executing a SQL query with C#

The simplest way to insert into a SQL Server database:

string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database2.mdf;Integrated Security=True";

string commandText = "INSERT INTO MyTable (ID, Name, Address) VALUES (10, 'Bob', '123 Main Street');";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

As long as commandText is a working query, it should insert a row. It would be better to use parameters for your values instead of hard coding them like I did here - that avoids SQL injection attacks and other potential problems. You can search Google for that (or the question you are asking now) and find tons of resources to help you.

If you need more specific help, post details such as what is actually happening when you try to run your code - are you getting an exception?

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 [UserName] FROM [aspnet_Users];";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    myConnection.Open();
    using (SqlCommand myCommand = new SqlCommand(Command, myConnection))
    {
        string Result = (string)myCommand.ExecuteScalar(); // returns the first column of the first row
    }
}

SqlDataAdapter

if you expect multiple rows / columns you can load it into a DataTable by

string Command = "SELECT [UserName] FROM [aspnet_Users]";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(Command, myConnection))
    {
        DataTable dtResult = new DataTable();
        myDataAdapter.Fill(dtResult);
    }
}

SqlDataReader

Another solution is the SQLDataReader. E.g. if you want to load all rows of a column into a List. This has less overhead than the DataAdapter.

List<string> Result = new List<string>();
string Command = "SELECT [UserName] FROM [aspnet_Users];";
using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();
    using (SqlCommand cmd = new SqlCommand(Command, mConnection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Result.Add((string)reader[0]);
            }
        }
    }
}

C# Run SQL Query on another machine

I would do it with an application that does something like this.

using MySql.Data.MySqlClient;

namespace WinformFiddle
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

    private void Form1_Load(object sender, EventArgs e)
    {
        MySqlConnection conn = null;
        try
        {
            conn = new MySqlConnection();
            conn.ConnectionString = "server=myserver.mydomain.edu;user id=MyUserWithAccessUsername;password=MyUserWithAccessPassword;persistsecurityinfo=True;database=roomscheduling;Integrated Security=False";
            conn.Open();

            MySqlCommand selCmd = new MySqlCommand("SELECT ...", conn);
            MySqlDataAdapter da = new MySqlDataAdapter(selCmd);
            ....

This uses MySql, but the exact equivalent can be done with any DB provider. The part of the connection string that says Integrated Security=False tells the system not to use the current user's window credentials but rather what is being provided in the connection string.

The same thing but using Sql Server, I think, would be like this (it's been a while since I use sql server...

using System.Data.SqlClient;

namespace WinformFiddle
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

    private void Form1_Load(object sender, EventArgs e)
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = "server=myserver.mydomain.edu;user id=MyUserWithAccessUsername;password=MyUserWithAccessPassword;persistsecurityinfo=True;database=roomscheduling;Integrated Security=False";
            conn.Open();

            SqlCommand selCmd = new SqlCommand("SELECT ...", conn);
            SqlDataAdapter da = new SqlDataAdapter(selCmd);
            ....

Execute a SQL query from C# console application and get its result written in a file

ExecuteNonQuery is used for update,insert and delete, and returns a integer.
You can simply save it to a file using File.WriteAllText() method.

If your query are select, consider instead using ExecuteReader() method.
So iterate over the response and write a line for each row returned.

Select query to get data from SQL Server

SqlCommand.ExecuteNonQuery Method

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

SqlCommand.ExecuteScalar Method
Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So to get no. of statements returned by SELECT statement you have to use ExecuteScalar method.

Reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

So try below code:

SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=");
conn.Open();

SqlCommand command = new SqlCommand("Select id from [table1] where name=@zip", conn);
command.Parameters.AddWithValue("@zip","india");
 // int result = command.ExecuteNonQuery();
using (SqlDataReader reader = command.ExecuteReader())
{
  if (reader.Read())
  {
     Console.WriteLine(String.Format("{0}",reader["id"]));
   }
}

conn.Close();

How to execute an .SQL script file using c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

public partial class ExcuteScript : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

        SqlConnection conn = new SqlConnection(sqlConnectionString);

        Server server = new Server(new ServerConnection(conn));

        server.ConnectionContext.ExecuteNonQuery(script);
    }
}

---

Related Topics