CheapASPNETHostingReview.com | Best and cheap ASP.NET hosting. In this tutorial we will show you how to using Microsoft Enterprise Library is a collection of reusable software components used for logging, validation, data access, exception handling etc.
Here I am describing how to use Microsoft Enterprise Library for data access.
Step 1: First download the project from http://entlib.codeplex.com/ URL.
Step 2: Now extract the project to get
1 2 3 4 | Microsoft.Practices.EnterpriseLibrary.Common.dll Microsoft.Practices.EnterpriseLibrary.Configuration.Design.dll Microsoft.Practices.EnterpriseLibrary.Data.dll Microsoft.Practices.ObjectBuilder.dll |
And give reference in the Bin directory by Right click on Bin -> Add Reference -> then give the path of these 4 dlls. Then
Step 3: Modification in the web.config for Connection String.
1 | <add name="ASPHostPortalConnection" providerName="System.Data.SqlClient" connectionString="DataSource=ASPHostPortalSQLEXPRESS;Initial Catalog=ASPHostPortal;User ID=sa;Password=admintest;Min Pool Size=10;Max Pool Size=100;Connect Timeout=100"/> |
Give the connection string as above where Datasource is your data source name, Initial Catalog is your database name and User ID and Password as in your sql server.
Step 4:
Now it is time to write the code.
Write the below 2 lines in the using block.
1 2 | using System.Data.Common; using Microsoft.Practices.EnterpriseLibrary.Data; |
Here I am writting some examples how to work on:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public DataTable Read() { try { Database db = DatabaseFactory.CreateDatabase("ASPHostPortalConnection"); DbCommand dbCommand = db.GetStoredProcCommand("[Topics_Return]"); DataSet dataSet = db.ExecuteDataSet(dbCommand); return dataSet.Tables[0]; } catch { return null; } } |
The above code is a sample that will return a dataset. Here Fewlines4bijuConnection is the connection name and Topics_Return is the stored procedure name that is nothing but a Select statement.
But if the stored procedure is taking parameter then the code will be like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | public int Save() { Database db = DatabaseFactory.CreateDatabase("ASPHostPortalConnection"); DbCommand dbCommand = db.GetStoredProcCommand("Topics_Save"); db.AddInParameter(dbCommand, "@Subject", DbType.AnsiString, "Here is the subject"); db.AddInParameter(dbCommand, "@Description", DbType.AnsiString, "Here is the Descriptiont"); db.AddInParameter(dbCommand, "@PostedBy", DbType.Int32, 4); db.AddOutParameter(dbCommand, "@Status", DbType.AnsiString, 255); try { db.ExecuteNonQuery(dbCommand); return Convert.ToInt32(db.GetParameterValue(dbCommand, "Status")); } catch { return 0; } } |
As the code explained above ASPHostPortal Connection is the connection name and Topics_Save is the stored procedure name that is taking 3 (Subject,Description,PostedBy) input parameters and 1(Status) output parameter.
You may give values from textbox, I am here provideing sample values like “Here is the subject”, “Here is the Descriptiont” or you may give the UserID from session, I am here giving 4. The output parameter will give you a string as defined and the code to get the value is
1 | int returnValue=Convert.ToInt32(db.GetParameterValue(dbCommand, "Status")); |
you can pass input parameter as below
1 | db.AddInParameter(dbCommand, "@Subject", DbType.AnsiString, "Here is the subject"); |
DbType.AnsiString since Subject is of string time, you can select different values like AnsiString, DateTime from the Enum as be the parameter type.
The above code describes if you are using any stored procedure.
Below is an example that shows how to use inline SQL statements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | public DataSet GetID(string title) { DataSet ds=new DataSet(); try { Database db = DatabaseFactory.CreateDatabase("ASPHostPortalConnection"); DbCommand dbCommand = db.GetSqlStringCommand("Select * FROM Topics where UserID=1 and IsDeleted=0"); ds= db.ExecuteDataSet(dbCommand); return ds; } catch { return ds; } return ds; } |
Happy coding!