SqlHelper

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

}
}