以前曾经用过Oracle+ExtJS进行项目开发,可能接触的很少,感觉和Sqlserver数据库大同小异,
就我自己而言,很多项目一般基于Ado.Net+Sqlserver2008 R2这一套(用过的Entity FrameWork实体框架(ORM)底层也是基于Ado.Net去访问数据库).
现在我自己写一个用Ado.Net访问Oracle数据库的一点点代码(还不完整,以后会补全基本的常用操作功能),我自己的思路是从简单三层一点点往抽象模式进行编写
首先定义的UserInfo是从oracle数据库映射的一个实体对象,在数据访问层中以UserInfoDal形式继承至BaseDal<T>泛型父类和一个关于对于List操作的泛型接口,
代码如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Dal { using myOracle.Model; using System.Data; using System.Data.OracleClient; using myOracle.Comm; public class UserInfoDal :BaseDal<UserInfo>,ListAction<UserInfo> { //子类内容 //public List<UserInfo> GetListByWhere() //{ // OracleConnection conn = DbAction.getConn(); // string sqlStr = "select * from userinfo t"; // OracleCommand com = new OracleCommand(sqlStr, conn); // conn.Open(); // OracleDataReader dr = com.ExecuteReader(); // List<UserInfo> list = new List<UserInfo>(); // while (dr.Read()) // { // list.Add(base.GetModel(dr)); // } // dr.Close(); // conn.Close(); // return list; //} //public List<UserInfo> GetListByWhere(string whereid) //{ // OracleConnection conn = DbAction.getConn(); // string sqlStr = "select * from userinfo t where fid=:fid "; // OracleCommand com = new OracleCommand(sqlStr, conn); // com.Parameters.Add(DbHelper.CreateParam("fid", string.IsNullOrEmpty(whereid)?"1":whereid)); // OracleDataAdapter da = new OracleDataAdapter(com); // DataSet ds = new DataSet(); // da.Fill(ds); // return DataSetTiList(ds); //} public List<UserInfo> DataReaderToList(OracleDataReader dr) { try { List<UserInfo> list = list = new List<UserInfo>(); while (dr.Read()) { UserInfo t = new UserInfo(); t.Fid = int.Parse(dr.GetOracleNumber(0).ToString()); t.Fname = dr.GetOracleString(1).ToString(); t.Fpassword = dr.GetOracleString(2).ToString(); list.Add(t); } return list; } catch (Exception) { throw; } finally { dr.Close(); } } public List<UserInfo> DataSetTiList(DataSet ds) { DataTable dt = ds.Tables[0]; List<UserInfo> list = null; if(dt.Rows.Count>0) { list = new List<UserInfo>(); foreach (DataRow item in dt.Rows) { UserInfo user = new UserInfo(); user.Fid = int.Parse(item[0].ToString()); user.Fname = item[1].ToString(); user.Fpassword = item[2].ToString(); list.Add(user); } } return list; } } }
Note:需要注意的是ListAction<T>目前没有多大实际意义(,但我还是保留下来).子类完全可以共享父类BaseDal<T>的基础方法,一般不是很特殊的东西直接继承父类就ok了;
其中会涉及到一个DbAction类,可以封装一些数据库连接关闭等的对象以及方法:这里先封装了getConn(),以后再继续补充:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Dal { using System.Data; using System.Data.OracleClient; public static class DbAction { private readonly static string connString = System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ToString(); public static OracleConnection getConn() { OracleConnection conn = new OracleConnection(connString); return conn; } } }
那我们可以举一个列子,比如我现在要新建一个Dal类,那么需要做的工作如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Dal { using myOracle.Model; public class EmpDal:BaseDal<Emp> { } }
回归正题,正常的通用逻辑应该归于在BaseDal<T>泛型父类中进行实现,先来讲一下接口,接口定义规范,我记得是这么说的,一般的话,我会先建好class再去建立相应的接口:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Dal { using myOracle.Comm; using myOracle.Model; using System.Data; using System.Data.OracleClient; using System.Text; using System.Reflection; public class BaseDal<T> : BaseAction<T> where T : BaseModel, new() { //全部公用 private string _TableName; public string TableName { get { if (string.IsNullOrEmpty(t.TableName)) { _TableName = typeof(T).Name;//using myOracle.Model; } else { _TableName = t.TableName; } return _TableName; } set { _TableName = value; } } private string _PrimaryKey; public string PrimaryKey { get { if (string.IsNullOrEmpty(_PrimaryKey)) { _PrimaryKey = t.PrimaryKey; } return _PrimaryKey; } set { _PrimaryKey = value; } } /// <summary> /// 分页查询字段列 /// </summary> private string fileds; public string Fileds { get { if (string.IsNullOrEmpty(fileds)) { fileds = t.Fileds; } return fileds; } set { fileds = value; } } private T _t; public T t { get { if (_t == null) { _t = new T(); } return _t; } set { _t = value; } } #region BaseMethod /// <summary> /// 判断记录是否存在 /// </summary> /// <param name="id">记录ID</param> public virtual bool Exists(int id) { return Exists(string.Format("{0}=:{0}", PrimaryKey), new List<DbParam> { new DbParam { ParamName = PrimaryKey, ParamValue = id } }); } /// <summary> /// 判断记录是否存在 /// </summary> /// <param name="strWhere">Where子句</param> public virtual bool Exists(string strWhere) { return Exists(strWhere, null); } /// <summary> /// 参数化where条件判断 /// </summary> /// <param name="strWhere">where关键字后的判断语句(参数化)</param> /// <param name="listPm">(参数化)参数集合</param> /// <returns></returns> public virtual bool Exists(string strWhere, List<DbParam> listPm) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from " + TableName); if (strWhere != "") strSql.Append(" where " + strWhere); OracleConnection conn = DbAction.getConn(); OracleCommand cmd = new OracleCommand(strSql.ToString(), conn); if (listPm != null) { foreach (DbParam pm in listPm) { cmd.Parameters.Add(DbHelper.CreateParam(pm.ParamName, pm.ParamValue)); } } object obj = cmd.ExecuteScalar(); int cmdresult; if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } return true; } /// <summary> /// 获得总条数 /// </summary> /// <param name="strWhere"></param> /// <param name="listPm"></param> /// <returns></returns> public virtual int GetCount(string strWhere, List<DbParam> listPm) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from " + TableName); if (!string.IsNullOrEmpty(strWhere)) strSql.Append(" where " + strWhere); OracleConnection conn = DbAction.getConn(); OracleCommand cmd = new OracleCommand(strSql.ToString(), conn); if (listPm != null) { foreach (DbParam pm in listPm) { cmd.Parameters.Add(DbHelper.CreateParam(pm.ParamName, pm.ParamValue)); } } conn.Open(); object obj = cmd.ExecuteScalar(); conn.Close(); int cmdresult; if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } return cmdresult; } /// <summary> /// 获得记录数 /// </summary> /// <param name="strWhere">Where子句</param> public virtual int GetCount(string strWhere) { return GetCount(strWhere, null); } #endregion #region GetModel public virtual T GetModel(string where) { return this.GetModel(where, null); } public virtual T GetModel(string where, List<DbParam> list) { OracleConnection conn = DbAction.getConn(); StringBuilder sb = new StringBuilder(); T model = default(T); sb.AppendFormat("select * from {0} t ", TableName); if (!string.IsNullOrEmpty(where)) { sb.Append(" where " + where); } OracleCommand com = new OracleCommand(sb.ToString(), conn); 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)); } } conn.Open(); using (IDataReader dr = com.ExecuteReader()) { if (dr.Read()) model = GetModel(dr); } conn.Close(); return model; } public virtual T GetModel(IDataReader dr) { try { T t=null; if (dr != null) { t= new T(); for (int i = 0; i <= dr.FieldCount; i++) { if(i==dr.FieldCount) { return t; } PropertyInfo ps = t.GetType().GetProperty(StringAction.UpperFirstChar(dr.GetName(i))); string name = dr.GetName(i); if (ps != null) { if (dr.GetValue(i) != DBNull.Value && dr.GetValue(i).ToString().Length > 0) { object iobject = dr.GetValue(i); try { ps.SetValue(t, dr.GetValue(i), null); } catch (Exception) { //处理Decimal到Int类型转换 ps.SetValue(t, TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i)), null); //TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i)); } } } } } return t; } catch (Exception) { throw; } } public virtual T GetModel(DataTable dt, DataRow row) { T t = new T(); if (dt != null) { if (dt.Rows.Count > 0) { for (int i = 0; i <= dt.Columns.Count; i++) { //DataRow row = dt.Rows[0]; PropertyInfo ps = t.GetType().GetProperty(StringAction.UpperFirstChar(dt.Columns[i].ColumnName)); string name = dt.Columns[i].ColumnName; string value = string.Empty; if (row[i] != null) { value = row[i].ToString(); } if (ps != null) { if (row[i] != DBNull.Value && value.Length > 0) { try { ps.SetValue(t, row[i], null); } catch (Exception) { //处理Decimal到Int类型转换 ps.SetValue(t, TypeConvert.ConvertToInt32((System.Decimal)row[i]), null); //TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i)); } } } } } } return t; } #endregion #region 增删改 public virtual bool Delete(int id) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("delete from {0} where {1}=:id", TableName, PrimaryKey); OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); com.Parameters.Add(DbHelper.CreateParam("id", string.IsNullOrEmpty(id.ToString()) ? 0 : id)); conn.Open(); int i = com.ExecuteNonQuery(); conn.Close(); return true; } /// <summary> /// 格式:可以直接传入一个fid值 =>19 /// 单个键值对 =>Fname='name' /// 多个键值对,使用逗号分隔 => Fname='name',Fid=19 /// </summary> /// <param name="strWhere"></param> /// <returns></returns> public virtual bool Delete(string strWhere) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("delete from {0} where ", TableName); if (!string.IsNullOrEmpty(strWhere)) { string[] strKey_Value = strWhere.Split(','); if (strKey_Value.Length > 1) { for (int i = 0; i < strKey_Value.Length; i++) { sb.Append(strKey_Value[i] + " and "); } sb.Replace("and", "", sb.Length - 5, 5); } else if (strWhere.IndexOf("=") > -1) { sb.Append(strWhere); } else { int id = 0; int.TryParse(strWhere, out id); return this.Delete(id); } } OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); conn.Open(); int isok = com.ExecuteNonQuery(); conn.Close(); if (isok > 0) { return true; } return false; } public virtual void Add(T model) { StringBuilder sb = new StringBuilder(); StringBuilder ParamStr = new StringBuilder(); sb.AppendFormat("insert into {0} (", TableName); List<DbParam> list = new List<DbParam>(); PropertyInfo[] propertys = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); for (int i = 0; i < propertys.Length; i++) { if (t.IsAutoId) { if (propertys[i].Name == PrimaryKey) continue; } //DateTime类型处理(日期最小不能小于1900.1.1) if (propertys[i].PropertyType == typeof(DateTime) && ((DateTime)propertys[i].GetValue(model, null)) < new DateTime(1900, 1, 1)) { propertys[i].SetValue(model, new DateTime(1900, 1, 1), null); } sb.Append(propertys[i].Name + ","); ParamStr.Append(":" + propertys[i].Name + ","); DbParam param = new DbParam() { ParamName = ":" + propertys[i].Name, ParamDbType = TypeConvert.GetOracleDbType(propertys[i].PropertyType), ParamValue = propertys[i].GetValue(model, null) }; list.Add(param); } sb.Replace(",", ")", sb.Length - 1, 1); ParamStr.Replace(",", ")", ParamStr.Length - 1, 1); sb.Append(" values("); sb.Append(ParamStr);//在plsql虽然可以加上分号";",但是在这里不能加上分号";" if (t.IsAutoId) { /* * 先取得一个序列的下一个值： select myseq.nextval from dual; 然后再把这个值当成主键值插入数据表： insert into mytable (id, ...) values (id_val, ...) * */ } OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); foreach (DbParam item in list) { com.Parameters.Add(DbHelper.CreateParam(item.ParamName, item.ParamValue)); //com.Parameters.Add(p); } OracleString rowid; conn.Open(); com.ExecuteOracleNonQuery(out rowid); conn.Close(); } /// <summary> /// model需要有主键 /// </summary> /// <param name="model"></param> public virtual void Update(T model) { StringBuilder sb = new StringBuilder(); List<OracleParameter> list = new List<OracleParameter>(); sb.AppendFormat("update {0} set ", TableName); PropertyInfo[] ps = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); string keyName = ""; for (int i = 0; i < ps.Length; i++) { if (ps[i].Name.ToLower() != PrimaryKey.ToLower()) { if (this.IsUpdateProperty(model, ps[i].Name)) { sb.Append(ps[i].Name + "=" + ":" + ps[i].Name + ","); list.Add(DbHelper.CreateParam(ps[i].Name, ps[i].GetValue(model, null))); } } else { keyName = ps[i].GetValue(model, null).ToString(); } } sb.Remove(sb.Length - 1, 1); if (!string.IsNullOrEmpty(keyName)) { //根据主键更新 sb.Append(" where " + PrimaryKey + "=" + keyName); } else { throw new Exception("主键不能为空"); } if (!string.IsNullOrEmpty(model.Where)) { //自定义where条件 sb.Append(" and " + model.Where); } OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); for (int i = 0; i < list.Count; i++) { com.Parameters.Add(list[i]); } OracleString rowid; conn.Open(); com.ExecuteOracleNonQuery(out rowid); conn.Close(); } #endregion #region 辅助函数 /// <summary> /// 是否字段值是否更新由BaseModel的columns定义===>推断出反射出来的属性是否需要更新 /// </summary> /// <param name="model">columns定义的列</param> /// <param name="val">反射的属性</param> /// <returns>是否更新</returns> public bool IsUpdateProperty(T model, string val) { bool result = false; string strs = model.Columns; if (strs == "*") { return true; } string[] cols = strs.Split(','); for (int i = 0; i < cols.Length; i++) { if (val.Equals(cols[i], StringComparison.OrdinalIgnoreCase)) { result = true; //跳出循环 break; } } return result; } #endregion #region GetList /// <summary> /// 使用DataReader实现 /// </summary> /// <returns></returns> public virtual List<T> GetAllToListBySql() { OracleConnection conn = DbAction.getConn(); StringBuilder sb = new StringBuilder(); sb.AppendFormat("select * from {0}", TableName); OracleCommand com = new OracleCommand(sb.ToString(), conn); conn.Open(); OracleDataReader dr = com.ExecuteReader(); List<T> list = new List<T>(); while (dr.Read()) { list.Add(GetModel(dr)); } dr.Close(); conn.Close(); return list; } public List<T> GetListByWhere(string where) { return GetListByWhere(where, null); } /// <summary> /// 使用DataAdapter实现 /// </summary> /// <param name="where"></param> /// <param name="list"></param> /// <returns></returns> public List<T> GetListByWhere(string where, List<DbParam> list) { OracleConnection conn = DbAction.getConn(); StringBuilder sb = new StringBuilder(); sb.AppendFormat("select * from {0}", TableName); List<T> TList = new List<T>(); if (string.IsNullOrEmpty(where)) { sb.Append(" where " + where); } OracleCommand com = new OracleCommand(sb.ToString(), conn); if (list != null) { for (int i = 0; i < list.Count; i++) { com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue)); } } OracleDataAdapter da = new OracleDataAdapter(com); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow item in ds.Tables[0].Rows) { TList.Add(GetModel(ds.Tables[0], item)); } return TList; } public virtual List<T> GetAllToList(IDataReader dr) { List<T> list = new List<T>(); if (dr != null) { while (dr.Read()) { list.Add(GetModel(dr)); } } return list; } public virtual List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName) { //自托管 using (IDataReader dr = GetDataReaderByPage(strWhere, PageSize, PageIndex, OrderFlag, colList, fldOrder, tblName)) { return GetAllToList(dr); } } #endregion #region 自定义sql public virtual int ToExecuteNonQuerySql(string sqlStr) { return ToExecuteNonQuerySql(sqlStr, null); } public virtual int ToExecuteNonQuerySql(string sqlStr, List<DbParam> list) { OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sqlStr, conn); if (list != null) { for (int i = 0; i < list.Count; i++) { com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue)); } } conn.Open(); OracleString outid; int count = com.ExecuteOracleNonQuery(out outid); conn.Close(); return count; } public virtual object ToExecuteQuerySql(string strSql) { return ToExecuteQuerySql(strSql, null); } public virtual object ToExecuteQuerySql(string strSql, List<DbParam> list) { OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(strSql, conn); if (list != null) { for (int i = 0; i < list.Count; i++) { com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue)); } } conn.Open(); object obj = com.ExecuteScalar(); conn.Close(); return obj; } #endregion #region 分页 /// <summary> /// 分页存储过程得到数据 /// </summary> /// <param name="model"></param> /// <param name="totalCount"></param> /// <returns></returns> public virtual List<T> GetDataByProcedure(T model, out int totalCount) { OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(); com.CommandText = model.ProcedureName; 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;//注意这里两种情况1.在实体层手动输入tableName,然后调用model.TableName;2.直接在数据访问层中反射类名TableName ps[1].Value = model.Fileds; ps[2].Value = model.Where; ps[3].Value = model.PageSize; ps[4].Value = model.PageIndex; ps[5].Value = model.OrderField; ps[6].Value = model.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; } model.Result = ds.Tables[0]; model.TotalCount = int.Parse(ps[7].Value.ToString()); model.TotalPage = Convert.ToInt32(Math.Ceiling(Double.Parse(ps[8].Value.ToString()))); totalCount = int.Parse(ps[7].Value.ToString()); List<T> list = new List<T>(); foreach (DataRow item in ds.Tables[0].Rows) { list.Add(GetModel(ds.Tables[0], item)); } return list; } /// <summary> /// 根据翻页(sql语句)获取记录到DataReader /// </summary> /// <param name="strWhere">Where子句</param> /// <param name="PageSize">每页记录数</param> /// <param name="PageIndex">当前页码</param> /// <param name="OrderType">排序规则(true-降序；flase-升序)</param> /// <param name="colList">以逗号分隔的查询列名称</param> /// <param name="fldOrder">排序字段名称</param> /// <param name="tblName">表名</param> public IDataReader GetDataReaderByPage(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName) { string strSql = BuildSql(strWhere, PageSize, PageIndex, OrderFlag, colList, fldOrder, tblName); OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(strSql, conn); conn.Open(); IDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);//使用using也可以 return dr; } public IDataReader GetDataReaderByPage(string strWhere, int PageSize, int PageIndex) { return GetDataReaderByPage(strWhere, PageSize, PageIndex, 1, Fileds, PrimaryKey, TableName); } public List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder) { return GetAllToList(strWhere, PageSize, PageIndex, OrderFlag, colList, fldOrder, TableName); } public List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList) { return GetAllToList(strWhere, PageSize, PageIndex, OrderFlag, colList, PrimaryKey, TableName); } public List<T> GetAllToList(string strWhere, int PageSize, int PageIndex, int OrderFlag) { return GetAllToList(strWhere, PageSize, PageIndex, OrderFlag, Fileds, PrimaryKey, TableName); } /// <summary> /// 默认降序排序 /// </summary> /// <param name="strWhere"></param> /// <param name="PageSize"></param> /// <param name="PageIndex"></param> /// <returns></returns> public List<T> GetAllToList(string strWhere, int PageSize, int PageIndex) { //默认降序排序 return GetAllToList(strWhere, PageSize, PageIndex, 1, Fileds, PrimaryKey, TableName); } /// <summary> /// 默认降序排序,取第一页数据,每页8条记录 /// </summary> /// <param name="strWhere"></param> /// <returns></returns> public List<T> GetAllToList(string strWhere) { //默认降序排序,取第一页数据,每页8条记录 return GetAllToList(strWhere, 8, 1, 1, Fileds, PrimaryKey, TableName); } /// <summary> /// 默认降序排序,取第一页数据,每页8条记录,没有where条件 /// </summary> /// <returns></returns> public List<T> GetAllToList() { //默认降序排序,取第一页数据,每页8条记录,没有where条件 return GetAllToList(null, 8, 1, 1, Fileds, PrimaryKey, TableName); } /// <summary> /// 分页sql /// </summary> /// <param name="strWhere"></param> /// <param name="PageSize"></param> /// <param name="PageIndex"></param> /// <param name="OrderFlag">排序类型</param> /// <param name="colList"></param> /// <param name="fldOrder">排序字段</param> /// <param name="tblName"></param> /// <returns></returns> public string BuildSql(string strWhere, int PageSize, int PageIndex, int OrderFlag, string colList, string fldOrder, string tblName) { //查询字段 string sColList = ""; if (string.IsNullOrEmpty(colList) || colList == "*") { PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); foreach (PropertyInfo pi in pis) { //如果是oracle,不支持字段列加个"[ ]" //sColList += "[" + pi.Name + "],"; sColList += pi.Name + ","; } sColList = sColList.Substring(0, sColList.Length - 1); } else { //如果是oracle,不支持字段列加个"[ ]" //sColList = SqlAction.GetSQLFildList(colList); sColList = colList; } StringBuilder strSql = new StringBuilder(); string strOrder; //排序字段 if (string.IsNullOrEmpty(fldOrder)) { fldOrder = PrimaryKey; }//排序类型 if (OrderFlag == 1) { strOrder = string.Format(" order by {0} desc", fldOrder); } else { strOrder = string.Format(" order by {0} asc", fldOrder); } //没有where 条件 if (string.IsNullOrEmpty(strWhere)) { strSql.Append(string.Format("select {0} from(select {1}, rownum as id from {2} {3}", sColList, sColList, tblName, strOrder)); strSql.Append(string.Format(") a where a.id between {0} and {1}", (PageIndex - 1) * PageSize + 1, PageIndex * PageSize)); } else { strSql.Append(string.Format("select {0} from(select {1}, rownum as id from {2} ", sColList, sColList, tblName)); strSql.Append(string.Format(" where {0} {1}", strWhere,strOrder)); strSql.Append(string.Format(") a where a.id between {0} and {1}", (PageIndex - 1) * PageSize + 1, PageIndex * PageSize)); } return strSql.ToString().Replace("[", "").Replace("]", ""); } #endregion #region 批量操作 /// <summary> /// 新增和更新需要的参数列表 /// </summary> /// <param name="model"></param> /// <param name="IsAdd"></param> /// <returns></returns> public virtual List<DbParam> AddOrUpdateParamList(T model, bool IsAdd) { PropertyInfo[] ps = typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public); List<DbParam> list = new List<DbParam>(); if (ps != null) { for (int i = 0; i < ps.Length; i++) { if (IsAdd) { if (t.IsAutoId) { if (ps[i].Name == PrimaryKey) { continue; } } } if (model != null) { DbParam param = new DbParam() { ParamName = ps[i].Name, ParamDbType = TypeConvert.GetOracleDbType(ps[i].PropertyType), ParamValue = ps[i].GetValue(model, null) }; list.Add(param); } else { DbParam param = new DbParam() { ParamName = ps[i].Name, ParamDbType = TypeConvert.GetOracleDbType(ps[i].PropertyType), ParamValue = null }; list.Add(param); } } } return list; } /// <summary> /// AddOrUpdateSql,操作列为"*" /// </summary> /// <param name="IsAdd"></param> /// <returns></returns> public virtual string GetAddUpdateSql(bool IsAdd) { StringBuilder strSql = new StringBuilder(); if (IsAdd) { StringBuilder strParameter = new StringBuilder(); strSql.Append(string.Format("insert into {0}(", TableName)); PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); for (int i = 0; i < pis.Length; i++) { if (t.IsAutoId) { if (t.PrimaryKey == pis[i].Name) continue; } strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 strParameter.Append(":" + pis[i].Name + ","); //构造参数SQL语句 } strSql = strSql.Replace(",", ")", strSql.Length - 1, 1); strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1); strSql.Append(" values ("); strSql.Append(strParameter.ToString()); } else { strSql.Append("update " + TableName + " set "); PropertyInfo[] pis = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); for (int i = 0; i < pis.Length; i++) { if (pis[i].Name != PrimaryKey) { strSql.Append(pis[i].Name + "=" + ":" + pis[i].Name + ","); } //strSql.Append(" "); } strSql = strSql.Replace(",", " ", strSql.Length - 1, 1); strSql.Append(" where " + PrimaryKey + "=:" + PrimaryKey); } return strSql.ToString(); } public virtual int AddUpdateList(List<T> list, EnumAction.AddUpdateType eAdd) { int iCount = 0; bool IsAdd = false; if (eAdd == EnumAction.AddUpdateType.Add) IsAdd = true; OracleConnection conn = DbAction.getConn(); using (OracleCommand com = new OracleCommand(GetAddUpdateSql(IsAdd), conn)) { //初始化,每一个参数的值ParamValue为null List<DbParam> listParam = AddOrUpdateParamList(null, IsAdd); foreach (DbParam dbpm in listParam) { com.Parameters.Add(dbpm); //===>其实可以写为一句 } //循环sql foreach (T model in list) { //数据来自model listParam = AddOrUpdateParamList(model, IsAdd); foreach (DbParam dbpm in listParam) { //为每一个参数赋值 com.Parameters[dbpm.ParamName].Value = dbpm.ParamValue; // ====>其实可以写为一句 } if (com.ExecuteNonQuery() > 0) { iCount++; } } return iCount; } } /// <summary> /// 批量删除,以逗号分隔 /// example: 19,21,11 /// </summary> /// <param name="ids"></param> /// <returns></returns> public int DeleteList(string ids) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("delete from {0} ", TableName); if (!string.IsNullOrEmpty(ids)) { sb.AppendFormat(" where {0} in (", PrimaryKey); sb.Append(ids + " )"); } else { return 0; } OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); conn.Open(); int isok = com.ExecuteNonQuery(); conn.Close(); return isok; } /// <summary> /// 除主键外,批量删除 /// example: DeleteList("Fname","Francis,Lyfeng,Harry"); /// </summary> /// <param name="colName"></param> /// <param name="colValues"></param> /// <returns></returns> public int DeleteList(string colName, string colValues) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("delete from {0} ", TableName); if (!string.IsNullOrEmpty(colName)) { PropertyInfo[] ps = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance); bool Flag = false; for (int i = 0; i < ps.Length; i++) { if (ps[i].Name.ToLower() == colName.ToLower()) { Flag = true; break; } } if (!Flag) { throw new Exception("数据表找不到对colName指定的列值定义,请确定colName是否在数据表中列存在"); } sb.AppendFormat(" where {0} in (", colName); string[] values = colValues.Split(','); for (int i = 0; i < values.Length; i++) { values[i] = "'" + values[i] + "'"; } sb.Append(values + " )"); } else { return 0; } OracleConnection conn = DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); conn.Open(); int isok = com.ExecuteNonQuery(); conn.Close(); return isok; } #endregion } }
接口为BaseAction<T>:
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); } }
其中TableName,PrimaryKey字段属性和泛型T的实体对象,将在进行通用sql语句编程时起到作用.
BaseDal<T>实现BaseAction<T>接口的方法,并约束T在BaseModel,且允许进行new操作
需要注意的是,在BaseModel也需要要有TableName,PrimaryKey等(以后会添加其他字段属性),并且在Model中自己定义的映射实体对象必须指定PrimaryKey
比如UserInfo.cs的实体对象应该至少这样定义:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace myOracle.Model { public class UserInfo:BaseModel { public UserInfo() { //父类 base.PrimaryKey = "Fid"; base.IsAutoId = false; } private int _fid; public int Fid { get { return _fid; } set { _fid = value; } } private string _fname; public string Fname { get { return _fname; } set { _fname = value; } } private string _fpassword; public string Fpassword { get { return _fpassword; } set { _fpassword = value; } } } }
如果经过试验你会发现:json序列化的时候,如果是用List<T>的数据源会将BaseModel的属性也会一同序列化回去,这样会发现很多不必要的数据.
至此,UI层直接通过业务逻辑层就可以实现一个简单的UserInfo列表的查询.
END!