.net简单的三层实现功能
在网上看到别人的代码,写下来留着看,欢迎各位指教,有没有工厂模式的学习资料啊,留下给小弟学习学习.
首先写个Model类,实现业务实体,我的比较简单,只有三个字段
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public class LoginModel 2 { 3 #region 4 5 private int _AdminId; 6 private string _AdminUID; 7 private string _AdminPWD; 8 9 public string AdminPWD 10 { 11 get { return _AdminPWD; } 12 set { _AdminPWD = value; } 13 } 14 15 public int AdminId 16 { 17 get { return _AdminId; } 18 set { _AdminId = value; } 19 } 20 21 22 public string AdminUID 23 { 24 get { return _AdminUID; } 25 set { _AdminUID = value; } 26 } 27 28 #endregion 29 }
2.创建一个DBHelp类,实现SQLServer数据库访问
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public class DBHelp 2 { 3 private static readonly string connectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString.ToString(); 4 private static readonly string providerName = "System.Data.SqlClient"; 5 6 /// <summary> 7 /// GetConnection 用于获取连接数据库的 connection 对象 8 /// </summary> 9 /// <returns></returns> 10 private static DbConnection GetConnection() 11 { 12 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 13 DbConnection connection = _factory.CreateConnection(); 14 connection.ConnectionString = connectionString; 15 return connection; 16 } 17 18 /// <summary> 19 /// GetCommand 获取命令参数 command 对象 20 /// </summary> 21 /// <param name="commandText"></param> 22 /// <param name="commandType"></param> 23 /// <param name="connection"></param> 24 /// <returns></returns> 25 private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection) 26 { 27 DbCommand command = connection.CreateCommand(); 28 command.CommandText = commandText; 29 command.CommandType = commandType; 30 return command; 31 } 32 33 /// <summary> 34 /// GetCommand 方法重载 35 /// </summary> 36 /// <param name="commandText">sql语句</param> 37 /// <param name="connection"></param> 38 /// <returns></returns> 39 private static DbCommand GetCommand(string commandText, DbConnection connection) 40 { 41 DbCommand command = connection.CreateCommand(); 42 command.CommandText = commandText; 43 command.CommandType = CommandType.Text; 44 return command; 45 } 46 47 /// <summary> 48 /// GetParameter 用于为命令设置参数 49 /// </summary> 50 /// <param name="paramName"></param> 51 /// <param name="paramValue"></param> 52 /// <param name="command"></param> 53 /// <returns></returns> 54 private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command) 55 { 56 DbParameter parameter = command.CreateParameter(); 57 parameter.ParameterName = paramName; 58 parameter.Value = paramValue; 59 return parameter; 60 } 61 62 /// <summary> 63 /// 执行无参的存储过程 64 /// </summary> 65 /// <param name="sqlCommand">存储过程名</param> 66 /// <returns></returns> 67 public static int ExecuteNonQueryProc(string sqlCommand) 68 { 69 int result = 0; 70 using (DbConnection connection = GetConnection()) 71 { 72 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 73 connection.Open(); 74 result = command.ExecuteNonQuery(); 75 command.Parameters.Clear(); 76 } 77 return result; 78 } 79 80 /// <summary> 81 /// 执行无返回值有参数的存储过程 82 /// </summary> 83 /// <param name="sqlCommand">存储过程名</param> 84 /// <param name="parameters">参数</param> 85 /// <returns></returns> 86 public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<string, object> parameters) 87 { 88 int result = 0; 89 using (DbConnection connection = GetConnection()) 90 { 91 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 92 foreach (KeyValuePair<string, object> p in parameters) 93 { 94 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 95 } 96 connection.Open(); 97 result = command.ExecuteNonQuery(); 98 command.Parameters.Clear(); 99 } 100 return result; 101 } 102 103 104 /// <summary> 105 /// 执行无返回值的sql语句 106 /// </summary> 107 /// <param name="sqlCommand"></param> 108 /// <param name="parameters"></param> 109 public static int ExecuteNonQuery(string sqlCommand) 110 { 111 int result = 0; 112 using (DbConnection connection = GetConnection()) 113 { 114 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 115 connection.Open(); 116 result = command.ExecuteNonQuery(); 117 command.Parameters.Clear(); 118 return result; 119 } 120 } 121 122 /// <summary> 123 /// 执行有参数的sql语句 124 /// </summary> 125 /// <param name="sqlCommand"></param> 126 /// <param name="para"></param> 127 /// <returns></returns> 128 public static int ExecuteNonQuery(string sqlCommand, Dictionary<string, object> para) 129 { 130 int result = 0; 131 using (DbConnection connection = GetConnection()) 132 { 133 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 134 foreach (KeyValuePair<string, object> p in para) 135 { 136 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 137 } 138 connection.Open(); 139 result = command.ExecuteNonQuery(); 140 command.Parameters.Clear(); 141 return result; 142 } 143 } 144 145 /// <summary> 146 /// 执行有返回值无参数的存储过程 147 /// </summary> 148 /// <param name="cmdText"></param> 149 /// <returns></returns> 150 public static object ExecuteScalarProc(string cmdText) 151 { 152 using (DbConnection connection = GetConnection()) 153 { 154 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection); 155 connection.Open(); 156 object val = command.ExecuteScalar(); 157 command.Parameters.Clear(); 158 return val; 159 } 160 } 161 162 /// <summary> 163 /// 执行有返回值的有参数的存储过程 164 /// </summary> 165 /// <param name="cmdText">存储过程名</param> 166 /// <param name="para">参数</param> 167 /// <returns></returns> 168 public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para) 169 { 170 using (DbConnection connection = GetConnection()) 171 { 172 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection); 173 foreach (KeyValuePair<string, object> p in para) 174 { 175 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 176 } 177 connection.Open(); 178 object val = command.ExecuteScalar(); 179 command.Parameters.Clear(); 180 return val; 181 } 182 } 183 184 /// <summary> 185 /// 执行有返回值的sql语句 186 /// </summary> 187 /// <param name="cmdText"></param> 188 /// <returns></returns> 189 public static object ExecuteScalar(string cmdText) 190 { 191 using (DbConnection connection = GetConnection()) 192 { 193 DbCommand command = GetCommand(cmdText, CommandType.Text, connection); 194 connection.Open(); 195 object val = command.ExecuteScalar(); 196 command.Parameters.Clear(); 197 return val; 198 } 199 } 200 201 /// <summary> 202 /// 执行有返回值有参数的sql语句 203 /// </summary> 204 /// <param name="cmdText"></param> 205 /// <param name="para"></param> 206 /// <returns></returns> 207 public static object ExecuteScalar(string cmdText, Dictionary<string, object> para) 208 { 209 using (DbConnection connection = GetConnection()) 210 { 211 DbCommand command = GetCommand(cmdText, CommandType.Text, connection); 212 foreach (KeyValuePair<string, object> p in para) 213 { 214 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 215 } 216 connection.Open(); 217 object val = command.ExecuteScalar(); 218 command.Parameters.Clear(); 219 return val; 220 } 221 222 } 223 224 /// <summary> 225 /// 执行无参数的存储过程,返回DbDataReader对象 226 /// </summary> 227 /// <param name="sqlCommand">存储过程名</param> 228 /// <returns></returns> 229 public static DbDataReader GetReaderProc(string sqlCommand) 230 { 231 232 try 233 { 234 235 DbConnection connection = GetConnection(); 236 237 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 238 239 connection.Open(); 240 241 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 242 243 return reader; 244 245 } 246 247 catch (Exception ex) 248 { 249 250 Console.Write("" + ex.Message); 251 252 return null; 253 254 } 255 256 } 257 258 /// <summary> 259 /// 执行有参数的存储过程,返回DbDataReader对象 260 /// </summary> 261 /// <param name="sqlCommand"></param> 262 /// <param name="parameters"></param> 263 /// <returns></returns> 264 public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters) 265 { 266 267 try 268 { 269 270 DbConnection connection = GetConnection(); 271 272 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection); 273 274 foreach (KeyValuePair<string, object> p in parameters) 275 { 276 277 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 278 279 } 280 281 connection.Open(); 282 283 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 284 285 command.Parameters.Clear(); 286 287 return reader; 288 289 } 290 291 catch 292 { 293 294 return null; 295 296 } 297 298 } 299 #region 300 301 /// <summary> 302 /// 执行无参数的sql语句,返回DbDataReader对象 303 /// </summary> 304 /// <param name="sqlCommand"></param> 305 /// <returns></returns> 306 public static DbDataReader GetReader(string sqlCommand) 307 { 308 309 try 310 { 311 312 DbConnection connection = GetConnection(); 313 314 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 315 316 connection.Open(); 317 318 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 319 320 command.Parameters.Clear(); 321 322 return reader; 323 324 } 325 326 catch (Exception ex) 327 { 328 329 Console.Write("" + ex.Message); 330 331 return null; 332 333 } 334 335 } 336 337 #endregion 338 /// <summary> 339 /// 执行有参数的sql语句,返回DbDataReader对象 340 /// </summary> 341 /// <param name="sqlCommand"></param> 342 /// <param name="parameters"></param> 343 /// <returns></returns> 344 public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> parameters) 345 { 346 try 347 { 348 DbConnection connection = GetConnection(); 349 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection); 350 foreach (KeyValuePair<string, object> p in parameters) 351 { 352 command.Parameters.Add(GetParameter(p.Key, p.Value, command)); 353 } 354 connection.Open(); 355 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 356 command.Parameters.Clear(); 357 return reader; 358 } 359 catch (Exception ex) 360 { 361 Console.Write("" + ex.Message); 362 return null; 363 } 364 } 365 366 /// <summary> 367 /// 返回DataTable对象 368 /// </summary> 369 /// <param name="safeSql"></param> 370 /// <returns></returns> 371 public static DataTable GetDataSet(string safeSql) 372 { 373 /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 374 DbConnection connection = GetConnection(); 375 connection.Open(); 376 DbDataAdapter da = _factory.CreateDataAdapter(); 377 da.SelectCommand = connection.CreateCommand(); 378 da.SelectCommand.CommandText = safeSql; 379 DataTable dt = new DataTable(); 380 da.Fill(dt); 381 return dt;*/ 382 383 using (DbConnection connection = GetConnection()) 384 { 385 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName); 386 DbCommand command = GetCommand(safeSql, CommandType.Text, connection); 387 connection.Open(); 388 DbDataAdapter da = _factory.CreateDataAdapter(); 389 da.SelectCommand = command; 390 DataTable datatable = new DataTable(); 391 da.Fill(datatable); 392 return datatable; 393 } 394 } 395 }
3.创建DAL实现要实现的 接口,主要是对原始数据(数据库或者文本文件等存放数据的形式)的操作层,而不是指原始数据,也就是说,是对数据的操作,而不是数据库,具体为业务逻辑层或表示层提供数据服务
引用Model
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 //增 2 public static bool Add(Model.LoginModel user) 3 { 4 string sql = "insert into admin(AdminUID,AdminPWD)values(@AdminUID,@AdminPWD)"; 5 Dictionary<string, object> dic = new Dictionary<string, object>(); 6 dic.Add("@AdminUID",user.AdminUID); 7 dic.Add("@AdminPWD", user.AdminPWD); 8 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false; 9 } 10 //删 11 public static bool Delete(int UserID) 12 { 13 string sql = "delete from admin where AdminID=@AdminID"; 14 Dictionary<string, object> dic = new Dictionary<string, object>(); 15 dic.Add("AdminID",UserID); 16 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false; 17 } 18 //改 19 public static bool Modelfy(Model.LoginModel user) 20 { 21 string sql = "update admin set AdminUID=@AdminUID and AdminPWD=@AdminPWD "; 22 Dictionary<string, object> dic = new Dictionary<string, object>(); 23 dic.Add("AdminUID", user.AdminUID); 24 dic.Add("AdminPWD", user.AdminPWD); 25 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false; 26 } 27 //查 28 public static List<LoginModel> GetAllUser() 29 { 30 string sql="select AdminUID,AdminPWD from admin"; 31 DataTable dt = DBHelp.GetDataSet(sql); 32 List<LoginModel>list=new List<LoginModel>(); 33 foreach (DataRow dr in dt.Rows) 34 { 35 LoginModel login = new LoginModel(); 36 login.AdminUID = dr["AdminUID"].ToString(); 37 login.AdminPWD = dr["AdminPWD"].ToString(); 38 list.Add(login); 39 } 40 return list; 41 }
4.创建BLL层,主要是针对具体的问题的操作,也可以理解成对数据层的操作,对数据业务逻辑处理
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static bool Add(LoginModel user) 2 { 3 return DAL.LoginDAL.Add(user); 4 } 5 public static bool Delete(int UserID) 6 { 7 return DAL.LoginDAL.Delete(UserID); 8 } 9 public static bool Modify(LoginModel user) 10 { 11 return DAL.LoginDAL.Modelfy(user); 12 } 13 public static List<LoginModel> GetAllUsers() 14 { 15 return DAL.LoginDAL.GetAllUser(); 16 }
5.显示数据到页面,后台CS代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public SqlCommand com; 2 3 protected void Page_Load(object sender, EventArgs e) 4 { 5 SqlConnection sql = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=ThreeLayer"); 6 string selSQL = "select AdminUID,AdminPWD,AdminId ,isExits from admin"; 7 sql.Open(); 8 com = new SqlCommand(selSQL, sql); 9 10 }
6.前台代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <tr> 2 <td>用户名</td> 3 <td>密码</td> 4 <td colspan="2">操作</td> 5 </tr> 6 <%System.Data.SqlClient.SqlDataReader dr; 7 dr = com.ExecuteReader(); 8 while (dr.Read()) 9 { %> 10 `<tr> 11 <td><%=dr.GetString(0) %></td> 12 <td><%=dr.GetString(1) %></td> 13 <td><a href="Edit.aspx?id=<%=dr.GetInt32(2) %>">编辑</a></td> 14 <td><a href="Delete.aspx?id=<%=dr.GetInt32(2) %>">删除</a></td> 15 </tr> 16 <%} %>
7.就实现一个删除吧,其他的实现方法是一样的,不过这个删除前台没有代码,点击显示页面的删除跳转到这个页面,删除以后再跳转会显示页面
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 protected void Page_Load(object sender, EventArgs e) 2 { 3 int UserID = Convert.ToInt32(Request.QueryString["id"].ToString()); 4 bool delete = BLL.LoginBLL.Delete(UserID); 5 if (delete == true) 6 { 7 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('删除成功')</script>"); 8 } 9 else if (delete == false) 10 { 11 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('删除失败')</script>"); 12 } 13 Response.Redirect("Show.aspx"); 14 }