using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Com.LingHu.Db
{
    /// <summary>
    /// XX出品
    /// 这个类是用来简化ADO.NET操作的。
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 链接数据库
        /// </summary>
        private string strcon = "";
        /// <summary>
        /// 链接对象
        /// </summary>
        private SqlConnection con = null;
        /// <summary>
        /// SQL命令对象
        /// </summary>
        private SqlCommand cmd = null;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="strcon">链接字符串</param>
        public SqlHelper(string strcon)
        {
            this.strcon = strcon;
            con = new SqlConnection(strcon);
            cmd = new SqlCommand();
            cmd.Connection = con;
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        private void OpenDB()
        {
            con.Open();
        }
        /// <summary>
        /// 关闭数据库，建议在使用SqlDataReader的时候调用
        /// </summary>
        public void CloseDB()
        {
            con.Close();
        }
        /// <summary>
        /// 初始化Command对象
        /// </summary>
        private void PreparedCommand(string sql, CommandType commandType, SqlParameter[] param)
        {
            //设置执行的SQL语句
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            if (commandType != CommandType.Text)
            {
                cmd.CommandType = commandType;
            }
            //初始化参数
            if (param != null && param.Length > 0)
            {
                foreach (SqlParameter p in param)
                {
                    //添加参数
                    cmd.Parameters.Add(p);
                }
            }
            //打开数据库
            OpenDB();
        }
        /// <summary>
        /// 执行非查询语句，Insert，Update，Delete
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        public void ExecuteNonQuery(string sql, params SqlParameter[] param)
        {
            this.PreparedCommand(sql, CommandType.Text, param);
            cmd.ExecuteNonQuery();
            CloseDB();
        }
        /// <summary>
        /// 执行非查询语句，Insert，Update，Delete
        /// </summary>
        /// <param name="sql">sql语句</param>
        public void ExecuteNonQuery(string sql)
        {
            this.ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="param">参数</param>
        public void ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] param)
        {
            this.PreparedCommand(sql, commandType, param);
            cmd.ExecuteNonQuery();
            CloseDB();
        }
        /// <summary>
        /// 要执行的查询语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] param)
        {
            this.PreparedCommand(sql, CommandType.Text, param);
            return cmd.ExecuteReader();
        }
        /// <summary>
        /// 要执行的查询语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteReader(string sql)
        {
            return this.ExecuteReader(sql, null);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="commandType">执行类型</param>
        /// <param name="param">参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteReader(string sql, CommandType commandTye, params SqlParameter[] param)
        {
            this.PreparedCommand(sql, commandTye, param);
            return cmd.ExecuteReader();
        }
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="commandTye">类型</param>
        /// <param name="param">参数</param>
        /// <returns>DataTable</returns>
        public DataTable GetTable(string sql, CommandType commandTye, params SqlParameter[] param)
        {
            this.PreparedCommand(sql, commandTye, param);
            SqlDataAdapter dra = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            dra.Fill(dt);
            CloseDB();
            return dt;

        }
        /// <summary>
        /// 得到Json类型数据
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="commandTye">类型</param>
        /// <param name="param">参数</param>
        /// <returns>String</returns>
        public string GetJson(string sql, CommandType commandTye, params SqlParameter[] param)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dt = this.GetTable(sql, commandTye, param);
            sb.Append("{‘Rows‘:‘" + dt.TableName + "‘");
            sb.Append(",‘data‘:[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sb.Append("{");
                //循环某一行的列
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sb.Append("‘");
                    sb.Append(dt.Columns[j].ColumnName);
                    sb.Append("‘:");
                    sb.Append("‘");
                    if (dt.Rows[i][j] != DBNull.Value)
                    {
                        sb.Append(dt.Rows[i][j].ToString().Trim());
                    }
                    //如果不是最后一列
                    if (j != dt.Columns.Count - 1)
                    {
                        sb.Append("‘,");
                    }
                    else
                    {
                        sb.Append("‘");

                    }

                }
                //如果不是最后一行
                if (i != dt.Rows.Count - 1)
                {
                    sb.Append("},");
                }
                else
                {
                    sb.Append("}");
                }
            }
            sb.Append("]");
            sb.Append("}");
            return sb.ToString();

        }
    }
}