using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data.OleDb;using System.Data;using System.Configuration;using System.Data.Sql;using System.Data.SqlClient;namespace DAL{    public class SqlHelper    {        public static string connstring = ConfigurationManager.ConnectionStrings["zzconstr"].ConnectionString;        //public static string connstring = "Data Source=ZZ-PC;Initial Catalog=IPTVDB;User ID=sa;Password=sa";        /// 
        /// 执行非查询，返回受影响行数,异常返回-1；        /// 
        ///         ///         ///         /// 
             public static bool ExceNonQuery(string sql, CommandType type, IDataParameter[] pars)        {                        SqlConnection con = new SqlConnection(connstring);            SqlCommand com = new SqlCommand(sql, con);                        if (pars != null && pars.Length > 0)            {                foreach (SqlParameter pp in pars)//把参数集全部加进去                    com.Parameters.Add(pp);            }            try            {                con.Open();                int t = com.ExecuteNonQuery();                if (t > 0)                {                    return true;                }                else                    return false;            }            catch (Exception e) { return false; }            finally            {                com.Parameters.Clear();                com.Dispose();                con.Close();            }        }        /// 
        /// 执行sql语句的查询，返回查询的数量。异常返回-1.        /// 
        ///         ///         ///         /// 
             public static int ExceQuery(string sql, CommandType type, IDataParameter[] pars)        {            SqlConnection con = new SqlConnection(connstring);            SqlCommand com = new SqlCommand(sql, con);            com.CommandType = type;            if (pars != null && pars.Length > 0)            {                foreach (SqlParameter pp in pars)//把参数集全部加进去                    com.Parameters.Add(pp);            }            try            {                con.Open();                if (com.ExecuteScalar() != null)//查询结果为空时返回0                {                    int t = (int)com.ExecuteScalar();                    return t;                }                else                    return -1;            }            catch (Exception e) { return -1; }            finally            {                com.Parameters.Clear();                com.Dispose();                con.Close();            }        }        /// 
        /// 执行查询，返回一个数据集        /// 
        ///         ///         /// 
             public static DataSet ExcueReturnDataset(string sql, IDataParameter[] pars)        {            SqlConnection con = new SqlConnection(connstring);            DataSet set = new DataSet();            SqlCommand com = new SqlCommand(sql, con);            if (pars != null && pars.Length > 0)            {                foreach (SqlParameter pp in pars)//把参数集全部加进去                    com.Parameters.Add(pp);            }                        SqlDataAdapter adpter = new SqlDataAdapter(com);                        try            {                set.Clear();                adpter.Fill(set);                return set;            }            catch (Exception ex) { return null; }            finally {                com.Parameters.Clear();                com.Dispose();                con.Close();             }        }        public static DataSet ExcueReturnDataset(string sql,CommandType type,IDataParameter[] pars)        {            SqlConnection con = new SqlConnection(connstring);            DataSet set = new DataSet();            SqlCommand com = new SqlCommand(sql, con);            com.CommandType = type;            if (pars != null && pars.Length > 0)            {                foreach (SqlParameter pp in pars)//把参数集全部加进去                    com.Parameters.Add(pp);            }            SqlDataAdapter adpter = new SqlDataAdapter(com);            try            {                set.Clear();                adpter.Fill(set);                return set;            }            catch (Exception ex) { return null; }            finally            {                com.Parameters.Clear();                com.Dispose();                con.Close();            }        }        public static IDataReader ExcueReturnDataReader(string sql, IDataParameter[] pars)        {            SqlConnection con = new SqlConnection(connstring);            SqlCommand com = new SqlCommand(sql, con);                        SqlDataReader reader;            if (pars != null && pars.Length > 0)            {                foreach (SqlParameter pp in pars)//把参数集全部加进去                    com.Parameters.Add(pp);            }            try            {                con.Open();                reader = com.ExecuteReader(CommandBehavior.CloseConnection);                return reader;            }            catch (Exception ex)            {                                 return null;             }            finally {                com.Parameters.Clear();                com.Dispose();                //con.Close();            }        }        /// 
        /// 执行存储过程，返回影响的行数                /// 
        /// 存储过程名        /// 存储过程参数        /// 影响的行数        /// 
             public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)        {            using (SqlConnection connection = new SqlConnection(connstring))            {                int result;                connection.Open();                SqlCommand command = new SqlCommand(storedProcName, connection);                command.CommandType = CommandType.StoredProcedure;                if (parameters!= null && parameters.Length > 0)                {                    foreach (SqlParameter pp in parameters)//把参数集全部加进去                        command.Parameters.Add(pp);                }                command.Parameters.Add("@return","").Direction = ParameterDirection.ReturnValue;                rowsAffected = command.ExecuteNonQuery();                result = (int)command.Parameters["@return"].Value;                connection.Close();                return result;            }        }        /// 
        /// 执行存储过程        /// 
        /// 存储过程名        /// 存储过程参数        /// DataSet结果中的表名        /// 
     
      DataSet
             public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)        {            using (SqlConnection connection = new SqlConnection(connstring))            {                DataSet dataSet = new DataSet();                SqlCommand com = new SqlCommand(storedProcName, connection);                com.CommandType =CommandType.StoredProcedure;                if (parameters != null && parameters.Length > 0)                {                    foreach (SqlParameter pp in parameters)//把参数集全部加进去                        com.Parameters.Add(pp);                }                SqlDataAdapter adpter = new SqlDataAdapter(com);                adpter.Fill(dataSet, tableName);                return dataSet;            }        }        /// 
        /// 执行查询语句，返回DataSet        /// 
        /// 查询语句        /// 
     
      DataSet
             public DataSet Query(string SQLString)        {            using (SqlConnection connection = new SqlConnection(connstring))            {                DataSet ds = new DataSet();                try                {                    connection.Open();                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);                    command.Fill(ds, "ds");                }                catch (System.Data.SqlClient.SqlException ex)                {                    throw new Exception(ex.Message);                }                return ds;            }        }    }}