using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _02SQLHelperC
{
    class Program
    {
        static void Main(string[] args)
        {
            // 对数据库的增删改查
            //创建表
            #region 创建表
            //string sql = "create table Test06(id int not null,name nvarchar(10),pwd varchar(10)";
            //int res = GBYLibrary.SQLHelper.ExecuteNonQuary(sql);
            //Console.WriteLine(res);
            #endregion


            // 插入数据
            #region 无参数，增加数据 插入数据
            //string sql = "insert into Test0806(id, name, pwd) values(1, N‘赵晓虎‘, ‘TigerZhao‘);";//N表示unicode编码，nvarchar输入中文必须带N，否则会变问号
            //int res = GBYLibrary.SQLHelper.ExecuteNonQuery(sql);
            //Console.WriteLine(res);

            //string sql = "insert into Test0806(id, name, pwd) values(1, N‘牛亮亮‘, ‘niuniu‘);";
            //int res = GBYLibrary.SQLHelper.ExecuteNonQuery(sql);
            //Console.WriteLine(res);

            #endregion

            #region 参数化增加
            //string sql = "insert into Test0806(id, name, pwd) values(@id, @name, @pwd);";
            //SqlParameter[] ps = {
            //                        new SqlParameter("@id", (object)2),
            //                        new SqlParameter("@name", "赵剑宇"),
            //// new SqlParameter("@name", System.Data.SqlDbType.NVarChar, 10) { Value = "http://www.mamicode.com/赵剑宇" },
            //                        new SqlParameter("@pwd", "happyZhao") 
            //                    };
            //int res = GBYLibrary.SQLHelper.ExecuteNonQuery(sql, ps);
            //Console.WriteLine(res); 
            #endregion

            // 修改
            #region 参数化修改
            //string sql = "update test0806 set name=@name where id=@id;";
            //SqlParameter[] ps = {
            //                        new SqlParameter("@name", "赵黑皮"),
            //                        new SqlParameter("@id", (object)2)
            //                    };
            //int res = GBYLibrary.SQLHelper.ExecuteNonQuery(sql, ps);

            #endregion


            // 删除
            #region 删除
            //string sql = "delete from test0806 where name=@name";
            //SqlParameter pName = new SqlParameter("@name", "赵晓虎");
            //int count = GBYLibrary.SQLHelper.ExecuteNonQuery(sql, pName);
            //Console.WriteLine(count);

            #endregion

            // 查询姓赵的人多少个？
            #region Scalar
            //string sql = "select count(*) from test0806 where name like @name;";
            //SqlParameter pName = new SqlParameter("@name", "赵%");
            //int count = (int)GBYLibrary.SQLHelper.ExecuteScalar(sql, pName);
            //Console.WriteLine(count); 
            #endregion


            // 读取数据
            #region 读取数据
            //using (SqlDataReader reader = GBYLibrary.SQLHelper.ExecuteReader("select * from Test0806;"))
            //{
            //    while (reader.Read())
            //    {
            //        List<string> list = new List<string>();
            //        for (int i = 0; i < reader.FieldCount; i++)
            //        {
            //            list.Add(reader[i].ToString());
            //        }

            //        Console.WriteLine(string.Join("," , list));
            //    }

            //    // 这里怎么释放呢？
            //} 
            #endregion


            //导出数据库到文本文件
            string sql = "select * from TestDataBase..Student;";
            using (StreamWriter writer = new StreamWriter("s.txt", true, Encoding.Default))
            {
                using (SqlDataReader reader = GBYLibrary.SQLHelper.ExecuteReader(sql))
                {
                    List<string> list = new List<string>();
                    while (reader.Read())
                    {
                        list.Clear();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            list.Add(reader[i].ToString());
                        }
                        writer.WriteLine(string.Join(",", list));
                    }

                }
            }

            Console.WriteLine("OK");
            Console.ReadKey();
        }
    }
}