How do I integrate a database into asp.net project using Postgresql and Petapoco?

Question

I am not very good at setting up a project at all. I am working on an application in VS that uses ASP.NET with MVC framework. I am wanting to use a Postgresql database and use it in my project with Petapoco. I have tried everything, and I added a connection string, but I can't get this to work..

Answer 1

This is done with Petapocoo 5.1.228

If you added it with nuget, you have a section within Project->Models->Generated. in this section you'll have .tt and .ttinclude files

One is called Database.tt, once run will create objects with basic decoration needed, or what you have setuped. In this one, you have near the top a section called Settings with ConnectionStringName parameter. This one has a comment that says "// Uses last connection string in config if not specified". This means if you don't set up a ConnectionStringName in web.config file, it will take the lase one. So to configure it,

ConnectionStringName = "NorthwindConnection";  //this is my example using Northwind

then in web.config :

<add name="NorthwindConnection" connectionString="data source=.;Initial 
Catalog=Northwind;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

Then if you have any trouble with DataProviders... go and read the doc in PetaPoco.Core.ttinclude this include how to add and check for DataProvider in GAC. And if you have the right set of driver installed.

Another word about Database.tt, there is settings you can use to tweak the generated POCOs in what namespaces, do I want the full database or exclude what... check it out! :P

Back in business, once you setted up database.tt right, right click and Run custom tool will do the job. On success, you'll have a Database.cs under the same path as database.tt. on error you have the same result with an exception to look up to.

The generated file will contains all POCOs and namesapaces you need to start using Petapoco....

in my case,

using NorthwindConnection;

in addithion with

public IEnumerable<Customer> GetCustomers()
{
    var db = new PetaPoco.Database("NorthwindConnection");
    return db.Query<Customer>("SELECT * FROM Customers");
} 

allows me to retrieve all customers.

EDIT: Data provider or connection trouble with driver: check PetaPoco.Core.ttinclude :

In order to work, the appropriate driver must be registered in the system machine.config file.  If you're
 using Visual Studio 2010 the file you want is here:

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

 After making changes to machine.config you will also need to restart Visual Studio.

 Here's a typical set of entries that might help if you're stuck:

    <system.data>
        <DbProviderFactories>
            <add name="Odbc Data Provider" invariant="System.Data.Odbc" 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" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
            <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
            <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
            <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
            <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
            <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.91, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
        </DbProviderFactories>
    </system.data>

 Also, the providers and their dependencies need to be installed to GAC.  

 Eg; this is how I installed the drivers for PostgreSQL

     gacutil /i Npgsql.dll
     gacutil /i Mono.Security.dll

Hope this helps solving your issue.