using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Configuration;
using System.Linq;
namespace Common
{
public class SqlHelp
{
public delegate T BuildObjectHandler<T>(IDataReader reader);
public delegate Object BuildObjectHandler1(IDataReader reader);
private static readonly string ConnStr = ConfigurationManager.AppSettings["ConnectionString"];
private static object syncLock = new object();
private static SqlHelp _instance;
public static SqlHelp Instance()
{
if (_instance == null)
{
lock (syncLock)
{
if (_instance == null)
{
_instance = new SqlHelp();
}
}
}
return _instance;
}
public int ExecuteNonQuery(string commandText)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = new SqlCommand(commandText);
command.Connection = conn;
return command.ExecuteNonQuery();
}
}
public object ExecuteFrist(string commandText)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = new SqlCommand(commandText);
command.Connection = conn;
return command.ExecuteScalar();
}
}
public object ExecuteFrist(string commandText, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(commandText, parameterValues);
command.Connection = conn;
return command.ExecuteScalar();
}
}
public object ExecuteFrist(string commandText, string ExecuteNonQuery, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = new SqlCommand(commandText, conn);
if (ps != null)
{
command.Parameters.Clear();
foreach (IDbDataParameter p in ps)
{
command.Parameters.Add(p);
}
}
return Convert.ToInt32(command.ExecuteScalar());
}
}
public int ExecuteNonQuery(string commandText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
int iRows;
SqlCommand command = new SqlCommand(commandText, conn);
if (ps != null)
{
command.Parameters.Clear();
foreach (IDbDataParameter p in ps)
{
command.Parameters.Add(p);
}
}
iRows = command.ExecuteNonQuery();
return iRows;
}
}
public int ExecuteQuery(string commandText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = new SqlCommand(commandText, conn);
if (ps != null)
{
command.Parameters.Clear();
foreach (IDbDataParameter p in ps)
{
command.Parameters.Add(p);
}
}
return Convert.ToInt32(command.ExecuteScalar());
}
}
public int ExecuteInsert(string commandText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = new SqlCommand(commandText, conn);
if (ps != null)
{
command.Parameters.Clear();
foreach (IDbDataParameter p in ps)
{
command.Parameters.Add(p);
}
}
return Convert.ToInt32(command.ExecuteNonQuery());
}
}
public DataTable ExecuteDataTable(string commandText)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
try
{
conn.Open();
SqlCommand command = new SqlCommand(commandText);
command.Connection = conn;
using (IDataReader dr = command.ExecuteReader())
{
DataTable dt = new DataTable("row");
dt.Load(dr);
conn.Close();
return dt;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
public IDataReader ExecuteProduceReader(string storedProcedureName, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(storedProcedureName, parameterValues);
command.Connection = conn;
return command.ExecuteReader();
}
}
public int ExecuteProduce(string produceName, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(produceName, parameterValues);
command.Connection = conn;
return command.ExecuteNonQuery();
}
}
public string ExecuteProduceReturn(string produceName, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(produceName, parameterValues);
command.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
command.Connection = conn;
command.ExecuteNonQuery();
return command.Parameters["@return"].Value.ToString();
}
}
public int ExecuteProduceReturnInt(string produceName, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(produceName, parameterValues);
command.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
command.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
command.Connection = conn;
command.ExecuteNonQuery();
return int.Parse(command.Parameters["@return"].Value.ToString());
}
}
public DataTable GetProduceDataTable(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlDataReader reader;
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand();
SetCommand(cmd, cmdText, cmdType,conn, cmdParms);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(reader);
reader.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return dt;
}
}
public DataTable GetProduceDataTableAndOutput(string cmdText, CommandType cmdType, SqlParameter[] cmdParms,out string output)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlDataReader reader;
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand();
SetCommand(cmd, cmdText, cmdType, conn, cmdParms);
cmd.Parameters.Add(new SqlParameter("@outPut", SqlDbType.VarChar, 60));
cmd.Parameters["@outPut"].Direction = ParameterDirection.Output;
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(reader);
output=cmd.Parameters["@outPut"].Value.ToString();
reader.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return dt;
}
}
public string ExecuteProduceOutPut(string produceName, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(produceName, parameterValues);
command.Parameters.Add(new SqlParameter("@outPut", SqlDbType.VarChar, 60));
command.Parameters["@outPut"].Direction = ParameterDirection.Output;
command.Connection = conn;
command.ExecuteNonQuery();
return command.Parameters["@outPut"].Value.ToString();
}
}
public int ExecuteProduceReturnOutPut(string produceName, out string outStr, params object[] parameterValues)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlCommand command = GetStoredCommand(produceName, parameterValues);
command.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
command.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(new SqlParameter("@outPut", SqlDbType.VarChar, 8000));
command.Parameters["@outPut"].Direction = ParameterDirection.Output;
command.Connection = conn;
command.ExecuteNonQuery();
outStr = command.Parameters["@outPut"].Value.ToString();
string ret = command.Parameters["@return"].Value.ToString();
return int.Parse(ret);
}
}
/// <summary>
/// 获取存储过程命令
/// </summary>
/// <param name="storedProcedureName"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public SqlCommand GetStoredCommand(string storedProcedureName, params object[] parameterValues)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.Parameters.Clear();
if (parameterValues != null && parameterValues.Length > 0)
{
for (int i = 0; i < parameterValues.Length; i++)
{
cmd.Parameters.Add(parameterValues[i]);
}
}
return cmd;
}
public IList<T> ExecuteQueryToList<T>(string sqlStr, BuildObjectHandler<T> buildObj)
{
IList<T> sets = new List<T>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sqlStr, conn);
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sets.Add(buildObj(dr));
}
dr.Close();
}
}
catch (Exception)
{
conn.Close();
throw;
}
}
return sets;
}
/// <summary>
/// 存储过程返回list集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlStr"></param>
/// <param name="buildObj"></param>
/// <returns></returns>
public IList<T> ExecuteQueryToListByProcedure<T>(string storedProcedureName, BuildObjectHandler<T> buildObj, params object[] parameterValues)
{
IList<T> sets = new List<T>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = GetStoredCommand(storedProcedureName, parameterValues);
cmd.CommandTimeout = 180;
cmd.Connection = conn;
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sets.Add(buildObj(dr));
}
dr.Close();
}
}
catch (Exception)
{
conn.Close();
throw;
}
}
return sets;
}
public T ExecuteQueryToFirst<T>(string sqlStr, BuildObjectHandler<T> buildObj)
{
IList<T> sets = new List<T>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sqlStr, conn);
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sets.Add(buildObj(dr));
break;
}
dr.Close();
}
}
catch (Exception)
{
conn.Close();
throw;
}
}
return sets.First();
}
public Object ExecuteQueryToFirstAndReturn(string proName, out int returnVal, BuildObjectHandler1 buildObj, params object[] parameterValues)
{
IList<Object> sets = new List<Object>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = GetStoredCommand(proName, parameterValues);
cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
conn.Open();
//cmd.ExecuteNonQuery();
//returnVal = 0;
try
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sets.Add(buildObj(dr));
break;
}
dr.Close();
returnVal = int.Parse(cmd.Parameters["@return"].Value.ToString());
}
}
catch (Exception)
{
conn.Close();
throw;
}
}
if (sets.Count == 0)
{
return null;
}
return sets.First();
}
//根据父ID找出所有的子节点
public string GetChildIDStr(string tabName, string pKey, string parentName, int parentID)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("create table #lsb(id int) ");
sqlStr.Append(string.Format("insert into #lsb values({0}) ", parentID));
sqlStr.Append(string.Format("insert into #lsb select {0} from {1} where {2}={3} ", pKey, tabName,
parentName, parentID));
sqlStr.Append(" while @@rowcount>0 ");
sqlStr.Append(
string.Format(
"insert into #lsb select a.{0} from {1} a inner join #lsb b on a.{2}=b.id where a.{0} not in (select id from #lsb)",
pKey, tabName, parentName));
sqlStr.Append(" select id from #lsb ");
sqlStr.Append(" drop table #lsb");
string childSr = "";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sqlStr.ToString(), conn);
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
childSr = childSr + dr[0].ToString() + ",";
}
dr.Close();
}
}
catch (Exception)
{
conn.Close();
throw;
}
return childSr.Trim(',');
}
}
public Dictionary<string, string> GetDictionary(string sqlStr)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sqlStr, conn);
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dic.Add(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
}
catch (Exception)
{
conn.Close();
throw;
}
}
return dic;
}
public SqlConnection GetConnection()
{
SqlConnection conn = new SqlConnection(ConnStr);
return conn;
}
public bool ExecuteTrasaction(string sqlStr, IList<SqlParameter> param)
{
SqlTransaction tran = null;
try
{
using (SqlConnection conn = GetConnection())
{
conn.Open();
SqlCommand cmd;
tran = conn.BeginTransaction();
if (param.Count > 0)
{
cmd = new SqlCommand(sqlStr, conn, tran);
foreach (var sqlParam in param)
{
if (sqlParam != null)
{
cmd.Parameters.Add(sqlParam);
}
}
cmd.ExecuteNonQuery();
tran.Commit();
}
return true;
}
}
catch
{
tran.Rollback();
}
return false;
}
/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlConnection connection = new SqlConnection(ConnStr);
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
connection.Close();
connection.Dispose();
//return the dataset
return ds;
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the SqlCommand to be prepared</param>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary>
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
private void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
/// <summary>
/// 执行无参数的sql语句
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public void ExecuteSqls(List<string> commandText)
{
//using (SqlConnection conn = new SqlConnection(ConnStr))
//{
// conn.Open();
// SqlCommand command = new SqlCommand(commandText);
// command.Connection = conn;
// return command.ExecuteNonQuery();
//}
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
cmd.Connection = conn;
cmd.Transaction = trans;
//循环
foreach (string sql in commandText)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
conn.Close();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 批量执行带参数的sql语句
/// </summary>
/// <param name="sqlList"></param>
public void ExecuteSqlsParameter(List<KeyValuePair<object, object>> sqlList)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
cmd.Connection = conn;
cmd.Transaction = trans;
//循环
foreach (KeyValuePair<object, object> sql in sqlList)
{
cmd.CommandText = sql.Key.ToString();
foreach (SqlParameter item in (SqlParameter[])sql.Value)
{
cmd.Parameters.Add(item);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
conn.Close();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
#region 设置SqlCommand对象
/// <summary>
/// 设置SqlCommand对象
/// </summary>
/// <param name="cmd">SqlCommand对象 </param>
/// <param name="cmdText">命令文本</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
private static void SetCommand(SqlCommand cmd, string cmdText, CommandType cmdType,SqlConnection conn, SqlParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
cmd.Parameters.AddRange(cmdParms);
}
}
#endregion
}
}
