public static List <dynamic> DataReaderToDynamic(System.Data.Common.DbDataReader reader, out double runTime) { var time = DateTime.Now; List <dynamic> list = new List <dynamic>(); var columns = new List <string>(); for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); } try { #region while while (reader.Read()) { object[] values = new object[columns.Count]; reader.GetValues(values); var d = getRow(columns, values); list.Add(d); } #endregion } catch (Exception ero) { reader.Close(); throw new CRLException("读取数据时发生错误:" + ero.Message); } reader.Close(); runTime = (DateTime.Now - time).TotalMilliseconds; return(list); }
public SignalProp getCurSigProp() { SignalProp prop = null; String sql = " SELECT s.lampId, s.signalName, s.type, s.state, c.state as specState, direction, dir_to, s.ip FROM cursignal c LEFT JOIN signal s ON c.lampId=s.lampId AND c.type=1 "; MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, myConn); System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); try { if (reader.Read()) { prop = new SignalProp(); prop.SignalID = reader.GetString(0).ToUpper(); prop.SignalName = DBStringToNormal(reader.GetString(1)); prop.Type = reader.IsDBNull(2)? (byte)0 : (byte)reader.GetInt16(2); prop.State = reader.GetString(3); prop.SpecState = reader.IsDBNull(4) ? (byte)0 : (byte)reader.GetInt16(4); prop.DirFrom = reader.IsDBNull(5) ? (byte)0 : (byte)reader.GetInt16(5); prop.DirTo = reader.IsDBNull(6) ? (byte)0 : (byte)reader.GetInt16(6); prop.Ip = reader.GetString(7); } reader.Close(); } catch (Exception e) { reader.Close(); Trace.TraceError("get cur signal prop " + e.StackTrace); } return(prop); }
public List <T> GetData <T>(out int outParame) where T : class, new() { var data = ObjectConvert.DataReaderToList <T>(reader, out runTime, false); reader.Close(); outParame = handler(); return(data); }
public List <T> GetDataTResult <T>(LambdaQuery.Mapping.QueryInfo <T> queryInfo, out int outParame) { var data = ObjectConvert.DataReaderToSpecifiedList <T>(reader, queryInfo); outParame = handler(); reader.Close(); reader.Dispose(); return(data); }
public async Task <object> SelectByCurriID() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}", FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(); curr_tname = item.ItemArray[data.Columns[FieldName.CURR_TNAME].Ordinal].ToString(); curr_ename = item.ItemArray[data.Columns[FieldName.CURR_ENAME].Ordinal].ToString(); degree_e_bf = item.ItemArray[data.Columns[FieldName.DEGREE_E_BF].Ordinal].ToString(); degree_e_full = item.ItemArray[data.Columns[FieldName.DEGREE_E_FULL].Ordinal].ToString(); degree_t_bf = item.ItemArray[data.Columns[FieldName.DEGREE_T_BF].Ordinal].ToString(); degree_t_full = item.ItemArray[data.Columns[FieldName.DEGREE_T_FULL].Ordinal].ToString(); level = Convert.ToChar(item.ItemArray[data.Columns[FieldName.LEVEL].Ordinal]); period = Convert.ToChar(item.ItemArray[data.Columns[FieldName.PERIOD].Ordinal]); year = item.ItemArray[data.Columns[FieldName.YEAR].Ordinal].ToString(); } data.Dispose(); } else { res.Close(); return("ไม่พบข้อมูลหลักสูตรที่ท่านเลือก"); } 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(null); }
public async Task <object> SelectByQuestionIdAsQuestionForm(int qid) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Questionare_question_answer> result = new List <Questionare_question_answer>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}", FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, qid); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Questionare_question_answer { detail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString(), questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]), questionare_question_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_QUESTION_ID].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string res.Close(); return("แบบสอบถามนี้ถูกลบแล้ว"); } 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 List <string> GetFldValues(string strSql, string strFldName) { List <string> lstReturn = null; System.Data.Common.DbDataReader reader = null; try { System.Data.SqlClient.SqlCommand mycmd = cnn.CreateCommand(); mycmd.CommandText = strSql; reader = mycmd.ExecuteReader(); if (reader == null) { bSuccess = false; strError = "内容不存在"; return(null); } if (!reader.HasRows) { bSuccess = true; strError = "内容为空"; reader.Close(); return(null); } lstReturn = new List <string>(); while (reader.Read()) { if (!Convert.IsDBNull(reader[strFldName])) { string strRT = reader[strFldName].ToString().TrimEnd(); lstReturn.Add(strRT); } } reader.Close(); bSuccess = true; } catch (System.Exception ex) { bSuccess = false; strError = ex.Message; if (reader != null && !reader.IsClosed) { reader.Close(); } return(null); } return(lstReturn); }
public DataTable GetBySqlQuery(string sqlQuery, bool dbNullParams, PagingProperties currentPaging, params SqlParameter[] _params) { if (dbNullParams) { _params = this.DbNullNonSetParams(sqlQuery, _params); } this.NullToDbNull(_params); DataTable dataTable = new DataTable(); System.Data.Common.DbDataReader reader = null; if (currentPaging != null) { System.Data.Common.DbCommand command = null; string commandText; if (currentPaging.HasPaging) { string countQuery = (sqlQuery.Contains("order") && (!sqlQuery.ToLower().Contains("top") && !sqlQuery.ToLower().Contains("offset"))) ? $"{sqlQuery} OFFSET 0 ROWS " : sqlQuery; //get row count command = this.GetDbCommand($"select count(*) from ({countQuery}) as returntable ", _params); currentPaging.RowsCount = command.ExecuteScalar().ToIntObj(); command.Dispose(); //get data with paging and sorting commandText = string.Format($@"{sqlQuery} { (!string.IsNullOrWhiteSpace(currentPaging.SortColumn) ? $"order by {currentPaging.SortColumn} {currentPaging.SortType}" : "")} OFFSET {((currentPaging.PageIndex - 1) * currentPaging.PageSize)} ROWS FETCH NEXT {currentPaging.PageSize} ROWS ONLY "); } else { //get data with paging and sorting commandText = string.Format($@"{sqlQuery} { (!string.IsNullOrWhiteSpace(currentPaging.SortColumn) ? $"order by {currentPaging.SortColumn} {currentPaging.SortType}" : "")}"); } command = this.GetDbCommand(commandText, _params); reader = command.ExecuteReader(); dataTable.Load(reader); reader.Close(); } else { System.Data.Common.DbCommand command = this.GetDbCommand(sqlQuery, _params); reader = this.GetDbCommand(sqlQuery, _params).ExecuteReader(); dataTable.Load(reader); reader.Close(); } return(dataTable); }
/// <summary> /// 平仓延迟多少秒 /// </summary> /// <param name="userid"></param> /// <returns></returns> public double GetDelayFlatOrder(string userid) { System.Data.Common.DbDataReader dbreader = null; double DelayFlatOrder = 0; try { string sql = string.Format(@"select a.DelayFlatOrder from Trade_UserGroups a,Trade_User_Group b where a.UserGroupId=b.UserGroupId and b.userid=@userid"); //使用参数化的sql语句,防止sql注入 dbreader = DbHelper.ExecuteReader(sql, new System.Data.Common.DbParameter[] { DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@userid", DbParameterType.String, userid, ParameterDirection.Input) }); if (dbreader.Read()) { DelayFlatOrder = Convert.ToDouble(dbreader["DelayFlatOrder"]); } } catch (Exception ex) { ComFunction.WriteErr(ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(DelayFlatOrder); }
public string[] getAllScales() { try { List <string> lstScales = new List <string>(); String query = string.Format("select {0} from {1}", COL_SCALES_TAG, TABLE_SCALES); //SqlCeCommand cmd = new SqlCeCommand(query, mConnection); setQuery(query); System.Data.Common.DbDataReader reader = mCmd.ExecuteReader(); while (reader.Read()) { string tag = reader[COL_SCALES_TAG].ToString(); lstScales.Add(tag); } reader.Close(); return(lstScales.ToArray()); } catch (Exception e) { UnhandledExceptionEventArgs args = new UnhandledExceptionEventArgs(e, false); CurrentDomain_UnhandledException(null, args); return(null); } }
/// <summary> /// 返回匿名类型 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TResult"></typeparam> /// <param name="reader"></param> /// <param name="resultSelector"></param> /// <param name="runTime"></param> /// <returns></returns> public static List <TResult> DataReaderToDynamic <T, TResult>(System.Data.Common.DbDataReader reader, Expression <Func <T, TResult> > resultSelector, out double runTime) where T : IModel, new() { var time = DateTime.Now; List <TResult> list = new List <TResult>(); var typeArry = TypeCache.GetProperties(typeof(T), true).Values; var columns = new Dictionary <string, int>(); for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i).ToLower(), i); } var reflection = ReflectionHelper.GetInfo <T>(); var actions = new List <CRL.ObjectConvert.ActionItem <T> >(); var first = true; //var objOrigin = new T(); while (reader.Read()) { object objInstance = reflection.CreateObjectInstance(); object[] values = new object[columns.Count]; reader.GetValues(values); var detailItem = ObjectConvert.DataReaderToObj <T>(columns, values, reflection, true, objInstance, typeArry, actions, first) as T; var result = resultSelector.Compile()(detailItem); list.Add(result); first = false; } reader.Close(); runTime = (DateTime.Now - time).TotalMilliseconds; return(list); }
public async Task <object> SelectWhereByCurriculumAcademic() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } oStudent_status_other result = new oStudent_status_other(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2} and {3} = {4}", FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.YEAR, ParameterName.YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.YEAR, year)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); //Set result to desired property result.grad_in_time = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.GRAD_IN_TIME].Ordinal]); result.grad_over_time = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.GRAD_OVER_TIME].Ordinal]); result.move_in = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.MOVE_IN].Ordinal]); result.quity1 = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.QUITY1].Ordinal]); result.quity2 = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.QUITY2].Ordinal]); result.quity3 = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.QUITY3].Ordinal]); result.quity4 = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.QUITY4].Ordinal]); result.curri_id = data.Rows[0].ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(); result.year = Convert.ToInt32(data.Rows[0].ItemArray[data.Columns[FieldName.YEAR].Ordinal]); data.Dispose(); } else //if no row return => set default student stat other result to all zeros { result.grad_in_time = 0; result.grad_over_time = 0; result.move_in = 0; result.quity1 = 0; result.quity2 = 0; result.quity3 = 0; result.quity4 = 0; result.curri_id = curri_id; result.year = year; } 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); }
//*************************************************************************************************** //Function: Query (無資料時直接關閉物件,並回傳False) //*************************************************************************************************** public static bool FunRsSql(string pSql, ref System.Data.Common.DbDataReader SqlRs) { try { SqlCommand SqlDbCmd = new SqlCommand(pSql); SqlDbCmd.Connection = SqlDBConn; SqlRs = SqlDbCmd.ExecuteReader(); if (!(SqlRs.HasRows)) //無資料則Close { //cls_Param.FunWriteLog( pSql + " (No Data)"); SqlRs.Close(); return(false); } else { //cls_Param.FunWriteLog(pSql); return(true); } } catch { return(false); } }
private async void ReadCommands() { try { MySqlCommand readCommands = new MySqlCommand("SELECT command,target,status FROM COMMANDS WHERE target = '" + ProgramOptions.LoggedInUser.Nick + "' AND status = 'start' LIMIT 1;", DataReaderMySQLConnection); System.Data.Common.DbDataReader reader = await readCommands.ExecuteReaderAsync(); while ((isReadingCommands = await reader.ReadAsync()) == true) { if (reader.GetString(2) == "start" && (reader.GetString(0) == "kicked" || reader.GetString(0) == "banned")) { MySqlCommand updateCommand = new MySqlCommand("START TRANSACTION; UPDATE COMMANDS SET status = 'finished' WHERE target = '" + ProgramOptions.LoggedInUser.Nick + "'; COMMIT; ", DataManipulationMySQLConnection); await updateCommand.ExecuteNonQueryAsync(); MessageBox.Show("You were " + reader.GetString(0) + " from the server!"); this.Close(); } } reader.Close(); } catch (Exception mysqlException) { if (mysqlException is MySqlException) { System.Diagnostics.Debug.WriteLine("Exception thrown in ReadCommandsAsync; Not harmful; " + mysqlException.ToString()); } } }
/// <summary> /// 判断是否转为经纪人 /// </summary> /// <param name="sql">查询SQL</param> /// <returns>bool</returns> public static bool IsCanEconomicMan(string sql) { System.Data.Common.DbDataReader dbreader = null; try { dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { double countOccMoney = System.DBNull.Value != dbreader["countOccMoney"] ? Convert.ToDouble(dbreader["countOccMoney"]) : 0; if (countOccMoney >= 0.1) { return(true); } } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(false); }
/// <summary> /// 获取交易用户信息 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static TradeUser GetTdUser(string sql) { System.Data.Common.DbDataReader dbreader = null; TradeUser tdUser = new TradeUser(); try { dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { tdUser.UserID = System.DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : string.Empty; break; } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(tdUser); }
/// <summary> /// 获取微交易用户UserID /// </summary> /// <param name="WUserId">WUserId</param> /// <returns>bool</returns> public static string GetWUserID(string WUserId) { System.Data.Common.DbDataReader dbreader = null; string id = ""; try { dbreader = DbHelper.ExecuteReader(string.Format(@"select UserID from Base_WUser where WUserId='{0}'", WUserId)); while (dbreader.Read()) { id = DBNull.Value != dbreader["UserID"] ? dbreader["UserID"].ToString() : ""; } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(id); }
private void button_dbQuery_Click(object sender, EventArgs e) { try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format("SELECT id,image FROM imageList WHERE id = {0} ;", textBox_imgId.Text); cmd.Connection = myConn; System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); long len = reader.GetBytes(1, 0, null, 0, 0); // ord=1 是image byte[] buffer = new byte[len]; len = reader.GetBytes(1, 0, buffer, 0, (int)len); MemoryStream ms = new MemoryStream(buffer); Image img = Image.FromStream(ms); pictureBox1.Image = img; } else { MessageBox.Show("没有查询到指定结果", "MySQL_Query"); } reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "MySQL_Query"); } }
/// <summary> /// 判断会员是否能承接头寸 /// </summary> /// <param name="orgid">组织机构ID</param> /// <param name="yingkui">盈亏</param> /// <param name="money">输出参数,如返回true则该值会员账户的余额,如返回false则该值为平台账户的余额</param> /// <returns>bool:true会员承接头寸;false平台承接头寸</returns> public static bool IsCanOrgTrade(string orgid, double yingkui, ref double money) { bool result = true;//默认为会员承担头寸 System.Data.Common.DbDataReader dbreader = null; try { string sql = string.Format(@"select IsTrade from Base_Org where OrgID='{0}'", orgid); dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { bool IsTrade = System.DBNull.Value != dbreader["IsTrade"] ? Convert.ToBoolean(dbreader["IsTrade"]) : false; if (IsTrade == false) //会员不承担头寸,则由平台承担 { result = false; //会员不承担头寸 //获取平台账户余额 sql = string.Format(@"select money from Trade_OrgFund where OrgID='system'"); dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0; } } else//会员承担头寸 { //获取会员账户余额 sql = string.Format(@"select money from Trade_OrgFund where OrgID='{0}'", orgid); dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0; if (money < yingkui) //如果此时会员账户余额不足以支付客户盈利,则由平台承接头寸 { result = false; //会员单位不承担头寸,由平台承接 sql = string.Format(@"select money from Trade_OrgFund where OrgID='system'"); dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { money = System.DBNull.Value != dbreader["money"] ? Convert.ToDouble(dbreader["money"]) : 0; } } } } } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(result); }
public string GetFldMaxValue(string strSql, string strFldName) { System.Data.Common.DbDataReader reader = null; try { System.Data.SqlClient.SqlCommand mycmd = cnn.CreateCommand(); mycmd.CommandText = strSql; reader = mycmd.ExecuteReader(); if (reader == null) { bSuccess = false; strError = "内容不存在"; return(""); } if (!reader.HasRows) { bSuccess = true; strError = "内容为空"; reader.Close(); return(""); } string strReturnMaxValue = ""; if (reader.Read()) { if (!Convert.IsDBNull(reader[strFldName])) { strReturnMaxValue = reader[strFldName].ToString(); } } reader.Close(); bSuccess = true; return(strReturnMaxValue); } catch (System.Exception ex) { bSuccess = false; strError = ex.Message; if (reader != null && !reader.IsClosed) { reader.Close(); } return("0"); } return("0"); }
public List <T> GetData <T>(out int outParame, ParameCollection fieldMapping = null) where T : class, new() { var data = ObjectConvert.DataReaderToList <T>(reader, false, fieldMapping); reader.Close(); outParame = handler(); return(data); }
public async Task <object> SelectOnlyNameAndId(string p_curri_id, int p_aca_year, string p_teacher_id, int p_indicator_num) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Primary_evidence_name_id_only> result = new List <Primary_evidence_name_id_only>(); d.iCommand.CommandText = string.Format("select p1.{0},{1}.{2} from " + "( select * from {3} where {4} = '{5}' and {6} = {7} and ({8} = '0' or {8} = '4') " + "and {0} IN " + "(select {14} from {1} where {9} = {10} and {11} = {12} and " + "({13} = '0' OR {13} = '{5}'))) as p1 INNER JOIN {1} on p1.{0} = {1}.{14}", Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM, FieldName.TABLE_NAME, FieldName.EVIDENCE_NAME, Primary_evidence_status.FieldName.TABLE_NAME, Primary_evidence_status.FieldName.CURRI_ID, p_curri_id, Primary_evidence_status.FieldName.TEACHER_ID, p_teacher_id, Primary_evidence_status.FieldName.STATUS, FieldName.ACA_YEAR, p_aca_year, FieldName.INDICATOR_NUM, p_indicator_num, FieldName.CURRI_ID, FieldName.PRIMARY_EVIDENCE_NUM ); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Primary_evidence_name_id_only { primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]), evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } 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> SelectExcludeUserType(int mode) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <oUser_type> result = new List <oUser_type>(); if (mode == 0) { d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ'", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME); } else if (mode == 1) { d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ' and {1} != 'กรรมการหลักสูตร'", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME); } else { d.iCommand.CommandText = string.Format("select * from {0} ", FieldName.TABLE_NAME); } try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oUser_type { user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.USER_TYPE_ID].Ordinal]), user_type = item.ItemArray[data.Columns[FieldName.USER_TYPE_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } 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> SelectWithBriefDetail() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Committee_with_detail> result = new List <Committee_with_detail>(); d.iCommand.CommandText = string.Format("select {0}.*,{1},{2},{3},{12} from {0},({4}) as {13} where {5} = '{6}' and {7} = {8} and {0}.{9} = {13}.{10} order by {11}", FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC, oTeacher.getSelectTeacherByJoinCommand(), FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year, FieldName.TEACHER_ID, Teacher.FieldName.TEACHER_ID, FieldName.DATE_PROMOTED, Teacher.FieldName.EMAIL, Teacher.FieldName.ALIAS_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Committee_with_detail { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), date_promoted = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_PROMOTED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]), t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } 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> Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <oCu_curriculum> result = new List <oCu_curriculum>(); d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oCu_curriculum { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), curr_tname = item.ItemArray[data.Columns[FieldName.CURR_TNAME].Ordinal].ToString(), curr_ename = item.ItemArray[data.Columns[FieldName.CURR_ENAME].Ordinal].ToString(), degree_e_bf = item.ItemArray[data.Columns[FieldName.DEGREE_E_BF].Ordinal].ToString(), degree_e_full = item.ItemArray[data.Columns[FieldName.DEGREE_E_FULL].Ordinal].ToString(), degree_t_bf = item.ItemArray[data.Columns[FieldName.DEGREE_T_BF].Ordinal].ToString(), degree_t_full = item.ItemArray[data.Columns[FieldName.DEGREE_T_FULL].Ordinal].ToString(), level = Convert.ToChar(item.ItemArray[data.Columns[FieldName.LEVEL].Ordinal]), period = Convert.ToChar(item.ItemArray[data.Columns[FieldName.PERIOD].Ordinal]), year = item.ItemArray[data.Columns[FieldName.YEAR].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } 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> Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Admin_with_creator> result = new List <Admin_with_creator>(); d.iCommand.CommandText = getselectcmd(); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Admin_with_creator { timestamp = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIMESTAMP].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], admin_creator_id = item.ItemArray[data.Columns[FieldName.ADMIN_CREATOR_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ADMIN_CREATOR_ID].Ordinal]) : 0, creator_name = item.ItemArray[data.Columns["c_t_prename"].Ordinal].ToString() + item.ItemArray[data.Columns["c_t_name"].Ordinal].ToString(), t_name = item.ItemArray[data.Columns[FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[FieldName.T_NAME].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[FieldName.FILE_NAME_PIC].Ordinal].ToString()), email = item.ItemArray[data.Columns[FieldName.EMAIL].Ordinal].ToString(), username = item.ItemArray[data.Columns[FieldName.USERNAME].Ordinal].ToString(), user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(), admin_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ADMIN_ID].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string } 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); }
private async void ReadOnlineUsers() { try { MySqlCommand readOnlineUsers = new MySqlCommand("SELECT ONLINE_USERS.nick, ONLINE_USERS.status, ACCOUNTS.privileges, ACCOUNTS.login FROM ONLINE_USERS LEFT JOIN ACCOUNTS ON ONLINE_USERS.Nick = ACCOUNTS.Nick WHERE ONLINE_USERS.status != 'Offline';", DataReaderMySQLConnection); System.Data.Common.DbDataReader reader = await readOnlineUsers.ExecuteReaderAsync(); List <Users> temporaryUsers = new List <Users> { }; while ((isReadingUsers = await reader.ReadAsync()) == true) { switch (reader.GetString(2)) { case "administrator": temporaryUsers.Add(new Administrator(reader.GetString(0), reader.GetString(1))); if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is Administrator)) { ProgramOptions.LoggedInUser = new Administrator(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3)); } break; case "moderator": temporaryUsers.Add(new Moderator(reader.GetString(0), reader.GetString(1))); if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is Moderator)) { ProgramOptions.LoggedInUser = new Moderator(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3)); } break; default: temporaryUsers.Add(new User(reader.GetString(0), reader.GetString(1))); if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is User)) { ProgramOptions.LoggedInUser = new User(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3)); } break; } } if (temporaryUsers != onlineUsers) { onlineUsers = temporaryUsers; } reader.Close(); loggedInUsersList.ItemsSource = onlineUsers; } catch (Exception mysqlException) { if (mysqlException is MySqlException) { System.Diagnostics.Debug.WriteLine("Exception thrown in ReadOnlineUsersAsync; Not harmful; " + mysqlException.ToString()); } } }
public static List <dynamic> DataReaderToDynamic(System.Data.Common.DbDataReader reader) { List <dynamic> list = new List <dynamic>(); while (reader.Read()) { var d = getDataRow(reader); list.Add(d); } reader.Close(); return(list); }
public async Task <object> Delete(List <Research_detail> list) { DBConnector d = new DBConnector(); List <string> file_to_delete = new List <string>(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } string deleteprecmd = string.Format("DELETE FROM {0} OUTPUT DELETED.{3} WHERE {1} = '{2}'", FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.FILE_NAME); string excludecond = "1=1 "; foreach (Research_detail item in list) { excludecond += string.Format("and {0} != {1} ", FieldName.RESEARCH_ID, item.research_id); } d.iCommand.CommandText = string.Format("{0} and ({1})", deleteprecmd, excludecond); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { file_to_delete.Add( item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString() ); } data.Dispose(); } else { //Reserved for return error string } 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(file_to_delete); }
private void lsMemeber_SelectedIndexChanged(object sender, EventArgs e) { if (lsvMemeber.SelectedItems.Count < 1) { return; } // //填充所选操作员拥有的岗位 // lsbHasRols.Items.Clear(); if (System.Threading.Thread.CurrentThread.CurrentCulture.Name == "zh-CN") { _sql = "select a.ROLE_ID,b.NAME from DMIS_SYS_MEMBER_ROLE a,DMIS_SYS_ROLE b where a.ROLE_ID=b.ID and a.MEMBER_ID=" + lsvMemeber.SelectedItems[0].Text; } else { _sql = "select a.ROLE_ID,b.OTHER_LANGUAGE_DESCR from DMIS_SYS_MEMBER_ROLE a,DMIS_SYS_ROLE b where a.ROLE_ID=b.ID and a.MEMBER_ID=" + lsvMemeber.SelectedItems[0].Text; } System.Data.Common.DbDataReader dr = DBOpt.dbHelper.GetDataReader(_sql); while (dr.Read()) { lsbHasRols.Items.Add(dr[1].ToString() + "(" + dr[0].ToString() + ")"); } dr.Close(); // //填充所选操作员没有的岗位 // lsbOtherRoles.Items.Clear(); _sql = "select * from DMIS_SYS_ROLE where ID not in (select ROLE_ID from DMIS_SYS_MEMBER_ROLE where MEMBER_ID=" + lsvMemeber.SelectedItems[0].Text + ")"; dr = DBOpt.dbHelper.GetDataReader(_sql); while (dr.Read()) { lsbOtherRoles.Items.Add(dr["OTHER_LANGUAGE_DESCR"].ToString() + "(" + dr["ID"].ToString() + ")"); } dr.Close(); }