Delete data from database table using SqlDataAdapter

   


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