到原理 (8) 為止，我們已經完成了資料的查詢工作，但資料庫應用程式不是只有查資料而已，對資料的新增，修改和刪除 (C/R/U/D) 也要實作，才算是具有完整的資料存取能力，所以我們也必須要做到 C/U/D 才行，對程式來說，C/U/D 比 R 要簡單，但還是會有一些需要考量的地方，首先，在一堆資料的集合物件中，大部份的情況下不是每一筆都需要做 C/U/D，怎麼判斷每一個物件的狀態，以及在處理物件時何時更改狀態，就是一個重要的課題了，再者，如何產生資料庫需要的 INSERT/UPDATE 和 DELETE 指令，也是我們需要關心的。
到原理 (8) 為止，我們已經完成了資料的查詢工作，但資料庫應用程式不是只有查資料而已，對資料的新增，修改和刪除 (C/R/U/D) 也要實作，才算是具有完整的資料存取能力，所以我們也必須要做到 C/U/D 才行，對程式來說，C/U/D 比 R 要簡單，但還是會有一些需要考量的地方，首先，在一堆資料的集合物件中，大部份的情況下不是每一筆都需要做 C/U/D，怎麼判斷每一個物件的狀態，以及在處理物件時何時更改狀態，就是一個重要的課題了，再者，如何產生資料庫需要的 INSERT/UPDATE 和 DELETE 指令，也是我們需要關心的。
為了要能掌握物件的狀態，我們新增了一個 EntityStatus 列舉項目：
1: public enum EntityStatus
2: {
3: Added,
4: Modified,
5: Deleted,
6: Unchanged
7: }
分別代表了新增，修改，刪除和未變更，不同的狀態會呼叫不同的指令，以對應要處理的 SQL 指令。
再來，我們在 EntityObject 抽象類別中新增了 LastModifiedDate 屬性和 Status 屬性，以及存取它們的一些方法：
1: public DateTime LastModifiedDate { get; private set; }
2: public EntityStatus Status { get; private set; }
3:
4: public EntityObject()
5: {
6: this.IsLoaded = false;
7: this.LastModifiedDate = DateTime.MinValue;
8: this.Status = EntityStatus.Unchanged;
9: }
10:
11: public void SetEntityModified()
12: {
13: this.Status = EntityStatus.Modified;
14: this.LastModifiedDate = DateTime.Now;
15: }
16:
17: public void SetEntityAdded()
18: {
19: this.Status = EntityStatus.Added;
20: this.LastModifiedDate = DateTime.Now;
21: }
22:
23: public void Delete()
24: {
25: this.Status = EntityStatus.Deleted;
26: this.LastModifiedDate = DateTime.Now;
27: }
28:
29: public void SetEntityUnchanged()
30: {
31: this.Status = EntityStatus.Unchanged;
32: this.LastModifiedDate = DateTime.Now;
33: }
接著，在 DataContext 中新增了幾個方法，用來產生 SQL 指令：
1: private string PrepareInsertStatement<T>()
2: {
3: StringBuilder sqlbuilder = new StringBuilder();
4: StringBuilder columnBuilder = new StringBuilder();
5: StringBuilder valueBuilder = new StringBuilder();
6:
7: Configuration.EntityConfiguration entityConfiguration = this._entitySetConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);
8:
9: foreach (Configuration.EntitySchemaMap schemaMap in entityConfiguration.EntitySchemaMaps)
10: {
11: if (columnBuilder.Length == 0)
12: columnBuilder.Append(schemaMap.EntitySchemaName);
13: else
14: columnBuilder.Append(", " + schemaMap.EntitySchemaName);
15:
16: if (valueBuilder.Length == 0)
17: valueBuilder.Append("@" + schemaMap.EntitySchemaName);
18: else
19: valueBuilder.Append(", @" + schemaMap.EntitySchemaName);
20: }
21:
22: sqlbuilder.Append("INSERT INTO ");
23: sqlbuilder.Append(entityConfiguration.SchemaName);
24: sqlbuilder.Append(" (");
25: sqlbuilder.Append(columnBuilder.ToString());
26: sqlbuilder.Append(") VALUES (");
27: sqlbuilder.Append(valueBuilder.ToString());
28: sqlbuilder.Append(")");
29:
30: return sqlbuilder.ToString();
31: }
32:
33: private string PrepareUpdateStatement<T>()
34: {
35: return PrepareUpdateStatement<T>(null);
36: }
37:
38: private string PrepareUpdateStatement<T>(ExpressionTree QueryConditionTree)
39: {
40: StringBuilder sqlbuilder = new StringBuilder();
41: StringBuilder columnBuilder = new StringBuilder();
42: Configuration.EntityConfiguration entityConfiguration =
43: this._entitySetConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);
44:
45: // load columns.
46: sqlbuilder.Append("UPDATE ");
47: sqlbuilder.Append(entityConfiguration.SchemaName);
48: sqlbuilder.Append(" SET ");
49:
50: int pos = 0;
51:
52: foreach (Configuration.EntitySchemaMap entityMapItem in entityConfiguration.EntitySchemaMaps)
53: {
54: if (pos == 0)
55: columnBuilder.Append(entityMapItem.EntitySchemaName + " = @" + entityMapItem.EntityPropertyName);
56: else
57: {
58: columnBuilder.Append(", ");
59: columnBuilder.Append(entityMapItem.EntitySchemaName + " = @" + entityMapItem.EntityPropertyName);
60: }
61:
62: pos++;
63: }
64:
65: sqlbuilder.Append(columnBuilder.ToString());
66:
67: // prepare WHERE clause.
68: if (QueryConditionTree != null)
69: sqlbuilder.Append(" WHERE " + QueryConditionTree.BuildExpressionEval(ExpressionTraversalType.LMR));
70: else
71: {
72: List<Configuration.EntitySchemaMap> keyColumns = entityConfiguration.EntitySchemaMaps.GetKeyColumns();
73: StringBuilder whereBuilder = new StringBuilder();
74:
75: foreach (Configuration.EntitySchemaMap keyColumn in keyColumns)
76: {
77: if (whereBuilder.Length == 0)
78: whereBuilder.Append(keyColumn.EntitySchemaName + " = @" + keyColumn.EntitySchemaName);
79: else
80: whereBuilder.Append(" AND " + keyColumn.EntitySchemaName + " = @" + keyColumn.EntitySchemaName);
81: }
82:
83: sqlbuilder.Append(" WHERE " + whereBuilder.ToString());
84: }
85:
86: return sqlbuilder.ToString();
87: }
88:
89: private string PrepareDeleteStatement<T>()
90: {
91: return PrepareDeleteStatement<T>(null);
92: }
93:
94: private string PrepareDeleteStatement<T>(ExpressionTree QueryConditionTree)
95: {
96: StringBuilder sqlbuilder = new StringBuilder();
97: Configuration.EntityConfiguration entityConfiguration =
98: this._entitySetConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);
99:
100: // load columns.
101: sqlbuilder.Append("DELETE FROM ");
102: sqlbuilder.Append(entityConfiguration.SchemaName);
103:
104: // prepare WHERE clause.
105: if (QueryConditionTree != null)
106: sqlbuilder.Append(" WHERE " + QueryConditionTree.BuildExpressionEval(ExpressionTraversalType.LMR));
107: else
108: {
109: List<Configuration.EntitySchemaMap> keyColumns = entityConfiguration.EntitySchemaMaps.GetKeyColumns();
110: StringBuilder whereBuilder = new StringBuilder();
111:
112: foreach (Configuration.EntitySchemaMap keyColumn in keyColumns)
113: {
114: if (whereBuilder.Length == 0)
115: whereBuilder.Append(keyColumn.EntitySchemaName + " = @" + keyColumn.EntitySchemaName);
116: else
117: whereBuilder.Append(" AND " + keyColumn.EntitySchemaName + " = @" + keyColumn.EntitySchemaName);
118: }
119:
120: sqlbuilder.Append(" WHERE " + whereBuilder.ToString());
121: }
122:
123: return sqlbuilder.ToString();
124: }
完成以後，我們再於 EntityObject 和 EntityCollectionObject<T> 中，加入 Save() 方法：
1: // EntityObject.Save()
2: public void Save()
3: {
4: DB.DataContext context = new DB.DataContext("sql");
5: MethodInfo methodToInvoke = null;
6: Type thisType = this.GetType();
7:
8: if (thisType == typeof(EntityObject))
9: return;
10:
11: switch (this.Status)
12: {
13: case EntityStatus.Added:
14:
15: methodToInvoke = context.GetType().GetMethod("Insert");
16: methodToInvoke = methodToInvoke.MakeGenericMethod(thisType);
17: methodToInvoke.Invoke(context, new object[] { this });
18:
19: break;
20: case EntityStatus.Modified:
21:
22: methodToInvoke = context.GetType().GetMethod("Update");
23: methodToInvoke = methodToInvoke.MakeGenericMethod(thisType);
24: methodToInvoke.Invoke(context, new object[] { this });
25:
26: break;
27: case EntityStatus.Deleted:
28:
29: methodToInvoke = context.GetType().GetMethod("Delete");
30: methodToInvoke = methodToInvoke.MakeGenericMethod(thisType);
31: methodToInvoke.Invoke(context, new object[] { this });
32:
33: break;
34: case EntityStatus.Unchanged:
35: break;
36: }
37:
38: context = null;
39: }
1: // EntityCollectionObject<T>.Save()
2: public void SaveObjects()
3: {
4: DB.DataContext context = new DB.DataContext("sql");
5: MethodInfo methodToInvoke = null;
6: Type thisType = this.GetType();
7: List<T> deletedEntityList = new List<T>();
8:
9: context.EnableProviderBulkLoad();
10: context.Open();
11:
12: if (thisType == typeof(EntityObject))
13: return;
14:
15: foreach (T item in this)
16: {
17: EntityStatus status = (EntityStatus)typeof(T).GetProperty("Status").GetValue(item, null);
18:
19: switch (status)
20: {
21: case EntityStatus.Added:
22:
23: methodToInvoke = context.GetType().GetMethod("Insert");
24: methodToInvoke = methodToInvoke.MakeGenericMethod(typeof(T));
25: methodToInvoke.Invoke(context, new object[] { item });
26:
27: break;
28: case EntityStatus.Modified:
29:
30: methodToInvoke = context.GetType().GetMethod("Update");
31: methodToInvoke = methodToInvoke.MakeGenericMethod(typeof(T));
32: methodToInvoke.Invoke(context, new object[] { item });
33:
34: break;
35: case EntityStatus.Deleted:
36:
37: methodToInvoke = context.GetType().GetMethod("Delete");
38: methodToInvoke = methodToInvoke.MakeGenericMethod(typeof(T));
39: methodToInvoke.Invoke(context, new object[] { item });
40:
41: deletedEntityList.Add(item);
42:
43: break;
44: case EntityStatus.Unchanged:
45: break;
46: }
47: }
48:
49: // find and remove the item marked "delete".
50: foreach (T deletedItem in deletedEntityList)
51: base.Remove(deletedItem);
52:
53: context.Close();
54: context.DisableProviderBulkLoad();
55: context = null;
56: }
同時，考量 EntityCollectionObject<T> 是一個集合，所以我們要對集合的處理習慣修改一下：
1: public new void Add(T entityItem)
2: {
3: this.Add(entityItem, true);
4: }
5:
6: public void Add(T entityItem, bool IsNewObject)
7: {
8: if (IsNewObject)
9: {
10: // invoke the new object's status to "Added".
11: MethodInfo setAddedStateMethod = typeof(T).GetMethod("SetEntityAdded");
12: setAddedStateMethod.Invoke(entityItem, null);
13: }
14: else
15: {
16: MethodInfo setUnchangedStateMethod = typeof(T).GetMethod("SetEntityUnchanged");
17: setUnchangedStateMethod.Invoke(entityItem, null);
18: }
19:
20: base.Add(entityItem);
21: }
22:
23: public new void Remove(T entityItem)
24: {
25: MethodInfo setDeletedStateMethod = typeof(T).GetMethod("SetEntityDeleted");
26: setDeletedStateMethod.Invoke(entityItem, null);
27: }
最後，我們就可以在主程式中呼叫這些方法，來處理 C/U/D 的工作了：
1: DB.DataContext db = new DB.DataContext("sql");
2: CustomerCollection customerList = db.GetEntities<CustomerCollection, Customer>();
3:
4: // single object save.
5: Customer customer = new Customer()
6: {
7: CustomerID = "TTTTT",
8: CompanyName = "test",
9: ContactName = "test",
10: ContactTitle = "test",
11: Country = "TW",
12: City = "KHH",
13: Address = "test",
14: Phone = "111-111-1111",
15: Fax = "111-111-1111",
16: PostalCode = "00000",
17: Region = "TW"
18: };
19:
20: customer.Save();
21:
22: // collection operations.
23: // add new customer.
24: customerList.Add(new Customer()
25: {
26: CustomerID = "TTTTT",
27: CompanyName = "test",
28: ContactName = "test",
29: ContactTitle = "test",
30: Country = "TW",
31: City = "KHH",
32: Address = "test",
33: Phone = "111-111-1111",
34: Fax = "111-111-1111",
35: PostalCode = "00000",
36: Region = "TW"
37: });
38:
39: customerList.SaveObjects();
40:
41: // update.
42: foreach (Customer customer in customerList)
43: {
44: if (customer.CustomerID == "TTTTT")
45: {
46: // change value.
47: customer.CompanyName = "12345";
48: customer.SetEntityModified();
49: }
50: }
51:
52: customerList.SaveObjects();
53:
54: // delete
55: foreach (Customer customer in customerList)
56: {
57: if (customer.CustomerID == "TTTTT")
58: {
59: // delete.
60: customer.Delete();
61: }
62: }
63:
64: customerList.SaveObjects();
Source Code: https://dotblogsfile.blob.core.windows.net/user/regionbbs/1111/2011112812814972.rar