using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace 数据库
{
    class AdapterSqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["connLocalhost"].ToString();
            }
        }

        /// <summary>
        /// 查询数据(sql语句, sql语句的参数, 储存类型)
        /// </summary>
        /// <returns> DataSet类型的数据集 </returns>
        private DataSet GetList(string sql, SqlParameter[] parm, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                //数据识别器对象(不需要开关)
                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                DataSet data = new DataSet();

                if (parm != null && parm.Length != 0)
                {
                    foreach (SqlParameter par in parm)
                    {
                        //给命令对象添加参数(比较麻烦)
                        adapter.SelectCommand.Parameters.Add(par);
                    }
                }

                //指定储存方式(储存过程和sql语句)
                adapter.SelectCommand.CommandType = type;

                adapter.Fill(data);
                return data;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询数据(sql语句, sql语句的参数, 储存类型)
        /// </summary>
        /// <returns> DataTable类型的数据集 </returns>
        private DataTable GetDataTable(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, new SqlConnection(ConnectionString));

            //--------------方式壹------------//
            //DataTable data = http://www.mamicode.com/new DataTable();>//adapter.Fill(data);
            //return data;

            //--------------方式贰------------//
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "User");
            return dataSet.Tables["User"];
        }

        /// <summary>
        /// 对外暴漏的测试方法
        /// </summary>
        public static void Show()
        {
            AdapterSqlHelper adapter = new AdapterSqlHelper();

            //带参数的sql语句以及参数数组的创建(占位符:@UserId, 值:3)
            string sql = "select * from User where [UserId] = @UserId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@UserId", 3),
            };

            DataTable data = adapter.GetList(sql, parm, CommandType.Text).Tables[0];
            foreach (DataRow row in data.Rows)
            {
                Console.WriteLine(row["UserName"]);
            }
        }
    }
}