using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DBUtility
{
public class SQLHelper
{
private SqlConnection con;
public SQLHelper()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ToString());
}
public SQLHelper(String ConnectionString)
{
con = new SqlConnection(ConnectionString);
}
/// <summary>
/// Execute SqlCommand
/// </summary>
/// <param name="proc_name"></param>
/// <param name="param"></param>
public void ExecuteSqlCommand(string proc_name, SqlParameter[] param)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(proc_name, con);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
cmd.Parameters.Add(parameter);
}
}
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
}
/// <summary>
/// Execute SqlCommand
/// </summary>
/// <param name="proc_name"></param>
public void ExecuteSqlCommand(string proc_name)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(proc_name, con);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
}
/// <summary>
/// Return DataTable
/// </summary>
/// <param name="proc_name"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string proc_name, SqlParameter[] param)
{
DataTable dt = new DataTable();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
sda.SelectCommand.Parameters.Add(parameter);
}
}
try
{
sda.Fill(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return dt;
}
/// <summary>
/// Return DataTable
/// </summary>
/// <param name="proc_name"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string proc_name)
{
DataTable dt = new DataTable();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Transaction = tran;
try
{
sda.Fill(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return dt;
}
/// <summary>
/// Return DataSet
/// </summary>
/// <param name="proc_name"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string proc_name)
{
DataSet ds = new DataSet();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Transaction = tran;
try
{
sda.Fill(ds);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return ds;
}
/// <summary>
/// Return DataSet
/// </summary>
/// <param name="proc_name"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string proc_name, SqlParameter[] param)
{
DataSet ds = new DataSet();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(proc_name, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
sda.SelectCommand.Parameters.Add(parameter);
}
}
try
{
sda.Fill(ds);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return ds;
}
/// <summary>
/// Return DataReader
/// </summary>
/// <param name="proc_name"></param>
/// <param name="param"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string proc_name, SqlParameter[] param)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(proc_name, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
cmd.Parameters.Add(parameter);
}
}
SqlDataReader sdr;
try
{
sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
return sdr;
}
/// <summary>
/// Return DataTable
/// </summary>
/// <param name="SqlStr"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataTable ExecuteDataTableSql(string SqlStr, SqlParameter[] param)
{
DataTable dt = new DataTable();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(SqlStr, con);
sda.SelectCommand.CommandType = CommandType.Text;
sda.SelectCommand.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
sda.SelectCommand.Parameters.Add(parameter);
}
}
try
{
sda.Fill(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return dt;
}
/// <summary>
/// Execute SqlCommand
/// </summary>
/// <param name="SqlStr"></param>
/// <param name="param"></param>
public void ExecuteSqlCommandSql(string SqlStr, SqlParameter[] param)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(SqlStr, con);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
cmd.Parameters.Add(parameter);
}
}
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
}
/// <summary>
/// Return DataReader
/// </summary>
/// <param name="SqlStr"></param>
/// <param name="param"></param>
/// <returns></returns>
public SqlDataReader ExecuteReaderSql(string SqlStr, SqlParameter[] param)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(SqlStr, con);
cmd.CommandType = CommandType.Text;
cmd.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
cmd.Parameters.Add(parameter);
}
}
SqlDataReader sdr;
try
{
sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
return sdr;
}
/// <summary>
/// Return DataSet
/// </summary>
/// <param name="proc_name"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataSet ExecuteDataSetSql(string SqlStr, SqlParameter[] param)
{
DataSet ds = new DataSet();
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter(SqlStr, con);
sda.SelectCommand.CommandType = CommandType.Text;
sda.SelectCommand.Transaction = tran;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
ReplaceParameters(parameter);
sda.SelectCommand.Parameters.Add(parameter);
}
}
try
{
sda.Fill(ds);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
finally
{
con.Close();
}
return ds;
}
/// <summary>
/// Replace Parameters
/// </summary>
/// <param name="SingleParameter"></param>
/// <returns></returns>
private SqlParameter ReplaceParameters(SqlParameter SingleParameter)
{
if (SingleParameter.Value is String)
{
SingleParameter.Value.ToString().Replace(@"\", @"\\");
SingleParameter.Value.ToString().Replace("'", "\"");
}
return SingleParameter;
}
}
调用方法 引入命名空间
SQLHelper sh= new SQLHelper();
DataTable dt = new DataTable();
SqlParameter[] param ={ new SqlParameter("@ID", SqlDbType.Int), new SqlParameter("@Title", SqlDbType.NVarChar) };
param[0].Value = 1;
param[1].Value = "档案管理";
dt = sh.ExecuteDataTable("sp_UDS_DabyGetAll", param);
