using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace Data_Helper
{
public abstract class SqlHelper
{
private static string DB_ConnString = ConfigurationSettings.AppSettings["ConnString"];
/// <summary>
/// 设置SqlCommand对象
/// </summary>
/// <param name="cmd">SqlCommand对象</param>
/// <param name="conn">SqlConnection 对象</param>
/// <param name="trans">SqlTransaction 对象</param>
/// <param name="cmdType">CommandType(执行存储过程或SQL语句)</param>
/// <param name="cmdText">存储过程名称或SQL语句</param>
/// <param name="cmdParms">命令中用到的参数集</param>
private static void SetCommand(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);
}
}
#region 执行操作，返回受影响的行数
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText)
{
return ExecuteNonQuery(CommandType.Text, cmdText);
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQueryProc(string cmdText)
{
return ExecuteNonQuery(CommandType.StoredProcedure, cmdText);
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">执行命令需要的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="tran">已存在的事务</param>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">执行命令需要的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(trans, CommandType.Text, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回受影响的行数(存储过程)
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">执行命令需要的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQueryProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回受影响的行数(存储过程)
/// </summary>
/// <param name="tran">已存在的事务</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">执行命令需要的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQueryProc(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteNonQuery(trans, CommandType.StoredProcedure, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="cmdType">语句类型</param>
/// <param name="cmdText">Sql语句或者存储过程</param>
/// <returns>返回受影响的行数</returns>
private static int ExecuteNonQuery(CommandType cmdType, string cmdText)
{
int result = 0;
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, con, null, cmdType, cmdText, null);
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="cmdType">语句类型</param>
/// <param name="cmdText">SQL语句或者存储过程</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回受影响的行数</returns>
private static int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
int result = 0;
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, con, null, cmdType, cmdText, cmdParms);
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// 执行操作，返回受影响的行数
/// </summary>
/// <param name="trans">已存在的事务</param>
/// <param name="cmdType">语句类型</param>
/// <param name="cmdText">SQL语句或者存储过程</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回受影响的行数</returns>
private static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
int result = 0;
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, con, trans, cmdType, cmdText, cmdParms);
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region 执行查询，返回SqlDataReader
/// <summary>
/// 执行查询，返回SqlDataReader
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>一个结果集对象SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string cmdText)
{
return ExecuteReader(CommandType.Text, cmdText);
}
/// <summary>
/// 执行查询，返回SqlDataReader(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <returns>一个结果集对象SqlDataReader</returns>
public static SqlDataReader ExecuteReaderProc(string cmdText)
{
return ExecuteReader(CommandType.StoredProcedure, cmdText);
}
/// <summary>
/// 执行查询，返回SqlDataReader
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>一个结果集对象SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteReader(CommandType.Text, cmdText, commandParameters);
}
/// <summary>
/// 执行查询，返回SqlDataReader(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>一个结果集对象SqlDataReader</returns>
public static SqlDataReader ExecuteReaderProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteReader(CommandType.StoredProcedure, cmdText, commandParameters);
}
/// <summary>
/// 执行查询，返回SqlDataReader
/// </summary>
/// <param name="cmdType">SQL语句类型</param>
/// <param name="cmdText">SQL语句</param>
/// <returns>一个结果集对象</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText)
{
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
try
{
using (SqlConnection conn = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, conn, null, cmdType, cmdText, null);
rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
}
}
catch
{
throw;
}
return rdr;
}
/// <summary>
/// 执行查询，返回SqlDataReader
/// </summary>
/// <param name="cmdType">SQL语句类型</param>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>一个结果集对象</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
try
{
using (SqlConnection conn = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
}
}
catch
{
throw;
}
return rdr;
}
#endregion
#region 执行操作，返回表中第一行，第一列的值
/// <summary>
/// 执行操作，返回表中第一行，第一列的值
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalar(string cmdText)
{
return ExecuteScalar(CommandType.Text, cmdText);
}
/// <summary>
/// 执行操作，返回表中第一行，第一列的值
/// </summary>
/// <param name="commandText">存储过程</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalarPorc(string cmdText)
{
return ExecuteScalar(CommandType.StoredProcedure, cmdText);
}
/// <summary>
/// 执行操作，返回表中第一行，第一列的值
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(CommandType.Text, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回表中第一行，第一列的值(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalarProc(string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(CommandType.StoredProcedure, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回表中第一行，第一列的值
/// </summary>
/// <param name="tran">已存在的事务</param>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalar(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(trans, CommandType.Text, cmdText, commandParameters);
}
/// <summary>
/// 执行操作，返回表中第一行，第一列的值
/// </summary>
/// <param name="tran">已存在的事务</param>
/// <param name="commandText">存储过程</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回的对象</returns>
public static object ExecuteScalarProc(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
return ExecuteScalar(trans, CommandType.StoredProcedure, cmdText, commandParameters);
}
private static object ExecuteScalar(CommandType cmdType, string cmdText)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, connection, null, cmdType, cmdText, null);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
private static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
private static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SetCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
#endregion
#region 执行一个命令，返回数据集或数据表
/// <summary>
/// 执行一个命令，返回数据表
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据表</returns>
public static DataTable ExecuteDataTable(string commandText)
{
return ExecuteDataSet(commandText).Tables[0];
}
/// <summary>
/// 执行一个命令，返回数据表(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据表</returns>
public static DataTable ExecuteDataTableProc(string commandText)
{
return ExecuteDataSetProc(commandText).Tables[0];
}
/// <summary>
/// 执行一个命令，返回数据表
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据表</returns>
public static DataTable ExecuteDataTable(string commandText, params SqlParameter[] commandParameters)
{
return ExecuteDataSet(commandText, commandParameters).Tables[0];
}
/// <summary>
/// 执行一个命令，返回数据表(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据表</returns>
public static DataTable ExecuteDataTableProc(string commandText, params SqlParameter[] commandParameters)
{
return ExecuteDataSetProc(commandText, commandParameters).Tables[0];
}
/// <summary>
/// 执行一个命令，返回数据集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据集</returns>
public static DataSet ExecuteDataSet(string commandText, params SqlParameter[] commandParameters)
{
return ExecuteDataSet(CommandType.Text, commandText, commandParameters);
}
/// <summary>
/// 执行一个命令，返回数据集(存储过程)
/// </summary>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">执行命令的参数集</param>
/// <returns>返回数据集</returns>
public static DataSet ExecuteDataSetProc(string commandText, params SqlParameter[] commandParameters)
{
return ExecuteDataSet(CommandType.StoredProcedure, commandText, commandParameters);
}
public static DataSet ExecuteDataSet(CommandType cmdType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(DB_ConnString))
{
SetCommand(cmd, conn, null, cmdType, commandText, commandParameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
#endregion
}
}