Csharp/CSharp Tutorial/ADO.Net/DataAdapter

Fill a DataSet using DataAdapter and output to console

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

Fill a DataTable using DataAdapter

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

Multiple DataAdapter

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

Update through SqlDataAdapter

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

Use a DataAdapter to fill a DataTable

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