c# SQLHelper总汇
///
public abstract class SqlHelper
{
///
private const string DefaultEncryptKey = "#4d&0)-.koOeG`~x";
//Database connection strings
private static string \_SQLConnString = null;
private ILog logger = LogManager.GetLogger("SQLHelper");
public static string SQLConnString
{
get
{
if (\_SQLConnString == null)
{
\_SQLConnString = ConfigurationManager.ConnectionStrings\["WFSQLConnString"\].ConnectionString;
//\_SQLConnString = IBOTech.Utility.Cryptography.TripledDESDecrypt(\_SQLConnString, DefaultEncryptKey);
}
return \_SQLConnString;
}
}
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 生成分页SQL，仅适用于Sql Server 2005或以上版本
/// </summary>
/// <param name="startIndex">开始位置，从1开始</param>
/// <param name="endIndex">结束位置</param>
/// <param name="sql">必须以“select ”开头，以“ order by ...”结尾。</param>
/// <returns></returns>
public static string GetPagerSql(int startIndex, int endIndex, string sql)
{
//去掉“select ”
sql = sql.Remove(, );
//取order by
int index = sql.ToLower().IndexOf(" order by ");
string orderby = sql.Substring(index);
sql = sql.Remove(index);
sql = "with temptbl as (SELECT ROW\_NUMBER() OVER (" + orderby + ")AS Row, " + sql + ")";
sql += "\\r\\nSELECT \* FROM temptbl where Row between " + startIndex.ToString() + " and " + endIndex.ToString();
//ILog logger = LogManager.GetLogger("SQLHelper");
//logger.Info("GetPagerSql:" + sql);
ICollection<string> Categories = new List<string>() { "" };
//System.Windows.Forms.MessageBox.Show(sql);
Logger.Write("GetPagerSql:" + sql, Categories, , , TraceEventType.Error, "", "", "", "", "", null);
// Logger.Write("test saitor");
return sql;
}
#region ExecuteDataset
/// <summary>
/// 执行数据集
/// </summary>
/// <param name="transaction">事务</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandText">命令文本</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
da.Dispose();
return ds;
}
/// <summary>
/// 执行数据集
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string connectionString, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
da.Dispose();
return ds;
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行数据集
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="SQLString">SQL语句</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string connectionString, string SQLString)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(SQLString, conn);
da.Fill(ds, "ds");
}
catch
{
throw;
}
return ds;
}
}
/// <summary>
/// 执行数据集
/// </summary>
/// <param name="conn">连接字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">命令文本</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataSet ExecuteDataset(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmd.Parameters.Clear();
da.Dispose();
}
catch
{
throw;
}
return ds;
}
#endregion ExecuteDataset
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="SQLString">SQL语句</param>
/// <returns></returns>
public static int ExecuteSql(string connectionString, string SQLString)
{
ILog logger = LogManager.GetLogger("SQLHelper");
logger.Info("ExecuteSql:" + SQLString);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch
{
throw;
}
}
}
}
/// <summary>
/// 执行一条记录
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="SQLString">SQL语句</param>
/// <returns></returns>
public static int ExecuteScalar(string connectionString, string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int var = int.Parse(cmd.ExecuteScalar().ToString());
return var;
}
catch
{
throw;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(string connectionString, List<String> SQLStringList)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string strsql = "";
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = ;
for (int n = ; n < SQLStringList.Count; n++)
{
strsql = SQLStringList\[n\];
if (strsql.Trim().Length > )
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
connection.Close();
return count;
}
catch
{
tx.Rollback();
connection.Close();
return -;
}
}
}
/// <summary>
/// 一般用于父子表的插入，并且父表的主键是自增型。
/// 如果identityParam不为空，则arrCommandText\[0\]必须存在Identity字段，arrParam\[1..n\]中应该存在名为identityParam的参数
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">命令类型</param>
/// <param name="arrCommandText">多条SQL语句</param>
/// <param name="arrParam">参数列表</param>
/// <param name="identityParam">null或""表示无父子关系</param>
public static void ExecuteNonQuery(string connectionString, CommandType commandType, List<string> arrCommandText, List<SqlParameter\[\]> arrParam, string identityParam)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlTransaction tran = connection.BeginTransaction();
try
{
SqlParameter\[\] paras;
int identityValue = ;
for (int i = ; i < arrCommandText.Count; i++)
{
if (arrCommandText\[i\].ToString() != "")
{
paras = arrParam == null ? null : arrParam\[i\];
if (i > && paras != null && !string.IsNullOrEmpty(identityParam))
{
identityParam = identityParam.ToLower();
for (int j = ; j < paras.Length; j++)
{
if (paras\[j\].ParameterName.ToLower() == identityParam)
{
paras\[j\].Value = identityValue;
break;
}
}
}
if (i == && !string.IsNullOrEmpty(identityParam))
{
if (commandType == CommandType.Text)
identityValue = Convert.ToInt32(ExecuteScalar(tran, commandType, (arrCommandText\[i\].ToLower().TrimEnd(" ;\\r\\n".ToCharArray()).EndsWith(" @@identity") ? arrCommandText\[i\] : arrCommandText\[i\].TrimEnd(" ;\\r\\n".ToCharArray()) + ";select @@IDENTITY"), paras));
else
identityValue = Convert.ToInt32(ExecuteScalar(tran, commandType, arrCommandText\[i\], paras));
}
else
ExecuteNonQuery(tran, commandType, arrCommandText\[i\], paras);
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行一条只读记录
/// </summary>
/// <param name="trans">事务</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
ICollection<string> Categories = new List<string>() { "" };
Logger.Write("ExecuteReader:" + cmdText, Categories, , , TraceEventType.Error, "", "", "", "", "", null);
return rdr;
}
/// <summary>
/// 日志写
/// </summary>
/// <param name="log">用户</param>
public static void LogWriter(string log)
{
ICollection<string> Categories = new List<string>() { "" };
Logger.Write("user:" + log, Categories, , , TraceEventType.Error, "", "", "", "", "", null);
}
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
//ICollection<string> Categories = new List<string>() { "3" };
//Logger.Write("ExecuteReader:" + cmdText, Categories, 3, 0, TraceEventType.Error, "", "", "", "", "", null);
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Execute a SqlCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter\[\] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
ILog logger = LogManager.GetLogger("SQLHelper");
logger.Info("ExecuteScalar:" + cmdText );
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
public static void CacheParameters(string cacheKey, params SqlParameter\[\] commandParameters)
{
parmCache\[cacheKey\] = commandParameters;
}
/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</returns>
public static SqlParameter\[\] GetCachedParameters(string cacheKey)
{
SqlParameter\[\] cachedParms = (SqlParameter\[\])parmCache\[cacheKey\];
if (cachedParms == null)
return null;
SqlParameter\[\] clonedParms = new SqlParameter\[cachedParms.Length\];
for (int i = , j = cachedParms.Length; i < j; i++)
clonedParms\[i\] = (SqlParameter)((ICloneable)cachedParms\[i\]).Clone();
return clonedParms;
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select \* from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter\[\] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// 过滤通配符
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
public static string FmLike(string text)
{
return text.Replace("%", "\[%\]").Replace("\_", "\[\_\]");
}
}手机扫一扫
移动阅读更方便
你可能感兴趣的文章