/// <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;
}