一个老早写的SqlHelper

这个是好多年前一直用的Helper类，自从上了ORM就基本没用过的，不过发现搞电气的兄弟们用的还多，毕竟不是IT的。

        //数据库连接        private SqlConnection conn = new SqlConnection();        //连接字符串        private string strConn { get; set; }        /// <summary>        /// 单例模试        /// </summary>        private static SqlHelper instance;

        public static SqlHelper GetInstance()        {            if (instance == null)            {                instance = new SqlHelper();            }            return instance;        }

  /// <summary>        /// 构造        /// </summary>        public SqlHelper()        {            this.strConn = SystemConfig.DBConnectionString;            this.conn.ConnectionString = strConn;        }        /// <summary>        /// 构造-传入数据库连接        /// </summary>        /// <param name="ConfigKey"></param>        public SqlHelper(string ConfigKey)        {            this.strConn = ConfigKey;            this.conn.ConnectionString = strConn;        }

  /// <summary>        /// 打开数据库连接        /// </summary>        private void Open()        {            if (conn.State == ConnectionState.Closed)            {                try                {                    conn.Open();                }                catch (Exception ex)                {                    //输出日志                }            }        }        /// <summary>        /// 关闭数据库连接        /// </summary>        private void Close()        {            try            {                conn.Close();            }            catch (Exception ex)            {                //输出日志            }        }

 /// <summary>        /// 执行SQL脚本无返回        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public string ExecuteSqlNonQuery(string sql)        {            string ret = "";            try            {                SqlCommand sqlcom = new SqlCommand();                this.Open();                sqlcom.Connection = conn;                sqlcom.CommandText = sql;                sqlcom.CommandType = CommandType.Text;                sqlcom.CommandTimeout = 600;                sqlcom.ExecuteNonQuery();            }            catch (Exception ex)            {                //输出日志                ret = "Error";            }            finally            {                this.Close();            }            return ret;        }

 /// <summary>        /// 返回单一值        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public dynamic ExecuteSqlScalar(string sql)        {            using (conn)            {                try                {                    this.Open();                    SqlCommand sqlcom = new SqlCommand();                    sqlcom.CommandText = sql;                    sqlcom.Connection = conn;                    sqlcom.CommandTimeout = 600;                    return sqlcom.ExecuteScalar();                }                catch (Exception ex)                {                    //输出日志                }            }            return "";        }

  /// <summary>        /// 填充一个DataSet        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public DataSet ExecuteSqlDataSet(string sql)        {            DataSet ds = null;            try            {                SqlCommand sqlcom = new SqlCommand();                this.Open();                sqlcom.Connection = conn;                sqlcom.CommandText = sql;                sqlcom.CommandTimeout = 600;                SqlDataAdapter da = new SqlDataAdapter();                da.SelectCommand = sqlcom;                ds = new DataSet();                da.Fill(ds);            }            catch (Exception ex)            {                ds = null;            }            finally            {                this.Close();            }            return ds;        }

  /// <summary>        /// 填充一个DataSet        /// </summary>        /// <param name="sql"></param>        /// <param name="tableName"></param>        /// <returns></returns>        public DataSet ExecuteSqlDataSet(string sql, string tableName)        {            DataSet ds = null;            try            {                SqlCommand sqlcom = new SqlCommand();                this.Open();                sqlcom.Connection = conn;                sqlcom.CommandText = sql;                sqlcom.CommandTimeout = 600;                SqlDataAdapter da = new SqlDataAdapter();                da.SelectCommand = sqlcom;                da.Fill(ds, tableName);            }            catch (Exception ex)            {                ds = null;            }            finally            {                this.Close();            }            return ds;        }

  /// <summary>        /// 执行存储过程无返回        /// </summary>        /// <param name="lst"></param>        /// <param name="spName"></param>        /// <returns></returns>        public string ExecuteSpNoQuery(List<object> lst, string spName)        {            string ret = "";            try            {                this.Open();                SqlCommand sqlcom = new SqlCommand();                sqlcom.CommandType = CommandType.StoredProcedure;                sqlcom.CommandText = spName;                sqlcom.Connection = conn;                SqlCommandBuilder.DeriveParameters(sqlcom);                for (int i = 0; i < lst.Count; i++)                {                    sqlcom.Parameters[i + 1].Value = lst[i] == null ? DBNull.Value : lst[i];                }                sqlcom.ExecuteNonQuery();            }            catch (Exception ex)            {                ret = "Error";                //输出日志            }            finally            {                this.Close();            }            return ret;        }

  /// <summary>        /// 执行存储过程返回单一值        /// </summary>        /// <param name="lst"></param>        /// <param name="spName"></param>        /// <returns></returns>        public string ExcuteSpExecuteScalar(List<object> lst, string spName)        {            string ret = "";            try            {                this.Open();                SqlCommand sqlcom = new SqlCommand();                sqlcom.CommandText = spName;                sqlcom.CommandType = CommandType.StoredProcedure;                sqlcom.Connection = conn;                sqlcom.CommandTimeout = 600;                SqlCommandBuilder.DeriveParameters(sqlcom);                for (int i = 0; i < lst.Count; i++)                {                    sqlcom.Parameters[i + 1].Value = lst[i] == null ? DBNull.Value : lst[i];                }                ret = sqlcom.ExecuteScalar().ToString();            }            catch (Exception ex)            {                ret = "";                //输出日志            }            finally            {                this.Close();            }            return ret;        }

        /// <summary>        /// 执行存储过程返回DataSet        /// </summary>        /// <param name="lst"></param>        /// <param name="spName"></param>        /// <returns></returns>        public DataSet ExcuteSpDataSet(List<object> lst, string spName)        {            DataSet ds = null;            try            {                this.Open();                SqlCommand sqlcom = new SqlCommand();                sqlcom.Connection = conn;                sqlcom.CommandText = spName;                sqlcom.CommandType = CommandType.StoredProcedure;                sqlcom.CommandTimeout = 600;                SqlCommandBuilder.DeriveParameters(sqlcom);                for (int i = 0; i < lst.Count; i++)                {                    sqlcom.Parameters[i + 1].Value = lst[i] == null ? DBNull.Value : lst[i];                }                SqlDataAdapter da = new SqlDataAdapter();                da.SelectCommand = sqlcom;                ds = new DataSet();                da.Fill(ds);            }            catch (Exception ex)            {                ds = null;                //输出日志            }            finally            {                this.Close();            }            return ds;        }

 /// <summary>        /// 返回DataTable与返回总计录，用于分页        /// </summary>        /// <param name="lst"></param>        /// <param name="spName"></param>        /// <param name="ReturnName"></param>        /// <param name="ReturnValue"></param>        /// <returns></returns>        public DataTable ExcuteSpPagingDataSet(List<object> lst, string spName, string ReturnName, ref object ReturnValue)        {            DataTable dt = null;            try            {                dt = new DataTable();                this.Open();                SqlCommand sqlcom = new SqlCommand(spName, conn);                sqlcom.CommandTimeout = 600;                sqlcom.CommandType = CommandType.StoredProcedure;                //初使化参数PARAMETER_NAME                SqlCommandBuilder.DeriveParameters(sqlcom);                for (int i = 0; i < lst.Count; i++)                {                    sqlcom.Parameters[i + 1].Value = lst[i] == null ? DBNull.Value : lst[i];                }                DataSet ds = new DataSet();                SqlDataAdapter daMain = new SqlDataAdapter();                daMain.SelectCommand = sqlcom;                //返回一个DataTable                //sqlcom.ExecuteNonQuery();                daMain.Fill(ds);                dt = ds.Tables[0];                ReturnValue = sqlcom.Parameters["@" + ReturnName].Value.ToString();            }            catch (Exception ex)            {                dt = null;            }            finally            {                this.Close();            }            return dt;        }