Powered by Blogger.

Tuesday, September 24, 2013

Working with enterprise library for data access in asp.net

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

You can also check my previous posts on: ValidationGroup example in Asp.Net, WCF tutorial and example in C#.Net and Difference between primary key and foreign key in SQL server.

Here we will see how we can insert data into database by using inline sql statements using enterprise library in Asp.Net.

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:

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:

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

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

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

Below is the code:
.aspx code:
<form id="form1" runat="server">
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />
        <asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />
        <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>

.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");
        string sql = "insert into Employees (Name,Age,JoiningDate,Location) values (@Name,@Age,@JoiningDate,@Location)";
        DbCommand dbCommand = db.GetSqlStringCommand(sql);
        db.AddInParameter(dbCommand, "@Name", DbType.AnsiString, txtName.Text.ToString());
        db.AddInParameter(dbCommand, "@Age", DbType.AnsiString, txtAge.Text.ToString());
        db.AddInParameter(dbCommand, "@JoiningDate", DbType.DateTime, DateTime.Now);
        db.AddInParameter(dbCommand, "@Location", DbType.AnsiString, txtLocation.Text.ToString());
           int result = db.ExecuteNonQuery(dbCommand);
           if (result > 0)
               lblResult.Text = "Record saved successfully!";
               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. You can check Part-2 to do the same thing using Store Procedure.