Powered by Blogger.

Monday, February 24, 2014

Working with enterprise library for data access in asp.net Part-3




In this post we will discuss how to retrieve sql server table data and show in gridview using Microsoft enterprise library.

Also you can check out:

- Generics tutorial in C#.Net

- List all Stored Procedure Created and Modified in Last N Days in SQL Server 2008

- How to add Eval for hyperlink in gridview in asp.net?

In Part-1 posts we discussed about how to insert data using inline sql statement using enterprise library and in Part-2 we discussed about how to insert data using stored procedure using enterprise library.

To work with enterprise library you have to download required from this URL. Here we will use Enterprise Library 5.0 version.

We need to give reference in Bin folder to the following dlls:
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Configuration.Design.dll
Microsoft.Practices.EnterpriseLibrary.Configuration.DesignTime.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.dll
Microsoft.Practices.ServiceLocation.dll
Microsoft.Practices.Unity.dll
Microsoft.Practices.Unity.Interception.dll

Now open the web.config file to define the connection string. If you are using Windows authentication to login to database then write like below:

<connectionStrings>
<add name="connection" providerName="System.Data.SqlClient" connectionString="Data
Source=localhost;database=TestDB;Integrated Security=SSPI"/>
</connectionStrings>

But if you are using SQL Server authentication to login to SQL Server database then you can write like below:

<connectionStrings>
<add name="connection" providerName="System.Data.SqlClient" connectionString="Data
Source=localhost;Initial Catalog=TestDB;User ID=sa;Password=aspdotnethelp;Min Pool Size=10;Max Pool Size=100;Connect Timeout=100"/>
</connectionStrings>

Below is the stored procedure:

CREATE PROCEDURE Select_Employees

AS
BEGIN
Select * from Employees
END
GO

Below is the code:
.aspx code:
  <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>

.aspx.cs code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Configuration;

public partial class EnterpriseLibraryTest : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindEmployees();
        }
    }
    void BindEmployees()
    {
        Database db = DatabaseFactory.CreateDatabase("connection");
        DbCommand dbCommand = db.GetStoredProcCommand("Select_Employees");

        try
        {
            DataSet ds = db.ExecuteDataSet(dbCommand);
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();
        }
        catch (Exception ex)
        {

        }
    }
}



0 comments

Post a Comment