using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;//
using System.Collections;//
/// <summary>
/// SQLHelper 的摘要说明
/// </summary>
public class SQLHelper
{
public SQLHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
private static SqlCommand command = new SqlCommand();//
private static SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
static SQLHelper()
{
command.Connection = conn;
}
/// <summary>
/// 打开数据库连接
/// </summary>
private static void OpenConnect()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public static void CloseConnect()
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <returns></returns>
private static SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
OpenConnect();//打开DB连接
SqlCommand command = new SqlCommand(procName, conn);
command.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
command.Parameters.Add(parameter);
}
}
command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
/// <summary>
/// 执行sql命令语句
/// </summary>
/// <param name="sql">传入要执行的sql语句</param>
public static void ExecuteSql(string sql)
{
OpenConnect();//打开连接
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.ExecuteNonQuery();
CloseConnect();//关闭连接
}
/// <summary>
/// 返回一个DataTable对象
/// </summary>
/// <param name="sql">要执行的SQL命令语句</param>
/// <returns>返回一的DataTable对象</returns>
public static DataTable GetDataTable(string sql)
{
DataSet dataSet = new DataSet();
OpenConnect();//打开DB连接
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
command.CommandText = sql;
command.CommandType = CommandType.Text;
adapter.Fill(dataSet);
CloseConnect();//关闭DB连接
return dataSet.Tables[0];
}
/// <summary>
/// 返回一的DataTable对象
/// </summary>
/// <param name="sql">要执行的SQL命令语句</param>
/// <param name="pageinfo">第几页</param>
/// <param name="pagesize">每页显示数据项数</param>
/// <param name="name"></param>
/// <returns>返回一的DataTable对象</returns>
public static DataTable GetDataTable(string sql, int pageinfo, int pagesize, string name)
{
DataSet dataSet = new DataSet();
OpenConnect();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
command.CommandText = sql;
command.CommandType = CommandType.Text;
adapter.Fill(dataSet, pageinfo, pagesize, name);
CloseConnect();
return dataSet.Tables[0];
}
/// <summary>
/// 返回第一行的第一列的值
/// </summary>
/// <param name="sql">要执行的SQL命令语句</param>
/// <returns>字符串形式返回查询结果第一行的第一列的值</returns>
public static string GetFirstColumnValue(string sql)
{
OpenConnect();
command.CommandType = CommandType.Text;
command.CommandText = sql;
object obj2 = command.ExecuteScalar();
CloseConnect();
if (obj2 == null)
{
return "";
}
return obj2.ToString();
}
/// <summary>
/// 返回一个SqlDataReader对象
/// </summary>
/// <param name="sql">要执行的SQL命令语句</param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
OpenConnect();
command.CommandType = CommandType.Text;
command.CommandText = sql;
return command.ExecuteReader(CommandBehavior.CloseConnection);//若关闭DataReader对象则关联的Connection对象也将关闭
}
/// <summary>
/// 返回一个bool值判断Reader对象是否存在行
/// </summary>
/// <param name="sql">要执行的SQL命令语句</param>
/// <returns>返回一个bool值判断Reader对象是否存在行</returns>
public static bool IsHasRow(string sql)
{
OpenConnect();
command.CommandType = CommandType.Text;
command.CommandText = sql;
bool flag = command.ExecuteReader().Read();
CloseConnect();
return flag;
}
public static DataSet RunProc(string procName, SqlParameter[] prams)
{
DataSet dataSet = new DataSet();
OpenConnect();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = CreateCommand(procName, prams);
adapter.Fill(dataSet);
CloseConnect();
return dataSet;
}
public static void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
dataReader = CreateCommand(procName, prams).ExecuteReader(CommandBehavior.CloseConnection);
}
public static void RunProcNon(string procName, SqlParameter[] prams)
{
OpenConnect();
CreateCommand(procName, prams).ExecuteNonQuery();
CloseConnect();
}
public static void RunProcNonQuery(string procName, SqlParameter[] prams)
{
CreateCommand(procName, prams).ExecuteNonQuery();
}
public static DataTable RunProcT(string procName, SqlParameter[] prams)
{
DataSet dataSet = new DataSet();
OpenConnect();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = CreateCommand(procName, prams);
adapter.Fill(dataSet);
CloseConnect();
return dataSet.Tables[0];
}
//--------------------------------------------------------------------------------------------------------------
public static void ExecuteSqlWithIdentityTrans(ArrayList sqls)
{
string newValue = null;
string str2 = "";
SqlTransaction transaction = null;
try
{
OpenConnect();//打开连接
transaction = conn.BeginTransaction();//开始数据库事务
command.Transaction = transaction;
foreach (string str3 in sqls)
{
str2 = str3;
if (newValue != null)
{
str2 = str3.Replace("IdenStr", newValue);
}
command.CommandType = CommandType.Text;
command.CommandText = str2;
if (str3.IndexOf("Idenity") != -1)
{
newValue = command.ExecuteScalar().ToString();
continue;
}
command.ExecuteNonQuery();
}
transaction.Commit();//提交数据库事务
}
catch (Exception exception)
{
transaction.Rollback();//回滚事务
throw exception;
}
finally
{
CloseConnect();//关闭连接
}
}
public static void ExecuteSqlWithTrans(ArrayList sqls)
{
SqlTransaction transaction = null;
try
{
OpenConnect();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
foreach (string str in sqls)
{
command.CommandType = CommandType.Text;
command.CommandText = str;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
throw exception;
}
finally
{
CloseConnect();
}
}
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
{
SqlParameter parameter;
if (Size > 0)
{
parameter = new SqlParameter(ParamName, DbType, Size);
}
else
{
parameter = new SqlParameter(ParamName, DbType);
}
parameter.Direction = Direction;
if ((Direction != ParameterDirection.Output) || (Value != null))
{
parameter.Value = Value;
}
return parameter;
}
public static SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}
}
