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";

        /// <summary>

        /// 执行非查询，返回受影响行数,异常返回-1；

        /// </summary>

        /// <param name="sql"></param>

        /// <param name="type"></param>

        /// <param name="pars"></param>

        /// <returns></returns>

        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 > )

            {

                foreach (SqlParameter pp in pars)//把参数集全部加进去

                    com.Parameters.Add(pp);

            }

            try

            {

                con.Open();

                int t = com.ExecuteNonQuery();

                if (t > )

                {

                    return true;

                }

                else

                    return false;

            }

            catch (Exception e) { return false; }

            finally

            {

                com.Parameters.Clear();

                com.Dispose();

                con.Close();

            }

        }

        /// <summary>

        /// 执行sql语句的查询，返回查询的数量。异常返回-1.

        /// </summary>

        /// <param name="sql"></param>

        /// <param name="type"></param>

        /// <param name="pars"></param>

        /// <returns></returns>

        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 > )

            {

                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 -;

            }

            catch (Exception e) { return -; }

            finally

            {

                com.Parameters.Clear();

                com.Dispose();

                con.Close();

            }

        }

        /// <summary>

        /// 执行查询，返回一个数据集

        /// </summary>

        /// <param name="sql"></param>

        /// <param name="pars"></param>

        /// <returns></returns>

        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 > )

            {

                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 > )

            {

                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 > )

            {

                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();

            }

        }

        /// <summary>

        /// 执行存储过程，返回影响的行数

        /// </summary>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <param name="rowsAffected">影响的行数</param>

        /// <returns></returns>

        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 > )

                {

                    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;

            }

        }

        /// <summary>

        /// 执行存储过程

        /// </summary>

        /// <param name="storedProcName">存储过程名</param>

        /// <param name="parameters">存储过程参数</param>

        /// <param name="tableName">DataSet结果中的表名</param>

        /// <returns>DataSet</returns>

        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 > )

                {

                    foreach (SqlParameter pp in parameters)//把参数集全部加进去

                        com.Parameters.Add(pp);

                }

                SqlDataAdapter adpter = new SqlDataAdapter(com);

                adpter.Fill(dataSet, tableName);

                return dataSet;

            }

        }

        /// <summary>

        /// 执行查询语句，返回DataSet

        /// </summary>

        /// <param name="SQLString">查询语句</param>

        /// <returns>DataSet</returns>

        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;

            }

        }

    }

}