在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
支持Oracle、MSSQL、MySQL、SQLite等多种数据库,增删改查操作支持实体类,配套Model生成器,支持事务;已在多个项目中实际使用。 没有语法糖,学习成本几乎为0,拿来即用。 源码地址: https://gitee.com/s0611163/DBHelper https://github.com/0611163/DBHelper 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 System.Text.RegularExpressions; using MySql.Data.MySqlClient; using System.Data.OleDb; using Models; /* ---------------------------------------------------------------------- * 作 者:suxiang * 创建日期:2016年11月23日 * 更新日期:2020年06月16日 * * 支持Oracle、MSSQL、MySQL、SQLite、Access数据库 * * 注意引用的MySql.Data.dll、System.Data.SQLite.dll的版本,32位还是64位 * 有的System.Data.SQLite.dll版本需要依赖SQLite.Interop.dll * * 需要配套的PagerModel、IsDBFieldAttribute、IsIdAttribute类 * * 为方便使用,需要配套的Model生成器 * ---------------------------------------------------------------------- */ namespace DBUtil { /// <summary> /// 数据库操作类 /// </summary> public static 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(); /// <summary> /// SQL过滤正则 /// </summary> private static Dictionary<string, Regex> m_SqlFilteRegexList = new Dictionary<string, Regex>(); #endregion #region 静态构造函数 /// <summary> /// 静态构造函数 /// </summary> static DBHelper() { m_SqlFilteRegexList.Add("net localgroup ", new Regex("net[\\s]+localgroup[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("net user ", new Regex("net[\\s]+user[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("xp_cmdshell ", new Regex("xp_cmdshell[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("exec ", new Regex("exec[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("execute ", new Regex("execute[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("truncate ", new Regex("truncate[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("drop ", new Regex("drop[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("restore ", new Regex("restore[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("create ", new Regex("create[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("alter ", new Regex("alter[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("rename ", new Regex("rename[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("insert ", new Regex("insert[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("update ", new Regex("update[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("delete ", new Regex("delete[\\s]+", RegexOptions.IgnoreCase)); m_SqlFilteRegexList.Add("select ", new Regex("select[\\s]+", RegexOptions.IgnoreCase)); } #endregion #region 生成变量 #region 生成 IDbCommand /// <summary> /// 生成 IDbCommand /// </summary> private static 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; case "access": command = new OleDbCommand(); break; } return command; } /// <summary> /// 生成 IDbCommand /// </summary> private static 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; case "access": command = new OleDbCommand(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; case "access": conn = new OleDbConnection(m_ConnectionString); break; } return conn; } #endregion #region 生成 IDbDataAdapter /// <summary> /// 生成 IDbDataAdapter /// </summary> private static 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; case "access": dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = cmd; break; } return dataAdapter; } #endregion #region 生成 m_ParameterMark /// <summary> /// 生成 m_ParameterMark /// </summary> public static string GetParameterMark() { switch (m_DBType) { case "oracle": return ":"; case "mssql": return "@"; case "mysql": return "@"; case "sqlite": return ":"; case "access": return "@"; } return ":"; } #endregion #region 生成 DbParameter /// <summary> /// 生成 DbParameter /// </summary> public static DbParameter GetDbParameter(string name, object vallue) { DbParameter dbParameter = null; switch (m_DBType) { case "oracle": dbParameter = new OracleParameter(name, vallue); break; case "mssql": dbParameter = new SqlParameter(name, vallue); break; case "mysql": dbParameter = new MySqlParameter(name, vallue); break; case "sqlite": dbParameter = new SQLiteParameter(name, vallue); break; case "access": dbParameter = new OleDbParameter(name, vallue); break; } return dbParameter; } #endregion #endregion #region 基础方法 #region 执行简单SQL语句 #region Exists public static bool Exists(string sqlString) { SqlFilter(ref 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 static int ExecuteSql(string sqlString) { SqlFilter(ref 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 static object GetSingle(string sqlString) { SqlFilter(ref 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 static DbDataReader ExecuteReader(string sqlString) { SqlFilter(ref 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 static DataSet Query(string sqlString) { SqlFilter(ref 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 #region SQL过滤,防注入 /// <summary> /// SQL过滤,防注入 /// </summary> /// <param name="sql">sql</param> public static void SqlFilter(ref string sql) { sql = sql.Trim(); string ignore = string.Empty; string upperSql = sql.ToUpper(); foreach (string keyword in m_SqlFilteRegexList.Keys) { if (upperSql.IndexOf(keyword.ToUpper()) == 0) { ignore = keyword; } } foreach (string keyword in m_SqlFilteRegexList.Keys) { if (ignore == "select " && ignore == keyword) continue; Regex regex = m_SqlFilteRegexList[keyword]; sql = sql.Substring(0, ignore.Length) + regex.Replace(sql.Substring(ignore.Length), string.Empty); } } #endregion #endregion #region 执行带参数的SQL语句 #region 执行SQL语句,返回影响的记录数 /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static 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 static 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) { |
请发表评论