Transcript Web Services - Train The Teachers

ADO.NET
Accessing Data using ADO.NET






ADO.NET Architecture
Connecting to Data Sources
Performing Connected Database Operations
Building Datasets
Reading and Writing XML with ADO.NET
Building Datasets from Existing Data Sources
ADO.NET Architecture



Advantages/Disadvantages of a
Connected/Disconnected Environment
ADO.NET Architecture
ADO.NET and XML
Advantages/Disadvantages of a
Connected Environment


A connected environment is one in which users
are constantly connected to a data source
Advantages:
Environment is easier to secure
 Concurrency is more easily controlled
 Data is more likely to be current than in other
scenarios


Disadvantages:
Must have a constant network connection
 Scalability

Advantages/Disadvantages of a
Disconnected Environment


In a disconnected environment, a subset of data from a
central data store can be copied and modified
independently, and the changes merged back into the
central data store
Advantages




You can work at any time that is convenient for you, and can
connect to a data source at any time to process requests
Other users can use the connection
A disconnected environment improves the scalability and
performance of applications
Disadvantages


Data is not always up to date
Change conflicts can occur and must be resolved
Evolution of data access
“Thin” Client
Client logic
Business logic
Data storage
1-Tier
(monolithic)
XML
Web
service
“Fat” Client
2-Tier
3-Tier
N-Tier
Internet
ADO.Net Namespaces
System.Data
 System.Data.Common
 System.Data.SqlClient
 System.Data.OleDb
 System.Data.XXX
 System.Data.SqlTypes
 System.Xml

Using ADO.NET Classes in a
Connected Scenario
xxxDataReader

xxxCommand
xxxConnection
database
1.
2.
3.
4.
5.
In a connected
scenario, resources are
held on the server until
the connection is
closed
Open connection
Execute command
Process rows in reader
Close reader
Close connection
Using ADO.NET Classes in a
Disconnected Scenario

DataSet
xxxDataAdapter
xxxConnection
database
1.
2.
3.
4.
5.
6.
7.
In a disconnected scenario,
resources are not held on
the server while the data is
processed
Open connection (optional)
Fill the DataSet
Close connection (optional)
Process the DataSet
Open connection (optional)
Update the data source
Close connection (optional)
ADO.NET and XML

ADO.NET is tightly integrated with XML
XML Web Services
Client
Data Source
1
Request data
2
4
XML
DataSet
3
5
Updated XML
DataSet
6
SQL query
Results
SQL updates
Connecting to Data Sources





Choosing a .NET Data Provider
Defining a Connection
Managing a Connection
Handling Connection Exceptions
Connection Pooling
Choosing a .NET Data Provider




SQL Server .NET Data Provider
 SQL Server version 7.0 or later
OLE DB .NET Data Provider
 SQL Server 6.5, Microsoft Access, Oracle, other data sources
with OLE DB providers
ODBC .NET Data Provider
 Legacy data sources that only have ODBC drivers
Oracle .NET Data Provider
 Oracle
How to Set a Connection String

Example: MS Access connection using OleDb:
Using System.Data.OleDb;
OleDbConnection MyConn = new OleDbConnection()
MyConn.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0; Data Source =
c:\data\nw1.mdb;“
How to Set a Connection String

You can set the ConnectionString property only when the connection is
closed
To reset a connection string, you must close and reopen the connection

Connection string for MS Access using System.Data.OleDb.OleDbConnection :

provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\NW1.mdb;

Connection string for MS SQL using System.Data.SqlClient.SqlConnection
server=(local);database=northwind;uid=sa;pwd=1234;
server=(local);database=northwind;trusted connection=SSPI;

Connection string for MS SQL using System.Data.OleDb.OleDbConnection
provider=SQLOLEDB; Data Source = (local); initial catalog = northwind;
uid=sa; pwd=1234;
Performing Connected Database
Operations






Working in a Connected Environment
Building Command Objects
Executing Command Objects That Return a Single
Value
Executing Commands That Return Rows
Executing Commands That Do Not Return Rows
Using Transactions
Working in a Connected
Environment
Data Source
XxxDataReader
XxxCommand
XxxConnection
XmlReader
XxxParameter
XxxParameter
XxxParameter
Building Command Objects



A command object is a reference to a SQL
statement or stored procedure
Properties
 (Name), Connection, CommandType,
CommandText,
Parameters
Methods
 ExecuteScalar, ExecuteReader, ExecuteNonQuery
 ExecuteXmlReader (SqlCommand only)
Executing Command Objects That
Return a Single Value

Call the ExecuteScalar method
 ExecuteScalar returns a value of the type
Object
 Use convert or a cast, to convert into
appropriate type
Executing Commands That
Return Rows
SqlCommand cmProducts = New SqlCommand( “ SELECT ProductName,
UnitsInStock FROM Products", MyConn);
MyConn.Open();
SqlDataReader rdrProducts = cmProducts.ExecuteReader();
While rdrProducts.Read(){
Console.WriteLine(rdrProducts.GetString(0))
}
rdrProducts.Close()
MyConn.Close();
Executing Commands That Do Not
Return Rows
SqlCommand cmProducts = New SqlCommand( “ UPDATE Products SET price =
price * 1.1", MyConn);
MyConn.Open();
int intItemsUpdated = cmProducts. ExecuteNonQuery();
MyConn.Close();
Building DataSets






Working in a Disconnected Environment
Building DataSets and DataTables
Binding and Saving a DataSet
Defining Data Relationships
Modifying Data in a DataTable
Sorting and Filtering
Working in a Disconnected
Environment
SQL Server 2000
Categories
Access
Products
Customers
SqlDataAdapter
DataSet
Categories
Products
Orders
OleDbDataAdapter
Employees
XML
File
Customers
Orders
XmlDataDocument
XML Web
service
Building DataSets and DataTables
DataSet
DataTable
DataTable
Connection
Database
Client
Server
Sample - One way of building a DataSet
Declare
// Students Table
DataTable tblStudents;
// Students table columns
DataColumn clmStudentID;
DataColumn clmStudentName;
DataColumn clmStudentClass;
//Classes Table
DataTable tblClasses;
// Classes Table Columns
DataColumn clmClassID;
DataColumn clmClassMajor;
// School DataSet
DataSet dsSchool;
Sample - One way of building a DataSet
The Students
Table
// build the Students table
tblStudents = new DataTable("Students");
// build and add columns for the students table
clmStudentID= new DataColumn("StudentID",typeof(int));
tblStudents.Columns.Add(clmStudentID);
clmStudentName = new DataColumn("StudentName", typeof(string));
tblStudents.Columns.Add(clmStudentName);
clmStudentClass = new DataColumn("StudentClass", typeof(int));
tblStudents.Columns.Add(clmStudentClass);
// Sst StudentID as a Primay Key
tblStudents.PrimaryKey = clmStudentID;
Sample - One way of building a DataSet
The Classes
Table
// build the Classes table
tblClasses = new DataTable("Classes");
// build and add columns for the Classes table
clmClassID = new DataColumn("ClasssID", typeof(int));
tblClasses.Columns.Add(clmClassID);
clmClassMajor = new DataColumn("ClassMajor", typeof(string));
tblClasses.Columns.Add(clmClassMajor);
// Sst ClasssID as a Primay Key
tblClasses.PrimaryKey = clmClassID;
Sample - One way of building a DataSet
The DataSet
// build the DataSet and add the two tables into it
dsSchool = new DataSet();
dsSchool.Tables.Add(tblStudents);
dsSchool.Tables.Add(tblClasses);
//optional - set the relationship
dsSchool.Relations.Add("fk_ClassesStudents", clmClassID, clmStudentClass);
Modifying Data in a DataTable
public void UpdateStudentClass(int StudentID, int StudentClass)
{
DataRow row = dsSchool.Tables["Students"].Rows.Find(StudentID);
row["StudentClass"] = StudentClass;
}
Reading and Writing XML with
ADO.NET




Creating XSD Schemas using XSD.EXE
Loading Schemas and Data into DataSets
Writing XML from a DataSet
Demo
Building Datasets from Existing
Data Sources






Configuring a DataAdapter to Retrieve Information
Populating a DataSet Using a DataAdapter
Configuring a DataAdapter to Update the Underlying
Data Source
Persisting Changes to a Data Source
How to Handle Conflicts
Demo
Review






Data-Centric Applications and ADO.NET
Connecting to Data Sources
Performing Connected Database Operations
Building Datasets
Reading and Writing XML with ADO.NET
Building Datasets from Existing Data Sources