private void button4_Click(object sender, EventArgs e)
{
using (var db = new Repository.SqliteDbContext())
{
Models.Accounts Root = new Models.Accounts();
Root.AccountName = "成本科目清单";
Root.AccountLevel = 0;
Root.Level = 0;
Root.Name = "根节点";
Root.Order = 0;
Root.Child = new List <Models.Tree>();
Models.ExpenseAccounts ea = new Models.ExpenseAccounts();
ea.Name = "费用1";
ea.PricingComent = "输入收费说明";
ea.Level = 1;
ea.Order = 0;
Root.Child.Add(ea);
ea.Child = new List <Models.Tree>();
Models.Accounts ac = new Models.Accounts();
ac.Name = "费用1-科目1";
ac.AccountName = "科目";
ac.Level = 2;
ac.Order = 0;
ea.Child.Add(ac);
var _account = db.Set <Models.Accounts>();
_account.Add(Root);
db.SaveChanges();
}
}
private void onDataDelete(Repository.SqliteDbContext db, DataTable btDT, DataTable itDT)
{
//删除子表
var itDTc = itDT.GetChanges(DataRowState.Deleted);
if (itDTc != null)
{
var items = db.Set <Models.TypeItem>();
foreach (System.Data.DataRow r in itDTc.Rows)
{
int?changeid = r.Field <int>(0, DataRowVersion.Original);
var i = items.ToList().Find(x => x.Id == changeid);
items.Attach(i);
items.Remove(i);
}
}
//删主表
var btDTc = btDT.GetChanges(DataRowState.Deleted);
if (btDTc != null)
{
var types = db.Set <Models.BaseType>();
foreach (System.Data.DataRow r in btDTc.Rows)
{
int?changeid = r.Field <int>(0, DataRowVersion.Original);
#region 除
//删除明细
var _base = types.Include(b => b.HaveItem)
.Where(x => x.Id == changeid)
.ToList().FirstOrDefault();
if (_base != null && _base.HaveItem != null)
{
db.RemoveRange(_base.HaveItem);
}
if (db.ChangeTracker != null)
{
var a = db.ChangeTracker.Entries <Models.BaseType>()
.Where(x => x.Entity.Id == changeid).FirstOrDefault().Entity;
types.Attach(a);
types.Remove(a);
}
#endregion
}
}
}
private void onDataModify(Repository.SqliteDbContext db, DataTable btDT, DataTable itDT)
{
//修改子表
var itDTc = itDT.GetChanges(DataRowState.Modified);
if (itDTc != null)
{
var items = db.Set <Models.TypeItem>();
foreach (System.Data.DataRow r in itDTc.Rows)
{
int?changeid = r.Field <int>(0, DataRowVersion.Original);
var i = items.ToList().Find(x => x.Id == changeid);
var changeitem = items.Attach(i);
changeitem.Entity.ItemName = r.Field <String>(2);
changeitem.Entity.ItemValue = r.Field <String>(3);
changeitem.Entity.ItemValueType = r.Field <String>(4);
changeitem.Entity.Comment = r.Field <String>(5);
changeitem.Entity.ItemOrder = r.Field <int>(6);
changeitem.Entity.Enable = DBNull.Value.Equals(r[7]) ? false : r.Field <Boolean>(7);
changeitem.Entity.Modifier = r.Field <String>(10);
changeitem.Entity.ModifyDate = DBNull.Value.Equals(r[11]) ? System.DateTime.Now : r.Field <DateTime>(11);
}
}
//修改主表
var btDTc = btDT.GetChanges(DataRowState.Modified);
if (btDTc != null)
{
var types = db.Set <Models.BaseType>();
foreach (System.Data.DataRow r in btDTc.Rows)
{
int?changeid = r.Field <int>(0, DataRowVersion.Original);
var i = types.ToList().Find(x => x.Id == changeid);
var b = db.Attach(i);
b.Entity.BaseTypeName = r.Field <String>(1);
b.Entity.Comment = r.Field <String>(2);
b.Entity.TypeOrder = r.Field <int>(3);
b.Entity.Enable = DBNull.Value.Equals(r[4]) ? false : r.Field <Boolean>(4);
b.Entity.Modifier = r.Field <String>(7);
b.Entity.ModifyDate = DBNull.Value.Equals(r[8]) ? System.DateTime.Now : r.Field <DateTime>(8);
}
}
}
//保存数据
public void ChangeGridData( )
{
var ds = (DataSet)((BindingSource)(dataGridView1.DataSource)).DataSource;
var bdt = ds.Tables["BaseType"];
var idt = ds.Tables["ItemType"];
var idts = ds.Relations[0];
/**
* 删除 修改 新增
* 1.删除子表 1.修改子表 1.新增主表
* 2.删除主表 2.修改主表 2.新增子表
*/
using (var db = new Repository.SqliteDbContext())
{
onDataDelete(db, bdt, idt);
onDataModify(db, bdt, idt);
onDataAdd(db, bdt, idt, idts);
db.SaveChanges();
}
ds.AcceptChanges();
initGridView();
}
public AccountServices()
{
_dbContext = new Repository.SqliteDbContext();
}
public TypeItemServices()
{
_dbContext = new Repository.SqliteDbContext();
}
public BaseTypeServices()
{
_dbContext = new Repository.SqliteDbContext();
}
public void initGridView()
{
dataGridView1.DataSource = null;
dataGridView2.DataSource = null;
using (var db = new Repository.SqliteDbContext())
{
var _BaseTypes = db.Set <Models.BaseType>();
var dl = from bt in _BaseTypes.AsEnumerable()
orderby bt.TypeOrder
select new
{
basetypeid = bt.Id,
项目名称 = bt.BaseTypeName,
备注 = bt.Comment,
顺序 = bt.TypeOrder,
状态 = bt.Enable,
创建人 = bt.Creator,
创建时间 = bt.CreateDate,
修改人 = bt.Modifier,
修改时间 = bt.ModifyDate
};
if (dl != null && dl.Count() > 0)
{
maxtypeid = dl.Max(x => x.basetypeid);
}
var il = from i in _BaseTypes.SelectMany(x => x.HaveItem).AsEnumerable()
orderby i.ItemParent.TypeOrder, i.ItemOrder
select new
{
ItemId = i.Id,
ParentId = i.ItemParent.Id,
值名称 = i.ItemName,
值 = i.ItemValue,
值类型 = i.ItemValueType,
备注 = i.Comment,
顺序 = i.ItemOrder,
状态 = i.Enable,
创建人 = i.Creator,
创建时间 = i.CreateDate,
修改人 = i.Modifier,
修改时间 = i.ModifyDate
};
if (il != null && il.Count() > 0)
{
maxitemid = il.Max(x => x.ItemId);
}
var tblDataSet = new DataSet();
var tblBaseType = dl.CopyToDataTable();
tblBaseType.TableName = "BaseType";
var tblItemType = il.CopyToDataTable();
tblItemType.TableName = "ItemType";
tblDataSet.Tables.Add(tblBaseType);
tblDataSet.Tables.Add(tblItemType);
tblDataSet.Relations.Add("BaseItemRelation",
tblBaseType.Columns["basetypeid"], tblItemType.Columns["ParentId"]);
BindingSource bsBaseType = new BindingSource();
bsBaseType.DataSource = tblDataSet;
bsBaseType.DataMember = "BaseType";
BindingSource bsItemType = new BindingSource();
bsItemType.DataSource = bsBaseType;
bsItemType.DataMember = "BaseItemRelation";
dataGridView1.DataSource = bsBaseType;
dataGridView1.Columns[0].Visible = false;
dataGridView2.DataSource = bsItemType;
dataGridView2.Columns[0].Visible = false;
dataGridView2.Columns[1].Visible = false;
}
}
private void onDataAdd(Repository.SqliteDbContext db, DataTable btDT, DataTable itDT, DataRelation drc)
{
//新增主表
// var btDTc = btDT.GetChanges(DataRowState.Added);
var btDTc = drc.ParentTable;//.GetChanges(DataRowState.Added);
if (btDTc != null)
{
var types = db.Set <Models.BaseType>();
foreach (System.Data.DataRow r in btDTc.Rows)
{
if (r.RowState != DataRowState.Added)
{
continue;
}
var newbasetype = new Models.BaseType();
newbasetype.BaseTypeName = r.Field <String>(1);
newbasetype.Comment = r.Field <String>(2);
newbasetype.TypeOrder = DBNull.Value.Equals(r[3]) ? 0 : r.Field <int>(3);
newbasetype.Enable = DBNull.Value.Equals(r[4]) ? false : r.Field <Boolean>(4);
newbasetype.Creator = r.Field <String>(5);
newbasetype.CreateDate = DBNull.Value.Equals(r[6]) ? System.DateTime.Now : r.Field <DateTime>(6);
newbasetype.Modifier = r.Field <String>(7);
newbasetype.ModifyDate = DBNull.Value.Equals(r[8]) ? System.DateTime.Now : r.Field <DateTime>(8);
var childrows = r.GetChildRows("BaseItemRelation");
foreach (DataRow cr in childrows)
{
var newitem = new Models.TypeItem();
newitem.ItemName = cr.Field <String>(2);
newitem.ItemValue = cr.Field <String>(3);
newitem.ItemValueType = cr.Field <String>(4);
newitem.Comment = cr.Field <String>(5);
newitem.ItemOrder = DBNull.Value.Equals(cr[6]) ? 0 : cr.Field <int>(6);
newitem.Enable = DBNull.Value.Equals(cr[7]) ? false : cr.Field <Boolean>(7);
newitem.Creator = cr.Field <String>(8);
newitem.CreateDate = DBNull.Value.Equals(cr[9]) ? System.DateTime.Now : cr.Field <DateTime>(9);
newitem.Modifier = cr.Field <String>(10);
newitem.ModifyDate = DBNull.Value.Equals(cr[11]) ? System.DateTime.Now : cr.Field <DateTime>(11);
if (newbasetype.HaveItem == null)
{
newbasetype.HaveItem = new List <Models.TypeItem>();
}
newbasetype.HaveItem.Add(newitem);
}
types.Add(newbasetype);
}
}
//新增子表
var itDTc = itDT.GetChanges(DataRowState.Added);
if (itDTc != null)
{
var items = db.Set <Models.TypeItem>();
foreach (System.Data.DataRow r in itDTc.Rows)
{
if (DBNull.Value.Equals(r[1]))
{
continue;
}
int basetypeid = r.Field <int>(1);
var b = db.Set <Models.BaseType>()
.Include(i => i.HaveItem)
.Where(x => x.Id == basetypeid)
.FirstOrDefault();
if (b != null)
{
var newitem = new Models.TypeItem();
newitem.ItemName = r.Field <String>(2);
newitem.ItemValue = r.Field <String>(3);
newitem.ItemValueType = r.Field <String>(4);
newitem.Comment = r.Field <String>(5);
newitem.ItemOrder = DBNull.Value.Equals(r[6]) ? 0 : r.Field <int>(6);
newitem.Enable = DBNull.Value.Equals(r[7]) ? false : r.Field <Boolean>(7);
newitem.Creator = r.Field <String>(8);
newitem.CreateDate = DBNull.Value.Equals(r[9]) ? System.DateTime.Now : r.Field <DateTime>(9);
newitem.Modifier = r.Field <String>(10);
newitem.ModifyDate = DBNull.Value.Equals(r[11]) ? System.DateTime.Now : r.Field <DateTime>(11);
b.HaveItem.Add(newitem);
}
}
}
}
