C# ClassConnectDB.SelectSqlDataReader方法代码示例

        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 int checkCredit(string year, string level, string term, string group)
        {
            try
            {
                /****************************
                 * function ตรวจสอบหน่วยกิตทั้งหมด
                 *
                 * **************************/
                int creditAll = 0;
                ClassConnectDB conn = new ClassConnectDB();
                string sql = @"SELECT sum (CONVERT(int,(SUBSTRING([StructSub_Credit], 1 , 1 )) ))as credit
                          FROM [WEBCSDB].[dbo].[StructSubject]
                          where StructSub_Code in (
                          SELECT TOP 1000 [StructSub_Code]
                          FROM  [DetailTech]
                          where [DetailTech_Level]='" + level + "' and [DetailTech_Class]='" + group + "' and [DetailTeach_Year]='" + year + "' and [DetailTech_Term]='" + term + "' )";

                SqlDataReader drr = conn.SelectSqlDataReader(sql);
                if (drr.Read())
                {
                    creditAll = Convert.ToInt32(drr["credit"].ToString());
                }
                return creditAll;
            }
            catch (Exception)
            {
                return 0;
            }
        }

 public static DataTable selectTestCode()
 {
     ClassConnectDB conn = new ClassConnectDB();
     string sql = "  SELECT *  FROM [WEBCSDB].[dbo].[Teacher] ";
     SqlDataReader drr = conn.SelectSqlDataReader(sql);
     DataTable dt = new DataTable();
     dt.Load(drr);
     return dt;
 }

        public static System.Data.DataTable manateYear()
        {
            string sql = "select YEAR(getdate())+542 as passYear,YEAR(getdate())+543 as curYear,YEAR(getdate())+544 as fuYear";

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

 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 System.Data.DataTable selectShowAllPlanTree(string yearCurriEdu)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = @"SELECT     ShowPlanEducate.ShowPlan_Year, ShowPlanEducate.ShowPlan_Semester, ShowPlanEducate.StructSub_Code, StructSubject.StructSub_NameTha
                                    FROM  ShowPlanEducate INNER JOIN
                                     StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
                                    where ShowPlanEducate.showplan_yearupdate='" + yearCurriEdu + "'";

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

                return dt;
            }
            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;
            }
        }

        public static DataTable swapSubject(string yearEdu, int year, int level, int term, string code, string namethai)
        {
            ClassConnectDB conn = new ClassConnectDB();

            string sql = "select distinct  StructSub_Code ,EduStdYearCurri from EducationOfStudents where EduStdYearEdu='" + yearEdu + "' ";

            for (int i = 1; i <= level; i++)
            {
                for (int j = 1; j <= term; j++)
                {
                    sql += "  and ( EduStdLevel=" + i + " and EduStdTerm = " + j + ") ";
                }
            }

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

            string sql2 = @"  select ROW_NUMBER() over (order by StructSub_Code ) as no,
                             StructSub_Code as subcode,  StructSub_NameTha as subThai ,  StructSubject.Curri_Id as curi ,  StructSub_Credit as credite
                            FROM         StructSubject INNER JOIN  Curriculum ON StructSubject.Curri_Id = Curriculum.Curri_Id  where 1=1 ";
            int m = 1;
            foreach (DataRow item in dt.Rows)
            {
                if (m == 1)
                {
                    sql2 += " and Curriculum.Curri_Year='" + item[1] + "' ";
                }

                sql2 += "   and StructSubject.StructSub_Code <> '" + item[0] + "' ";
            }

            if (code != "")
            {
                sql2 += " AND StructSubject.StructSub_Code = '" + code + "'";
            }
            if (namethai != "")
            {
                sql2 += " AND StructSubject.StructSub_NameTha LIKE '%" + namethai + "%'";
            }

            DataTable dtt = new DataTable();
            SqlDataReader drr = conn.SelectSqlDataReader(sql2);
            dtt.Load(drr);

            return dtt;
        }

 public static bool checkSubjectCurri(string code, string cur)
 {
     string selec = "  SELECT [Curri_Id] FROM [WEBCSDB].[dbo].[StructSubject]  where [StructSub_Code]='" + code + "'  and [Curri_Id]='" + cur + "' ";
     ClassConnectDB conn = new ClassConnectDB();
     SqlDataReader drr = conn.SelectSqlDataReader(selec);
     string curri = "";
     //if (drr.Read())
     //{
     //     curri = drr["Curri_Id"].ToString();
     //}
     if (drr.HasRows)
     {
         return true;
     }
     else return false;
 }

        public static DataTable selectDtShowGrideViewCase3(string year, string level, string term)
        {
            ClassConnectDB conn = new ClassConnectDB();
            string sql = @"SELECT     StructSubject.StructSub_Code, StructSubject.StructSub_NameTha, StructSubject.StructSub_Credit, StructSubject.Curri_Id,DetailTech_ID
                       FROM DetailTech INNER JOIN
                      StructSubject ON DetailTech.StructSub_Code = StructSubject.StructSub_Code
                      where  DetailTech.DetailTeach_Year='" + year + "' AND DetailTech.DetailTech_Level='" + level + "' AND   DetailTech.DetailTech_Term='" + term + "' AND DetailTech.DetailTeach_Status='u'  ";
            SqlDataReader drr = conn.SelectSqlDataReader(sql);
            DataTable dtt = new DataTable();
            dtt.Load(drr);

            DataTable dt = new DataTable();
            dt.Columns.Add("no");
            dt.Columns.Add("subcode");
            dt.Columns.Add("subThai");
            dt.Columns.Add("credite");
            dt.Columns.Add("curi");
            dt.Columns.Add("dchid");

            int i = 1;
            if (dtt.Rows.Count > 0)
            {
                foreach (DataRow item in dtt.Rows)
                {
                    dt.Rows.Add(i, item[0], item[1], item[2], item[3], item[4]);
                    i++;
                }
            }

            return dt;
        }

        public static DataTable selectSubjectWithPlan(string yearEdu, int level, int term)
        {
            ClassConnectDB conn = new ClassConnectDB();
            DataTable dt = new DataTable();
            dt.Columns.Add("no");
            dt.Columns.Add("subcode");
            dt.Columns.Add("subThai");
            dt.Columns.Add("credite");
            dt.Columns.Add("curi");

            string sql = @"
                      SELECT     StructSubject.StructSub_Code, StructSubject.StructSub_NameTha, StructSubject.StructSub_Credit, StructSubject.Curri_Id
                     FROM         ShowPlanEducate INNER JOIN
                                          StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
                     where  ShowPlanEducate.ShowPlan_YearUpdate='" + yearEdu + "' and    ShowPlanEducate.ShowPlan_Year='" + level + "' and  ShowPlanEducate.ShowPlan_Semester='" + term + "'";
            SqlDataReader drr = conn.SelectSqlDataReader(sql);
            DataTable dtt = new DataTable();
            dtt.Load(drr);
            int i = 1;
            foreach (DataRow item in dtt.Rows)
            {
                dt.Rows.Add(i, item[0], item[1], item[2], item[3]);
                i++;
            }
            return dt;
        }

        public static DataTable selectShowSubjectShowpopup(string year, string code, string nameThai)
        {
            ClassConnectDB conn = new ClassConnectDB();

            string sql = "Select *   FROM  [StructSubject] where 1=1  ";
            if (!string.IsNullOrEmpty(code))
            {
                sql += " and [StructSub_Code]='" + code + "'";
            }
            if (!string.IsNullOrEmpty(nameThai))
            {
                sql += " and [StructSub_NameTha] LIKE '%" + nameThai + "%'";
            }

            SqlDataReader drr = conn.SelectSqlDataReader(sql);
            DataTable dt = new DataTable();
            dt.Load(drr);
            conn.Close();
            return dt;
        }

        public static DataTable searchShowDetailTeach(string year, string level, string term)
        {
            ClassConnectDB conn = new ClassConnectDB();
            string sql = @"SELECT     (DetailTech.DetailTeach_Year) as DetailTeach_Year, (DetailTech.DetailTech_Level) as DetailTech_Level , (DetailTech.DetailTech_Term) as DetailTech_Term, ( StructSubject.StructSub_Code) as  StructSub_Code,( StructSubject.StructSub_NameTha) as StructSub_NameTha,
                      (StructSubject.StructSub_NameEn ) as StructSub_NameEn  , (StructSubject.StructSub_Credit ) as StructSub_Credit  FROM         DetailTech INNER JOIN
                      StructSubject ON DetailTech.StructSub_Code = StructSubject.StructSub_Code WHERE 1=1 ";
            if (!year.Equals("N"))
            {
                sql += "  AND [DetailTeach_Year]='" + year + "' ";
            }
            if (!level.Equals("0"))
            {
                sql += " AND  [DetailTech_Level]= '" + level + "'";
            }
            if (!term.Equals("0"))
            {
                sql += " AND [DetailTech_Term] ='" + term + "'";
            }

            SqlDataReader drr = conn.SelectSqlDataReader(sql);
            DataTable dt = new DataTable();
            dt.Load(drr);
            conn.Close();
            return dt;
        }