先简单介绍本人的框架学习路程:
1.基本的简单三层
2.简单工厂:
3.抽象工厂:
4.基于Oracle自己整合的myOracle框架(增删查改)
主要的核心思想以及代码是封装在BaseDal类中的,接口没有提取成一层,封装在BaseAction类中,代码如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Dal { using myOracle.Comm; using System.Data; public interface BaseAction<T> { //定义共用父类接口方法 bool Exists(int id); bool Exists(string strWhere); bool Exists(string strWhere, List<DbParam> listPm); int GetCount(string strWhere, List<DbParam> listPm); int GetCount(string strWhere); T GetModel(string where); T GetModel(string where, List<DbParam> list); T GetModel(IDataReader dr); T GetModel(DataTable dt, DataRow row); bool Delete(string id); void Add(T model); void Update(T model); bool IsUpdateProperty(T model, string val); List<T> GetAllToListBySql(); List<T> GetAllToList(IDataReader dr); List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName); List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder); List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList); List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag); List<T> GetAllToList(string strWhere, int PageSize, int PageIndex); List<T> GetAllToList(string strWhere); List<T> GetAllToList(); string BuildSql(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName); IDataReader GetDataReaderByPage(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName); IDataReader GetDataReaderByPage(string strWhere, int PageSize, int PageIndex); List<T> GetListByWhere(string where); List<T> GetListByWhere(string where, List<DbParam> list); int ToExecuteNonQuerySql(string sqlStr); int ToExecuteNonQuerySql(string sqlStr, List<DbParam> list); object ToExecuteQuerySql(string strSql); object ToExecuteQuerySql(string strSql, List<DbParam> list); List<T> GetDataByProcedure(T model, out int totalCount); List<DbParam> AddOrUpdateParamList(T model, bool IsAdd); string GetAddUpdateSql(bool IsAdd); int AddUpdateList(List<T> list, EnumAction.AddUpdateType eAdd); int DeleteList(string ids); int DeleteList(string colName, string colValues); } }
Note:基于myOracle框架开发出了EasyUI和ExtJs两套简单系统,实际验证框架一般情况能正常运行.
因为之前是基于Oracle数据库开发的,所以取名为myOracle,但本质上是可以实现兼容Sqlserver,MySql数据库等的,只是现在暂时没有实现,留作日后扩展:
5.兼容Oracle,SqlServer数据库的YZR.Data
与以往搭建的框架的不同在于YZR.Data是基于多层架构的,由图也可以看到,YZR.Entity是使用枚举定义的.YZR.Data在此是充当一个ORM的作用.框架核心在于YZR.Core,逻辑在于YZR.Logic.
接下来简单跑一下增删查改:
一般从UI层通过Ajax的一条.ashx或者mvc的一个Action过来:
namespace YZR.UI.R_System.Ajax { using System.Data; using YZR.Logic; using YZR.Core; /// <summary> /// UserHandler 的摘要说明 /// </summary> public partial class SysHandler { public override void GetList() { switch (ObjName) { case "query": jsonResult = sysLogic.GetUsers(); break; default: base.GetList(); break; } } public override void Update() { switch (ObjName) { case "submit": jsonResult = sysLogic.UpdateUser(); break; default: base.Update(); break; } } public override void SelectOne() { switch (ObjName) { case "queryone": jsonResult = sysLogic.QueryOne(); break; default: base.SelectOne(); break; } } public override void Delete() { switch (ObjName) { case "del": jsonResult = sysLogic.Remove(); break; default: base.SelectOne(); break; } } } public partial class SysHandler : AjaxBase { SysLogic sysLogic; public SysHandler() { this.Query<string>("id"); //使用组合对象方式 sysLogic = new SysLogic(this); } } }
这个时候可以看一下统一处理的AjaxBase:
public string ObjName { get { if (!string.IsNullOrEmpty(HttpContext.Current.Request["action"])) return HttpContext.Current.Request["action"]; else return "YZR_Exception";//操作异常 } }
这里假设业务多样,需要重写统一的处理:
namespace YZR.Logic { using System.Data; using YZR.Comm; using YZR.Core; using YZR.Data; using YZR.Data.Tool; using YZR.Entity; using YZR.Utility; public class SysLogic:LogicBase { public SysLogic(IBase custom) : base(custom) { } #region 用户表相关操作 /// <summary> /// 返回UserList的Json数据 /// </summary> /// <returns></returns> public string GetUsers() { //IDataBase action = new RAction("SYS_ADMIN");//DbManager.GetDbUtility("SYS_ADMIN"); //DbManager db = new DbManager(); IDataBase action = DbManager.Instance.GetDbUtility("SYS_ADMIN"); int totalCount = 0; if (PageIndex < 1) throw new Exception("pageIndex参数不正确"); //去掉前缀 string orderField = Sort.Replace("JSON_", ""); DataTable dt = action.Select(getComBoxWhere,PageSize, PageIndex, orderField, "1", out totalCount); action.RClose(); string JsonStr = JsonHelper.CreateJsonParameters(dt, true, totalCount, "rows"); return JsonStr; } public string UpdateUser() //包括新增和编辑 { IDataBase action = new RAction("SYS_ADMIN"); int blResult = 0; int id = GetID != "" ? Convert.ToInt32(GetID) : 0; List<DbParam> list = new List<DbParam>(); DataTable dt=action.SelectOne(" SYSID=" + id, list); string writeMsg = "操作失败！"; RDic dic = new RDic(); dic.Add("USERNAME", Query<string>(SYS_ADMIN.USERNAME) ?? ""); dic.Add("REALLYNAME", Query<string>(SYS_ADMIN.REALLYNAME) ?? ""); dic.Add("BIRTHDAY", Convert.ToDateTime(Query<string>(SYS_ADMIN.BIRTHDAY) ?? DateTime.Now.ToString())); dic.Add("ADDRESS", Query<string>(SYS_ADMIN.ADDRESS) ?? ""); dic.Add("POSTCODE", Query<string>(SYS_ADMIN.POSTCODE) ?? ""); dic.Add("EMAIL", Query<string>(SYS_ADMIN.EMAIL) ?? ""); dic.Add("HOMEPHONE", Query<string>(SYS_ADMIN.HOMEPHONE) ?? ""); dic.Add("MOBILEPHONE", Query<string>(SYS_ADMIN.MOBILEPHONE) ?? ""); dic.Add("QQ", Query<string>(SYS_ADMIN.QQ) ?? ""); dic.Add("ICQ", Query<string>(SYS_ADMIN.ICQ) ?? ""); dic.Add("SEX", Query<int>(SYS_ADMIN.SEX)); dic.Add("IP", GetUserLoginIP.getIP() ?? ""); //IP //dic.Add("BLOGID",Query<int>(SYS_ADMIN.BLOGID)); dic.Add("SUPERADMIN", Query<int>(SYS_ADMIN.SUPERADMIN)); dic.Add("APASSWORD", Query<string>(SYS_ADMIN.APASSWORD) ?? ""); if (dt.Rows.Count == 0)//数据库没有此id代表的数据 { //并且id=0表示新增 if (id < 1) { dic.Add("REGTIME", DateTime.Now);//DateTime.Now.ToString("yyyy-MM-dd") blResult = action.Insert(dic); if (blResult > 0) { writeMsg = "增加成功!"; } else { writeMsg = "增加失败!"; } } else { //否则操作失败 } } else//数据库有此id代表的值 { dic.Add("SYSID", id); blResult = action.Update(dic,SYS_ADMIN.SYSID,true); if (blResult > 0) { writeMsg = "更新成功!"; } else { writeMsg = "更新失败!"; } } action.RClose(); //return "\"Msg\":\""+writeMsg+"\""; return writeMsg; } public string QueryOne() { IDataBase action = new RAction("SYS_ADMIN"); int id = GetID != "" ? Convert.ToInt32(GetID) : 0; List<DbParam> list = new List<DbParam>(); DataTable dt = action.SelectOne(" SYSID=" + id, list); action.RClose(); string writeMsg = "操作失败！"; if (dt.Rows.Count > 0) { return JsonHelper.CreateJsonOne(dt,null); } return writeMsg; } public string Remove() { IDataBase action = new RAction("SYS_ADMIN"); string id = GetID != "" ? GetID : null; string writeMsg = "操作失败！"; int blResult; if(!string.IsNullOrEmpty(id)) { blResult = action.Remove(id, SYS_ADMIN.SYSID); action.RClose(); if (blResult > 0) { writeMsg = "删除成功"; } return writeMsg; } else return writeMsg; } #endregion } }
在此版本的开发中,应该使用IDataBase action = DbManager.Instance.GetDbUtility("SYS_ADMIN"); 来获得IDataBase实例
原因:
RAtion是处理Oracle数据库的,RMotion是处理Sqlserver数据库的.使用上面代码,可以实现切换数据库不需要修改代码.Note:接下来,我的想法是尝试将MySql封装在RAtion中,实现一个类可以处理多个数据库,这个作为接下来要实现的东西.
下面贴出主要的几个类:
namespace YZR.Data { using System.Data; using System.Data.OracleClient; using System.Reflection; using YZR.Comm; using YZR.Entity; public class RAction : IDisposable, IDataBase { private OracleConnection conn; private OracleTransaction ston; private OracleCommand com; private OracleCommand getCommand() { return conn.CreateCommand(); } private OracleCommand getCommandNon() { return new OracleCommand(); } private OracleCommand getCommand(string commandText) { return new OracleCommand(commandText, conn); } public RAction(string name) { this.tableName = name; conn = DbAction.getConn(); conn.Open(); } //private string tableName = typeof(T).Name; private string tableName; public string TableName { get { return tableName; } set { tableName = value; } } public DataTable SelectOne(string where, List<DbParam> list) { StringBuilder sb = new StringBuilder(); StringBuilder files = new StringBuilder(); //Type t = typeof(T); Type tt = typeof(TableNames); string[] tNames = tt.GetEnumNames(); string tn = "SYS_ADMIN"; bool Flag = false; if (tNames.Contains<string>(tn)) { Flag = true; } Type type=null;//System.Type是引用类型 if(Flag) { string path = AppDomain.CurrentDomain.BaseDirectory; Assembly ass = Assembly.LoadFrom(path + "bin\\YZR.Entity.dll"); //Assembly ass = Assembly.Load("YZR.Entity"); type = ass.GetType("YZR.Entity." + tn); } //string[] Names = t.GetEnumNames(); string[] Names=null; if(type==null) { throw new Exception("枚举实体输错"); } Names = type.GetEnumNames(); DataTable d = getDataTableInfo(tableName); if (d.Rows.Count > 0) { for (int i = 0; i < d.Rows.Count; i++) { string typename = d.Rows[i][2].ToString().ToLower(); if (typename == "date") { Names[i] = "to_char(" + Names[i] + ",'YYYY-MM-DD') " + Names[i]; } } } for (int i = 0; i < Names.Length; i++) { files.Append(Names[i] + ","); } files.Remove(files.Length - 1, 1); sb.AppendFormat("select {1} from {0} ", TableName, files); if (!string.IsNullOrEmpty(where)) { sb.Append(" where rownum=1 and " + where); } //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } if (list != null && list.Count > 0) { for (int i = 0; i < list.Count; i++) { com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue)); } } IDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; return dt; } public DataTable SelectOne(RWhere where) { StringBuilder sb = new StringBuilder(); StringBuilder files = new StringBuilder(); //Type t = typeof(T); //string[] Names = t.GetEnumNames(); Type tt = typeof(TableNames); string[] tNames = tt.GetEnumNames(); string tn = tableName; bool Flag = false; if (tNames.Contains<string>(tn)) { Flag = true; } Type type = null;//System.Type是引用类型 if (Flag) { string path = AppDomain.CurrentDomain.BaseDirectory; Assembly ass = Assembly.LoadFrom(path + "bin\\YZR.Entity.dll"); //Assembly ass = Assembly.Load("YZR.Entity"); type = ass.GetType("YZR.Entity." + tn); } //string[] Names = t.GetEnumNames(); string[] Names = null; if (type == null) { throw new Exception("枚举实体输错"); } //string[] Names = t.GetEnumNames(); Names = type.GetEnumNames(); DataTable d = getDataTableInfo(tableName); if (d.Rows.Count > 0) { for (int i = 0; i < d.Rows.Count; i++) { string typename = d.Rows[i][2].ToString().ToLower(); if (typename == "date") { Names[i] = "to_char(" + Names[i] + ",'YYYY-MM-DD') " + Names[i]; } } } for (int i = 0; i < Names.Length; i++) { files.Append(Names[i] + ","); } files.Remove(files.Length - 1, 1); sb.AppendFormat("select {1} from {0} ", TableName, files); sb.Append(" where rownum=1 " + where.LogicName+" "+where.WhereName+" "+where.ActionName+" "+where.ResultValue+" "); //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } IDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; return dt; } public DataTable SelectOne(List<RWhere> whereList) { StringBuilder sb = new StringBuilder(); StringBuilder files = new StringBuilder(); //Type t = typeof(T); Type tt = typeof(TableNames); string[] tNames = tt.GetEnumNames(); string tn = tableName; bool Flag = false; if (tNames.Contains<string>(tn)) { Flag = true; } Type type = null;//System.Type是引用类型 if (Flag) { string path = AppDomain.CurrentDomain.BaseDirectory; Assembly ass = Assembly.LoadFrom(path + "bin\\YZR.Entity.dll"); //Assembly ass = Assembly.Load("YZR.Entity"); type = ass.GetType("YZR.Entity." + tn); } //string[] Names = t.GetEnumNames(); string[] Names = null; if (type == null) { throw new Exception("枚举实体输错"); } //string[] Names = t.GetEnumNames(); Names = type.GetEnumNames(); DataTable d = getDataTableInfo(tableName); if (d.Rows.Count > 0) { for (int i = 0; i < d.Rows.Count; i++) { string typename = d.Rows[i][2].ToString().ToLower(); if (typename == "date") { Names[i] = "to_char(" + Names[i] + ",'YYYY-MM-DD') " + Names[i]; } } } for (int i = 0; i < Names.Length; i++) { files.Append(Names[i] + ","); } files.Remove(files.Length - 1, 1); sb.AppendFormat("select {1} from {0} ", TableName, files); sb.Append(" where rownum=1 "); if (whereList.Any()) { for (int i = 0; i < whereList.Count; i++) { RWhere where = whereList[i]; sb.AppendFormat(" " + where.LogicName + " " + where.WhereName + " " + where.ActionName + " '" + where.ResultValue + "' "); } } //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } IDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; return dt; } public DataTable Select() { StringBuilder sb = new StringBuilder(); sb.AppendFormat("select * from {0} ", tableName); //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } OracleDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; return dt; } public DataTable Select(List<RWhere> whereList) { StringBuilder sb = new StringBuilder(); StringBuilder sbWhere = new StringBuilder(); if (!whereList.Any()) { sb.AppendFormat("select * from {0} ", tableName); } else { for (int i = 0; i < whereList.Count; i++) { RWhere where = whereList[i]; sbWhere.AppendFormat(" "+where.LogicName + " " + where.WhereName + " " + where.ActionName + " '" + where.ResultValue+"' "); } sb.AppendFormat("select * from {0} where 1=1 {1} ", tableName, sbWhere.ToString()); } //OracleConnection conn = DbAction.getConn(); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } //conn.Open(); OracleDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; // conn.Close(); return dt; } public DataTable Select(string Where, int pageSize, int pageIndex, string orderField, string orderFlag, out int totalCount) { //OracleCommand com = new OracleCommand(); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommandNon(); com.Connection = conn; } else { //若ston不为null,即含有事务,com已经创建 } com.CommandText = "fenye"; com.CommandType = CommandType.StoredProcedure; OracleParameter[] ps = { new OracleParameter("tableName",OracleType.VarChar), new OracleParameter("fields",OracleType.VarChar), new OracleParameter("wherecase",OracleType.VarChar), new OracleParameter("pageSize",OracleType.Number), new OracleParameter("pageNow",OracleType.Number), new OracleParameter("orderField",OracleType.VarChar), new OracleParameter("orderFlag",OracleType.Number), new OracleParameter("myrows",OracleType.Number), new OracleParameter("myPageCount",OracleType.Number), new OracleParameter("p_cursor",OracleType.Cursor) }; ps[0].Value = TableName; ps[1].Value = "*"; ps[2].Value = Where; ps[3].Value = pageSize; ps[4].Value = pageIndex; ps[5].Value = orderField; ps[6].Value = orderFlag; ps[7].Direction = ParameterDirection.Output; ps[8].Direction = ParameterDirection.Output; ps[9].Direction = ParameterDirection.Output; foreach (OracleParameter item in ps) { com.Parameters.Add(item); } OracleDataAdapter da = new OracleDataAdapter(com); DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception) { throw; } totalCount = int.Parse(ps[7].Value.ToString()); return ds.Tables[0]; } public DataTable Select(List<RWhere> whereList, int pageSize, int pageIndex, string orderField, string orderFlag, out int totalCount) { StringBuilder sbWhere = new StringBuilder(); sbWhere.Append(" where 1=1 "); if (whereList.Any()) { for (int i = 0; i < whereList.Count; i++) { RWhere where = whereList[i]; sbWhere.AppendFormat(" " + where.LogicName + " " + where.WhereName + " " + where.ActionName + " '" + where.ResultValue + "' "); } } //OracleCommand com = new OracleCommand(); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommandNon(); com.Connection = conn; } else { //若ston不为null,即含有事务,com已经创建 } com.CommandText = "fenye"; com.Connection = conn; com.CommandType = CommandType.StoredProcedure; OracleParameter[] ps = { new OracleParameter("tableName",OracleType.VarChar), new OracleParameter("fields",OracleType.VarChar), new OracleParameter("wherecase",OracleType.VarChar), new OracleParameter("pageSize",OracleType.Number), new OracleParameter("pageNow",OracleType.Number), new OracleParameter("orderField",OracleType.VarChar), new OracleParameter("orderFlag",OracleType.Number), new OracleParameter("myrows",OracleType.Number), new OracleParameter("myPageCount",OracleType.Number), new OracleParameter("p_cursor",OracleType.Cursor) }; ps[0].Value = TableName; ps[1].Value = "*"; ps[2].Value = sbWhere.ToString(); ps[3].Value = pageSize; ps[4].Value = pageIndex; ps[5].Value = orderField; ps[6].Value = orderFlag; ps[7].Direction = ParameterDirection.Output; ps[8].Direction = ParameterDirection.Output; ps[9].Direction = ParameterDirection.Output; foreach (OracleParameter item in ps) { com.Parameters.Add(item); } OracleDataAdapter da = new OracleDataAdapter(com); DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception) { throw; } totalCount = int.Parse(ps[7].Value.ToString()); return ds.Tables[0]; } public int Insert(RDic dic) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("insert into {0} (", tableName); foreach (KeyValuePair pair in dic) { string key = pair.Key; sb.Append(key + ","); object value = pair.Value; } sb.Remove(sb.Length - 1, 1); sb.Append(") values("); foreach (KeyValuePair pair in dic) { object value = pair.Value; try { if (value.ToString().Contains("YZRDateTime"))//此处有空指针报错的危险,需要try catch { sb.Append("to_date('" + value.ToString().Replace("YZRDateTime", "").Trim() + "','YYYY-MM-DD HH24:MI:SS'),"); } else sb.Append("'" + value + "',"); } catch (Exception ex) { throw new Exception("出现了没有赋值的列,请检查表单的数据是否完整"); } } sb.Remove(sb.Length - 1, 1); sb.Append(")"); //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } OracleString rowid; int count = com.ExecuteOracleNonQuery(out rowid); return count; } /// <summary> /// 在RDic字典中需要加上主键列 /// </summary> /// <param name="dic"></param> /// <param name="Pkey"></param> /// <param name="isOne"></param> /// <returns></returns> public int Update(RDic dic, string Pkey, bool isOne) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("update {0} set ", tableName); string key = string.Empty; object value; foreach (KeyValuePair pair in dic) { key = pair.Key; value = pair.Value; if (key.ToLower().Trim() == Pkey.ToLower().Trim()) continue; try { if (value.ToString().Contains("YZRDateTime"))//此处有空指针报错的危险,需要try catch { sb.Append(key + "=to_date('" + value.ToString().Replace("YZRDateTime", "").Trim() + "','YYYY-MM-DD HH24:MI:SS'),"); } else sb.Append(key + "='" + value + "',"); } catch (Exception ex) { throw new Exception("出现了没有赋值的列,请检查表单的数据是否完整"); } } sb.Remove(sb.Length - 1, 1); if (isOne) { sb.AppendFormat(" where {0}='{1}'", Pkey, dic[Pkey].ToString()); } else { StringBuilder newids = new StringBuilder(); string ids = dic[Pkey].ToString(); string[] strs = ids.Split(','); for (int i = 0; i < strs.Length; i++) { newids.Append("'" + strs[i] + "',"); } newids.Remove(newids.Length - 1, 1); sb.AppendFormat(" where {0} in ({1}) ", Pkey, newids); } //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } OracleString rowid; int count = com.ExecuteOracleNonQuery(out rowid); return count; } public int Update(RDic dic, Enum Pkey, bool isOne) { return Update(dic, Pkey.ToString(), isOne); } public int Remove(string ids, string PKey) { StringBuilder sb = new StringBuilder(); StringBuilder newids = new StringBuilder(); string[] strs = ids.Split(','); for (int i = 0; i < strs.Length; i++) { newids.Append("'" + strs[i] + "',"); } newids.Remove(newids.Length - 1, 1); sb.AppendFormat("delete from {0} where {1} in ({2})", tableName, PKey, newids); //OracleCommand com = new OracleCommand(sb.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sb.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sb.ToString(); } OracleString rowid; int count = com.ExecuteOracleNonQuery(out rowid); return count; } public int Remove(string ids, Enum PKey) { return Remove(ids, PKey.ToString()); } /// <summary> /// 软删除 /// </summary> /// <param name="Flag">软删除标志</param> /// <param name="FValue">软删除值</param> /// <param name="PKey">主键</param> /// <param name="id">值</param> /// <returns></returns> public int Delete(string Flag, object FValue, string PKey, object id) { RDic dic = new RDic(); dic.Add(Flag, FValue); dic.Add(PKey, id); return Update(dic, PKey, true); } public int Delete(string Flag, object FValue, Enum PKey, object id) { return Delete(Flag, FValue, PKey.ToString(), id); } /// <summary> /// 批量软删除 /// </summary> /// <param name="Flag"></param> /// <param name="FValue"></param> /// <param name="PKey"></param> /// <param name="ids"></param> /// <returns></returns> public int BatchDelete(string Flag, object FValue, string PKey, string ids) { RDic dic = new RDic(); dic.Add(Flag, FValue); dic.Add(PKey, ids); return Update(dic, PKey, false); } public int BatchDelete(string Flag, object FValue, Enum PKey, string ids) { return BatchDelete(Flag, FValue, PKey.ToString(),ids); } public int Count(string strWhere, List<DbParam> listPm) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from " + TableName); if (!string.IsNullOrEmpty(strWhere)) strSql.Append(" where " + strWhere); //OracleCommand cmd = new OracleCommand(strSql.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(strSql.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = strSql.ToString(); } if (listPm != null) { foreach (DbParam pm in listPm) { com.Parameters.Add(DbHelper.CreateParam(pm.ParamName, pm.ParamValue)); } } object obj = com.ExecuteScalar(); int cmdresult; if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } return cmdresult; } public int Count(List<RWhere> wheres) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from " + TableName); if (wheres.Any()) { strSql.Append(" where 1=1"); for (int i = 0; i < wheres.Count; i++) { RWhere where = wheres[i]; strSql.AppendFormat(" " + where.LogicName + " " + where.WhereName + " " + where.ActionName + " '" + where.ResultValue + "' "); } } //OracleCommand cmd = new OracleCommand(strSql.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(strSql.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = strSql.ToString(); } object obj = com.ExecuteScalar(); int cmdresult; if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } return cmdresult; } public int Count() { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from " + TableName); //OracleCommand cmd = new OracleCommand(strSql.ToString(), conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(strSql.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = strSql.ToString(); } object obj = com.ExecuteScalar(); int cmdresult; if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } return cmdresult; } private OracleConnection getConnection() { return DbAction.getConn(); } public void beginTransaction() { ston = conn.BeginTransaction(); com = getCommand(); com.Transaction = ston; } public void RCommit() { ston.Commit(); } public void Rrollback() { ston.Rollback(); } private DataTable getDataTableInfo(string tName) { string sql = string.Format("select column_id,column_name,data_type from user_tab_columns where table_name = '{0}'", tName); //OracleCommand com = new OracleCommand(sql, conn); if (ston == null)//返回一个新的OracleCommand,不含事务 { com = getCommand(sql.ToString()); } else { //若ston不为null,即含有事务,com已经创建 com.CommandText = sql.ToString(); } OracleDataReader dr = com.ExecuteReader(); DataTable dt = new DataTable(); int fieldcout = dr.FieldCount; if (dr.FieldCount > 0) { for (int i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); dt.Columns.Add(dc); } object[] rowobject = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(rowobject); dt.LoadDataRow(rowobject, true); } } dr.Close(); ; return dt; } public void Dispose() { conn.Close(); } public void ROpen() { conn.Open(); } public void RClose() { conn.Close(); } } }
RMotion是同理于RAtion的,只是处理的数据库不一样.
namespace YZR.Data { public class RWhere { private string logicName;//运算符 public string LogicName { get { return logicName; } set { logicName = value; } } private string whereName;//字段 public string WhereName { get { return whereName; } set { whereName = value; } } private string actionName;//操作符 public string ActionName { get { return actionName; } set { actionName = value; } } private object resultValue;//值 public object ResultValue { get { if(resultValue is DateTime) { DateTime rV = Convert.ToDateTime(resultValue); return "to_date('" + rV.ToString("yyyy-MM-dd") + "','yyyy-mm-dd')"; } return resultValue; } set { resultValue = value; } } public RWhere(string logicName, string whereName, string actionName, object resultValue) { this.logicName = logicName; this.whereName = whereName; this.actionName = actionName; this.resultValue = resultValue; } public RWhere(string logicName, Enum whereName, string actionName, object resultValue) { this.logicName = logicName; this.whereName = whereName.ToString(); this.actionName = actionName; this.resultValue = resultValue; } } }
namespace YZR.Data { /// <summary> /// 自定义键值对 By YZR /// </summary> public class KeyValuePair { public KeyValuePair() { } public KeyValuePair(string key, object value) { this.key = key; this.value = value; } private string key; public string Key { get { return key; } set { key = value; } } private object value; public object Value { get { return this.value; } set { this.value = value; } } } public class RDic : IEnumerable { List<KeyValuePair> list = new List<KeyValuePair>(); public void Add(string key, object value) { //TODO 做一些处理以适应各种数据类型在sql语句中的写法 if(value is DateTime) { value = "YZRDateTime"+value.ToString(); } list.Add(new KeyValuePair(key, value)); } public bool ContainsKey(string key) { bool res = false; foreach (KeyValuePair item in list) { if (item.Key == key) { res = true; break; } } return res; } public IEnumerator GetEnumerator() { return this.list.GetEnumerator(); } public object this[int index] {//int类型 get { return list[index].Value; } //index>ary.Count时超出索引界限 //set { } } public object this[string name] {//string类型 通过name查找索引 参数类型自己决定 返回类型自己决定 get { for (int i = 0; i < list.Count; i++) { if (list[i].Key == name) { return list[i].Value; } } return null; } } } }
namespace YZR.Core { using YZR.Data; /// <summary> /// Db管理类 By YZR /// </summary> public class DbManager { private Dictionary<string, IDataBase> dbUtilitys;//存储Db键值对 public static string DefaultDbUtilityName;//Db默认key值 private static DbManager instance;//实例 public static DbManager Instance { get { return DbManager.instance; } set { DbManager.instance = value; } } static DbManager() { DefaultDbUtilityName = "default"; instance = new DbManager(); }//只执行一次的操作,初始化DbManager public DbManager() { this.dbUtilitys = new Dictionary<string, IDataBase>(); }//得到Db键值对 public IDataBase GetDbUtility(string name) { return this.dbUtilitys[name]; }//根据key获取DbUtility public void RegistDbUtility(string name,IDataBase dbUtility) { this.dbUtilitys[name] = dbUtility; }//往Db键值对注册 public IDataBase DefaultDbUtility { get { return this.dbUtilitys[DefaultDbUtilityName]; } }//获取默认DbUtility } }
END!
