ASP.net best practise - where do I connect to database from?

cn = new SqlConnection(
         ConfigurationManager.Connec开发者_如何学PythontionStrings["LocalSqlServer"].ToString());
cn.Open();

Where should this code go assuming I have content pages and master pages?

Should I put it in the master pages Page_Init? Will it then be fully accessible from every stage of execution on my content pages?

I'm used to classic ASP, so I would usually do it:

Declare variables

Open connection

process code

Close connection

Render HTML

But there are lots of stages of the page lifecycle in .net so I am wondering where it is best to place this code?

And does this connection need closing, or will garbage handling take care of it for me?

For me id create a DataAccess Layer so that I can remove the database connectivity in the codebehind files. My webapp will then reference the Dal and expose a public method that will allow Dal interaction from the front end

in terms of opening and closing Dal connections - wrap it in a using statement - that takes care of opening and closing the connection when required.

more information on this can be found here - http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx

I would go too for the Data Access Layer, but in order to answer your question more directly, here is how would I do this in ASP.NET, also handling transactions.

1. Override OnPreInit: initialize connection, open it and start new transaction
2. Override OnUnload: commit/rollback transaction, close connection
3. Override Dispose: dispose of both connection and transaction

I would also add a VoteRollback() method used to request a transaction rollback if something goes wrong. Page execution continues (you'll have to handle problems through your code) but when the page is unloaded the transaction is rolled back

I would recommend you create a seperate class library project that exposes classes that relate to your tables in your database. Then put a reference to that project and away you go. To simplify opening/closing connections, writing classes etc take a look at Subsonic which will map all your tables for you and then you can do something like this in your codebehind.

Product prod = Product.Find(3);
prod.Name = "iPhone";
prod.Save();

In .Net it depends on the structure you use. If you connect explicitly by manually using a SqlConnection object, you'll need to manage its connection directly. If you use a dataset tableadapter object or a DataContext object with LINQ (my personal recommendation), the connection is typically managed for you, but you'll want to enclose your object within a using block to make sure it is collected properly.

Our team's consideration of a "best practice" is to built a universal data manager that implements IDisposable in our common data class that handles the lazy-loading of any connection based objects to retrieve data. This way we can enforce the closing of any connections within the dispose events of that data manager to keep it clean.

Edit:

I always start with Guthrie.
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

101 LINQ Samples

http://krisvandermotten.wordpress.com/2006/11/30/creating-a-data-access-layer-with-linq-to-sql-part-2/

Below is a code sample of a piece of my standard BaseDataManager. I should probably have created an interface for it a long time ago, but the abstract bit seems to get my other team members to adopt it fairly well. I post this code as is with no warranty, but it works for me, and it keeps my connection pool nice and clean (and we do a LOT of data pulls for document information stored in a database). I cut out a bunch of our other base methods to keep it simple, and what's below is the portion that answers your question directly:

[Serializable()]
public abstract class BaseDataManager : IDisposable
{

    private bool _disposedValue = false;
    private SqlConnection _connectionObject = null;

    public BaseDataManager()
    {

    }

    public BaseDataManager(string connectionString)
    {
        this.SqlConnectionString = connectionString;
    }

    public BaseDataManager(string connectionString, string username, string password)
    {
        if (!connectionString.EndsWith(";")) connectionString += ";";
        this.SqlConnectionString += "User ID=" + username + ";password=" + password;
    }

    public string SqlConnectionString
    {
        get;
        set;
    }

    public virtual SqlConnection Connection
    {
        get
        {
            if (_connectionObject == null && !String.IsNullOrEmpty(this.SqlConnectionString))
                _connectionObject = new SqlConnection(this.SqlConnectionString);
            return _connectionObject;
        }
        set
        {
            _connectionObject = value;
        }
    }

    #region IDisposable Support
    /// <summary>
    /// (Protected) Method that performs actual cleanup on dispose. This interface
    /// has been implemented to clean up data connections that are left stranded
    /// when the class is disposed while the connection possibly remains open in
    /// the connection pool. This opportunity is also used to free up the private
    /// variables of the class.
    /// </summary>
    /// <param name="disposing">Used for explicitly calling Dispose</param>
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposedValue)
        {
            if (disposing)
            {
                //---------------------------------------------------------------------------------------------
                // Close the connection object prior to setting it to nothing
                //---------------------------------------------------------------------------------------------
                if (_connectionObject != null) _connectionObject.Close();
                _connectionObject = null;
            }

            _disposedValue = true;
        }
    }

    /// <summary>
    /// (Public) Method that implements IDisposable. This code is autogenerated 
    /// the implementation interface in the VS IDE. Do not change this code.
    /// </summary>
    public void Dispose()
    {
        // Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    #endregion

}

You should only be using it when you need data from your database.

If you are not creating a DAL layer, then you would put this is your events such as page_load, or onClick events. You should not be opening a connection just for the sake of opening a connection on the page.

snippet of code for opening up connection

SqlConnection conn = null
try
{
    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
    conn.Open()

    // do something with the connection
}
catch(Exception ex)
{
    //log error
}
finally
{
    // clean up connection
    if(conn!=null)
    {
        //check if connetion is open, if it is close it / dispose        
    }

}

First thumb of rule, decouple the Domain layer from the ASP.NET specifics/View.

A lot of people create a class library for the Domain layer, that way it is guaranteed that the Page/View related code does not get blended with it.

So you would have a domain layer, where you have classes such as User, Comment, etc... and those classes would talk to the database. I usually have a DbUser class where all the database interaction happens, and DbUser inherits DbQuery which creates the connection for you. That way I can keep database interaction code completely to the Db{ClassName} class and DbQuery. It can really help if you want to keep the code really organized.

Simple scenario: When I get a page request to view a profile of a specific user, I simply do User.GetUser(id); where id is the user id of the user. The User class has a static method GetUser, which creates a new instance of DbUser and calls the getUser method there. getUser method within the DbUser creates a connection and a query to the database, then it returns an instance of User and voila, you get it in your hands.

I hope my answer helps you in the right direction though it may be a bit off topic.

UPDATE: By decoupling the Domain layer from the website then you have more options, such as re-using the class library for another project that interacts to the same database.