ASP.NET/Database

ASP.NET Database

ASP.NET v1.1.4322

Introduction

A database is essentially a place to store data. However the key to understanding the term "database" is that a database is an electronic warehouse for storing data. Databases can contain any type of data (depending on the manufacturer/vendor). For example the Wikibooks website is maintained within a database.

There are many types of database. Hierarchy database, Network database, Relational database, Object database, XML database. Even a file system or Excel can be considered as a database.

The most common structure for storing the data is as a table. Each row in the table is a record and each column is a field. The table may relate to other tables by sharing a common field. This is known as a relational database.

Relational database is the dominant database in the database market today. There are proprietary ones like Oracle, IBM DB2, Microsoft SQL Server, Sybase, ANTs, and open source ones like PostgreSQL, MySQL, Derby, Berkeley DB (Sleepcat) etc.

There are typically limitations on length and width of database tables. SQL Server 2005 has an 8K row width limit for example, and many lengths are limited by 32-bit addressing, to at most approximately 4E9 records. This increases to approximately 1.8E19 records with 64-bit addressing.

The Microsoft .NET framework comes with several namespaces to manage data in databases. .NET also comes with SQL Server and Oracle native providers. A native provider will only work with one database product, and uses the native database more efficiently. There are several generic database classes, however if you are using a database that has a native provider, use it.

Performing database operations is fairly easy in ASP.NET. Below is a quick sample of how to connect to a database.

Connecting to a Database

VB.NET

Class Page1
    Public Sub Page_Load (byval sender as System.Object, byval eventars as System.EventArgs)
        Dim sqlconn as new SQLConnection("database connection string")
        sqlconn.Open()
    End Sub

End Class

C#.NET

public class Page1

{
    public void Page_Load(object sender, EventArgs e)
    {
        string strConn = "''database connection string''"; 
        // the italicized ''database connection string'' above should   
        // be replaced by some real valid one.
        SqlConnection sqlConn = new SqlConnection(strConn);
        sqlConn.Open();
    }
}

Comments on the C# example:

• Since the Page_Load event handler (i.e. the "void" method which handles this event) is declared inside a class declaration (named "Page1"), it means usually that in such case it is in a separate "code behind" file, and if the ASP.NET file of the page is named "Page1.aspx" it's code behind file is usually named "Page1.aspx.cs". Should it be declared in the same file as the source code of the page (i.e., inside <script runat="server"> ... </script> tags) there would be no need to make a page class declaration.
• In the case you are using the code behind coding model it is necessary to include the appropriate attributes in the @Page directive (look in the SDK documentation for details).
• In the code behind file the class is to be declared inside a "namespace" declaration (named usually after the name of the application - namespace DataAccess{ ... }, for instance).
• Above the namespace declaration it is advisable to add using declarations for the integrated namespaces of the .NET framework, so you will be able to refer to the .NET classes with short names, not System.Data.SqlClient.SqlConnection, for instance. In the case of single file code model, the appropriate @Import directives should be added.
• The Page1 class should usually be inherited explicitly from System.Web.UI.Page. Thus our Page1 source code file might look like:

using System;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess

{
    public class Page1 : System.Web.UI.Page
    {
        public void Page_Load(object sender, EventArgs e)
        {
            ...
        }
    }
}

• The access modifier of the Page_Load event handler in the present example is "public". Usually it is customary to use a "protected", or even a "private" access modifier (for encapsulation reasons). In the present example though, the programmer might wish to access the sqlConn variable outside the Page1 class, so "public" it is...

See also: SQLConnection parameters

ADO.NET

class DBConnection
{
  public void Page_Load (System.Object sender, System.EventArgs eventars)
  {
    try
     {
        SqlConnection sqlconnection = new SqlConnection("database connection string");
        sqlconnection.Open();
      }
       catch(Exception ex)
      {
        MessageBox.show(ex.message);
       }
  }
}

ADO.NET Connection String Example (Trusted Connection)

server=DBSERVER;database=DBName;Trusted_Connection=True;

Connection Pooling

In classic ASP, database connections were not managed very effectively. However with the advent of ASP.NET, database connection pooling is fairly translucent. If the same connection string is provided, connection pooling is handled automatically.