C# ClassConnectDB.InsertValue方法代码示例

        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 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 insertInstructionMedia(string classID, string namefile, string type, string path, string des, string filesize)
        {
            try
            {
                ClassConnectDB conn = new ClassConnectDB();
                string sql = @"INSERT INTO InstructionMedia
                      (InstMedia_Name, InstMedia_Type, InstMedia_Path, InstMedia_Description, Create_date, ClassRoom_ID,InstMedia_Size)
                        VALUES(@name,@type,@path,@des,getdate(),@classID,@filetype)";
                string addvalue = "@name,@type,@path,@des,@classID,@filetype";
                string value = namefile + "," + type + "," + path + "," + des + "," + classID + "," + filesize;

                bool insert = conn.InsertValue(sql, addvalue, value);
                if (insert)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool insertGrade(string codesubject, string codestd, string grade, string detailID)
        {
            try
            {

                ClassConnectDB conn = new ClassConnectDB();

                string selectCheck = @"SELECT    DetailTech.DetailTech_Term  FROM  EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
                                            where DetailTech.DetailTech_ID='" + detailID + "'";
                SqlDataReader drr = conn.SelectSqlDataReader(selectCheck);
                if (!drr.HasRows)
                {

                    string sqlSchoolRecord = @"  INSERT INTO SchoolRecord
                                          (SchoolRecord_Level, SchoolRecord_Ter, SchoolRecord_Year, Std_Campus_Code)
                                        SELECT  DetailTech.DetailTech_Level, DemtailTech.DetailTech_Term, DetailTech.DetailTeach_Year, EnrollIn.Std_Campus_Code
                                        FROM         EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
                                        where [email protected] ";
                    string Addvalue = "@deid";
                    string value = detailID;
                    conn.InsertValue(sqlSchoolRecord, Addvalue, value);
                }

                /*join enrool  ; if not Enrolln your can't check*/
                string sqlInserDetailTeach = @"INSERT INTO DetailResultStudent (StructSub_Code, DeStudent_Grade, SchoolRecord_ID)
                                            VALUES (@code,@grade,(
                                            SELECT     SchoolRecord_ID FROM SchoolRecord where  SchoolRecord_Year=
                                            (SELECT      DetailTech.DetailTeach_Year  FROM         EnrollIn INNER JOIN  DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID where [email protected] )
                                            AND  SchoolRecord_Term =
                                            (SELECT    DetailTech.DetailTech_Term  FROM  EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
                                            where [email protected] ))) ";
                string AddvalueDE = "@code,@grade,@detailID";
                string valueDe = codesubject + "," + grade + "," + detailID;
                conn.InsertValue(sqlInserDetailTeach, AddvalueDE, valueDe);


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

        public static bool deleteStudentInclass(string id, string dchID)
        {

            try
            {
                ClassConnectDB conn = new ClassConnectDB();

                string sqlcheckEnrollInCheckname = @"SELECT  CheckName.CheckName_ID  FROM  CheckName INNER JOIN
                      EnrollIn ON CheckName.Enroll_ID = EnrollIn.Enroll_ID WHERE(EnrollIn.DetailTech_ID = '" + dchID + "') AND (EnrollIn.Std_Campus_Code = '" + id + "')";

                SqlDataReader drrcheckNameinroll = conn.SelectSqlDataReader(sqlcheckEnrollInCheckname);
                if (drrcheckNameinroll.HasRows)
                {

                    string deleteCheckName = @"DELETE FROM CheckName WHERE CheckName_ID  in ( SELECT  CheckName.CheckName_ID  FROM  CheckName INNER JOIN
                      EnrollIn ON CheckName.Enroll_ID = EnrollIn.Enroll_ID WHERE(EnrollIn.DetailTech_ID = @deid) AND (EnrollIn.Std_Campus_Code = @id))";
                    string addvalueDelete = "@deid,@id";
                    string valuedelete = dchID + "," + id;
                    conn.DeleteValue(deleteCheckName, addvalueDelete, valuedelete);

                    string sqlinsert = "DELETE FROM EnrollIn WHERE [email protected] AND  [email protected]";
                    string addvalue = "@id,@dchID";
                    string value = id + "," + dchID;
                    conn.InsertValue(sqlinsert, addvalue, value);

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

        public static bool insertStudentInclass(string id, string dchID)
        {
            try
            {
                ClassConnectDB conn = new ClassConnectDB();

                string sql = "SELECT  DetailTech_ID FROM  EnrollIn WHERE Std_Campus_Code='" + id + "' AND  DetailTech_ID ='" + dchID + "'";
                SqlDataReader ddr = conn.SelectSqlDataReader(sql);//chechk detailTeach has Roww
                if (!ddr.HasRows)
                {

                    string sqlinsert = "INSERT INTO EnrollIn ( DetailTech_ID, Std_Campus_Code) VALUES (@dchId,@stdCode)";
                    string addvalue = "@dchId,@stdCode";
                    string value = dchID + "," + id;
                    conn.InsertValue(sqlinsert, addvalue, value);

                    string sqlSelectCheckname = @"SELECT max(CheckName.CheckName_Num)As numCheck  FROM EnrollIn INNER JOIN
                      CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID WHERE ([DetailTech_ID] = '" + dchID + "')";
                    string colum = "numCheck";
                    int countid = Convert.ToInt32(selectMaxIDValue(sqlSelectCheckname, colum));
                    for (int i = 1; i <= countid; i++)
                    {
                        string sqlinsertChechname = @"INSERT INTO CheckName (CheckName_Num,Create_date,Enroll_ID)VALUES(@num,(SELECT  distinct CheckName.Create_date
                                                            FROM EnrollIn INNER JOIN CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID
                                                             WHERE [email protected] AND [email protected]),( select max(Enroll_ID) FROM EnrollIN))";
                        string addvalueinsert = "@num,@deid";
                        string valueinsert = i.ToString() + "," + dchID;
                        conn.InsertValue(sqlinsertChechname, addvalueinsert, valueinsert);

                    }


                }
                conn.Close();
                return true;


            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool createDetailTeach(string year, string level, string group, string term, string showPlanId, string teacher, string checkCreateRoom, string createUser)
        {
            try
            {
                ClassConnectDB conn = new ClassConnectDB();
                string sql = @"INSERT INTO DetailTech
                      (DetailTeach_Year,DetailTech_Level, DetailTech_Class, DetailTech_Term, ShowPlan_Id, Tch_ID, ClassRoom_ID, Create_user, CreateDate)
                        VALUES (@year,@level,@group,@term,@planId,@tchID,@classID,@user,getdate())";
                string Addvalue = "@year,@level,@group,@term,@planId,@tchID,@classID,@user";
                string value = year + "," + level + "," + group + "," + term + "," + showPlanId + "," + teacher + "," + checkCreateRoom + "," + createUser;

                bool insert = conn.InsertValue(sql, Addvalue, value);
                if (insert)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static string checkCreateRoom(string showPlanId, string teacherid)
        {
            try
            {
                string classid = "";

                ClassConnectDB conn = new ClassConnectDB();
                string sql = @"SELECT ClassRoom.ClassRoom_ID AS classid
                                FROM  DetailTech INNER JOIN ClassRoom ON DetailTech.ClassRoom_ID = ClassRoom.ClassRoom_ID
                                WHERE DetailTech.ShowPlan_Id = '" + showPlanId + "' AND DetailTech.Tch_ID = '" + teacherid + "'";

                SqlDataReader drr = conn.SelectSqlDataReader(sql);
                if (drr.Read())
                {
                    classid = drr["classid"].ToString();
                }
                else
                {

                    string createRoom = @"INSERT INTO ClassRoom (ClassRoom_Name, Create_date) VALUES( (SELECT StructSubject.StructSub_NameTha
                                            FROM ShowPlanEducate INNER JOIN   StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code WHERE [email protected]),getdate())";
                    string Addvalue = "@planID";
                    string value = showPlanId;

                    bool insertRoom = conn.InsertValue(createRoom, Addvalue, value);

                    if (insertRoom)
                    {

                        string sqlselectClassMax = "SELECT MAX(ClassRoom_ID) AS idclass FROM ClassRoom";
                        SqlDataReader read = conn.SelectSqlDataReader(sqlselectClassMax);
                        if (read.Read())
                        {

                            classid = read["idclass"].ToString();
                        }
                    }

                }
                conn.Close();
                return classid;
            }
            catch (Exception)
            {
                return "";
            }
        }

        public static bool InsertStudentInclass(string code, string name, string group, string classID)
        {
            try
            {
                ClassConnectDB conn = new ClassConnectDB();
                string sql = @"  INSERT INTO EnrollIn  ( DetailTech_ID, Std_Campus_Code) VALUES     (
                                    ( SELECT     DetailTech_ID FROM         DetailTech   WHERE     (ClassRoom_ID = @classID) AND (DetailTech_Class = @group) ), 
                                    (SELECT     Std_Campus_Code  FROM  Student WHERE     (Std_Campus_Code = @codestd)))";

                string Addvalue = "@classID,@group,@codestd";
                string value = classID + "," + group + "," + code;
                bool insert = conn.InsertValue(sql, Addvalue, value);

                return insert;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool insertSchoolRecord(string year, string level, string term, string codesubject, string resultGrade, string code, string userid, string userType)
        {
            try
            {
                string sql = @"INSERT INTO SchoolRecord
                      (SchoolRecord_Year, SchoolRecord_Level, SchoolRecord_Term, StructSub_Code, SchoolRecord_Grade, Std_Campus_Code, Create_User, Create_Type, Create_Date, 
                      Update_User, Update_Type, Update_Date)
                    VALUES (@year,@level,@term,@subcode,@grade,@stdcode,@createuser,@createType,getDate(),@updUser,@updType,getDate())";
                string Addvalue = "@year,@level,@term,@subcode,@grade,@stdcode,@createuser,@createType,@updUser,@updType";
                string value = year + "," + level + "," + term + "," + codesubject + "," + resultGrade + "," + code + "," + userid + "," + userType + "," + userid + "," + userType;

                ClassConnectDB conn = new ClassConnectDB();

                bool inert = conn.InsertValue(sql, Addvalue, value);
                if (inert)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool updateInManageCurriculumPage(string updyear, string year)
        {
            try
            {
                string sql = "UPDATE Curriculum SET Curri_Year [email protected] Where [email protected]";
                string Addvalue = "@year,@updyear";
                string value = year+","+updyear;

                ClassConnectDB conn = new ClassConnectDB();
                conn.InsertValue(sql, Addvalue, value);
                return true;
            }
            catch (Exception)
            {
                return false;

            }
        }

      /// Manage Data in ManageCuluculum Page
      /// 
        public static bool insertYearInManageCurriculumPage(string year)
        {

            try
            {
                string sql = "INSERT INTO Curriculum (Curri_Year)VALUES(@year)";
                string Addvalue = "@year";
                string value =year;

                ClassConnectDB conn = new ClassConnectDB();
                conn.InsertValue(sql, Addvalue, value);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool getInsertGridAddPlanEducate(string yearMod, string year, string semes, string code)
        {

            try
            {
                string sql = @"INSERT INTO ShowPlanEducate (ShowPlan_YearUpdate,ShowPlan_Year, ShowPlan_Semester, StructSub_Code) 
                                VALUES(@yearMod,@year,@sem,@code)";
                string Addvalue = "@yearMod,@year,@sem,@code";
                string value = yearMod+","+ year + "," + semes+","+code;

                ClassConnectDB conn = new ClassConnectDB();
                conn.InsertValue(sql, Addvalue, value);
                return true;
            }
            catch (Exception)
            {
                return false;

            }
        }

        public static bool getInsertGridAddSubject(string code,string codepass)
        {
            try
            {
                string sql = "INSERT INTO FollowSubject(StructSub_Code, StructSub_CodePass)VALUES(@code,@codepass)";
                string Addvalue = "@code,@codepass";
                string value = code + "," + codepass;

                ClassConnectDB conn = new ClassConnectDB();
                conn.InsertValue(sql, Addvalue, value);
                return true;
            }
            catch (Exception)
            {
                return false;

            }
            
        }

        public static bool insertUserTeacherPageAdmin(Entity.Teacher teacher)
        {

            ClassConnectDB conn = new ClassConnectDB();

           // string code = teacher.Tch_Campus_code.ToString();
            string fname = teacher.Tch_FName.ToString();
            string lname = teacher.Tch_LName.ToString();
            string nickname = teacher.Tch_Nickname.ToString();
            string sex = teacher.Tch_Sex.ToString();
            //string birth = teacher.Tch_Birth.ToString();
            string address = teacher.Tch_Address.ToString();
            string tel = teacher.Tch_Tel.ToString();
            string type = teacher.Tch_Type.ToString();
            string email = teacher.Tch_email.ToString();
            string fnameEn = teacher.Tch_FNameEn.ToString();
            string lnameEn = teacher.Tch_LNameEn.ToString();
            string username = teacher.Tch_username.ToString();
            string password = teacher.Tch_password.ToString();
            string typeTea = teacher.Tch_TypeInSch.ToString();

            try
            {
                string sqlInsert = @"INSERT INTO Teacher
                        (Tch_FName, Tch_LName,Tch_FNameEn,Tch_LNameEn, Tch_Nickname, Tch_Sex, Tch_Address, Tch_Tel, Tch_Type, Tch_email,Tch_username, Tch_password,Tch_TypeInSch)
                        VALUES (@fname,@lname,@fnameEn,@lnameEn,@nickname,@sex,@address,@tel,@type,@email,@user,@pass,@typeSch)";
                string Addvalue = "@fname,@lname,@fnameEn,@lnameEn,@nickname,@sex,@address,@tel,@type,@email,@user,@pass,@typeSch";
                string value = fname + "," + lname + "," + fnameEn + "," + lnameEn + "," + nickname + "," + sex + "," + address + "," + tel + "," + type + "," + email + "," + username + "," + password+","+typeTea;

                conn.InsertValue(sqlInsert, Addvalue, value);
                conn.Close();

                return true;
            }
            catch (Exception)
            {
                return false;
            }

        }