using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace Youzi.DBUtility
{
    public class SQLHelper
    {

        private static string server = "";
        private static string database = "";
        private static string uid = "";
        private static string pwd = "";
        private static string constring = "";
        public static void IO()
        {
            string path=Application.StartupPath;               //獲得當前程序集所在的路徑 
            StreamReader sr = File.OpenText(path + "\\youzi.ini");
            server= sr.ReadLine()+";";
            database = sr.ReadLine()+";";
            uid = sr.ReadLine()+";";
            pwd = sr.ReadLine()+";";
            sr.Close();
            constring = "" + server + database + uid + pwd;
        }


        //private static string constring = "server=.;database=HisDB19;uid=sa;pwd=;";
          

        /// <summary>
        /// 執行增，刪，改.
        /// </summary>
        /// <param name="sql">不返回結果集的sql語句</param>
        /// <returns>影響的行數</returns>
        public static int ExecuteNonQuery(string sql)
        {
            SQLHelper.IO();
           
            using (SqlConnection con=new SqlConnection(constring))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 執行查詢
        /// </summary>
        /// <param name="sql">查詢語句</param>
        /// <returns>結果集</returns>
        public static SqlDataReader ExecuteDataQuery(string sql)
        {
            SQLHelper.IO();
          
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            return cmd.ExecuteReader();
        }

        public static DataSet SelectAll(string sql)
        {
            SQLHelper.IO();
           
            SqlConnection con = new SqlConnection(constring);
           
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter adap = new SqlDataAdapter();
            adap.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adap.Fill(ds);
            return ds;
        }


        public static DataSet Select(string sql)
        {
            SQLHelper.IO();
           
            SqlConnection con = new SqlConnection(constring);

            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter adap = new SqlDataAdapter();
            adap.SelectCommand = cmd;
            DataSet ds = new DataSet();
            adap.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 獲得最大號
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static string GetMaxOrder(string tablename)
        {
            SQLHelper.IO();
          
            string sql = "select maxOrder from maxOrderTable where tablename='" + tablename + "'";
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            SqlCommand cmd=new SqlCommand(sql,con);
            object obj=cmd.ExecuteScalar();
            int max=int.Parse(obj.ToString());
            max++;
            sql="update maxOrderTable set maxOrder='"+max+"'where tablename='"+tablename+"'";
            cmd.CommandText=sql;
            cmd.ExecuteNonQuery(); 

            return max.ToString().PadLeft(4,'0');
        }

       
    }
}