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;
}
}
}
