a. ado.net layers
|
RDO (Remote Data Objects): ms api usg ms lower-layer DAO (Data Access Objs) for relational db (not non-relational) access; llimited use as as objs couldnt be marshalled across net. evolved into ADO (ActiveX Data Objects). [searchDatabase.com] ADO (ActiveX Data Objects): OO ms api for db access to relational and non-relational db. improved support for disconnected db; a disconnected recordset can marshall over n/w. pt of Universal Data Access strategy: provide universal access to various kds of db rather than buildg universal db. (oracle) ADO.net (not ActiveX Data Objects): "the data access model for .NET-based applications" [mackman 2003]; a disconnected and distributed data access technology based on XML, largely indepdt of data src. |
_____ _____________ / \---------> ( Data Provider ) |\______/| ( Connection ) | | -------------- | Data | | | Store | | | | da.Update() ------------ da.Fill(ds)-------- / | |<---------- ( DataAdapter ) -------->|DataSet|--------> Data View -< | | ( _________ ) | | \ \______/ --------
|
Additional classes:
| Object | Description |
|---|---|
| CommandBuilder | A helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. |
| Parameter | Defines input, output, and return value parameters for commands and stored procedures. |
| Exception | Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception. |
| Error | Exposes the information from a warning or error returned by a data source. |
| ClientPermission | Provided for .NET Framework data provider code access security attributes. |
xDataSet.Tables["aTable"].Columns["aColumn"}.Caption = "@Home"; // untyped refs xDataSet,aTable.aColumn.Caption = "@Home"; // typed refs(old ado: only untyped refs to data objs)
Web.config: <configuration> <appSettings> <add key=SQLConnection value="conn string"> </appSettings> <configuration>
b. System.Data
Sql | OracleConnection MyConnection = new
SqlConnection("server=(local);"+"database=tbl; Trusted_Connection=yes");
SqlCommand MyCommand = new SqlCommand("SELECT col1, col2 FROM aTable",MyConnection);
MyConnection.Open();
Sql | OracleDataReader MyDataReader = MyCommand.ExecuteReader();
while(MyDataReader.Read())
{ foreach(object item in items)...
myReader.GetInt32(0); myReader.GetString(1) .. }
Sql | OracleConnection MyConnection = new // 1. create conn obj
SqlConnection("server=(local);"+"database=tbl; Trusted_Connection=yes");
SqlDataAdapter MyDataAdapter = // 2. create data adapter
new SqlDataAdaper("select * from oTable", MyConnection);
// or new SqlDataAdapter("SELECT * FROM aTable",
// "server=(local); database=aTable; Trusted_Connection=yes");
DataSet oDataSet = new DataSet(); // 3. create data set
MyDataAdapter.Fill(oDataSet)
| (ds,"tbl"); // 4. fill/update data set
oDataGrid.DataSource = oDataSet.Tables["tbl"].DefaultView;
oDataGrid.DataBind(); // 5. show data from dataset usg say data bindg
SqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows){ while (myReader.Read()) {
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } }
myReader.Close();
... this.PreRender += new EventHandler(f_PreRender); ... // after all ctrl
// evts processed
public void f_PreRender(object o_sender, EventArgs e)
{ MySqlAdapter.Update(MyDataSet); aDataGrid.DataBind(); }
// Data adapter, combines sql cmd + connectn string
oDataAdapter.Fill(oDataTable);
oDataAdapter.SelectCommand.CommandText =
"SELECT * FROM bTable"; // change adapter's SELECT cmd
DataSet aDataSet = new DataSet("xxx");
aDataSet.Tables.Add(aDataTable);
aDataSet.Tables.Add(bDataTable); // add both tbls to dataset
aDropDownList.DataBind():
aDataSet.Tables[".."].DefaultView.RowFilter = "conctactId=" +
aDropDownList.SelectedItem.Value.ToString();
<asp:DropDownList id="aDropDownList" runat="server" DataSource="<%# aDataSet %>" DataTextField='aName' DataValueField="aValue"></asp:DropDownList> ... aSqlDataAdapter.Fill(aDataSet); aDropDownList.DataBind(); ...
DataColumn dc1 = myDataSet.Tables["aTable"].Columns["SameCol"]; DataColumn dc2 = myDataSet.Tables["cTable"].Columns["SameCol"]; ForeignKeyConstraint fkc = new ForeignKeyConstraint["oTable",dc1,dc2]; fkc.UpdateRule = Rule.Cascade; fkc.DeleteRule = Rule.Cascade; fkc.AcceptRejectRule = AcceptRejectRule.Cascade; myDataSet.Tables["Chemicals"].Constraints.Add[fkc]; myDataSet.EnforceConstraints = true;
DataTableMapping dtm = myDataAdapter.TableMappings.Add("MyMap","aTable");
// create dtm from the data adapter, & add col mappgs to dtm usg Add
dtm.ColumnMappings.Add("newcol","oldcol");
DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "MyMap"); // use dtm instead of DataTable
foreach (DataRow myRow in ds.Tables["aTable"].Rows)
{ string s = myRow["newcol"].ToString(); } // use idx or col name
object o = {"samar","abbas"}; MyDataView.Find(o);
MyDataView.Sort("fname","lname"); MyDataView.Table = ...;
MyDataView.Sort(ocol DESC) {reverse sort, descendg}
SqlCommandBuilder cb = new SqlCommandBuilder(oDataAdapter); // create insert, del, update cmds auto oDataAdapter.Update(oDataTable); // [webb.248]
c. T-SQL
d. Transactions
Transactn: grp of cmds treated as a single unit, all-or-nothg fashion: if 1 cmd failed, all fail + data removed.
MyDataSet.RejectChanges(); // restore DataSet to original state MyDataGrid.DataBind(); ... // refresh the datagrid int i = MyAdapter.Update(MyDataSet); // update db from dataset Session["MyDataSet"] = MyDataSet; // save changes to state var MyDataGrid.DataBind(); // refresh data grid
SqlTransaction MyTrans = MyConnection.BeginTransaaction(); SqlCommand MyCommand = new SqlCommand(); MyCommand.Transaction = MyTrans; MyCommand.CommandText = "..."; MyCommand.ExecuteNonQuery(); MyTrans.Commit(); MyConnection.Close();
try{ MyTransactn.Commit(); }
catch(Exception e) { MyTransactn.Rollback(); } // cancel pendg cmds
finally { MyConnection.Close(); }
Sql|OracleTransaction MyTransaction =
MyConnection.BeginTransaction(IsolationLevel.ReadCommitted);
SqlCommand MyCommand = new SqlCommand("DELETE ...", MyConnection, MyTransaction);
try{ int i_rows = MyCommand.ExecuteNonQuery();
MyTransaction.Commit(); } // commit the transactn
catch{ MyTransaction.Rollback(); }