2012/12/14

3 tier architecture example in asp.net with C#

Introduction

Here I will explain about uses of 3-Tier architecture and how to create or implement 3-tier architecture for our project in asp.net 
Description
1.    What is the use of 3-tier architecture and why we go for that architecture? 
2.    First we need to know what 3-Tier architecture is. 
3.    How to create 3-Tier architecture for our project?

 Uses of 3-Tier Architecture
  
1.    To make application more understandable. 
2.    Easy to maintain, easy to modify application and we can maintain good look of architecture.
3.    If we use this 3-Tier application we can maintain our application in consistency manner.   
Basically 3-Tier architecture contains 3 layers
1.    Application Layer or Presentation Layer 
2.    Business Access Layer(BAL) or Business Logic Layer(BLL) 
3.    Data Access Layer(DAL)
Here I will explain each layer with simple example that is User Registration
Application Layer or Presentation Layer
Presentation layer contains UI part of our application i.e., our aspx pages or input is taken from the user. This layer mainly used for design purpose and get or set the data back and forth. Here I have designed my registration aspx page like this

 

This is Presentation Layer for our project Design your page like this and double click on button save now in code behind we need to write statements to insert data into database this entire process related to Business Logic Layer and Data Access Layer.
Now we will discuss about Business Access Layer or Business Logic Layer 
Business Access Layer (BAL) or Business Logic Layer (BLL)
This layer contains our business logic, calculations related with the data like insert data, retrieve data and validating the data. This acts as a interface between Application layer and Data Access Layer
Now I will explain this business logic layer with my sample
I have already finished form design (Application Layer) now I need to insert user details into database if user click on button save. Here user entering details regarding Username, password, Firstname, Lastname, Email, phone no, Location. I need to insert all these 7 parameters to database. Here we are placing all of our database actions into data access layer (DAL) in this case we need to pass all these 7 parameters to data access layers.
In this situation we will write one function and we will pass these 7 parameters to function like this
String Username= InserDetails (string Username, string Password, string Email, string Firstname, string Lastname, string phnno, string Location)
If we need this functionality in another button click there also we need to declare the parameters like string Username, string Password like this rite. If we place all these parameters into one place and use these parameters to pass values from application layer to data access layer by using single object to whenever we require how much coding will reduce think about it for this reason we will create entity layer or property layer this layer comes under sub of group of our Business Logic layer
Don't get confuse just follow my instructions enough
How we have to create entity layer it is very simple 
Right click on your project web application---> select add new item ----> select class file in wizard ---> give name as BEL.CS because here I am using this name click ok
 Open the BEL.CS class file declare the parameters like this in entity layer 
Don’t worry about code it’s very simple for looking it’s very big nothing is there just parameters declaration that’s all check I have declared whatever the parameters I need to pass to data access layer I have declared those parameters only 
BEL.CS

#region Variables
/// <summary>
/// User Registration Variables
/// </summary>
private string _UserName;
private string _Password;
private string _FirstName;
private string _LastName;
private string _Email;
private string _Phoneno;
private string _Location;
private string _Created_By;
#endregion
/// <summary>
/// Gets or sets the <b>_UserName</b> attribute value.
/// </summary>
/// <value>The <b>_UserName</b> attribute value.</value>
public string UserName
{
get
{
return _UserName;
}
set
{
_UserName = value;
}
}
/// <summary>
/// Gets or sets the <b>_Password</b> attribute value.
/// </summary>
/// <value>The <b>_Password</b> attribute value.</value>
public string Password
{
get
{
return _Password;
}
set
{
_Password = value;
}
}
/// <summary>
/// Gets or sets the <b>_FirstName</b> attribute value.
/// </summary>
/// <value>The <b>_FirstName</b> attribute value.</value>
public string FirstName
{
get
{
return _FirstName;
}
set
{
_FirstName = value;
}
}
/// <summary>
/// Gets or sets the <b>_LastName</b> attribute value.
/// </summary>
/// <value>The <b>_LastName</b> attribute value.</value>
public string LastName
{
get
{
return _LastName;
}
set
{
_LastName = value;
}
}
/// <summary>
/// Gets or sets the <b>_Email</b> attribute value.
/// </summary>
/// <value>The <b>_Email</b> attribute value.</value>
public string Email
{
get
{
return _Email;
}
set
{
_Email = value;
}
}
/// <summary>
/// Gets or sets the <b>_Phoneno</b> attribute value.
/// </summary>
/// <value>The <b>_Phoneno</b> attribute value.</value>
public string Phoneno
{
get
{
return _Phoneno;
}
set
{
_Phoneno = value;
}
}
/// <summary>
/// Gets or sets the <b>_Location</b> attribute value.
/// </summary>
/// <value>The <b>_Location</b> attribute value.</value>
public string Location
{
get
{
return _Location;
}
set
{
_Location = value;
}
}
/// <summary>
/// Gets or sets the <b>_Created_By</b> attribute value.
/// </summary>
/// <value>The <b>_Created_By</b> attribute value.</value>
public string Created_By
{
get
{
return _Created_By;
}
set
{
_Created_By = value;
}
Our parameters declaration is finished now I need to create Business logic layer how I have create it follow same process for add one class file now give name called BLL.CS. Here one point don’t forget this layer will act as only mediator between application layer and data access layer based on this assume what this layer contains. Now I am writing the following BLL.CS(Business Logic layer)

#region Insert UserInformationDetails
/// <summary>
/// Insert UserDetails
/// </summary>
/// <param name="objUserBEL"></param>
/// <returns></returns>
public string InsertUserDetails(BEL objUserDetails)
{
DAL objUserDAL = new DAL();
try
{
return objUserDAL.InsertUserInformation(objUserDetails);
}
catch (Exception ex)
{
throw ex;
}
finally
{
objUserDAL = null;
}
}
#endregion
Here if you observe above code you will get doubt regarding these
what is
BEL objUserDetails
DAL objUserDAL = new DAL();
and how this method comes
return objUserDAL.InsertUserInformation(objUserDetails);
Here BEL objUserDetails means we already created one class file called BEL.CS with some parameters have you got it now I am passing all these parameters to Data access Layer by simply create one object for our BEL class file 
What is about these statements I will explain about it in data access layer
DAL objUserDAL = new DAL();
return objUserDAL.InsertUserInformation(objUserDetails);
this DAL related our Data access layer. Check below information to know about that function and Data access layer
Data Access Layer(DAL)
Data Access Layer contains methods to connect with database and to perform insert,update,delete,get data from database based on our input data
I think it’s to much data now directly I will enter into DAL
Create one more class file like same as above process and give name as DAL.CS
Write the following code in DAL class file

//SQL Connection string
string ConnectionString = ConfigurationManager.AppSettings["LocalConnection"].ToString();
#region Insert User Details
/// <summary>
/// Insert Job Details
/// </summary>
/// <param name="objBELJobs"></param>
/// <returns></returns>
public string InsertUserInformation(BEL objBELUserDetails)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("sp_userinformation", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@UserName",objBELUserDetails.UserName);
cmd.Parameters.AddWithValue("@Password", objBELUserDetails.Password);
cmd.Parameters.AddWithValue("@FirstName", objBELUserDetails.FirstName);
cmd.Parameters.AddWithValue("@LastName", objBELUserDetails.LastName);
cmd.Parameters.AddWithValue("@Email", objBELUserDetails.Email);
cmd.Parameters.AddWithValue("@PhoneNo", objBELUserDetails.Phoneno);
cmd.Parameters.AddWithValue("@Location", objBELUserDetails.Location);
cmd.Parameters.AddWithValue("@Created_By", objBELUserDetails.Created_By);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string strMessage = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
return strMessage;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
Here if you observe above functionality I am getting all the parameters by simply creating BEL objBELUserDetails. If we create one entity file we can access all parameters through out our project by simply creation of one object for that entity class based on this we can reduce redundancy of code and increase re usability
Observe above code have u seen this function before? in BLL.CS i said i will explain it later got it in DAL.CS I have created one function InsertUserInformation and using this one in BLL.CS by simply creating one object of DAL in BLL.CS.
Here you will get one doubt that is why BLL.CS we can use this DAL.CS directly into our code behind  we already discuss Business logic layer provide interface between DAL and Application layer by using this we can maintain consistency to our application.
Now our Business Logic Layer is ready and our Data access layer is ready now how we can use this in our application layer write following code in your save button click like this

protected void btnsubmit_Click(object sender, EventArgs e)
{
string Output = string.Empty;
if (txtpwd.Text == txtcnmpwd.Text)
{
BEL objUserBEL = new BEL();
objUserBEL.UserName = txtuser.Text;
objUserBEL.Password = txtpwd.Text;
objUserBEL.FirstName = txtfname.Text;
objUserBEL.LastName = txtlname.Text;
objUserBEL.Email = txtEmail.Text;
objUserBEL.Phoneno = txtphone.Text;
objUserBEL.Location = txtlocation.Text;
objUserBEL.Created_By = txtuser.Text;
BLL objUserBLL = new BLL();
Output = objUserBLL.InsertUserDetails(objUserBEL);
}
else
{
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "Password mismatch" + "');</script>");
}
lblErrorMsg.Text = Output;
}
Here if you observe I am passing all parameters using this BEL(Entity Layer) and we are calling the method InsertUserDetails by using this BLL(Business Logic Layer)
Now run your applciation test with debugger you can get idea clearly.

SQL SERVER JOINs

 Introduction

we’ll see the basic concepts of SQ L Joins. In the later part of the article, we’ll focus on the advanced subject of Self-JOIN and some interesting observations on how inner JOIN can be simulated using left JOIN. The author has tried his best to amalgamate various topics in a single concept.
The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

Inner JOIN

A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN.  This is the default type of JOIN in the Query and View Designer.


Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN.  You can create three different outer JOINs to specify the unmatched rows to be included:
Left Outer JOIN: In Left Outer JOIN, all rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer JOIN: In Right Outer JOIN, all rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.

Full Outer JOIN: In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.


Additional Notes related to JOIN

The following are three classic examples to demonstrate the cases where Outer JOIN is useful. You must have noticed several instances where developers write query as given below.
1.SELECT t1.*
2.FROM Table1 t1
3.WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
4.GO
The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.
1./* LEFT JOIN - WHERE NULL */
2.SELECT t1.*,t2.*
3.FROM Table1 t1
4.LEFT JOIN Table2 t2 ON t1.ID = t2.ID
5.WHERE t2.ID IS NULL
The above example can also be created using Right Outer JOIN.

NOT Inner JOIN
Remember, the term Not Inner JOIN does not exist in database terminology. However, when full Outer JOIN is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner JOIN. This JOIN will show all the results that were absent in Inner JOIN.


Cross JOIN

A cross JOIN devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. One common example is when a company lists all its products in a pricing table to compare each product with others prices.


Self-JOIN

In this particular case, one table JOINs to itself with one or two aliases to stave off confusion. A self-JOIN can be of any type, as long as the joined tables are the same. A self-JOIN is unique in the sense that it involves a relationship with only one table. A common example is when a company has a hierarchal reporting structure whereby a member of staff reports to another member. Self-JOIN can either be an Outer JOIN or an Inner JOIN.
Self-JOIN is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values of the same column. Self-JOIN is a JOIN in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-JOIN can either be an inner JOIN or an outer JOIN. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data type of the inter-related columns must be of the same type or cast to the same type.
Now, think of a situation where all the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to employee information, where the table may have both an employee’s ID number for each record and also a field that displays the ID number of an employee’s supervisor or manager. To retrieve the data, it is mandatory for the tables to relate/JOIN to itself.
Another example that can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer to the sample database for table structure.
Note: Before we continue further let me make it very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN, it should be done with priority.
Run the following two scripts and observe the result-set. It will be identical.
01.USE AdventureWorks
02.GO
03./* Example of INNER JOIN */
04.SELECT p.ProductID, piy.ProductID
05.FROM Production.Product p
06.INNER JOIN Production.ProductInventory piy ON piy.ProductID = p.ProductID
07.GO
08./* Example of LEFT JOIN simulating INNER JOIN */
09.SELECT p.ProductID, piy.ProductID
10.FROM Production.Product p
11.LEFT JOIN Production.ProductInventory piy ON 1 = 1
12.WHERE piy.ProductID = p.ProductID
13.GO   
After gazing at the identical result the first question that cropped up in my mind was - what is behind the scene plan? Looking at the actual execution plan of the query it is quite evident that even if LEFT JOIN is used in SQL Server Query Optimizer, it converts to INNER JOIN since results are the same and performance is better.

Looking at the above scenario it makes me ponder how smart Query Optimizer Engine is and how it might be saving innumerable performance-related issues for sub-optimal queries.
Now let us try to grasp the cause of LEFT JOIN acting as INNER JOIN. When 1= 1 is used in ON clause it is always true and converts LEFT JOIN to CROSS JOIN. However, when WHERE condition’s effect is applied to the above CROSS JOIN it produces a result similar to INNER JOIN in our case. SQL Server Query Optimizer interprets this in advance and uses INNER JOIN right away.
I think a good question to ask in an interview would be -“How to write an OUTER JOIN that will give you the exact result, execution plan and performance as INNER JOIN?”

WebService or Creating and Consuming Web Service in asp.net

Introduction:


Here I will explain what webservice is, uses of webservice and how to create webservice and how to consume webservice in asp.net.

Description:
Today I am writing article to explain about webservices. First we will see what is webservice is and uses of webservice and then we will see how to use webservice in our applications.

What is Web Service?

Web Service is an application that is designed to interact directly with other applications over the internet. In simple sense, Web Services are means for interacting with objects over the Internet. The Web serivce consumers are able to invoke method calls on remote objects by using SOAP and HTTP over the Web. WebService is language independent and Web Services communicate by using standard web protocols and data formats, such as
  • HTTP
  • XML
  • SOAP
Advantages of Web Service

Web Service messages are formatted as XML, a standard way for communication between two incompatible system. And this message is sent via HTTP, so that they can reach to any machine on the internet without being blocked by firewall.

Examples for Web Service

Weather Reporting: You can use Weather Reporting web service to display weather information in your personal website.

Stock Quote: You can display latest update of Share market with Stock Quote on your web site.

News Headline: You can display latest news update by using News Headline Web Service in your website.

In summary you can any use any web service which is available to use. You can make your own web service and let others use it. Example you can make Free SMS Sending Service with footer with your advertisement, so whosoever use this service indirectly advertise your company... You can apply your ideas in N no. of ways to take advantage of it.

Frequently used word with web services

What is SOAP?

SOAP (simple object access protocol) is a remote function calls that invokes method and execute them on Remote machine and translate the object communication into XML format. In short, SOAP are way by which method calls are translate into XML format and sent via HTTP.

What is WSDL? 

WSDL stands for Web Service Description Language, a standard by which a web service can tell clients what messages it accepts and which results it will return.
WSDL contains every detail regarding using web service and Method and Properties provided by web service and URLs from which those methods can be accessed and Data Types used.

What is UDDI?

UDDI allows you to find web services by connecting to a directory.

What is Discovery or .Disco Files?

Discovery files are used to group common services together on a web server. Discovery files .Disco and .VsDisco are XML based files that contains link in the form of URLs to resources that provides discovery information for a web service. Disco File contains URL for the WSDL, URL for the documentation and URL to which SOAP messages should be sent.

Before start creating web service first create one table in your database and give name UserInformation in my code I am using same name and enter some dummy data for our testing purpose

Column Name
Data Type
Allow Nulls
UserId
Int(Set Identity=true)
No
UserName
Varchar(50)
Yes
FirstName
Varchar(50)
Yes
LastName
Varchar(50)
Yes
Location
Varchar(50)
Yes

Now we will see how to create new web service application in asp.net

Open visual studio ---> Select File ---> New ---> Web Site ---> select ASP.NET Web Service



Now our new web service ready our webservice website like this



Now open your Service.cs file in web service website to write the code to get the user details from database

Before writing the WebMethod in Service.cs first add following namespaces


using System.Xml;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
After adding namespaces write the following method GetUserDetails in Service.cs page

[WebMethod]
public XmlElement GetUserDetails(string userName)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from UserInformation where UserName like @userName+'%'", con);
cmd.Parameters.AddWithValue("@userName", userName);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// Create an instance of DataSet.
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
// Return the DataSet as an XmlElement.
XmlDataDocument xmldata = new XmlDataDocument(ds);
XmlElement xmlElement = xmldata.DocumentElement;
return xmlElement;
}

Here we need to remember one point that is adding [WebMethod] before method definition because we need to access web method pulically otherwise it’s not possible to access method publically. If you observe above code I converted dataset to XmlElement t because sometimes we will get error like return type dataset invalid type it must be either an IListSource, IEnumerable, or IDataSource to avoid this error I converted dataset to XmlElement.

Here we need to set the database connection in web.config because here I am getting database connection from web.config


<connectionStrings>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings>
Now run your web service it would be like this


Our web service is working fine now we need to know how we can use webservice in our application? Before to know about using web service in application first Deploy your webservice application in your local system if you want to know how to deploy application in your local system check this link deploy application in local system

How to Use Web service in web application?

By using this webservice we can get the user details based on username. For that first create one new web application

Open visual studio ---> Select File ---> New ---> Web Site ---> select ASP.NET Web Site


After creation of new website right click on solution explorer and choose “Add web reference” that would be like this


After select Add Web reference option one window will open like this 



Now enter your locally deployed web service link and click Go button after that your web service will found and window will looks like this


 

Now click on Add Reference button web service will add successfully. Now open your Default.aspx page and design like this


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Getting Data from WebService</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<b>Enter UserName:</b>
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
<div>
<asp:GridView ID="gvUserDetails" runat="server" EmptyDataText="No Record Found">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Now in code behind add following namespaces

using System.Data;
using System.Xml;


After adding namespaces write the following code in code behind


protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindUserDetails("");
}
}
protected void BindUserDetails(string userName)
{
localhost.Service objUserDetails = new localhost.Service();
DataSet dsresult = new DataSet();
XmlElement exelement = objUserDetails.GetUserDetails(userName);
if(exelement!=null)
{
XmlNodeReader nodereader = new XmlNodeReader(exelement);
dsresult.ReadXml(nodereader, XmlReadMode.Auto);
gvUserDetails.DataSource = dsresult;
gvUserDetails.DataBind();
}
else
{
gvUserDetails.DataSource = null;
gvUserDetails.DataBind();   
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
BindUserDetails(txtUserName.Text);
}