C# QueryHelper.Select方法代码示例

        /// <summary>
        /// 取得科目中英文對照表
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string,string> SelectAll()
        {
            //"科目中英文對照表"
            //<Content>
            //   <Subject Chinese="國文" English="Chinese"/>
            //   <Subject Chinese="英文" English="English"/>
            //</Content>

            QueryHelper helper = new QueryHelper();

            Dictionary<string, string> result = new Dictionary<string, string>();

            DataTable table = helper.Select("select * from list where name='科目中英文對照表'");

            if (table.Rows.Count == 1)
            {
                string Content = table.Rows[0].Field<string>("content");

                StringReader reader = new StringReader(Content);

                XElement Element = XElement.Load(reader);

                foreach (XElement elmSubject in Element.Elements("Subject"))
                {
                    string Chinese = elmSubject.AttributeText("Chinese");
                    string English = elmSubject.AttributeText("English");

                    if (!result.ContainsKey(Chinese))
                        result.Add(Chinese, English);
                }
            }

            return result;
       }

    private void btnSave_Click(object sender, EventArgs e)
    {
        string classname = txtName.Text.Trim();

            if (classname == String.Empty)
                return;

            QueryHelper queryHelper = new QueryHelper();
            string strQuery = String.Format(@"select class_name from class where class_name='{0}'", classname);

            DataTable dataTable = queryHelper.Select(strQuery);

            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                K12.Data.ClassRecord addRecord = new K12.Data.ClassRecord();

                addRecord.Name = classname;

                string addRecord_ID = K12.Data.Class.Insert(addRecord);
                //Class.Instance.SyncDataBackground(ClassID);   同步處理
                //  log 待處理
                //PermRecLogProcess prlp = new PermRecLogProcess();
                //prlp.SaveLog("學籍.班級", "新增班級", "新增班級,名稱:" + txtName.Text);
                if (chkInputData.Checked)
                    K12.Presentation.NLDPanels.Class.PopupDetailPane(addRecord_ID);
                //Class.Instance.SyncDataBackground(ClassID);   同步處理
            }
            else
            {
                MessageBox.Show("班級名稱重複");
                return;
            }
            this.Close();
    }

 /// <summary>
 /// 檢查一般學生學號是否重複
 /// </summary>
 /// <param name="number"></param>
 /// <returns></returns>
 public static bool CheckStudentNumberSame1(string number)
 {
     bool retVal = false;
     QueryHelper qh = new QueryHelper();
     string strSQL = "select id from student where status=1 and student_number='"+number+"'";
     DataTable dt= qh.Select(strSQL);
     if (dt.Rows.Count > 0)
         retVal = true;
     return retVal;
 }

        /// <summary>
        /// 匯入前準備
        /// </summary>
        /// <param name="Option"></param>
        public override void Prepare(ImportOption Option)
        {
            mHelper = new AccessHelper();
            mOption = Option;
            mCourseNameIDs = new Dictionary<string, string>();
            mTask = Task.Factory.StartNew
            (() =>
            {
                QueryHelper Helper = new QueryHelper();

                DataTable Table = Helper.Select("select uid,course_name,school_year,semester,month from $shinmin.retake.course");

                foreach (DataRow Row in Table.Rows)
                {
                    string CourseID = Row.Field<string>("uid");
                    string CourseName = Row.Field<string>("course_name");
                    string SchoolYear = Row.Field<string>("school_year");
                    string Semester = Row.Field<string>("semester");
                    string Month = Row.Field<string>("month");
                    string CourseKey = CourseName + "," + SchoolYear + "," + Semester + "," + Month;

                    if (!mCourseNameIDs.ContainsKey(CourseKey))
                        mCourseNameIDs.Add(CourseKey, CourseID);
                }

                Table = Helper.Select("select id,teacher_name,nickname from teacher");

                foreach (DataRow Row in Table.Rows)
                {
                    string TeacherID = Row.Field<string>("id");
                    string TeacherName = Row.Field<string>("teacher_name");
                    string TeacherNickname = Row.Field<string>("nickname");
                    string TeacherKey = string.IsNullOrWhiteSpace(TeacherNickname) ? TeacherName : TeacherName + "(" + TeacherNickname + ")";

                    if (!mTeacherNameIDs.ContainsKey(TeacherKey))
                        mTeacherNameIDs.Add(TeacherKey, TeacherID);
                }
            }
            );
        }

        /// <summary>
        /// 取得學生曾經參與過的社團的學年度學期
        /// </summary>
        /// <param name="StudentIdList"></param>
        /// <returns></returns>
        public static Dictionary<string, ValueObj.ClubsVO> GetClubRecordByStudentIdList(List<string> StudentIdList)
        {
            Dictionary<string, ValueObj.ClubsVO> result = new Dictionary<string,ValueObj.ClubsVO>();

            // 社團參與紀錄的取得, 由k12.scjoin.universal改成k12.resultscore.universal, 2013/12/16
            //string tableName1 = "k12.clubrecord.universal";
            //string tableName2 = "k12.scjoin.universal";

            //if (IsUDTExists(tableName1) == false || IsUDTExists(tableName2) == false)
            //{
            //    if (Global.IsDebug) Console.WriteLine("[GetClubRecordByStudentIdList] UDT for Club not found!!");
            //    return result;
            //}

            //StringBuilder sb = new StringBuilder();
            //sb.Append("select t1.school_year, t1.semester, t2.ref_student_id");
            //sb.Append(" from $" + tableName1 + " t1, $" + tableName2 + " t2");
            //sb.Append(" where t2.ref_club_id::int = t1.uid");
            //sb.Append(" and t2.ref_student_id in ('" + string.Join("','", StudentIdList.ToArray()) + "')");
            //sb.Append(" and t1.school_year is not NULL");
            //sb.Append(" and t1.semester is not NULL");

            string tableName = "k12.resultscore.universal";
            if (IsUDTExists(tableName) == false)
            {
                if (Global.IsDebug) Console.WriteLine("[GetClubRecordByStudentIdList] UDT(" + tableName + ") for Club not found!!");
                return result;
            }

            StringBuilder sb = new StringBuilder();
            sb.Append("select t.school_year, t.semester, t.ref_student_id");
            sb.Append(" from $" + tableName + " t");
            sb.Append(" where t.ref_student_id in ('" + string.Join("','", StudentIdList.ToArray()) + "')");
            sb.Append(" and t.school_year is not NULL");
            sb.Append(" and t.semester is not NULL");

            if (Global.IsDebug) Console.WriteLine("[GetClubRecordByStudentIdList] sql: [" + sb.ToString() + "]");
            
            QueryHelper qh = new QueryHelper();
            DataTable dt = qh.Select(sb.ToString());

            foreach (DataRow row in dt.Rows)
            {
                string studentId = ("" + row["ref_student_id"]).Trim();
                if(!result.ContainsKey(studentId))
                    result.Add(studentId, new ValueObj.ClubsVO());

                result[studentId].AddClub(row);
            }

            return result;
        }

 /// <summary>
 /// 透過班級ID取得班級內學生狀態一般的學生ID
 /// </summary>
 /// <param name="ClassIDList"></param>
 /// <returns></returns>
 public static List<string> GetStudentIDList1ByClassID(List<string> ClassIDList)
 {
     List<string> retVal = new List<string>();
     if (ClassIDList.Count > 0)
     {
         QueryHelper qh = new QueryHelper();
         string strSQL = "select student.id from student inner join class on student.ref_class_id=class.id where student.status in(1) and class.id in(" + string.Join(",", ClassIDList.ToArray()) + ") order by student_number;";
         DataTable dt = qh.Select(strSQL);
         foreach (DataRow dr in dt.Rows)
             retVal.Add(dr[0].ToString());
     }
     return retVal;
 }

 /// <summary>
 /// 班座排序
 /// </summary>
 /// <param name="StudIDList"></param>
 /// <returns></returns>
 public static List<string> GetStudentIDListByStudentID(List<string> StudIDList)
 {
     List<string> retVal = new List<string>();
     if (StudIDList.Count > 0)
     {
         QueryHelper qh = new QueryHelper();
         string strSQL = "select student.id from student left join class on student.ref_class_id=class.id where student.id in(" + string.Join(",", StudIDList.ToArray()) + ") order by class.class_name,student.seat_no;";
         DataTable dt = qh.Select(strSQL);
         foreach (DataRow dr in dt.Rows)
             retVal.Add(dr[0].ToString());
     }
     return retVal;
 }

 /// <summary>
 /// 取得所有學生異動(學號_狀態代號,學生編號)
 /// </summary>
 /// <param name="開始異動代碼"></param>
 /// <param name="結束異動代碼"></param>
 /// <returns></returns>
 public static Dictionary<string, string> GetHasStudentUpdateRecord01Dict(int beginCode,int endCode)
 {
     Dictionary<string, string> retVal = new Dictionary<string, string>();
     QueryHelper qh = new QueryHelper();
     string strSQL="select student.student_number,student.status,student.id from student inner join update_record on student.id=update_record.ref_student_id where to_number(update_code,'999') between "+beginCode+" and "+endCode+";";
     DataTable dt = qh.Select(strSQL);
     foreach (DataRow dr in dt.Rows)
     {
         string key = dr[0].ToString() + "_" + dr[1].ToString();
         if (!retVal.ContainsKey(key))
             retVal.Add(key, dr[2].ToString());
     }
     return retVal;
 }

        private static string SelectIDCondition(string TableName, string Condition)
        {
            QueryHelper helper = new QueryHelper();

            DataTable Table = helper.Select("select uid from " + TableName + " where " + Condition);

            List<string> IDs = new List<string>();

            foreach (DataRow Row in Table.Rows)
                IDs.Add(Row.Field<string>("uid"));

            string strUDTCondition = string.Join(",", IDs.Select(x => "'" + x + "'"));

            return string.IsNullOrWhiteSpace(strUDTCondition) ? string.Empty : "uid in (" + strUDTCondition + ")";
        }

        private void AddNewForm_Load(object sender, EventArgs e)
        {
            try
            {
                QueryHelper qh = new QueryHelper();
                DataTable dt = qh.Select("select \"group\" from $school group by \"group\" order by \"group\"");

                foreach (DataRow row in dt.Rows)
                {
                    string gname = row["group"] + "";
                    cboGroup.Items.Add(gname);
                }
            }
            catch { }
        }

        /// <summary>
        /// 取得所有學生學號 key:StudentNumber ; value:StudentID
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> GetAllStudenNumberDict()
        {
            Dictionary<string, string> retVal = new Dictionary<string, string>();
            QueryHelper qh = new QueryHelper();
            string strSQL = "select student.student_number,student.id from student where student.status='1';";
            DataTable dt = qh.Select(strSQL);
            foreach (DataRow dr in dt.Rows)
            {
                string key = dr[0].ToString();
                string value = dr[1].ToString();
                if (!retVal.ContainsKey(key))
                    retVal.Add(key, value);
            }

            return retVal;
        }

        /// <summary>
        /// 取得所有學生學號_狀態對應 StudentNumber_Status,id
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, int> GetAllStudenNumberStatusDict()
        {
            Dictionary<string, int> retVal = new Dictionary<string, int>();
            QueryHelper qh = new QueryHelper();
            string strSQL = "select student.student_number,student.status,student.id from student;";
            DataTable dt = qh.Select(strSQL);
            foreach (DataRow dr in dt.Rows)
            {
                string key = dr[0].ToString() + "_" + dr[1].ToString();
                int id = int.Parse(dr[2].ToString());
                if (!retVal.ContainsKey(key))
                    retVal.Add(key, id);
            }

            return retVal;
        }

        public static List<string> ResolveField(this string SQL)
        {
            List<string> fields = new List<string>();
            try
            {
                QueryHelper Access = new QueryHelper();

                DataTable dataTable = Access.Select(SQL + " limit 0;");
                foreach (DataColumn column in dataTable.Columns)
                {
                    if (!fields.Contains(column.ColumnName))
                        fields.Add(column.ColumnName);
                }
            }
            catch { }

            return fields;
        }

        /// <summary>
        /// 檢查UDT是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool IsUDTExists(string tableName)
        {
            bool result = false;
            StringBuilder sb = new StringBuilder();
            sb.Append("select count(1) from _udt_table where name = '" + tableName + "'");

            if (Global.IsDebug) Console.WriteLine("[IsUDTExists] sql: [" + sb.ToString() + "]");

            QueryHelper qh = new QueryHelper();
            DataTable dt = qh.Select(sb.ToString());

            int count = int.Parse("" + dt.Rows[0]["count"]);

            if (count > 0)
                result = true;

            return result;
        }

        /// <summary>
        /// 依學號取得學生ID StudentNumber,id
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> GetStudenIdDictByStudentNumber(List<string> StudNumList)
        {
            Dictionary<string, string> retVal = new Dictionary<string, string>();
            QueryHelper qh = new QueryHelper();
            StringBuilder sbQry = new StringBuilder();
            sbQry.Append("'"); sbQry.Append(string.Join("','", StudNumList.ToArray())); sbQry.Append("'");
            string strSQL = "select student.student_number, student.id from student where student_number in(" + sbQry.ToString() + ") and student.status='1';";
            DataTable dt = qh.Select(strSQL);
            
            foreach (DataRow dr in dt.Rows)
            {
                string key = dr[0].ToString();
                string value = dr[1].ToString();
                if (!retVal.ContainsKey(key))
                    retVal.Add(key, value);
            }

            return retVal;
        }