How to connect to a database in ASP.NET?

Question

I moved to ASP.NET from PHP where the queries are run directly. So I always create Connection in the Page_Load Event, dispose it after I do all stuff needed, and access data with NpgsqlCommand. (Yes, I use Postgresql in my ASP.NET applications.)

After starting to learn ASP.NET MVC I was amazed how easy it is to access SQL with the LINQ to SQL thing. But... It works only with MS SQL. So my question is how to implement the same functionality in my applications? How to connect to databases easily?

I wrote my own wrapper classes for connecting to Postgresql. 1 class per a table.

This is a part of the Student class:

public class Student : User
{
    private static NpgsqlConnection connection = null;

    private const string TABLE_NAME = "students";

    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Password { get; set; }

    /// <summary>
    /// Updates the student
    /// </summary>
    public void Update()
    {
        Connect();
        Run(String.Format("UPDATE " + TABLE_NAME + " SET first_name='{0}', last_name='{1}', password='{2}' WHERE id={3}", FirstName, LastName, Password, Id));
        connection.Dispose();
    }

    /// <summary>
    /// Inserts a new student
    /// </summary>
    public void Insert()
    {
        Connect();
        Run(String.Format("INSERT INTO " + TABLE_NAME + " (first_name, last_name, password) VALUES ('{0}', '{1}', '{2}')",FirstName, LastName, Password));
        connection.Dispose();
    }

    private static void Run(string queryString)
    {
        NpgsqlCommand cmd = new NpgsqlCommand(queryString, connection);
        cmd.ExecuteScalar();
        cmd.Dispose();
    }

    private static void Connect()
    {
        connection = new NpgsqlConnection(String.Format("Server=localhost;Database=db;Uid=uid;Password=pass;pooling=false"));
        connection.Open();
    }

    //....

So as you see the problem here is that with every INSERT, DELETE, UPDATE request I'm using Connect() method which connects to the database. I didn't realize how stupid it was before I had to wait for 10 minutes to have 500 rows inserted, as there were 500 connections to the database.

Using pooling while connecting does help, but still making the connection and making the server check the pool during every single query is stupid.

So I decided to move Connection property to a static DB class, and it didn't work either, because it's a really bad idea to store such objects as connections in a static class.

I really don't know what to do know. Yes, there's an option of manullay creating the connections in every Page_Load event and close them in the end like I'm doing it right now.

Student student = new Student { FirstName="Bob", LastName="Black" };
NpgsqlConnection connection = ... ;
student.Insert(connection);

But this code is pretty ugly. I will be really thankful to somebody who can hep me here.

Answer 1

I would not recommend this design. It is better to encapsulate each database call which means each call opens a new connection each time you need to do something on the db. This might sound inefficient if it were not for connection pooling. ASP.NET will reuse connections automatically for you in a pool. The problem in your design is that there is nothing that guarantees that the connection will be closed.

Thus, you should try something like

private static void Insert()
{
    var sql = "Insert Into "....;
    ExecuteActionQuery(sql);
}

private static void ExecuteActionQuery( string query )
{
    using (var conn = new NpgsqlConnection(String.Format(connString))
    {
        conn.Open();
        using ( var cmd = new NpgsqlCommand(query, connection) )
        {
            cmd.ExecuteNonQuery();
        }
    }
}

I typically make a few global functions that encapsulate the standard operations so that I need only pass a query and parameters and my method does the rest. In my example, my ExecuteActionQuery does not take parameters but this was for demonstration only.

Answer 2

Not really pertaining to your question but another solution, if you like linq to sql, you could try DBLinq which provides a Linq to SQL provider for Postgresql and others databases.

http://code.google.com/p/dblinq2007/