应用抽象工厂+反射实现通用数据源的设计（一）

应用抽象工厂+反射实现通用数据源的设计（一）

上一篇博客提到了.net的架构模式，主要写了普通的实现和OOP思想的三层模式，当然有一定的弊端，这篇博文主要是通过对学生表的操作实现抽象工厂+反射通用数据源的设计。

一：Model层对实体类的封装：

public int Sid { get; set; } public string Sname { get; set; } public string StuSex { get; set; } public string StuAdd { get; set; }

二：DBLibrary 访问数据库的两个类进行分装：SqlServer和Access

1.SQLHelper：

private static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"].ToString(); //SqlParameter[] 方便传递数组 ///

/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回受影响的行数 public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams) { SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand comm = new SqlCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { return comm.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } } ///

/// 封装Command对象的ExecuteReader 方法用于数据的查询 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回SqlDataReader对象 public static SqlDataReader ExcuteReader(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams) { SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand comm = new SqlCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { //自动关闭 return comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (SqlException ex) { throw ex; } } ///

/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回的是object单一的值 public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams) { SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand comm = new SqlCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { return comm.ExecuteScalar(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } } ///

/// 主要用于返回DataTable 查询的数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回DataTable对象 public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParams) { SqlConnection conn = new SqlConnection(); SqlCommand comm = new SqlCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = comm; DataSet ds = new DataSet(); try { //自动打开自动关闭 实现断开式的链接 da.Fill(ds); return ds.Tables[0]; } catch (SqlException ex) { throw ex; } finally { conn.Close(); } } ///

/// 主要用于给Command对象进行初始化赋值工作 ///

/// 是操作的Comman对象 /// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters private static void PrepareCommand(SqlCommand comm, SqlConnection conn, string cmdText, CommandType cmdType, SqlParameter[] cmdParams) { if (conn.State == ConnectionState.Closed) conn.Open(); comm.Connection = conn; comm.CommandText = cmdText; comm.CommandType = cmdType; if (cmdParams != null) { for (int i = 0; i < cmdParams.Length; i++) { comm.Parameters.Add(cmdParams[i]); } } }

2.AccessHelper

public class AccessHelper { private static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"].ToString(); //SqlParameter[] 方便传递数组 ///

/// 主要用于封装Command对象的ExecuteNonQuery方法,用于数据的增删改 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回受影响的行数 public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams) { OleDbConnection conn = new OleDbConnection(ConnectionString); OleDbCommand comm = new OleDbCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { return comm.ExecuteNonQuery(); } catch (OleDbException ex) { throw ex; } finally { conn.Close(); } } ///

/// 封装Command对象的ExecuteReader 方法用于数据的查询 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回SqlDataReader对象 public static OleDbDataReader ExcuteReader(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams) { OleDbConnection conn = new OleDbConnection(ConnectionString); OleDbCommand comm = new OleDbCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { //自动关闭 return comm.ExecuteReader(CommandBehavior.CloseConnection); } catch (OleDbException ex) { throw ex; } } ///

/// 封装Commond对象的ExecuteScalar方法，用于返回首行首列数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回的是object单一的值 public static object ExecuteScalar(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams) { OleDbConnection conn = new OleDbConnection(ConnectionString); OleDbCommand comm = new OleDbCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); try { return comm.ExecuteScalar(); } catch (OleDbException ex) { throw ex; } finally { conn.Close(); } } ///

/// 主要用于返回DataTable 查询的数据 ///

/// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters /// 返回DataTable对象 public static DataTable GetDataTable(string cmdText, CommandType cmdType, params OleDbParameter[] cmdParams) { OleDbConnection conn = new OleDbConnection(ConnectionString); OleDbCommand comm = new OleDbCommand(); PrepareCommand(comm, conn, cmdText, cmdType, cmdParams); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = comm; DataSet ds = new DataSet(); try { //自动打开自动关闭 实现断开式的链接 da.Fill(ds); return ds.Tables[0]; } catch (OleDbException ex) { throw ex; } finally { conn.Close(); } } ///

/// 主要用于给Command对象进行初始化赋值工作 ///

/// 是操作的Comman对象 /// Connection对象 /// Command.CommandText /// Command.CommandType /// Command.Parameters private static void PrepareCommand(OleDbCommand comm, OleDbConnection conn, string cmdText, CommandType cmdType, OleDbParameter[] cmdParams) { if (conn.State == ConnectionState.Closed) conn.Open(); comm.Connection = conn; comm.CommandText = cmdText; comm.CommandType = cmdType; if (cmdParams != null) { for (int i = 0; i < cmdParams.Length; i++) { comm.Parameters.Add(cmdParams[i]); } } } }

三：定义一个学生表的接口（引用model实体层）：

public interface IStudent { IList GetAllStudents(); Student GetStudent(int stuno); IList GetStudentByName(string stuname); int AddStudnet(Student student); int ModifyStudent(Student student); int DelStudetnt(int stuno); }

四：1.让SqlServerDal中实现StudentServer学生表接口中的方法（引用Model和DBLibrary，修改调用的存储过程）：

public class StudentServer:IStudent { private IList GetStudentBySQL(string strsql,params SqlParameter[] cmdParams) { IList list = new List(); using (SqlDataReader dr = SQLHelper.ExcuteReader(strsql, CommandType.Text, cmdParams)) { while (dr.Read()) { Student student = new Student(); student.Sid = dr.GetInt32(0); student.Sname = dr.GetString(1); student.StuSex = dr.GetString(2); student.StuAdd = dr.GetString(3); list.Add(student); } return list; } } ///

/// 得到所有的学生信息 ///

/// public IList GetAllStudents() { string strsql = "select * from Student"; return this.GetStudentBySQL(strsql,null); } ///

/// 根据主建ID查询学生 ///

/// /// public Student GetStudent(int stuno) { string strsql = "select * from Student where Sid=@stuno"; SqlParameter param_id = new SqlParameter(); param_id.ParameterName = "@stuno"; param_id.SqlDbType = SqlDbType.Int; param_id.Value = stuno; IList students = this.GetStudentBySQL(strsql, param_id); if (students != null && students.Count > 0) { return students[0]; } else { return null; } } ///

/// 根据学生的姓名进行查询 ///

/// /// public IList GetStudentByName(string stuname) { string paramStuName=string.Format("%{0}%",stuname); string strsql = "select * from Student where like @stuname"; SqlParameter param_name = new SqlParameter(); param_name.ParameterName = stuname; param_name.SqlDbType = SqlDbType.VarChar; //与数据库对应 param_name.Size = 50; param_name.Value = paramStuName; return this.GetStudentBySQL(strsql, param_name); } ///

/// 添加一个学生 ///

/// /// public int AddStudnet(Student student) { string strsql = @"INSERT INTO [dbo].[Student] ([StuName] ,[StuSex] ,[StuAddr]) VALUES (@stuName ,@stuSex, ,@stuAddr)"; SqlParameter param_name = new SqlParameter("@stuName",SqlDbType.VarChar,50); param_name.Value = student.Sname; SqlParameter param_sex = new SqlParameter("@stuSex", SqlDbType.VarChar, 2); param_name.Value = student.StuSex; SqlParameter param_addr = new SqlParameter("@stuAddr", SqlDbType.VarChar, 100); param_name.Value = student.StuAdd; return SQLHelper.ExecuteNonQuery(strsql, CommandType.Text, param_name, param_sex, param_addr); } ///

/// 修改一个学生 ///

/// /// 代表受影响的行数 public int ModifyStudent(Student student) { string strsql = "pro_ModifyStudent"; SqlParameter param_id = new SqlParameter("@@stuno", SqlDbType.Int); param_id.Value = student.Sid; //默认为Input 输入类型 param_id.Direction = ParameterDirection.Input; SqlParameter param_name = new SqlParameter("@stuName", SqlDbType.VarChar, 50); param_name.Value = student.Sname; param_name.Direction = ParameterDirection.Input; SqlParameter param_sex = new SqlParameter("@stuSex", SqlDbType.VarChar, 2); param_sex.Value = student.StuSex; param_sex.Direction = ParameterDirection.Input; SqlParameter param_addr = new SqlParameter("@stuAddr", SqlDbType.VarChar, 100); param_addr.Value = student.StuAdd; param_addr.Direction = ParameterDirection.Input; SqlParameter param_return = new SqlParameter("@returnValueParams", SqlDbType.Int); //存储过程返回值的类型 param_return.Direction = ParameterDirection.ReturnValue; SQLHelper.ExecuteNonQuery(strsql, CommandType.StoredProcedure, param_id, param_name, param_sex, param_addr, param_return); return (int)param_return.Value; } ///

/// 删除一个学生 ///

/// /// public int DelStudetnt(int stuno) { string strsql = "delete from Student where stuno=@stuno"; SqlParameter param_id = new SqlParameter(); param_id.ParameterName = "@stuno"; param_id.SqlDbType = SqlDbType.Int; param_id.Value = stuno; return SQLHelper.ExecuteNonQuery(strsql, CommandType.Text, param_id); }}

2.AccessDal中实现StudentServer学生表接口中的方法（引用Model和DBLibrary，Access数据库中没有int类型 为TinyInt）

public class StudentServer : IStudent { private IList GetStudentBySQL(string strsql, params OleDbParameter[] cmdParams) { IList list = new List(); using (OleDbDataReader dr = AccessHelper.ExcuteReader(strsql, CommandType.Text, cmdParams)) { while (dr.Read()) { Student student = new Student(); student.Sid = dr.GetInt32(0); student.Sname = dr.GetString(1); student.StuSex = dr.GetString(2); student.StuAdd = dr.GetString(3); list.Add(student); } return list; } } ///

/// 得到所有的学生信息 ///

/// public IList GetAllStudents() { string strsql = "select * from Student"; return this.GetStudentBySQL(strsql, null); } ///

/// 根据主建ID查询学生 ///

/// /// public Student GetStudent(int stuno) { string strsql = "select * from Student where Sid=@stuno"; OleDbParameter param_id = new OleDbParameter(); param_id.ParameterName = "@stuno"; param_id.OleDbType = OleDbType.Integer; param_id.Value = stuno; IList students = this.GetStudentBySQL(strsql, param_id); if (students != null && students.Count > 0) { return students[0]; } else { return null; } } ///

/// 根据学生的姓名进行查询 ///

/// /// public IList GetStudentByName(string stuname) { string paramStuName = string.Format("%{0}%", stuname); string strsql = "select * from Student where like @stuname"; OleDbParameter param_name = new OleDbParameter(); param_name.ParameterName = stuname; param_name.OleDbType = OleDbType.VarChar; //与数据库对应 param_name.Size = 50; param_name.Value = paramStuName; return this.GetStudentBySQL(strsql, param_name); } ///

/// 添加一个学生 ///

/// /// public int AddStudnet(Student student) { string strsql = @"INSERT INTO [dbo].[Student] ([StuName] ,[StuSex] ,[StuAddr]) VALUES (@stuName ,@stuSex, ,@stuAddr)"; OleDbParameter param_name = new OleDbParameter("@stuName", OleDbType.VarChar, 50); param_name.Value = student.Sname; OleDbParameter param_sex = new OleDbParameter("@stuSex", OleDbType.VarChar, 2); param_name.Value = student.StuSex; OleDbParameter param_addr = new OleDbParameter("@stuAddr", OleDbType.VarChar, 100); param_name.Value = student.StuAdd; return AccessHelper.ExecuteNonQuery(strsql, CommandType.Text, param_name, param_sex, param_addr); } ///

/// 修改一个学生 ///

/// /// 代表受影响的行数 public int ModifyStudent(Student student) { string strsql = @"UPDATE [dbo].[Student] SET [StuName]=@stuName ,[StuSex]=@stuSex ,[StuAddr]=@stuAddr WHERE StuNo=@stuno"; OleDbParameter param_id = new OleDbParameter("@@stuno", OleDbType.TinyInt); param_id.Value = student.Sid; //默认为Input 输入类型 param_id.Direction = ParameterDirection.Input; OleDbParameter param_name = new OleDbParameter("@stuName", OleDbType.VarChar, 50); param_name.Value = student.Sname; param_name.Direction = ParameterDirection.Input; OleDbParameter param_sex = new OleDbParameter("@stuSex", OleDbType.VarChar, 2); param_sex.Value = student.StuSex; param_sex.Direction = ParameterDirection.Input; OleDbParameter param_addr = new OleDbParameter("@stuAddr", OleDbType.VarChar, 100); param_addr.Value = student.StuAdd; param_addr.Direction = ParameterDirection.Input; return AccessHelper.ExecuteNonQuery(strsql, CommandType.StoredProcedure, param_id, param_name, param_sex, param_addr); } ///

/// 删除一个学生 ///

/// /// public int DelStudetnt(int stuno) { string strsql = "delete from Student where stuno=@stuno"; OleDbParameter param_id = new OleDbParameter(); param_id.ParameterName = "@stuno"; param_id.OleDbType = OleDbType.Integer; param_id.Value = stuno; return AccessHelper.ExecuteNonQuery(strsql, CommandType.Text, param_id); } }

注意：Access中引用的是System.Data.OleDb;的命名空间

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。