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();
}
