.NET访问数据库经典架构（运用反射给实体赋值）

.NET访问数据库经典架构

1、查询接口IQueryPolice

     QueryOne //查询一条记录

     QueryMany//查询多条记录

     QueryImg//查询图片

     QueryOneStrField//查询单个字段

2、运用反射给实体赋值

   运用反射给实体赋值，减少了大量的重复代码，T是对应的是数据库字段（全用属性表示），SetPropertyValue 设置其值。

   实体的赋值过程(不用实体的免看).

   Eg: XXX.xxx=XXX

代码如下：

using System; using System.Collections.Generic; using SNBC.CommonObject; using System.Diagnostics; using SNBC.Domain; using System.Reflection; using System.Data.OracleClient; using System.Data; using System.Drawing; using System.IO; using System.Threading; namespace SNBC.DAO { /// <summary> /// 数据库连接字符串基类，继承此类可以使用数据库连接字符串source /// </summary> public abstract class DAOBase { /// <summary> /// 数据库连接字符串 /// </summary> public virtual string source { get; set; } } /// <summary> /// 我们自己的数据库连接字符串类 /// </summary> public class DAOSelf : DAOBase { string m_source; /// <summary> /// 获得我们自己的数据库连接字符串 /// </summary> public override string source { get { m_source = DBConfig.GetConnectString(); return m_source; } set { m_source = value; } } } /// <summary> /// 交警的数据库连接字符串类 /// </summary> public class DAOPolice : DAOBase { string m_source; /// <summary> /// 获得交警的数据库连接字符串 /// </summary> public override string source { get { m_source = DBConfig.GetPoliceConnectString(); return m_source; } set { m_source = value; } } } /// <summary> /// 天九的数据库连接字符串类 /// </summary> public class DAOTJ : DAOBase { string m_source; /// <summary> /// 获得天九的数据库连接字符串 /// </summary> public new string source { get { m_source = DBConfig.GetTJConnectString(); return m_source; } set { m_source = value; } } } /// <summary> /// 范型单例类，使用此类可以创建单例模式的对象 /// </summary> /// <typeparam name="T">要创建的单例模式的类型</typeparam> public class Singleton<T> where T : new() { /// <summary> /// 构造函数，只有子类可以调用。外部无法调用 /// </summary> protected Singleton() { Debug.Assert(null == instance); } /// <summary> /// T类型对象的实例 /// </summary> protected static T instance = new T(); /// <summary> /// 用来返回T类型对象的实例 /// </summary> public static T Instance { get { return instance; } } } /// <summary> /// 数据库查询基类 /// </summary> /// <typeparam name="T">查询返回的对象，T类型的对象必须继承于DomainBase，并且有默认的构造函数</typeparam> public class QueryBase<T> : DAOBase where T : DomainBase, new() { /// <summary> /// T的类型 /// </summary> protected Type type; /// <summary> /// 用来存放图片查询的返回值 /// </summary> protected List<byte[]> imgs; /// <summary> /// 构造函数，取得T类型的Type /// </summary> public QueryBase() { type = typeof(T); } /// <summary> /// 根据SQL语句查询多条记录 /// </summary> /// <param name="sql">要查询的SQL语句</param> /// <returns>根据SQL语句查询出的T类型的对象的列表</returns> public List<T> QueryMany(string sql) { Logger.Log.Debug("start QueryMany"); Logger.Log.Info(sql); List<T> lst = new List<T>(); OracleDataReader reader = null; try { var ps = type.GetProperties(); reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); while (reader.Read()) { T t = new T(); foreach (var p in ps) { if(!String.IsNullOrEmpty(t.GetFieldName(p.Name))) { var obj = reader[t.GetFieldName(p.Name)]; SetPropertyValue(p, t, obj); } } lst.Add(t); } return lst; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询多条记录错误！"); } finally { if (reader != null) reader.Close(); } } /// <summary> /// 设置对象的属性的值 /// </summary> /// <param name="p">对象的属性</param> /// <param name="t">T类型的对象</param> /// <param name="obj">从数据库取出的值，转换为object的对象</param> protected void SetPropertyValue(PropertyInfo p, object t, object obj) { try { //Logger.Log.Info("字段:" + p.Name + " 字段类型：" + p.PropertyType.Name); if (p.Name == "details") { return; } switch (p.PropertyType.Name) { case "Int32": var ivalue = Int32.Parse(obj.ToString()); p.SetValue(t, ivalue, null); break; case "String": p.SetValue(t, obj.ToString(), null); break; case "Decimal": var dvalue = decimal.Parse(obj.ToString()); p.SetValue(t, dvalue, null); break; case "Boolean": var bvalue = (obj.ToString() == "0") ? false : true; p.SetValue(t, bvalue, null); break; case "DateTime": var tvalue = DateTime.Parse(obj.ToString()); p.SetValue(t, tvalue, null); break; case "List`1": var tmp = p.PropertyType.GetGenericArguments()[0].Name; //查询图片 if (tmp == "Byte[]") { byte[] img = new byte[0]; img = (byte[])obj; Image s = ByteToImage(img); if ((s.Size.Height >= 35) || (s.Size.Width >= 35)) { imgs.Add(img); } //p.SetValue(t, imgs, null); } break; case "Nullable`1": var tmpnull = p.PropertyType.GetGenericArguments()[0].Name; if (tmpnull == "DateTime") { if (obj.ToString()!="") { var dtvalue = DateTime.Parse(obj.ToString()); p.SetValue(t, dtvalue, null); } } else if (tmpnull == "Int32") { if (obj.ToString() != "") { var nivalue = Int32.Parse(obj.ToString()); p.SetValue(t, nivalue, null); } } else if (tmpnull == "Decimal") { if (obj.ToString() != "") { var ndvalue = Decimal.Parse(obj.ToString()); p.SetValue(t, ndvalue, null); } } else if (tmpnull == "Boolean") { if (obj.ToString() != "") { var nbvalue = (obj.ToString() == "0") ? false : true; p.SetValue(t, nbvalue, null); } } break; default: throw new Exception("字段类型未找到！"); //break; } } catch (System.Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("设置字段值错误！" + "字段:" + p.Name + " 字段类型：" +p.PropertyType.Name); } } /// <summary> /// 根据SQL语句查询单条数据库记录 /// </summary> /// <param name="sql">要查询的SQL语句</param> /// <returns>返回T类型的对象的一个实例</returns> /// </summery> public T QueryOne(string sql) { Logger.Log.Debug("start QueryOne"); Logger.Log.Info(sql); OracleDataReader reader = null; var ps = type.GetProperties(); try { reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); if (!reader.Read()) return null; T t = new T(); foreach (var p in ps) { if(!String.IsNullOrEmpty(t.GetFieldName(p.Name))) { var obj = reader[t.GetFieldName(p.Name)]; SetPropertyValue(p, t, obj); } } return t; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询单条记录错误！"); } finally { if (reader != null) reader.Close(); } } /// <summary> /// 查询单个字段的数据，并且以字符串形式返回这个字段的值 /// </summary> /// <param name="dbfldname">要查询的单个字段的名字</param> /// <param name="sql">要查询的SQL语句</param> /// <returns>字符串形式返回字段的值</returns> public string QueryOneStrField(string dbfldname, string sql) { Logger.Log.Debug("start QueryOneStrField"); Logger.Log.Info(dbfldname + sql); OracleDataReader reader = null; try { reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); if (!reader.Read()) return null; return reader[dbfldname].ToString(); ; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询单条字符串记录错误！"); } finally { if (reader != null) reader.Close(); } } #region 图象操作 /// <summary> /// 由图像转换为二进制数据 /// </summary> /// <param name="Picture">Image类的图像</param> /// <returns>二进制数据</returns> private byte[] ImageToByte(Image Picture) { MemoryStream ms = new MemoryStream(); if (Picture == null) return new byte[ms.Length]; Picture.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] BPicture = new byte[ms.Length]; BPicture = ms.GetBuffer(); return BPicture; } //二进制转换为图像 /// <summary> /// 由二进制数据转化为图像 /// </summary> /// <param name="btImage">要转换的二进制数据</param> /// <returns>Image图像类</returns> private Image ByteToImage(byte[] btImage) { if (btImage.Length == 0) return null; System.IO.MemoryStream ms = new System.IO.MemoryStream(btImage); System.Drawing.Image image = System.Drawing.Image.FromStream(ms); return image; } #endregion } /// <summary> /// 交警查询接口 /// </summary> /// <typeparam name="T">参数</typeparam> public interface IQueryPolice<T> where T : DomainBase, new() { /// <summary> /// 查询一条记录 /// </summary> /// <param name="sql">查询的SQL语句</param> /// <returns>查询对象</returns> T QueryOne(string sql); /// <summary> /// 查询多条记录 /// </summary> /// <param name="sql">查询的SQL语句</param> /// <returns>查询的对象集合</returns> List<T> QueryMany(string sql); /// <summary> /// 查询图片 /// </summary> /// <param name="sql">查询图片的SQL语句</param> /// <returns>查询的图片对象</returns> T QueryImg(string sql); /// <summary> /// 查询单个字段的数据，并且以字符串形式返回这个字段的值 /// </summary> /// <param name="dbfldname">要查询的单个字段的名字</param> /// <param name="sql">要查询的SQL语句</param> /// <returns>字符串形式返回字段的值</returns> string QueryOneStrField(string dbfldname, string sql); } /// <summary> /// 视频查询接口 /// </summary> public interface IQueryVideo { /// <summary> /// 根据违法序号和终端编号查询视频 /// </summary> /// <param name="vioSeq">违法序号</param> /// <param name="terminalID">终端编号</param> /// <returns></returns> string QueryVioVideo(string vioSeq, string terminalID, string carNum, DateTime vioDate, string hpzl); } /// <summary> /// 处理交警数据库相关的查询 /// </summary> /// <typeparam name="T">查询返回的对象，T类型的对象必须继承于DomainBase，并且有默认的构造函数</typeparam> public class QueryPolice<T> :QueryBase<T> , IQueryPolice<T>where T : DomainBase, new() { /// <summary> /// 构造函数，用来初始化交警数据库的连接字符串 /// </summary> public QueryPolice() { imgs = new List<byte[]>(); source = DBConfig.GetPoliceConnectString(); } /// <summary> /// 查询图片的服务比较特殊，所以单独在此做成方法 /// </summary> /// <param name="sql">要查询图片的SQL语句</param> /// <returns>返回查询的图像</returns> public T QueryImg(string sql) { Logger.Log.Debug("start QueryImg"); OracleDataReader reader = null; try { bool bflag = false; PropertyInfo pi =null; var ps = type.GetProperties(); foreach (var pp in ps) { if (pp.PropertyType.Name == "List`1") { bflag = true; pi = pp; imgs.Clear(); break; } } Logger.Log.Info(sql); reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); T t = new T(); while (reader.Read()) { foreach (var p in ps) { var obj = reader[t.GetFieldName(p.Name)]; SetPropertyValue(p, t, obj); } } if (bflag) pi.SetValue(t, imgs, null); return t; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询多条记录错误！"); } finally { if (reader != null) reader.Close(); } } } /// <summary> /// 我们自己的数据库的查询类 /// </summary> /// <typeparam name="T">要返回的对象的类型</typeparam> public class QueryMyDB<T> : QueryBase<T> where T : DomainBase, new() { /// <summary> /// 构造函数，初始化我们自己的数据库的连接字符串 /// </summary> public QueryMyDB() { source = DBConfig.GetConnectString(); } } /// <summary> /// 查询视频的类 /// </summary> public class QueryVideo : DAOTJ, IQueryVideo { /// <summary> /// 查询视频的类的构造函数 /// </summary> public QueryVideo() { urlPrefix = DBConfig.GetvideoHttpURLPrifex(); destdir = DBConfig.GetvideoHttpLocalDir(); } /// <summary> /// 要返回的视频的播放地址出去文件名的部分 /// </summary> private string urlPrefix;// = "http://localhost/video/"; /// <summary> /// 视频文件存放的位置,于前面的播放地址的虚拟路径要对应起来 /// </summary> private string destdir;// = "c://video//"; /// <summary> /// 根据违法序号和终端编号查询视频 /// </summary> /// <param name="vioSeq">违法序号</param> /// <param name="terminalID">终端编号</param> /// <returns></returns> public string QueryVioVideo(string vioSeq, string terminalID,string carNum,DateTime vioDate,string hpzl) { Logger.Log.Debug("start QueryVioVideo"); string filename; filename = DateTime.Now.ToString().Replace(':', '-') + ".mpg"; List<byte[]> imgs = new List<byte[]>(); byte[] mpg = new byte[0]; try { Logger.Log.Debug("目录:" + destdir + terminalID); Logger.Log.Debug("文件名："+ filename); if (Directory.Exists(destdir + terminalID)) { Directory.Delete(destdir + terminalID,true); } Directory.CreateDirectory(destdir + terminalID); //#if DEBUG // string sql = "select * from vio_dv_pic where Id = 143445"; // OracleDataReader read = OracleHelper.ExecuteReader(source, CommandType.Text, sql); //#else string tmp = carNum.Substring(1); string sql = string.Format("Select * from tj.view_chuping_dv where hphm like '_" + tmp + "' and hpzl = '" + hpzl + "' and wfsj = to_date('{0}','yyyy:mm:dd hh24:mi:ss')", vioDate.ToString()); Logger.Log.Info(sql); OracleParameter[] parameters = new OracleParameter[1]; //parameters[0] = new OracleParameter(":ScID", OracleType.VarChar, 16); //parameters[0].Value = vioSeq; OracleDataReader read = OracleHelper.ExecuteReader(source, CommandType.Text, sql); //#endif while (read.Read()) { mpg = (byte[])read["pic1"]; } File.WriteAllBytes(destdir + terminalID +"//" + filename, mpg); } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询视频错误"); //return null; } Logger.Log.Debug("目标：" + urlPrefix + terminalID + "/" + filename); return urlPrefix + terminalID + "/"+ filename; } } /// <summary> /// 我们自己数据库需要的一些特殊查询方式的类 /// </summary> public class QuerySpecial : DAOSelf { /// <summary> /// 查询单个字段的数据，并且以字符串形式返回这个字段的值 /// </summary> /// <param name="dbfldname">要查询的单个字段的名字</param> /// <param name="sql">要查询的SQL语句</param> /// <returns>字符串形式返回字段的值</returns> public string QueryOneStrField(string dbfldname, string sql) { Logger.Log.Debug("start QueryOneStrField"); Logger.Log.Info(dbfldname + sql); OracleDataReader reader = null; try { reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); if (!reader.Read()) return null; return reader[dbfldname].ToString(); ; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("查询单条字符串记录错误！"); } finally { if(reader!=null) reader.Close(); } } /// <summary> /// 根据给定的SQL语句查询是否有记录 /// </summary> /// <param name="sql">要查询的SQL语句</param> /// <returns>如果能查询到记录，返回true，否则返回false</returns> public bool QueryHaveRecord(string sql) { Logger.Log.Debug("start QueryHaveRecord"); Logger.Log.Info(sql); OracleDataReader reader = null; try { reader = OracleHelper.ExecuteReader(source, CommandType.Text, sql); if (!reader.Read()) return false; else return true; } catch (Exception e) { Logger.Log.Fatal(e.Message); CommExceptionMethod.ThrowException(e, "查询错误"); return false; } finally { if (reader != null) reader.Close(); } } } /// <summary> /// 写入数据库操作的基类，使用此类对数据库进行增，删，改操作 /// </summary> public class UpdasteBase : DAOSelf { private static object lockObj = new object (); /// <summary> /// 根据给定的SQL语句，对数据库进行增，删，改操作 /// </summary> /// <param name="sqlupdate">要执行的SQL语句</param> /// <returns></returns> public bool Update(string sqlupdate) { Logger.Log.Debug("start Update"); Logger.Log.Info(sqlupdate); OracleConnection conn = new OracleConnection(source); conn.Open(); OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); try { OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlupdate); trans.Commit(); return true; } catch (Exception e) { Logger.Log.Fatal(e.Message); trans.Rollback(); //throw new Exception( "根据KioskID查询终端设备错误"); return false; } finally { conn.Close(); } } private OracleConnection m_conn = null; private OracleTransaction m_trans = null; /// <summary> /// 开始事务 /// </summary> public void BeginTrans() { Logger.Log.Debug("start BeginTrans"); Monitor.Enter(lockObj); m_conn = new OracleConnection(source); m_conn.Open(); m_trans = m_conn.BeginTransaction(IsolationLevel.ReadCommitted); } /// <summary> /// 提交事务 /// </summary> public void Commit() { Logger.Log.Debug("start Commit"); try { m_trans.Commit(); } catch (Exception ex) { Logger.Log.Fatal(ex.Message); m_trans.Rollback(); throw new Exception("提交事务错误"); } } /// <summary> /// 会滚事务 /// </summary> public void RollBack() { Logger.Log.Debug("start RollBack"); m_trans.Rollback(); } /// <summary> /// 关闭数据库连接 /// </summary> public void CloseConn() { Logger.Log.Debug("start CloseConn"); m_conn.Close(); Monitor.Exit(lockObj); } /// <summary> /// 执行数据库操作，带事务，必须在BeginTrans()之后执行 /// </summary> /// <param name="sql">要执行的SQL语句</param> /// <returns>是否执行成功</returns> public bool UpdateWithTran(string sql) { Logger.Log.Debug("start UpdateWithTran"); Logger.Log.Info(sql); try { OracleHelper.ExecuteNonQuery(m_trans, CommandType.Text, sql); //trans.Commit(); return true; } catch (Exception e) { Logger.Log.Fatal(e.Message); throw new Exception("UpdateWithTran错误"); //return false; } } } /// <summary> /// 更新我们自己的数据库，如果操作我们的数据库，使用此类 /// </summary> public class UpdateMyDB : UpdasteBase { } }