sql helper 连接mysql_万能的SqlHelper，麻麻再也不用担心用什么数据库了

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

namespace WangSql.DBUtility

{

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 创建数据库连接

///

///创建数据库连接///

public SqlHelper(string connectionString)

{

conn = DBFactory.CreateDbConnection(type, connectionString);

}

#endregion

#region 判断并打开conn

///

///判断并打开conn///

///

public IDbConnection CreatConn()

{

if (conn.State == ConnectionState.Closed)

{

conn.Open();

}

return conn;

}

#endregion

#region 执行查询sql语句

///

///执行查询sql语句///

/// 查询sql语句

/// 返回一个表

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语句

///

///执行查询带参的sql语句///

/// 查询sql语句

/// sql语句中的参数

/// 返回一个表

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语句

///

///执行无参的增，删，改sql语句///

/// 增，删，改的sql语句

/// sql语句中的参数

/// 返回所影响的行数

public int ExecuteNonQuery(string sql)

{

int result = 0;

using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))

{

result = cmd.ExecuteNonQuery();

}

conn.Close();

return result;

}

#endregion

#region 执行带参的增，删，改sql语句

///

///执行带参的增，删，改sql语句///

/// 增，删，改的sql语句

/// sql语句中的参数

/// 返回所影响的行数

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 事务

///

///执行多条SQL语句，实现数据库事务。///

/// SQL语句的哈希表(key为sql语句，value是该语句的OleDbParameter[])

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

}