using System;
using System.Data;
using System.Data.SqlClient;
namespace Common
{
    public class SqlHelper
    {
        //连接字符串
        public static readonly string ConStr = XMLhelper.GetNode("ConnectStr");
        /// <summary>
        /// 执行sql,返回DataSet
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataSet ExecSqlDs(string sql)
        {
            var con = new SqlConnection(ConStr);
            var adp = new SqlDataAdapter(sql, con);
            var ds = new DataSet();
            adp.Fill(ds, "sql");
            return ds;
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sqlParameters">参数</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public static string ExecSql(string sql, params SqlParameter[] sqlParameters)
        {
            string result = string.Empty;
            var con = new SqlConnection(ConStr);
            var command = new SqlCommand(sql, con) { CommandType = CommandType.Text };
            if (sqlParameters.Length > 0)
            {
                CmdGetParam(command, sqlParameters);
            }
            try
            {
                con.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sqlParameters">参数</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public static DataTable ExecSqlDateTable(string sql, params SqlParameter[] sqlParameters)
        {
            var con = new SqlConnection(ConStr);
            var command = new SqlCommand(sql, con) { CommandType = CommandType.Text };
            var dataSet = new DataSet();
            if (sqlParameters.Length > 0)
            {
                CmdGetParam(command, sqlParameters);
            }
            try
            {
                var adapter = new SqlDataAdapter(command);
                adapter.Fill(dataSet);
            }
            catch (Exception)
            {
                return new DataTable();
            }
            return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : new DataTable();
        }

        /// <summary>
        /// 执行SQL 返回第一格的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sqlParameters">参数</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public static string ExecSqlSc(string sql, params SqlParameter[] sqlParameters)
        {
            var con = new SqlConnection(ConStr);
            var command = new SqlCommand(sql, con) { CommandType = CommandType.Text };
            string result = string.Empty;
            if (sqlParameters.Length > 0)
            {
                CmdGetParam(command, sqlParameters);
            }
            try
            {
                con.Open();
                var ex = command.ExecuteScalar();
                if (ex != null && ex != DBNull.Value)
                {
                    result = ex.ToString();
                }
            }
            catch (Exception ex)
            {
                result = "*报错False*:" + ex.Message;
            }
            finally
            {
                con.Close();
            }
            return result;
        }



        //检查执行ExecSqlSc是否报错
        public static bool CheckSc(string result)
        {
            return !result.Contains("*报错False*:");
        }


        /// <summary>
        /// sql命令传入参数
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="sqlParameters">参数数组</param>
        public static void CmdGetParam(SqlCommand cmd, SqlParameter[] sqlParameters)
        {
            if (sqlParameters.Length <= 0) return;
            cmd.Parameters.Clear();
            foreach (var sqlParameter in sqlParameters)
            {
                cmd.Parameters.Add(sqlParameter);
            }
        }


        /// <summary>
        /// 执行SQL事务
        /// </summary>
        /// <param name="sqls">对象二维数组, [,0]为sql语句,[,1]为sql参数数组</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public static string ExecSqlT(object[,] sqls)
        {

            string result = string.Empty;
            var con = new SqlConnection(ConStr);
            con.Open();
            SqlTransaction tan = con.BeginTransaction();        //建立事务
            try
            {
                var command = new SqlCommand { Connection = con, CommandType = CommandType.Text, Transaction = tan };
                
                //计算有多少组语句需要执行
                for (int i = 0; i < sqls.GetLength(0); i++)
                {
                    command.CommandText =  sqls[i, 0].ToString();   //取出语句
                    var sqlParameters = (SqlParameter[])sqls[i, 1]; //取出参数
                    if (sqlParameters.Length > 0)
                    {
                        CmdGetParam(command, sqlParameters);        //参数赋值
                    }
                    command.ExecuteNonQuery();                     //执行
                }
                tan.Commit();         //事务提交
            }
            catch (Exception ex)
            {
                tan.Rollback();     //事务回滚
                result = ex.Message;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        #region reader读取数据

        /// <summary>
        /// 从reader返回string
        /// </summary>
        /// <param name="columnName">查询的列名</param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static string ReaderString(string columnName, SqlDataReader reader)
        {
            return (string)reader[columnName];
        }

        /// <summary>
        /// 从reader返回int
        /// </summary>
        /// <param name="columnName">查询的列名</param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static int ReaderInt(string columnName, SqlDataReader reader)
        {
            return Convert.ToInt32(reader[columnName]);
        }

        /// <summary>
        /// 从reader返回DateTime
        /// </summary>
        /// <param name="columnName">查询的列名</param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static DateTime ReaderDateTime(string columnName, SqlDataReader reader)
        {
            return (DateTime)reader[columnName];
        }

        /// <summary>
        /// 从reader返回bool
        /// </summary>
        /// <param name="columnName">查询的列名</param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static bool ReaderBool(string columnName, SqlDataReader reader)
        {
            return (bool)reader[columnName];
        }


        #endregion
    }
}