ADO.net
//Overview
Data-->DataReader-->Data Provider--> DataSet
Data Provider: Connection, Command, DataAdapter
DataSet: DataRelationCollection,
DataTable collection(including DataTable))
DataTable: DataRowCollection, DataColumnColl, ConstrainColl
DataAdapter: retrieve data from DB, fill tables in DataSet


//SQL Server .net data provider
using System.Data
using System.Data.SqlClient;
...
string strConnection = "server=allan; uid=sa; pwd=; database=northwind";
string strCommand = "Select productName, unitPrice from Products";
SqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Products");
DataTable dataTable = dataSet.Table[0];
foreach(DataRow row in dataTable.Rows) {
lbProducts.Items.Add(row["ProductName"]+"($" +row["UnitProice"] + ")");
}

//OLEDB Data provider
using System.Data.OleDb;
...
string strConnection = "provider=Microsoft.JET.OLEDB.4.0; data source=c:\\nwind.mdb";
OleDbDataAdapter dataAdapter = ...

//DataGrids
using System.Data.SqlClient
public class Form1: System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dgOrders;
private System.Data.DataSet dataSet;
private System.Data.SqlClient.Sqlconnection connection;
private System.Data.SqlClient.SqlCommand;
private System.Data.SqlClient.SqlDataAdapter dataAdapter;

private void Form1_Load(object sender, System.EventArgs e)
{
string connectionString = "server=allan; uid=sa; pwd=;database=northWind";
connection = new System.Data.SqlClient.Sqlconnection(connectionString);
connection.Open();
dataSet = new System.Data.DataSet();
dataSet.CaseSensitive = true;

command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = "Select * from Orders";
dataAdapter = new System.DataSqlClient.SqlAdapter();
//DataAdapter has SelectCommand, InsertCommand, UpdaterCommand
//and DeleteCommand
dataAdapter.SelectCommand = command;
dataAdapter.TableMappings.Add("Table", "Orders");
dataAdapter.Fill(dataSet);
ProductDataGrid.DataSource = dataSet.Table["Orders"].DefaultView;

//Data Relationships, add code below
command2 = new System.Data.SqlClient();
command2.Connection = connection;
command2.CommandText = "Select * form [order details]";
dataAdapter2 = new System.Data.SqlClient.SqlDataAdapter();
dataAdapter2.SelectCommand = command2;
dataAdapter2.TableMappings.Add("Table", "Details");
dataAdatper2.Fill(dataSet);

System.Data.DataRelation dataRelation;

System.Data.DataColumn datacolumn1;
System.Data.DataColumn datacolumn2;
dataColumn1 = dataSet.Table["Orders"].Columns["OrderID"];
dataColumn2 = dataSet.Table["Details"].Columns["OrderID"];
dataRelation new System.Data.DataRelation("OrdersToDetails", dataColumn1, dataColumn2);
dataSet.Relations.Add(dataRelation);
productDataGrid.dataSource = dataset.DefaultViewManger;
productDataGrid.DataMember = "Orders"; //display Order table, it has mapping to Order Detail

}
}

//Update Data using ADO.net
string cmd = "update Products set ...";
...
//creat connection, comand obj
command.Connection = connection;
command.CommandText=cmd;
command.ExecuteNonQuery();

//Transaction 1.SQL Transaction 2. Connection Transaction

//1. SQL Transaction
//creat connection and com