sql server and oracle 不同之处只在于：
1·参数@和：
2·自增和序列
3·oracle使用了存储过程以获得当前插入数据的ID
DAL层的ORM
using Oracle.DataAccess.Client; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DIDAO.DAL { public class OracleHelper { //链接字符串 private static readonly string connStr = ConfigurationManager.ConnectionStrings["dbconnStr"].ConnectionString; /// <summary> /// 创建链接 /// </summary> /// <returns>链接</returns> public static OracleConnection CreateConnection() { OracleConnection conn = new OracleConnection(connStr); conn.Open(); return conn; } /// <summary> /// 使用亦有链接的 非查询 /// </summary> /// <param name="conn">链接</param> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(OracleConnection conn,string sql,params OracleParameter[] parameters) { using(OracleCommand cmd=new OracleCommand(sql,conn)) { cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } /// <summary> /// 自己创建链接的 非查询 /// </summary> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>受影响行数</returns> public static int ExecuteNonQuery(string sql, params OracleParameter[] parameters) { using (OracleConnection conn = CreateConnection()) { return ExecuteNonQuery(conn, sql, parameters); } } /// <summary> /// 使用已有链接的 带存储过程的Insert非查询，直接写存储过程参数 /// </summary> /// <param name="conn">已有链接</param> /// <param name="proName">存储过程名称</param> /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> /// <param name="seqName">序列的名称</param> /// <returns>当前序列号，即ID</returns> public static object ExecuteNonQueryWithProduce(OracleConnection conn, string proName, string strInsertSQL, string seqName) { using (OracleCommand cmd = new OracleCommand(proName, conn)) //命令中执行的不在是sql，而是存储过程 { try { cmd.CommandType = CommandType.StoredProcedure; //标记该命令的类型不是sql，而是存储过程 //存储过程中有参数名称，以及设置对应参数的值 cmd.Parameters.Add(new OracleParameter("strInsertSQL", OracleDbType.Varchar2) { Value = strInsertSQL }); ////存储过程中的参入参数 strInsertSQL cmd.Parameters.Add(new OracleParameter("seqName", OracleDbType.Varchar2) { Value = seqName }); // //存储过程中的传入参数 seqName cmd.Parameters.Add(new OracleParameter("ID", OracleDbType.Int64) { Direction = ParameterDirection.Output }); //存储过程中的传出参数ID，只需要声明 //cmd.Parameters.AddRange(parameters); cmd.ExecuteNonQuery(); string newId = cmd.Parameters["ID"].Value.ToString(); //获得传出参数的ID的值 return newId; } catch(Exception ex) { throw new Exception(ex.ToString()); } } } /// <summary> /// 自己创建链接的 带存储过程的Insert非查询，直接写存储过程参数 /// </summary> /// <param name="proName">存储过程名称</param> /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> /// <param name="seqName">序列的名称</param> /// <returns>当前序列号，即ID</returns> public static object ExecuteNonQueryWithProduce(string proName, string strInsertSQL, string seqName) { using (OracleConnection conn = CreateConnection()) { return ExecuteNonQueryWithProduce(conn, proName, strInsertSQL, seqName); } } /// <summary> /// 使用已有链接的 带存储过程的Insert非查询，传存储过程参数 /// </summary> /// <param name="conn">已有链接</param> /// <param name="proName">存储过程名称</param> /// <param name="parameters">存储过程中的传入、传出参数 数组</param> /// <returns>当前序列号，即ID</returns> public static object ExecuteNonQueryWithProduce(OracleConnection conn, string proName, params OracleParameter[] parameters) { using (OracleCommand cmd = new OracleCommand(proName, conn)) //命令中执行的不在是sql，而是存储过程 { try { cmd.CommandType = CommandType.StoredProcedure; //标记该命令的类型不是sql，而是存储过程 ////存储过程中有参数名称，以及设置对应参数的值 //cmd.Parameters.Add(new OracleParameter("strInsertSQL", OracleDbType.Varchar2) { Value = strInsertSQL }); ////存储过程中的参入参数 strInsertSQL //cmd.Parameters.Add(new OracleParameter("seqName", OracleDbType.Varchar2) { Value = seqName }); // //存储过程中的传入参数 seqName //cmd.Parameters.Add(new OracleParameter("ID", OracleDbType.Int32) { Direction = ParameterDirection.Output }); //存储过程中的传出参数ID，只需要声明 cmd.Parameters.AddRange(parameters); //参数中包括存储过程的传入传出参数，以及子sql语句中的参数 --------------****----------------- int i = cmd.ExecuteNonQuery(); //直接返回执行插入之后，存储过程传出的变量值 string newId = cmd.Parameters["ID"].Value.ToString(); //获得传出参数的ID的值 return newId; } catch (Exception ex) { throw new Exception(ex.ToString()); } } } /// <summary> /// 自己创建链接的 带存储过程的Insert非查询，传存储过程参数 /// </summary> /// <param name="proName">存储过程名称</param> /// <param name="parameters">存储过程中的传入、传出参数 数组</param> /// <returns>当前序列号，即ID</returns> public static object ExecuteNonQueryWithProduce(string proName,params OracleParameter[] parameters) { using (OracleConnection conn = CreateConnection()) { return ExecuteNonQueryWithProduce(conn, proName, parameters); } } /// <summary> /// 使用已有链接的 单查询 /// </summary> /// <param name="conn">链接</param> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的一条结果</returns> public static object ExecuteScalar(OracleConnection conn,string sql,params OracleParameter[] parameters) { using(OracleCommand cmd=new OracleCommand(sql,conn)) { cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } /// <summary> /// 自己创建链接的 单查询 /// </summary> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的一条结果</returns> public static object ExecuteScalar(string sql,params OracleParameter[] parameters) { using(OracleConnection conn=CreateConnection()) { return ExecuteScalar(conn, sql, parameters); } } /// <summary> /// 使用已有链接的 reader查询 /// </summary> /// <param name="conn">链接</param> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的结果集table</returns> public static DataTable ExecuteReader(OracleConnection conn,string sql,params OracleParameter[] parameters) { DataTable table = new DataTable(); using(OracleCommand cmd=new OracleCommand(sql,conn)) { cmd.Parameters.AddRange(parameters); using (OracleDataReader reader = cmd.ExecuteReader()) { table.Load(reader); } } return table; } /// <summary> /// 自己创建链接的 reader查询 /// </summary> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的结果集table</returns> public static DataTable ExecuteReader(string sql,params OracleParameter[] parameters) { using(OracleConnection conn=CreateConnection()) { return ExecuteReader(conn, sql, parameters); } } /// <summary> /// 使用已有链接的 stream查询 /// </summary> /// <param name="conn">链接</param> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的结果流stream</returns> public static System.IO.Stream ExecuteStream(OracleConnection conn,string sql,params OracleParameter[] parameters) { using(OracleCommand cmd=new OracleCommand(sql,conn)) { cmd.Parameters.AddRange(parameters); using (System.IO.Stream stream = cmd.ExecuteStream()) { return stream; } } } /// <summary> /// 自己创建链接的stream查询 /// </summary> /// <param name="sql">sql文本</param> /// <param name="parameters">sql参数</param> /// <returns>查询到的结果流stream</returns> public static System.IO.Stream ExecuteStream(string sql, params OracleParameter[] parameters) { using(OracleConnection conn=CreateConnection()) { return ExecuteStream(conn, sql, parameters); } } } }
using Oracle.DataAccess.Client; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace DIDAO.DAL { public class MyORM_DAL { //约定： //1 实例的共有类型名称 与表名相同 //2 主键默认是ID //3 Oracle的序列名称是SE_T_ALL 或者Sql Server、MySql 主键自增 //4 通过STATUS控制删除或禁用 /// <summary> /// 获得 拼接:的列名字符串、拼接=的列名字符串、拼接参数的字符串 /// </summary> /// <param name="obj">实例</param> /// <param name="className">类型名称</param> /// <param name="propListWithConStr">拼接:的列名字符串</param> /// <param name="propListWithEqualStr">拼接=的列名字符串</param> /// <param name="paraList">拼接参数的字符串</param> private void GetArrayOrListWithConEqualPara(Object obj, out string className, out string propListWithConStr, out string propListWithEqualStr, out List<OracleParameter> paraList) { Type type = obj.GetType(); className = type.Name; PropertyInfo[] properties = type.GetProperties(); string[] propListWithCon = new string[properties.Length - 1]; string[] propListWithEqual = new string[properties.Length - 1]; paraList = new List<OracleParameter>(); //用集合，可以在更新时再加一项ID int i = 0; foreach (PropertyInfo property in properties) { string propName = property.Name; if (propName != "ID") { object propValuePro = property.GetValue(obj); object propValue = propValuePro == null ? DBNull.Value : propValuePro; propListWithCon[i] = ":" + propName; propListWithEqual[i] = propName + "=:" + propName; OracleParameter para = new OracleParameter(); para.ParameterName = ":" + propName; para.Value = propValue; paraList.Add(para); i++; } } propListWithConStr = string.Join(",", propListWithCon); propListWithEqualStr = string.Join(",", propListWithEqual); } /// <summary> /// 拼接新增sql语句中的列的value字符串 (isTimestamp ：有timestamp类型为true) /// </summary> /// <param name="obj">实例</param> /// <param name="isTimestamp">是否有Oracle中的Timestamp类型 (只要是Oracle不论Date/Timestamp 都可以to_timetamp() ： true)</param> /// <returns>新增sql语句中的列的value字符串</returns> private string JoinPropValueStr(Object obj,bool isTimestamp) { string propValueStrs = ""; Type type = obj.GetType(); PropertyInfo[] props = type.GetProperties(); foreach (PropertyInfo prop in props) { string propName = prop.Name; if (propName != "ID") { string propTyName = prop.PropertyType.Name; //属性类别名称 object propValue = prop.GetValue(obj); if (propValue == null || propValue.ToString().Length <= 0) { propValueStrs += "null,"; } else { if (propTyName == "String") { propValueStrs += "'" + propValue + "',"; } else if (propTyName == "DateTime" || propTyName == "DateTime?") { if ( isTimestamp) //如果Oracle中Date类型是 timestamp类型，需要 to_timestamp() /Date类型也可以to_timestamp() { //to_timestamp('1990-09-24','yyyy-mm-dd hh24:mi:ss.ff'), propValueStrs += "to_timestamp('" + propValue + "','yyyy-mm-dd hh24:mi:ss.ff'),"; } else { propValueStrs += "'" + propValue + "',"; //日期的格式必须是 YYYY-MM-DD 或者 yyyy-mm-dd hh24:MI:SS } } else { propValueStrs += propValue + ","; } } } } propValueStrs = propValueStrs.TrimEnd(','); //去掉最后的"," return propValueStrs; } /// <summary> /// RowToModel /// </summary> /// <param name="type">实例的类型</param> /// <param name="row">DataRow</param> /// <returns>一个实例</returns> public Object RowToModel(Type type, DataRow row) { Object obj = Activator.CreateInstance(type); PropertyInfo[] properties = type.GetProperties(); foreach (PropertyInfo property in properties) { string propName = property.Name; string propTyName = property.PropertyType.Name; object propValuePro = row[propName.ToString()]; //从数据库中取得的属性值 object propValue = propValuePro == DBNull.Value ? null : propValuePro; if(propTyName=="Decimal") { propValue = Convert.ToDecimal(propValue); } if (propType == "Decimal?") { propValue = propValue as Decimal?; } property.SetValue(obj, propValue); } return obj; } /// <summary> /// ORM新增实例 /// </summary> /// <param name="obj"></param> /// <returns>受影响行数</returns> public int InsertModel(Object obj,string seqName) { string className, propListWithConStr, propListWithEqualStr; List<OracleParameter> paraList; GetArrayOrListWithConEqualPara(obj, out className, out propListWithConStr, out propListWithEqualStr, out paraList); //拼接 StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO ").Append(className).Append(" VALUES(").Append(seqName).Append(".NEXTVAL,").Append(propListWithConStr).AppendLine(")"); string sql = sb.ToString(); return OracleHelper.ExecuteNonQuery(sql, paraList.ToArray()); } /// <summary> /// ORM新增实例 带上事务 /// </summary> /// <param name="conn">外连接</param> /// <param name="tx">事务</param> /// <param name="obj"></param> /// <returns>受影响行数</returns> public int InsertModelWithTransaction(OracleConnection conn , OracleTransaction tx, Object obj, string seqName) { string className = obj.GetType().Name; //拼接 StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO ").Append(className).Append(" VALUES(").Append(seqName).Append(".NEXTVAL,").Append(JoinPropValueStr(obj,true)).AppendLine(")"); string sql = sb.ToString(); return OracleHelper.ExecuteNonQuery(conn,tx,sql); } /// <summary> /// 调用存储过程进行新增，返回ID （---> isTimestamp 表示有tiemstamp类型） /// </summary> /// <param name="obj">实例</param> /// <param name="seqName">存储过程中的参数-序列，sql语句中参数-序列</param> /// <param name="proName">存储过程名称</param> /// <param name="isTimestamp">是否有Oracle中的Timestamp类型 (只要是Oracle不论Date/Timestamp 都可以to_timetamp() ： true)</param> /// <returns>返回当前插入语句的ID</returns> public object InsertModelWithProduce(Object obj, string seqName, string proName, bool isTimestamp) { string className = obj.GetType().Name; List<OracleParameter> produceParaList = new List<OracleParameter>(); StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO ").Append(className).Append(" VALUES(").Append(seqName).Append(".NEXTVAL,").Append(JoinPropValueStr(obj, isTimestamp)).AppendLine(")"); string strInsertSQL = sb.ToString(); #region 冗余 ////return OracleHelper.ExecuteNonQuery(sql, paraList.ToArray()); ////object id = OracleHelper.ExecuteNonQuery("PR_GETITEMID", sql, "SE_T_ALL"); ////首先 sql语句中的参数 sql语句中SE_T_ALL参数 必须位于集合的第一位 ,序列名称在存储过程中已有传参 #endregion //只传 存储过程中的参数 produceParaList.AddRange(new List<OracleParameter>(){ new OracleParameter("strInsertSQL", OracleDbType.Varchar2) { Value = strInsertSQL }, //存储过程中的参入参数 strInsertSQL new OracleParameter("seqName", OracleDbType.Varchar2) { Value = seqName }, //存储过程中的传入参数 seqName new OracleParameter("ID", OracleDbType.Int64) { Direction=ParameterDirection.Output} //存储过程中的传出参数ID，只需要声明 }); object id = OracleHelper.ExecuteNonQueryWithProduce(proName, produceParaList.ToArray()); return id; } /// <summary> /// 调用存储过程进行新增，返回ID （sql直接拼接，没有参数 ---> isTimestamp 表示有tiemstamp类型） /// </summary> /// <param name="proName">存储过程名称</param> /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> /// <param name="seqName">序列的名称</param> /// <param name="isTimestamp">是否有Oracle中的Timestamp类型 (只要是Oracle不论Date/Timestamp 都可以to_timetamp() ： true)</param> /// <returns>当前序列号，即ID</returns> public object InsertModelWithProduceForDirectSQL(Object obj, string seqName, string proName,bool isTimestamp) { string className = obj.GetType().Name; List<OracleParameter> produceParaList = new List<OracleParameter>(); StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO ").Append(className).Append(" VALUES(").Append(seqName).Append(".NEXTVAL,").Append(JoinPropValueStr(obj,isTimestamp)).AppendLine(")"); string strInsertSQL = sb.ToString(); return OracleHelper.ExecuteNonQueryWithProduce(proName, strInsertSQL, seqName); } /// <summary> /// ORM跟新实例 /// </summary> /// <param name="obj"></param> /// <returns>受影响行数</returns> public int UpdateModel(Object obj) { string className, propListWithConStr, propListWithEqualStr; List<OracleParameter> paraList; GetArrayOrListWithConEqualPara(obj, out className, out propListWithConStr, out propListWithEqualStr, out paraList); StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(" SET ").Append(propListWithEqualStr).AppendLine(" WHERE ID=:ID"); string sql = sb.ToString(); //获得obj的第一个属性ID的值 object id = obj.GetType().GetProperty("ID").GetValue(obj); paraList.Add(new OracleParameter(":ID", id)); return OracleHelper.ExecuteNonQuery(sql, paraList.ToArray()); } /// <summary> /// 根据某字段更新 未删除的另一个字段 /// </summary> /// <param name="type">实例的类型</param> /// <param name="updateFieldStr">待更新的字段字段串："ACTIVE='T'"</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="FieldStr">条件字符串："USERNAME='yang'"</param> /// <returns>受影响行数</returns> public int UpdateFieldByField(Type type, string updateFieldStr, int status, string FieldStr) { //string sql = @"UPDATE TD_USER SET ACTIVE='T' WHERE STATUS=1 AND USERNAME='yang'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(string.Format(" SET {0} WHERE STATUS={1} AND {2}",updateFieldStr,status,FieldStr)); return OracleHelper.ExecuteNonQuery(sb.ToString()); } /// <summary> /// 根据某字段更新 未删除的另一个字段 /// </summary> /// <param name="type">实例的类型</param> /// <param name="updateFieldStr">待更新的字段字段串："ACTIVE='T'"</param> /// <param name="FieldStr">条件字符串："USERNAME='yang'"</param> /// <returns>受影响行数</returns> public int UpdateFieldByField(Type type, string updateFieldStr, string FieldStr) { //string sql = @"UPDATE TD_USER SET ACTIVE='T' WHERE STATUS=1 AND USERNAME='yang'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(string.Format(" SET {0} WHERE {1}", updateFieldStr, FieldStr)); return OracleHelper.ExecuteNonQuery(sb.ToString()); } /// <summary> /// 根据某字段更新 另一个字段（使用事务） /// </summary> /// <param name="conn">外链接</param> /// <param name="tx">该链接的事务</param> /// <param name="type">实例的类型</param> /// <param name="updateFieldStr">待更新的字段字段串："ACTIVE='T'"</param> /// <param name="fieldStr">条件字符串："CARDNUM='C1344341'"</param> /// <returns>受影响行数</returns> public int UpdateFieldByField(OracleConnection conn , OracleTransaction tx,Type type, string updateFieldStr, string fieldStr) { //string sql = @"UPDATE TD_LEARNCARD SET ACTIVE='T' WHERE CARDNUM='C1344341'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(string.Format(" SET {0} WHERE {1}", updateFieldStr, fieldStr)); return OracleHelper.ExecuteNonQuery(conn, tx, sb.ToString()); } /// <summary> /// 通过更新状态码 删除或禁用实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="id">主键</param> /// <returns>受影响行数</returns> public int DeleteOrDisableModelByUpdateStatus(Type type,int status,int id) { //string sql = "UPDATE TD_CHAPTER SET STATUS=:STATUS WHERE ID=:ID"; //约定：通过STATUS控制删除或禁用，主键为ID string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(" SET STATUS=:STATUS WHERE ID=:ID"); return OracleHelper.ExecuteNonQuery(sb.ToString(), new OracleParameter(":STATUS", status), new OracleParameter(":ID", id)); } /// <summary> /// 通过更新状态码 删除或禁用实例，并记录操作者和操作时间 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="deleteby">删除或禁用者</param> /// <param name="deleteday">删除或禁用时间</param> /// <param name="id">主键</param> /// <returns>受影响行数</returns> public int DeleteOrDisableModelByUpdateStatus(Type type, int status, long deleteby, DateTime deleteday, int id) { //string sql = "UPDATE TD_CHAPTER SET STATUS=:STATUS,DELETEBY=:DELETEBY,DELETEDATE=to_timestamp(:DELETEDATE,'yyyy-mm-dd hh24:mi:ss.ff') WHERE ID=:ID"; //约定：通过STATUS控制删除或禁用，主键为ID string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(" SET STATUS=:STATUS,DELETEBY=:DELETEBY,DELETEDATE=:DELETEDATE WHERE ID=:ID"); return OracleHelper.ExecuteNonQuery(sb.ToString(), new OracleParameter(":STATUS", status), new OracleParameter(":DELETEBY", deleteby), new OracleParameter(":DELETEDATE", deleteday), new OracleParameter(":ID", id)); } /// <summary> /// 批量删除或禁用 所有id包含在idList集合中的数据 (需要先检查 idList都是数字字符串) /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="idList">id字符串表示：1,2,3,4 </param> /// <returns>受影响行数</returns> public int DeleteOrDiableModelByIdList(Type type, int status, string idList) { //string sql = "UPDATE TD_ADMIN SET STATUS=:STATUS WHERE ID IN (:IdList);"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(" SET STATUS=:STATUS WHERE ID IN (").Append(idList).Append(")"); return OracleHelper.ExecuteNonQuery(sb.ToString(), new OracleParameter(":STATUS", status)); } /// <summary> /// 批量删除或禁用 所有id包含在idList集合中的数据 (需要先检查 idList都是数字字符串) --并记录操作者和操作时间 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="deleteby">删除或禁用者</param> /// <param name="deleteday">删除或禁用时间</param> /// <param name="idList">id字符串表示：1,2,3,4 </param> /// <returns>受影响行数</returns> public int DeleteOrDiableModelByIdList(Type type, int status, long deleteby, DateTime deleteday, string idList) { //string sql = "UPDATE TD_ADMIN SET STATUS=:STATUS,DELETEBY=:DELETEBY,DELETEDATE=to_timestamp(:DELETEDATE,'yyyy-mm-dd hh24:mi:ss.ff') WHERE ID IN (:IdList);"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("UPDATE ").Append(className).Append(" SET STATUS=:STATUS,DELETEBY=:DELETEBY,DELETEDATE=to_timestamp(:DELETEDATE,'yyyy-mm-dd hh24:mi:ss.ff') WHERE ID IN (").Append(idList).Append(")"); return OracleHelper.ExecuteNonQuery(sb.ToString(), new OracleParameter(":STATUS", status), new OracleParameter(":DELETEBY", deleteby), new OracleParameter(":DELETEDATE", deleteday) ); } /// <summary> /// 根据字段集合 删除 /// </summary> /// <param name="type">实例的公有类型</param> /// <param name="fieldName">字段名称：ID /ADMINID</param> /// <param name="fieldListStr">字段集合的字符串表示：1,2,3,4 </param> /// <returns>受影响行数</returns> public int DeleteModelByFieldList(Type type, string fieldName,string fieldListStr=null) { // DELETE FROM TD_ADMIN_ROLE WHERE ADMINID IN (:fieldListStr) string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM ").Append(className); if (fieldListStr !=null) { sb.Append(" WHERE ").Append(fieldName).Append(" IN (").Append(fieldListStr).Append(")"); } return OracleHelper.ExecuteNonQuery(sb.ToString()); } /// <summary> /// 根据ID删除实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="id">ID</param> /// <returns>受影响行数</returns> public int DeleteModelById(Type type, int id) { string className = type.Name; string sql = "DELETE FROM " + className + " WHERE ID=:ID"; return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(":ID", id)); } /// <summary> /// 根据某字段 彻底删除实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="field">字段字符串： username='yzk'</param> /// <returns>是否彻底删除成功</returns> public int DeleteModelByField(Type type,string field) { string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM ").Append(className); if(field!=null) { sb.Append(" WHERE ").Append(field); } return OracleHelper.ExecuteNonQuery(sb.ToString()); } /// <summary> /// 根据某字段 彻底删除实例 /// </summary> /// <param name="conn">外连接</param> /// <param name="tx">共同的事务</param> /// <param name="type">实例的类型</param> /// <param name="field">字段字符串： username='yzk'</param> /// <returns>是否彻底删除成功</returns> public int DeleteModelByField(OracleConnection conn,OracleTransaction tx,Type type, string field) { string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM ").Append(className); if (field != null) { sb.Append(" WHERE ").Append(field); } return OracleHelper.ExecuteNonQuery(conn,tx,sb.ToString()); } /// <summary> /// 根据ID查询实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="id">ID</param> /// <returns>一个实例</returns> public Object SelectModelById(Type type, int id) { string className=type.Name; string sql = "SELECT * FROM " + className + " WHERE ID=:ID"; DataTable dt = OracleHelper.ExecuteReader(sql,new OracleParameter(":ID",id)); if(dt.Rows.Count<=0) { return null; } else if (dt.Rows.Count == 1) { return RowToModel(type, dt.Rows[0]); } else { throw new Exception("出错：数据在这里重复，其ID是：" + id); } } /// <summary> /// 根据ID查询 未删除实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="id">ID</param> /// <returns>一个实例</returns> public Object SelectModelById(Type type,long status, int id) { string className = type.Name; string sql = "SELECT * FROM " + className + " WHERE STATUS=:STATUS AND ID=:ID"; DataTable dt = OracleHelper.ExecuteReader(sql, new OracleParameter(":STATUS", status), new OracleParameter(":ID", id)); if (dt.Rows.Count <= 0) { return null; } else if (dt.Rows.Count == 1) { return RowToModel(type, dt.Rows[0]); } else { throw new Exception("出错：数据在这里重复，其ID是：" + id); } } /// <summary> /// 根据ID查询 未删除实例（使用外连接） /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="id">ID</param> /// <returns>一个实例</returns> public Object SelectModelById(OracleConnection conn,Type type, long status, int id) { string className = type.Name; string sql = "SELECT * FROM " + className + " WHERE STATUS=:STATUS AND ID=:ID"; DataTable dt = OracleHelper.ExecuteReader(conn,sql, new OracleParameter(":STATUS", status), new OracleParameter(":ID", id)); if (dt.Rows.Count <= 0) { return null; } else if (dt.Rows.Count == 1) { return RowToModel(type, dt.Rows[0]); } else { throw new Exception("出错：数据在这里重复，其ID是：" + id); } } /// <summary> /// 根据ID查询 未删除实例（使用外连接，共同事务） /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="id">ID</param> /// <returns>一个实例</returns> public Object SelectModelById(OracleConnection conn,OracleTransaction tx, Type type, long status, int id) { string className = type.Name; string sql = "SELECT * FROM " + className + " WHERE STATUS=:STATUS AND ID=:ID"; DataTable dt = OracleHelper.ExecuteReader(conn, sql, new OracleParameter(":STATUS", status), new OracleParameter(":ID", id)); if (dt.Rows.Count <= 0) { return null; } else if (dt.Rows.Count == 1) { return RowToModel(type, dt.Rows[0]); } else { throw new Exception("出错：数据在这里重复，其ID是：" + id); } } /// <summary> /// 根据某字段字符串查询 实例 /// </summary> /// <param name="type">实例类型</param> /// <param name="fieldStr">条件字符串：username='yzk'，默认为null</param> /// <returns>满足条件的实例集合</returns> public List<Object> SelectModelByField(Type type, string fieldStr) { string className = type.Name; List<Object> list = new List<object>(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ").Append(className); if (fieldStr != null) { sb.Append(" Where ").AppendLine(fieldStr); } DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); foreach (DataRow row in dt.Rows) { list.Add(RowToModel(type, row)); } return list; } /// <summary> /// 根据某字段字符串 排序后 查询未删除实例 /// </summary> /// <param name="type">实例类型</param> /// <param name="fieldStr">条件字符串：Category=1</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="fieldName">排序字段名称</param> /// <returns>排序后的 未删除实例</returns> public List<object> SelectModelByField(Type type, string fieldStr, long status, string fieldName) { string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ").Append(className).Append(" WHERE STATUS=").Append(status).Append(" AND ").Append(fieldStr).Append(" ORDER BY ").Append(fieldName).Append(" ASC"); DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); List<object> list = new List<object>(); foreach (DataRow row in dt.Rows) { list.Add(RowToModel(type, row)); } return list; } /// <summary> /// 根据用户名 查询未删除得 激活的用户 /// </summary> /// <param name="type">实例类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="isActive">是否激活：T激活，F未激活</param> /// <param name="username">用户名</param> /// <returns>查询未删除得 激活的用户</returns> public object SelectModelByUserName(Type type, long status, string isActive, string username) { string sql = @"SELECT * FROM TD_USER WHERE STATUS=1 AND ACTIVE='T' AND USERNAME='yang'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ").Append(className).Append(string.Format(" WHERE STATUS={0} AND ACTIVE='{1}' AND USERNAME='{2}'", status, isActive, username)); DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); if(dt.Rows.Count>1) { throw new Exception("数据库错误：用户名重复="+username); } else if(dt.Rows.Count<1) { return null; } return RowToModel(type, dt.Rows[0]); } /// <summary> /// 根据唯一字段 查询未删除得 实例 /// </summary> /// <param name="type">实例类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="fieldStr">唯一字段："NUM='123'"</param> /// <returns>查询未删除得 实例</returns> public object SelectModelByOnlyField(Type type, long status, string fieldStr) { string sql = @"SELECT * FROM TD_MARCHANT WHERE STATUS=1 AND NUM='2'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ").Append(className).Append(string.Format(" WHERE STATUS={0} AND {1}", status, fieldStr)); DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); if (dt.Rows.Count > 1) { throw new Exception("数据库错误：唯一字段重复=" + fieldStr); } else if (dt.Rows.Count < 1) { return null; } return RowToModel(type, dt.Rows[0]); } /// <summary> /// 查询所有的实例 /// </summary> /// <param name="type">实例的类型</param> /// <returns>所有实例</returns> public List<Object> SelectAllModel(Type type) { string className=type.Name; List<Object> list = new List<object>(); string sql = "SELECT * FROM " + className; DataTable dt = OracleHelper.ExecuteReader(sql); foreach(DataRow row in dt.Rows) { list.Add(RowToModel(type, row)); } return list; } /// <summary> /// 查询所有 未删除的实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <returns>所有 未删除的实例</returns> public List<Object> SelectAllModel(Type type,int status) { string className = type.Name; List<Object> list = new List<object>(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ").Append(className).Append(string.Format(" WHERE STATUS={0}",status)); DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); foreach (DataRow row in dt.Rows) { list.Add(RowToModel(type, row)); } return list; } /// <summary> /// 查询指定条件下的条数 /// </summary> /// <param name="type">实例的类型</param> /// <param name="fieldStr">指定字段，如："USERNAME='USERNAME'" </param> /// <returns>数据条数</returns> public long SelectCountByField(Type type,string fieldStr) { string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("select count(*) from ").Append(className); if(fieldStr!=null) { sb.Append(" where ").AppendLine(fieldStr); } string sql = sb.ToString(); object obj = OracleHelper.ExecuteScalar(sql); return Convert.ToInt32(obj); } /// <summary> /// 查询指定条件下 未删除的条数 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="fieldStr">指定字段，如："USERNAME='USERNAME'" </param> /// <returns>数据条数</returns> public long SelectCountByField(Type type,int status, string fieldStr) { string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("select count(*) from ").Append(className).Append(" where STATUS=").Append(status); if (fieldStr != null) { sb.Append(" AND ").AppendLine(fieldStr); } string sql = sb.ToString(); object obj = OracleHelper.ExecuteScalar(sql); return Convert.ToInt32(obj); } /// <summary> /// 查询指定条件下 激活的条数 /// </summary> /// <param name="type">实例的类型</param> /// <param name="isActive">是否激活：T激活，2未激活</param> /// <param name="fieldStr">指定字段，如："EMAIL='qww@123.com'"</param> /// <returns></returns> public long SelectCountByActiveAndFeild(Type type,string isActive,string fieldStr) { //string sql = @"SELECT COUNT(*) FROM TD_USER WHERE ACTIVE='T' AND EMAIL='qww@123.com'"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) FROM ").Append(className).Append(string.Format(" WHERE ACTIVE='{0}' AND {1}", isActive, fieldStr)); object obj = OracleHelper.ExecuteScalar(sb.ToString()); return Convert.ToInt64(obj); } /// <summary> /// 根据ID查询状态：（只适用 TD_ADMIN) １ 正常 ，2 删除，3 禁用 /// </summary> /// <param name="id"></param> /// <returns>状态： １ 正常 ，2 删除，3 禁用</returns> public object SelectStatusById(long id) { //string sql = "SELECT STATUS FROM TD_ADMIN WHERE ID=:ID"; StringBuilder sb = new StringBuilder(); sb.Append("SELECT STATUS FROM TD_ADMIN WHERE ID=").Append(id); return OracleHelper.ExecuteScalar(sb.ToString()); } /// <summary> /// 根据ID查询状态： １ 正常 ，2 删除，3 禁用 /// </summary> /// <param name="type">实例的类型</param> /// <param name="id"></param> /// <returns>状态： １ 正常 ，2 删除，3 禁用</returns> public object SelectStatusById(Type type,long id) { //string sql = "SELECT STATUS FROM TD_ADMIN WHERE ID=:ID"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT STATUS FROM ").Append(className).Append(" WHERE ID=").Append(id); return OracleHelper.ExecuteScalar(sb.ToString()); } /// <summary> /// 根据用户名查询状态：１ 正常 ，2 删除，3 禁用 /// </summary> /// <param name="type">实例的类型</param> /// <param name="username">用户名</param> /// <returns>状态： １ 正常 ，2 删除，3 禁用</returns> public object SelectStatusByUserName(Type type, string username) { string sql = @"SELECT STATUS FROM TD_USER WHERE USERNAME=:USERNAME"; string className = type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT STATUS FROM ").Append(className).Append(string.Format(" WHERE USERNAME='{0}'", username)); return OracleHelper.ExecuteScalar(sb.ToString()); } /// <summary> /// 直接根据拼接的sql语句查询实例集合 /// </summary> /// <param name="type">实例的类型</param> /// <param name="sql"></param> /// <returns>实例集合</returns> public List<object> SelectModelsBySql(Type type,string sql) { DataTable dt = OracleHelper.ExecuteReader(sql); List<object> list=new List<object>(); foreach(DataRow row in dt.Rows) { list.Add(RowToModel(type,row)); } return list; } /// <summary> /// 根据rownum分页查询 未删除的实例 /// </summary> /// <param name="type">类型的实例</param> /// <param name="orderField">分页rownum的排序字段：ID</param> /// <param name="status">状态：1正常，2删除，3禁用</param> /// <param name="startnum">rownum起始数</param> /// <param name="endnum">rownum结束数</param> /// <returns>返回 分页查询后的实例集合</returns> public List<object> SelectModelsByRownum(Type type,string orderField,int status,long startnum,long endnum) { //string sql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER(order by ID ASC) num,ID,USERNAME,REALNAME,MOBILE,EMAIL,IDCARD,ADDRESS FROM TD_ADMIN WHERE STATUS=1 ) T WHERE T.num BETWEEN :startnum AND :endnum"; string className=type.Name; StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER(order by ").Append(orderField).Append(" ASC) num,"); string propNameStr = JoinPropNameStr(type); sb.Append(propNameStr); sb.Append(" FROM ").Append(className).Append(string.Format(" WHERE STATUS={0} ) T WHERE T.num BETWEEN {1} AND {2}", status, startnum, endnum)); DataTable dt = OracleHelper.ExecuteReader(sb.ToString()); List<object> list = new List<object>(); foreach (DataRow row in dt.Rows) { list.Add(RowToModel(type, row)); } return list; } /// <summary> /// 拼接 属性名字符串 （包括ID） /// </summary> /// <param name="type">类型的实例</param> /// <returns>拼接 属性名字符串 （包括ID）</returns> private string JoinPropNameStr(Type type) { PropertyInfo[] props = type.GetProperties(); string[] propNameArr = new string[props.Length]; for (int i = 0; i < props.Length; i++) { propNameArr[i] = props[i].Name; } string propNameStr = string.Join(",", propNameArr); return propNameStr; } } }
BLL层的ORM（多余）
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using DIDAO.DAL; namespace DIDAO.BLL { public class MyORM_BLL { MyORM_DAL myORM_DAL = new MyORM_DAL(); /// <summary> /// ORM新增实例 /// </summary> /// <param name="obj">实例</param> /// <returns>是否新增成功</returns> public bool InsertModel(Object obj,string seqName) { return myORM_DAL.InsertModel(obj,seqName) > 0; } /// <summary> /// 调用存储过程进行新增，返回ID （---> isTimestamp 表示有tiemstamp类型） /// </summary> /// <param name="obj">实例</param> /// <param name="seqName">存储过程中的参数-序列，sql语句中参数-序列</param> /// <param name="proName">存储过程名称</param> /// <param name="isTimestamp">是否有Oracle中的Timestamp类型 (只要是Oracle不论Date/Timestamp 都可以to_timetamp() ： true)</param> /// <returns>返回当前插入语句的ID</returns> public object InsertModelWithProduce(Object obj, string seqName, string proName, bool isTimestamp) { return myORM_DAL.InsertModelWithProduce(obj, seqName, proName,isTimestamp); } /// <summary> /// 调用存储过程进行新增，返回ID （sql直接拼接，没有参数 ---> isTimestamp 表示有tiemstamp类型） /// </summary> /// <param name="proName">存储过程名称</param> /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> /// <param name="seqName">序列的名称</param> /// <param name="isTimestamp">是否有Oracle中的Timestamp类型 (只要是Oracle不论Date/Timestamp 都可以to_timetamp() ： true)</param> /// <returns>当前序列号，即ID</returns> public object InsertModelWithProduceForDirectSQL(Object obj, string seqName, string proName, bool isTimestamp) { return myORM_DAL.InsertModelWithProduceForDirectSQL(obj, seqName, proName, isTimestamp); } /// <summary> /// ORM跟新实例 /// </summary> /// <param name="obj">实例</param> /// <returns>是否更新成功</returns> public bool UpdateModel(Object obj) { return myORM_DAL.UpdateModel(obj) > 0; } /// <summary> /// 根据ID查询实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="id">ID</param> /// <returns>一个实例</returns> public Object SelectModelById(Type type, int id) { return myORM_DAL.SelectModelById(type, id); } /// <summary> /// 按条件查询实例集合 /// </summary> /// <param name="type">实例类型</param> /// <param name="fieldStr">条件字符串：username='yzk'，默认为null</param> /// <returns>满足条件的实例集合</returns> public List<Object> SelectModelByField(Type type, string fieldStr = null) { return myORM_DAL.SelectModelByField(type, fieldStr); } /// <summary> /// 查询所有de实例 /// </summary> /// <param name="type">实例的类型</param> /// <returns>所有实例</returns> public List<Object> SelectAllModel(Type type) { return myORM_DAL.SelectAllModel(type); } /// <summary> /// 根据ID查询状态： １ 正常 ，2 删除，3 禁用 /// </summary> /// <param name="id"></param> /// <returns>状态： １ 正常 ，2 删除，3 禁用</returns> public object SelectStatusById(long id) { return myORM_DAL.SelectStatusById(id); } /// <summary> /// 根据ID删除实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="id">ID</param> /// <returns>是否删除成功</returns> public bool DeleteModelById(Type type, int id) { return myORM_DAL.DeleteModelById(type, id) > 0; } /// <summary> /// 根据某字段 彻底删除实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="field">字段字符串： username='yzk'</param> /// <returns>是否彻底删除成功</returns> public bool DeleteModelByField(Type type, string field=null) { return myORM_DAL.DeleteModelByField(type, field) > 0; } /// <summary> /// 批量删除或禁用 所有id包含在idList集合中的数据 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="idList">id字符串表示：1,2,3,4 </param> /// <returns>受影响行数</returns> public bool DeleteOrDiableModelByIdList(Type type, int status, string idList) { return myORM_DAL.DeleteOrDiableModelByIdList(type, status, idList) > 0; } /// <summary> /// 通过更新状态码 删除或禁用实例 /// </summary> /// <param name="type">实例的类型</param> /// <param name="status">状态码：1 正常，2 删除，3 禁用</param> /// <param name="id">主键</param> /// <returns>受影响行数</returns> public bool DeleteOrDisableModelByUpdateStatus(Type type, int status, int id) { return myORM_DAL.DeleteOrDisableModelByUpdateStatus(type, status, id) > 0; } /// <summary> /// 查询指定条件下的条数 /// </summary> /// <param name="type">实例的类型</param> /// <param name="fieldStr">指定字段，如："USERNAME='USERNAME'" </param> /// <returns>数据条数</returns> public long SelectCountByField(Type type, string fieldStr=null) { return myORM_DAL.SelectCountByField(type, fieldStr); } /// <summary> /// 直接根据拼接的sql语句查询实例集合 /// </summary> /// <param name="type">实例的类型</param> /// <param name="sql"></param> /// <returns>实例集合</returns> public List<object> SelectModelsBySql(Type type, string sql) { return myORM_DAL.SelectModelsBySql(type, sql); } } }
MODEL层
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace Console_ORM 8 { 9 public class T_USERINFO 10 { 11 public Int32 ID { get; set; } 12 13 public String USERNAME { get; set; } 14 15 public String PWD { get; set; } 16 17 public String MOBILE { get; set; } 18 19 public String EMAIL { get; set; } 20 21 public String ADDRESS { get; set; } 22 23 public Int32 GENDER { get; set; } 24 25 26 } 27 }
