ADO.NET 2.0ADO.NET 2.0
CaptatorTlf: 8620 4242www.captator.dk
Henrik Lykke NielsenSoftwarearkitekt, Microsoft Regional Director for [email protected]: 2237 3311
AgendaAgenda
ProvidermodellenProvidermodellen Basis klasser og interfacesBasis klasser og interfaces Provider factories, ConnectionstrengeProvider factories, Connectionstrenge
Schema APISchema API DataSet, DataTable, DataViewDataSet, DataTable, DataView
Indexeringsmekanisme, serialiseringIndexeringsmekanisme, serialisering SqlBulkCopySqlBulkCopy
Asynkrone kaldAsynkrone kald DataBindingDataBinding
XML Parser
<xml>…
</xml>
Web/Win Form ControlsVS.NET
Designers
.NET Data Provider
DataAdapter DataReader
CommandConnection
… SQLOLEDB
VS.NET Class Generator
DataSetItemCust
Order
Kode
ADO.NET arkitekturenADO.NET arkitekturen
OleDbOleDb
OleDbConnectionOleDbConnection IDbConnectionIDbConnection
OleDbCommandOleDbCommand IDbCommandIDbCommand
OleDbDataAdapterOleDbDataAdapter DbDataAdapterDbDataAdapter
DataAdapterDataAdapter IDataAdapterIDataAdapter
IDbDataAdapterIDbDataAdapter IDataAdapterIDataAdapter
OleDbDataReaderOleDbDataReader IDataReaderIDataReader
IDataRecordIDataRecord
SqlClientSqlClient
SqlConnectionSqlConnection IDbConnectionIDbConnection
SqlCommandSqlCommand IDbCommandIDbCommand
SqlDataAdapterSqlDataAdapter DbDataAdapterDbDataAdapter
DataAdapterDataAdapter IDataAdapterIDataAdapter
IDbDataAdapterIDbDataAdapter IDataAdapterIDataAdapter
SqlDataReaderSqlDataReader IDataReaderIDataReader
IDataRecordIDataRecord
Basis klasser og interfaces i Basis klasser og interfaces i 1.11.1 Bemærk: Læses som at SqlDataAdapter arver fra DbDataAdapterBemærk: Læses som at SqlDataAdapter arver fra DbDataAdapter
OleDbOleDb
OleDbConnectionOleDbConnection DbConnectionDbConnection
IDbConnectionIDbConnection
OleDbCommandOleDbCommand DbCommandDbCommand
IDbCommandIDbCommand
OleDbDataAdapterOleDbDataAdapter DbDataAdapterDbDataAdapter
DataAdapterDataAdapter IDataAdapterIDataAdapter
IDbDataAdapterIDbDataAdapter IDataAdapterIDataAdapter
OleDbDataReaderOleDbDataReader DbDataReaderDbDataReader
IDataReaderIDataReader IDataRecordIDataRecord
SqlClientSqlClient
SqlConnectionSqlConnection DbConnectionDbConnection
IDbConnectionIDbConnection ISqlConnectionISqlConnection
IDbConnectionIDbConnection SqlCommandSqlCommand
DbCommandDbCommand IDbCommandIDbCommand
ISqlCommandISqlCommand IDbCommandIDbCommand
SqlDataAdapterSqlDataAdapter DbDataAdapterDbDataAdapter
DataAdapterDataAdapter IDataAdapterIDataAdapter
IDbDataAdapterIDbDataAdapter IDataAdapterIDataAdapter
SqlDataReaderSqlDataReader DbDataReaderDbDataReader
IDataReaderIDataReader IDataRecordIDataRecord
ISqlReaderISqlReader ISqlRecordISqlRecord
IDataRecordIDataRecord
Basis klasser og interfaces i Basis klasser og interfaces i 2.02.0
DbDataAdapter implementerer nu IDbDataAdapterDbDataAdapter implementerer nu IDbDataAdapter
Interfaces og basis klasse i Interfaces og basis klasse i 1.11.1
SqlClient klasseSqlClient klasse OleDb klasseOleDb klasse Basisklasse/interfaceBasisklasse/interface
SqlConnection OleDbConnection IDbConnection
SqlCommand OleDbCommand IDbCommand
SqlDataReader OleDbDataReader IDataReader IDataRecord
SqlTransaction OleDbTransaction IDbTransaction
SqlParameter OleDbParameter IDbDataParameter IDataParameter
SqlParameterCollection OleDbParameterCollection IDataParameterCollection IList
SqlDataAdapter OleDbDataAdapter DbDataAdapter DataAdapter IDbDataAdapter IDataAdapter
SqlCommandBuilder OleDbCommandBuilder
SqlException SystemException
OleDbException ExternalException SystemException
Interfaces og basis klasser i Interfaces og basis klasser i 2.02.0
De abstrakte basis klasser befinder De abstrakte basis klasser befinder sig i sig i System.Data.Common System.Data.Common namespacetnamespacet
SqlClient klasseSqlClient klasse Abstrakt basis klasseAbstrakt basis klasse InterfaceInterface
SqlConnection DbConnection IDbConnection
SqlCommand DbCommand IDbCommand
SqlDataReader DbDataReader IDataReader IDataRecord
SqlTransaction DbTransaction IDbTransaction
SqlParameter DbParameter IDbDataParameter IDataParameter
SqlParameterCollection DbParameterCollection IDataParameterCollection IList
SqlDataAdapter DbDataAdapter IDbDataAdapter IDataAdapter
SqlCommandBuilder DbCommandBuilder
SqlException DbException
SqlConnectionStringBuilder DbConnectionStringBuilder
Provider factoriesProvider factories
System.Data.Common.DbProviderFactorySystem.Data.Common.DbProviderFactory En abstrakt klasse der er basisklasse for de konkrete En abstrakt klasse der er basisklasse for de konkrete
factories: SqlClientFactory, OleDbFactory, ...factories: SqlClientFactory, OleDbFactory, ... Metoder:Metoder:
CreateConnection As DbConnectionCreateConnection As DbConnection CreateCommand As DbCommandCreateCommand As DbCommand CreateCommandBuilder, CreateCommandBuilder,
CreateConnectionStringBuilder, CreateDataAdapter, CreateConnectionStringBuilder, CreateDataAdapter, CreateParameter, CreatePermission, CreateParameter, CreatePermission, CreateDataSourceEnumerator, CreateDataSourceEnumerator, CanCreateDataSourceEnumeratorCanCreateDataSourceEnumerator
De konkrete factories har ingen konstruktører, men i De konkrete factories har ingen konstruktører, men i stedet en Instance-metodestedet en Instance-metode
Public ReadOnly Shared Instance As SqlClientFactoryPublic ReadOnly Shared Instance As SqlClientFactory
public readonly static SqlClientFactory Instancepublic readonly static SqlClientFactory Instance
VB
C#
Provider factoriesProvider factories
System.Data.Common.DbProviderFactoriesSystem.Data.Common.DbProviderFactories Util klasseUtil klasse Metoder:Metoder:
Public Shared Function GetFactory(ByVal providerInvariantName As String) _ As System.Data.Common.DbProviderFactory
Public Shared Function GetFactory(ByVal providerRow As System.Data.DataRow) _ As System.Data.Common.DbProviderFactory
Public Shared Function GetFactoryClasses() As System.Data.DataTable
Public Shared Function GetFactory(ByVal providerInvariantName As String) _ As System.Data.Common.DbProviderFactory
Public Shared Function GetFactory(ByVal providerRow As System.Data.DataRow) _ As System.Data.Common.DbProviderFactory
Public Shared Function GetFactoryClasses() As System.Data.DataTable
public static System.Data.Common.DbProviderFactory GetFactory( string providerInvariantName)
public static System.Data.Common.DbProviderFactory GetFactory( System.Data.DataRow providerRow)
public static System.Data.DataTable GetFactoryClasses()
public static System.Data.Common.DbProviderFactory GetFactory( string providerInvariantName)
public static System.Data.Common.DbProviderFactory GetFactory( System.Data.DataRow providerRow)
public static System.Data.DataTable GetFactoryClasses()
VB
C#
Provider factoriesProvider factories
DbProviderFactories er registreret i DbProviderFactories er registreret i machine.configmachine.config
GetFactoryClassesGetFactoryClasses
giver en DataTablegiver en DataTable
med ovenståendemed ovenstående
infoinfo
<system.data> <DbProviderFactories> <add name="Odbc Data Provider" invariant="System.Data.Odbc" support="BF" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OleDb Data Provider" ... /> <add name="OracleClient Data Provider" ... /> <add name="SqlClient Data Provider" ... /> <add name="SQL Server CE Data Provider" ... /> </DbProviderFactories></system.data>
<system.data> <DbProviderFactories> <add name="Odbc Data Provider" invariant="System.Data.Odbc" support="BF" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OleDb Data Provider" ... /> <add name="OracleClient Data Provider" ... /> <add name="SqlClient Data Provider" ... /> <add name="SQL Server CE Data Provider" ... /> </DbProviderFactories></system.data>
Attibut-navnAttibut-navn Column-navnColumn-navnname Name
invariant InvariantName
support SupportedClasses
description Description
type AssemblyQualifiedName
ConnectionStrengeConnectionStrenge
System.Data.Common.DbConnectionStringBuilSystem.Data.Common.DbConnectionStringBuilderderer et svagt-typet dictionaryer et svagt-typet dictionary
Dim factory As System.Data.Common.DbProviderFactoryfactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim connBuilder As System.Data.Common.DbConnectionStringBuilderconnBuilder = factory.CreateConnectionStringBuilder()
connBuilder.Add("Data Source", "localhost")connBuilder.Add("User Id", "DemoUser")connBuilder.Add("Password", "hemmeligt")
Dim factory As System.Data.Common.DbProviderFactoryfactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim connBuilder As System.Data.Common.DbConnectionStringBuilderconnBuilder = factory.CreateConnectionStringBuilder()
connBuilder.Add("Data Source", "localhost")connBuilder.Add("User Id", "DemoUser")connBuilder.Add("Password", "hemmeligt")
System.Data.Common.DbProviderFactory factory;factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common.DbConnectionStringBuilder connBuilder;connBuilder = factory.CreateConnectionStringBuilder();
connBuilder.Add("Data Source", "localhost");connBuilder.Add("User Id", "DemoUser");connBuilder.Add("Password", "hemmeligt");
System.Data.Common.DbProviderFactory factory;factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common.DbConnectionStringBuilder connBuilder;connBuilder = factory.CreateConnectionStringBuilder();
connBuilder.Add("Data Source", "localhost");connBuilder.Add("User Id", "DemoUser");connBuilder.Add("Password", "hemmeligt");
VB
C#
ConnectionStrengeConnectionStrenge
Nedarves af (mere eller mindre) typestærke Nedarves af (mere eller mindre) typestærke klasserklasser OdbcConnectionStringBuilderOdbcConnectionStringBuilder
Driver, DsnDriver, Dsn
OleDbConnectionStringBuilderOleDbConnectionStringBuilder DataSource, FileName, Provider, ...DataSource, FileName, Provider, ...
OracleClientConnectionStringBuilderOracleClientConnectionStringBuilder DataSource, IntegratedSecurity, DataSource, IntegratedSecurity,
LoadBalanceTimeout, MaxPoolSize, MinPoolSize, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, Password, PersistSecurityInfo, Pooling, Unicode, Password, PersistSecurityInfo, Pooling, Unicode, UserID, ...UserID, ...
SqlConnectionStringBuilderSqlConnectionStringBuilder ApplicationName, AsynchronousProcessing, ApplicationName, AsynchronousProcessing,
ConnectTimeout, DataSource, Encrypt, ConnectTimeout, DataSource, Encrypt, FailOverPartner, InitialCatalog, IntegratedSecurity, FailOverPartner, InitialCatalog, IntegratedSecurity, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, MultipleActiveResultSets, NetworkLibrary, Password, MultipleActiveResultSets, NetworkLibrary, Password, PersistSecurityInfo, Pooling, Replication, UserID, ...PersistSecurityInfo, Pooling, Replication, UserID, ...
Enumerering af data sourcesEnumerering af data sources
GetDataSources på en GetDataSources på en DbDataSourceEnumerator finder DbDataSourceEnumerator finder datasources (MS SQL) på nettetdatasources (MS SQL) på nettet
System.Data.Sql.SqlDataSourceEnumeratSystem.Data.Sql.SqlDataSourceEnumerator er den eneste konkrete or er den eneste konkrete implementationimplementation
Dim factory As System.Data.Common.DbProviderFactoryfactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim enumerator As System.Data.Common.DbDataSourceEnumeratorenumerator = factory.CreateDataSourceEnumerator()
Dim dataSourceTable As System.Data.DataTable = enumerator.GetDataSources()
Dim factory As System.Data.Common.DbProviderFactoryfactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim enumerator As System.Data.Common.DbDataSourceEnumeratorenumerator = factory.CreateDataSourceEnumerator()
Dim dataSourceTable As System.Data.DataTable = enumerator.GetDataSources()
System.Data.Common.DbProviderFactory factory;factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common.DbDataSourceEnumerator enumerator;enumerator = factory.CreateDataSourceEnumerator();
System.Data.DataTable dataSourceTable = enumerator.GetDataSources();
System.Data.Common.DbProviderFactory factory;factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common.DbDataSourceEnumerator enumerator;enumerator = factory.CreateDataSourceEnumerator();
System.Data.DataTable dataSourceTable = enumerator.GetDataSources();
VB
C#
Fælles Fælles programmeringsmodelprogrammeringsmodel Lettere for Microsoft (og andre) at udvide Lettere for Microsoft (og andre) at udvide
providereprovidere Mere ensartet programmeringsmodelMere ensartet programmeringsmodel Provider factories løser instansieringsproblemetProvider factories løser instansieringsproblemet
Gem providerens InvariantName i f.eks. Gem providerens InvariantName i f.eks. konfiguationsfilkonfiguationsfil
GetFactory der returnerer den enkelte GetFactory der returnerer den enkelte provider factory instans bruger reflektion – så provider factory instans bruger reflektion – så genbrug den!genbrug den!
Brug af basisklasser giver ikke performance Brug af basisklasser giver ikke performance overheadoverhead
GetFactoryClasses() god til udvikler/admin toolsGetFactoryClasses() god til udvikler/admin tools ConnectionStringBuilders gør det lettere at lave ConnectionStringBuilders gør det lettere at lave
connectionstrenge – men de enkelte er connectionstrenge – men de enkelte er (desværre) ret forskellige(desværre) ret forskellige
Schema APIetSchema APIet
API til at få schema-oplysninger om API til at få schema-oplysninger om en DataSourceen DataSource
DbConnections GetSchema-metode i DbConnections GetSchema-metode i tre formertre former
Public Function conn.GetSchema() As DataTable
Public Function conn.GetSchema(collectionName As String) As DataTable
Public Function conn.GetSchema(collectionName As String, _ restrictionValues As String()) As DataTable
Public Function conn.GetSchema() As DataTable
Public Function conn.GetSchema(collectionName As String) As DataTable
Public Function conn.GetSchema(collectionName As String, _ restrictionValues As String()) As DataTable
VB
public DataTable conn.GetSchema()
public DataTable conn.GetSchema(string collectionName)
public DataTable conn.GetSchema(string collectionName, string[] restrictionValues)
public DataTable conn.GetSchema()
public DataTable conn.GetSchema(string collectionName)
public DataTable conn.GetSchema(string collectionName, string[] restrictionValues)
C#
Schema - Schema - MetaDataCollectionsMetaDataCollections Få en liste over metadatacollectionsFå en liste over metadatacollections
Metadata collections:Metadata collections: MetaDataCollections, MetaDataCollections,
DataSourceInformation, DataTypes, DataSourceInformation, DataTypes, Restrictions, Users, Databases, Tables, Restrictions, Users, Databases, Tables, Columns, Views, ViewColumns, Columns, Views, ViewColumns, ProcedureParameters, Procedures, ProcedureParameters, Procedures, ForeignKeys, IndexColumns, Indexes, ForeignKeys, IndexColumns, Indexes, UserDefinesTypesUserDefinesTypes
Dim tbl As DataTable = conn.GetSchema()' ellertbl = conn.GetSchema("MetaDataCollections")
Dim tbl As DataTable = conn.GetSchema()' ellertbl = conn.GetSchema("MetaDataCollections")
DataTable tbl = conn.GetSchema();// ellerDataTable tbl = conn.GetSchema("MetaDataCollections");
DataTable tbl = conn.GetSchema();// ellerDataTable tbl = conn.GetSchema("MetaDataCollections");
VB
C#
Schema - Schema - DataSourceInformationDataSourceInformation Få informationer om datasourcenFå informationer om datasourcen
Indeholder én rowIndeholder én row Muligvis forskellige felter – ens for Muligvis forskellige felter – ens for
MS’s providereMS’s providere DataSourceProductName, DataSourceProductName,
DataSourceProductVersion, IdentifierPattern, DataSourceProductVersion, IdentifierPattern, ParameterMarkerFormat, ParameterMarkerPattern, ParameterMarkerFormat, ParameterMarkerPattern, ParameterNameMaxLength, ParameterNameMaxLength, ParameterNamePattern, QuotedIdentifierPattern, ParameterNamePattern, QuotedIdentifierPattern, StatementSeparatorPattern, StringLiteralPattern, StatementSeparatorPattern, StringLiteralPattern, SupportedJoinOperators, ...SupportedJoinOperators, ...
Dim tbl As DataTable = conn.GetSchema("DataSourceInformation")Dim tbl As DataTable = conn.GetSchema("DataSourceInformation")
DataTable tbl = conn.GetSchema("DataSourceInformation");DataTable tbl = conn.GetSchema("DataSourceInformation");
VB
C#
Schema - restriktionerSchema - restriktioner
Restriktioner er simple filtre for, hvilke entiteter Restriktioner er simple filtre for, hvilke entiteter man ønsker metadata forman ønsker metadata for
Restriktioner angives via værdier i et streng arrayRestriktioner angives via værdier i et streng array Restriktioner er defineret i ”Restrictions”-Restriktioner er defineret i ”Restrictions”-
collectionencollectionen Eksempel på brug af restrictionerEksempel på brug af restrictioner
Konkrete informationer varierer mellem providereKonkrete informationer varierer mellem providere
Dim res As String(3)res(1) = "Person"res(2) = "Contact"Dim tbl As System.Data.DataTable = conn.GetSchema("Columns", res)
Dim res As String(3)res(1) = "Person"res(2) = "Contact"Dim tbl As System.Data.DataTable = conn.GetSchema("Columns", res)
VB
string[] res = new string[4];res[1] = "Person"; res[2] = "Contact";System.Data.DataTable tbl = conn.GetSchema("Columns", res);
string[] res = new string[4];res[1] = "Person"; res[2] = "Contact";System.Data.DataTable tbl = conn.GetSchema("Columns", res);
C#
DataSet, DataTable - DataSet, DataTable - performanceperformance Ny indexeringsmekanisme giver markant Ny indexeringsmekanisme giver markant
forbedret performance ved for eksempel Fill forbedret performance ved for eksempel Fill og Mergeog Merge
public System.Data.DataTable CreateDataTable(int numberOfRows){ System.Random rnd = new System.Random();
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("Id", typeof(System.Guid)); tbl.Columns["Id"].Unique = true; tbl.Columns.Add("RandomValue", typeof(int));
for (int n = 0; n < numberOfRows; n++) { System.Data.DataRow row = tbl.NewRow(); row["Id"] = System.Guid.NewGuid(); row["RandomValue"] = rnd.Next(); tbl.Rows.Add(row); }
return tbl;}
public System.Data.DataTable CreateDataTable(int numberOfRows){ System.Random rnd = new System.Random();
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("Id", typeof(System.Guid)); tbl.Columns["Id"].Unique = true; tbl.Columns.Add("RandomValue", typeof(int));
for (int n = 0; n < numberOfRows; n++) { System.Data.DataRow row = tbl.NewRow(); row["Id"] = System.Guid.NewGuid(); row["RandomValue"] = rnd.Next(); tbl.Rows.Add(row); }
return tbl;}
C#
DataSet, DataTable - DataSet, DataTable - performanceperformance ResultaterResultater
0
200
400
600
800
1000
1200
1400
1600
100000 200000 500000 1000000
Antal rows
An
tal s
eku
nd
er
VS2003 - not unique VS2005 - not unique VS2003 - unique VS2005 - unique
Antal rows Antal rows VS2003VS2003ikke uniqueikke unique
VS2005VS2005ikke uniqueikke unique
VS2003VS2003uniqueunique
VS2005VS2005uniqueunique
100.000 1 sek 1 sek 6 sek 3 sek
200.000 1 sek 2 sek 33 sek 6 sek
500.000 3 sek 5 sek 322 sek 17 sek
1.000.000 6 sek 11 sek 1414 sek 38 sek
Dataset, DataTable - Dataset, DataTable - serialiseringserialisering DataSets og DataTables serialiserer sig DataSets og DataTables serialiserer sig
selv som XML uanset man bruger en selv som XML uanset man bruger en BinaryFormatterBinaryFormatter
Kan nu fortælle, at der skal serialiseres Kan nu fortælle, at der skal serialiseres binærtbinært
RemotingFormat-propertyen kan sættes tilRemotingFormat-propertyen kan sættes til System.Data.SerializationFormat.Xml System.Data.SerializationFormat.Xml
(default)(default) System.Data.SerializationFormat.BinarySystem.Data.SerializationFormat.Binary
Dataset, DataTable - Dataset, DataTable - serialiseringserialisering
Eksempel med 100.000 simple rows skrevet Eksempel med 100.000 simple rows skrevet til filtil fil XML-format: 16.813 KB – 19.851 KBXML-format: 16.813 KB – 19.851 KB Binært format: 835 KB – 3.374 KBBinært format: 835 KB – 3.374 KB
tbl.RemotingFormat = System.Data.SerializationFormat.Binary
Dim formatter As New BinaryFormatter()Dim st As New FileStream(fileName, FileMode.Create)formatter.Serialize(st, tbl)st.Close()
tbl.RemotingFormat = System.Data.SerializationFormat.Binary
Dim formatter As New BinaryFormatter()Dim st As New FileStream(fileName, FileMode.Create)formatter.Serialize(st, tbl)st.Close()
VB
tbl.RemotingFormat = System.Data.SerializationFormat.Binary;
BinaryFormatter formatter = new BinaryFormatter();FileStream st = new FileStream(fileName, FileMode.Create);formatter.Serialize(st, tbl);st.Close();
tbl.RemotingFormat = System.Data.SerializationFormat.Binary;
BinaryFormatter formatter = new BinaryFormatter();FileStream st = new FileStream(fileName, FileMode.Create);formatter.Serialize(st, tbl);st.Close();
C#
DataTable - XMLDataTable - XML
XML understøttelse som for DataSets:XML understøttelse som for DataSets:ReadXml, ReadXmlSchemaReadXml, ReadXmlSchemaWriteXml, WriteXmlSchemaWriteXml, WriteXmlSchema
DataSet, DataTable, DataSet, DataTable, DataReaderDataReader Populér DataTable/DataSet ud fra Populér DataTable/DataSet ud fra
DataReaderDataReader
Generer DataReader ud fra Generer DataReader ud fra DataTable/DataSetDataTable/DataSet
Hvis CreateDataReader kaldes på et DataSet med Hvis CreateDataReader kaldes på et DataSet med flere tabeller vil DataReaderen indeholde multiple flere tabeller vil DataReaderen indeholde multiple resultsetresultset
' Tag fat i en database …Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()Dim tbl As New System.Data.DataTable()tbl.Load(dr)
' Tag fat i en database …Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()Dim tbl As New System.Data.DataTable()tbl.Load(dr)
VB
// Tag fat i en database …System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();System.Data.DataTable tbl = new System.Data.DataTable();tbl.Load(dr);
// Tag fat i en database …System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();System.Data.DataTable tbl = new System.Data.DataTable();tbl.Load(dr);
C#
Dim tbl As System.Data.DataTable = LavEnDataTable()Dim dr As System.Data.DataTableReader = tbl.CreateDataReader()
Dim tbl As System.Data.DataTable = LavEnDataTable()Dim dr As System.Data.DataTableReader = tbl.CreateDataReader()
VB
System.Data.DataTable tbl = LavEnDataTable();System.Data.DataTableReader dr = tbl.CreateDataReader();
System.Data.DataTable tbl = LavEnDataTable();System.Data.DataTableReader dr = tbl.CreateDataReader(); C#
DataTable, DataViewDataTable, DataView
Generer en DataTable ud fra et Generer en DataTable ud fra et DataViewDataView
Dim tbl As System.Data.DataTable = LavEnDataTable()
Dim view As New System.Data.DataView(tbl)view.Sort = "FirstName ASC, LastName DESC"view.RowFilter = "FirstName>'C'"
Dim columnNames As String() = { "FirstName", "LastName" }
Dim tblFiltered As System.Data.DataTabletblFiltered = view.ToTable("MinFiltreredeTabel", False, columnNames)
Dim tbl As System.Data.DataTable = LavEnDataTable()
Dim view As New System.Data.DataView(tbl)view.Sort = "FirstName ASC, LastName DESC"view.RowFilter = "FirstName>'C'"
Dim columnNames As String() = { "FirstName", "LastName" }
Dim tblFiltered As System.Data.DataTabletblFiltered = view.ToTable("MinFiltreredeTabel", False, columnNames)
VB
System.Data.DataTable tbl = LavEnDataTable();
System.Data.DataView view = new System.Data.DataView(tbl);view.Sort = "FirstName ASC, LastName DESC";view.RowFilter = "FirstName>'C'";
string[] columnNames = { "FirstName", "LastName" };
System.Data.DataTable tblFiltered;tblFiltered = view.ToTable("MinFiltreredeTabel", false, columnNames);
System.Data.DataTable tbl = LavEnDataTable();
System.Data.DataView view = new System.Data.DataView(tbl);view.Sort = "FirstName ASC, LastName DESC";view.RowFilter = "FirstName>'C'";
string[] columnNames = { "FirstName", "LastName" };
System.Data.DataTable tblFiltered;tblFiltered = view.ToTable("MinFiltreredeTabel", false, columnNames);
C#
Connection statistik - Connection statistik - SqlClientSqlClient StatisticsEnabled, RetrieveStatisticsStatisticsEnabled, RetrieveStatistics
conn.StatisticsEnabled = true;
System.Collections.Hashtable statistics = (System.Collections.Hashtable)_conn.RetrieveStatistics();
txtStatistics.Text = "";
foreach (string key in statistics.Keys){ txtStatistics.Text += key.PadRight(20, ' ') + "= " + statistics[key].ToString() + "\r\n";}
conn.StatisticsEnabled = true;
System.Collections.Hashtable statistics = (System.Collections.Hashtable)_conn.RetrieveStatistics();
txtStatistics.Text = "";
foreach (string key in statistics.Keys){ txtStatistics.Text += key.PadRight(20, ' ') + "= " + statistics[key].ToString() + "\r\n";}
NetworkServerTime = 50BytesReceived = 20989728UnpreparedExecs = 12SumResultSets = 12SelectCount = 12PreparedExecs = 0ConnectionTime = 113202ExecutionTime = 3194Prepares = 0
NetworkServerTime = 50BytesReceived = 20989728UnpreparedExecs = 12SumResultSets = 12SelectCount = 12PreparedExecs = 0ConnectionTime = 113202ExecutionTime = 3194Prepares = 0
BuffersSent = 12SelectRows = 239664ServerRoundtrips = 12CursorOpens = 0Transactions = 0BytesSent = 1968BuffersReceived = 2628IduRows = 0IduCount = 0
BuffersSent = 12SelectRows = 239664ServerRoundtrips = 12CursorOpens = 0Transactions = 0BytesSent = 1968BuffersReceived = 2628IduRows = 0IduCount = 0
C#
Multiple Active ResultSets Multiple Active ResultSets (MARS)(MARS) SQL Server 2005 featureSQL Server 2005 feature Man kan nu have flere resultsets åbne på Man kan nu have flere resultsets åbne på
samme tid– flere DataReadere om en samme tid– flere DataReadere om en connection (hver sin command)connection (hver sin command)
SqlClient.SqlConnection conn = new SqlClient.SqlConnection(connString);conn.Open();
SqlClient.SqlCommand cmd1 = new SqlClient.SqlCommand(sql1, conn);SqlClient.SqlDataReader rd1 = cmd1.ExecuteReader();
rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read();
SqlClient.SqlCommand cmd2 = new SqlClient.SqlCommand(sql2, conn);SqlClient.SqlDataReader rd2 = cmd2.ExecuteReader();
rd2.Read(); rd1.Read(); rd2.Read();
rd2.Close();rd1.Close();conn.Close();
SqlClient.SqlConnection conn = new SqlClient.SqlConnection(connString);conn.Open();
SqlClient.SqlCommand cmd1 = new SqlClient.SqlCommand(sql1, conn);SqlClient.SqlDataReader rd1 = cmd1.ExecuteReader();
rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read();
SqlClient.SqlCommand cmd2 = new SqlClient.SqlCommand(sql2, conn);SqlClient.SqlDataReader rd2 = cmd2.ExecuteReader();
rd2.Read(); rd1.Read(); rd2.Read();
rd2.Close();rd1.Close();conn.Close();
Op til 9 poolede commands per connectionOp til 9 poolede commands per connection
C#
UpdateBatchSize og UpdateBatchSize og SqlBulkCopySqlBulkCopy DbDataAdapter.UpdateBatchSize property DbDataAdapter.UpdateBatchSize property
påvirker DbDataAdapter.Updatepåvirker DbDataAdapter.Update System.Data.SqlClient.SqlBulkCopy påvirker System.Data.SqlClient.SqlBulkCopy påvirker
performanceperformance
100.000 rows i en DataTable100.000 rows i en DataTable DbDataAdapter.Update tog 70 sekunder DbDataAdapter.Update tog 70 sekunder
(BatchSize=100)(BatchSize=100) SqlBulkCopy.WriteToServer tog 3 sekunderSqlBulkCopy.WriteToServer tog 3 sekunder
System.Data.DataTable tbl = CreateDataTable(100000);
System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(connString);
bulk.DestinationTableName = "RandomTable";bulk.WriteToServer(tbl);
bulk.Close();
System.Data.DataTable tbl = CreateDataTable(100000);
System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(connString);
bulk.DestinationTableName = "RandomTable";bulk.WriteToServer(tbl);
bulk.Close();
C#
Asynkrone kaldAsynkrone kald
Asynkrone kommandoer i ADO.NET 1.1 og ADO.NET 2.0Asynkrone kommandoer i ADO.NET 1.1 og ADO.NET 2.0 I 1.1 er det muligt at lave asynkrone kald ved at bruge I 1.1 er det muligt at lave asynkrone kald ved at bruge
ThreadPool eller asynkrone delegates – en baggrundstråd ThreadPool eller asynkrone delegates – en baggrundstråd blokeresblokeres
Asynkrone kommandoer i 2.0 blokerer Asynkrone kommandoer i 2.0 blokerer ingeningen baggrundstrådebaggrundstråde
Brugen af asynkrone kommandoer til ADO.NET (SqlClient)Brugen af asynkrone kommandoer til ADO.NET (SqlClient) I scenarier hvor det er vigtigt I scenarier hvor det er vigtigt ikkeikke at blokere tråde (fx. at blokere tråde (fx.
Web apps)Web apps) Designet som resten af de asynkrone arkitekturer i .NETDesignet som resten af de asynkrone arkitekturer i .NET Xxx => BeginXxx med input-parms og EndXxx med Xxx => BeginXxx med input-parms og EndXxx med
output-parms og retur-værdioutput-parms og retur-værdiSynkron MetodeSynkron Metode Asynkrone MetoderAsynkrone Metoder
BeginXxxBeginXxx EndXxx EndXxx
ExecuteNonQuery ExecuteNonQuery BeginExecuteNonQuery BeginExecuteNonQuery EndExecuteNonQuery EndExecuteNonQuery
ExecuteReader ExecuteReader BeginExecuteReader BeginExecuteReader EndExecuteReader EndExecuteReader
ExecuteXmlReader ExecuteXmlReader BeginExecuteXmlReader BeginExecuteXmlReader EndExecuteXmlReader EndExecuteXmlReader
Asynkrone kaldAsynkrone kald
OpsætningOpsætning ””Asynchronous Processing=true” eller ”async=true” i Asynchronous Processing=true” eller ”async=true” i
ConnectionstringConnectionstring Brug forskellige connections til synkrone og asynkrone Brug forskellige connections til synkrone og asynkrone
connections (af hensyn til performance)connections (af hensyn til performance) Der findes 4 måder at afslutte et asynkront kald på:Der findes 4 måder at afslutte et asynkront kald på:
Callback – angiv metode der skal kaldes når arbejde udførtCallback – angiv metode der skal kaldes når arbejde udført Synkroniseringsobjekt – koordinering af asynkrone kald via Synkroniseringsobjekt – koordinering af asynkrone kald via
WaitHandleWaitHandle Polling – check på IAsyncResults property IsCompletedPolling – check på IAsyncResults property IsCompleted Kald EndXxx som vil blokere indtil det asynkrone kald er Kald EndXxx som vil blokere indtil det asynkrone kald er
færdigtfærdigt Vær opmærksom på:Vær opmærksom på:
Kald EndXxx, når et asynkront kald er færdigt (for at undgå Kald EndXxx, når et asynkront kald er færdigt (for at undgå ressource-leaks)ressource-leaks)
Der kan kastes exceptions ved både BeginXxx og EndXxxDer kan kastes exceptions ved både BeginXxx og EndXxx
DataBindingDataBinding
System.Windows.Forms.BindingSource System.Windows.Forms.BindingSource fungererfungerer som en datakilde for kontrollersom en datakilde for kontroller som en mekanisme for opdatering af som en mekanisme for opdatering af
datadata som et datalager (datasource) i sig selv som et datalager (datasource) i sig selv
Kan bindes til forskellige datasourcesKan bindes til forskellige datasources Simple objekter (benytter properties)Simple objekter (benytter properties) Lister såsom ArrayListLister såsom ArrayList Komplekse datakilder såsom DataTablesKomplekse datakilder såsom DataTables
DataBindingDataBinding
Sæt BindingSources DataSource-property til Sæt BindingSources DataSource-property til datakildendatakilden
DataSource kan også sættes til en DataSource kan også sættes til en System.TypeSystem.Type Informationen videregives til kontroller Informationen videregives til kontroller
der bindesder bindesDim binding As System.Windows.Forms.BindingSource
binding = New System.Windows.Forms.BindingSource()grdPerson.DataSource = binding
Dim p As Personp = New Person("Mr", "Anders", "And", "[email protected]")binding.Add(p)p = New Person("Ms", "Andersine", "And", "[email protected]")binding.Add(p)p = New Person("Mr", "Mikkel", "Mus", "[email protected]")binding.Add(p)
Dim binding As System.Windows.Forms.BindingSource
binding = New System.Windows.Forms.BindingSource()grdPerson.DataSource = binding
Dim p As Personp = New Person("Mr", "Anders", "And", "[email protected]")binding.Add(p)p = New Person("Ms", "Andersine", "And", "[email protected]")binding.Add(p)p = New Person("Mr", "Mikkel", "Mus", "[email protected]")binding.Add(p)
VB
DataBindingDataBinding
Events såsom:Events såsom: AddingNew, CurrentItemChanged, AddingNew, CurrentItemChanged,
PositionChangedPositionChanged ListChanged(ListChangedType: Added, Changed, ListChanged(ListChangedType: Added, Changed,
Removed, Moved, ...)Removed, Moved, ...) Properties såsom:Properties såsom:
Current, Filter, Position, Sort, ListCountCurrent, Filter, Position, Sort, ListCount Metoder såsom:Metoder såsom:
Move*, Add, Clear, Remove, RemoveFIlter, Move*, Add, Clear, Remove, RemoveFIlter, RemoveSortRemoveSort
ResetBindings-metoden fortæller ResetBindings-metoden fortæller kontrollerne, at data har ændret sigkontrollerne, at data har ændret sig
DataBinding- DataBinding- BindingNavigatorBindingNavigator En video kontrol der kan bruges til En video kontrol der kan bruges til
navigering og redigeringnavigering og redigering Sæt BindingNavigator-komponentens Sæt BindingNavigator-komponentens
BindingSource-property til et BindingSource-property til et BindingSource objektBindingSource objekt
ToolStripButtons kan let tilføjes og fjernesToolStripButtons kan let tilføjes og fjernes
SpørgsmålSpørgsmål
www.captator.www.captator.dkdk
nyheder, artikler, nyheder, artikler, information, ...information, ...
Top Related