Ways to instantiate PetaPoco

There are various way to construct a new instance PetaPoco with each trying to solve a particular use case.

The API, as it stands, is currently comprised of:

public Database()
public Database(IDbConnection connection)
public Database(string connectionString, string providerName = null)
public Database(string connectionString, DbProviderFactory factory)
public Database(string connectionStringName)
public Database(string connectionString, IProvider provider)

Useful information

Version 5.1.84 or greater does not require a DbProviderFactory registration for any particular SQL client for Database use. However, there is a minor got-cha here, which is the provider name still needs to be set. A list of valid provider names given below.

Note: Users of the T4 templates will still require a DbProviderFactory registration, a valid .net provider name, and the additional GAC registration of their selected SQL client. For more detailed instructions for the T4 template, please read the top of the T4 include file.

Valid provider names

Valid provider names are:

Provider	Accepted provider names
MySqlDatabaseProvider	MySql,MySql.Data.MySqlClient
PostgreSQLDatabaseProvider	Npgsql, pgsql
SQLiteDatabaseProvider	SQLite,System.Data.SQLite
SqlServerCEDatabaseProviders	SqlServerCe, SqlCeConnection, System.Data.SqlServerCe.4.0
OracleDatabaseProvider	Oracle, Oracle.ManagedDataAccess.Client
SqlServerDatabaseProvider (Default)	SqlServer, System.Data.SqlClient

Note: the casing of the provider name does not matter, as comparisons are invariant culture ignore case.

Each constructor option explained

public Database()

The parameterless constructor is a quick and easy way to create a new instance of PetaPoco. This constructor will use the first connection string it finds in the app/web configuration.

Sample postgre app/web configuration file.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.data>
  <connectionStrings>
    <clear />
    <add name="postgres" connectionString="Host=127.0.0.1;Username=petapoco;Password=petapoco;Database=petapoco;Port=5001;" providerName="Npgsql"/>
  </connectionStrings>
</configuration>

public Database(IDbConnection connection)

The constructor which takes an instance of an existing connection is one of PetaPoco's more interesting and less known features. The why lies with the use case. It's not hard to imagine a situation where a project uses its own implementation of a DAL or a product like EF and has a need to easily access/manipulate data. In such a case, one can simply acquire a IDbConnection and construct an instance of PetaPoco for easy data access and manipulation.

Note: Please be aware that PetaPoco does not take ownership of the connection management when using this constructor, that responsibility to left to the caller.

public Database(string connectionString, string providerName = null)

With this constructor one simply provides a connection string and optional provider name. If no provider name is given, the default SQL Server provider is used. A table of valid provider names are provider here.

public Database(string connectionStringName)

Hopefully this one is fairly obvious. If you guessed it maps the given connection string name to a connection string in the app/web config with the same name, then congrats for you.

Note: Ensure you give the connection string entry a valid provider name. A table of valid provider names are provider here.

Sample MySQL app/web configuration file.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="mysql" connectionString="Server=127.0.0.1;Uid=petapoco;Pwd=petapoco;Database=petapoco;Port=5002" providerName="MySql" />        
    </connectionStrings>
</configuration>

Sample instantiation of PetaPoco using the aforementioned MySQL configuration.

var db = new Database("mysql");

public Database(string connectionString, IProvider provider)