复杂项目不能把SQL语句直接写到程序里，不模块化、难以维护，应该采用三层架构。Web开发中的三层架构也是同样的结构。 模型层Model；数据访问层DAL（Data Access Layer）；业务逻辑层BLL（business logic layer ）。实体类就是Model；对数据进行操作的代码写在DAL中，一般就是SQL语句，DAL只有对数据的操作；BLL调用DAL中的代码进行逻辑操作SQL语句一般只应该出现在DAL中。 三层：UI（界面，User Interface）、BLL、DAL。Model是在三层之间进行数据传递的。UI层调用BLL、BLL调用DAL，数据用Model传递，UI不能直接调用DAL。
SQLHelper using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace 三层架构.DAL { class SQLHelper { public static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; /// /// 执行非查询语句 /// /// SQL语句 /// 传入变量 /// 影响行数 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(); } } } /// /// 执行非查询语句 /// /// SQL语句 /// 传入变量 /// 返回查询结果中的第一行第一列的值 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 三层架构.Model; 3 using System.Data.SqlClient; 4 using System.Data; 5 using System.Collections.Generic; 6 7 namespace 三层架构.DAL 8 { 9 class PersonDAL 10 { 11 //增加字段 12 public static int AddNew(Person model)//返回新添加字段的主键 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 //删除操作 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 //更新数据 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 //查询数据 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 = 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("未知数据错误！"); 47 } 48 } 49 public static IEnumerable GetAllPerson() 50 { 51 DataTable dt = SQLHelper.ExecuteDataTable("select * from T_Person"); 52 List list = new List (); 53 foreach (DataRow row in dt.Rows) 54 { 55 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 三层架构.Model; 2 using 三层架构.DAL; 3 using System; 4 using System.Collections; 5 using System.Collections.Generic; 6 7 namespace 三层架构.BLL 8 { 9 class PersonBLL 10 { 11 //增加字段 12 public static int AddNew(Person model)//返回新添加字段的主键 13 { 14 return PersonDAL.AddNew(model); 15 } 16 //删除操作 17 public static int Delete(int id) 18 { 19 return PersonDAL.Delete(id); 20 } 21 //更新数据 22 public static int UpDate(Person model) 23 { 24 if (model.Age < 0) 25 { 26 throw new Exception("年龄不能为负数！"); 27 } 28 return PersonDAL.UpDate(model); 29 } 30 //查询一条数据 31 public static Person GetPerson(int id) 32 { 33 return PersonDAL.GetPerson(id); 34 } 35 //查询所有数据 36 public static IEnumerable GetAllPerson() 37 { 38 return PersonDAL.GetAllPerson(); 39 } 40 } 41 } Model 1 namespace 三层架构.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 }