using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;


namespace Kindeditor.DBUtility
{
    public class SqlServerHelp
    {

        
/// <summary>
/// BaseClass 的摘要描述
/// </summary>

        public static SqlConnection DBCon()
        {
            // return new SqlConnection("server=.;Database=ExanOnline;user=sa;pwd=sa;");
            // return new SqlConnection("Data Source=(local);Initial Catalog=ExanOnline;Integrated Security=True");
            //  string conStr = ConfigurationManager.ConnectionStrings["Paperstring"].ToString();
            //  string connString = ConfigurationManager.AppSettings["Paperstring6"].ToString();

            // string connString = ConfigurationManager.ConnectionStrings["ConnectionServer"].ConnectionString;

            // return new SqlConnection(connString);
            // return new SqlConnection("Data Source=iZo33jour9x3ceZ;Initial Catalog=paper;Persist Security Info=True;User ID=dhu;Password=XUri1234;");
   return new SqlConnection("Data Source=CY106s081;Initial Catalog=DHU;Persist Security Info=True;User ID=SJ;Password=XUri1234;");//网上
   // return new SqlConnection("Data Source=WINDOWS-E5P5KCA\\DHUSUN;Initial Catalog=paper;Persist Security Info=True;User ID=DHU;Password=XUri1234;");//159机器上
   // return new SqlConnection("Data Source=DESKTOP-GPJSMAQ\\DHU;Initial Catalog=paper;Persist Security Info=True;User ID=dhu;Password=XUri1234;"); //本机
        }

        //=================================================
        //功能描述：教师进来，该教师下的答辩学生、评阅学生、开题指导学生都进来了
        //输入参数：教师的ID，出来学生的表
        //时间：2007.11.10
        //=================================================
        public static DataTable GetstuTable(string TeaId, int  lx1)
        {
            DataTable Dt = new DataTable();
           
                string sql = "select distinct gid from groupinfo  where sid='" + TeaId + "' and vtype='1' and  grouptype="+lx1+" ";
                DataTable dt2 = SqlServerHelp.GetDataTable(sql);
                string sql2 = "select a.sid,a.sname,a.sid+a.sname as na from student a,groupinfo b where a.sid=b.sid   and  grouptype=" + lx1 + " and vtype='0' ";
                int i = 0;
                int k = dt2.Rows.Count;

                foreach (DataRow dr in dt2.Rows)
                {
                   string gid = dr["gid"].ToString();
                    sql2 = sql2 + " and b.gid='" + gid + "' ";
                    i = i + 1;
                    if(i<k)
                    {
                        sql2 = sql2 + "  union all  ";
                    }
                }
                Dt = GetDataTable(sql2);
                //DataTable newDataTable = DataTable1.Copy();
                ////添加baiDataTable2的数据
                //foreach (DataRow dr in DataTable2.Rows)
                //{
                //    newDataTable.ImportRow(dr);
                //}
                return Dt;
           
        }


        public static string getSingleAns(RadioButtonList rbl)
        {
            string Answer = "";
            if (rbl.SelectedIndex != -1)
            {
                Answer = rbl.SelectedValue;
            }
            else
            {
                Answer = "";
            }
            return Answer;
        }

        public static string getGroupId()
        {
            DateTime Dt66 = DateTime.Now;
            string id1 = string.Format("{0:yyyyMMddHHmmss}", Dt66);

            string id = "";
            //  string sqlSeq = " SELECT NEXT VALUE FOR GrpSeq1";
            string sqlSeq = "SELECT count(*) from groupinfo";
            id= id1+ SqlServerHelp.RunSqlReturn(sqlSeq);
            return id;

            //CREATE SEQUENCE GrpSeq1 AS TINYINT
            // START WITH 1
            ///INCREMENT BY 1;
            //  GO
        }

        public static void Show(string msg)
        {

            HttpContext.Current.Response.Write("<script language='javascript'>alert('" + msg + "');</script>");
        }

        public static string FY(string id)
        {
            string re = "1";
            if (id == "A")
            {
                re = "1";
            }

            if (id == "B")
            {
                re = "2";
            }

            if (id == "C")
            {
                re = "3";
            }

            if (id == "D")
            {
                re = "4";
            }
            if (id == "o")
            {
                re = "8";
            }

            return re;
        }


        public static void Message(System.Web.UI.Page page, string nr)
        {

            page.RegisterStartupScript("message", "<script language='javascript' defer>alert('" + nr + "');</script>");
        }

        public static string NFY(string id)
        {
            string re = "A";
            if (id == "1")
            {
                re = "A";
            }

            if (id == "2")
            {
                re = "B";
            }

            if (id == "3")
            {
                re = "C";
            }

            if (id == "4")
            {
                re = "D";
            }

            if (id == "5")
            {
                re = "A";
            }

            return re;
        }

        public static void BindDG(GridView dg, string id, string strsql, string Tname)
        {
            SqlConnection conn = DBCon();
            SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, Tname);
            dg.DataSource = ds.Tables[Tname];
            dg.DataKeyNames = new string[] { id };
            dg.DataBind();
        }
        public static void OperateData(string strsql)
        {
            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand cmd = new SqlCommand(strsql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public static bool CheckStudent(string studentNum, string studentPwd)
        {
            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand cmd = new SqlCommand("select count(*) from tb_student where StudentNum='" + studentNum + "' and StudentPwd='" + studentPwd + "'", conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            conn.Close();
        }

        public static string getStuName(string sNum)
        {
            string sql = "select sname from student where sid='" + sNum + "'";
            string aa = "";
            aa = FindString(sql);
            return aa;
        }

        public static string getStuXH(string name)
        {
            string sql = "select sid from student where sname='" + name + "'";
                string aa = "";
            aa = FindString(sql);
            return aa;
        }

        public static bool CheckTeacher(string teacherNum, string teacherPwd)
        {
            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand cmd = new SqlCommand("select count(*) from teacher where tid='" + teacherNum + "' and Pwd='" + teacherPwd + "'", conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            if (i > 0)
                return true;
            else
                return false;
            conn.Close();
        }

        public static string getTeaName(string teacherNum)
        {
            string sql = "select tname from teacher where tid='" + teacherNum + "' ";
                string aa = "";
            aa = FindString(sql);
            return aa;
        }

        public static string getTeaGH(string teacher)
        {
            string sql = "select tid from teacher where tname='" + teacher + "' ";
                string aa = "";
            aa = FindString(sql);
            return aa;
        }

        public static bool CheckAdmin(string adminNum, string adminPwd)
        {
            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand cmd = new SqlCommand("select count(*) from tb_Admin where AdminNum='" + adminNum + "' and AdminPwd='" + adminPwd + "'", conn);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            if (i > 0)
                return true;
            else
                return false;
            conn.Close();
        }


        //=================================================
        //功能描述：执行SQL语句
        //输入参数：sql，查询的SQL语句
        //时间：2007.11.10
        //=================================================
        public static void execsql(string sql)
        {

            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            com.ExecuteNonQuery();
            conn.Close();




        }

        public static string getProblemType(string sql)
        {

            string str = "单选题";
            if(sql=="PD")
            {
                str = "判断题";
                return str;
            }

            if (sql == "JD")
            {
                str = "简答题";
                return str;
            }

            if (sql == "MX")
            {
                str = "多选题";
                return str;
            }

            if (sql == "TK")
            {
                str = "填空题";
                return str;
            }
            if (sql == "DX")
            {
                str = "单选题";
                return str;
            }
            return str;
        }

        public static string RunSqlReturn(string sql)
        {

            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);

            string hang = Convert.ToString(com.ExecuteScalar());
            conn.Close();
            return hang;




        }

    

        public static int ExecuteCmd(string sql)
        {
            try
            {
                SqlConnection conn = DBCon();
                conn.Open();
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
            catch
            {
                return -1;
            }
            finally
            {

            }
        }
        //=================================================
        //功能描述：获取DATASET
        //输入参数：sql，查询的SQL语句
        //返回值：DataSet
        //时间：2007.11.10
        //=================================================
        public static DataSet GetDataset(string sql)
        {

            SqlConnection conn = DBCon();
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, "ds");
            closeConnection();
            return ds;



        }

        public static DataSet GetDataSet(string sql)
        {

            SqlConnection conn = DBCon();
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, "ds");
            closeConnection();
            return ds;



        }

        public static DataTable GetDataTable(string sql)
        {
            try
            {

                SqlConnection conn = DBCon();
                SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
                DataTable Dt = new DataTable();
                adp.Fill(Dt);
                return Dt;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                closeConnection();
            }
        }
        //=================================================
        //功能描述：获取DATASET1
        //输入参数：sql，查询的SQL语句
        //返回值：DataSet
        //时间：2007.11.10
        //=================================================
        public static DataSet select(string sql, string tablename)
        {

            SqlConnection conn = DBCon();
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, tablename);
            closeConnection();

            return ds;



        }
        //=================================================
        //功能描述：获取某个字段数据
        //输入参数：sql，查询的SQL语句
        //返回值：hang
        //时间：2007.11.10
        //=================================================
        public static string FindString(string sql)
        {

            SqlConnection conn = DBCon();
            conn.Open();
            SqlCommand com = new SqlCommand(sql, conn);

            string hang = Convert.ToString(com.ExecuteScalar());
            conn.Close();
            return hang;




        }
        //=================================================
        //功能描述：对DATAGRIG进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void binddatagrid(string sql, DataGrid dg)
        {


            DataSet ds = GetDataset(sql);
            dg.DataSource = ds.Tables[0].DefaultView;

            closeConnection();
            dg.DataBind();



        }
        //=================================================
        //功能描述：对DropDownList进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void bindDropDownList(string sql, DropDownList dl, string class_name, string id)
        {


            DataSet ds = GetDataset(sql);
            dl.DataSource = ds.Tables[0].DefaultView;
            dl.DataTextField = class_name;
            dl.DataValueField = id;

            closeConnection();

            dl.DataBind();



        }
        //=================================================
        //功能描述：对RadioButtonList进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void bindRadioButtonList(string sql, RadioButtonList rl, string class_name, string id)
        {


            DataSet ds = GetDataset(sql);
            rl.DataSource = ds.Tables[0].DefaultView;
            rl.DataTextField = class_name;
            rl.DataValueField = id;
            rl.SelectedIndex = 0;
            closeConnection();
            rl.DataBind();



        }
        //=================================================
        //功能描述：对GridView进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void bindGridView(string sql, GridView dg)
        {

            SqlConnection conn = DBCon();
            DataSet ds = GetDataset(sql);
            dg.DataSource = ds.Tables[0].DefaultView;

            closeConnection();
            dg.DataBind();



        }
        //=================================================
        //功能描述：对datalist进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dl，需要绑定的datalist控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void binddatalist(string sql, DataList dl)
        {

            SqlConnection conn = DBCon();
            DataSet ds = GetDataset(sql);
            dl.DataSource = ds.Tables[0].DefaultView;

            closeConnection();
            dl.DataBind();



        }
        //=================================================
        //功能描述：对repeater进行数据绑定，无排序
        //输入参数：sql，查询的SQL语句；dl，需要绑定的repeater控件
        //返回值：无
        //时间：2007.11.10
        //=================================================
        public static void bindrepeater(string sql, Repeater rp)
        {

            SqlConnection conn = DBCon();
            DataSet ds = GetDataset(sql);
            rp.DataSource = ds.Tables[0].DefaultView;

            closeConnection();
            rp.DataBind();



        }

        private static void closeConnection()
        {
            SqlConnection conn = DBCon();
            SqlCommand cmd = new SqlCommand();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
                cmd.Dispose();
            }
        }


        /// <summary>
        /// 递归拷贝所有子目录。
        /// </summary>
        /// <param >源目录</param>
        /// <param >目的目录</param>
        public static void copyDirectory(string sPath, string dPath)
        {
            string[] directories = System.IO.Directory.GetDirectories(sPath);
            if (!System.IO.Directory.Exists(dPath))
                System.IO.Directory.CreateDirectory(dPath);
            System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(sPath);
            System.IO.DirectoryInfo[] dirs = dir.GetDirectories();
            CopyFile(dir, dPath);
            if (dirs.Length > 0)
            {
                foreach (System.IO.DirectoryInfo temDirectoryInfo in dirs)
                {
                    string sourceDirectoryFullName = temDirectoryInfo.FullName;
                    string destDirectoryFullName = sourceDirectoryFullName.Replace(sPath, dPath);
                    if (!System.IO.Directory.Exists(destDirectoryFullName))
                    {
                        System.IO.Directory.CreateDirectory(destDirectoryFullName);
                    }
                    CopyFile(temDirectoryInfo, destDirectoryFullName);
                    copyDirectory(sourceDirectoryFullName, destDirectoryFullName);
                }
            }

        }

        /// <summary>
        /// 拷贝目录下的所有文件到目的目录。
        /// </summary>
        /// <param >源路径</param>
        /// <param >目的路径</param>
        public static void CopyFile(System.IO.DirectoryInfo path, string desPath)
        {
            string sourcePath = path.FullName;
            System.IO.FileInfo[] files = path.GetFiles();
            foreach (System.IO.FileInfo file in files)
            {
                string sourceFileFullName = file.FullName;
                string destFileFullName = sourceFileFullName.Replace(sourcePath, desPath);
                file.CopyTo(destFileFullName, true);
            }
        }
    

}


    
}
