Powered by Blogger.

Sunday, February 23, 2014

Data access block of Microsoft enterprise library 6.0 using inline sql statement




In this post we will discuss how to use data access block of Microsoft Enterprise Library 6.0. Previously we have discussed how to use Microsoft Enterprise Library 5.

Also you can check out some of the posts:

- Export Data Of DataGridview To Excel using C# Windows Appllication

- Constructor and Destructors in C#.Net

- How to handle exception in sql server stored procedure?

First of all visit this link to download the files and after you can unzip for the required dlls.

The below dlls we needed to work with data access block"
- Microsoft.Practices.EnterpriseLibrary.Common.dll
- Microsoft.Practices.EnterpriseLibrary.Data.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 full code:

 protected void btnSubmit_Click(object sender, EventArgs e)
    {
        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        Database db = factory.Create("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());
        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!";
        }
    }

The above 2 lines which in bold are very important. Else you will get this error.



0 comments

Post a Comment