Csharp/C Sharp/Database ADO.net/Insert

Bind parameters to insert command

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

  class PropagateAdds{
     static void Main() {
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        string qry = @"select * from employee";
        string upd = @"insert into employee(firstname,lastname)values(@firstname,@lastname)";
        SqlConnection conn = new SqlConnection(connString);
        try{
           SqlDataAdapter da = new SqlDataAdapter();
           da.SelectCommand = new SqlCommand(qry, conn);
           DataSet ds = new DataSet();   
           da.Fill(ds, "employee");
           DataTable dt = ds.Tables["employee"];
           DataRow newRow = dt.NewRow();
           newRow["firstname"] = "R";
           newRow["lastname"] = "B";
           dt.Rows.Add(newRow);
           foreach (DataRow row in dt.Rows){
              Console.WriteLine(
                 "{0} {1}",
                 row["firstname"].ToString().PadRight(15),
                 row["lastname"].ToString().PadLeft(25));
           }
           // Update employees
           SqlCommand cmd = new SqlCommand(upd, conn);
           cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname");
           cmd.Parameters.Add("@lastname",  SqlDbType.NVarChar, 20, "lastname");
           da.InsertCommand = cmd;
           da.Update(ds, "employee");
        } catch(Exception e) {
           Console.WriteLine("Error: " + e);
        } finally {
           conn.Close();
        }
     }  
  }

      </source>

Insert Data Using SqlCommandBuilder

<source lang="csharp"> /*

* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/

using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET {

   public class InsertingDataUsingCommandBuilder
   {
       static void Main(string[] args)
       {
           SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
           SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, Contact, Email FROM Test", MyConnection);
           SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter);
           DataSet MyDataSet = new DataSet();
           MyDataAdapter.Fill(MyDataSet);
           DataRow MyRow = MyDataSet.Tables[0].NewRow();
           MyRow["ID"] = 200;
           MyRow["Contact"] = "Greg";
           MyRow["Email"] = "MacBeth";
           MyDataSet.Tables[0].Rows.Add(MyRow);
           MyDataAdapter.Update(MyDataSet);
       }
   }

}

      </source>

Inserte Data Using SQL Statements

<source lang="csharp"> /*

* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/

using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET {

   public class InsertingDataUsingSQLStatements
   {
       static void Main(string[] args)
       {
           SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
           MyConnection.Open();
           String MyString = @"INSERT INTO Test(ID, Contact, Email) VALUES(2, "Greg", "MacBeth")";
           SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
           MyCmd.ExecuteScalar();
           MyConnection.Close();
       }
   }

}

      </source>

Run an INSERT statement with parameters

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

   public static void Main() {
       SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
       mySqlConnection.Open();
       SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
       mySqlCommand.rumandText =
         "INSERT INTO Customers (" +
         "  CustomerID, CompanyName, ContactName" +
         ") VALUES (" +
         "  @CustomerID, @CompanyName, @ContactName" +
         ")";
       mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
       mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40);
       mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30);
       mySqlCommand.Parameters["@CustomerID"].Value = "J4COM";
       mySqlCommand.Parameters["@CompanyName"].Value = "J4 Company";
       mySqlCommand.Parameters["@ContactName"].IsNullable = true;
       mySqlCommand.Parameters["@ContactName"].Value = DBNull.Value;
       mySqlCommand.ExecuteNonQuery();
       Console.WriteLine("Successfully added row to Customers table");
       mySqlConnection.Close();
   }

}

</source>

Use SQL command to insert data into database table

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

  class CommandExampleCreateDb
  {
     static void Main() 
     {
        SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
        SqlCommand nonqueryCommand = thisConnection.CreateCommand();
        try 
        {
           thisConnection.Open();
           nonqueryCommand.rumandText = "CREATE DATABASE MyDb";
           Console.WriteLine(nonqueryCommand.rumandText);
           nonqueryCommand.ExecuteNonQuery();
           Console.WriteLine("Database created, now switching");
           thisConnection.ChangeDatabase("MyDb");
           nonqueryCommand.rumandText = "CREATE TABLE MynfexTable (COL1 integer)";
           Console.WriteLine(nonqueryCommand.rumandText);
           Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
           nonqueryCommand.rumandText = "INSERT INTO MynfexTable VALUES (99)";
           Console.WriteLine(nonqueryCommand.rumandText);
           Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
        
        } catch (SqlException ex) {
        
           Console.WriteLine(ex.ToString());
        
        } finally {  
        
           thisConnection.Close();
           Console.WriteLine("Connection Closed.");
        
        }
     }
  }

      </source>