1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
* Provider Factory
<p>Brian Ritchie contributed a Provider Factory and Data Tools for Mono ADO.NET<br>
which gives us a foundation for abstract data provider access within Mono
** Here are the deails:
<ul>
<li>The Provider information is seperate from the connection string information.<br>
This allows the list of providers to be stored in the machine.config file.</li>
<li>Provider and ProviderCollection objects are available to access <br>
the list of providers and modify them at runtime.</li>
<li>The ProviderFactory object is used to create new connections, commands,<br>
dataadapters, parameters, etc.</li>
</ul>
** Overview of the ProviderFactory object model:
<ul>
<li><b>ProviderFactory</b>: used to create new Connections, Commands,<br>
DataAdapters, or Commands. All objects are returned using <br>
the provider interfaces such as IDbConnection, IDbCommand,<br>
IDbDataAdapter, or IDataParamter</li>
<li><b>DataTools</b>: static methods for doing <br>
common tasks like filling a DataSet <br>
with the contents of a select statement.</li>
<li><b>ProviderCollection</b>: list of providers configured <br>
in the system. Initially loaded from app.config, but can <br>
be modified at run-time.</li>
<li><b>Provider</b>: represents a given provider (factory) <br>
and holds information needed to <br>
create the types.</li>
<li><b>ProviderSectionHandler</b>: works behind the <br>
scenes to load the list of <br>
providers from the app.config into a ProviderCollection.</li>
</ul>
** C# source code samples for creating a connection:
<pre>
// Create connection using enhanced connection string
// The factory attribute specifies which provider
// to use. The factory attribute is parsed out, the
// object is created, and then the rest of the
// connection string is passed into the provider. The
// providers are defined in
// the app.config (or machine.config).
IDbConnection conn;
string connectionString =
"factory=System.Data.SqlClient;" +
"server=speedy;database=pubs;uid=sa";
conn = ProviderFactory.CreateConnection(connectionString);
// Create connection specifying provider
// and standard connection string
IDbConnection conn;
string provider = "System.Data.SqlClient";
string connectionString = "server=speedy;database=pubs;uid=sa";
conn = ProviderFactory.CreateConnection(provider,connectionString);
// Create connection using connection string stored
// in app.config under <appSettings>
IDbConnection conn;
string appSetting = "PubsConnStr";
conn = ProviderFactory.CreateConnectionFromConfig(appSetting);
</pre>
<p>C# Sample for Creating a DataAdapter and filling a DataSet.
<pre>
// Create Connection
IDbConnection conn =
ProviderFactory.CreateConnectionFromConfig("PubsConnStr");
// Select command
IDbCommand cmd=conn.CreateCommand();
cmd.Text="select * from author";
// Data Adapter
DataSet ds=new DataSet();
IDbDataAdapter adapter=ProviderFactory.CreateDataAdapter(cmd);
adapter.Fill(ds, "Table1");
</pre>
<p>Creating a DataAdapter and filling a DataSet. <br>
The super lazy method for people like me.
<pre>
// Create Connection
IDbConnection conn =
ProviderFactory.CreateConnectionFromConfig("TdsPubsConnStr");
// Data Adapter
DataSet ds=DataTools.FillDataSet(conn, "select * from author");
</pre>
<p>Here's some sample code on displaying a list <br>
of configured ADO.NET providers:
<pre>
Console.WriteLine("Configured Providers:");
foreach (Provider p in ProviderFactory.Providers)
Console.WriteLine(p.Description);
</pre>
<p>A Super lazy overload to the FillDataSet method (in <br>
DataTools.cs) that will fill a dataset in one line of code.
<pre>
DataSet ds=DataTools.FillDataSet("PubsConnStr", "select * from authors");
</pre>
** About Configuration Files
<p>Information about <a href="http://msdn.microsoft.com/library/en-us/vbcon/html/vboriintroductiontoapplicationsettingstorage.asp?frame=true">app.config</a> files
can be found at <a href="http://msdn.microsoft.com/">MSDN</a>.
<p>See the mcs/class/Mono.Data/app.config for sample configuration file in<br>
mcs source. Basically, if your application is named blah.exe, <br>
you would create an app.config file named blah.exe.config
<p>Here's a sample app.config file showing the provider <br>
declarations along with sample connection strings:
<pre>
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="mono.data">
<section name="providers" type="Mono.Data.ProviderSectionHandler,Mono.Data" />
</sectionGroup>
</configSections>
<appSettings>
<add key="PubsConnStr" value="factory=System.Data.SqlClient;server=speedy;database=pubs;uid=sa;pwd=" />
</appSettings>
<mono.data>
<providers>
<provider name="System.Data.SqlClient" connection="System.Data.SqlClient.SqlConnection" adapter="System.Data.SqlClient.SqlDataAdapter" assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<provider name="System.Data.OleDb" connection="System.Data.OleDb.OleDbConnection" adapter="System.Data.OleDb.OleDbDataAdapter" assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<provider name="System.Data.Odbc" connection="System.Data.Odbc.OdbcConnection" adapter="System.Data.OleDb.OdbcDataAdapter" assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<provider name="Mono.Data.TdsClient" connection="Mono.Data.TdsClient.TdsConnection" adapter="Mono.Data.TdsClient.TdsDataAdapter" assembly="Mono.Data.TdsClient" />
<provider name="Mono.Data.MySql" connection="Mono.Data.MySql.MySqlConnection" adapter="Mono.Data.MySql.MySqlDataAdapter" assembly="Mono.Data.MySql" />
<provider name="Mono.Data.PostgreSqlClient" connection="Mono.Data.PostgreSqlClient.PgSqlConnection" adapter="Mono.Data.PostgreSqlClient.PgSqlDataAdapter" assembly="Mono.Data.PostgreSqlClient" />
<provider name="Mono.Data.SqliteClient" connection="Mono.Data.SqliteClient.SqliteConnection" adapter="Mono.Data.SqliteClient.SqliteDataAdapter" assembly="Mono.Data.SqliteClient" />
<provider name="Mono.Data.SybaseClient" connection="Mono.Data.SybaseClient.SybaseConnection" adapter="Mono.Data.SybaseClient.SybaseDataAdapter" assembly="Mono.Data.SybaseClient" />
</providers>
</mono.data>
</configuration>
</pre>
|
