3-tier architecture

Complex projects cannot directly write SQL statements into the program, which is not modular and difficult to maintain. A three-tier architecture should be adopted. The three-tier architecture in Web development is also the same. Model Layer Model; Data Access layer (DAL); business logic Layer (BLL ). The entity class is Model. The code for data operations is written in DAL, which is generally an SQL statement. DAL only performs data operations; BLL calls the code in the DAL to perform logical operations. The SQL statement should only appear in the DAL. Layer 3: UI (User Interface), BLL, and DAL. The Model transmits data between three layers. The UI Layer calls BLL and BLL to call the DAL. Data is transmitted using the Model. The UI cannot directly call the DAL.
SQLHelper
Using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Configuration;
Using System. Data;
Using System. Data. SqlClient;

Namespace three-tier architecture. DAL
{
Class SQLHelper
{
Public static readonly string conStr = ConfigurationManager. ConnectionStrings ["conStr"]. ConnectionString;
/// <Summary>
/// Execute non-query statements
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "parameters"> input variable </param>
/// <Returns> affected rows </returns>
Public static int ExecuteNonQuery (string SQL, params SqlParameter [] parameters)
{
Using (SqlConnection con = new SqlConnection (conStr ))
{
Con. Open ();
Using (SqlCommand cmd = con. CreateCommand ())
{
Cmd. CommandText = SQL;
If (parameters! = Null)
{
Foreach (SqlParameter param in parameters)
{
Cmd. Parameters. Add (param );
}
}
Return cmd. ExecuteNonQuery ();
}
}
}
/// <Summary>
/// Execute non-query statements
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "parameters"> input variable </param>
/// <Returns> return the value of the first column in the first row of the query result. </returns>
Public static object ExecuteScalar (string SQL, params SqlParameter [] parameters)
{
Using (SqlConnection con = new SqlConnection (conStr ))
{
Con. Open ();
Using (SqlCommand cmd = con. CreateCommand ())
{
Cmd. CommandText = SQL;
If (parameters! = Null)
{
Foreach (SqlParameter param in parameters)
{
Cmd. Parameters. Add (param );
}
}
Return cmd. ExecuteScalar ();
}
}
}
Public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] parameters)
{
Using (SqlConnection con = new SqlConnection (conStr ))
{
Con. Open ();
Using (SqlCommand cmd = con. CreateCommand ())
{
Cmd. CommandText = SQL;
If (parameters! = Null)
{
Foreach (SqlParameter param in parameters)
{
Cmd. Parameters. Add (param );
}
}
DataTable dt = new DataTable ();
SqlDataAdapter da = new SqlDataAdapter (cmd );
Da. Fill (dt );
Return dt;
}
}
}
}
}

DAL
1 using System;
2 using layer-3 architecture. Model;
3 using System. Data. SqlClient;
4 using System. Data;
5 using System. Collections. Generic;
6
7. namespace layer-3 architecture. DAL
8 {
9 class PersonDAL
10 {
11 // Add a field
12 public static int AddNew (Person model) // return the primary key of the newly added Field
13 {
14 object obj = SQLHelper. executeScalar ("insert into T_person (Age, Name) output inserted. id values (@ Age, @ Name) ", new SqlParameter (" Age ", model. age), new SqlParameter ("Name", model. name ));
15 return Convert. ToInt32 (obj );
16}
17 // Delete www.2cto.com
18 public static int Delete (int id)
19 {
20 return SQLHelper. ExecuteNonQuery ("delete from T_person where id = @ id", new SqlParameter ("id", id ));
21}
22 // update data
23 public static int UpDate (Person model)
24 {
25 return SQLHelper. executeNonQuery ("update T_Person set age = @ age, name = @ name where id = @ id", new SqlParameter ("age", model. age), new SqlParameter ("name", model. name), new SqlParameter ("id", model. id ));
26}
27 // query data
28 public static Person GetPerson (int id)
29 {
30 DataTable dt = SQLHelper. ExecuteDataTable ("select * from T_Person where id = @ id", new SqlParameter ("id", id ));
31 if (dt. Rows. Count <= 0)
32 {
33 return null;
34}
35 else if (dt. Rows. Count = 1)
36 {
37 DataRow row = dt. Rows [0];
38 Person person Person = new Person ();
39 person. Id = (int) row ["Id"];
40 person. Name = (string) row ["Name"];
41 person. Age = Convert. ToInt32 (row ["Age"]);
42 return person;
43}
44 else
45 {
46 throw new Exception ("unknown data error! ");
47}
48}
49 public static IEnumerable <Person> GetAllPerson ()
50 {
51 DataTable dt = SQLHelper. ExecuteDataTable ("select * from T_Person ");
52 List <Person> list = new List <Person> ();
53 foreach (DataRow row in dt. Rows)
54 {
55 Person person Person = new Person ();
56 person. Id = (int) row ["Id"];
57 person. Name = (string) row ["Name"];
58 person. Age = Convert. ToInt32 (row ["Age"]);
59 list. Add (person );
60}
61 return list;
62}
63}
64}

BLL
1 using layer-3 architecture. Model;
2 using three-tier architecture. DAL;
3 using System;
4 using System. Collections;
5 using System. Collections. Generic;
6
7. namespace layer-3 architecture. BLL
8 {
9 class PersonBLL
10 {
11 // Add a field
12 public static int AddNew (Person model) // return the primary key of the newly added Field
13 {
14 return PersonDAL. AddNew (model );
15}
16 // delete operation
17 public static int Delete (int id)
18 {
19 return PersonDAL. Delete (id );
20}
21 // update data
22 public static int UpDate (Person model)
23 {
24 if (model. Age <0)
25 {
26 throw new Exception ("Age cannot be negative! ");
27}
28 return PersonDAL. UpDate (model );
29}
30 // query a piece of data
31 public static Person GetPerson (int id)
32 {
33 return PersonDAL. GetPerson (id );
34}
35 // query all data
36 public static IEnumerable <Person> GetAllPerson ()
37 {
38 return PersonDAL. GetAllPerson ();
39}
40}
41}

Model
1 namespace layer-3 architecture. Model
2 {
3 class Person
4 {
5 public int Id {get; set ;}
6 public int Age {get; set ;}
7 public string Name {get; set ;}
8}
9}

 

Author: Xie xiaoge