DataReader vs DataSet

This article contain difference between Dataset and DataReader and also Detailed explaination of Functionality & Features provided by DataReader/DataSet.

I have found lots of article who talk about Dataset and DataReader but the article written by John Papa in MSDN Magazine contains very useful explaination on this topic. So I have used that information to write this article & tried to give you the best.

Here Points to ponder contain the difference between Dataset and DataReader.

Points to ponder:

• DataSet Object works under Disconnected mode, While DataReader Object has Connected mode.
• DataSet Object has Read/Write access, While DataReader Object has Read-only access.
• DataSet Object Supports multiple tables from different databases, While DataReader Object Supports a single table based on a single SQL query of one database.
• DataSet Object has Greater overhead to enable additional features, While DataReader Object being Lightweight object with very little overhead.
• DataSet Object is Bind to multiple controls, While DataReader Object is Bind to a single control.
• DataSet Object supports Forward and backward scanning of data, While DataReader Object supports Forward-only scanning of data.
• DataSet Object has Slower access to data, While DataReader Object has Faster access to data.
• DataSet Object is Supported by Visual Studio .NET tools, While DataReader Object Must be manually coded.

Connected Mode:

A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time.

As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.

Unlike the DataSet, the DataReader is not implemented directly in the System.Data namespace.

Rather, the DataReader is implemented through a specific managed provider’s namespace such as System.Data.SqlClient.SqlDataReader.

Because all DataReaders, including the OleDbDataReader, the SqlDataReader, and other managed provider’s DataReaders implement the same IDataReader interface, they should all provide the same base set of functionality.

Each DataReader is optimized for a specific data provider. If the database you are developing against has a managed provider for ADO.NET, then you should take advantage of it. Otherwise, you can use the System.Data.OleDb or the System.Data.Odbc namespaces, which expose more generic managed providers that can access a variety of data sources.

If you are developing against SQL Server™ or Oracle, it would be more efficient to use the provider that was made specifically for these databases. In this column, I will query the SQL Server Northwind database using the System.Data.SqlClient namespace.

The SqlDataReader can only retrieve one row at a time from the data source and in order for it to get the next record, it has to maintain its connection to the data source.

The DataSet, however, doesn’t need to know about where it gets its data.

The DataReader can only get its data from a data source through a managed provider.

The DataSet can also get its data from a data source via a managed provider, but the data source can also be loaded manually, even from an XML file on a hard drive.

In ASP.NET, DataReader objects can be used for more robust situations such as binding themselves to an ASP.NET DataGrid or a DropDownList server control.

The following code demonstrates how to retrieve a list of products from the Northwind database using a SqlDataReader object:

   1: string sSQL = "SELECT * FROM Products";

   2: string sConnString =

   3:     "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4: using (SqlConnection oCn = new SqlConnection(sConnString))

   5: {

   6:     SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   7:     oSelCmd.CommandType = CommandType.Text;

   8:     oCn.Open();

   9:     SqlDataReader oDr = oSelCmd.ExecuteReader();

  10:     DataGrid1.DataSource = oDr;

  11:     DataGrid1.DataBind();

  12: }

Now Read the Data using

   1: SqlDataReader oDr = oCmd.ExecuteReader();

   2: while(oDr.Read()) {

   3:     Console.WriteLine(oDr[0]);

   4: }

Supporting Multiple Resultsets

The DataReader supports access to multiple resultsets, one at a time, in the order they are retrieved. This code is easily modified to handle multiple resultsets. The following code retrieves a SqlDataReader and loops through its rows, again writing the first column’s value for each row to the console:

   1: SqlDataReader oDr = oCmd.ExecuteReader();

   2:     do {

   3:         while(oDr.Read())

   4:         {

   5:             Console.WriteLine(oDr[0]);

   6:         }

   7:         Console.WriteLine(oDr[0]);

   8:     }

   9:     while(oDr.NextResult());

Once all of the rows from the first resultset are traversed, the rowset from the second query is retrieved and its rows are traversed and written. This process can continue for several resultsets using a single SqlDataReader.

The Read method of the SqlDataReader loads the next record so that it can be accessed and moves the position of the cursor ahead. It returns a Boolean value indicating the existence of more records. This feature can help circumvent a common problem in classic ADO development: the endless loop.

The DataReader in the previous code sample shows how to get the value for a column from the DataReader using its ordinal index position.

Disconnected Mode:

The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it.

Instead, to fill a DataSet from a database you first create a DataAdapter object for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.

You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects.

One of its purposes is to serve as the route for a rowset to get from the database to the DataSet.

For example, when the SqlDataAdapter’s Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet.

Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.

The following code shows how a DataSet can be filled from the Products table of the Northwind database:

   1: string sSQL = "SELECT * FROM Products";

   2: string sConnString = 

   3:     "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4: SqlDataAdapter oDa = new SqlDataAdapter();

   5: DataSet oDs = new DataSet();

   6: using(SqlConnection oCn = new SqlConnection(sConnString))

   7: {

   8:     SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   9:     oSelCmd.CommandType = CommandType.Text;

  10:     oDa.SelectCommand = oSelCmd;

  11:     oDa.Fill(oDs, "Products");

  12: }

The DataSet can read and load itself from an XML document as well as export its rowset to an XML document. Because the DataSet can be represented in XML, it can be easily transported across processes, a network, or even the Internet via HTTP.

Unlike the DataReader, the DataSet is not read-only.

A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider’s objects.

Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only.

In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched.

The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.

Not only can the DataSet be loaded from XML, it can also be loaded manually.

   1: //-- Create the table

   2: DataTable oDt = new DataTable("Employees");

   3: DataRow oRow;

   4: oDt.Columns.Add("EmployeeID", System.Type.GetType("System.Int32"));

   5: oDt.Columns.Add("FirstName", System.Type.GetType("System.String"));

   6: oDt.Columns.Add("LastName", System.Type.GetType("System.String"));

   7: oDt.Constraints.Add("PK_Employees", oDt.Columns["EmployeeID"], true);

   8: oDt.Columns["EmployeeID"].AutoIncrement = true;

   9: oDt.Columns["EmployeeID"].AutoIncrementSeed = -1000;

  10: oDt.Columns["EmployeeID"].AutoIncrementStep = -1;

  11: oDs.Tables.Add(oDt);

  12:  

  13: //-- Add the rows

  14: oRow = oDs.Tables["Employees"].NewRow();

  15: oRow["FirstName"] = "Haley";

  16: oRow["LastName"] = "Smith";

  17: oDs.Tables["Employees"].Rows.Add(oRow);

  18: oRow = oDs.Tables["Employees"].NewRow();

  19: oRow["FirstName"] = "Madelyn";

  20: oRow["LastName"] = "Jones";

  21: oDs.Tables["Employees"].Rows.Add(oRow);

  22:  

  23: //-- Bind it to a DataGrid

  24: grdEmployees.DataSource = oDs.Tables["Employees"];

Because the DataSet is disconnected, its use can reduce the demand on database servers. It does, however, increase the memory footprint in the tier where it is stored, so be sure to account for that when designing around a DataSet as your data store.

Scaling up on the middle tier using parallel servers and load balancing is a common way to handle the increased load so that session-based information can be stored in objects such as the DataSet.

When to Use the DataSet

The DataSet’s disconnected nature allows it to be transformed into XML and sent over the wire via HTTP if appropriate. This makes it ideal as the return vehicle from business-tier objects and Web services.

DataSet objects are a good choice when the data must be edited or rows added or deleted from the database.

Since the DataSet can be traversed in any direction, all of these features are available. This flexibility also makes the DataSet an easy choice when the situation calls for multiple iterations of the rowset.

If a rowset is intended to be bound to more than one read-only ASP.NET server control, you should consider using a DataSet instead.

The DataSet also works well when a rowset must be persisted between page calls to the Session or Cache objects.

When to Use the DataReader

Binding a DataReader to a DropDownList or even a read-only DataGrid in ASP.NET works well as the data can be retrieved and displayed in the list but does not need to be persisted for editing.

When populating a list or retrieving 10,000 records for a business rule.

When a huge amount of data must be retrieved to a business process, even on a middle tier, it can take a while to load a DataSet, pass the data to it on the business tier from the database, and then store it in memory. The footprint could be quite large and with numerous instances of it running (such as in a Web application where hundreds of users may be connected), scalability would become a problem. If this data is intended to be retrieved and then traversed for business rule processing, the DataReader could speed up the process as it retrieves one row at a time and does not require the memory resources that the DataSet requires.

When output or return parameters need to be retrieved, a DataReader will not allow you to get them until the DataReader and its connection are closed. If data must be bound to a read-only list in a Web Form, a DataReader is a very good choice.

 

Reference:

http://msdn.microsoft.com/en-us/magazine/cc188717.aspx

Entity Framework

The challenge of bringing data from efficient storage engines such as SQL Server into object-oriented programming models is hardly a new one. Most developers address this challenge by writing complex data access code to move data between their applications and the database. However, writing such code requires an understanding of the database, so that you can access data either from the raw tables, from views, or from stored procedures.

More often than not, databases are managed by DBAs whose job it is to ensure that the database is available, powerful, efficient, and secure. The means of accomplishing this generally take the data further out of the scope of your own concepts of how your data should be structured in your applications. It requires a solid knowledge of the database schema, table definitions, and stored procedures along with their parameters and results, views, keys, and more, so that you can create your data access code.

Entity-relationship modeling, introduced in the 1970s by Peter Chen, goes a long way toward solving this problem. Using entity-relationship modeling, programmers create a conceptual model of the data and write their data access code against that model, while an additional layer provides a bridge between the entity-relationship model and the actual data store. Most modeling, to date, gets done on a whiteboard.

With the ADO.NET Entity Framework, Microsoft has made entity-relationship modeling executable. They achieved this using a combination of XML schema files, behind the scenes code generation (creating .NET objects), and the ADO.NET Entity Framework APIs. The schema files define a conceptual layer to expose the data store’s (e.g., a SQL Server database) schema, and to create a map between that and your application-level objects. The ADO.NET Entity Framework allows you to write your programs against classes that are generated from this conceptual layer. The Framework then handles all the required translation as you extract data from the database and send it back in.

Figure 1: How the different layers of the Entity Data Model fit into your application.

This article provides an explanation of the basic concepts of ADO.NET Entity Framework, gives you an opportunity to get your hands dirty with code, and provides a glimpse into the possibilities that ADO.NET Entity Framework opens up for developers.

 

Click to read more…..

Export Gridview to Excel

The focus of the article is the Export to Excel functionality – the Gridview and it’s data binding are only for demonstrating the Export functionality.The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Default.aspx :

           <asp:SqlDataSource ID="sqldsCustomers" runat="server" SelectCommand="select * from UserDetails"
            SelectCommandType="Text" ConnectionString="server=server;database=LegalWindow;Trusted_Connection=yes;" />
        <asp:GridView ID="gvCustomers" runat="server" AllowPaging="true" AllowSorting="true"
            PageSize="10″" DataSourceID="sqldsCustomers" GridLines="Both" />
        <asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />

Default.aspx.cs :

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
//  pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}

GridViewExportUtil.cs

 public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }

For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility.  For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound.  You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting.  Here is the code for the export button click handler

protected void BtnExportGrid_Click(object sender, EventArgs args)
    {
        if (this.rdoBtnListExportOptions.SelectedIndex == 1)
        {
            //  the user wants all rows exported, turn off paging
            //  and rebing the grid before sending it to the export
            //  utility
            this.gvCustomers.AllowPaging = false;
            this.gvCustomers.DataBind();
        }
        else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
        {
            //  the user wants just the first 100,
            //  adjust the PageSize and rebind
            this.gvCustomers.PageSize = 100;
            this.gvCustomers.DataBind();
        }

        //  pass the grid that for exporting …
        GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
    }

For More Extensive Study of GridView Export to Excel………………read more

Repeater : Format Particular Cell in ItemDataBound using HtmlTableCell

In My one of the Project i need to display Data in repeater control with certain criteria.Criteria like i need to highlight particular cell when it is null so i want to change background of that cell so that it hightlight.so i feel this might be helpful to you also. so i m writing this post.It really very useful , u can also use this in yr application when need arise.

======================================================================
ASPX :
======================================================================

<asp:Repeater ID=”rpt” runat=”server” OnItemDataBound=”rpt_ItemDataBound” >
<ItemTemplate>
<tr><td id=”tdmon” runat=”server” align=”center”>
<asp:Label ID=”lblMon” runat=”server” Text=’<%# Eval(“monReason”) %>’></asp:Label>
<asp:LinkButton ID=”lnkMon” runat=”server”></asp:LinkButton>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
======================================================================
CS :
======================================================================

protected void rpt_ItemDataBound(object sender, RepeaterItemEventArgs e)
{

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
if (string.IsNullOrEmpty(((Label)e.Item.FindControl(“lblMon”)).Text))
{
((LinkButton)e.Item.FindControl(“lnkMon”)).Text = “Select”;

}
else
{

((HtmlTableCell)e.Item.FindControl(“tdmon”)).Attributes.Add(“class”, “Cell_Sceduleing_Highlight”);
}
}

}

Happy Programming !!!

Filtering & sorting DataTables with DataTable.Select()

We all know that connections to databases from within our applications cost us processor cycles and thus time so it’s a good idea to only ‘talk’ to your database when you really need too. In the .NET framework items like DataSets and DataTables etc. facilitate this to a large degree. In this post let’s take the .Select() method of the DataTable class as evidence of this.

.NET framework 2.0 DataTable.Select() method

The DataTable.Select() method has four overloads and allows you to filter and display your rows in a number of ways in a manner similar to the way SQL does. The method can accept an expression string which lthough not nearly as powerful as full blown SQL does provide the advantage of not having to go back to the database to do simple things like showing records relevant to only a certain date range or a certain customer, product etc.

Below is some of the valid expressions and filters you can pop into the Select() method to narrow your record set.

Standard Operator based expressions

Things like >, <, >=, <=, =, <> are all supported of course. If your DataTable was called salesStaff and you only wanted to return staff with sales of greater than 100, you could do this with salesStaff.Select(”sales > 100″). An array of DataRows will be returned, so depending on what you want to do with the results of the Select() method you may have to copy them back into another DataTable… more on that below.

More SQL query like expressions

Filtering by Like is also supported by the Select() method which I think is quite sweet. Wildcards as in SQL are * and %. Just like most variations of SQL support aggregate types so too does the Select() expression ‘language’. Items like AVG, MIN, MAX, SUM and COUNT are all supported, so too are functions like LEN and SUBSTRING. If you need to test multple columns/conditions you can join them with the AND or OR operators but be careful if your using .Net 1.1 SP 1 as there was a documented bug in .Select() when it was used with AND.

Sorting with DataTable.Select()

This is probabely the main way I use the Select() method. Its supported not via the expression parameter but via the sort parameter which is available in two overloaded .Select() methods. Its format is columnName asc/desc.

Importing DataTable.Select() method results into another DataTable

Putting the results from a Select() query into another DataTable is often a requirement say for instance if you wanted to bind your results to a control. As mentioned above the Select() method returns an array of DataRows and since DataRows are the main building blocks of DataTables the process is very easy.

//copy the schema of source table
DataTable above24 = dt.Clone();

//get only the rows you want
DataRow[] results = dt.Select(”age > 24″);

//populate new destination table
foreach (DataRow dr in results)
above24.ImportRow(dr);

DataTable.Select() shortcomings

The main things I don’t like about the .Select() method is how you have to go through intermediary steps to get your results into another DataTable, why can’t it just return another DataTable (which is directly bindable to a load of .NET data controls) and its lack of support for selecting distinct/unique rows which is often needed. As as note on that last one, it is possible to return distinct rows in a DataTable using LINQ which is a .NET 3.5 component, however that topic might best be served with another post at a later date.

Can’t I just use a DataView instead of calling DataTable.Select()?

You can and DataViews are directly bindable to many controls too, however it is not always the best solution due to the generally accepted believe among many developers that .Select() is much faster than using the DataView equivalent of RowFilter (property). I regularly interchange between the two for a lot of small database projects, however for the projects where I need to be processing a mega amount of data I pretty much stick with .Select() 100% of the time as I reckon it provides real speed (as in seconds, not PC invisible micro time) advantages compared to DataViews.

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;

    }

}