轻量级ORM
? ? 不知道可能稱的上是ORM，其實就是一個DBHelper。看到網上不少人寫自己的ORM，但我覺得都不太好。
? ? 我這個ORM，學習成本非常低，常用的方法僅有幾個，在使用的過程中，你不需要有太多要注意的地方，也不會有“我怎樣實現連表查詢”的疑問。反射犧牲一些性能，但是降低了實現和使用的復雜度。
? ? 支持Oracle、MSSQL、MySQL、SQLite四種數據庫，并配有Model生成器。
Model層的設計：
? ? Models目錄中的類及其屬性和數據庫中的表和字段是完全對應的，Models全部由生成器生成，并且不允許手動修改。ExtModels目錄中的類是擴展類，主要用于查詢與顯示，比如表中存的是code，但你需要關聯查詢另一張表中的name，就可以在這個擴展類中擴展一個用于顯示的name字段。Models和ExtModels目錄的中類都是partial修飾。
? ? 例(由于是SQLite數據庫，所以生成的Model沒有注釋，其它三種數據庫有注釋)：
Model：
using System; using System.Collections.Generic; using System.Linq;namespace Models {/// <summary>/// /// </summary> [Serializable]public partial class BS_Template{/// <summary>/// /// </summary> [IsId][IsDBField]public string id { get; set; }/// <summary>/// /// </summary> [IsDBField]public string typeCode { get; set; }/// <summary>/// /// </summary> [IsDBField]public string type { get; set; }/// <summary>/// /// </summary> [IsDBField]public string code { get; set; }/// <summary>/// /// </summary> [IsDBField]public string name { get; set; }/// <summary>/// /// </summary> [IsDBField]public string path { get; set; }/// <summary>/// /// </summary> [IsDBField]public string folder { get; set; }/// <summary>/// /// </summary> [IsDBField]public string remarks { get; set; }} } View CodeExtModel：
using System; using System.Collections.Generic; using System.Linq;namespace Models {/// <summary>/// /// </summary>public partial class BS_Template{//暫沒有擴展字段 } } View Code?
DBHelper代碼：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.Data.Common; using System.Data.Objects.DataClasses; using System.Data.OracleClient; using System.Data.SqlClient; using System.Data.SQLite; using System.Reflection; using System.Text; using Models; using MySql.Data.MySqlClient;namespace DBUtil {/// <summary>/// 數據庫操作類/// 2016年09月09日/// </summary>public class DBHelper{#region 變量/// <summary>/// 數據庫類型/// </summary>private static string m_DBType = ConfigurationManager.AppSettings["DBType"];/// <summary>/// 數據庫類型/// </summary>private static bool m_AutoIncrement = ConfigurationManager.AppSettings["AutoIncrement"].ToLower() == "true" ? true : false;/// <summary>/// 數據庫連接字符串/// </summary>private static string m_ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();/// <summary>/// 事務/// </summary> [ThreadStatic]private static DbTransaction m_Tran;/// <summary>/// 帶參數的SQL插入和修改語句中，參數前面的符號/// </summary>private static string m_ParameterMark = GetParameterMark();#endregion#region 構造函數/// <summary>/// 數據庫操作類/// </summary>public DBHelper(){}#endregion#region 生成變量#region 生成 IDbCommand/// <summary>/// 生成 IDbCommand/// </summary>private DbCommand GetCommand(){DbCommand command = null;switch (m_DBType){case "oracle":command = new OracleCommand();break;case "mssql":command = new SqlCommand();break;case "mysql":command = new MySqlCommand();break;case "sqlite":command = new SQLiteCommand();break;}return command;}/// <summary>/// 生成 IDbCommand/// </summary>private DbCommand GetCommand(string sql, DbConnection conn){DbCommand command = null;switch (m_DBType){case "oracle":command = new OracleCommand(sql);command.Connection = conn;break;case "mssql":command = new SqlCommand(sql);command.Connection = conn;break;case "mysql":command = new MySqlCommand(sql);command.Connection = conn;break;case "sqlite":command = new SQLiteCommand(sql);command.Connection = conn;break;}return command;}#endregion#region 生成 IDbConnection/// <summary>/// 生成 IDbConnection/// </summary>private static DbConnection GetConnection(){DbConnection conn = null;switch (m_DBType){case "oracle":conn = new OracleConnection(m_ConnectionString);break;case "mssql":conn = new SqlConnection(m_ConnectionString);break;case "mysql":conn = new MySqlConnection(m_ConnectionString);break;case "sqlite":conn = new SQLiteConnection(m_ConnectionString);break;}return conn;}#endregion#region 生成 IDbDataAdapter/// <summary>/// 生成 IDbDataAdapter/// </summary>private DbDataAdapter GetDataAdapter(DbCommand cmd){DbDataAdapter dataAdapter = null;switch (m_DBType){case "oracle":dataAdapter = new OracleDataAdapter();dataAdapter.SelectCommand = cmd;break;case "mssql":dataAdapter = new SqlDataAdapter();dataAdapter.SelectCommand = cmd;break;case "mysql":dataAdapter = new MySqlDataAdapter();dataAdapter.SelectCommand = cmd;break;case "sqlite":dataAdapter = new SQLiteDataAdapter();dataAdapter.SelectCommand = cmd;break;}return dataAdapter;}#endregion#region 生成 m_ParameterMark/// <summary>/// 生成 m_ParameterMark/// </summary>private static string GetParameterMark(){switch (m_DBType){case "oracle":return ":";case "mssql":return "@";case "mysql":return "@";case "sqlite":return ":";}return ":";}#endregion#region 生成 DbParameter/// <summary>/// 生成 DbParameter/// </summary>private DbParameter GetDbParameter(string name, object value){DbParameter dbParameter = null;switch (m_DBType){case "oracle":dbParameter = new OracleParameter(name, value);break;case "mssql":dbParameter = new SqlParameter(name, value);break;case "mysql":dbParameter = new MySqlParameter(name, value);break;case "sqlite":dbParameter = new SQLiteParameter(name, value);break;}return dbParameter;}#endregion#endregion#region 基礎方法#region 執行簡單SQL語句#region Existspublic bool Exists(string sqlString){using (DbConnection conn = GetConnection()){using (DbCommand cmd = GetCommand(sqlString, conn)){try{conn.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return false;}else{return true;}}catch (Exception ex){throw ex;}finally{cmd.Dispose();conn.Close();}}}}#endregion#region 執行SQL語句，返回影響的記錄數/// <summary>/// 執行SQL語句，返回影響的記錄數/// </summary>/// <param name="sqlString">SQL語句</param>/// <returns>影響的記錄數</returns>public int ExecuteSql(string sqlString){DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;using (DbCommand cmd = GetCommand(sqlString, conn)){try{if (conn.State != ConnectionState.Open) conn.Open();if (m_Tran != null) cmd.Transaction = m_Tran;int rows = cmd.ExecuteNonQuery();return rows;}catch (Exception ex){throw new Exception(ex.Message);}finally{cmd.Dispose();if (m_Tran == null) conn.Close();}}}#endregion#region 執行一條計算查詢結果語句，返回查詢結果/// <summary>/// 執行一條計算查詢結果語句，返回查詢結果（object）/// </summary>/// <param name="sqlString">計算查詢結果語句</param>/// <returns>查詢結果（object）</returns>public object GetSingle(string sqlString){using (DbConnection conn = GetConnection()){using (DbCommand cmd = GetCommand(sqlString, conn)){try{if (conn.State != ConnectionState.Open) conn.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (Exception ex){throw ex;}finally{cmd.Dispose();}}}}#endregion#region 執行查詢語句，返回IDataReader/// <summary>/// 執行查詢語句，返回IDataReader ( 注意：調用該方法后，一定要對IDataReader進行Close )/// </summary>/// <param name="sqlString">查詢語句</param>/// <returns>IDataReader</returns>public DbDataReader ExecuteReader(string sqlString){DbConnection conn = GetConnection();DbCommand cmd = GetCommand(sqlString, conn);try{if (conn.State != ConnectionState.Open) conn.Open();DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);return myReader;}catch (Exception ex){throw ex;}}#endregion#region 執行查詢語句，返回DataSet/// <summary>/// 執行查詢語句，返回DataSet/// </summary>/// <param name="sqlString">查詢語句</param>/// <returns>DataSet</returns>public DataSet Query(string sqlString){using (DbConnection conn = GetConnection()){DataSet ds = new DataSet();try{conn.Open();using (DbCommand cmd = GetCommand(sqlString, conn)){DbDataAdapter adapter = GetDataAdapter(cmd);adapter.Fill(ds, "ds");}}catch (Exception ex){throw ex;}finally{conn.Close();}return ds;}}#endregion#endregion#region 執行帶參數的SQL語句#region 執行SQL語句，返回影響的記錄數/// <summary>/// 執行SQL語句，返回影響的記錄數/// </summary>/// <param name="SQLString">SQL語句</param>/// <returns>影響的記錄數</returns>public int ExecuteSql(string SQLString, params DbParameter[] cmdParms){DbConnection conn = m_Tran == null ? GetConnection() : m_Tran.Connection;using (DbCommand cmd = GetCommand()){try{PrepareCommand(cmd, conn, m_Tran, SQLString, cmdParms);int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (Exception ex){throw ex;}finally{cmd.Dispose();if (m_Tran == null) conn.Close();}}}#endregion#region 執行查詢語句，返回IDataReader/// <summary>/// 執行查詢語句，返回IDataReader ( 注意：調用該方法后，一定要對IDataReader進行Close )/// </summary>/// <param name="strSQL">查詢語句</param>/// <returns>IDataReader</returns>public DbDataReader ExecuteReader(string sqlString, params DbParameter[] cmdParms){DbConnection conn = GetConnection();DbCommand cmd = GetCommand();try{PrepareCommand(cmd, conn, null, sqlString, cmdParms);DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);cmd.Parameters.Clear();return myReader;}catch (Exception ex){throw ex;}}#endregion#region 執行查詢語句，返回DataSet/// <summary>/// 執行查詢語句，返回DataSet/// </summary>/// <param name="sqlString">查詢語句</param>/// <returns>DataSet</returns>public DataSet Query(string sqlString, params DbParameter[] cmdParms){DbConnection conn = GetConnection();DbCommand cmd = GetCommand();PrepareCommand(cmd, conn, null, sqlString, cmdParms);using (DbDataAdapter da = GetDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (Exception ex){throw ex;}finally{cmd.Dispose();conn.Close();}return ds;}}#endregion#region PrepareCommandprivate void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms){if (conn.State != ConnectionState.Open) conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null) cmd.Transaction = trans;cmd.CommandType = CommandType.Text;if (cmdParms != null){foreach (DbParameter parm in cmdParms){cmd.Parameters.Add(parm);}}}#endregion#endregion#endregion#region 增刪改查#region 獲取最大編號/// <summary>/// 獲取最大編號/// </summary>/// <typeparam name="T">實體Model</typeparam>/// <param name="key">主鍵</param>public int GetMaxID<T>(string key){Type type = typeof(T);string sql = null;switch (m_DBType){case "oracle":sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);break;case "mssql":sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);break;case "mysql":sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);break;case "sqlite":sql = string.Format("SELECT Max(cast({0} as int)) FROM {1}", key, type.Name);break;}using (DbConnection conn = GetConnection()){using (IDbCommand cmd = GetCommand(sql, conn)){try{conn.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return 1;}else{return int.Parse(obj.ToString()) + 1;}}catch (Exception ex){throw ex;}finally{cmd.Dispose();conn.Close();}}}}#endregion#region 添加/// <summary>/// 添加/// </summary>public void Insert(object obj){Insert(obj, m_AutoIncrement);}/// <summary>/// 添加/// </summary>public void Insert(object obj, bool autoIncrement){StringBuilder strSql = new StringBuilder();Type type = obj.GetType();strSql.Append(string.Format("insert into {0}(", type.Name));PropertyInfo[] propertyInfoList = GetEntityProperties(type);List<string> propertyNameList = new List<string>();int savedCount = 0;foreach (PropertyInfo propertyInfo in propertyInfoList){if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0){propertyNameList.Add(propertyInfo.Name);savedCount++;}}strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));DbParameter[] parameters = new DbParameter[savedCount];int k = 0;for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++){PropertyInfo propertyInfo = propertyInfoList[i];if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0 && autoIncrement) return;if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0){object val = propertyInfo.GetValue(obj, null);DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);parameters[k++] = param;}}ExecuteSql(strSql.ToString(), parameters);}#endregion#region 修改/// <summary>/// 修改/// </summary>public void Update(object obj){object oldObj = Find(obj, false);if (oldObj == null) throw new Exception("無法獲取到舊數據");StringBuilder strSql = new StringBuilder();Type type = obj.GetType();strSql.Append(string.Format("update {0} ", type.Name));PropertyInfo[] propertyInfoList = GetEntityProperties(type);List<string> propertyNameList = new List<string>();int savedCount = 0;foreach (PropertyInfo propertyInfo in propertyInfoList){if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0){object oldVal = propertyInfo.GetValue(oldObj, null);object val = propertyInfo.GetValue(obj, null);if (!object.Equals(oldVal, val)){propertyNameList.Add(propertyInfo.Name);savedCount++;}}}strSql.Append(string.Format(" set "));DbParameter[] parameters = new DbParameter[savedCount];StringBuilder sbPros = new StringBuilder();int k = 0;for (int i = 0; i < propertyInfoList.Length && savedCount > 0; i++){PropertyInfo propertyInfo = propertyInfoList[i];if (propertyInfo.GetCustomAttributes(typeof(IsDBFieldAttribute), false).Length > 0){object oldVal = propertyInfo.GetValue(oldObj, null);object val = propertyInfo.GetValue(obj, null);if (!object.Equals(oldVal, val)){sbPros.Append(string.Format(" {0}={1}{0},", propertyInfo.Name, m_ParameterMark));DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);parameters[k++] = param;}}}if (sbPros.Length > 0){strSql.Append(sbPros.ToString(0, sbPros.Length - 1));}strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));if (savedCount > 0){ExecuteSql(strSql.ToString(), parameters);}}#endregion#region 刪除/// <summary>/// 根據Id刪除/// </summary>public void Delete<T>(int id){Type type = typeof(T);StringBuilder sbSql = new StringBuilder();sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type)));ExecuteSql(sbSql.ToString());}/// <summary>/// 根據Id集合刪除/// </summary>public void BatchDelete<T>(string ids){if (string.IsNullOrWhiteSpace(ids)) return;Type type = typeof(T);StringBuilder sbSql = new StringBuilder();sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type)));ExecuteSql(sbSql.ToString());}/// <summary>/// 根據條件刪除/// </summary>public void Delete<T>(string conditions){if (string.IsNullOrWhiteSpace(conditions)) return;Type type = typeof(T);StringBuilder sbSql = new StringBuilder();sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));ExecuteSql(sbSql.ToString());}#endregion#region 獲取實體#region 根據實體獲取實體/// <summary>/// 根據實體獲取實體/// </summary>private object Find(object obj, bool readCache = true){Type type = obj.GetType();object result = Activator.CreateInstance(type);bool hasValue = false;IDataReader rd = null;string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));try{rd = ExecuteReader(sql);PropertyInfo[] propertyInfoList = GetEntityProperties(type);int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){hasValue = true;IDataRecord record = rd;foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}if (hasValue){return result;}else{return null;}}#endregion#region 根據Id獲取實體/// <summary>/// 根據Id獲取實體/// </summary>private object FindById(Type type, int id){object result = Activator.CreateInstance(type);IDataReader rd = null;bool hasValue = false;string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));try{rd = ExecuteReader(sql);PropertyInfo[] propertyInfoList = GetEntityProperties(type);int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){hasValue = true;IDataRecord record = rd;foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}if (hasValue){return result;}else{return null;}}#endregion#region 根據Id獲取實體/// <summary>/// 根據Id獲取實體/// </summary>public T FindById<T>(string id) where T : new(){Type type = typeof(T);T result = (T)Activator.CreateInstance(type);IDataReader rd = null;bool hasValue = false;string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));try{rd = ExecuteReader(sql);PropertyInfo[] propertyInfoList = GetEntityProperties(type);int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){hasValue = true;IDataRecord record = rd;foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}if (hasValue){return result;}else{return default(T);}}#endregion#region 根據sql獲取實體/// <summary>/// 根據sql獲取實體/// </summary>public T FindBySql<T>(string sql) where T : new(){Type type = typeof(T);T result = (T)Activator.CreateInstance(type);IDataReader rd = null;bool hasValue = false;try{rd = ExecuteReader(sql);PropertyInfo[] propertyInfoList = GetEntityProperties(type);int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){hasValue = true;IDataRecord record = rd;foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}if (hasValue){return result;}else{return default(T);}}#endregion#endregion#region 獲取列表/// <summary>/// 獲取列表/// </summary>public List<T> FindListBySql<T>(string sql) where T : new(){List<T> list = new List<T>();object obj;IDataReader rd = null;try{rd = ExecuteReader(sql);if (typeof(T) == typeof(int)){while (rd.Read()){list.Add((T)rd[0]);}}else if (typeof(T) == typeof(string)){while (rd.Read()){list.Add((T)rd[0]);}}else{PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){IDataRecord record = rd;obj = new T();foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}list.Add((T)obj);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}return list;}#endregion#region 獲取列表/// <summary>/// 獲取列表/// </summary>public List<T> FindListBySql<T>(string sql, params DbParameter[] cmdParms) where T : new(){List<T> list = new List<T>();object obj;IDataReader rd = null;try{rd = ExecuteReader(sql, cmdParms);if (typeof(T) == typeof(int)){while (rd.Read()){list.Add((T)rd[0]);}}else if (typeof(T) == typeof(string)){while (rd.Read()){list.Add((T)rd[0]);}}else{PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();int fcnt = rd.FieldCount;List<string> fileds = new List<string>();for (int i = 0; i < fcnt; i++){fileds.Add(rd.GetName(i).ToUpper());}while (rd.Read()){IDataRecord record = rd;obj = new T();foreach (PropertyInfo pro in propertyInfoList){if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value){continue;}pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);}list.Add((T)obj);}}}catch (Exception ex){throw ex;}finally{if (rd != null && !rd.IsClosed){rd.Close();rd.Dispose();}}return list;}#endregion#region 分頁獲取列表/// <summary>/// 分頁(任意entity，盡量少的字段)/// </summary>public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new(){PagerModel pagerModel = new PagerModel(currentPage, pageSize);using (DbConnection connection = GetConnection()){connection.Open();IDbCommand cmd = null;StringBuilder sb = new StringBuilder();string commandText = null;int startRow = 0;int endRow = 0;switch (m_DBType){case "oracle":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);endRow = startRow + pageSize;sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.Append(" ) row_limit where rownum <= ");sb.Append(endRow);sb.Append(" ) where rownum_ >");sb.Append(startRow);#endregionbreak;case "mssql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1) + 1;endRow = startRow + pageSize - 1;sb.Append(string.Format(@"select * from (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTablewhere rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));#endregionbreak;case "mysql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append("select * from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);#endregionbreak;case "sqlite":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);#endregionbreak;}List<T> list = FindListBySql<T>(sb.ToString());pagerModel.result = list;}return pagerModel;}#endregion#region 分頁獲取列表/// <summary>/// 分頁(任意entity，盡量少的字段)/// </summary>/// <typeparam name="T"></typeparam>/// <param name="sql"></param>/// <returns></returns>public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params DbParameter[] cmdParms) where T : new(){PagerModel pagerModel = new PagerModel(currentPage, pageSize);using (DbConnection connection = GetConnection()){connection.Open();IDbCommand cmd = null;StringBuilder sb = new StringBuilder();string commandText = null;int startRow = 0;int endRow = 0;switch (m_DBType){case "oracle":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);endRow = startRow + pageSize;sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.Append(" ) row_limit where rownum <= ");sb.Append(endRow);sb.Append(" ) where rownum_ >");sb.Append(startRow);#endregionbreak;case "mssql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1) + 1;endRow = startRow + pageSize - 1;sb.Append(string.Format(@"select * from (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTablewhere rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));#endregionbreak;case "mysql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append("select * from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);#endregionbreak;case "sqlite":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);#endregionbreak;}List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);pagerModel.result = list;}return pagerModel;}#endregion#region 分頁獲取列表/// <summary>/// 分頁(任意entity，盡量少的字段)/// </summary>public DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params DbParameter[] cmdParms){DataSet ds = null;using (DbConnection connection = GetConnection()){connection.Open();IDbCommand cmd = null;StringBuilder sb = new StringBuilder();string commandText = null;int startRow = 0;int endRow = 0;totalCount = 0;switch (m_DBType){case "oracle":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);totalCount = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);endRow = startRow + pageSize;sb.Append("select * from ( select row_limit.*, rownum rownum_ from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.Append(" ) row_limit where rownum <= ");sb.Append(endRow);sb.Append(" ) where rownum_ >");sb.Append(startRow);#endregionbreak;case "mssql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);totalCount = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1) + 1;endRow = startRow + pageSize - 1;sb.Append(string.Format(@"select * from (select ROW_NUMBER() over({1}) as rowNumber, t.* from ({0}) t) tempTablewhere rowNumber between {2} and {3} ", sql, orderby, startRow, endRow));#endregionbreak;case "mysql":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);totalCount = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append("select * from (");sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);#endregionbreak;case "sqlite":#region 分頁查詢語句commandText = string.Format("select count(*) from ({0}) T", sql);cmd = GetCommand(commandText, connection);totalCount = int.Parse(cmd.ExecuteScalar().ToString());startRow = pageSize * (currentPage - 1);sb.Append(sql);if (!string.IsNullOrWhiteSpace(orderby)){sb.Append(" ");sb.Append(orderby);}sb.AppendFormat(" limit {0} offset {1}", pageSize, startRow);#endregionbreak;}ds = Query(sql, cmdParms);}return ds;}#endregion#region getReaderValue 轉換數據/// <summary>/// 轉換數據/// </summary>private Object getReaderValue(Object rdValue, Type ptype){if (ptype == typeof(double))return Convert.ToDouble(rdValue);if (ptype == typeof(decimal))return Convert.ToDecimal(rdValue);if (ptype == typeof(int))return Convert.ToInt32(rdValue);if (ptype == typeof(long))return Convert.ToInt64(rdValue);if (ptype == typeof(DateTime))return Convert.ToDateTime(rdValue);if (ptype == typeof(Nullable<double>))return Convert.ToDouble(rdValue);if (ptype == typeof(Nullable<decimal>))return Convert.ToDecimal(rdValue);if (ptype == typeof(Nullable<int>))return Convert.ToInt32(rdValue);if (ptype == typeof(Nullable<long>))return Convert.ToInt64(rdValue);if (ptype == typeof(Nullable<DateTime>))return Convert.ToDateTime(rdValue);return rdValue;}#endregion#region 獲取主鍵名稱/// <summary>/// 獲取主鍵名稱/// </summary>public string GetIdName(Type type){PropertyInfo[] propertyInfoList = GetEntityProperties(type);foreach (PropertyInfo propertyInfo in propertyInfoList){if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0){return propertyInfo.Name;}}return "Id";}#endregion#region 獲取主鍵值/// <summary>/// 獲取主鍵名稱/// </summary>public object GetIdVal(object val){string idName = GetIdName(val.GetType());if (!string.IsNullOrWhiteSpace(idName)){return val.GetType().GetProperty(idName).GetValue(val, null);}return 0;}#endregion#region 獲取實體類屬性/// <summary>/// 獲取實體類屬性/// </summary>private PropertyInfo[] GetEntityProperties(Type type){List<PropertyInfo> result = new List<PropertyInfo>();PropertyInfo[] propertyInfoList = type.GetProperties();foreach (PropertyInfo propertyInfo in propertyInfoList){if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0&& propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0){result.Add(propertyInfo);}}return result.ToArray();}#endregion#region 獲取基類/// <summary>/// 獲取基類/// </summary>public Type GetBaseType(Type type){while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name){type = type.BaseType;}return type;}#endregion#endregion#region 事務#region 開始事務/// <summary>/// 開始事務/// </summary>public static void BeginTransaction(){DbConnection conn = GetConnection();if (conn.State != ConnectionState.Open) conn.Open();m_Tran = conn.BeginTransaction();}#endregion#region 提交事務/// <summary>/// 提交事務/// </summary>public static void CommitTransaction(){DbConnection conn = m_Tran.Connection;try{m_Tran.Commit();}catch (Exception ex){m_Tran.Rollback();}finally{if (conn.State == ConnectionState.Open) conn.Close();m_Tran.Dispose();m_Tran = null;}}#endregion#region 回滾事務(出錯時調用該方法回滾)/// <summary>/// 回滾事務(出錯時調用該方法回滾)/// </summary>public static void RollbackTransaction(){DbConnection conn = m_Tran.Connection;m_Tran.Rollback();if (conn.State == ConnectionState.Open) conn.Close();}#endregion#endregion} } View Code?
如何使用：
? ? 添加：
/// <summary> /// 添加 /// </summary> public void Insert(object obj) {dbHelper.Insert(obj); } View Code? ? 修改：
/// <summary> /// 修改 /// </summary> public void Update(object obj) {dbHelper.Update(obj); } View Code? ? 根據ID刪除：
/// <summary> /// 刪除 /// </summary> public void Del(int id) {dbHelper.Delete<BS_Template>(id); } View Code? ? 根據ID批量刪除：
/// <summary> /// 刪除 /// </summary> public void BatchDelete(string ids) {dbHelper.BatchDelete<BS_Template>(ids); } View Code? ? 根據條件刪除：
/// <summary> /// 刪除 /// </summary> public void Delete(string conditions) {dbHelper.Delete<BS_Template>(conditions); } View Code? ? 獲取最大ID(當然，ID一般采用自增，對于并發量極少的系統，或單機系統，為了省事，可以這樣做)：
/// <summary> /// GetMaxId /// </summary> public int GetMaxId() {return dbHelper.GetMaxID<BS_Template>("id"); } View Code? ? 根據條件查詢實體：
public BS_Template Get(string typeCode, Enums.TemplateType templateType) {StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template where typeCode='{0}' and type='{1}'", typeCode, (int)templateType));return dbHelper.FindBySql<BS_Template>(sql.ToString()); } public BS_Template Get2(string templateId, Enums.TemplateType templateType) {StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template where id='{0}' and type='{1}'", templateId, (int)templateType));return dbHelper.FindBySql<BS_Template>(sql.ToString()); } View Code? ? 根據ID查詢實體：
public BS_Test Get(string id) {return dbHelper.FindById<BS_Test>(id); } View Code? ? 查詢列表：
/// <summary> /// 查詢列表 /// </summary> public List<BS_Test> GetList(string name) {StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Test twhere 1=1 "));if (!string.IsNullOrWhiteSpace(name)){sql.AppendFormat(" and t.name like '%{0}%'", name);}return dbHelper.FindListBySql<BS_Test>(sql.ToString()); } View Code? ? 分頁查詢列表：
/// <summary> /// 分頁獲取模板集合 /// </summary> public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType) {StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template twhere 1=1 "));if (!string.IsNullOrWhiteSpace(noticeType)){sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);}if (!string.IsNullOrWhiteSpace(coreType)){sql.AppendFormat(" and ct.name like '%{0}%'", coreType);}if (!string.IsNullOrWhiteSpace(name)){sql.AppendFormat(" and t.name like '%{0}%'", name);}sql.AppendFormat(" and t.type = '{0}'", (int)templateType);string orderby = "order by cast(id as int)";pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);return pager.result as List<BS_Template>; } View Code? ? 完整DAL：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using DBUtil; using Models;namespace DAL {/// <summary>/// 模板/// </summary>public class TemplateDal{#region 變量private DBHelper dbHelper = new DBHelper();#endregion#region 分頁獲取模板集合/// <summary>/// 分頁獲取模板集合/// </summary>public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType){StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template twhere 1=1 "));if (!string.IsNullOrWhiteSpace(noticeType)){sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);}if (!string.IsNullOrWhiteSpace(coreType)){sql.AppendFormat(" and ct.name like '%{0}%'", coreType);}if (!string.IsNullOrWhiteSpace(name)){sql.AppendFormat(" and t.name like '%{0}%'", name);}sql.AppendFormat(" and t.type = '{0}'", (int)templateType);string orderby = "order by cast(id as int)";pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);return pager.result as List<BS_Template>;}#endregion#region 獲取字段關聯模板集合/// <summary>/// 獲取字段關聯模板集合/// </summary>public List<BS_Template> GetList(string fieldId){StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template tleft join BS_TplFieldRelation r on r.templateId=t.idleft join BS_TplField f on f.id=r.fieldId where f.id='{0}'", fieldId));return dbHelper.FindListBySql<BS_Template>(sql.ToString());}#endregion#region 獲取public BS_Template Get(string typeCode, Enums.TemplateType templateType){StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template where typeCode='{0}' and type='{1}'", typeCode, (int)templateType));return dbHelper.FindBySql<BS_Template>(sql.ToString());}public BS_Template Get2(string templateId, Enums.TemplateType templateType){StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template where id='{0}' and type='{1}'", templateId, (int)templateType));return dbHelper.FindBySql<BS_Template>(sql.ToString());}#endregion#region GetMaxId/// <summary>/// GetMaxId/// </summary>public int GetMaxId(){return dbHelper.GetMaxID<BS_Template>("id");}#endregion#region 添加/// <summary>/// 添加/// </summary>public void Insert(object obj){dbHelper.Insert(obj);}#endregion#region 修改/// <summary>/// 修改/// </summary>public void Update(object obj){dbHelper.Update(obj);}#endregion#region 刪除/// <summary>/// 刪除/// </summary>public void Del(int id){dbHelper.Delete<BS_Template>(id);}/// <summary>/// 刪除/// </summary>public void BatchDelete(string ids){dbHelper.BatchDelete<BS_Template>(ids);}/// <summary>/// 刪除/// </summary>public void Delete(string conditions){dbHelper.Delete<BS_Template>(conditions);}#endregion} } View Code? ? 查詢sql書寫規范：
? ? SQL不能寫的亂七八糟，該換行換行，該對齊對齊，例：
/// <summary> /// 分頁獲取模板集合 /// </summary> public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType) {StringBuilder sql = new StringBuilder(string.Format(@"select *from BS_Template twhere 1=1 "));if (!string.IsNullOrWhiteSpace(noticeType)){sql.AppendFormat(" and nt.name like '%{0}%'", noticeType);}if (!string.IsNullOrWhiteSpace(coreType)){sql.AppendFormat(" and ct.name like '%{0}%'", coreType);}if (!string.IsNullOrWhiteSpace(name)){sql.AppendFormat(" and t.name like '%{0}%'", name);}sql.AppendFormat(" and t.type = '{0}'", (int)templateType);string orderby = "order by cast(id as int)";pager = dbHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page);return pager.result as List<BS_Template>; } View Code? ? select換行，from換行，join換行，where換行，and換行。
? ??
? ? 數據庫事務：
try {DBHelper.BeginTransaction();BS_Template model = new BS_Template();model.id = m_TemplateDal.GetMaxId().ToString();model.code = k.ToString("0000");model.name = "測試" + k.ToString();model.remarks = "測試" + k.ToString();model.type = ((int)Enums.TemplateType.Notice).ToString();m_TemplateDal.Insert(model);//throw new Exception("a"); BS_Test test = new BS_Test();test.id = m_TestDal.GetMaxId().ToString();test.code = "測試" + k.ToString();test.name = "測試" + k.ToString();test.remarks = "測試" + k.ToString();m_TestDal.Insert(test);DBHelper.CommitTransaction();MessageBox.Show("成功"); } catch (Exception ex) {DBHelper.RollbackTransaction();MessageBox.Show(ex.Message); } View Code?
? ? 代碼下載：http://files.cnblogs.com/files/s0611163/DBHelperDemo.zip
? ? Model生成器下載：http://files.cnblogs.com/files/s0611163/Model%E7%94%9F%E6%88%90%E5%99%A8.zip
?
轉載于:https://www.cnblogs.com/s0611163/p/5858456.html
總結
- 上一篇： NHibernate 错误
- 下一篇： hive添加分区