using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;


namespace Zm.Common
{
    /// <summary>
    /// sql数据库帮助类
    /// </summary>
    public static class SqlHelper
    {
        private static string _DefaultDBKEY = "PcbDatabaseEntities";
        /// <summary>
        /// 返回数据库连接字符串
        /// </summary>
        /// <param name="db">数据库连接字符串</param>
        /// <returns></returns>
        public static string SQL_ConnectString(string db = "PcbDatabaseEntities")
        {

            object oExcludeDics = System.Configuration.ConfigurationManager.AppSettings[db];

            if (oExcludeDics != null)
            {
                return oExcludeDics.ToString();

            }
            else
            {

                return "";

            }

        }


        public static DataTable SQL_GetTable(string sql)
        {

            return SQL_GetTable(_DefaultDBKEY, sql);
        }


        public static DataTable SQL_GetTable(string db, string sql)
        {
            string constring = SQL_ConnectString(db);

            SqlConnection con1 = null;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                cmd.Connection = con1;


                da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                con1.Open();
                da.Fill(ds, "Table1");
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.WriteLine(sql + "\n" + ex.ToString());
                throw new Exception("SQL_GetTable", ex);


            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                if (con1 != null)
                    con1.Dispose();
            }


            return ds.Tables[0];


        }

        public static DataTable SQL_GetTable(string db, string sql, params SqlParameter[] sqlParms)
        {
            string constring = SQL_ConnectString(db);

            SqlConnection con1 = null;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                cmd.Connection = con1;

                if (sqlParms != null)
                {
                    foreach (SqlParameter parm1 in sqlParms)
                        cmd.Parameters.Add(parm1);
                }

                da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                con1.Open();
                da.Fill(ds, "Table1");
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.WriteLine(sql + "\n" + ex.ToString());
                throw new Exception("SQL_GetTable", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                if (con1 != null)
                    con1.Dispose();
            }

            return ds.Tables[0];

        }

        public static DataTable Pro_GetTable(string db, string procedureName, params SqlParameter[] sqlParms)
        {
            string constring = SQL_ConnectString(db);

            SqlConnection con1 = null;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand(procedureName, con1);
                cmd.CommandType = CommandType.StoredProcedure;

                if (sqlParms != null)
                {
                    foreach (SqlParameter parm1 in sqlParms)
                        cmd.Parameters.Add(parm1);
                }

                da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                con1.Open();
                da.Fill(ds, "Table1");
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.WriteLine(procedureName + "\n" + ex.ToString());
                throw new Exception("SQL_GetTable", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                if (con1 != null)
                    con1.Dispose();
            }

            return ds.Tables[0];

        }

        public static DataTable Pro_GetTable(string procedureName, SqlParameter[] sqlParms)
        {
            string constring = SQL_ConnectString(_DefaultDBKEY);

            SqlConnection con1 = null;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand(procedureName, con1);
                cmd.CommandType = CommandType.StoredProcedure;

                if (sqlParms != null)
                {
                    foreach (SqlParameter parm1 in sqlParms)
                        cmd.Parameters.Add(parm1);
                }

                da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                con1.Open();
                da.Fill(ds);
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.WriteLine(procedureName + "\n" + ex.ToString());
                throw new Exception("SQL_GetTable", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                if (con1 != null)
                    con1.Dispose();
            }

            return ds.Tables[0];

        }




        public static System.Data.DataRow SQL_FirstRow(string sql)
        {
            return SQL_FirstRow(_DefaultDBKEY, sql);
        }


        /// <summary>
        /// 返回第一行记录，没有返回null
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static System.Data.DataRow SQL_FirstRow(string db, string sql)
        {


            if (string.IsNullOrEmpty(sql)) return null;

            string constring = SQL_ConnectString(db);

            System.Data.DataRow dtRow = null;

            DataTable tb = SQL_GetTable(db, sql);

            if (tb != null)
            {
                if (tb.Rows.Count >= 1)
                    dtRow = tb.Rows[0];
            }


            return dtRow;
        }

        public static object SQL_ExecuteNonQuery(string sql)
        {
            return SQL_ExecuteNonQuery(_DefaultDBKEY, sql);
        }

        public static object SQL_ExecuteScalar(string sql)
        {
            string constring = SQL_ConnectString(_DefaultDBKEY);
            if (string.IsNullOrEmpty(sql)) return null;


            SqlConnection con1 = null;
            SqlCommand cmd = null;

            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = con1;
                con1.Open();
                object re = cmd.ExecuteScalar();
                return re;

            }
            catch (Exception ex)
            {
                throw new Exception("SQL_ExecuteScalar", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (con1 != null)
                    con1.Dispose();

            }


        }


        public static object SQL_ExecuteNonQuery(string db, string sql)
        {

            if (string.IsNullOrEmpty(sql)) return null;

            string constring = SQL_ConnectString(db);

            SqlConnection con1 = null;
            SqlCommand cmd = null;

            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = con1;
                con1.Open();
                object re = cmd.ExecuteNonQuery();

                // System.Diagnostics.Debug.WriteLine("影响行数：" + Helper.ObjectToString(re));

                return re;

            }
            catch (Exception ex)
            {
                throw new Exception("SQL_ExecuteNonQuery", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (con1 != null)
                    con1.Dispose();

            }


        }


        public static object SQL_ExecuteNonQuery(string sql, params SqlParameter[] sqlParms)
        {
            return SQL_ExecuteNonQuery(sql, sqlParms);
        }


        public static object SQL_ExecuteNonQuery(string db, string sql, params SqlParameter[] sqlParms)
        {

            if (string.IsNullOrEmpty(sql)) return null;

            string constring = SQL_ConnectString(db);

            SqlConnection con1 = null;
            SqlCommand cmd = null;

            try
            {
                con1 = new SqlConnection(constring);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                if (sqlParms != null)
                {
                    foreach (SqlParameter parm1 in sqlParms)
                        cmd.Parameters.Add(parm1);
                }

                cmd.Connection = con1;
                con1.Open();
                object re = cmd.ExecuteNonQuery();

                //System.Diagnostics.Debug.WriteLine("影响行数：" + Helper.ObjectToString(re));

                return re;

            }
            catch (Exception ex)
            {
                throw new Exception("SQL_ExecuteNonQuery", ex);
            }
            finally
            {
                if (con1 != null && con1.State != ConnectionState.Closed)
                    con1.Close();

                if (cmd != null)
                    cmd.Dispose();

                if (con1 != null)
                    con1.Dispose();
            }


        }

    }
}