using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ICS.WCF.Base
{
    public class DBhlper
    {
        public static DataTable Query(string sql, string sqlconn)
        {
            try
            {
                if (sqlconn == "")
                {
                    throw new Exception("数据库连接字符串取得失败");
                }

                using (SqlConnection conn = new SqlConnection(sqlconn))
                {
                    conn.Open();
                    using (SqlCommand com = new SqlCommand(sql,conn))
                    {
                        using (SqlDataAdapter dapt = new SqlDataAdapter(com))
                        {
                            DataTable dt = new DataTable();
                            dapt.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static string getDataSource(string sqlconn)
        {
            try
            {
                if (sqlconn == "")
                {
                    throw new Exception("连接U9字符串取得失败");
                }
                SqlConnection conn = new SqlConnection(sqlconn);
                return "[" + conn.DataSource + "].[" + conn.Database + "]";
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        public static bool ReaerEXecute(string sql, string sqlconn)
        {
            try
            {
                SqlConnection conn = new SqlConnection(sqlconn);
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                SqlDataReader a = cmd.ExecuteReader();
                //conn.Close();
                return a.HasRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public static void EXecute(string sql, string sqlconn)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlconn))
                {
                    throw new Exception("数据库连接字符串取得失败");
                }
                using (SqlConnection conn = new SqlConnection(sqlconn))
                {
                    conn.Open();
                    using (SqlCommand com = new SqlCommand(sql,conn))
                    {
                        com.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public static void SQLRunAll(List<string> SqlAll, string sqlconn)
        {
            try
            {
                if (sqlconn == "")
                {
                    throw new Exception("连接U9字符串取得失败");
                }

                SqlConnection connetion = new SqlConnection(sqlconn);                     //创建数据库连接
                SqlTransaction transaction = null;                                   //声明事务
                SqlCommand command = null;

                connetion.Open();                                                     //打开数据库连接
                transaction = connetion.BeginTransaction();             //使用数据库连接创建事务
                command = connetion.CreateCommand();
                command.Transaction = transaction;

                try
                {
                    for (int i = 0; i < SqlAll.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(SqlAll[i]))                        //判断当前SQL语句是否为空
                        {
                            command.CommandText = SqlAll[i];
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();                                               //事务提交
                }
                catch (Exception ex)
                {
                    transaction.Rollback();                                            //事务撤销
                    throw new Exception();
                }
                finally
                {
                    connetion.Close();
                }

            }
            catch (Exception ex)
            {
                throw new Exception();
            }

        }





    }
}
