首页 > 代码库 > SQLHELP
SQLHELP
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.Data.OracleClient; using System.Data.SqlClient; using System.Linq; using System.Text; namespace ZX.DataAccess.DAL_Pub { public static class SqlHelperExt { public static int AddRange(this IDataParameterCollection coll, IDataParameter[] par) { int i = 0; foreach (var item in par) { coll.Add(item); i++; } return i; } } #region SqlHelper public class SqlHelper { private IDbConnection conn = null; private IDbCommand cmd = null; private IDataReader dr = null; private DbType type = DbType.NONE; #region 创建数据库连接 /// <summary> /// 创建数据库连接 /// </summary> public SqlHelper(string connectionString) { conn = DBFactory.CreateDbConnection(type, connectionString); } #endregion #region 判断并打开conn /// <summary> /// 判断并打开conn /// </summary> /// <returns></returns> public IDbConnection CreatConn() { if (conn.State == ConnectionState.Closed) { conn.Open(); } return conn; } #endregion #region 执行查询sql语句 /// <summary> /// 执行查询sql语句 /// </summary> /// <param name="sql">查询sql语句</param> /// <returns>返回一个表</returns> public DataTable ExecuteReader(string sql) { DataTable dt = new DataTable(); using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { using (dr = cmd.ExecuteReader()) { dt.Load(dr); } } conn.Close(); return dt; } #endregion #region 执行查询带参的sql语句 /// <summary> /// 执行查询带参的sql语句 /// </summary> /// <param name="sql">查询sql语句</param> /// <param name="par">sql语句中的参数</param> /// <returns>返回一个表</returns> public DataTable ExecuteReader(string sql, IDataParameter[] par) { DataTable dt = new DataTable(); using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { cmd.Parameters.AddRange(par); using (dr = cmd.ExecuteReader()) { dt.Load(dr); } } conn.Close(); return dt; } public DataTable ExecuteReader(string sql, IDataParameter par) { DataTable dt = new DataTable(); using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { cmd.Parameters.Add(par); using (dr = cmd.ExecuteReader()) { dt.Load(dr); } } conn.Close(); return dt; } #endregion #region 执行增,删,改sql语句 /// <summary> /// 执行无参的增,删,改sql语句 /// </summary> /// <param name="sql">增,删,改的sql语句</param> /// <param name="par">sql语句中的参数</param> /// <returns>返回所影响的行数</returns> public int ExecuteNonQuery(string sql) { int result = 0; using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { result = cmd.ExecuteNonQuery(); } conn.Close(); return result; } #endregion #region 执行带参的增,删,改sql语句 /// <summary> /// 执行带参的增,删,改sql语句 /// </summary> /// <param name="sql">增,删,改的sql语句</param> /// <param name="par">sql语句中的参数</param> /// <returns>返回所影响的行数</returns> public int ExecuteNonQuery(string sql, IDbDataParameter[] par) { int result = 0; using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { cmd.Parameters.AddRange(par); result = cmd.ExecuteNonQuery(); } conn.Close(); return result; } public int ExecuteNonQuery(string sql, IDbDataParameter par) { int result = 0; using (cmd = DBFactory.CreateDbCommand(sql, CreatConn())) { cmd.Parameters.Add(par); result = cmd.ExecuteNonQuery(); } conn.Close(); return result; } #endregion #region 事务 /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLList">SQL语句的哈希表(key为sql语句,value是该语句的OleDbParameter[])</param> public bool ExecuteTransaction(Hashtable SqlList) { CreatConn(); using (IDbTransaction trans = conn.BeginTransaction()) { IDbCommand cmd = DBFactory.CreateDbCommand(type); try { //循环 foreach (DictionaryEntry myDE in SqlList) { string cmdText = myDE.Key.ToString(); IDbDataParameter[] cmdParms = (IDbDataParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); return false; } finally { conn.Close(); } } return true; } private void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, string cmdText, IDataParameter[] cmdParms) { CreatConn(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) cmd.Parameters.AddRange(cmdParms); } #endregion } #endregion public enum DbType { //Oracle,SqlServer,MySql,Access,SqlLite NONE, ORACLE, SQLSERVER, MYSQL, ACCESS, SQLLITE } public class DBFactory { public static IDbConnection CreateDbConnection(DbType type, string connectionString) { IDbConnection conn = null; switch (type) { case DbType.ORACLE: conn = new OracleConnection(connectionString); break; case DbType.SQLSERVER: conn = new SqlConnection(connectionString); break; //case DbType.MYSQL: // conn = new MySqlConnection(connectionString); // break; //case DbType.ACCESS: // conn = new OleDbConnection(connectionString); // break; //case DbType.SQLLITE: // conn = new SQLiteConnection(connectionString); break; case DbType.NONE: throw new Exception("未设置数据库类型"); default: throw new Exception("不支持该数据库类型"); } return conn; } public static IDbCommand CreateDbCommand(DbType type) { IDbCommand cmd = null; switch (type) { case DbType.ORACLE: cmd = new OracleCommand(); break; case DbType.SQLSERVER: cmd = new SqlCommand(); break; //case DbType.MYSQL: // cmd = new MySqlCommand(); break; //case DbType.ACCESS: // cmd = new OleDbCommand(); // break; //case DbType.SQLLITE: // cmd = new SQLiteCommand(); // break; case DbType.NONE: throw new Exception("未设置数据库类型"); default: throw new Exception("不支持该数据库类型"); } return cmd; } public static IDbCommand CreateDbCommand(string sql, IDbConnection conn) { DbType type = DbType.NONE; if (conn is OracleConnection) type = DbType.ORACLE; //else if (conn is SqlConnection) // type = DbType.SQLSERVER; //else if (conn is MySqlConnection) // type = DbType.MYSQL; //else if (conn is OleDbConnection) // type = DbType.ACCESS; //else if (conn is SQLiteConnection) type = DbType.SQLLITE; IDbCommand cmd = null; switch (type) { case DbType.ORACLE: cmd = new OracleCommand(sql, (OracleConnection)conn); break; case DbType.SQLSERVER: cmd = new SqlCommand(sql, (SqlConnection)conn); break; //case DbType.MYSQL: // cmd = new MySqlCommand(sql, (MySqlConnection)conn); // break; //case DbType.ACCESS: // cmd = new OleDbCommand(sql, (OleDbConnection)conn); // break; //case DbType.SQLLITE: // cmd = new SQLiteCommand(sql, (SQLiteConnection)conn); // break; case DbType.NONE: throw new Exception("未设置数据库类型"); default: throw new Exception("不支持该数据库类型"); } return cmd; } } }
SQLHELP
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。