Csharp/CSharp Tutorial/ADO.Net/SqlCommand Insert

Command NonQuery

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class CommandNonQuery
   {
       static void Main()
       {
           SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
           string sqlqry = @"select count(*) from employees";
           string sqlins = @"insert into employees(firstname,lastname)values("Z", "Z")";
           string sqldel = @"delete from employees where firstname = "Z" and lastname = "Z"";
           SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
           SqlCommand cmdnon = new SqlCommand(sqlins, conn);
           try
           {
               conn.Open();
               Console.WriteLine("Before INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
               Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
               cmdnon.ExecuteNonQuery();
               Console.WriteLine("After INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
               cmdnon.rumandText = sqldel;
               Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
               cmdnon.ExecuteNonQuery();
               Console.WriteLine("After DELETE: Number of employees {0}\n", cmdqry.ExecuteScalar());
           }
           catch (SqlException ex)
           {
               Console.WriteLine(ex.ToString());
           }
           finally
           {
               conn.Close();
               Console.WriteLine("Connection Closed.");
           } 
       }
   }</source>

Execute nonquery to insert a record (row)

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

  static void Main()
  {
     SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
     string sqlqry = @"select count(*) from employee ";
     string sqlins = @"insert into employee (firstname,lastname)values("Z", "Z")";
     string sqldel = @"delete from employee where firstname = "Z" and lastname = "Z"";
     SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
     SqlCommand cmdnon = new SqlCommand(sqlins, conn);
     try
     {
        conn.Open();
        Console.WriteLine("Before INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());
        Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
        cmdnon.ExecuteNonQuery();
        Console.WriteLine("After INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());
        cmdnon.rumandText = sqldel;
        Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
        cmdnon.ExecuteNonQuery();
        Console.WriteLine("After DELETE: Number of employee {0}\n", cmdqry.ExecuteScalar());
     }
     catch (SqlException ex)
     {
        Console.WriteLine(ex.ToString());
     }
     finally
     {
        conn.Close();
        Console.WriteLine("Connection Closed.");
     }
  }

}</source>

how to execute multiple SQL statements using a SqlCommand object

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class ExecuteMultipleSQL {

   public static void Main() {
       SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
       SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
       mySqlCommand.rumandText =
         "INSERT INTO Customers (CustomerID, CompanyName) " +
         "VALUES ("J5COM", "Jason 5 Company");" +
         "SELECT CustomerID, CompanyName " +
         "FROM Customers " +
         "WHERE CustomerID = "J5COM";" +
         "UPDATE Customers " +
         "SET CompanyName = "ACompany" " +
         "WHERE CustomerID = "J5COM";" +
         "SELECT CustomerID, CompanyName " +
         "FROM Customers " +
         "WHERE CustomerID = "J5COM";";
       mySqlConnection.Open();
       SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
       do {
           while (mySqlDataReader.Read()) {
               Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
               Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
           }
           Console.WriteLine(""); // visually split the results
       } while (mySqlDataReader.NextResult());
       mySqlDataReader.Close();
       mySqlConnection.Close();
   }

}</source>

Inserting Data Using SQLStatements

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

 static void Main(string[] args)
 {
   SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   MyConnection.Open();
   String MyString = @"INSERT INTO Employee(ID, FirstName, LastName) VALUES(2, "G", "M")";
   SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
   MyCmd.ExecuteScalar();
   MyConnection.Close();
 }

}</source>