DbHelperSQL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
public class DBHelperSQL
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private static string _conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
/// <summary>
/// 数据库参数
/// </summary>
private List<SqlParameter> args = new List<SqlParameter>();
/// <summary>
/// 构造函数
/// </summary>
public DBHelperSQL()
{
}
#region 存储过程相关
/// <summary>
/// 添加参数
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="value">参数值</param>
/// <param name="sqlDbType">参数类型</param>
/// <param name="direct">传入传出设定</param>
/// <returns></returns>
public SqlParameter AddParam(string ParamName, object value, SqlDbType sqlDbType, ParameterDirection direct)
{
SqlParameter arg = new SqlParameter();
arg.ParameterName = ParamName;
arg.SqlDbType = sqlDbType;
arg.Direction = direct;
if (direct == ParameterDirection.Input || direct == ParameterDirection.InputOutput)
{
arg.Value = value;
}
this.args.Add(arg);
return arg;
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="sqlParam">参数对象</param>
public void AddParam(SqlParameter sqlParam)
{
this.args.Add(sqlParam);
}
/// <summary>
/// 添加参数(只传入)
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="value">参数值</param>
/// <param name="sqlDbType">参数类型</param>
/// <returns></returns>
public void AddParamInput(string ParamName, object value, SqlDbType sqlDbType)
{
SqlParameter arg = new SqlParameter();
arg.ParameterName = ParamName;
arg.SqlDbType = sqlDbType;
arg.Value = value;
arg.Direction = ParameterDirection.Input;
this.args.Add(arg);
}
/// <summary>
/// 添加参数(只传出)
/// </summary>
/// <param name="ParamName">参数名</param>
/// <param name="sqlDbType">参数类型</param>
/// <returns></returns>
public SqlParameter AddParamOuput(string ParamName, SqlDbType sqlDbType)
{
SqlParameter arg = new SqlParameter();
arg.ParameterName = ParamName;
arg.SqlDbType = sqlDbType;
arg.Direction = ParameterDirection.Output;
this.args.Add(arg);
return arg;
}
/// <summary>
/// 获取多个表的数据
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public DataSet GetDs(string proc)
{
DataSet ret = null;
SqlDataAdapter daSql = null;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
try
{
ret = new DataSet();
daSql = new SqlDataAdapter();
dbCmd = new SqlCommand();
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = dbConn;
if (this.args.Count > 0) dbCmd.Parameters.AddRange(args.ToArray());
dbCmd.ExecuteNonQuery();
daSql.SelectCommand = dbCmd;
if (ret != null) daSql.Fill(ret);
}
catch (Exception ex)
{
ret = null;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
/// <summary>
/// 获取单表的数据
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public DataTable GetTable(string proc)
{
DataTable ret = null;
DataSet ds = null;
SqlDataAdapter daSql = null;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
try
{
ds = new DataSet();
daSql = new SqlDataAdapter();
dbCmd = new SqlCommand();
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = dbConn;
if (this.args.Count > 0) dbCmd.Parameters.AddRange(args.ToArray());
dbCmd.ExecuteNonQuery();
daSql.SelectCommand = dbCmd;
if (ds != null)
{
daSql.Fill(ds);
ret = ds.Tables[ds.Tables.Count - 1];
}
}
catch (Exception ex)
{
ds = null;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
/// <summary>
/// 执行一个处理
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public int Exec(string proc)
{
int ret = 0;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
try
{
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd = new SqlCommand();
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = dbConn;
if (this.args.Count > 0)
{
dbCmd.Parameters.AddRange(args.ToArray());
}
ret = dbCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ret = -1;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
/// <summary>
/// 执行一个处理
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public int ExecSql(string proc)
{
int ret = 0;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
try
{
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd = new SqlCommand();
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.Text;
dbCmd.Connection = dbConn;
if (this.args.Count > 0)
{
dbCmd.Parameters.AddRange(args.ToArray());
}
ret = dbCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ret = -1;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
/// <summary>
/// 执行一个处理(带返回Int)
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public int ExecRetInt(string proc)
{
int ret = 0;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
SqlParameter sqlReturn = null;
try
{
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd = new SqlCommand();
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = dbConn;
sqlReturn = new SqlParameter();
sqlReturn.ParameterName = "rval";
sqlReturn.Direction = ParameterDirection.ReturnValue;
sqlReturn.SqlDbType = SqlDbType.Int;
args.Add(sqlReturn);
dbCmd.Parameters.AddRange(args.ToArray());
dbCmd.ExecuteNonQuery();
ret = Convert.ToInt32(sqlReturn.Value);
}
catch (Exception ex)
{
ret = -1;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
/// <summary>
/// 执行一个处理(带返回String)
/// </summary>
/// <param name="proc">存储过程名</param>
/// <returns></returns>
public string ExecRetStr(string proc)
{
int cnt = 0;
string ret = string.Empty;
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
SqlParameter sqlReturn = null;
try
{
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd = new SqlCommand();
dbCmd.CommandText = proc;
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = dbConn;
sqlReturn = new SqlParameter();
sqlReturn.ParameterName = "rval";
sqlReturn.Direction = ParameterDirection.ReturnValue;
sqlReturn.SqlDbType = SqlDbType.NVarChar;
args.Add(sqlReturn);
dbCmd.Parameters.AddRange(args.ToArray());
cnt = dbCmd.ExecuteNonQuery();
ret = sqlReturn.Value.ToString();
}
catch (Exception ex)
{
ret = string.Empty;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ret;
}
#endregion
#region 直接执行SQL语句
/// <summary>
/// 公有方法，获取数据，返回一个DataSet。
/// </summary>
private static DataSet GetDataSet(String dbstring)
{
DataSet ds = new DataSet();
SqlConnection dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
try
{
SqlDataAdapter adapter = new SqlDataAdapter(dbstring, dbConn);
adapter.Fill(ds);
}
catch (Exception e)
{
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
return ds;
}
/// <summary>
/// 执行SQL语句返回DataTable
/// </summary>
public static DataTable ExecuteDataTable(string dbstring)
{
DataSet ds = GetDataSet(dbstring);
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}
/// <summary>
/// 执行无返回的SQL语句
/// </summary>
public static int ExecuteNonQuery(string dbstring)
{
SqlCommand dbCmd = null;
SqlConnection dbConn = null;
dbCmd = new SqlCommand();
dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
dbCmd.CommandText = dbstring;
dbCmd.CommandType = CommandType.Text;
dbCmd.Connection = dbConn;
try
{
return dbCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return 0;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
}
/// <summary>
/// 执行一条SQL语句，返回一个DataReader。用完之后记得关闭Reader。
/// </summary>
/// <param name="selectSql">要执行的SQL字符串</param>
/// <returns></returns>
public static SqlDataReader ExecuteDataReader(string selectSql)
{
SqlConnection conn = new SqlConnection(_conStr);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = selectSql;
if (conn.State != ConnectionState.Open)
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 事务执行(重载),利用List传送SQL语句
/// </summary>
public static void ExcuteTransactionSql(List<string> dtSql)
{
SqlCommand dbCmd = new SqlCommand();
SqlConnection dbConn = new SqlConnection(_conStr);
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
SqlTransaction trans = dbConn.BeginTransaction();
dbCmd.Connection = dbConn;
dbCmd.Transaction = trans;
try
{
foreach (String str in dtSql)
{
dbCmd.CommandText = str;
dbCmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
finally
{
if (dbConn.State == ConnectionState.Open)
{
dbConn.Close();
}
}
}
/// <summary>
/// 返回一个字符串
/// </summary>
public static string ExecuteString(string dbstring)
{
DataTable dt = null;
DataSet ds = GetDataSet(dbstring);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
if (dt.Rows.Count == 0)
return "";
else
return dt.Rows[0][0].ToString();
}
#endregion
}
