ADO Net

Information about ADO Net

Published on October 24, 2007

Author: Breezy

Source: authorstream.com

Content

Introduction to ADO.Net:  Introduction to ADO.Net Malek Kemmou CEO Arrabeta [email protected] 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

Related presentations


Other presentations created by Breezy

Plant Anatomy
03. 01. 2008
0 views

Plant Anatomy

Learning Long Division
15. 06. 2007
0 views

Learning Long Division

Ch 2 Chemistry of Life
05. 01. 2008
0 views

Ch 2 Chemistry of Life

REORGANIZATION
27. 09. 2007
0 views

REORGANIZATION

Enhanced Fujita Scale 6 23 04
05. 10. 2007
0 views

Enhanced Fujita Scale 6 23 04

severe convection punkka
07. 10. 2007
0 views

severe convection punkka

lsad07 psp
09. 10. 2007
0 views

lsad07 psp

idioms1
10. 10. 2007
0 views

idioms1

SabadosCiencia2006
13. 10. 2007
0 views

SabadosCiencia2006

Rousset EID06
19. 10. 2007
0 views

Rousset EID06

TheodoreRoosevelt
22. 10. 2007
0 views

TheodoreRoosevelt

Timss
17. 10. 2007
0 views

Timss

Wynn ASA 2000
04. 10. 2007
0 views

Wynn ASA 2000

aas strom
29. 08. 2007
0 views

aas strom

element connections
29. 08. 2007
0 views

element connections

hwr clustering
29. 08. 2007
0 views

hwr clustering

Pov map 20060717 1
29. 11. 2007
0 views

Pov map 20060717 1

CONSTRUCTING BUD VASES ADN BOWS
11. 12. 2007
0 views

CONSTRUCTING BUD VASES ADN BOWS

nobel talk
15. 10. 2007
0 views

nobel talk

18 FOSIS
24. 10. 2007
0 views

18 FOSIS

Lec 08 FO1 06 Urbanisation
01. 11. 2007
0 views

Lec 08 FO1 06 Urbanisation

America vs The World
22. 10. 2007
0 views

America vs The World

Vasco Da Gama Slide Show
07. 11. 2007
0 views

Vasco Da Gama Slide Show

Fliess
15. 11. 2007
0 views

Fliess

01 threat
19. 11. 2007
0 views

01 threat

Konsolen
21. 11. 2007
0 views

Konsolen

the dancers
23. 11. 2007
0 views

the dancers

Probil
26. 11. 2007
0 views

Probil

UNE Benz
27. 11. 2007
0 views

UNE Benz

Galaxies
29. 08. 2007
0 views

Galaxies

DB2 XML DatabaseFINAL
23. 10. 2007
0 views

DB2 XML DatabaseFINAL

akzonobel
15. 10. 2007
0 views

akzonobel

ilana
29. 08. 2007
0 views

ilana

lauter
07. 11. 2007
0 views

lauter

GradSch GPOs
04. 10. 2007
0 views

GradSch GPOs

PHYS402 01
16. 10. 2007
0 views

PHYS402 01

cry beloved
02. 08. 2007
0 views

cry beloved

curtis
02. 08. 2007
0 views

curtis

Chaplet of Divine Mercy
02. 08. 2007
0 views

Chaplet of Divine Mercy

CS583 opinion mining
02. 08. 2007
0 views

CS583 opinion mining

A TIME FOR ANDREW Pres 2
02. 08. 2007
0 views

A TIME FOR ANDREW Pres 2

arthur powerpoint 11 20 03
02. 08. 2007
0 views

arthur powerpoint 11 20 03

cheryl toner ific
02. 08. 2007
0 views

cheryl toner ific

bats
02. 08. 2007
0 views

bats

23 stavros thurs
02. 08. 2007
0 views

23 stavros thurs

aas04 jeff
29. 08. 2007
0 views

aas04 jeff

moustakis
29. 08. 2007
0 views

moustakis

irsurveys07
29. 08. 2007
0 views

irsurveys07

venice oct03
29. 08. 2007
0 views

venice oct03

Office of Homeleand Security
29. 10. 2007
0 views

Office of Homeleand Security

agn presentation 102106
29. 08. 2007
0 views

agn presentation 102106

ReginaSchulteLadbeck 042104
29. 08. 2007
0 views

ReginaSchulteLadbeck 042104

Weingarten
03. 01. 2008
0 views

Weingarten

Presentation NASDAQ
24. 02. 2008
0 views

Presentation NASDAQ

nov retail ebony
24. 02. 2008
0 views

nov retail ebony

APAsymp04AIDMAN
02. 08. 2007
0 views

APAsymp04AIDMAN

Ray Flores Roadmap
04. 03. 2008
0 views

Ray Flores Roadmap

Beloved
02. 08. 2007
0 views

Beloved

2004 4050S1 11 Levin
02. 08. 2007
0 views

2004 4050S1 11 Levin

Konstantinidis
29. 09. 2007
0 views

Konstantinidis

Qin and Han Dynasties
25. 03. 2008
0 views

Qin and Han Dynasties

andy powell presentation
02. 08. 2007
0 views

andy powell presentation

arena rome minier
13. 11. 2007
0 views

arena rome minier

Presentation010605
10. 04. 2008
0 views

Presentation010605

03edclark lecture
13. 04. 2008
0 views

03edclark lecture

richard mushotzky
29. 08. 2007
0 views

richard mushotzky

Lawrence D Boston 2006
14. 04. 2008
0 views

Lawrence D Boston 2006

DMCH13
16. 04. 2008
0 views

DMCH13

ERates
17. 04. 2008
0 views

ERates

JHAN 14
18. 04. 2008
0 views

JHAN 14

4884061 firstfileFILE
22. 04. 2008
0 views

4884061 firstfileFILE

ppt26
23. 12. 2007
0 views

ppt26

Operations
28. 04. 2008
0 views

Operations

CH10 Outline
07. 04. 2008
0 views

CH10 Outline

CIM research
30. 04. 2008
0 views

CIM research

komossa
29. 08. 2007
0 views

komossa

ieee sp 2004
18. 06. 2007
0 views

ieee sp 2004

icws 2006 3
18. 06. 2007
0 views

icws 2006 3

ICTP intro
18. 06. 2007
0 views

ICTP intro

human mating beh 2005
18. 06. 2007
0 views

human mating beh 2005

IMDS CIESP
14. 11. 2007
0 views

IMDS CIESP

welch adv camp july05
02. 10. 2007
0 views

welch adv camp july05

Glycosylation
15. 06. 2007
0 views

Glycosylation

Making a Story Board
15. 06. 2007
0 views

Making a Story Board

Story Literary Elements
15. 06. 2007
0 views

Story Literary Elements

Life Cycle of Plants and Animals
15. 06. 2007
0 views

Life Cycle of Plants and Animals

Session1Alila
02. 11. 2007
0 views

Session1Alila

beetleborers
02. 01. 2008
0 views

beetleborers

2006 IADB
10. 10. 2007
0 views

2006 IADB

robo wk1
03. 01. 2008
0 views

robo wk1

Rosemary Panama
22. 10. 2007
0 views

Rosemary Panama

ec06nicapan
25. 10. 2007
0 views

ec06nicapan

Allies Pre Training Module
02. 08. 2007
0 views

Allies Pre Training Module

Carmona
30. 12. 2007
0 views

Carmona

TheSuccessofSingapor e2006
27. 03. 2008
0 views

TheSuccessofSingapor e2006

Advisory Board Presentation
02. 08. 2007
0 views

Advisory Board Presentation

Cameron SAS44 A Century of OA
27. 02. 2008
0 views

Cameron SAS44 A Century of OA

dubrovnik
16. 10. 2007
0 views

dubrovnik

sprfett
07. 01. 2008
0 views

sprfett

mccune albright syndrome
15. 10. 2007
0 views

mccune albright syndrome

michael soendermann 2007
18. 10. 2007
0 views

michael soendermann 2007

astro12Summer12
29. 08. 2007
0 views

astro12Summer12

familyweek1
19. 02. 2008
0 views

familyweek1