Csharp/CSharp Tutorial/ADO.Net/DataAdapter

Fill a DataSet using DataAdapter and output to console

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

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           foreach (DataRow row in dt.Rows)
               Console.WriteLine("{0} {1}", row[0], row["LastName"]);

           DataSet ds = new DataSet( );
           da.Fill(ds, "Contact");
           Console.WriteLine(ds.Tables.Count);
           Console.WriteLine(ds.Tables[0].TableName);
           foreach (DataRow row in ds.Tables["Contact"].Rows)
               Console.WriteLine("{0} {1}", row[0], row[1]);
       }
   }</source>

Fill a DataTable using DataAdapter

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

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           
           DataTable dt1 = new DataTable();
           da.Fill(dt1);
           foreach (DataRow row in dt1.Rows)
           {
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   row["ContactID"], row["FirstName"], row["LastName"]);
           }
       }
   }</source>

Multiple DataAdapter

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

 private System.Windows.Forms.DataGrid dataGrid1;
 private System.Windows.Forms.DataGrid dataGrid2;
 public MainClass()
 {
   InitializeComponent();
       string ConnectionString ="Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost;";
       SqlConnection conn = new SqlConnection(ConnectionString);
       conn.Open();
       DataSet ds = new DataSet("CustomersOrders");
       SqlDataAdapter adapter1 = new SqlDataAdapter("SELECT * FROM Orders", conn);
       SqlDataAdapter adapter2 = new SqlDataAdapter("SELECT * FROM Customers", conn);
       adapter1.Fill(ds, "Orders");
       adapter2.Fill(ds, "Customers");
       DataViewManager dvm = new DataViewManager(ds);
       dvm.DataViewSettings["Orders"].RowFilter = "EmployeeID = 4";
       dvm.DataViewSettings["Orders"].Sort = "ShippedDate ASC";
       dvm.DataViewSettings["Customers"].RowFilter = "ContactName Like "C%"";
       dvm.DataViewSettings["Customers"].Sort = "ContactName ASC";
       DataView dataView1 = dvm.CreateDataView(ds.Tables["Orders"]);
       DataView dataView2 = dvm.CreateDataView(ds.Tables["Customers"]);
       dataGrid1.DataSource = dataView1;
       dataGrid2.DataSource = dataView2;    
 }
 private void InitializeComponent()
 {
   this.dataGrid1 = new System.Windows.Forms.DataGrid();
   this.dataGrid2 = new System.Windows.Forms.DataGrid();
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
   this.SuspendLayout();
   // 
   this.dataGrid1.DataMember = "";
   this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
   this.dataGrid1.Location = new System.Drawing.Point(0, 8);
   this.dataGrid1.Size = new System.Drawing.Size(256, 264);
   // 
   this.dataGrid2.DataMember = "";
   this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
   this.dataGrid2.Location = new System.Drawing.Point(272, 8);
   this.dataGrid2.Size = new System.Drawing.Size(256, 264);
   // 
   this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
   this.ClientSize = new System.Drawing.Size(536, 273);
   this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                   this.dataGrid2,
                                   this.dataGrid1});
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
   this.ResumeLayout(false);
 }
 [STAThread]
 static void Main() 
 {
   Application.Run(new MainClass());
 }

}</source>

Update through SqlDataAdapter

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

   static void Main(string[] args) {
       SqlConnection thisConnection = new SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;Database=northwind");
       SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection);
       SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
       DataSet thisDataSet = new DataSet();
       thisAdapter.Fill(thisDataSet, "Customers");
       Console.WriteLine("name before change: {0}",thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
       thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc.";
       thisAdapter.Update(thisDataSet, "Customers");
       Console.WriteLine("name after change: {0}", thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);
       thisConnection.Close();
   }

}</source>

Use a DataAdapter to fill a DataTable

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

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT TOP 5 * FROM Sales.SalesOrderDetail";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           
           dt.Columns.Add(new DataColumn("ExtendedPrice", typeof(Decimal),
               "UnitPrice * (1 - UnitPriceDiscount) * OrderQty"));
           foreach (DataRow row in dt.Rows)
               Console.WriteLine("ID = {0}, UnitPrice = {1}, " +
                   "Disc = {2}, Qty = {3}, Total = {4}",
                   row["SalesOrderDetailID"], row["UnitPrice"],
                   row["UnitPriceDiscount"], row["OrderQty"],
                   row["ExtendedPrice"]);
       }
   }</source>