Csharp/C Sharp/Database ADO.net/SqlDataAdapter

Add data to database using SqlDataAdapter

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

     public static void Main() {
        SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
        SqlDataAdapter thisAdapter = new SqlDataAdapter( 
           "SELECT ID, FirstName FROM Employee", thisConnection);
        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
        DataSet thisDataSet = new DataSet();
        thisAdapter.Fill(thisDataSet, "Employee");
        Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Employee"].Rows.Count);
        DataRow thisRow = thisDataSet.Tables["Employee"].NewRow();
        thisRow["ID"] = "123";
        thisRow["FirstName"] = "Ltd";
        thisDataSet.Tables["Employee"].Rows.Add(thisRow);
        Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Employee"].Rows.Count);
        thisAdapter.Update(thisDataSet, "Employee");
     }

}

      </source>

Delete data from database table using SqlDataAdapter

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

  class PropagateDeletes {
     static void Main() {
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        string qry = @"select * from employee ";
        string del = @"delete from employee where id = @id";
        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"];
           SqlCommand cmd = new SqlCommand(del, conn);
           cmd.Parameters.Add("@id",SqlDbType.Int, 4, "id");
           string filt = @"firstname = "o" and lastname = "B"";
           foreach (DataRow row in dt.Select(filt)) {
              row.Delete();
           }
           da.DeleteCommand = cmd;
           da.Update(ds, "employee");
           foreach (DataRow row in dt.Rows) {
              Console.WriteLine(
                 "{0} {1}",
                 row["firstname"].ToString().PadRight(15),
                 row["lastname"].ToString().PadLeft(25));
           }
        } catch(Exception e) {
           Console.WriteLine("Error: " + e);
        } finally {
           conn.Close();
        }
     }  
  }
          
      </source>

Update database using the SqlDataAdapter

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

 static void Main(string[] args){
        SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
        SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);
        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
        DataSet thisDataSet = new DataSet();
        thisAdapter.Fill(thisDataSet, "Employee");
        Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
        thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";
        thisAdapter.Update(thisDataSet, "Employee");
        Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);

 }

}

      </source>