DAT430DAT430

Extending MicrosoftExtending Microsoft®® ADO.NET ADO.NET Building a Custom Data Factory Building a Custom Data Factory APIAPI  

Gert E.R. DrapersGert E.R. DrapersArchitectArchitectMicrosoft Corp.Microsoft Corp.

AgendaAgenda

• ADO.NET Provider ArchitectureADO.NET Provider Architecture• Purpose of a Data FactoryPurpose of a Data Factory• Abstracting StatementsAbstracting Statements• Using CachingUsing Caching

ADO.NET ConceptsADO.NET Concepts

• ADO.NET ArchitectureADO.NET Architecture– Split into DataSet and .NET Data ProvidersSplit into DataSet and .NET Data Providers– Exposes two distinct programming modelsExposes two distinct programming models• Disconnected dataDisconnected data• Streamed dataStreamed data

– Use of these models affects your design Use of these models affects your design decisionsdecisions

.NET Data Provider.NET Data Provider

IDataReaderIDataReader

ExceptionException ErrorError

CommandBuilderCommandBuilder

IDbCommandIDbCommand IDataParameterIDataParameterIDbConnectionIDbConnectionData SourceData Source

IDbTransactionIDbTransaction

IDbDataAdapterIDbDataAdapterDbDataAdapterDbDataAdapterIDataAdapterIDataAdapterDataAdapterDataAdapter

DataSetDataSet

ADO.NET ArchitectureADO.NET Architecture

SqlClient .NET Data ProviderSqlClient .NET Data Provider

SqlDataReaderSqlDataReader

DataSetDataSet

SqlExceptionSqlException SqlErrorSqlError

SqlCommandBuilderSqlCommandBuilder

SqlCommandSqlCommand SqlParameterSqlParameterSqlConnectionSqlConnectionSQLServerSQLServer

SqlTransactionSqlTransaction

SqlDataAdapterSqlDataAdapter

ADO.NET ArchitectureADO.NET Architecture

ADO.NET In ContextADO.NET In Context

• Assume a multi-tier architectureAssume a multi-tier architecture– Separation of UI from data access codeSeparation of UI from data access code• Role based development (data access specialist)Role based development (data access specialist)

– Encapsulation of data access code into distinct Encapsulation of data access code into distinct classesclasses• Reusability and maintainabilityReusability and maintainability• Abstraction of the data sourceAbstraction of the data source

– Data Access classes have both an external and Data Access classes have both an external and internal viewinternal view

Data Access ArchitectureData Access Architecture

• External ViewExternal View– You choose how to represent the dataYou choose how to represent the data

Data Services(black box)

Client (business or presentation services)

? = o.GetTitles(isbn)

return ?

DataSet Data ReaderCustom Object

Data Access ArchitectureData Access Architecture• Internal ViewInternal View– Use one of these choices inside your data access Use one of these choices inside your data access

classesclasses

Data ServicesData Services

Method Invoked Method Invoked from Clientfrom Client

.NET Data .NET Data ProviderProvider

GetTitlesGetTitlesMethod Method

BodyBody

DirectDirect??

Provider Provider FactoryFactory

??

Data FactoryData Factory

DataSetDataSetData readersData readers

??

Caching, other Caching, other objectsobjects

DataSetDataSet DataReaderDataReader Custom Custom ObjectObject

demodemo

Provider FactoryProvider Factory

Purpose Of A Data FactoryPurpose Of A Data Factory

• Reduce the amount of code you have to writeReduce the amount of code you have to write– By creating command objects and populating By creating command objects and populating

parameters automaticallyparameters automatically

• Provider IndependenceProvider Independence– Not locked into one .NET Data ProviderNot locked into one .NET Data Provider

• Database IndependenceDatabase Independence– Target application for SQL Server or Oracle without Target application for SQL Server or Oracle without

changing any code!changing any code!

• PerformancePerformance

Data Factory APIData Factory API

• CacheFilePathCacheFilePath• ConnectionConnection• ProviderProvider• UseCacheUseCache• BeginTransactionBeginTransaction• CreateDataAdapterCreateDataAdapter• CreateSqlFilesCreateSqlFiles• CreateSqlFileCreateSqlFile• ExecuteDataReaderExecuteDataReader• ExecuteNonQueryExecuteNonQuery• ExecuteScalarExecuteScalar

• ExecuteSqlXmlReaderExecuteSqlXmlReader• GetDataSetGetDataSet• GetDataTableGetDataTable• GetProvidersGetProviders• GetXsltResultsGetXsltResults• RemoveStatementRemoveStatement• SyncDataSetSyncDataSet• SqlFilesCreated (event)SqlFilesCreated (event)

Data Factory FeaturesData Factory Features

• Abstracts providersAbstracts providers– Hardcoded SqlClient and OleDb but configurable Hardcoded SqlClient and OleDb but configurable

through DataFactory.config filethrough DataFactory.config file

• Database IndependentDatabase Independent– Uses statement files (XML) that abstract the SQLUses statement files (XML) that abstract the SQL

• Caches Command ObjectsCaches Command Objects– Uses a synchronized Hashtable and relies on Uses a synchronized Hashtable and relies on

ICloneable interfaceICloneable interface

Provider AbstractionProvider Abstraction

• Relies on the Provider propertyRelies on the Provider property• Uses System.Reflection to dynamically Uses System.Reflection to dynamically

create the appropriate objectscreate the appropriate objects• Can be extended for custom providersCan be extended for custom providers

<?xml version="1.0" encoding="utf-8" ?> <?xml version="1.0" encoding="utf-8" ?> <DataFactory><DataFactory> <Provider name="Cbks" assembly=“computebooksData.dll"> <Provider name="Cbks" assembly=“computebooksData.dll"> <Command type="ComputeBooks.Data.CbksCommand" /> <Command type="ComputeBooks.Data.CbksCommand" /> <Parameter type="ComputeBooks.Data.CbksParameter" /> <Parameter type="ComputeBooks.Data.CbksParameter" /> <DataAdapter type="CompuetBooks.Data.CbksDataAdapter" /> <DataAdapter type="CompuetBooks.Data.CbksDataAdapter" /> <DataReader type="ComputeBooks.Data.CbksDataReader" /> <DataReader type="ComputeBooks.Data.CbksDataReader" /> </Provider> </Provider></DataFactory></DataFactory>

demodemo

Provider AbstractionProvider Abstraction

Database IndependenceDatabase Independence

• Relies on statement files and two internal Relies on statement files and two internal structuresstructures– Synchronized provider-specific Hashtable for Synchronized provider-specific Hashtable for

cachingcaching<?xml version="1.0" encoding="utf-8" ?> <?xml version="1.0" encoding="utf-8" ?> <DataFactory><DataFactory> <Statement name="GetTitles" type="StoredProcedure"> <Statement name="GetTitles" type="StoredProcedure"> <Sql>usp_GetTitles</Sql> <Sql>usp_GetTitles</Sql> <Parameters> <Parameters> <Param name="author" SqlName="@author" type="string“ <Param name="author" SqlName="@author" type="string“ maxLength="30" direction="Input" /> maxLength="30" direction="Input" /> <Param name="title" SqlName="@title" type="string“ <Param name="title" SqlName="@title" type="string“ maxLength="100" direction="Input" /> maxLength="100" direction="Input" /> </Parameters> </Parameters> </Statement> </Statement></DataFactory></DataFactory>

demodemo

Using Statement Files And Using Statement Files And CachingCaching

Statement FilesStatement Files

• Can be created automatically using the Can be created automatically using the CreateSqlFile and CreateSqlFiles shared CreateSqlFile and CreateSqlFiles shared methodsmethods– Based on a procedure prefixBased on a procedure prefix– Works for SQL ServerWorks for SQL Server• Uses sp_procedure_params_rowset Uses sp_procedure_params_rowset

– CreateSqlFiles uses asynchronous delegate to CreateSqlFiles uses asynchronous delegate to run on a background threadrun on a background thread

demodemo

Creating Statement FilesCreating Statement Files

Statement FlowStatement Flow

• Using a shared HashtableUsing a shared Hashtable

Is statement in the cache?

Load and parse statement file

Pull out the command object and clone it

Populate the parameters

Return Command

Execute a statement

Create the command object with parameters

NoNo

YesYes

Using The Data FactoryUsing The Data Factory

Public Function GetTitles(ByVal author As String, _Public Function GetTitles(ByVal author As String, _ ByVal title As String) As DataSet ByVal title As String) As DataSet dim cn As New SqlConnection(_connect) dim cn As New SqlConnection(_connect) Dim daSql As SqlDataAdapter Dim daSql As SqlDataAdapter Dim parmSql As SqlParameter Dim parmSql As SqlParameter Dim ds As New DataSet() Dim ds As New DataSet()

TryTry daSql = New SqlDataAdapter("usp_GetTitle", cn) daSql = New SqlDataAdapter("usp_GetTitle", cn) daSql.SelectCommand.CommandType = CommandType.StoredProcedure daSql.SelectCommand.CommandType = CommandType.StoredProcedure parmSql = daSql.SelectCommand.Parameters.Add( _ parmSql = daSql.SelectCommand.Parameters.Add( _ New SqlParameter("@author", SqlDbType.varchar, 50)) New SqlParameter("@author", SqlDbType.varchar, 50)) parmSql.Value = author parmSql.Value = author parmSql = daSql.SelectCommand.Parameters.Add( _ parmSql = daSql.SelectCommand.Parameters.Add( _ New SqlParameter("@title", SqlDbType.varchar, 200)) New SqlParameter("@title", SqlDbType.varchar, 200)) parmSql.Value = title parmSql.Value = title daSql.Fill(ds) daSql.Fill(ds) Return ds Return ds Catch e As SqlException Catch e As SqlException ‘ Handle Error ‘ Handle Error End Try End TryEnd FunctionEnd Function

• Client code before and afterClient code before and after– Overall 30-40% reduction in codeOverall 30-40% reduction in code

Public Function GetTitles(ByVal author As String, _Public Function GetTitles(ByVal author As String, _ ByVal title As String) As DataSet ByVal title As String) As DataSet Dim parms As New HybridDictionary() Dim parms As New HybridDictionary() Dim ds As New DataSet() Dim ds As New DataSet()

TryTry parms.Add(“author”, author) parms.Add(“author”, author) parms.add(“title”, title) parms.add(“title”, title) ds = _df.GetDataSet(“GetTitles”,parms) ds = _df.GetDataSet(“GetTitles”,parms) Return ds Return ds Catch e As DataFactoryException Catch e As DataFactoryException ‘ Handle Error ‘ Handle Error End Try End TryEnd FunctionEnd Function

• Client code before and afterClient code before and after– Overall 30-40% reduction in codeOverall 30-40% reduction in code

Using The Data FactoryUsing The Data Factory

Performance and FuturesPerformance and Futures

• Results from Application Center TestResults from Application Center Test– In an ASP.NET application the site performed In an ASP.NET application the site performed

slightly better using the DataFactoryslightly better using the DataFactory– Could be improved by caching the creation of Could be improved by caching the creation of

the type objectsthe type objects

Questions?Questions?

Resources from Microsoft PressResources from Microsoft Press

For more information please visit the TechEd Bookshop.For more information please visit the TechEd Bookshop.www.microsoft.com/mspresswww.microsoft.com/mspress

ADO .NET

Don’t forget to complete the Don’t forget to complete the on-line Session Feedback form on-line Session Feedback form on the Attendee Web siteon the Attendee Web site

https://web.mseventseurope.com/teched/https://web.mseventseurope.com/teched/