Csharp/C Sharp by API/System.Data.SqlClient/SqlDataAdapter

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

new SqlDataAdapter

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

 static void Main(string[] args)
 {
   SqlConnection SConn = new SqlConnection("Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
   SqlDataAdapter da = new SqlDataAdapter("SELECT ID, FirstName, LastName FROM Employee", SConn);
   DataSet ds = new DataSet();
   DataTableMapping custMap = da.TableMappings.Add("Employee", "MyDatabase");
   custMap.ColumnMappings.Add("ID", "Number");
   custMap.ColumnMappings.Add("FirstName", "first name");
   custMap.ColumnMappings.Add("LastName", "last name");
   da.Fill(ds);
 }

}


 </source>


SqlDataAdapter.DeleteCommand

<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>


SqlDataAdapter.Fill

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

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText =
     "SELECT TOP 5 ID, FirstName, LastName " +
     "FROM Employee " +
     "ORDER BY ID";
   SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
   mySqlDataAdapter.SelectCommand = mySqlCommand;
   DataSet myDataSet = new DataSet();
   mySqlConnection.Open();
   Console.WriteLine("Retrieving rows from the Employee table");
   int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Employee");
   Console.WriteLine("numberOfRows = " + numberOfRows);
   mySqlConnection.Close();
   DataTable myDataTable = myDataSet.Tables["Employee"];
   foreach (DataRow myDataRow in myDataTable.Rows)
   {
     Console.WriteLine("ID = " + myDataRow["ID"]);
     Console.WriteLine("First Name = " + myDataRow["FirstName"]);
     Console.WriteLine("Last Name = " + myDataRow["LastName"]);
   }
 }

}


 </source>


SqlDataAdapter.InsertCommand

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

  static void Main(string[] args)
  {
     string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
     string qry = @"select * from employees where country = "UK"";
     string ins = @"insert into employees(firstname,lastname,titleofcourtesy,city,country)
                                   values(@firstname,@lastname,@titleofcourtesy,@city,@country)";
     SqlConnection conn = new SqlConnection(connString);
     try
     {
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = new SqlCommand(qry, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "employees");
        DataTable dt = ds.Tables["employees"];
        DataRow newRow = dt.NewRow();
        newRow["firstname"] = "R";
        newRow["lastname"] = "B";
        newRow["titleofcourtesy"] = "Sir";
        newRow["city"] = "B";
        newRow["country"] = "UK";
        dt.Rows.Add(newRow);
        foreach (DataRow row in dt.Rows)
        {
           Console.WriteLine(
              "{0} {1} {2}",
              row["firstname"].ToString().PadRight(15),
              row["lastname"].ToString().PadLeft(25),
              row["city"]);
        }
        SqlCommand cmd = new SqlCommand(ins, conn);
        cmd.Parameters.Add("@firstname",SqlDbType.NVarChar, 10,"firstname");
        cmd.Parameters.Add("@lastname",SqlDbType.NVarChar,20,"lastname");
        cmd.Parameters.Add("@titleofcourtesy",SqlDbType.NVarChar,25,"titleofcourtesy");
        cmd.Parameters.Add("@city",SqlDbType.NVarChar,15,"city");
        cmd.Parameters.Add("@country",SqlDbType.NVarChar,15,"country");
        da.InsertCommand = cmd;
        da.Update(ds, "employees");
     }
     catch(Exception e)
     {
        Console.WriteLine("Error: " + e);
     }
     finally
     {
        conn.Close();
     }
  }

}

 </source>


SqlDataAdapter.RowUpdated

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

  class SqlDemo {
     static void Main(){
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        SqlConnection cn = new SqlConnection(connString);
        try
        {
           cn.Open();
           SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);
    
           SqlCommandBuilder cb = new SqlCommandBuilder(da);
           DataSet ds = new DataSet();
           da.Fill(ds, 0, 1, "Employee");
           da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
           DataTable dt = ds.Tables["Employee"];
           dt.Rows[0][1] = "T";
           da.Update(ds, "Employee");
           da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);

        } catch (SqlException ex) {
           Console.WriteLine(ex.Message);
        }
        finally
        {
           cn.Close();
        }
     }
     static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     } 
     static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     }
 }
     
  
 </source>


SqlDataAdapter.RowUpdating

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

  class SqlDemo {
     static void Main(){
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        SqlConnection cn = new SqlConnection(connString);
        try
        {
           cn.Open();
           SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);
    
           SqlCommandBuilder cb = new SqlCommandBuilder(da);
           DataSet ds = new DataSet();
           da.Fill(ds, 0, 1, "Employee");
           da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
           DataTable dt = ds.Tables["Employee"];
           dt.Rows[0][1] = "T";
           da.Update(ds, "Employee");
           da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);

        } catch (SqlException ex) {
           Console.WriteLine(ex.Message);
        }
        finally
        {
           cn.Close();
        }
     }
     static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     } 
     static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     }
 }
     
  
 </source>


SqlDataAdapter.SelectCommand

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

 public static void Main() {
   string connectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
   SqlConnection mySqlConnection = new SqlConnection(connectionString);
   string selectString = "SELECT TOP 10 ID, FirstName, LastName FROM Employee ORDER BY ID";
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText = selectString;
   SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
   mySqlDataAdapter.SelectCommand = mySqlCommand;
   DataSet myDataSet = new DataSet();
   mySqlConnection.Open();
   Console.WriteLine("Retrieving rows from the Employee table");
   mySqlDataAdapter.Fill(myDataSet, "Employee");
   mySqlConnection.Close();
   DataTable myDataTable = myDataSet.Tables["Employee"];
   foreach (DataRow myDataRow in myDataTable.Rows)
   {
     Console.WriteLine("ID = "+ myDataRow["ID"]);
     Console.WriteLine("FirstName = "+ myDataRow["FirstName"]);
     Console.WriteLine("LastName = "+ myDataRow["LastName"]);
   }
 }

}


 </source>


SqlDataAdapter.SelectCommand.Parameters.Add

<source lang="csharp"> using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class Form1 : System.Windows.Forms.Form {

   private System.Windows.Forms.Label label1;
   private System.Windows.Forms.TextBox txtStateWanted;
   private System.Windows.Forms.Button btnFind;
   private System.Windows.Forms.DataGrid dataGrid1;
   public Form1() {
       this.label1 = new System.Windows.Forms.Label();
       this.txtStateWanted = new System.Windows.Forms.TextBox();
       this.btnFind = new System.Windows.Forms.Button();
       this.dataGrid1 = new System.Windows.Forms.DataGrid();
       ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
       this.SuspendLayout();
       this.label1.Location = new System.Drawing.Point(62, 241);
       this.label1.Size = new System.Drawing.Size(32, 23);
       this.label1.Text = "State";
       this.txtStateWanted.Location = new System.Drawing.Point(102, 241);
       this.txtStateWanted.Size = new System.Drawing.Size(64, 20);
       this.txtStateWanted.Text = "CA";
       this.btnFind.Location = new System.Drawing.Point(206, 241);
       this.btnFind.Text = "Fill";
       this.btnFind.Click += new System.EventHandler(this.btnFind_Click);
       this.dataGrid1.DataMember = "";
       this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
       this.dataGrid1.Location = new System.Drawing.Point(6, 9);
       this.dataGrid1.Size = new System.Drawing.Size(280, 224);
       this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
       this.ClientSize = new System.Drawing.Size(292, 273);
       this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                         this.label1,
                                                                         this.txtStateWanted,
                                                                         this.btnFind,
                                                                         this.dataGrid1});
       ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
       this.ResumeLayout(false);
   }
   [STAThread]
   static void Main() {
       Application.Run(new Form1());
   }
   private void btnFind_Click(object sender, System.EventArgs e) {
       try {
           DataSet ds = new DataSet();
           SqlDataAdapter da = new SqlDataAdapter("SELECT pubid, pubname, city, state FROM publishers where state = @State", "data source=.;database=biblio;uid=admin;pwd=pw");
           da.SelectCommand.Parameters.Add("@State", txtStateWanted.Text);
           da.Fill(ds, "PublishersIn" + txtStateWanted.Text);
           dataGrid1.DataSource = ds.Tables[0];
       } catch (SqlException sex) {
           Debug.WriteLine(sex.ToString());
       }
   }

}


 </source>


SqlDataAdapter.TableMappings

<source lang="csharp"> using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.rumon; class Program {

   static void Main(string[] args) {
       string cnStr = "uid=sa;pwd=;Initial Catalog=yourDatabase;Data Source=(local)";
       DataSet myDS = new DataSet("Cars");
       SqlDataAdapter dAdapt = new SqlDataAdapter("Select * From Inventory", cnStr);
       DataTableMapping custMap = dAdapt.TableMappings.Add("Inventory", "Current Inventory");
       custMap.ColumnMappings.Add("CarID", "Car ID");
       custMap.ColumnMappings.Add("PetName", "Name of Car");
       try {
           dAdapt.Fill(myDS, "Inventory");
       } catch (Exception ex) {
           Console.WriteLine(ex.Message);
       }
       PrintDataSet(myDS);
   }
   static void PrintDataSet(DataSet ds) {
       Console.WriteLine("Tables in "{0}" DataSet.\n", ds.DataSetName);
       foreach (DataTable dt in ds.Tables) {
           Console.WriteLine("{0} Table.\n", dt.TableName);
           for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
               Console.Write(dt.Columns[curCol].ColumnName.Trim() + "\t");
           }
           for (int curRow = 0; curRow < dt.Rows.Count; curRow++) {
               for (int curCol = 0; curCol < dt.Columns.Count; curCol++) {
                   Console.Write(dt.Rows[curRow][curCol].ToString().Trim() + "\t");
               }
               Console.WriteLine();
           }
       }
   }

}


 </source>


SqlDataAdapter.TableMappings.Add

<source lang="csharp">

using System; using System.Data; using System.Data.rumon; using System.Data.SqlClient;

   public class UsingDataTableMappings
   {
       static void Main(string[] args)
       {
           SqlConnection SConn = new SqlConnection("Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
           SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Contact, Email FROM CaseInfo", SConn);
           DataSet ds = new DataSet();
           DataTableMapping custMap = da.TableMappings.Add("CaseInfo", "MyDatabase");
           custMap.ColumnMappings.Add("ID", "CaseNumber");
           custMap.ColumnMappings.Add("Contact", "MyContact");
           custMap.ColumnMappings.Add("Email", "Email Address");
           da.Fill(ds);
       }
   }
  
   
 </source>


SqlDataAdapter.Update

<source lang="csharp">

using System; using System.Data; using System.Data.SqlClient;

  class PropagateAddsBuilder {
     static void Main() {
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        string qry = @"select * from employee";

        SqlConnection conn = new SqlConnection(connString);
        try {
           SqlDataAdapter da = new SqlDataAdapter();
           da.SelectCommand = new SqlCommand(qry, conn);
           SqlCommandBuilder cb = new SqlCommandBuilder(da);
           DataSet ds = new DataSet();   
           da.Fill(ds, "employee");
           DataTable dt = ds.Tables["employee"];
           // Add a row
           DataRow newRow = dt.NewRow();
           newRow["firstname"] = "y";
           newRow["lastname"] = "y";
           dt.Rows.Add(newRow);
           foreach (DataRow row in dt.Rows){
              Console.WriteLine(
                 "{0} {1}",
                 row["firstname"].ToString().PadRight(15),
                 row["lastname"].ToString().PadLeft(25));
           }
           da.Update(ds, "employee");
        } catch(Exception e)  {
           Console.WriteLine("Error: " + e);
        } finally {
           conn.Close();
        }
     }  
  }
  
   
 </source>


SqlDataAdapter.UpdateCommand

<source lang="csharp">

using System; using System.Data; using System.Data.SqlClient;

  class PropagateChanges {
     static void Main(){
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        string qry = @"select * from employee ";
        string upd = @"update employee set firstname = @firstname 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"];
           dt.Rows[0]["firstname"] = "W";
           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,15, "firstname");
           SqlParameter parm = cmd.Parameters.Add("@id",SqlDbType.Int,4,"id");
           parm.SourceVersion = DataRowVersion.Original;
           da.UpdateCommand = cmd;
           da.Update(ds, "employee");
        } catch(Exception e) {
           Console.WriteLine("Error: " + e);
        } finally {
           conn.Close();
        }
     }  
  }
  
   
 </source>