Reader Level:
Articles

MVC architecture in ASP.Net using C# and Microsoft Data Access Application block

By Munir Shaikh on February 28, 2006
In this article we will learn how to use Microsoft Data Access Application block in ASP.Net using C#, I have used Microsoft Data Access Application block in conjunction with MVC architecture, here you will learn lot about class libraries, interface, implementation etc.
  • 0
  • 0
  • 125466
Download Files:
 

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.

Mvc.gif

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:

  1. Calls stored procedure or SQL text command
  2. Specify parameter detail
  3. 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.

MDAA.jpg
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.

SolutionView.jpg

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

  1. AddnewMember 
  2. 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:

  1. 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.
  2. 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.
  3. It increases the system performance as there is no need to do connection pooling etc. 
  4. 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.

Munir Shaikh

Munir is MCP in Microsoft .NET Framework 3.5, Windows Communication FoundationAppl ication Developmen, software Developer/ project lead with 9 Yrs development experience who works on IT projects mainly for Microsoft and ... Read more

COMMENT USING

Trending up