Introduction to ADO.Net : Introduction to ADO.Net Malek Kemmou
CEO Arrabeta
kemmou@kemmou.com
bioAdapter.Fill(bio_DataSet); repeater1.DataBind(); : bioAdapter.Fill(bio_DataSet); repeater1.DataBind(); CEO Arrabeta (consulting firm based in Casablanca Morocco)
Newtelligence Alliance Partner
Senior Consultant and Senior Trainer
Solutions Architecture, integration, interoperability
Microsoft Regional Director for Middle East and Africa
Ineta MEA Speaker Bureau
Speaker at many conferences and events (TechEd, NDC, MDC, DevDays, DevEssentials …)
Agenda : Agenda Overview of Data Access in .Net
Fetching Data
Processing Data
Data as XML
Tips & Tricks
What is ADO.NET? : What is ADO.NET? Managed Code (.NET) Data Access Methodology
Complete Integration with the .NET Framework
Improved support for the disconnected business model
Improved integration with XML
Explicit control of Data Access behaviors for .NET applications
Introducing ADO.NET : Introducing ADO.NET Part of the .NET framework, ADO.NET was built with the new world of XML, disconnected data, web and HTTP in mind
Is a rewrite of ADO for the .NET framework
Not a replacement of ADO for COM developers
ADO.NET is a natural evolution of ADO, built around n-tier development and architected with XML at its core
Overview of Data in .Net : Overview of Data in .Net
.NET Data Provider DataReader Command Connection
Fetching Data : Fetching Data Connected Model
Create a connection
Open Connection
Execute Commands
Obtain Results
Process Rows
Close Connection
Data Bind
Create & open a Connection : Create & open a Connection C#
SqlConnection cnn = new SqlConnection(“Data Source = MyServer; User Id=myUser; password=myPassword”);
cnn.Open();
VB.Net
Dim cnn as New SqlConnection((“Data Source = MyServer; User Id=myUser; password=myPassword”)
cnn.open
Open a Transaction if needed : Open a Transaction if needed C#
SqlTransaction tnx = cnn.BeginTransaction();
// do some Data Access and processing
If (somecondition) tnx.Commit();
Else tnx.Rollback();
VB.Net
Dim tnx as SqlTransaction = cnn.BeginTransaction
‘ Do some Data Access and processing
If (somecondition) Then
tnx.Commit()
Else
tnx.Rollback()
End If
Execute Commands : Execute Commands Various types of commands
Insert, Update, Delete, stored procedure, …
Optionally transmit parameters
SqlCommand cmd = new SqlCommand("DeleteAccount", cnn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@A_ID",typeof(string));
param.Value = accountID;
cmd.Parameters.Add(param);
Int32 RowsAffected = cmd.ExecuteNonQuery();
Obtain a Single Value : Obtain a Single Value Use ExecuteScalar
SqlCommand cmd = new SqlCommand(
"Select Balance from Accounts where ” +
“AccountID = @A_ID", &cnn);
cmd.Parameters.Add("@A_ID",accountID);
Decimal AccountBalance = (Decimal) cmd.ExecuteScalar();
Process Rows : Process Rows Dynamic Processing
SqlCommand cmd = new SqlCommand("Select Desc, "
+"Amt from Activity where AccountID = @A_ID", cnn);
cmd.Parameters.Add("@A_ID",accountID);
SqlDataReader results = cmd.ExecuteReader();
While (results.Read()) {
Console.Write("Description: " + results.GetString(0));
Console.WriteLine("Amount: " + results.GetDecimal(1));
}
Data Binding (Web Forms) : Data Binding (Web Forms) public void Page_Load(Object sender, EventArgs e) {
// Créer une SqlCommand et obtenir un DataReader
SqlConnection cnn = new SqlConnection("server=localhost;uid=sa;");
cnn.Open();
SqlCommand cmd = new SqlCommand("Select * from customers", cnn);
SqlDataReader results = cmd.ExecuteReader();
// Lier les résulats
ActivityList.DataSource = results;
ActivityList.DataBind(); }
Demo : Demo Connected DataAccess from a Web Page
Processing Data : Processing Data Disconnected Model
Fill DataSet
Navigate the DataSet
Update Changes from DataSet
Data Bind
DataSet can be used as cache
DataSet : DataSet A DataSet is a local buffer of tables, or a collection of disconnected “recordsets”
Keeps track of the relationships between the tables it contains
DataSets are an in-memory relational store
Exposes a rich programming model
All data is stored in a local cache
Same performance and semantics regardless of whether the data is loaded from a database, loaded from XML, or is generated by the application.
No connection!
(Not directly anyway)
DataSets: Tables : DataSets: Tables A DataSet contains a collection of DataTables (the DataTableCollection)
A DataTable represents one table of in-memory data. It contains a collection of columns (the DataColumnCollection) that represents the table's schema
A DataTable also contains a collection of rows (the DataRowCollection), representing the data held by the table. It remembers the original state along with current state, tracking the kinds of changes that have occurred.
Fill DataSet from Database : Fill DataSet from Database Use a DataAdapter
SqlCommand selectCommand =
new SqlCommand("Select CategoryName from Categories",cnn);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;
DataSet categories = new DataSet("Categories");
adapter.Fill(categories);
Navigate the DataSet : Navigate the DataSet Navigate the Row Collection of a Table
Obtain Rows as an Array
Use language expressions as “foreach”
foreach(DataRow customer in myDataSet.Tables["Customer"].Rows) {
Console.WriteLine("Orders for customer: " + customer["Name"]);
foreach(DataRow order in customer.GetChildRows("cust_orders") ) {
Console.Write("\t Order ID = " + order["OrderID"]);
Console.WriteLine("Amount = " + order["Amount"]);
}
}
Update Changes : Update Changes SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand delete = new SqlCommand("DeleteOrder",cnn);
delete.CommandType=CommandType.StoredProcedure;
delete.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
adapter.DeleteCommand = delete;
SqlCommand insert = new SqlCommand("AddOrder",cnn);
insert.CommandType=CommandType.StoredProcedure;
insert.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
insert.Parameters.Add("@CustD",typeof(Int32)).SourceColumn="CustomerID";
insert.Parameters.Add("@Date",typeof(DateTime)).Value = DateTime.Now;
adapter.InsertCommand = insert;
SqlCommand update = new SqlCommand("UpdateOrder",cnn);
update.CommandType=CommandType.StoredProcedure;
update.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
update.Parameters.Add("@CustD",typeof(Int32)).SourceColumn="CustomerID";
adapter.UpdateCommand = update;
adapter.Update(ordersTable);
Winforms DataBinding : Winforms DataBinding SqlCommand cmd = new SqlCommand("GetAccountInfo", cnn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@A_ID",accountID);
DataSet account = new DataSet;
DataAdapter adapter = new DataAdapter(cmd);
adapter.Fill(account);
DataGrid accountGrid = new DataGrid();
accountGrid.SetDataBinding(myDataSet, "AccountList");
Demo : Demo A Simple WinForm Working with Data
ADO .NET & XML : ADO .NET & XML DataSets and XML
Load/Save XML Data to/From DataSet
Schema can be loaded/saved as XSD
Schema can be inferred from XML Data
Loading XML : Loading XML DataSet ds = new DataSet();
ds.ReadXml("inventory.xml");
DataTable inventory = ds.Tables["Inventory"];
DataRow row = inventory.NewRow();
row["TitleID"]=1;
row["Quantity"]=25;
inventory.Rows.Add(row);
ds.WriteXml("updatedinventory.xml");
Load Schema from XSD : Load Schema from XSD myDataSet.ReadXmlSchema(schemaFile);
Complex Types converted to tables
Nested Complex Types converted to child tables
Keys/Constraints converted into unique constraints
Foreign Key Constraints inferred
Inferred Schema : Inferred Schema If no schema is defined before calling DataSet.ReadXml(), schema is inferred from data
General Rules
An element becomes a table if :
It is repetitive within its parent or it contains more than one simple content
…Otherwise, it becomes a column
Attributes become columns
Relations are created for nested tables
Hidden columns are created for the keys
Useful for dynamic data binding
Demo : Demo DataSets and XML
X/Path And XSL/T Over DataSet : X/Path And XSL/T Over DataSet XmlDataDocument xmlData = new XmlDataDocument(po);
// Requête X/Path
XmlNodeList nodes = xmlData.SelectNodes("//Item[@qty>100]");
foreach(XmlNode node in nodes) {
DataRow row = xmlData.GetRowFromElement((XmlElement)node);
row.Delete();
}
// Transformation XSLT
XslTransform xsltransform = new XslTransform();
xsltransform.Load("po.xsl");
XmlReader xReader = xsltransform.Transform(xmlData, null);
Tips & Tricks : Tips & Tricks Auto generate Commands for updating DataSet
Refreshing DataSet data
Managing and processing errors when updating a DataSet
Working with row versions and changes
Passing null values
Guarantee connection closes when DataReader is finished
Inserting primary keys
Auto Generate Commands for Updating DataSet : Auto Generate Commands for Updating DataSet Use CommandBuilder
SqlDataAdapter sda = new SqlDataAdapter(“select x, y, z from table1”,
cnn);
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.UpdateCommand = scb.GetUpdateCommand();
sda.InsertCommand = scb.GetInsertCommand();
sda.DeleteCommand = scb.GetDeleteCommand();
sda.Update(ds);
Refreshing DataSet Data : Refreshing DataSet Data Fill data with the Fill method of the Adapter
myAdapter1.fill(dataSet12);
Process data.
Before Updating, use :
DataSet dataSetTemp = new DataSet();
myAdapter1.fill(dataSetTemp);
dataSet12.Merge(dataSetTemp, true);
Managing and Processing Errors When updating DataSet : Managing and Processing Errors When updating DataSet DataAdapter.ContinueUpdateOnError
Default is False; setting to True allows all updates to complete even if updates on certain rows generate errors
DataTable.GetErrors()
Returns array of DataRow objects that represent rows whose updates failed
DataRow.RowError
Returns a string with a general error message that applies to the entire row
DataRow.GetColumnsInError()
Returns array of DataColumn objects that contributed to error
DataRow.GetColumnError(x)
Returns string description of the error itself
x is column ordinal, name, or DataColumn object
Working with Row versions and changes : Working with Row versions and changes Item(x)
Allows you to examine the value of column x, where x is column’s ordinal or name
Item(x, version)
Allows you to examine the value of a specific version of column x (DataRowVersion.Current, .Default, .Original, or .Proposed)
BeginEdit(), EndEdit()
In conjunction with .Items(x), allows you to modify column values in the row
CancelEdit()
Abandons pending changes to an edited row
RowState
DataRowState.Added, .Deleted, .Detached, .Modified, or .Unchanged
Passing null values : Passing null values Use DBNull.Value
SqlParameter param = new SqlParameter();
param.Value = DBNull.value;
Guarantee connection closes when DataReader finishes : Guarantee connection closes when DataReader finishes Use CommandBehavior.CloseConnection
private DataReader getCategories() {
SqlCommand cmd = new SqlCommand( "Select * from Categories, cnn);
DataReader results =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return results;
}
Inserting Primary Keys : Inserting Primary Keys Use Guids as Primary Keys
Can be generated on the client
Guarantees to be unique
Doesn’t change when updated on the server
No problem on child tables
Summary : Summary ADO.Net has tailored objects
.NET Data Providers for connected access
Executing commands
DataReader
DataSet for disconnected access, user interaction and caching
ADO.Net and XML are made for each other
Questions : Questions I will post session content on my blog :
http://www.malekkemmou.ma