This page is likely outdated (last edited on 23 Sep 2006). Visit the new documentation for updated content.
Provider Factory
The assembly Mono.Data.dll has classes for a Provider Factory and Data Tools for Mono ADO.NET. This gives us a foundation for abstract data provider access within Mono.
Sure ADO.NET 2.0 has this functionality, but Mono.Data provides some of this ability for .net 1.0, .net 1.1, .net 2.0, mono 1.0, and mono 1.1. Also, many of the ADO.NET 2.0 features still need to be implemented in Mono and the providers may not have these ADO.NET 2.0 features implemented.
Table of contents |
Here are the details
-
The Provider information is seperate from the connection string information. This allows the list of providers to be stored in the machine.config file.
-
Provider and ProviderCollection objects are available to access the list of providers and modify them at runtime.
-
The ProviderFactory object is used to create new connections, commands, dataadapters, parameters, etc.
Overview of the ProviderFactory object model
-
ProviderFactory: used to create new Connections, Commands, DataAdapters, or Commands. All objects are returned using the provider interfaces such as IDbConnection, IDbCommand, IDbDataAdapter, or IDataParamter
-
DataTools: static methods for doing common tasks like filling a DataSet with the contents of a select statement.
-
ProviderCollection: list of providers configured in the system. Initially loaded from app.config, but can be modified at run-time.
-
Provider: represents a given provider (factory) and holds information needed to create the types.
-
ProviderSectionHandler: works behind the scenes to load the list of providers from the app.config into a ProviderCollection.
C# source code samples for creating a connection
// 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);
C# Sample for Creating a DataAdapter and filling a DataSet.
// 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");
Here’s some sample code on displaying a list
of configured ADO.NET providers:
Console.WriteLine("Configured Providers:");
foreach (Provider p in ProviderFactory.Providers)
Console.WriteLine(p.Description);
A Super lazy overload to the FillDataSet method (in DataTools.cs) that will fill a dataset in one line of code.
DataSet ds=DataTools.FillDataSet("PubsConnStr", "select * from authors");
Example of using CreateCommandBuilder
string providerName = "System.Data.SqlClient";
string connectionString = "server=localhost;database=pubs;uid=sa";
Provider provider = ProviderFactory.Providers[providerName];
IDbConnection conn;
conn = Provider.CreateConnection();
conn.Open();
IDbCommand cmd;
IDbTransaction trans = conn.BeginTransaction();
cmd = new con.CreateCommand();
cmd.CommandText = "SELECT * FROM sometable";
cmd.Transaction = trans;
IDbDataAdapter adapter = new CreateDataAdapter();
adapter.SelectCommand = cmd;
provider.CreateCommandBuilder(adapter);
// CreateCommandBuilder looks for commandbuilder in app.config file
DataSet ds = new DataSet();
adapter.Fill(ds);
DataRow row = ds.Tables[0].NewRow();
row[0] = "test";
row[1] = 123;
ds.Tables[0].Rows.Add(row);
adapter.Update(ds);
conn.Commit();
Recent Additions to Mono.Data
These additions will appear in the next release of Mono (maybe 1.1.14?)
Exceptions are thrown for missing arguments.
Warning message is displayed in the console if there
are no providers defined in your app.config file.
Property ParameterPrefix in Provider: allows you to
figure out what parameter prefix to use when adding parameters.
What is defined for parameterfix in the app.config file will show up
as a character using the property ParameterPrefix.
at will be @ for a prefix for a named parameter
colon will be : for a prefix for a named parameter
questionmark will be ? for a positional parameter
GetCommandBuilder() will allow you to create
a CommandBuilder add set an IDbAdapter to it.
It uses the commandbuilder attribute from the app.config file
About Configuration Files
Information about app.config files can be found at MSDN. See the mcs/class/Mono.Data/app.config for sample configuration file in the mcs source. Basically, if your application is named blah.exe, you would create an app.config file named blah.exe.config Here’s a sample app.config file showing the provider declarations along with sample connection strings:
<?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=localhost;database=pubs;uid=sa;pwd=mypassword" />
<add key="OraConnStr" value="factory=System.Data.OracleClient;data source=testdb;user id=scott;password=tiger" />
<add key="PgConnStr" value="factory=Npgsql;Server=localhost;Database=monotest;User ID=monotest;Password=monotest" />
<add key="MySqlByteFXConnStr" value="factory=ByteFX.Data.MySqlClient;Database=test" />
<add key="FireConnStr" value="factory=FirebirdSql.Data.Firebird;Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost" />
<add key="Sqlite2ConnStr" value="factory=Mono.Data.SqliteClient;URI=file:sqlitetest2.db" />
<add key="Sqlite3ConnStr" value="factory=Mono.Data.SqliteClient;URI=file:sqlitetest3.db,Version=3" />
<add key="SybaseConnStr" value="factory=Mono.Data.SybaseClient;Server=MYSERVER,5000;Database=testdb;User ID=sa;Password=;" />
<add key="MySqlConnStr" value="factory=MySql.Data;server=localhost;user id=user;password=pass;database=mysql;pooling=false" />
</appSettings>
<mono.data>
<providers>
<provider
name="System.Data.SqlClient"
connection="System.Data.SqlClient.SqlConnection"
adapter="System.Data.SqlClient.SqlDataAdapter"
command="System.Data.SqlClient.SqlCommand"
assembly="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="Microsoft SQL Server 7.0/2000/2005"
parameterprefix="at"
commandbuilder="System.Data.SqlClient.SqlCommandBulder" />
<provider
name="System.Data.OleDb"
connection="System.Data.OleDb.OleDbConnection"
adapter="System.Data.OleDb.OleDbDataAdapter"
command="System.Data.OleDb.OleDbCommand"
assembly="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="OLE DB" />
<provider
name="System.Data.Odbc"
connection="System.Data.Odbc.OdbcConnection"
adapter="System.Data.Odbc.OdbcDataAdapter"
command="System.Data.Odbc.OdbcCommand"
assembly="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="ODBC" />
<provider
name="System.Data.OracleClient"
connection="System.Data.OracleClient.OracleConnection"
adapter="System.Data.OracleClient.OracleDataAdapter"
command="System.Data.OracleClient.OracleCommand"
assembly="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="Oracle"
parameterprefix="colon"
commandbuilder="System.Data.OracleClient.OracleCommandBuilder" />
<provider
name="ByteFX.Data.MySqlClient"
connection="ByteFX.Data.MySqlClient.MySqlConnection"
adapter="ByteFX.Data.MySqlClient.MySqlDataAdapter"
command="ByteFX.Data.MySqlClient.MySqlCommand"
assembly="ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756"
description="MySQL (ByteFX)" />
<provider
name="Npgsql"
connection="Npgsql.NpgsqlConnection"
adapter="Npgsql.NpgsqlDataAdapter"
command="Npgsql.NpgsqlCommand"
assembly="Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
description="PostgreSQL" />
<provider
name="Mono.Data.SqliteClient"
connection="Mono.Data.SqliteClient.SqliteConnection"
adapter="Mono.Data.SqliteClient.SqliteDataAdapter"
command="Mono.Data.SqliteClient.SqliteCommand"
assembly="Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756"
description="SQL Lite" />
<provider
name="Mono.Data.SybaseClient"
connection="Mono.Data.SybaseClient.SybaseConnection"
adapter="Mono.Data.SybaseClient.SybaseDataAdapter"
command="Mono.Data.SybaseClient.SybaseCommand"
assembly="Mono.Data.SybaseClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756"
description="Sybase" />
<provider
name="FirebirdSql.Data.Firebird"
connection="FirebirdSql.Data.Firebird.FbConnection"
adapter="FirebirdSql.Data.Firebird.FbDataAdapter"
command="FirebirdSql.Data.Firebird.FbCommand"
assembly="FirebirdSql.Data.Firebird, Version=1.6.3.0, Culture=neutral, PublicKeyToken=e1b4f92304d7b12f"
description="Firebird" />
<provider
name="IBM.Data.DB2"
connection="IBM.Data.DB2.DB2Connection"
adapter="IBM.Data.DB2.DB2DataAdapter"
command="IBM.Data.DB2.DB2Command"
assembly="IBM.Data.DB2, Version=1.0.1728.9997, Culture=neutral, PublicKeyToken=7c307b91aa13d208"
description="IBM DB2 Universal Database" />
<provider
name="MySql.Data"
connection="MySql.Data.MySqlClient.MySqlConnection"
adapter="MySql.Data.MySqlClient.MySqlDataAdapter"
command="MySql.Data.MySqlClient.MySqlCommand"
assembly="MySql.Data, Version=1.0.7.30073, Culture=neutral, PublicKeyToken=8e323390df8d9ed4"
description="MySQL (MySQL AB)" />
</providers>
</mono.data>
</configuration>