Hopefully this has what you are looking for:
1 using System;
2 using System.Data;
3 using SubSonic.Utilities;
4 using System.Text;
5 namespace SubSonic
6 {
7
8 public abstract class ActiveRecord : AbstractRecord where T : AbstractRecord, new()
9 {
10 public ActiveRecord()
11 {
12 MarkNew();
13 }
14
15 #region CommandMethods
16
17 /// <summary>
18 /// Made Public for use with transactions
19 /// </summary>
20 /// <param name="userName"></param>
21 /// <returns></returns>
22 public QueryCommand GetInsertCommand(string userName)
23 {
24 Query q = new Query(table);
25 q.QueryType = QueryType.Insert;
26 QueryCommand cmd = new QueryCommand(DataService.GetSql(q));
27
28 //loop the Columns and addin the params
29
30 foreach (TableSchema.TableColumn column in table.Columns)
31 {
32 if (!column.AutoIncrement)
33 {
34 object oVal;
35 if (Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_BY) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))
36 {
37 oVal = userName;
38 }
39 else if (Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_ON) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))
40 {
41 oVal = DateTime.Now;
42 }
43 else
44 {
45 oVal = GetColumnValue(column.ColumnName);
46
47 //if the value is a boolean, it can be read improperly
48 //reset to 0 or 1
49 if (oVal != null)
50 {
51 if (Utility.IsMatch(oVal.ToString(), "false"))
52 {
53 oVal = 0;
54 }
55 else if (Utility.IsMatch(oVal.ToString(), "true"))
56 {
57 oVal = 1;
58 }
59 }
60 }
61 if (oVal == null)
62 {
63 oVal = DBNull.Value;
64 }
65 cmd.Parameters.Add("@" + column.ColumnName, oVal, column.DataType);
66 }
67 }
68 return cmd;
69 }
70
71 public QueryCommand GetUpdateCommand(string userName)
72 {
73 Query q = new Query(table);
74 q.QueryType = QueryType.Update;
75 QueryCommand cmd = new QueryCommand(DataService.GetSql(q));
76
77 //loop the Columns and addin the params
78 foreach (TableSchema.TableColumn column in table.Columns)
79 {
80 object oVal;
81 if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))
82 {
83 oVal = userName;
84 }
85 else if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))
86 {
87 oVal = DateTime.Now;
88 }
89 else
90 {
91 oVal = GetColumnValue(column.ColumnName);
92 }
93 if (oVal == null)
94 {
95 oVal = DBNull.Value;
96 }
97 cmd.Parameters.Add("@" + column.ColumnName, oVal,column.DataType);
98 }
99 return cmd;
100 }
101
102 public static QueryCommand GetDeleteCommand(object keyID)
103 {
104 Query q = new Query(table);
105 q.QueryType = QueryType.Delete;
106 q.AddWhere(table.PrimaryKey.ColumnName, keyID);
107
108 return DataService.BuildCommand(q);
109 }
110
111 public static QueryCommand GetDeleteCommand(string columnName, object oValue)
112 {
113 Query q = new Query(table);
114 q.QueryType = QueryType.Delete;
115 q.AddWhere(columnName, oValue);
116
117 return DataService.BuildCommand(q);
118 }
119
120 #endregion
121
122
123 #region Persistence
124
125 protected virtual void PreUpdate()
126 {
127 }
128
129 protected virtual void PostUpdate()
130 {
131 }
132
133 /// <summary>
134 /// Saves this object's state to the selected Database.
135 /// </summary>
136 public void Save()
137 {
138 Save(String.Empty);
139 }
140
141 /// <summary>
142 /// Saves this object's state to the selected Database.
143 /// </summary>
144 /// <param name="userID"></param>
145 public void Save(int userID)
146 {
147 Save(userID.ToString());
148 }
149
150 /// <summary>
151 /// Saves this object's state to the selected Database.
152 /// </summary>
153 /// <param name="userID"></param>
154 public void Save(Guid userID)
155 {
156 string sUserID = string.Empty;
157 if (userID!= null)
158 sUserID = userID.ToString();
159
160 Save(sUserID);
161 }
162
163 /// <summary>
164 /// Saves this object's state to the selected Database.
165 /// </summary>
166 /// <param name="userName"></param>
167 public void Save(string userName)
168 {
169 PreUpdate();
170
171 QueryCommand cmd;
172 if (IsNew)
173 {
174 cmd = GetInsertCommand(userName);
175 }
176 else
177 {
178 cmd = GetUpdateCommand(userName);
179 }
180
181 //reset the Primary Key with the id passed back by the operation
182 object pkVal = DataService.ExecuteScalar(cmd);
183
184 //set the primaryKey, only if an auto-increment
185 //if (table.PrimaryKey.AutoIncrement)
186 // HACK: GUID fix
187 if (table.PrimaryKey.AutoIncrement || table.PrimaryKey.DataType == DbType.Guid)
188 {
189 try {
190 SetPrimaryKey(pkVal);
191 } catch {
192
193 //this will happen if there is no PK defined on a table. Catch this and notify
194 throw new Exception("No Primary Key is defined for this table. A primary key is required to use SubSonic");
195 }
196 }
197
198 //set this object as old
199 MarkOld();
200 isDirty = false;
201 PostUpdate();
202
203 }
204
205 /// <summary>
206 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
207 /// </summary>
208 /// <returns>Number of rows affected by the operation</returns>
209 public static int Delete(object keyID)
210 {
211 return DeleteByParameter(BaseSchema.PrimaryKey.ColumnName, keyID, null);
212 }
213
214 /// <summary>
215 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
216 /// </summary>
217 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
218 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
219 /// <returns>Number of rows affected by the operation</returns>
220 public static int Delete(string columnName, object oValue)
221 {
222 return DeleteByParameter(columnName, oValue, null);
223 }
224
225 /// <summary>
226 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
227 /// </summary>
228 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
229 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
230 /// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>
231 /// <returns>Number of rows affected by the operation</returns>
232 public static int Delete(string columnName, object oValue, string userName)
233 {
234 return DeleteByParameter(columnName, oValue, userName);
235 }
236
237 /// <summary>
238 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
239 /// </summary>
240 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
241 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
242 /// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>
243 /// <returns>Number of rows affected by the operation</returns>
244 private static int DeleteByParameter(string columnName, object oValue, string userName)
245 {
246 int iOut = 0;
247
248 bool containsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.DELETED);
249 bool containsIsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.IS_DELETED);
250 bool containsModifiedBy = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_BY);
251 bool containsModifiedOn = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_ON);
252 if (containsDeleted || containsIsDeleted)
253 {
254 //update the column and set deleted=true;
255 //new T();
256 Query qry = new Query(BaseSchema);
257 if (containsDeleted)
258 {
259 qry.AddUpdateSetting(ReservedColumnName.DELETED, true);
260 }
261
262 if (containsIsDeleted)
263 {
264 qry.AddUpdateSetting(ReservedColumnName.IS_DELETED, true);
265 }
266
267 if (containsModifiedBy && !String.IsNullOrEmpty(userName))
268 {
269 qry.AddUpdateSetting(ReservedColumnName.MODIFIED_BY, userName);
270 }
271
272 if (containsModifiedOn)
273 {
274 qry.AddUpdateSetting(ReservedColumnName.MODIFIED_ON, DateTime.Now);
275 }
276 qry.AddWhere(columnName, oValue);
277 qry.Execute();
278 }
279 else
280 {
281 iOut = DestroyByParameter(columnName, oValue);
282 }
283 return iOut;
284 }
285
286 /// <summary>
287 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
288 /// </summary>
289 /// <returns>Number of rows affected by the operation</returns>
290 public static int Destroy(object keyID)
291 {
292 return DestroyByParameter(BaseSchema.PrimaryKey.ColumnName, keyID);
293 }
294
295 /// <summary>
296 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
297 /// </summary>
298 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
299 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
300 /// <returns>Number of rows affected by the operation</returns>
301 public static int Destroy(string columnName, object oValue)
302 {
303 return DestroyByParameter(columnName, oValue);
304 }
305
306 /// <summary>
307 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
308 /// </summary>
309 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
310 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
311 /// <returns>Number of rows affected by the operation</returns>
312 private static int DestroyByParameter(string columnName, object oValue)
313 {
314 QueryCommand cmd = GetDeleteCommand(columnName, oValue);
315 return DataService.ExecuteQuery(cmd);
316 }
317
318 #endregion
319
320 #region Object Overrides
321 //public string Inspect() {
322 // return Inspect(true);
323 //}
324 //public string Inspect(bool useHtml) {
325 // System.Text.StringBuilder sb = new StringBuilder();
326 // string sOut = "";
327 // if (useHtml) {
328 // sb.Append("<table><tr><td colspan=2><h3>" + this.TableName + " Inspection</h3></td></tr>");
329
330 // foreach (TableSchema.TableColumn col in table.Columns) {
331 // sb.Append("<tr><td><b>" + col.ColumnName + "</b></td><td>" + this.GetColumnValue(col.ColumnName).ToString() + "</td></tr>");
332
333 // }
334 // sb.Append("</table>");
335 // sOut = sb.ToString();
336 // } else {
337 // sb.Append("#################" + this.TableName + " Inspection ####################\r\n");
338
339 // foreach (TableSchema.TableColumn col in table.Columns) {
340 // sb.Append(col.ColumnName + ": " + this.GetColumnValue(col.ColumnName).ToString() + "\r\n");
341
342 // }
343 // sb.Append("#############################################################################\r\n");
344 // sOut = sb.ToString();
345
346 // }
347 // return sOut;
348 //}
349 #endregion
350 }
351 }
Best regards,
Jeremy Hodges | Windows Server 2003 MCP
Managed Microsoft hosting:
www.orcsweb.com