ASP.NET: SQL Server 2008 Database Connection from ASP.NET

1. Web.config file– put the database connection
<connectionStrings>
<add name=BalloonShopConnectionconnectionString=Server=(local)\SqlExpress;
         Integrated Security=True;
         Database=BalloonShop
         providerName=System.Data.SqlClient/>
</connectionStrings>
//put the key in one line.
2. ApplicationConfiguration.csDataLayer– read the database connection from web.config and make a database connnection Property.

using System;
using System.Configuration;
//remember here class is static and constructor is static. no access modifier can be used for constructor

public static class BalloonShopConfiguration
{
    // Caches the connection string
    private static string dbConnectionString;
    // Caches the data provider name
    private static string dbProviderName;
   
    static BalloonShopConfiguration()
    {
        dbConnectionString =
        ConfigurationManager.ConnectionStrings[“BalloonShopConnection”].
        ConnectionString;
        dbProviderName =
        ConfigurationManager.ConnectionStrings[“BalloonShopConnection”].
        ProviderName;
      
    }
    // Returns the connection string for the BalloonShop database
    public static string DbConnectionString
    {
        get
        {
            return dbConnectionString;
        }
    }
    // Returns the data provider name
    public static string DbProviderName
    {
        get
        {
            return dbProviderName;
        }
    }
  
}
3. GenericDataAccess.csDataLayer– make the connection with database using the Property created in ApplicationConfiguration.cs. You can also create other database related operation for example: Insert, Update, Delete

using System.Data;
using System.Data.Common;

public static class GenericDataAccess
{
    // static constructor
    static GenericDataAccess()
    {
    }
  
    // creates and prepares a new DbCommand object on a new  connection
   
    public static DbCommand CreateCommand()
    {
        // Obtain the database provider name
        string dataProviderName = BalloonShopConfiguration.DbProviderName;
        // Obtain the database connection string
        string connectionString = BalloonShopConfiguration.DbConnectionString;
        // Create a new data provider factory
        DbProviderFactory factory = DbProviderFactories.
        GetFactory(dataProviderName);
        // Obtain a database specific connection object
        DbConnection conn = factory.CreateConnection();
        // Set the connection string
        conn.ConnectionString = connectionString;
        // Create a database specific command object
        DbCommand comm = conn.CreateCommand();
        // Set the command type to stored procedure
        comm.CommandType = CommandType.StoredProcedure;
        // Return the initialized command object
        return comm;
    }

    //execute a command and returns the results as DataTable
    public staticDataTable ExecuteSelectCommand(DbCommand command)
    {
        DataTable table;
        try
        {
            command.Connection.Open();//open the data connection
            DbDataReader reader = command.ExecuteReader();
            //execute command and save into reader
            table = new DataTable();
            table.Load(reader);
            reader.Close();//close the reader
        }
        catch (Exceptionex)
        {
            throw ex;
        }
        finally
        {
            command.Connection.Close();//close the connection
        }
        return table;
    }
    // execute an update, delete, or insert command
    // and return the number of affected rows
    public static int ExecuteNonQuery(DbCommand command)
    {
        // The number of affected rows
        int affectedRows = -1;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the connection of the command
            command.Connection.Open();
            // Execute the command and get the number of affected rows
            affectedRows = command.ExecuteNonQuery();
        }
        catch (Exceptionex)
        {
            // Log eventual errors and rethrow them
            //Utilities.LogError(ex);
            throw ex;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        // return the number of affected rows
        return affectedRows;
    }

 

  
}
4. CatalogAccess.csBussinessLogic-Acess the related database function from GenericDataAccess.cs
public static class CatalogAccess
{
    public struct DepartmentDetails
    {
        public string Name;
        public string Description;
    }
      //constructor
static CatalogAccess()
    {
    }
// Retrieve the list of departments
    public static DataTable GetDepartments()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = “GetDepartments”;
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }
public static bool UpdateDepartment(string id, string name, string description)
    {
        //get a configured dbcommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        //set the stored procedure name
        comm.CommandText = “UpdateDepartment”;
        //create new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = “@DepartmentId”;
        param.Value = id;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        //create new parameter
        param = comm.CreateParameter();
        param.ParameterName = “@DepartmentName”;
        param.Value = name;
        param.DbType = DbType.String;
        param.Size = 50;
        comm.Parameters.Add(param);
        //create new parameter
        param = comm.CreateParameter();
        param.ParameterName = “@DepartmentDescription”;
        param.Value = description;
        param.DbType = DbType.String;
        param.Size = 1000;
        comm.Parameters.Add(param);
        //result will represent the number of changed rows
        int result = -1;
        try
        {
            //execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery( comm);
        }
        catch
        {
            //nothing
        }
       //result will be 1 in case of success
        return (result != -1);
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s