/// <summary> ///Description : This function is used to invoke Execute Scalar Method ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : Object ///Comments : /// </summary> public object ExecuteScalar(bool blTransaction, CommandType cmdType, string cmdText, Parameters[] cmdParms, bool blDisposeCommand) { try { PrepareCommand(blTransaction, cmdType, cmdText, cmdParms); return oCommand.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { if (blDisposeCommand && null != oCommand) oCommand.Dispose(); } }
/// <summary> ///Description : This function is used to invoke Execute Scalar Method ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : Object ///Comments : /// </summary> public object ExecuteScalar(CommandType cmdType, string cmdText, Parameters[] cmdParms, bool blDisposeCommand) { try { EstablishFactoryConnection(); PrepareCommand(false, cmdType, cmdText, cmdParms); return oCommand.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { if (blDisposeCommand && null != oCommand) oCommand.Dispose(); CloseFactoryConnection(); } }
/// <summary> ///Description : This function is used to invoke Execute Scalar Method ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : Object ///Comments : Overloaded Method. /// </summary> public object ExecuteScalar(CommandType cmdType, string cmdText, Parameters[] cmdParms) { return ExecuteScalar(cmdType, cmdText, cmdParms, true); }
/// <summary> ///Description : This function is used to Execute the Command ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Transaction, Command Type, Command Text, Parameter Structure Array ///OutPut : Count of Records Affected ///Comments : /// </summary> public int ExecuteNonQuery(bool blTransaction, CommandType cmdType, string cmdText, Parameters[] cmdParms) { return ExecuteNonQuery(blTransaction, cmdType, cmdText, cmdParms, true); }
/// <summary> ///Description : This function is used to fetch data using Data Reader ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, Parameter AStructure Array ///OutPut : Data Reader ///Comments : /// </summary> public SQLiteDataReader ExecuteReader(CommandType cmdType, string cmdText, Parameters[] cmdParms) { // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { EstablishFactoryConnection(); PrepareCommand(false, cmdType, cmdText, cmdParms); return oCommand.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { CloseFactoryConnection(); throw ex; } finally { if (null != oCommand) oCommand.Dispose(); } }
///// <summary> ///// 获得参数最新更新时间(这个时间启动时加载到本地, ///// 一旦改变,要重新下载【工作时间表】和【部品超时表】到本地) ///// </summary> ///// <param name="deviceNo">设备编号</param> ///// <returns>返回NULL,获取失败,不更新参数</returns> //public DateTime? GetParamUpdateTime(string deviceNo) //{ // try // { // string CommandText = "select TrmnParamUpdTime from M_TerminalSetting where TerminalNo=@TerminalNo"; // Parameters[] p = new Parameters[]{ // new Parameters("@TerminalNo", deviceNo, DbType.String) // }; // object dt = this.SqlSrvHelper.ExecuteScalar(CommandType.Text, CommandText, p); // if (dt != null) // return Convert.ToDateTime(dt); // } // catch (Exception ex) // { // //log.Error("GetParamUpdateTime:" + ex.StackTrace); // LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, "GetParamUpdateTime:"+ ex.StackTrace); // } // return null; //} /// <summary> /// 更新工作时间到本地 /// </summary> /// <param name="dt">参数最新更新时间</param> public bool FectchWorkTime(DateTime dt, DataTable tblSysWork) { bool ok = false; try { string CommandText; if (tblSysWork != null && tblSysWork.Rows.Count > 0) { for (int i = 0; i < tblSysWork.Rows.Count; i++) { CommandText = "insert into WorkTimeList(ID,LatestTime,FromTime,ToTime,Flag) values(@ID,@LatestTime,@FromTime,@ToTime,0)"; Parameters[] p = new Parameters[]{ new Parameters( "@ID", System.Guid.NewGuid().ToString().Replace("-", ""), DbType.String), new Parameters( "@LatestTime", dt.Ticks, DbType.Int64), new Parameters( "@FromTime", Convert.ToDateTime(tblSysWork.Rows[i]["AMTimeStart"]).Ticks, DbType.Int64), new Parameters( "@ToTime", Convert.ToDateTime(tblSysWork.Rows[i]["AMTimeOver"]).Ticks, DbType.Int64) }; this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText, p); } CommandText = "update WorkTimeList set Flag=Flag+1 where 1=1"; this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText); ok = true; } } catch (Exception ex) { //log.Error("FectchWorkTime:" + ex.StackTrace); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, "FectchWorkTime:" + ex.StackTrace); } finally { try { string CommandText = "delete from WorkTimeList where Flag <> 1"; this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText); } catch (Exception) { } } return ok; }
/// <summary> ///Description : This function is used to Execute the Command ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, Parameter Structure Array ///OutPut : Count of Records Affected ///Comments : Overloaded method. /// </summary> public int ExecuteNonQuery(CommandType cmdType, string cmdText, Parameters[] cmdParms) { return ExecuteNonQuery(cmdType, cmdText, cmdParms, true); }
/// <summary> ///Description : This function is used to Prepare Command For Execution ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Transaction, Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : NA ///Comments : /// </summary> private void PrepareCommand(bool blTransaction, CommandType cmdType, string cmdText, Parameters[] cmdParms) { if (oConnection.State != ConnectionState.Open) { oConnection.ConnectionString = S_CONNECTION; oConnection.Open(); oConnectionState = ConnectionState.Open; } oCommand = new SQLiteCommand(this.oConnection); oCommand.Connection = oConnection; oCommand.CommandText = cmdText; oCommand.CommandType = cmdType; if (blTransaction) oCommand.Transaction = oTransaction; if (null != cmdParms) CreateDBParameters(cmdParms); }
/// <summary> ///Description : This function is used to fetch data using Data Adapter ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : Data Set ///Comments : /// </summary> public DataSet DataAdapter(CommandType cmdType, string cmdText, Parameters[] cmdParms) { // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work SQLiteDataAdapter dda = null; try { EstablishFactoryConnection(); dda = new SQLiteDataAdapter(); PrepareCommand(false, cmdType, cmdText, cmdParms); dda.SelectCommand = oCommand; DataSet ds = new DataSet(); dda.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { if(null != oCommand) oCommand.Dispose(); CloseFactoryConnection(); } }
/// <summary> /// 将已经上传的数据标记为已经上传(标记为2)! /// </summary> /// <param name="kdt">切换点</param> /// <param name="epc">卡号</param> /// <returns></returns> public bool UpdateFlagAfterUploaded(long kdt, string epc) { bool ok = false; try { string CommandText = "update [cards] set flag = @flag where kdt=@kdt and epc=@epc"; Parameters[] p = new Parameters[]{ new Parameters( "@kdt", kdt, DbType.Int64), new Parameters( "@epc", epc, DbType.String), new Parameters( "@flag", (int)CardStatus.Uploaded, DbType.Int32) //falg=2 标记已上传 }; ok = this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText, p) > 0; } catch (Exception e) { //log.ErrorFormat("UpdateFlagAfterUploaded:{0},delete kdt={1}", e.ToString(), kdt); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, string.Format ("UpdateFlagAfterUploaded:{0},delete kdt={1}", e.ToString(), kdt)); } return ok; }
/// <summary> /// 将工作列表加载到对象 /// </summary> /// <returns></returns> //public WorkTimeExtent GetWorkTimeExtent() //{ // WorkTimeExtent wte = null; // try // { // string CommandText = "select FromTime,ToTime from WorkTimeList where Flag=1"; // DataSet ds = this.SqliteHelper.ExecuteDataSet(CommandType.Text, CommandText); // if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // { // wte = new WorkTimeExtent(); // for (int i = 0; i < ds.Tables[0].Rows.Count; i++) // { // long k = Convert.ToInt64(ds.Tables[0].Rows[i]["FromTime"]); // long v = Convert.ToInt64(ds.Tables[0].Rows[i]["ToTime"]); // wte.Add(k, v); // } // } // } // catch (Exception ex) // { // //log.Error("GetWorkTimeExtent:" + ex.StackTrace); // LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, "GetWorkTimeExtent:" + ex.StackTrace); // } // return wte; //} /// <summary> /// 更新参数表 /// </summary> /// <param name="key">键(不操过200的字符串)</param> /// <param name="value">值(不操过200的字符串)</param> /// <returns></returns> public int UpdateParam(string key, object value) { try { string CommandText = "update [params] set pvalue = @pvalue where pkey = @pkey"; Parameters[] p = new Parameters[]{ new Parameters( "@pkey", key, DbType.String), new Parameters( "@pvalue", value, DbType.Object) }; return this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText, p); } catch (Exception e) { //log.ErrorFormat("UpdateParam:{0}", e.StackTrace); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, string.Format("UpdateParam:{0}", e.StackTrace)); } return -1; }
/// <summary> /// 获取指定名称的参数 /// </summary> /// <param name="key"></param> /// <returns></returns> public string SelectParam(string key) { try { string CommandText = "SELECT pvalue cnt FROM [params] WHERE pkey = @pkey"; Parameters[] p = new Parameters[]{ new Parameters( "@pkey", key, DbType.String) }; object pvalue = this.SqliteHelper.ExecuteScalar(CommandType.Text, CommandText, p); if (pvalue != null) return (string)pvalue; } catch (Exception e) { //log.Error("SelectParam:" + e.ToString()); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, "SelectParam:" + e.ToString()); } return null; }
/// <summary> /// 插入卡记录 /// </summary> /// <param name="card"></param> /// <returns></returns> public bool InsertCard(Card card) { bool ok = false; try { //String CommandText = "select kdt from [cards] where kdt=@kdt and epc=@epc "; //Parameters[] p = new Parameters[]{ //new Parameters("@kdt", card.Kdt, DbType.Int64), //new Parameters("@epc", card.EPC, DbType.String), //}; //Object obj = this.SqliteHelper.ExecuteScalar (CommandType.Text, CommandText, p); //if (obj != null && obj != DBNull.Value) return true; String CommandText = "insert or ignore into [cards] (kdt,epc ,dt,flag) values(@kdt,@epc,@dt,@flag)"; Parameters[] p = new Parameters[]{ new Parameters("@kdt", card.Kdt, DbType.Int64), new Parameters("@epc", card.EPC, DbType.String), new Parameters("@dt", card.Dt, DbType.Int64), new Parameters("@flag",card.Flag, DbType.Int32) //falg=0 新插入 }; this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText, p); ok = true; } catch (Exception e) { //log.ErrorFormat("InsertCard:{0},{1}", e.ToString(), card.ToString()); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error,string.Format ("InsertCard:{0},{1}", e.ToString(), card.ToString())); } return ok; }
/// <summary> /// 服务重启动时,加载当前时间对应的切换点已经存储的卡 /// </summary> /// <param name="kdt">切换点</param> /// <returns></returns> public List<Card> FetchCardsForRestart(long kdt) { List<Card> ret = new List<Card>(); try { DataSet ds = null; String CommandText = "select * from [cards] where kdt=@kdt"; Parameters[] p = new Parameters[]{ new Parameters( "@kdt", kdt, DbType.String) }; ds = this.SqliteHelper.ExecuteDataSet(CommandType.Text, CommandText, p); foreach (DataRow dr in ds.Tables[0].Rows) { long _kdt = (dr["kdt"] as long?).Value; long _dt = (dr["dt"] as long?).Value; ret.Add(new Card(_kdt, dr["epc"] as String, _dt)); } ds.Clear(); ds = null; } catch (Exception e) { //log.ErrorFormat("FetchCardsForRestart:{0}", e.ToString()); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, string.Format("FetchCardsForRestart:{0}", e.ToString())); } return ret; }
/// <summary> ///Description : This function is used to invoke Execute Scalar Method ///Author : AuthorWho ///Date : 2011-11-01 ///Input : Command Type, Command Text, 2-Dimensional Parameter Array ///OutPut : Object ///Comments : /// </summary> public object ExecuteScalar(bool blTransaction, CommandType cmdType, string cmdText, Parameters[] cmdParms) { return ExecuteScalar(blTransaction, cmdType, cmdText, cmdParms, true); }
public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, Parameters[] cmdParms) { return DataAdapter(cmdType, cmdText, cmdParms); }
/// <summary> ///Description : This function is used to Create Parameters for the Command For Execution ///Author : AuthorWho ///Date : 2011-11-01 ///Input : 2-Dimensional Parameter Array ///OutPut : NA ///Comments : /// </summary> private void CreateDBParameters(Parameters[] colParameters) { for (int i = 0; i < colParameters.Length; i++) { Parameters oParam = (Parameters)colParameters[i]; oParameter = oCommand.CreateParameter(); oParameter.ParameterName = oParam.ParamName; oParameter.Value = oParam.ParamValue; oParameter.Direction = oParam.ParamDirection; oCommand.Parameters.Add(oParameter); } }
/// <summary> /// 删除切换点(包括以前)已经上传的数据 /// </summary> /// <param name="kdt">切换点</param> /// <returns></returns> public bool DeleteCards(long kdt) { bool ok = false; try { string CommandText = "delete from [cards] where kdt<=@kdt and flag = @flag"; Parameters[] p = new Parameters[]{ new Parameters( "@kdt", kdt, DbType.Int64), new Parameters( "@flag", (int)CardStatus.Uploaded, DbType.Int32) //falg=2 已经上传的 }; this.SqliteHelper.ExecuteNonQuery(CommandType.Text, CommandText, p); ok = true; } catch (Exception e) { //log.ErrorFormat("{0},DeleteCards kdt={1}", e.ToString(), kdt); LogManager.WriteLog(Framework.Libs.Common.LogFile.Error, string.Format("{0},DeleteCards kdt={1}", e.ToString(), kdt)); } return ok; }