C# ClassConnectDB.Close方法代码示例

        public static string selectCuriID(string curYear, string curCourse, string curGroup)
        {
                    ClassConnectDB con=new ClassConnectDB();
            string reid="";
            try
            {
            string [email protected]"SELECT Curri_Id
                                FROM Curriculum Where [email protected] AND [email protected] AND [email protected]";
            string Addvalue="@year,@co,@group";
            string value=curYear+","+curCourse+","+curGroup;
           SqlDataReader dr= con.SelectWhereSqlDataReader(sqlSelect,Addvalue,value);
           if (dr.Read())
           {
               reid = dr["Curri_Id"].ToString();
           }


            return reid;

            }
            catch (Exception)
            {

                return "";
            }
            con.Close();
            
        }

        public static DataTable getCommentForPost(int thePost)
        {
            try
                {
                    string sql = @"SELECT   TOP 1  [KmComPost_ID]
                                  ,[KmPost_ID]
                                  ,[ComPost_Detail]
                                  ,REPLACE(CONVERT(VARCHAR(11), Create_date, 106), ' ', '-') AS datepostkm
                                  ,CONVERT(VARCHAR(12),Create_time ,114) as timepostkm
                                  ,[Create_user_id]
								  ,[Create_user_type]
                                  FROM KmCommentPost where KmPost_ID='" + thePost + "'order by [Create_date]  asc ";

                    ClassConnectDB conn = new ClassConnectDB();
                    SqlDataReader drr = conn.SelectSqlDataReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(drr);

                    conn.Close();
                    return dt;
                }
                catch (Exception)
                {
                    return null;
                }


        }

        public static Entity.Student checkForgotPassword(string username, string email)
        {
            try
            {
                string sqlforgot = "  SELECT * FROM Student WHERE [email protected] AND [email protected]";
                string Addvalue = "@user,@email";
                string value = username + "," + email;

                Entity.Student stdCheck = new Entity.Student();

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
                //SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
                if (readCheckRole.Read())
                {
                    stdCheck.Std_FName = readCheckRole["Std_FName"].ToString();
                    stdCheck.Std_LName = readCheckRole["Std_LName"].ToString();
                    stdCheck.Std_Username = readCheckRole["Std_Username"].ToString();
                    stdCheck.Std_Password = readCheckRole["Std_Password"].ToString();
                    stdCheck.Std_email = readCheckRole["Std_email"].ToString();
                }
                conn.Close();
                return stdCheck;

            }
            catch (Exception)
            {

                return null;
            }
        }

        public static bool insertSubject(Entity.CurriculumInfo subject)
        {
            
             subcode=subject.StructSub_Code.ToString();
            subNameTha=subject.StructSub_NameTha.ToString();
            subNameEn=subject.StructSub_NameEn.ToString();
             detail=subject.StructSub_Detail.ToString();
            subCre=subject.StructSub_Credit.ToString();
            curID=subject.Curri_Id.ToString();

            ClassConnectDB conn = new ClassConnectDB();
            //conn.TransStart();
            try
            {

                string insertSubject = "INSERT INTO StructSubject(StructSub_Code, StructSub_NameTha, StructSub_NameEn, StructSub_Detail, StructSub_Credit, Curri_Id)VALUES(@code,@nameTha,@nameEn,@detail,@struCre,@curiID)";
                string AddvalueSubject = "@code,@nameTha,@nameEn,@detail,@struCre,@curiID";
                string value = subcode + "," + subNameTha + "," + subNameEn + "," + detail + "," + subCre + "," + curID;
                conn.InsertValue(insertSubject,AddvalueSubject,value);

                return true;

            }
            catch (Exception)
            {
                return true;
            }
            conn.Close();

            
        }

        public static Entity.Teacher checkForgotPassword(string username, string email)
        {
            try
            {
                string sqlforgot = "  SELECT * FROM Teacher WHERE [email protected] AND [email protected]";
                string Addvalue = "@user,@email";
                string value = username + "," + email;

                Entity.Teacher tchCheck = new Entity.Teacher();

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
                //SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
                if (readCheckRole.Read())
                {
                    tchCheck.Tch_FName = readCheckRole["Tch_FName"].ToString();
                    tchCheck.Tch_LName = readCheckRole["Tch_LName"].ToString();
                    tchCheck.Tch_username = readCheckRole["Tch_username"].ToString();
                    tchCheck.Tch_password = readCheckRole["Tch_password"].ToString();
                    tchCheck.Tch_email = readCheckRole["Tch_email"].ToString();
                }
                conn.Close();
                return tchCheck;

            }
            catch (Exception)
            {

                return null;
            }
        }

        public static Entity.Employee checkForgotPassword(string username, string email)
        {

            try
            {
                string sqlforgot = "  SELECT * FROM Employee WHERE [email protected] AND [email protected]";
                string Addvalue = "@user,@email";
                string value = username + "," + email;

                Entity.Employee empCheck = new Entity.Employee();

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
                //SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
                if (readCheckRole.Read())
                {
                    empCheck.Emp_FName = readCheckRole["Emp_FName"].ToString();
                    empCheck.Emp_LName = readCheckRole["Emp_LName"].ToString();
                    empCheck.Emp_username = readCheckRole["Emp_username"].ToString();
                    empCheck.Emp_password = readCheckRole["Emp_password"].ToString();
                    empCheck.Emp_Email = readCheckRole["Emp_Email"].ToString();
                }
                conn.Close();
                return empCheck;

            }
            catch (Exception)
            {

                return null;
            }

        }

        public static bool insertDetailTeachAfter(string year, string level, string term, string group, string subject, string userid)
        {
            ClassConnectDB conn = new ClassConnectDB();

            string sqlcheckvalue = "SELECT  DetailTech_ID FROM  DetailTech WHERE [email protected] and [email protected] and DetailTeach_Year [email protected] and [email protected] and  [email protected]";
            string addvaluecheck = "@level,@class,@year,@term,@code";
            string valuecheck = level + "," + group + "," + year + "," + term + "," + subject;
            SqlDataReader drrcheck = conn.SelectWhereSqlDataReader(sqlcheckvalue, addvaluecheck, valuecheck);
            if (!drrcheck.HasRows)
            {
                string sql = @"INSERT INTO DetailTech
                      (DetailTech_Level, DetailTech_Class, DetailTeach_Year, DetailTech_Term, StructSub_Code, Create_user, CreateDate)
                        VALUES(@level,@class,@year,@term,@code,@user,getDate())";

                string addvalue = "@level,@class,@year,@term,@code,@user";
                string value = level + "," + group + "," + year + "," + term + "," + subject + "," + userid;
                bool insert = conn.InsertValue(sql, addvalue, value);
                conn.Close();
                if (insert) { return true; }
                else { return false; }
            }
            else
            {
                return false;
            }
        }

 public static bool deleteDetailTeach(string dchID)
 {
     ClassConnectDB conn = new ClassConnectDB();
     string sql = "DELETE FROM DetailTech WHERE DetailTech_ID='" + dchID + "'";
     bool del = conn.QueryExecuteNonQuery(sql);
     conn.Close();
     return del;
 }

 public static DataTable selectCommentPost(int postID)
 {
     ClassConnectDB conn = new ClassConnectDB();
     string sql = @" SELECT Top 1 [ComPost_ID]
                       ,[Post_ID]
                       ,[ComPost_Detail]
                       ,[Create_date]
                       ,[Create_time]
                       ,[Create_user_id]
                       ,[Create_user_type]
                     FROM [CommentPost]
                     where Post_ID = " + postID + " ORDER BY Create_date desc ,  Create_time desc";
     SqlDataReader drr = conn.SelectSqlDataReader(sql);
     DataTable dt = new DataTable();
     dt.Load(drr);
     conn.Close();
     return dt;
 }

        public static Entity.Student checkRoleLogin(string username, string password)
        {
            try
            {
                Entity.Student stdlog = new Entity.Student();

                string sqlchekRole = " SELECT * FROM Student WHERE (Std_Username = @user) AND (Std_Password = @pass)";
                string Addvalue = "@user,@pass";
                string value = username + "," + password;

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
                if (readCheckRole.Read())
                {

                    stdlog.Std_Type = readCheckRole["Std_Type"].ToString();
                    stdlog.Std_FName = readCheckRole["Std_FName"].ToString();
                    stdlog.Std_LName = readCheckRole["Std_LName"].ToString();
                    stdlog.Std_Campus_Code = readCheckRole["Std_Campus_Code"].ToString();
                    stdlog.Std_YearEducate = readCheckRole["Std_YearEducate"].ToString();
                    stdlog.Std_Username = readCheckRole["Std_Username"].ToString();
                    stdlog.Std_Password = readCheckRole["Std_Password"].ToString();
                    stdlog.std_picturepath = readCheckRole["Std_picturepath"].ToString();
                }

                string iplog = Common.network.showIp();
                string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
                string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
                string tid = readCheckRole["Std_Campus_Code"].ToString();
                string insertLog = "INSERT INTO LogLogInStd(Log_IP, Log_Date, Log_timeStart, Std_Campus_Code) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
                conn.QueryExecuteNonQuery(insertLog);


                conn.Close();
                return stdlog;


            }
            catch (Exception)
            {

                return null;
            }
        }

        public static Entity.Teacher checkRoleLogin(string username, string password)
        {
            Entity.Teacher tch = new Entity.Teacher();
            try
            {
                string sqlchekRole = "  SELECT * FROM Teacher WHERE   [email protected] AND [email protected]";
                string Addvalue = "@user,@pass";
                string value = username + "," + password;

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
                if (readCheckRole.Read())
                {
                    tch.Tch_ID = readCheckRole["Tch_ID"].ToString();
                    tch.Tch_Type = readCheckRole["Tch_Type"].ToString();
                    tch.Tch_FName = readCheckRole["Tch_FName"].ToString();
                    tch.Tch_LName = readCheckRole["Tch_LName"].ToString();
                    tch.Tch_username = readCheckRole["Tch_username"].ToString();
                    tch.Tch_password = readCheckRole["Tch_password"].ToString();
                    tch.Tch_picturepath = readCheckRole["Tch_picturepath"].ToString();

                }

                string iplog = Common.network.showIp();
                string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
                string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
                string tid = readCheckRole["Tch_ID"].ToString();
                string insertLog = "INSERT INTO LogLoginTeach(Log_IP, Log_Date, Log_timeStart, Tch_ID) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
                conn.QueryExecuteNonQuery(insertLog);
                conn.Close();

                return tch;



            }
            catch (Exception)
            {

                return null;
            }

        }

        public static Entity.Employee checkRoleLogin(string username, string password)
        {

            try
            {
                Entity.Employee emp = new Entity.Employee();

                string sqlchekRole = "  SELECT * FROM Employee WHERE [email protected] AND [email protected]";
                string Addvalue = "@user,@pass";
                string value = username + "," + password;

                ClassConnectDB conn = new ClassConnectDB();
                SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
                if (readCheckRole.Read())
                {
                    emp.Emp_ID = readCheckRole["Emp_ID"].ToString();
                    emp.Emp_Type = readCheckRole["Emp_Type"].ToString();
                    emp.Emp_LName = readCheckRole["Emp_LName"].ToString();
                    emp.Emp_FName = readCheckRole["Emp_FName"].ToString();
                    emp.Emp_username=readCheckRole["Emp_username"].ToString();
                    emp.Emp_password = readCheckRole["Emp_password"].ToString();
                }

                string iplog = Common.network.showIp();
                string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
                string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
                string tid = readCheckRole["Emp_ID"].ToString();
                string insertLog = "INSERT INTO LogLoginEmp(Log_IP, Log_Date, Log_timeStart, Emp_id) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
                conn.QueryExecuteNonQuery(insertLog);
             

                conn.Close();
                return emp;


            }
            catch (Exception)
            {

                return null;
            }
        }

 public static string selectCuriYear(string yearedu)
 {
     string year = "";
     try
     {
         string sql = @"SELECT max( [Curri_Year]) as yearEducate
                       FROM [Curriculum]
                       where  curri_year <= '" + yearedu + "' ";
         ClassConnectDB conn = new ClassConnectDB();
         SqlDataReader drr = conn.SelectSqlDataReader(sql);
         if (drr.Read())
         {
             year = drr["yearEducate"].ToString();
         }
         conn.Close();
         return year;
     }
     catch (Exception)
     {
         return null;
     }
 }

 public static System.Data.DataTable selectUserPost(string userid)
 {
     try
     {
         ClassConnectDB conn = new ClassConnectDB();
         string sql = @"SELECT  [Post_ID]
                   ,[Post_Detail]
                   ,[Create_date]
                   ,[Create_time]
                   ,[Create_user_id]
                   ,[Create_user_type]
               FROM [Post]
               where Create_user_id = '" + userid + "'  ORDER BY Create_date desc ,  Create_time desc ";
         SqlDataReader drr = conn.SelectSqlDataReader(sql);
         DataTable dt = new DataTable();
         dt.Load(drr);
         conn.Close();
         return dt;
     }
     catch (Exception ex) {
         return null;
     }
 }

        public static DataTable selectShowStudentSuject(string code, string user)
        {
            try
            {
                string sql = @"SELECT     SchoolRecord.SchoolRecord_Level, SchoolRecord.SchoolRecord_Term, SchoolRecord.StructSub_Code, StructSubject.StructSub_NameTha,
                                           SchoolRecord.SchoolRecord_Grade   FROM         SchoolRecord INNER JOIN
                                                StructSubject ON SchoolRecord.StructSub_Code = StructSubject.StructSub_Code
                                                where SchoolRecord.Std_Campus_Code='" + user + "'  AND SchoolRecord.StructSub_Code='" + code + "' ";

                ClassConnectDB conn = new ClassConnectDB();
                DataTable dt = new DataTable();
                SqlDataReader drr = conn.SelectSqlDataReader(sql);
                dt.Load(drr);
                conn.Close();
                return dt;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                //return null;
                throw;
            }
        }