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

new SqlDataAdapter

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

SqlDataAdapter.DeleteCommand

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

SqlDataAdapter.Fill

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

SqlDataAdapter.InsertCommand

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

SqlDataAdapter.RowUpdated

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

SqlDataAdapter.RowUpdating

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

SqlDataAdapter.SelectCommand

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

SqlDataAdapter.SelectCommand.Parameters.Add

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

SqlDataAdapter.TableMappings

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

SqlDataAdapter.TableMappings.Add

  

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

SqlDataAdapter.Update

  

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

SqlDataAdapter.UpdateCommand

  

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