In this article we will learn how to use Microsoft Data Access Application block in ASP.Net using C#.
Why MVC?
The purpose of many computer systems is to retrieve data from a data store and display it for the user. After the user changes the data, the system stores the updates in the data. One problem is that the user interface tends to change much more frequently than the data storage system. Another problem with coupling the data and user interface pieces is that business applications tend to incorporate business logic that goes far beyond data transmission
The Model-View-Controller (MVC) pattern separates the modeling of the domain, the presentation, and the actions based on user input into three separate classes [Burbeck92]:
Model. The model manages the behavior and data of the application domain, responds to requests for information about its state (usually from the view), and responds to instructions to change state (usually from the controller).
View. The view manages the display of information.
Controller. The controller interprets the mouse and keyboard inputs from the user, informing the model and/or the view to change as appropriate.
What is Microsoft Data Access Application Block?
It consist of single .Net based assembly, which contains all of the functionalities necessary to perform the most common data access task against Microsoft SQL SERVER 7/2000 database.
Specifically the data access application block helps us in following:
- Calls stored procedure or SQL text command
- Specify parameter detail
- Return SqlDataReader, DataSet, XMLDataReader objects or single values
In general Data Access Application block is designed to encapsulate Microsoft's recommended best practices for data access.
What you can achieve with the use of Microsoft Data Access Application Block?
- Minimize the data access code you need to write often to single line.
- Ensures that your data access logic is implemented in an efficient and effective manner.
Mainly it has got SqlHelper class provides set of static methods which you can use to execute a variety of different command types against the database.
To have more data detail information on Microsoft Data Access Application please refer to following URL
http://www.microsoft.com/downloads/details.aspx?FamilyID=f63d1f0a-9877-4a7b-88ec-0426b48df275&DisplayLang=en
Database work to do as below:
-- Table Creation
create table tbl_Member
(
memberId Int primary key identity(1,1) Not Null,
firstName varchar(50) Not Null,
lastName varchar(50) Not Null,
contactNo varchar(15) Not Null,
emailAddress varchar(70) Not Null
)
-- Stored Procedure Creation
create procedure usps_proInsMember
(
@fName varchar(50),@lName varchar(50),@coNo varchar(15),@emailAddr varchar(70)
)
AS
INSERT INTO tbl_Member (firstName,lastName,contactNo,emailAddress)
VALUES(@fName,@lName,@coNo,@emailAddr)
-- StoredProcedure to listAllMember
create procedure usps_proSelectMember
AS
SELECT * FROM tbl_Member
-- exec usps_proInsMember 'Munir','Shaikh','23423423','munnamax@rediffmail.com'
-- SELECT * FROM tbl_Member
Let us start with actual topic as how to implement MVC with ASP.Net and c#.
I will assume that you have installed Microsoft Data Access Block For .Net on your machine you can download form Microsoft site.
Follow the steps as
Create new project with name "Mvc"
>>Right click on solution
>>Add
>>New Project
Select Class Library under Visual C# project. Give Library Name as: AbstractLayer
Similarly you need to follow above steps for "BusinessLayer" and "DataLayer" please refer to the image for the solution explorer how it looks like.
In short we have added three different libraries to MVC project, each these libraries themself are acting as single project.
Now let us follow steps to add references of these class libraries
>>Right click on Business Layer
>>Add References
>>Under Project Tab
>>Select Abstract Layer and Select Data Layer
Click on ok, and references get added to the selected library.
Similarly follow the steps for datalayer and add reference of Abstractlayer
STEPI:
Let us discuss what is "AbstractLayer" in our project?
In this project as we are dealing with the Member's basic functionalities like
- AddnewMember
- MemberList
In short I will call "ExecuteNonQuery" and "ExecuteDataset" methods from Microsoft Data access Application block for .net
So all the getter and setter methods will be there in this which we can derive from the class and use it while passing to object. So let us add class to AbstractLayer called as "baseMember.cs" which will hold all the getter and setter methods. As this class is acting as abstract so we need access above method by deriving this class in the "DataLayer"
STEPII:
So let us have a look at "DataLayer" class library, we will add a class called as "Member.cs" which is derived from baseMember main aim is to pass object to the respective methods. So entire code of this page look like below
using System;
using System.Data;
using System.Data.SqlClient;
using AbstractLayer;
using DataLayer;
namespace BusinessLayer
{
/// <summary>
/// Summary description for Member.
/// </summary>
public class Member : baseMember
{
public int AddnewMember(AbstractLayer.baseMember objMember)
{
IdataAccess MemberSqlDataAccess = new SqlDataAccess(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
return MemberSqlDataAccess.AddnewMember(objMember);
}
public DataSet MemberList()
{
DataSet dtSt;
IdataAccess MemberListSqlDataAccess = new SqlDataAccess(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
dtSt = MemberListSqlDataAccess.ListAllMembers();
return dtSt;
}
}
}
STEPIII:(when you install Microsoft Data Access Application block, you can find SqlHelper.cs to following location
under program files\Microsoft Application Blocks for .NET\Data Access\Code\CS\Microsoft.ApplicationBlocks.Data\SQLHelper.cs
)
DataLayer:
Copy SqlHelper.cs from above path and add under "DataLayer" in our project. We will add class called as "IdataAccess.cs" in "DataLayer" is basically acting as an interface code goes as below
using System;
using System.Data;
using AbstractLayer;
namespace DataLayer
{
/// <summary>
/// Summary description for IdataAccess.
/// </summary>
public interface IdataAccess
{
//all the method signature goes here
int AddnewMember(AbstractLayer.baseMember objMember);
DataSet ListAllMembers();
}
}
which will hold all the signatures to implement this interface signature we have to have another class so we will add another class "SqlDataAccess.cs" under the same layer. Code goes as below
using System;
using Microsoft.ApplicationBlocks.Data;
using AbstractLayer;
using System.Data;
using System.Data.SqlClient;
namespace DataLayer
{
/// <summary>
/// Summary description for sqlDataAccess.
/// </summary>
public class SqlDataAccess : IdataAccess
{
// All the implementation of signatures goes here
string m_Connection_String=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
public SqlDataAccess(string _ConnectionString)
{
m_Connection_String=_ConnectionString;
}
public int AddnewMember(AbstractLayer.baseMember objMember)
{
SqlTransaction objTrans=null;
SqlConnection myConnection = new SqlConnection(m_Connection_String);
try
{
// Insert Member Personal details only
myConnection.Open();
objTrans= myConnection.BeginTransaction();
SqlParameter [] arrParam=new SqlParameter[4];
arrParam[0]=new SqlParameter("@fName", objMember.firstName);
arrParam[1]=new SqlParameter("@lName",objMember.lastName);
arrParam[2]=new SqlParameter("@coNo",objMember.contactNo);
arrParam[3]=new SqlParameter("@emailAddr",objMember.emailAddress);
//pass connection string, storedprocedure name and parameter array
SqlHelper.ExecuteNonQuery(m_Connection_String,CommandType.StoredProcedure,"usps_proInsMember",arrParam);
}
catch(Exception Ex)
{
objTrans.Rollback();
string sError=Ex.Message.ToString();
return -1;
}
finally
{
myConnection.Close();
}
return 1;
}
public DataSet ListAllMembers()
{
DataSet dtStMembers;
dtStMembers = SqlHelper.ExecuteDataset(m_Connection_String,CommandType.StoredProcedure,"usps_proSelectMember");
return dtStMembers;
}
}
}
Note: We are making use of "Microsoft.ApplicationBlocks.Data" namespace in above class.
Now so far so good till now we have taken care of abstractlayer, businesslayer & Datalayers. Now let us discuss about our design i.e. HTML (Inline coding). As this deals with look and feel so we will add folder "View" under "MVC" project and to perform addition of new member we will add "AddMember.aspx" file, which is basically Member Screen through which input values will be supplied. Now let us look at the code behind of the above page basically we will instantiate the business class as
protected BusinessLayer.Member objMember = new BusinessLayer.Member();
and pass value as an object.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace Mvc
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnSave;
protected System.Web.UI.WebControls.TextBox txtFirstName;
protected System.Web.UI.WebControls.TextBox txtLastName;
protected System.Web.UI.WebControls.TextBox txtContactNo;
protected System.Web.UI.WebControls.TextBox txtEmailAddress;
protected System.Web.UI.WebControls.Label lblMessage;
protected BusinessLayer.Member objMember = new BusinessLayer.Member();
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void btnSave_Click(object sender, System.EventArgs e)
{
AddMember();
int retVal = objMember.AddnewMember(objMember);
if(retVal>0)
{
lblMessage.Text = "New record added successfully";
}
else
{
lblMessage.Text = "An error has occured while processing your request";
}
}
public void AddMember()
{
objMember.firstName = txtFirstName.Text.Trim();
objMember.lastName = txtLastName.Text.Trim();
objMember.contactNo = txtContactNo.Text.Trim();
objMember.emailAddress = txtEmailAddress.Text.Trim();
}
}
}
This will take care of your "Add New Member"
Similarly we can add "MemberList.aspx" under "View" folder and add datagrid to this page. To display the member list code goes as below.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace Mvc.View
{
/// <summary>
/// Summary description for MemberList.
/// </summary>
public class MemberList : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid dtGrdMember;
protected BusinessLayer.Member objMember = new BusinessLayer.Member();
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if (!Page.IsPostBack)
{
//To load DataGrid Code goes here
DataSet dtSt;
dtSt = objMember.MemberList();
dtGrdMember.DataSource = dtSt;
dtGrdMember.DataBind();
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
And that's all.
As normal developer you will always think that this is very big and vast procedure, but remember the benefits.
Advantages:
- Code will be separated from the Data layer due to which it will be very easy to maintain for the long run of the project, as every system keep on going under modification / enhancement so at that time you will have to just go on adding view files and signature in the interface and its implementation.
- Easy to understand and code transfer. i.e. when want to implement at client's server you just need to upload view files and DLL fields.
- It increases the system performance as there is no need to do connection pooling etc.
- Easy to maintain documentation
I have implemented above MVC Architecture for one the biggest system and working much better than normal way.
So read this article, pass it on to your friends and rate this article
Finally: While building this project remember to build sequentially
1st: AbstractLayer
2nd: BusinessLayer
3rd: DataLayer
4th: MVC project
As these are separate project's it self.
Troubleshooting: After doing all this next time you open MVC application you will find that libraries are not opened by default so you need to add them under the solution as an existing project.