Powered by Blogger.

Monday, February 24, 2014

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




In this post we will discuss about data access using enterprise library in Asp.net.

Also you can check out:

- How to display serial number automatically in GridView in asp.net?

- Call stored procedure in Asp.Net using C#.Net

- Constructor and Destructors in C#.Net

In the Part-1 we discussed how to insert data to database using Microsoft enterprise library using inline sql statements. Here we will check we can achieve the same thing using SQL Server.

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:

USE [TestDB]
GO

/****** Object:  StoredProcedure [dbo].[Insert_Users]    Script Date: 01/17/2013 22:05:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Insert_Employees]
@Name varchar(50),
@Age  int,
@Location  varchar(200)
AS
BEGIN
BEGIN TRANSACTION

BEGIN
INSERT INTO Employees (
Name,
Age,
JoiningDate,
Location
)
VALUES (
@Name,
@Age,
GETDATE(),
@Location)  
END
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END


GO


Below is the code:
.aspx code:
<form id="form1" runat="server">
    <div>
        Name:  
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />
        Age:    
        <asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />
        Location:
        <asp:TextBox ID="txtLocation" runat="server"></asp:TextBox><br />
        <asp:Button ID="btnSubmit" runat="server" Text="Submit"
            onclick="btnSubmit_Click" /><br />
        <asp:Label ID="lblResult" runat="server" Text=""></asp:Label>
    </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)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        Database db = DatabaseFactory.CreateDatabase("connection");
        DbCommand dbCommand = db.GetStoredProcCommand("Insert_Employees"); // Here Insert_Employees is the stored procedure name.
        db.AddInParameter(dbCommand, "@Name", DbType.AnsiString, txtName.Text.ToString());
        db.AddInParameter(dbCommand, "@Age", DbType.AnsiString, txtAge.Text.ToString());
        db.AddInParameter(dbCommand, "@Location", DbType.AnsiString, txtLocation.Text.ToString());
        try
        {
           int result = db.ExecuteNonQuery(dbCommand);
           if (result > 0)
           {
               lblResult.Text = "Record saved successfully!";
           }
           else
           {
               lblResult.Text = "OOPS Some error occured!";
           }
        }
        catch (Exception ex)
        {
            lblResult.Text = "OOPS Some error occured!";
        }
    }
}

Look at the using statements that we have used above.

After that the data will be save in the database.



0 comments

Post a Comment