How to create a three-tier database application using C #

Source: Internet
Author: User
Tags date count reference tostring
Programs | creating | data | How the database uses C # to create a three-tier database application
1. Analysis
The following hierarchy is used in our program: The Web layer, the business entity layer, and the data tier.
which
The business entity layer is responsible for data exchange between the Web layer and the data tier.
The data tier represents only the database.
The Web layer accesses the database through the business entity layer.
Our middle business entity layer uses WebService.
2. Instance
We learn a three-tier architecture from an example.
(1) Taking sql2000 as an example
Establish a testuser database.
Table's SQL script (executed in Query Analyzer):
/****** object:table [dbo]. [Customers] Script date:2004-01-08 0:46:35 ******/
CREATE TABLE [dbo]. [Customers] (
[CustomerID] [INT] IDENTITY (1, 1) not NULL,
[CustomerName] [Char] () not NULL,
[Addr] [varchar] (m) NULL,
[City] [Char] (a) NULL,
[Phone] [Char] (a) NULL,
[Fax] [Char] (Ten) NULL
) on [PRIMARY]
Go

/****** object:table [dbo]. [Users] Script date:2004-01-08 0:46:36 ******/
CREATE TABLE [dbo]. [Users] (
[ID] [int] IDENTITY (1, 1) not NULL,
[Truename] [Char] () not NULL,
[Regname] [Char] () not NULL,
[PWD] [Char] (ten) Not NULL,
[Sex] [Char] (2) NULL,
[Email] [Char] (a) NULL
) on [PRIMARY]
Go

ALTER TABLE [dbo]. [Customers] With NOCHECK ADD
CONSTRAINT [pk_customers] PRIMARY KEY nonclustered
(
[CustomerID]
) on [PRIMARY]
Go

ALTER TABLE [dbo]. [Users] With NOCHECK ADD
CONSTRAINT [pk_users] PRIMARY KEY nonclustered
(
[ID]
) on [PRIMARY]
Go

(2) Create a business entity layer
1. Open vs.net2002, create a new project, select ASP.net Web service, location: http://localhost/mydotnet/tiner/WebData/
2. WebService's Code
Using System;
Using System.Collections;
Using System.ComponentModel;
Using System.Data;
Using System.Data.SqlClient;
Using System.Diagnostics;
Using System.Web;
Using System.Web.Services;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.HtmlControls;

Namespace WebData
{
<summary>
Summary description of the Service1.
</summary>
[WebService (Namespace = "http://www.ourfly.com", Description = "<font size=4 color= ' #FF6633 ' ><b><br ><center> programs that use C # to write a three-tier architecture. </center></b><br><br></font> ")]
public class Service1:System.Web.Services.WebService
{
SqlDataAdapter Myadapter;
String strconn= "Data source=localhost;initial catalog=testuser;uid=sa;pwd=";

Public Service1 ()
{
CodeGen: This call is required by the ASP.net Web service designer
InitializeComponent ();
}

#region Component Designer generated code

Required by the WEB service designer
Private IContainer components = null;

<summary>
Designer supports required methods-do not use the Code editor to modify
The contents of this method.
</summary>
private void InitializeComponent ()
{
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
if (disposing && components!= null)
{
Components. Dispose ();
}
Base. Dispose (disposing);
}

#endregion

Defines a private method that is used to determine whether a user exists
Private Boolean Boolreg (string strregname)
{
Boolean strresult;
SqlConnection cn;
SqlCommand cmd;

String strSQL;
Cn=new SqlConnection (strconn);
cn. Open ();

Strsql= "SELECT COUNT (*) from the Users where Regname= '" +strregname+ "";
Cmd=new SqlCommand (STRSQL,CN);

SqlDataReader reader = cmd. ExecuteReader ();
Reader. Read ();
int i = reader. GetInt32 (0);
if (i>0)
{
Reader. Close ();
cn. Close ();
strresult= true;
}
Else
{
Reader. Close ();
cn. Close ();
Strresult=false;
}

return strresult;
}

[WebMethod (description= "Completes the user registration function.")]
public string Reguser (string strtruename,string strregname,string strpwd,string strsex,string stremail)
{
String strresult;
SqlConnection cn;
SqlCommand cmd;

To determine whether a user exists
if (Boolreg (strregname))
{
strresult= "This user already exists, please re-register";
return strresult;
}
Else
{
String strSQL;
Cn=new SqlConnection (strconn);
cn. Open ();

strsql= INSERT INTO Users (truename,regname,pwd,sex,email) VALUES (' ";
Strsql+=strtruename+ "', '";
Strsql+=strregname+ "', '";
Strsql+=strpwd+ "', '";
strsql+=strsex+ "', '";
strsql+=stremail+ "')";

Cmd=new SqlCommand (STRSQL,CN);

Try
{
Cmd. ExecuteNonQuery ();
cn. Close ();
strresult= "User registration Success";
}
catch (Exception e)
{
cn. Close ();
strresult= "Please check your entries carefully";
}
}
return strresult;

}

[WebMethod (description= "User login")]
public string Login (String strregname,string strpwd)
{
SqlConnection cn;
SqlDataAdapter da;
DataSet ds;
String Strsql,strresult;

Strsql= "Select Truename,regname,pwd from Users where Regname= '" +strregname+ "' and Pwd= '" "+strpwd+" ";

Cn=new SqlConnection (strconn);
cn. Open ();

Da=new SqlDataAdapter (STRSQL,CN);
Ds=new DataSet ();
Da. Fill (ds, "Users");

if (ds. tables["Users". ROWS.COUNT&GT;0)
{
strresult= "Login Success";

}
Else
{
strresult= "username or password is incorrect or not this user!" Please re-enter! ";

}
cn. Close ();
return strresult;
}


[WebMethod (description= "Gets the DataSet.")]
Public DataSet GetDataSet ()
{
SqlConnection cn;
Cn=new SqlConnection (strconn);
String Strsel= "select * from Customers";
cn. Open ();
Myadapter=new SqlDataAdapter (Strsel,strconn);
DataSet ds=new DataSet ();
Myadapter.fill (ds, "Customers");
return DS;
}
}
}

After running, the following figure shows:

(3) Web presentation layer
Open vs.net2002, create a new project, select ASP.net web application, location: Http://localhost/mydotnet/tiner/WebApplication1
In Solution Explorer, right-click Reference and choose Add Web Reference, enter Http://localhost/mydotnet/tiner/WebData/Service1.asmx as shown in the following image:

After adding the reference, the following figure:
OK, let's start writing code, the detailed code is as follows:
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;
Using System.Data.SqlClient;

Namespace Tiner
{
<summary>
Summary description of the WebForm1.
</summary>
public class WebForm1:System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.TextBox txtUserName;
protected System.Web.UI.WebControls.Button Btlogin;
protected System.Web.UI.WebControls.Button Btreg;
protected System.Web.UI.WebControls.Panel Panel1;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.TextBox txttruename;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.Label Label7;
protected System.Web.UI.WebControls.Label Label8;
protected System.Web.UI.WebControls.Button Btok;
protected System.Web.UI.WebControls.TextBox txtregname;
protected System.Web.UI.WebControls.TextBox txtpwd;
protected System.Web.UI.WebControls.DropDownList dropdownlistsex;
protected System.Web.UI.WebControls.TextBox Txtemail;
protected System.Web.UI.WebControls.TextBox Txtpassword;

String Myresult;
DataSet ds;
localhost. Service1 myservice =new localhost. Service1 ();

private void Page_Load (object sender, System.EventArgs e)
{
Place user code here to initialize page
if (! Page.IsPostBack)
{
Panel1.visible =false;
}
}

#region Web Form Designer generated code
Override protected void OnInit (EventArgs e)
{
//
CodeGen: This call is required for the ASP.net Web forms Designer.
//
InitializeComponent ();
Base. OnInit (e);
}

<summary>
Designer supports required methods-do not use the Code editor to modify
The contents of this method.
</summary>
private void InitializeComponent ()
{
This. Btlogin.click + = new System.EventHandler (this. Btlogin_click);
This. Btreg.click + = new System.EventHandler (this. Btreg_click);
This. Btok.click + = new System.EventHandler (this. Btok_click);
This. Load + = new System.EventHandler (this. Page_Load);

}
#endregion



private void Btreg_click (object sender, System.EventArgs e)
{
Datagrid1.visible =false;
Panel1.visible =true;
}

private void Btlogin_click (object sender, System.EventArgs e)
{
if (txtUsername.Text = = "" | | txtpassword.text== "")
{
Label1.Text = "Please enter user name or password";
Return
}

Datagrid1.visible =true;
Panel1.visible =false;
Myresult=myservice.login (Txtusername.text,txtpassword.text);
if (myresult.tostring () = "Login Successful")
{
Ds=myservice.getdataset ();
DataGrid1.DataSource =ds. tables["Customers"];
Datagrid1.databind ();
}
Else
{
Label1.Text = "User name or password is incorrect or not this user!" Please re-enter! ";
}
}

private void Btok_click (object sender, System.EventArgs e)
{
Myresult=myservice.reguser (Txttruename.text,txtregname.text,txtpwd.text,dropdownlistsex.selecteditem.text, Txtemail.text);
if (myresult.tostring () = "User Registration successful")
{
Label1.Text = "User registration is successful, you can login to view information";
Return
}
else if (myresult.tostring () = "This user already exists, please re-register")
{
Label1.Text = "This user already exists, please re-register";
Return
}
Else
{
Label1.Text = "User Registration error, please check each item";
Return
}

}

}
}
Run Start, enter the correct username and password, click the "Login" button, you will see the following interface:
Click "Register New User", appear the registration interface, if the registered user exists, will produce the prompt:
Summarize:
The Web presentation layer has no database connection operations at all, and its connection to the database task is done through the business layer, so that the program's structure is clearer. Of course, the program can add other layers, such as: Business Rules layer.
If you are wrong, you are welcome to advise.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.