Csharp/C Sharp/Database ADO.net/SqlDataAdapter — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 11:42, 26 мая 2010

Add data to database using SqlDataAdapter

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


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


Update database using the SqlDataAdapter

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"]);

  }
}