Data Access Layer – C#


This data access layer is used in turn by other program modules to access and manipulate the data within the data store without having to deal with the complexities inherent in this access.

For example, instead of using commands such as insert, delete, and update to access a specific table in a database, a class and a few stored procedures could be created in the database. The procedures would be called from a method inside the class, which would return an object containing the requested values. Or, the insert, delete and update commands could be executed within simple functions like registeruser or loginuser stored within the data access layer.

Web.config:


<connectionStrings>

    <add

        name="ConnectionString"
        connectionString="Data Source=server;InitialCatalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=sa"
        providerName="System.Data.SqlClient" />

</connectionStrings>

Step 1 :  Create WebConfiguration.cs under App_Code with the following code:


using System;
using System.Configuration;

/// <summary>
/// Repository for configuration settings
/// </summary>
public static class WebConfiguration

{

    // Caches the connection string
    private readonly static string dbConnectionString;

    // Caches the data provider name
    private readonly static string dbProviderName;

    // Initialize various properties in the constructor
    static WebConfiguration()
    {
        dbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        dbProviderName = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
    }

    // Returns the connection string for the database
    public static string DbConnectionString
    {
        get
        {
            return dbConnectionString;
        }
    }

    // Returns the data provider name
    public static string DbProviderName
    {
        get
        {
            return dbProviderName;
        }
    }
}

Step 2 : Create Class GenericDataAccess.cs with following code:


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

/// <summary>
/// Class contains generic data access functionality to be accessed from
/// the business tier
/// </summary>
public class GenericDataAccess
{

    // static constructor
    static GenericDataAccess()
    {
    }

    // execute a command and return the results as a DataTable object
    public DataTable ExecuteSelectCommand(DbCommand command)
    {
        // The DataTable to be returned
        DataTable table;

        // Execute the command making sure the connection gets closed in the end
        try
        {

            // Open the data connection
            command.Connection.Open();

            // Execute the command and save the results in a DataTable
            DbDataReader reader = command.ExecuteReader();

            table = new DataTable();
            table.Load(reader);

            // Close the reader
            reader.Close();

        }

        catch (Exception ex)
        {

            //Utilities.LogError(ex);
            throw ex;

        }
        finally
        {

            // Close the connection
            command.Connection.Close();

        }
        return table;

    }

    // execute an update, delete, or insert command
    // and return the number of affected rows
    public 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 (Exception ex)
        {

            // 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;

    }

    // execute a select command and return a single result as a string
    public string ExecuteScalar(DbCommand command)
    {

        // The value to be returned
        string value = "";

        // 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
            value = command.ExecuteScalar().ToString();

        }
        catch (Exception ex)
        {

            //Utilities.LogError(ex);
            //throw ex;
        }
        finally
        {

            // Close the connection
            command.Connection.Close();

        }
        // return the result
        return value;

    }

    // creates and prepares a new DbCommand object on a new connection

    public DbCommand CreateCommand()

    {

        // Obtain the database provider name
        string dataProviderName = WebConfiguration.DbProviderName;

        // Obtain the database connection string
        string connectionString = WebConfiguration.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;

    }

    //creates and prepares a new DbCommand object on a new connection(For QueryText)
    public DbCommand CreateCommand4QueryText()

    {

        // Obtain the database provider name
        string dataProviderName = WebConfiguration.DbProviderName;

        // Obtain the database connection string
        string connectionString = WebConfiguration.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.Text;

        // Return the initialized command object
        return comm;

    }

}

Step 3: Create BAL Folder under App_Code and start creating your BAL Classes & Use it on Presentation layer


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

public class BAL_AddAdImage

{

    public BAL_AddAdImage()

    {

    }

    public static DataTable getPropertyImageAll(int PropertyID)
    {

        // get a configured DbCommand object
        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name
        comm.CommandText = "PropertyDB_GetPropertyImageDetails_All";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@PropertyID";
        param.Value = PropertyID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // return the result table
        DataTable table = gda.ExecuteSelectCommand(comm);

        return table;

    }

    public static string GetImagePath(int ImageID)
    {

        // get a configured DbCommand object
        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name
        comm.CommandText = "PropertyDB_GetPropertyImagePath";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ImageID";
        param.Value = ImageID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        string path = gda.ExecuteScalar(comm).ToString();

        return path;

    }

    public static bool Delete(int ImageID)
    {

        // get a configured DbCommand object
        GenericDataAccess gda = new GenericDataAccess();

        DbCommand comm = gda.CreateCommand();

        // set the stored procedure name
        comm.CommandText = "PropertyDB_PropertyImageDetails_Delete";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ImageID";
        param.Value = ImageID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        int retval = gda.ExecuteNonQuery(comm);

        if (retval > 0)
            return true;
        else
            return false;

    }

}

Advertisements

3 thoughts on “Data Access Layer – C#

  1. Pingback: toko cctv
  2. Pingback: itunes
  3. Fantastic blog! Do you have any helpful hints for aspiring writers?
    I’m hoping to start my own website soon but I’m a little lost on
    everything. Would you propose starting with a free platform like WordPress or
    go for a paid option? There are so many choices
    out there that I’m totally overwhelmed .. Any suggestions? Many thanks!

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