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

namespace Hotel.DAO
{
    public class GetDatabase
    {
        //數據庫連接對象
        private SqlConnection con = null;
        private SqlDataAdapter adp = null;
        private DataSet ds = null;
        private SqlCommand cmd = null;
        //private SqlParameter para = null;

        //打開數據庫連接的方法
        public GetDatabase()
        {
            this.con = new SqlConnection("server=.;database=hotelDB;uid=sa;pwd=;");
            con.Open();
        }

        //執(zhí)行數據庫增加、修改、刪除等操作的方法
        public int ExecuteSql(string sql)
        {
            int i = 0;
            try
            {
                this.cmd = new SqlCommand(sql, this.con);
                i = cmd.ExecuteNonQuery();
            }
            catch(SqlException ex)
            {
                Console.WriteLine(ex.Message);          
            }
            return i;
        }

        //返回數據集DataSet查詢操作
        public DataSet GetDataSet(string sql,string nTableName)
        {
            try 
            {
                this.adp = new SqlDataAdapter(sql,this.con);
                ds = new DataSet();
                adp.Fill(ds, "nTableName");
            }
            catch(SqlException er)
            {
                Console.WriteLine(er.Message);
            }
            return ds;
        }

        //執(zhí)行數據庫只讀操作的方法
        public SqlDataReader GetDataReader(string sql)
        {
            SqlDataReader dr = null;
            try
            {
                this.cmd= new SqlCommand(sql, this.con);
                dr = cmd.ExecuteReader();
            }
            catch (SqlException er)
            {
                Console.WriteLine(er.Message);
            }
            return dr;
        }
        public void DrClose(SqlDataReader dr)
        {
            if (dr != null)
            {
                dr.Close();
            }
 
        }

        //數據庫讀取每一行數據的方法
        //public int GetReaDNum(string sql)
        //{
        //    int t = 0;
        //    SqlDataReader dr = null;
        //    try
        //    {
        //        this.cmd = new SqlCommand(sql, this.sqlcon);
        //        dr = cmd.ExecuteReader();
        //        if (dr.HasRows)
        //        {
        //            t = 1;
        //        }
        //    }
        //    catch (SqlException e)
        //    {
        //        Console.WriteLine(e.Message);
        //    }
        //    return t;
        //}

        //使用儲存過程
        public int ExecuteProc(string name,SqlParameter [] para)
        {
            int k = 0;
            using (cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = name;
                foreach (SqlParameter pa in para)
                {
                    cmd.Parameters.Add(pa);
                }
                k = cmd.ExecuteNonQuery();

            }
            return k;
        }
      


        //關閉數據庫連接的方法，釋放數據庫資源
        public void DataClose()
        {
            if (this.con != null)
            {
                this.con.Close();
            }
        }
    }
}