public static void UpdateStatus(int id, string tableName, string fileName,string title, int archiveId, IQueryTable toTable, QueryColumn toStatus, QueryColumn toPrimary)
{
NBearLite.DbProviders.DbProvider provider =
NBearLite.DbProviders.DbProviderFactory.CreateDbProvider(
null,
"System.Data.SqlServerCe",
@"Data Source=enforce.sdf;
Encrypt Database=True;
Password=enforce_123456;
File Mode=shared read;
Persist Security Info=False;");
Database database = new Database(provider);
UpdateSqlSection section = new UpdateSqlSection(database, toTable);
section.AddColumn(toStatus, 2); // 2 代表已归档
section.Where(toPrimary == id);
section.Execute();
InsertSqlSection insert = new InsertSqlSection(database, DataBases.ArchiveExt);
insert.AddColumn(DataBases.ArchiveExt.ArchiveID, archiveId);
insert.AddColumn(DataBases.ArchiveExt.TableName, tableName);
insert.AddColumn(DataBases.ArchiveExt.PrimaryValue, id);
insert.AddColumn(DataBases.ArchiveExt.FileName, fileName);
insert.AddColumn(DataBases.ArchiveExt.Title, title);
insert.AddColumn(DataBases.ArchiveExt.CreateTime, DateTime.Now);
insert.AddColumn(DataBases.ArchiveExt.ExchangeGUID, CommonInvoke.NewGuid);
insert.Execute();
}
public void UpdateModelOne(QccustomerlibraryModel model, DbTransaction tran)
{
QccustomerlibraryTable table = new QccustomerlibraryTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.MillComments, model.MillComments).Where(table.QualityCode == model.QualityCode && table.BuyerId == model.BuyerId)
.Execute();
}
public void UpdateModel(QcfinishdtlModel model, DbTransaction tran)
{
QcfinishdtlTable table = new QcfinishdtlTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.FinishingCode, model.FinishingCode)
.Execute();
}
public void UpdateModel(QcconstructiondtlModel model, DbTransaction tran)
{
QcconstructiondtlTable table = new QcconstructiondtlTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.Construction, model.Construction)
.Execute();
}
public void UpdateModel(QcyarndtlModel model, DbTransaction tran)
{
QcyarndtlTable table = new QcyarndtlTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.YarnType, model.YarnType)
.AddColumn(table.YarnCount, model.YarnCount)
.AddColumn(table.Threads, model.Threads)
.AddColumn(table.YarnRatio, model.YarnRatio)
.AddColumn(table.YarnComponent, model.YarnComponent)
.Where(table.QualityCode == model.QualityCode)
.Execute();
}
public void UpdateModel(QcavailablewidthModel model, DbTransaction tran)
{
QcavailablewidthTable table = new QcavailablewidthTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.Gauge, model.Gauge)
.AddColumn(table.Diameter, model.Diameter)
.AddColumn(table.TotalNeedles, model.TotalNeedles)
.AddColumn(table.Width, model.Width)
.AddColumn(table.MaxWidth, model.MaxWidth)
.AddColumn(table.UpdatedBy, model.UpdatedBy)
.AddColumn(table.UpdatedTime, model.UpdatedTime)
.Execute();
}
public void UpdateModel(QC_HF_ChangeLogModel model, DbTransaction tran)
{
QC_HF_ChangeLogTable table = new QC_HF_ChangeLogTable();
UpdateSqlSection sql = DataAccess.DefaultDB.Update(table);
if (tran != null)
{
sql.SetTransaction(tran);
}
sql.AddColumn(table.QUALITY_CODE, model.QualityCode)
.AddColumn(table.CREATE_DATE, model.CreateDate)
.AddColumn(table.CREATOR, model.Creator)
.AddColumn(table.QC_Ref_PPO_Old, model.QC_Ref_PPO_Old)
.AddColumn(table.QC_Ref_GP_Old, model.QC_Ref_GP_Old)
.AddColumn(table.HF_Ref_PPO_Old, model.HF_Ref_PPO_Old)
.AddColumn(table.HF_Ref_GP_Old, model.HF_Ref_GP_Old)
.AddColumn(table.QC_Ref_PPO_New, model.QC_Ref_PPO_New)
.AddColumn(table.QC_Ref_GP_New, model.QC_Ref_GP_New)
.AddColumn(table.HF_Ref_PPO_New, model.HF_Ref_PPO_New)
.AddColumn(table.HF_Ref_GP_New, model.HF_Ref_GP_New).Where(table.QUALITY_CODE == model.QualityCode)
.Execute();
}
private IEnumerable <IInsqlSection> ParseSections(XElement root)
{
var sqlSections = root.Elements(XName.Get("sql", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql sql section `id` is empty !");
}
var section = new SqlInsqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var selectSqlSections = root.Elements(XName.Get("select", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql select insert sql section element `id` is empty !");
}
var section = new SelectSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var insertSqlSections = root.Elements(XName.Get("insert", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql insert sql section element `id` is empty !");
}
var section = new InsertSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var updateSqlSections = root.Elements(XName.Get("update", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql update sql section element `id` is empty !");
}
var section = new UpdateSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var deleteSqlSections = root.Elements(XName.Get("delete", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql delete sql section element `id` is empty !");
}
var section = new DeleteSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
return(sqlSections
.Concat(selectSqlSections)
.Concat(insertSqlSections)
.Concat(updateSqlSections)
.Concat(deleteSqlSections)
.ToList());
}
/// <summary>
/// 更新表
/// </summary>
/// <param name="database">Comfy.Data.Database</param>
/// <param name="table">table類</param>
/// <param name="model">model類</param>
/// <param name="updateFields">要更新的字段，名字要和model中的屬性相同。如果要更新所有的字段，此參數為null</param>
/// <param name="whereStrs">條件字段</param>
public static void UpdateTable(Database database, QueryTable table, Object model, Comfy.Data.QueryColumn[] updateFields, Comfy.Data.QueryColumn[] whereQc)
{
Type modelType = model.GetType();
Type tableType = table.GetType();
String s = "";
Comfy.Data.QueryColumn wQc = null;
UpdateSqlSection updateSql = database.Update(table);
if (updateFields == null) //更新所有字段
{
foreach (System.Reflection.PropertyInfo info in modelType.GetProperties()) //更新所有字段
{
s = info.Name;
int i = 0;
foreach (QueryColumn wqc in whereQc)
{
if (wqc.Name == s)
{
i++;
break;
}
}
if (i == 0)
{
QueryColumn qc = (Comfy.Data.QueryColumn)table.GetType().GetProperty(s).GetValue(table, null);
updateSql.AddColumn(qc, info.GetValue(model, null));
}
}
}
else //更新部分字段
{
foreach (QueryColumn qc in updateFields)
{
foreach (System.Reflection.PropertyInfo info in modelType.GetProperties()) //更新所有字段
{
if (qc.Name == info.Name)
{
updateSql.AddColumn(qc, info.GetValue(model, null));
break;
}
}
}
}
foreach (QueryColumn wqc in whereQc)
{
foreach (System.Reflection.PropertyInfo wInfo in modelType.GetProperties()) //條件語句
{
if (wqc.Name == wInfo.Name)
{
updateSql.Where(wqc == wInfo.GetValue(model, null));
break;
}
}
}
updateSql.Execute();
}
