private string getSelectByCurriculumAcademicCommand() { string selectlablist = string.Format("select * from {0} " + "where {1} = '{2}' and {3} = {4} " + "order by {5} ", FieldName.TABLE_NAME, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year, FieldName.NAME ); string selectofficer = string.Format("select {0}.*," + "{1} = {2} " + ", {3} from {0}, {4} " + "where " + "exists(select * from {5} " + "where {0}.{6} = {7} " + "and {8} = '{9}' and {10} = {11}) " + "and {12} = {13} ", Lab_officer.FieldName.TABLE_NAME, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.TABLE_NAME, FieldName.TABLE_NAME, Lab_officer.FieldName.LAB_NUM, FieldName.LAB_NUM, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year, Lab_officer.FieldName.OFFICER, User_list.FieldName.USER_ID ); return(string.Format("BEGIN {0} {1} END", selectlablist, selectofficer)); }
private string getSelectByCurriculumAcademicCommand() { return(string.Format("SELECT {0}.*,{1},{2}, " + "{3} = {4}" + ", {5} from {0}, {6}, {7}, {8} " + "where {9} = '{10}' and {11} = {12} " + "and {0}.{13} = {6}.{14} " + "and {15} = {16} " + "and {17}.{18} = {1} order by {19} desc ", FieldName.TABLE_NAME, Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID, User_type.FieldName.USER_TYPE_NAME, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, Questionare_privilege.FieldName.TABLE_NAME, /*6*/ User_type.FieldName.TABLE_NAME /*7*/, User_list.FieldName.TABLE_NAME /*8*/, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year, FieldName.QUESTIONARE_SET_ID, Questionare_privilege.FieldName.QUESTIONARE_SET_ID, FieldName.PERSONNEL_ID, User_list.FieldName.USER_ID, User_type.FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_ID, FieldName.DATE )); }
public async Task <object> selectWithFullDetail(string curri_id_data) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Personnel_educational> result = new List <Personnel_educational>(); string selpersonnel = string.Format("select {0}, {1}.{2}," + "{3} = {4} " + ", {5}, {6}, {7}, {8}, {9} " + "from {1}, {10} " + "where {1}.{2} in(1,2) " + "and exists (select * from {11} " + "where {1}.{0} = {12} and {13} = '{14}') " + "and {1}.{2} = {10}.{15} ", User_list.FieldName.USER_ID, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_list.FieldName.EMAIL, User_list.FieldName.TEL, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME, User_curriculum.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, curri_id_data, User_type.FieldName.USER_TYPE_ID ); string seleducation = string.Format("select * from {0} " + "where " + "exists (select * from {1} " + "where {2} = {3} and {4} = '{5}') " + "and exists (select * from {6} " + "where {2} = {7} and {8} in(1, 2)) ", Educational_teacher_staff.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME, Educational_teacher_staff.FieldName.PERSONNEL_ID, User_curriculum.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, curri_id_data, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID ); d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", selpersonnel, seleducation); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { if (data.Columns.Contains(Educational_teacher_staff.FieldName.EDUCATION_ID)) {//Educational table data int personnelid = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PERSONNEL_ID].Ordinal]); result.First(p => p.user_id == personnelid).history.Add(new Educational_teacher_staff { college = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(), degree = Convert.ToChar(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal]), grad_year = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]), pre_major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString(), major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(), personnel_id = personnelid }); } else { result.Add(new Personnel_educational { //Main user table data user_id = Convert.ToInt32(item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal]), email = item.ItemArray[data.Columns[User_list.FieldName.EMAIL].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()), tel = item.ItemArray[data.Columns[User_list.FieldName.TEL].Ordinal].ToString(), user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(), fullname = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> SelectByCurriculumAndTitle() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Extra_privilege_individual_with_privilege_choices result = new Extra_privilege_individual_with_privilege_choices(); string temp5tablename = "#temp5"; string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME, Title.FieldName.TITLE_CODE, title_code); string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME, Title_privilege.FieldName.TITLE_CODE, title_code); string createtabletemp5 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "[{2}] {8} NOT NULL," + "[{3}] INT NOT NULL," + "[{4}] INT NOT NULL," + "[{5}] VARCHAR(16) NULL," + "[{6}] VARCHAR(60) NULL," + "[{7}] {9} NULL," + "PRIMARY KEY([row_num])" + ") " + "alter table {0} " + "alter column [{2}] {8} collate database_default " + "alter table {0} " + "alter column [{5}] VARCHAR(16) collate database_default " + "alter table {0} " + "alter column [{6}] VARCHAR(60) collate database_default " + "alter table {0} " + "alter column [{7}] {9} collate database_default ", temp5tablename, FieldName.USER_ID, FieldName.CURRI_ID, FieldName.TITLE_CODE, FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE ); string insertintotemp5_maindata = string.Format("insert into {12} " + "select {0}.*," + "{1} = {2} " + ", {3}, {4} " + "from {0}, {5} " + "where {6} = {7} and {8} = '{9}' " + "and {0}.{10} = {5}.{11} ", FieldName.TABLE_NAME, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_list.FieldName.TABLE_NAME, FieldName.TITLE_CODE, title_code, FieldName.CURRI_ID, curri_id, FieldName.USER_ID, User_list.FieldName.USER_ID, temp5tablename); string insertintotemp5_setextrapriv = string.Format("insert into {19} " + "select {0}.{1},{2}.{3},{4},{5}," + "{6} = {7} " + ", {8}, {9} " + "from {0}, {10}, {2} " + "where {4} = {11} and {2}.{3} = '{12}' " + "and {0}.{1} = {10}.{13} " + "and {2}.{14} = {0}.{15} " + "and {2}.{3} = {10}.{16} " + "and not exists(" + "select * from {19} where " + "{0}.{1} = {17} " + "and {2}.{4} = {18}" + ") ", User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, Extra_privilege_by_type.FieldName.TABLE_NAME, Extra_privilege_by_type.FieldName.CURRI_ID, Extra_privilege_by_type.FieldName.TITLE_CODE, Extra_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_curriculum.FieldName.TABLE_NAME, title_code, curri_id, User_curriculum.FieldName.USER_ID, Extra_privilege_by_type.FieldName.USER_TYPE_ID, User_list.FieldName.USER_TYPE_ID, User_curriculum.FieldName.CURRI_ID, FieldName.USER_ID, FieldName.TITLE_CODE, temp5tablename ); string insertintotemp5_setdefpriv = string.Format("insert into {18} " + "select {0}.{1},{2},{3},{4}," + "{5} = {6} " + ", {7}, {8} " + "from {0}, {9}, {10} " + "where {3} = {11} and {2} = '{12}' " + "and {0}.{1} = {9}.{13} " + "and {0}.{14} = {10}.{15} " + "and not exists(" + "select * from {18} where " + "{0}.{1} = {16} " + "and {10}.{3} = {17}" + ") ", User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, Default_privilege_by_type.FieldName.TITLE_CODE, Default_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_curriculum.FieldName.TABLE_NAME, Default_privilege_by_type.FieldName.TABLE_NAME, title_code, curri_id, User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID, Default_privilege_by_type.FieldName.USER_TYPE_ID, FieldName.USER_ID, FieldName.TITLE_CODE, temp5tablename ); string selectfromtemp5 = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.USER_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} END", createtabletemp5, insertintotemp5_maindata, insertintotemp5_setextrapriv, insertintotemp5_setdefpriv, selectTitle, selectPrivilegeChoices, selectfromtemp5); try { //Set result's curri_id result.curri_id = curri_id; System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable tabledata = new DataTable(); tabledata.Load(res); foreach (DataRow item in tabledata.Rows) { if (tabledata.Columns.Contains(Title.FieldName.NAME)) { //Set title name from title table result result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]); result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString(); } else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE)) { //Set privilege choice for target title result.choices.Add(new Privilege_choice { title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]), privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString() }); } else { //Read main privilege data int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); result.privilege_list.Add(new User_privilege { user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_ID].Ordinal]), t_name = item.ItemArray[tabledata.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[tabledata.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()), privilege = new Privilege_choice { title_privilege_code = title_priv_code, //Find privilege caption from choices array privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege } }); } } tabledata.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }