SQLHELP
时间：2021-07-01 10:21:17
帮助过：11人阅读
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
标签：for collect 打开 load default begin parameter color try
