using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace RC.Framework
{
    public class SqlHelper
    {
        #region 数据库访问

        /// <summary>
        ///     连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString; }
        }

        /// <summary>
        /// 查询DataTable数据集
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable Query(string conn, string sql, SqlParameter[] parms = null)
        {
            var table = new DataTable();
            using (var con = new SqlConnection(conn))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    using (var dr = cmd.ExecuteReader())
                    {
                        table.Load(dr);
                    }

                    cmd.Parameters.Clear();
                }

                con.Close();
            }

            return table;
        }
        /// <summary>
        /// 查询DataTable数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable Query(string sql, SqlParameter[] parms = null)
        {
            var table = new DataTable();
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    using (var dr = cmd.ExecuteReader())
                    {
                        table.Load(dr);
                    }

                    cmd.Parameters.Clear();
                }

                con.Close();
            }

            return table;
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] parms = null)
        {
            int result;
            try
            {
                using (var con = new SqlConnection(ConnectionString))
                {
                    con.Open();
                    using (var cmd = new SqlCommand(sql, con))
                    {
                        if (parms != null)
                            foreach (var sqlParameter in parms)
                                cmd.Parameters.Add(sqlParameter);
                        result = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }

                    con.Close();
                }
            }
            catch (Exception ex)
            {
                LogManager.LogException(ex);
                return -1;
            }

            return result;
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, SqlParameter[] parms = null)
        {
            object result;
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                con.Close();
            }
            return result;
        }

        #endregion
    }
}