1using System;
2using System.Collections.Generic;
3using System.Configuration;
4using System.Data;
5using System.Data.SqlClient;
6
7namespace NGuestBook.SQLServerDAL
8{
9 /// <summary>
10 /// SQLServer数据库操作助手
11 /// </summary>
12 public sealed class SQLServerDALHelper
13 {
14 /// <summary>
15 /// 用于连接SQLServer数据库的连接字符串，存于Web.config中
16 /// </summary>
17 private static readonly string _sqlConnectionString = ConfigurationManager.AppSettings["SQLServerConnectionString"];
18
19 /// <summary>
20 /// 执行SQL命令，不返回任何值
21 /// </summary>
22 /// <param name="sql">SQL命令</param>
23 public static void ExecuteSQLNonQurey(string sql)
24 {
25 SqlConnection connection = new SqlConnection(_sqlConnectionString);
26 SqlCommand command = new SqlCommand(sql,connection);
27 connection.Open();
28 command.ExecuteNonQuery();
29 connection.Close();
30 }
31
32 /// <summary>
33 /// 执行SQL命令，并返回SqlDataReader
34 /// </summary>
35 /// <param name="sql">SQL命令</param>
36 /// <returns>包含查询结果的SqlDataReader</returns>
37 public static SqlDataReader ExecuteSQLReader(string sql)
38 {
39 SqlConnection connection = new SqlConnection(_sqlConnectionString);
40 SqlCommand command = new SqlCommand(sql, connection);
41 connection.Open();
42 SqlDataReader sqlReader = command.ExecuteReader();
43 //connection.Close();
44
45 return sqlReader;
46 }
47
48 /// <summary>
49 /// 执行存储过程，不返回任何值
50 /// </summary>
51 /// <param name="storedProcedureName">存储过程名</param>
52 /// <param name="parameters">参数</param>
53 public static void ExecuteProcedureNonQurey(string storedProcedureName,IDataParameter[] parameters)
54 {
55 SqlConnection connection = new SqlConnection(_sqlConnectionString);
56 SqlCommand command = new SqlCommand(storedProcedureName,connection);
57 command.CommandType = CommandType.StoredProcedure;
58 if (parameters != null)
59 {
60 foreach (SqlParameter parameter in parameters)
61 {
62 command.Parameters.Add(parameter);
63 }
64 }
65 connection.Open();
66 command.ExecuteNonQuery();
67 connection.Close();
68 }
69
70 /// <summary>
71 /// 执行存储，并返回SqlDataReader
72 /// </summary>
73 /// <param name="storedProcedureName">存储过程名</param>
74 /// <param name="parameters">参数</param>
75 /// <returns>包含查询结果的SqlDataReader</returns>
76 public static SqlDataReader ExecuteProcedureReader(string storedProcedureName,IDataParameter[] parameters)
77 {
78 SqlConnection connection = new SqlConnection(_sqlConnectionString);
79 SqlCommand command = new SqlCommand(storedProcedureName,connection);
80 command.CommandType = CommandType.StoredProcedure;
81 if (parameters != null)
82 {
83 foreach (SqlParameter parameter in parameters)
84 {
85 command.Parameters.Add(parameter);
86 }
87 }
88 connection.Open();
89 SqlDataReader sqlReader = command.ExecuteReader();
90 //connection.Close();
91
92 return sqlReader;
93 }
94 }
95}
//访问存储过程，实现数据库操作、
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.SqlClient;
6using NGuestBook.IDAL;
7using NGuestBook.Entity;
8
9namespace NGuestBook.SQLServerDAL
10{
11 public class AdminDAL : IAdminDAL
12 {
13 /// <summary>
14 /// 插入管理员
15 /// </summary>
16 /// <param name="admin">管理员实体类</param>
17 /// <returns>是否成功</returns>
18 public bool Insert(AdminInfo admin)
19 {
20 SqlParameter[] parameters =
21 {
22 new SqlParameter("@Name",SqlDbType.NVarChar),
23 new SqlParameter("@Password",SqlDbType.NVarChar)
24 };
25 parameters[0].Value = admin.Name;
26 parameters[1].Value = admin.Password;
27 try
28 {
29 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_InsertAdmin", parameters);
30 return true;
31 }
32 catch
33 {
34 return false;
35 }
36 }
37
38 /// <summary>
39 /// 删除管理员
40 /// </summary>
41 /// <param name="id">欲删除的管理员的ID</param>
42 /// <returns>是否成功</returns>
43 public bool Delete(int id)
44 {
45 SqlParameter[] parameters =
46 {
47 new SqlParameter("@ID",SqlDbType.Int)
48 };
49 parameters[0].Value = id;
50 try
51 {
52 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_DeleteAdmin", parameters);
53 return true;
54 }
55 catch
56 {
57 return false;
58 }
59 }
60
61 ///<summary>
62 /// 更新管理员信息
63 /// </summary>
64 /// <param name="admin">管理员实体类</param>
65 /// <returns>是否成功</returns>
66 public bool Update(AdminInfo admin)
67 {
68 SqlParameter[] parameters =
69 {
70 new SqlParameter("@ID",SqlDbType.Int),
71 new SqlParameter("@Name",SqlDbType.NVarChar),
72 new SqlParameter("@Password",SqlDbType.NVarChar)
73 };
74 parameters[0].Value = admin.ID;
75 parameters[1].Value = admin.Name;
76 parameters[2].Value = admin.Password;
77 try
78 {
79 SQLServerDALHelper.ExecuteProcedureNonQurey("Pr_UpdateAdmin", parameters);
80 return true;
81 }
82 catch
83 {
84 return false;
85 }
86 }
87
88 /// <summary>
89 /// 按ID取得管理员信息
90 /// </summary>
91 /// <param name="id">管理员ID</param>
92 /// <returns>管理员实体类</returns>
93 public AdminInfo GetByID(int id)
94 {
95 SqlParameter[] parameters =
96 {
97 new SqlParameter("@ID",SqlDbType.Int)
98 };
99 parameters[0].Value = id;
100 SqlDataReader dataReader = null;
101 try
102 {
103 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByID", parameters);
104 dataReader.Read();
105 AdminInfo admin = new AdminInfo();
106 admin.ID = (int)dataReader["ID"];
107 admin.Name = (string)dataReader["Name"];
108 admin.Password = (string)dataReader["Password"];
109
110 return admin;
111 }
112 catch
113 {
114 return null;
115 }
116 finally
117 {
118 dataReader.Close();
119 }
120 }
121
122 /// <summary>
123 /// 按用户名及密码取得管理员信息
124 /// </summary>
125 /// <param name="name">用户名</param>
126 /// <param name="password">密码</param>
127 /// <returns>管理员实体类，不存在时返回null</returns>
128 public AdminInfo GetByNameAndPassword(string name, string password)
129 {
130 SqlParameter[] parameters =
131 {
132 new SqlParameter("@Name",SqlDbType.NVarChar),
133 new SqlParameter("@Password",SqlDbType.NVarChar)
134 };
135 parameters[0].Value = name;
136 parameters[1].Value = password;
137 SqlDataReader dataReader = null;
138 try
139 {
140 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByNameAndPassword", parameters);
141 dataReader.Read();
142 AdminInfo admin = new AdminInfo();
143 admin.ID = (int)dataReader["ID"];
144 admin.Name = (string)dataReader["Name"];
145 admin.Password = (string)dataReader["Password"];
146
147 return admin;
148 }
149 catch
150 {
151 return null;
152 }
153 finally
154 {
155 dataReader.Close();
156 }
157 }
158
159 /// <summary>
160 /// 按管理员名取得管理员信息
161 /// </summary>
162 /// <param name="name">管理员名</param>
163 /// <returns>管理员实体类</returns>
164 public AdminInfo GetByName(string name)
165 {
166 SqlParameter[] parameters =
167 {
168 new SqlParameter("@Name",SqlDbType.NVarChar)
169 };
170 parameters[0].Value = name;
171 SqlDataReader dataReader = null;
172 try
173 {
174 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAdminByName", parameters);
175 dataReader.Read();
176 AdminInfo admin = new AdminInfo();
177 admin.ID = (int)dataReader["ID"];
178 admin.Name = (string)dataReader["Name"];
179 admin.Password = (string)dataReader["Password"];
180
181 return admin;
182 }
183 catch
184 {
185 return null;
186 }
187 finally
188 {
189 dataReader.Close();
190 }
191 }
192
193 /// <summary>
194 /// 取得全部管理员信息
195 /// </summary>
196 /// <returns>管理员实体类集合</returns>
197 public IList<AdminInfo> GetAll()
198 {
199 SqlDataReader dataReader = null;
200 try
201 {
202 dataReader = SQLServerDALHelper.ExecuteProcedureReader("GetAllAdmin", null);
203 IList<AdminInfo> adminCollection=new List<AdminInfo>();
204 while (dataReader.Read())
205 {
206 AdminInfo admin = new AdminInfo();
207 admin.ID = (int)dataReader["ID"];
208 admin.Name = (string)dataReader["Name"];
209 admin.Password = (string)dataReader["Password"];
210 adminCollection.Add(admin);
211 }
212
213 return adminCollection;
214 }
215 catch
216 {
217 return null;
218 }
219 finally
220 {
221 dataReader.Close();
222 }
223 }
224 }
225}
///直接访问数据库实体
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.OleDb;
6using NGuestBook.IDAL;
7using NGuestBook.Entity;
8
9namespace NGuestBook.AccessDAL
10{
11 public class AdminDAL : IAdminDAL
12 {
13 /// <summary>
14 /// 插入管理员
15 /// </summary>
16 /// <param name="admin">管理员实体类</param>
17 /// <returns>是否成功</returns>
18 public bool Insert(AdminInfo admin)
19 {
20 string SQLCommand = "insert into [TAdmin]([Name],[Password]) values(@name,@password)";
21 OleDbParameter[] parameters ={
22 new OleDbParameter("name",admin.Name),
23 new OleDbParameter("password",admin.Password)
24 };
25
26 try
27 {
28 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
29 return true;
30 }
31 catch
32 {
33 return false;
34 }
35 }
36
37 /// <summary>
38 /// 删除管理员
39 /// </summary>
40 /// <param name="id">欲删除的管理员的ID</param>
41 /// <returns>是否成功</returns>
42 public bool Delete(int id)
43 {
44 string SQLCommand = "delete from [TAdmin] where [ID]=@id";
45 OleDbParameter[] parameters ={
46 new OleDbParameter("id",id)
47 };
48
49 try
50 {
51 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
52 return true;
53 }
54 catch
55 {
56 return false;
57 }
58 }
59
60 /// <summary>
61 /// 更新管理员信息
62 /// </summary>
63 /// <param name="admin">管理员实体类</param>
64 /// <returns>是否成功</returns>
65 public bool Update(AdminInfo admin)
66 {
67 string SQLCommand = "update [TAdmin] set [Name]=@name,[Password]=@password where [ID]=@id";
68 OleDbParameter[] parameters ={
69 new OleDbParameter("id",admin.ID),
70 new OleDbParameter("name",admin.Name),
71 new OleDbParameter("password",admin.Password)
72 };
73
74 try
75 {
76 AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
77 return true;
78 }
79 catch
80 {
81 return false;
82 }
83 }
84
85 /// <summary>
86 /// 按ID取得管理员信息
87 /// </summary>
88 /// <param name="id">管理员ID</param>
89 /// <returns>管理员实体类</returns>
90 public AdminInfo GetByID(int id)
91 {
92 string SQLCommand = "select * from [TAdmin] where [ID]=@id";
93 OleDbParameter[] parameters ={
94 new OleDbParameter("id",id)
95 };
96
97 try
98 {
99 OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
100 if (!dataReader.HasRows)
101 {
102 throw new Exception();
103 }
104
105 AdminInfo admin = new AdminInfo();
106 dataReader.Read();
107 admin.ID=(int)dataReader["ID"];
108 admin.Name=(string)dataReader["Name"];
109 admin.Password=(string)dataReader["Password"];
110
111 return admin;
112 }
113 catch
114 {
115 return null;
116 }
117 }
118
119 /// <summary>
120 /// 按用户名及密码取得管理员信息
121 /// </summary>
122 /// <param name="name">用户名</param>
123 /// <param name="password">密码</param>
124 /// <returns>管理员实体类，不存在时返回null</returns>
125 public AdminInfo GetByNameAndPassword(string name, string password)
126 {
127 string SQLCommand = "select * from [TAdmin] where [Name]=@name and [Password]=@password";
128 OleDbParameter[] parameters ={
129 new OleDbParameter("name",name),
130 new OleDbParameter("password",password),
131 };
132
133 try
134 {
135 OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
136 if (!dataReader.HasRows)
137 {
138 throw new Exception();
139 }
140
141 AdminInfo admin = new AdminInfo();
142 dataReader.Read();
143 admin.ID = (int)dataReader["ID"];
144 admin.Name = (string)dataReader["Name"];
145 admin.Password = (string)dataReader["Password"];
146
147 return admin;
148 }
149 catch
150 {
151 return null;
152 }
153 }
154
155 /// <summary>
156 /// 按管理员名取得管理员信息
157 /// </summary>
158 /// <param name="name">管理员名</param>
159 /// <returns>管理员实体类</returns>
160 public AdminInfo GetByName(string name)
161 {
162 string SQLCommand = "select * from [TAdmin] where [Name]=@name";
163 OleDbParameter[] parameters ={
164 new OleDbParameter("name",name),
165 };
166
167 try
168 {
169 OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
170 if (!dataReader.HasRows)
171 {
172 throw new Exception();
173 }
174
175 AdminInfo admin = new AdminInfo();
176 dataReader.Read();
177 admin.ID = (int)dataReader["ID"];
178 admin.Name = (string)dataReader["Name"];
179 admin.Password = (string)dataReader["Password"];
180
181 return admin;
182 }
183 catch
184 {
185 return null;
186 }
187 }
188
189 /// <summary>
190 /// 取得全部管理员信息
191 /// </summary>
192 /// <returns>管理员实体类集合</returns>
193 public IList<AdminInfo> GetAll()
194 {
195 string SQLCommand = "select * from [TAdmin]";
196 try
197 {
198 OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, null);
199 if (!dataReader.HasRows)
200 {
201 throw new Exception();
202 }
203
204 IList<AdminInfo> adminCollection = new List<AdminInfo>();
205 int i = 0;
206 while (dataReader.Read())
207 {
208 AdminInfo admin = new AdminInfo();
209 admin.ID = (int)dataReader["ID"];
210 admin.Name = (string)dataReader["Name"];
211 admin.Password = (string)dataReader["Password"];
212
213 adminCollection.Add(admin);
214 i++;
215 }
216
217 return adminCollection;
218 }
219 catch
220 {
221 return null;
222 }
223 }
224 }
225}
