CarsPortal 1: Connecting To SQL Server Database in ASP.Net

Introduction

CarsPortal is a series of tutorials to implement a cars portal for selling used and new cars. The series will start from the beginning by implementing simple pages then advances to the level of publishing the entire website on the internet. In this tutorial, a simple example for connecting to a SQL Server Database in ASP.NET will be introduced. The tutorial starts by creating a new database and then connecting to that database by implementing a connection class, finally the connection class will be used to display the contents of a selected database table on the website form. The tutorial is implemented by Visual Studio 2012 RC Ultimate edition.

1.1 Creating new project

To create a new project, select "File" -> "New" -> "Project..." from the main menu.

From the dialog box that appears, do the following:

Click Visual C# since C# will be used as the coding language of our project
Select Web, so our project will be a website application
Select ASP.NET Web Forms Application
Write a name for the project, for example “MyWebsite”
Finally click on “Ok” to close the dialog box and open the new project

1.2 Creating New SQL Server Compact Database

Here I will explain how to create a new SQL Server database to be used as the website data storage.

Go to Solution Explorer then right-click on the App_Data folder then select "Add" -> "New Item".

Select Data from the Visual C# tree, then:

Select SQL Server Compact 4.0 Local Database
Change the database name to the preferred name.
Click the “Add” button

Then go to the view menu and select Server Explorer.

Here you can see the SQL Server database that is created.

1.3 Adding Table to the Database

Then go to Server Explorer then expand the database then right-click on Tables then select Add New Table.

Write “Cars” in the table name field
Write “CarID” as a column name for the car number
Choose “numeric” for the value for the ID, it is a number
Choose “No” for the “Allow Nulls” to make the ID field the primary key of the table
Write “CarName” to represent the second property of the name, or represent car name
Choose “nvarchar” for the name since it is composed of letters and characters.

Click “Ok” to accept and save the table.

From the Server Explorer, right-click on the Cars table and select Show Table Data as shown in the following figure.

Fill in the cars table with three or four types of cars as shown in the following figure

1.4 Creating class to connect to the database

Right-click on the project from the Solution Explorer, then select Add -> Class.

Then from the window that will appear select class and write DBConnection in the Name TextBox.

Then write the following code:

using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using System.Web;
namespace CarsWebsite
{
    public class DBConnection
    {
        private SqlCeConnection conn;
        public DBConnection()
        {
            //constructor
        }
        ~DBConnection()
        {
            //destructor
            conn = null;
        }
        public void Disconnect()
        {
            conn.Close();
        }
        public string ConnectToDatabase()
        {
            try
            {
                conn = new SqlCeConnection(@"Data Source=|DataDirectory|\PortalDB.sdf");
                conn.Open();
                return "Connected";
            }
            catch (SqlCeException e)
            {
                conn = null;
                return e.Message;
            }
        }
    }
}

There are the following four main functions:

ConnectToDatabase function to connect to the database
Disconnect function to close the database connection
The class constructor
The class destructor

1.5 Using the connection class “DBConnection” in a WebForm

Add new WebForm: from the Solution Explorer, right-click on the name of the solution then click Add -> Web Form.

When prompted for the form name write “CarsFrm”, then click OK.

Then we need to add a Lable to display the status of the database “connected” or “disconnected”. Ensure that CarsFrm is opened. From the toolbox select Label and drag it to the CarsFrm

From the properties change the label Name to lblStatus.

To connect to the database using the CarsFrm, double-click on the form to open its code editor.

Create an object form the DBConnection class as follows.

public DBConnection DBConn = new DBConnection();

To connect to the database using the CarsFrm, the database is connected in the Form Load event and disconnected in the Form Close event. So, as long as the form is opened it will be connected to the database when it is closed it will be disconnected from the database.

Double-click on the form to open the code editor and use the ConnectToDatabase function in the Form1_Load procedure as follows.

protected void Page_Load(object sender, EventArgs e)
{
     lblStatus.Text= DBConn.ConnectToDatabase();
}

In the Page_Unload event, close the database connection as follows.

protected void Page_UnLoad(object sender,EventArgs e)
{
    DBConn.Disconnect();
}

The overall code of CarsFrm should be as follows.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CarsWebsite
{
    public partial class CarsFrm : System.Web.UI.Page
    {
        public DBConnection DBConn = new DBConnection();
        protected void Page_Load(object sender, EventArgs e)
        {
            lblStatus.Text= DBConn.ConnectToDatabase();
        }
        protected void Page_UnLoad(object sender,EventArgs e)
        {
            DBConn.Disconnect();
        }
    }
}

Ensure that CarsFrm is selected and click the Run button to run the website. With no errors, you should see a blank form with the word “Connected” written on it.

1.6 Displaying data from the Cars table by using the DBConn object

The display of data is done in two steps, the first step is to update the DBConnection class and the add SQL command to get the contents of the Cars table, the second step is to add a GridView on CarsFrm to display the Cars table data using the previous SQL command.

Add Function to DBConnection class to retrieve Cars table data

Open the DBConnection class. From the Solution Explorer double-click on DBConnection.cs to open it. Add the following headers.

using System.Data;
using System.Windows.Forms;

Then add the following function.

public DataTable getAllCars()
{
    try
    {
        string strCommand = "SELECT * FROM Cars";
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter(strCommand, conn);
        da.Fill(dt);
        dt.AcceptChanges();
        return dt;
    }
    catch (SqlCeException e)
    {
        return null;
    }
}

This defines a function for retrieving the contents of the Cars table, we used the SQL command “SELECT * FROM Cars” then the SqlCeDataAdapter will be used to execute that command and fill in a DataTable with the output retrieved by the SQL command. The DataTable is the memory version of the database table.

The overall class code will be as follows.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Web;
namespace CarsWebsite
{
    public class DBConnection
    {
        private SqlCeConnection conn;
        public DBConnection()
        {
            //constructor
        }
        ~DBConnection()
        {
            //destructor
            conn = null;
        }
        public void Disconnect()
        {
            conn.Close();
        }
        public string ConnectToDatabase()
        {
            try
            {
                conn = new SqlCeConnection(@"Data Source=|DataDirectory|\PortalDB.sdf");
                conn.Open();
                return "Connected";
            }
            catch (SqlCeException e)
            {
                conn = null;
                return e.Message;
            }
        }
        public DataTable getAllCars()
        {
            try
            {
                string strCommand = "SELECT * FROM Cars";
                DataTable dt = new DataTable();
                SqlCeDataAdapter da = new SqlCeDataAdapter(strCommand, conn);
                da.Fill(dt);
                dt.AcceptChanges();
                return dt;
            }
            catch (SqlCeException e)
            {
                return null;
            }
        }
    }
}

Use the preceding function to display the contents of the Cars table.

Open CarsFrm in Design view, you can double-click on the form name from the Solution Explorer, make sure the form is in design view. Then from the toolbox select GridView as shown in the figure, drag the GridView to the form.

The GridView will be placed on the form as shown in the following figure.

From the GridView properties change its name to “gvCars”.

Now to display the contents of the Cars table in the grid view, double-click on CarsFrm to open code editor, then change the code of CarsFrm_Load to be as follows.

protected void Page_Load(object sender, EventArgs e)
{
    lblStatus.Text= DBConn.ConnectToDatabase();
    if (lblStatus.Text == "Connected")
    {
        gvCars.DataSource = DBConn.getAllCars();
        gvCars.DataBind();
    }
}

The code test to ensure that the database is connected then it uses DBConn's getAllCars function to get a DataTable object, the object will be passed to the gvCars's DataSource to display the Cars table contents.

Ensure that you are currently opening CarsFrm, then run the program, the following form will be displayed.

We successfully loaded the contents of the Cars table into our website.

Exercise

Add a new table to the database, name it “Customers”, and show its content on the new GridView on the same form used in the tutorial.

Further Reading: CarsPortal 2: Creating Master Page For Website in ASP.Net