Transcript DAT430: Extending ADO.NET Building A Custom Data Factory API

DAT430
Extending Microsoft® ADO.NET
Building a Custom Data Factory
API
Gert E.R. Drapers
Architect
Microsoft Corp.
Agenda
•
•
•
•
ADO.NET Provider Architecture
Purpose of a Data Factory
Abstracting Statements
Using Caching
ADO.NET Concepts
• ADO.NET Architecture
– Split into DataSet and .NET Data Providers
– Exposes two distinct programming models
• Disconnected data
• Streamed data
– Use of these models affects your design
decisions
ADO.NET Architecture
DataSet
IDbDataAdapter
DbDataAdapter
IDataAdapter
DataAdapter
IDbConnection
CommandBuilder
IDataReader
IDbCommand
IDataParameter
Exception
Error
Data Source
IDbTransaction
.NET Data Provider
ADO.NET Architecture
DataSet
SqlCommandBuilder
SqlDataReader
SqlDataAdapter
SqlConnection
SqlCommand
SqlParameter
SqlException
SqlError
SQLServer
SqlTransaction
SqlClient .NET Data Provider
ADO.NET In Context
• Assume a multi-tier architecture
– Separation of UI from data access code
• Role based development (data access specialist)
– Encapsulation of data access code into distinct
classes
• Reusability and maintainability
• Abstraction of the data source
– Data Access classes have both an external and
internal view
Data Access Architecture
• External View
– You choose how to represent the data
Client
(business or presentation services)
? = o.GetTitles(isbn)
return ?
DataSet
Data Services
(black box)
Data Reader
Custom
Object
Data Access Architecture
• Internal View
– Use one of these choices inside your data access
classes
DataSet
Method Invoked
from Client
DataReader
Custom
Object
?
Direct
GetTitles
Method
Body
?
?
Provider
Factory
Data Factory
DataSet
Data readers
Data Services
Caching, other
objects
.NET Data
Provider
demo
Provider Factory
Purpose Of A Data Factory
• Reduce the amount of code you have to write
– By creating command objects and populating
parameters automatically
• Provider Independence
– Not locked into one .NET Data Provider
• Database Independence
– Target application for SQL Server or Oracle without
changing any code!
• Performance
Data Factory API
•
•
•
•
•
•
•
•
•
•
•
CacheFilePath
Connection
Provider
UseCache
BeginTransaction
CreateDataAdapter
CreateSqlFiles
CreateSqlFile
ExecuteDataReader
ExecuteNonQuery
ExecuteScalar
•
•
•
•
•
•
•
•
ExecuteSqlXmlReader
GetDataSet
GetDataTable
GetProviders
GetXsltResults
RemoveStatement
SyncDataSet
SqlFilesCreated (event)
Data Factory Features
• Abstracts providers
– Hardcoded SqlClient and OleDb but
configurable through DataFactory.config file
• Database Independent
– Uses statement files (XML) that abstract the
SQL
• Caches Command Objects
– Uses a synchronized Hashtable and relies on
ICloneable interface
Provider Abstraction
• Relies on the Provider property
• Uses System.Reflection to dynamically
create the appropriate objects
• Can be extended for custom providers
<?xml version="1.0" encoding="utf-8" ?>
<DataFactory>
<Provider name="Cbks" assembly=“computebooksData.dll">
<Command type="ComputeBooks.Data.CbksCommand" />
<Parameter type="ComputeBooks.Data.CbksParameter" />
<DataAdapter type="CompuetBooks.Data.CbksDataAdapter" />
<DataReader type="ComputeBooks.Data.CbksDataReader" />
</Provider>
</DataFactory>
demo
Provider Abstraction
Database Independence
• Relies on statement files and two internal
structures
– Synchronized provider-specific Hashtable for
caching
<?xml version="1.0" encoding="utf-8" ?>
<DataFactory>
<Statement name="GetTitles" type="StoredProcedure">
<Sql>usp_GetTitles</Sql>
<Parameters>
<Param name="author" SqlName="@author" type="string“
maxLength="30" direction="Input" />
<Param name="title" SqlName="@title" type="string“
maxLength="100" direction="Input" />
</Parameters>
</Statement>
</DataFactory>
demo
Using Statement Files And
Caching
Statement Files
• Can be created automatically using the
CreateSqlFile and CreateSqlFiles shared
methods
– Based on a procedure prefix
– Works for SQL Server
• Uses sp_procedure_params_rowset
– CreateSqlFiles uses asynchronous delegate to
run on a background thread
demo
Creating Statement Files
Statement Flow
• Using a shared Hashtable
Execute a statement
Is statement
in the cache?
No
Load and parse statement file
Yes
Create the command object
with parameters
Pull out the command
object and clone it
Return
Command
Populate the
parameters
Using The Data Factory
• Client code before and after
– Overall 30-40% reduction in code
Public
ByVal
dim
Dim
Dim
Dim
Function GetTitles(ByVal author As String, _
title As String) As DataSet
cn As New SqlConnection(_connect)
daSql As SqlDataAdapter
parmSql As SqlParameter
ds As New DataSet()
Try
daSql = New SqlDataAdapter("usp_GetTitle", cn)
daSql.SelectCommand.CommandType = CommandType.StoredProcedure
parmSql = daSql.SelectCommand.Parameters.Add( _
New SqlParameter("@author", SqlDbType.varchar, 50))
parmSql.Value = author
parmSql = daSql.SelectCommand.Parameters.Add( _
New SqlParameter("@title", SqlDbType.varchar, 200))
parmSql.Value = title
daSql.Fill(ds)
Return ds
Catch e As SqlException
‘ Handle Error
End Try
End Function
Using The Data Factory
• Client code before and after
– Overall 30-40% reduction in code
Public
ByVal
Dim
Dim
Function GetTitles(ByVal author As String, _
title As String) As DataSet
parms As New HybridDictionary()
ds As New DataSet()
Try
parms.Add(“author”, author)
parms.add(“title”, title)
ds = _df.GetDataSet(“GetTitles”,parms)
Return ds
Catch e As DataFactoryException
‘ Handle Error
End Try
End Function
Performance and Futures
• Results from Application Center Test
– In an ASP.NET application the site performed
slightly better using the DataFactory
– Could be improved by caching the creation of
the type objects
Questions?
Resources from Microsoft Press
ADO .NET
For more information please visit the TechEd Bookshop.
www.microsoft.com/mspress
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/