ASP. NET MVC uses Petapoco Mini ORM framework +npgsql to drive connected PostgreSQL database

Original: ASP. NET MVC uses Petapoco Mini ORM framework +npgsql to drive connected PostgreSQL database

Some time ago in the garden saw the small butterfly Jinghong released about the green version of the linux.net--"Jws.mono". Because I'm right. NET program is very interested in Linux, I also read some information about mono, but there is no time to take time to study this, the Small Butterfly Jinghong blog and aroused my interest, I spent four days, and finally ran on the liunx belong to my own application, Where the database is used to the PostgreSQL database. For the database selection, is in the small Butterfly Jinghong blog linux.net Study notes (4), the use of this database.

Today, I'm simply explaining the connection to the PostgreSQL database using the ASP. NET MVC + Micro ORM Framework Petapoco. C # Operations PostgreSQL database A lot of people should know, just need to use Npgsql driver. For the use of npgsql you can refer to the Zhang Shanyu Teacher's blog PostgreSQL. NET driver Npgsql. About the introduction and use of Petapoco, you can refer to the reader: Petapoco official website,. NET Object Relational Mapper Petapoco, OoC's Blog, Petapoco (ii), Petapoco Introduction (a), small and convenient ORM class library--petapoco (This is the information I have been looking for a long time on the internet AH), They all have a clearer and detailed description of how Petapoco is used.

Because it is the first time to use the PostgreSQL database, I have encountered a lot of problems in the process of using, some problems, I only have a problem to everyone to post, and then give you a detailed explanation of my code.

The problem is simple, but I have not found the Npgsql driver, but I have loaded the driver into the solution, why this problem, I found a lot of information on Google, including Petapoco source code and unit testing, have not found a solution. The solution was later found on a foreign exchange site because the MVC application needed to manually configure the drivers in the Webconfig file, so I added the following configuration to the configuration file:

 
<system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
           invariant="Npgsql"
           support="FF"
           description=".Net Framework Data Provider for Postgresql Server"
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

So the problem is easy to solve. See: (Example Address: http://www.cnicode.com/)

Now let's look at the code implementation:

1. Before looking at the code, we need to load npgsql and Petapoco into the current project, and I will use NuGet to add to the current project as follows:

Install-package Npgsql

Install-package Petapoco

2. Take a look at the important code in Web. config

1> Database Connection string

 
<connectionStrings>
    <add name ="Postgresql" connectionString="Server=127.0.0.1;User id=postgres;password=123;Database=mono_test;" providerName="Npgsql"/>
  </connectionStrings>

2>npgsql Drive configuration file

 
<!-provider-driven configuration file->
   <system.data>
     <DbProviderFactories>
       <add name = "Npgsql Data Provider"
            invariant = "Npgsql"
            support = "FF"
            description = ". Net Framework Data Provider for Postgresql Server"
            type = "Npgsql.NpgsqlFactory, Npgsql" />
     </ DbProviderFactories>
   </system.data>

3. Take a look at the overall project structure

4.userinfo.cs code in an entity class

 
namespace PetaPoco
{
    [TableName("userinfo")]
    [PrimaryKey("id")]
    [ExplicitColumns] public class UserInfo
    {
        [Column("id")] public int Id { get; set; }

        [Column("name")] public string Name { get; set; }

        [Column("age")] public int Age { get; set; }

        [Column("qq")] public int Qq { get; set; }
    }
}

The code in 5.Controllers

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PetaPoco;

namespace PostgreSqlDemo.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/
        Database db = new PetaPoco.Database("Postgresql");
        public ActionResult Index()
        {
            ViewData.Model = db.Query<UserInfo>("select * from userinfo");
            return View();
        }

        //
        // GET: /Home/Details/5

        public ActionResult Details(int id)
        {
            ViewData.Model = db.SingleOrDefault<UserInfo>("select * from userinfo where [email protected]", id);
            return View();
        }

        //
        // GET: /Home/Create

        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /Home/Create

        [HttpPost]
        public ActionResult Create(UserInfo user)
        {
            try
            {
                db.Insert(user);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Home/Edit/5

        public ActionResult Edit(int id)
        {
            ViewData.Model = db.SingleOrDefault<UserInfo>("where [email protected]", id);
            return View();
        }

        //
        // POST: /Home/Edit/5

        [HttpPost]
        public ActionResult Edit(UserInfo user)
        {
            try
            {
                db.Update(user);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Home/Delete/5

        public ActionResult Delete(int id)
        {
            ViewData.Model = db.SingleOrDefault<UserInfo>("where [email protected]",id);
            return View();
        }

        //
        // POST: /Home/Delete/5

        [HttpPost]
        public ActionResult Delete(UserInfo user)
        {
            try
            {
                db.Delete(user);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

The code in 6.view will be written using ASP. No code is posted here.

ASP. NET MVC uses the Petapoco mini ORM Framework +npgsql drive to connect the PostgreSQL database is basically over, I will record a video tutorial corresponding to this post, the source code and video tutorial will be released later in blog post.

Finally thanked Zhang Shanyu teacher and small butterfly Jinghong's blog, especially thanks to the small butterfly jinghong on QQ to my help.

Shing May 15, 2014 16:47:44

Blog Address: http://www.cnblogs.com/haoxilu/

ASP. NET MVC uses Petapoco Mini ORM framework +npgsql to drive connected PostgreSQL database