Introduction to ADO.Net - PowerPoint PPT Presentation

1 / 38
About This Presentation
Title:

Introduction to ADO.Net

Description:

SqlConnection cnn = new SqlConnection('Data Source = MyServer; User Id=myUser; ... SqlCommand cmd = new SqlCommand('DeleteAccount', cnn) ... – PowerPoint PPT presentation

Number of Views:176
Avg rating:3.0/5.0
Slides: 39
Provided by: fasahat
Category:
Tags: ado | cnn | introduction | net

less

Transcript and Presenter's Notes

Title: Introduction to ADO.Net


1
Introduction to ADO.Net
  • Malek Kemmou
  • CEO Arrabeta
  • kemmou_at_kemmou.com

2
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 )

3
Agenda
  • Overview of Data Access in .Net
  • Fetching Data
  • Processing Data
  • Data as XML
  • Tips Tricks

4
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

5
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

6
Overview of Data in .Net
.NET Data Provider
DataReader
Command
Connection
7
Fetching Data
  • Connected Model
  • Create a connection
  • Open Connection
  • Execute Commands
  • Obtain Results
  • Process Rows
  • Close Connection
  • Data Bind

8
Create open a Connection
  • C
  • SqlConnection cnn new SqlConnection(Data
    Source MyServer User IdmyUser
    passwordmyPassword)
  • cnn.Open()
  • VB.Net
  • Dim cnn as New SqlConnection((Data Source
    MyServer User IdmyUser passwordmyPassword)
  • cnn.open

9
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

10
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("_at_A_ID",typ
    eof(string))
  • param.Value accountID
  • cmd.Parameters.Add(param)
  • Int32 RowsAffected cmd.ExecuteNonQuery()

11
Obtain a Single Value
  • Use ExecuteScalar
  • SqlCommand cmd new SqlCommand(
  • "Select Balance from Accounts where
  • AccountID _at_A_ID", cnn)
  • cmd.Parameters.Add("_at_A_ID",accountID)
  • Decimal AccountBalance (Decimal)
    cmd.ExecuteScalar()

12
Process Rows
  • Dynamic Processing
  • SqlCommand cmd new SqlCommand("Select Desc, "
  • "Amt from Activity where AccountID _at_A_ID",
    cnn)
  • cmd.Parameters.Add("_at_A_ID",accountID)
  • SqlDataReader results cmd.ExecuteReader()
  • While (results.Read())
  • Console.Write("Description "
    results.GetString(0))
  • Console.WriteLine("Amount "
    results.GetDecimal(1))

13
Data Binding(Web Forms)
public void Page_Load(Object sender, EventArgs e)
// Créer une SqlCommand et obtenir un
DataReader SqlConnection cnn new
SqlConnection("serverlocalhostuidsa") cnn.Op
en() SqlCommand cmd new SqlCommand("Select
from customers", cnn) SqlDataReader results
cmd.ExecuteReader() // Lier les
résulats ActivityList.DataSource
results ActivityList.DataBind()
14
Demo
  • Connected DataAccess from a Web Page

15
Processing Data
  • Disconnected Model
  • Fill DataSet
  • Navigate the DataSet
  • Update Changes from DataSet
  • Data Bind
  • DataSet can be used as cache

16
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)

17
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.

18
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)

19
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"Cust
    omer".Rows)
  • Console.WriteLine("Orders for customer "
    customer"Name")
  • foreach(DataRow order in customer.GetChildRows("c
    ust_orders") )
  • Console.Write("\t Order ID "
    order"OrderID")
  • Console.WriteLine("Amount "
    order"Amount")

20
Update Changes
SqlDataAdapter adapter new SqlDataAdapter() Sq
lCommand delete new SqlCommand("DeleteOrder",cnn
) delete.CommandTypeCommandType.StoredProcedure
delete.Parameters.Add("_at_OrderID",typeof(Int32)).S
ourceColumn"OrderID" adapter.DeleteCommand
delete SqlCommand insert new
SqlCommand("AddOrder",cnn) insert.CommandTypeCom
mandType.StoredProcedure insert.Parameters.Add("_at_
OrderID",typeof(Int32)).SourceColumn"OrderID" in
sert.Parameters.Add("_at_CustD",typeof(Int32)).Source
Column"CustomerID" insert.Parameters.Add("_at_Date"
,typeof(DateTime)).Value DateTime.Now adapter.I
nsertCommand insert SqlCommand update new
SqlCommand("UpdateOrder",cnn) update.CommandType
CommandType.StoredProcedure update.Parameters.Add
("_at_OrderID",typeof(Int32)).SourceColumn"OrderID"
update.Parameters.Add("_at_CustD",typeof(Int32)).Sou
rceColumn"CustomerID" adapter.UpdateCommand
update adapter.Update(ordersTable)
21
Winforms DataBinding
SqlCommand cmd new SqlCommand("GetAccountInfo",
cnn) cmd.CommandTypeCommandType.StoredProcedure
cmd.Parameters.Add("_at_A_ID",accountID) DataSet
account new DataSet DataAdapter adapter new
DataAdapter(cmd) adapter.Fill(account) DataGrid
accountGrid new DataGrid() accountGrid.SetData
Binding(myDataSet, "AccountList")
22
Demo
  • A Simple WinForm Working with Data

23
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

24
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")

25
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

26
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

27
Demo
  • DataSets and XML

28
X/Path And XSL/TOver DataSet
  • XmlDataDocument xmlData new XmlDataDocument(po)
  • // Requête X/Path
  • XmlNodeList nodes xmlData.SelectNodes("//Item_at_q
    ty100")
  • foreach(XmlNode node in nodes)
  • DataRow row xmlData.GetRowFromElement((XmlE
    lement)node)
  • row.Delete()
  • // Transformation XSLT
  • XslTransform xsltransform new XslTransform()
  • xsltransform.Load("po.xsl")
  • XmlReader xReader xsltransform.Transform(xmlData
    , null)

29
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

30
Auto Generate Commandsfor 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)

31
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)

32
Managing and Processing ErrorsWhen 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

33
Working with Row versionsand changes
  • Item(x)
  • Allows you to examine the value of column x,
    where x is columns 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

34
Passing null values
  • Use DBNull.Value
  • SqlParameter param new SqlParameter()
  • param.Value DBNull.value

35
Guarantee connection closeswhen DataReader
finishes
  • Use CommandBehavior.CloseConnection
  • private DataReader getCategories()
  • SqlCommand cmd new SqlCommand( "Select from
    Categories, cnn)
  • DataReader results
  • cmd.ExecuteReader(CommandBehavior.CloseConnec
    tion)
  • return results

36
Inserting Primary Keys
  • Use Guids as Primary Keys
  • Can be generated on the client
  • Guarantees to be unique
  • Doesnt change when updated on the server
  • No problem on child tables

37
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

38
Questions
  • I will post session content on my blog
  • http//www.malekkemmou.ma
Write a Comment
User Comments (0)
About PowerShow.com