ADO.NET - | Лаборатория ITLab

Transcript ADO.NET - | Лаборатория ITLab

ADO.NET: Working in
Disconnected Environment
Sergey Baidachni
MCT, MCSD, MCDBA
Overview



Introduction
Architecture of ADO.NET
ObjectSpaces – New technologies
Introduction





Data Storage
Connected environment
Disconnected environment
ADO.NET vs. ADO
Demonstration (ADO.NET and Excel)
Data Storage
Data storage - method of storing specific items
that together constitute a unit of information.
Data Storage
Unstructured Structured
Excel
Hierarchical
CSV XML
Active Directory
Relational Database
SQL Server
Oracle
Access
ADO.NET
Connected Environment (Scenario)
1. Open connection
2. Execute command
3. Process rows in reader
4. Close reader
5. Close connection
Connected Environment


Working with data directly via open
connection
Advantages




Simple security realization
Work with real data
Simple organization of distributed work
Drawbacks


Continual connection
Not available via Internet
Disconnected Environment (Scenarion)
1. Open connection
2. Fill the DataSet
3. Close connection
4. Process the DataSet
5. Open connection
6. Update the data source
7. Close connection
Disconnected Environment

Storage of data local copy from repository
Possibility to update the main data source

Advantages




Economy of server resources
Does not require continual connection
Drawbacks


Demands conflict resolution while data update
Data is not always up to date
ADO.NET vs. ADO
ADO

Connection
ADO.NET




Command
RecordSet




XxxConnection
XxxTransaction
XxxCommand
XxxDataReader
XxxDataAdapter
DataSet
Demonstration
OleDbConnection conn=new OleDbConnection();
OleDbCommand comm=new OleDbCommand();
OleDbDataAdapter adapt=new OleDbDataAdapter(comm);
DataSet data=new DataSet();
conn.ConnectionString= “Provider=Microsoft.Jet.OLEDB.4.0;
c:\myexcel.xls;Extended Properties=""Excel 8.0;HDR=NO""“;
comm.Connection=conn;
comm.CommandText=“select * from [Sheet1$]”;
adapt.Fill(data);
Architecture of ADO.NET








Data providers
What does it look like?
Connection to the source
Command creation
Cursors
Data storage in memory
DataAdapter – automatic data upload
XML Integration
.NET Data Providers


Concept of data provider
Provider types





SQL .NET Data Provider
Oracle .NET Data Provider
OleDB .NET Data Provider
Odbc .NET Data Provider
How to select data provider
Как это выглядит
.NET Data Provider
Connection
Command
SelectCommand
DataReader
InsertCommand
DataAdapter
DeleteCommand
UpdateCommand
DataSet
DataTable
DataRelation
DataColumn
DataRow
DataConstraint
Connection


What is Connection?
Define Connection



SqlConnection conn=new SqlConnection();
Conn.ConnectionString=“User ID=sa;password=;
Data Source=MyServer;Initial Catalog=Northwind;”
ConnectionString Parameters





Provider
Data Source
Initial Catalog
Integrated Security
UserID/Password
Connection (Error and Pooling)



System.Data.SqlClient.SqlException
Errors collection
SqlError





Class
LineNumber
Message
Number
Pooling and Dispose method
Command Object


A command object is a reference to a SQL
statement or stored procedure
Properties





Connection
CommandType
CommandText
Parameters
Methods



ExecuteNonQuery
ExecuteReader
ExecuteScalar
DataReader Object



What is query?
Forward-only cursor
Read method





Read next record
Return true if record is exist
IsDbNull
Close method
NextResult – for multiply select statements
What Are DataSets and DataTables
DataSet
DataTable
DataTable
Connection
Stored
Procedure
Database
Server
Data Store
The DataSet Object Model

Common collections




Tables (collection of DataTable objects)
Relations (collection of DataRelation objects)
Data binding to Web and Windows controls supported
Schema can be defined programmatically or using
XSD
DataColumn
DataTable
DataRelation
DataRow
Constraints
What Is a DataAdapter?
DataSet
Data source
DataAdapter
DataTable
Fill
Update
DataAdapter
DataTable
Fill
Update
The XxxDataAdapter Object Model
XxxDataAdapter
SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
XxxCommand
XxxCommand
XxxCommand
XxxDataReader
XxxCommand
XxxConnection
sp_SELECT
sp_UPDATE
sp_INSERT
sp_DELETE
Demo
XML Support
ADO.NET is tightly integrated with XML
 Using XML in a disconnected application

XML Web Services
Client
Data Source
1
Request data
2
4
XML
DataSet
3
5
Updated XML
DataSet
6
SQL query
Results
SQL updates
Object Spaces




Introduction to problem
What is Object Spaces?
What do we need?
How can we resolve the problem
Introduction to Problem


There’s something apparently missing
Current situation
public struct Customer
{
int customerID;
string customerName;
}
…..
Customer LoadCustomer(int id){…}
void SaveCustomer(Customer obj){…}
Database
What is Object Spaces?


Relation between object and database
Way to avoid long-drawn coding
ObjectSpace
Map Files
Database
What do we need?





Single table to single Object
Object hierarchy to many tables
Object hierarchy to single table
Single Object to multiple tables
Inheritance


All types in single table
Table for base type and related table per derived
type
How we can resolve problem





XML Again!
RSD – Relational Schema Definition
OSD – Object Schema Definition
MSD – Mapping Schema definition
MappingSchema class
RSD

Tables, fields and relations descriptions
<rsd:Database Name="Northwind" Owner="sa"
xmlns:rsd="http://schemas.microsoft.com/data/2002/09/28/rsd">
<r:Schema Name="dbo" xmlns:r="http://schemas.microsoft.com/data/2002/09/28/rsd">
<rsd:Tables>
<rsd:Table Name="Customers">
<rsd:Columns>
<rsd:Column Name="CustomerID" SqlType="nchar" Precision="5" />
<rsd:Column Name="CompanyName" SqlType="nvarchar" Precision="40" />
</rsd:Columns>
<rsd:Constraints>
<rsd:PrimaryKey Name="PK_Customers">
<rsd:ColumnRef Name="CustomerID" />
</rsd:PrimaryKey>
</rsd:Constraints>
</rsd:Table>
</rsd:Tables>
</r:Schema>
</rsd:Database>
OSD

Objects description
<osd:ExtendedObjectSchema Name="DataTypesOSD"
xmlns:osd="http://schemas.microsoft.com/data/.../persistenceschema">
<osd:Classes>
<osd:Class Name="Samples.Customer">
<osd:Member Name="Id" Key="true" />
<osd:Member Name="Company" />
</osd:Class>
</osd:Classes>
</osd:ExtendedObjectSchema>
MSD

Mapping Schemes
<m:MappingSchema xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping">
<m:DataSources>
<m:DataSource Name="NorthwindRSD" Type="SQL Server" Direction="Source">
<m:Schema Location="RSD.XML" />
<m:Variable Name="Customers" Select="Customers" />
</m:DataSource>
<m:DataSource Name="DataTypesOSD" Type="Object" Direction="Target">
<m:Schema Location="OSD.XML" />
</m:DataSource>
</m:DataSources>
<m:Mappings>
<m:Map SourceVariable="Customers" TargetSelect="Samples.Customer">
<m:FieldMap SourceField="CustomerID" TargetField="Id" />
<m:FieldMap SourceField="CompanyName" TargetField="Company" />
</m:Map>
</m:Mappings>
</m:MappingSchema>
ObjectSpace methods










BeginTransaction
Commit
Rollback
GetObject
GetObjectReader
GetObjectSet
MarkForDeletion
PersistChanges
Resync
StartTracking
msdn.microsoft.com/library