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 =http://www.mamicode.com/= 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    }}