public CommandResult Insert(TInfo info)
{
CommandResult result;
try
{
LDB_DataContext ldb = LDB_DataContextFactory.CreateLDB(ConnectStr);
InsertingItem(info, ldb);
ldb.SubmitChanges();
result = new CommandResult(ResultCode.Successful, successMsg);
}
catch (System.Data.SqlClient.SqlException ex)
{
if (ex.Number == 2627)
{
result = new CommandResult(ResultCode.Fail, "不能插入重复ID的数据!");
}
else
{
result = new CommandResult(ResultCode.Fail, "数据在写入数据库时发生错误!");
}
ExceptionPolicy.HandleException(ex, this.GetType().Name + "." + "Insert()");
}
catch (Exception ex)
{
result = new CommandResult(ResultCode.Fail, "数据在写入数据库时发生错误!");
ExceptionPolicy.HandleException(ex, this.GetType().Name + "." + "Insert()");
}
return(result);
}
public QueryResultList <TInfo> GetItems(SearchCondition search)
{
QueryResultList <TInfo> result;
try
{
LDB_DataContext ldb = LDB_DataContextFactory.CreateLDB(ConnectStr);
List <TInfo> infoes;
if (search != null)
{
infoes = GetingItems(ldb, search);
}
else
{
infoes = GetingAllItems(ldb);
}
result = new QueryResultList <TInfo>(ResultCode.Successful, successMsg, infoes);
}
catch (Exception ex)
{
result = new QueryResultList <TInfo>(ResultCode.Fail, "从数据库获取数据时发生错误!", new List <TInfo>());
ExceptionPolicy.HandleException(ex, this.GetType().Name + "." + "GetItems()");
}
return(result);
}
protected override void InsertingItem(LDB_SysparameterInfo info, LDB_DataContext parking)
{
string cmd = @"delete from Sysparameter where Parameter=@p0 ; " +
"insert into SysParameter (Parameter,ParameterValue,Description) values (@p1,@p2,@p3) ;";
parking.ExecuteCommand(cmd, info.Parameter, info.Parameter, info.ParameterValue, info.Description);
}
//本地数据库
public static LDB_DataContext CreateLDB(string connStr)
{
System.Diagnostics.Debug.Assert(!string.IsNullOrEmpty(connStr), "没有找到有效的数据库连接!");
IDbConnection conn = new SQLiteConnection(connStr);
LDB_DataContext ldb = new LDB_DataContext(conn);
return(ldb);
}
protected override void InsertingItem(LDB_SysparameterInfo info, LDB_DataContext parking)
{
LDB_SysparameterInfo original = parking.Sysparameter.SingleOrDefault(s => s.Parameter == info.Parameter);
if (original != null)
{
//删除原来的
parking.Sysparameter.DeleteOnSubmit(original);
}
parking.Sysparameter.InsertOnSubmit(info);
//不适用以下代码是因为每次ExecuteCommand命令时都会访问数据库，sqlite访问数据库会比较耗时，到时数据库操作耗时过长
//string cmd = @"delete from Sysparameter where Parameter=@p0 ; " +
// "insert into SysParameter (Parameter,ParameterValue,Description) values (@p1,@p2,@p3) ;";
//parking.ExecuteCommand(cmd, info.Parameter, info.Parameter, info.ParameterValue, info.Description);
}
public CommandResult Delete(TInfo info)
{
CommandResult result;
try
{
LDB_DataContext ldb = LDB_DataContextFactory.CreateLDB(ConnectStr);
DeletingItem(info, ldb);
ldb.SubmitChanges();
result = new CommandResult(ResultCode.Successful, successMsg);
}
catch (Exception ex)
{
result = new CommandResult(ResultCode.Fail, "从数据库删除数据时发生错误!");
ExceptionPolicy.HandleException(ex, this.GetType().FullName + "." + "Delete()");
}
return(result);
}
public CommandResult Update(TInfo newVal, TInfo original)
{
CommandResult result;
try
{
LDB_DataContext ldb = LDB_DataContextFactory.CreateLDB(ConnectStr);
UpdatingItem(newVal, original, ldb);
ldb.SubmitChanges();
result = new CommandResult(ResultCode.Successful, successMsg);
}
catch (Exception ex)
{
result = new CommandResult(ResultCode.Fail, "数据在写入数据库时发生错误!");
ExceptionPolicy.HandleException(ex, this.GetType().FullName + "." + "Update()");
}
return(result);
}
public void Delete(TInfo info, IUnitWork unitWork)
{
if (unitWork == null)
{
throw new NullReferenceException("参数unitWork为空!");
}
LDB_UnitWork trans = unitWork as LDB_UnitWork;
if (trans != null)
{
LDB_DataContext ldb = trans.LDB;
DeletingItem(info, ldb);
}
else
{
throw new InvalidCastException("参数unitWork不能转换成类型Ralid.LinqDataProvider.UnitWork," +
"请检查参数是否是一个Ralid.LinqDataProvider.UnitWork实例!");
}
}
public QueryResult <TInfo> GetByID(TID id)
{
QueryResult <TInfo> result;
try
{
LDB_DataContext ldb = LDB_DataContextFactory.CreateLDB(ConnectStr);
TInfo info = GetingItemByID(id, ldb);
if (info != null)
{
result = new QueryResult <TInfo>(ResultCode.Successful, successMsg, info);
}
else
{
result = new QueryResult <TInfo>(ResultCode.Fail, string.Format("没有找到ID={0}的数据!", id.ToString()), info);
}
}
catch (Exception ex)
{
result = new QueryResult <TInfo>(ResultCode.Fail, "从数据库获取数据时发生错误!", null);
ExceptionPolicy.HandleException(ex, this.GetType().Name + "." + "GetByID()");
}
return(result);
}
protected override LDB_SysparameterInfo GetingItemByID(string id, LDB_DataContext parking)
{
return(parking.Sysparameter.SingleOrDefault(s => s.Parameter == id));
}
protected virtual void DeletingItem(TInfo info, LDB_DataContext ldb)
{
//如果删除实体时要删除其关联数据,就得重写这个方法
ldb.GetTable <TInfo>().Attach(info);
ldb.GetTable <TInfo>().DeleteOnSubmit(info);
}
protected override LDB_CardPaymentInfo GetingItemByID(int id, LDB_DataContext parking)
{
return(parking.CardPaymentRecord.SingleOrDefault(c => c.ID == id));
}
protected virtual List <TInfo> GetingItems(LDB_DataContext ldb, SearchCondition search)
{
//如果要实现这个功能,子类一定要重写这个方法
throw new NotImplementedException("子类没有重写GetingItems方法");
}
protected virtual List <TInfo> GetingAllItems(LDB_DataContext ldb)
{
//如果实体类要加载其关联数据,就重写此方法
return(ldb.GetTable <TInfo>().Select(t => t).ToList());
}
protected virtual TInfo GetingItemByID(TID id, LDB_DataContext ldb)
{
//每一个子类都要重写这个方法
throw new Exception("子类没有重写GetingItemByID方法!");
}
public LDB_UnitWork(string connStr)
{
_LDB = LDB_DataContextFactory.CreateLDB(connStr);
}
protected virtual void InsertingItem(TInfo info, LDB_DataContext ldb)
{
ldb.GetTable <TInfo>().InsertOnSubmit(info);
}
protected override List <LDB_CardPaymentInfo> GetingItems(LDB_DataContext parking, SearchCondition search)
{
List <LDB_CardPaymentInfo> items = null;
IQueryable <LDB_CardPaymentInfo> result = parking.CardPaymentRecord;
if (search is RecordSearchCondition)
{
RecordSearchCondition condition = search as RecordSearchCondition;
if (condition.RecordDateTimeRange != null)
{
result = result.Where(c => c.ChargeDateTime >= condition.RecordDateTimeRange.Begin);
result = result.Where(c => c.ChargeDateTime <= condition.RecordDateTimeRange.End);
}
if (!string.IsNullOrEmpty(condition.CardID))
{
result = result.Where(c => c.CardID == condition.CardID);
}
if (condition.PaymentMode != null)
{
result = result.Where(c => c.PaymentMode == condition.PaymentMode.Value);
}
if (condition.Operator != null)
{
result = result.Where(c => c.OperatorID == condition.Operator.OperatorName);
}
if (!string.IsNullOrEmpty(condition.StationID))
{
result = result.Where(c => c.StationID == condition.StationID);
}
if (condition.IsUnSettled != null)
{
if (condition.IsUnSettled.Value)
{
result = result.Where(c => c.SettleDateTime == null);
}
else
{
result = result.Where(c => c.SettleDateTime != null);
}
}
if (condition.SettleDateTime != null)
{
result = result.Where(c => c.SettleDateTime == condition.SettleDateTime.Value);
}
if (!string.IsNullOrEmpty(condition.CarPlate))
{
result = result.Where(c => c.CarPlate.Contains(condition.CarPlate));
}
if (condition.CarType != null)
{
result = result.Where(c => c.CarType == condition.CarType.Value);
}
if (!string.IsNullOrEmpty(condition.CardCertificate))
{
result = result.Where(c => c.CardCertificate.Contains(condition.CardCertificate));
}
if (search is CardPaymentRecordSearchCondition)
{
CardPaymentRecordSearchCondition condition1 = search as CardPaymentRecordSearchCondition;
if (condition1.EnterDateTime != null)
{
result = result.Where(c => c.EnterDateTime == condition1.EnterDateTime.Value);
}
if (condition1.IsCenterCharge != null && condition1.IsCenterCharge.Value)
{
result = result.Where(c => c.IsCenterCharge == true);
}
if (condition1.IsCenterCharge != null && !condition1.IsCenterCharge.Value)
{
result = result.Where(c => c.IsCenterCharge == false);
}
if (condition1.ChargeDateTime != null)
{
result = result.Where(c => c.ChargeDateTime == condition1.ChargeDateTime.Value);
}
if (condition1.PaymentCode != null)
{
result = result.Where(c => c.PaymentCode == condition1.PaymentCode.Value);
}
if (!string.IsNullOrEmpty(condition1.OperatorCardID))
{
result = result.Where(c => c.OperatorCardID == condition1.OperatorCardID);
}
}
if (search is LDB_CardPaymentRecordSearchCondition)
{
LDB_CardPaymentRecordSearchCondition ldbsearch = search as LDB_CardPaymentRecordSearchCondition;
if (ldbsearch.UpdateFlag != null)
{
result = result.Where(c => c.UpdateFlag == ldbsearch.UpdateFlag);
}
}
items = result.ToList();
if (condition.CardType != null)
{
items = items.Where(c => c.CardType == condition.CardType).ToList();
}
}
else
{
items = new List <LDB_CardPaymentInfo>();
}
return(items);
}
protected virtual void UpdatingItem(TInfo newVal, TInfo original, LDB_DataContext ldb)
{
//所有实体都可以用这个方法来更新数据
ldb.GetTable <TInfo>().Attach(newVal, original);
}