ADO.NET(SqlHelper.cs)
新建一个SqlHelper.cs类
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Configuration;
6 using System.Data;
7 using System.Data.SqlClient;
8
9 namespace DAL
10 {
11 public class SqlHelper
12 {
13 #region 数据库连接字符串
14 private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
15 #endregion
16
17
18 #region 执行非查询语句（增删改）
19 public static int ExecuteNonQuery(string sqlText, params SqlParameter[] parameters)
20 {
21 using (SqlConnection conn = new SqlConnection(connStr))
22 {
23 conn.Open();
24 using (SqlCommand cmd = conn.CreateCommand())
25 {
26 cmd.CommandText = sqlText;
27 cmd.Parameters.AddRange(parameters);
28 return cmd.ExecuteNonQuery();
29 }
30 }
31 }
32
33
34 #endregion
35
36
37
38 #region 查询返回单个值
39 public static object ExecuteScalar(string sqlText, params SqlParameter[] parameters)
40 {
41 using (SqlConnection conn = new SqlConnection(connStr))
42 {
43 conn.Open();
44 using (SqlCommand cmd = conn.CreateCommand())
45 {
46 cmd.CommandText = sqlText;
47 cmd.Parameters.AddRange(parameters);
48 return cmd.ExecuteScalar();
49 }
50 }
51 }
52 #endregion
53
54
55
56 #region 查询多行数据 返回数据表
57 public static DataTable ExecuteDataTable(string sqlText, params SqlParameter[] parameters)
58 {
59 using (SqlConnection conn = new SqlConnection(connStr))
60 {
61 conn.Open();
62 using (SqlCommand cmd = conn.CreateCommand())
63 {
64 cmd.CommandText = sqlText;
65 cmd.Parameters.AddRange(parameters);
66 DataTable dt = new DataTable();
67 SqlDataAdapter da = new SqlDataAdapter( cmd);
68 da.Fill(dt);
69 return dt;
70 }
71 }
72 }
73
74 #endregion
75 }
76 }
上面的SqlHelpers.cs类可以帮我们解决一般的对数据库的增删查改操作；下面以数据库中StudentInfo表为例，通过调用SqlHelpers.cs类中的方法来实现对StudentInfo表的增删查改！
新建一个类StudentInfoServerce.cs
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using Model;
6 using System.Data;
7 using System.Data.SqlClient;
8
9
10 namespace DAL
11 {
12 public class StudentInfoServerce
13 {
14 #region 获取数据表所有数据
15 public List<StudentInfo> ListAll()
16 {
17 List<StudentInfo> list = new List<StudentInfo>();
18 DataTable dt = SqlHelper.ExecuteDataTable("select * from StudentInfo");
19 foreach (DataRow row in dt.Rows)
20 {
21 if (row != null)
22 {
23 list.Add(ToModel(row));
24 }
25 }
26 return list;
27
28 }
29 #endregion
30
31
32
33 #region 将数据行转换为实体
34 public static StudentInfo ToModel(DataRow row)
35 {
36 StudentInfo model = new StudentInfo();
37 model.ID1 = (int)(row.IsNull("ID") ? null : (System.Int32?)row["ID"]);
38 model.Name1 = (System.String)(row.IsNull("Name") ? null : row["Name"]);
39 model.Age1 = (System.Int32)(row.IsNull("Age") ? null : row["Age"]);
40 model.Address1 = (System.String)(row.IsNull("Address") ? null : row["Address"]);
41 return model;
42 }
43 #endregion
44
45
46
47 #region 通过ID 删除当前ID行数据
48 public bool Delete(int id)
49 {
50 int rows = SqlHelper.ExecuteNonQuery("delete from StudentInfo where ID=@ID", new SqlParameter("@ID", id));
51 return rows > 0;
52 }
53 #endregion
54
55
56
57 #region 根据id号 取得当前id当前行实体
58 public StudentInfo GetModel(int id)
59 {
60 DataTable dt = SqlHelper.ExecuteDataTable("select * from studentInfo where id=@id", new SqlParameter("@id", id));
61 if (dt.Rows.Count > 1)
62 {
63 throw new Exception("more than 1 row was found");
64 }
65 if (dt.Rows.Count <= 0)
66 {
67 return null;
68 }
69 return ToModel(dt.Rows[0]);
70 }
71 #endregion
72
73
74
75 #region 修改用户信息
76 public bool Modify(StudentInfo studentInfo)
77 {
78 string sql = "update StudentInfo set Name=@Name,Age=@Age,Address=@Address where id=@id";
79 int row = SqlHelper.ExecuteNonQuery(sql,
80 new SqlParameter("@id", studentInfo.ID1),
81 new SqlParameter("@Name", studentInfo.Name1),
82 new SqlParameter("@Age", studentInfo.Age1),
83 new SqlParameter("@Address", studentInfo.Address1));
84 return row > 0;
85 }
86 #endregion
87
88
89
90 #region 增添一天实体数据
91 public int Add(StudentInfo studentInfo)
92 {
93 string sql = "insert into StudentInfo (Name,Age,Address) output inserted.id values(@Name,@Age,@Address)";
94 int id = (int)SqlHelper.ExecuteScalar(sql,
95 new SqlParameter("@Name", studentInfo.Name1),
96 new SqlParameter("@Age", studentInfo.Age1),
97 new SqlParameter("@Address", studentInfo.Address1));
98 return id;
99 }
100 #endregion
101 }
102 }
