C# - Build Your Own ORM - Part 5 - Update and Delete
In the previous post, of this series, I provided a simple function to Insert new objects into a table. In this, the final post, I will be providing functions to Update and Delete objects along with ideas on ways to extend and improve on the existing code. Recall that my objective is to demonstrate that one does not require much code to duplicate the basic functionality of a typical ORM and thus, one should seriously consider building their own.
The Update Function
The Update function's algorithm is, as expected, similar to that of the Insert function in the previous post. The function cycles through all the public fields of the given object and compiles the associated values into a list taking care to isolate the object's "id" as it must be added to the end of the list. This is because the "id" will be applied to the resulting SQL as the last parameter. The function then builds the requisite SQL and executes it.
As with the Insert function, there are a number of assumptions made about the object-relational mapping. The "id" field is expected to represent a single unique key column in the corresponding table. The object type's name must match a table name. And finally, the object field names must match table column names.
1: public static int Update(System.Data.Odbc.OdbcConnection conn, object obj)
2: {
3: string sql, field_names = null;
4: System.Type obj_type;
5: System.Collections.ArrayList param_vals;
6: object id=null;
7: int res;
8: System.Data.Odbc.OdbcCommand cmd;
9:
10: obj_type = obj.GetType();
11: param_vals = new System.Collections.ArrayList();
12:
13: // iterate through all public fields of the given object
14: foreach (System.Reflection.FieldInfo obj_field in obj_type.GetFields())
15: {
16: // if the current field is not the "id" field
17: if (obj_field.Name != "id")
18: {
19: // record the field's name and value
20: field_names = Append_Str(field_names, obj_field.Name + "=?");
21: param_vals.Add(obj_field.GetValue(obj));
22: }
23: // otherwise record the id's value
24: else
25: id = obj_field.GetValue(obj);
26: }
27: // since the id is the last parameter in the sql query, we add it to the end of the parameter list
28: param_vals.Add(id);
29:
30: // build the necessary update query
31: sql = "update " + obj_type.Name + " set " + field_names + " where id=?";
32:
33: // and execute
34: cmd = Build_Command(conn, sql, param_vals.ToArray());
35: res = cmd.ExecuteNonQuery();
36:
37: return res;
38: }
The Delete Function
The Delete function is very simple. It extracts the "id" from the given object and uses it to build the necessary SQL.
1: public static void Delete (System.Data.Odbc.OdbcConnection conn, object obj)
2: {
3: string sql;
4: System.Type obj_type;
5: System.Data.Odbc.OdbcCommand cmd;
6: object id=null;
7:
8: obj_type = obj.GetType();
9:
10: // iterate through all public fields of the given object to record it's "id"
11: foreach (System.Reflection.FieldInfo field in obj_type.GetFields())
12: if (field.Name == "id")
13: id = field.GetValue(obj);
14:
15: // consruct and execute the necessary sql
16: sql = "delete from " + obj_type.Name + " where id=?";
17: cmd = Build_Command(conn, sql, id);
18: cmd.ExecuteNonQuery();
19: }
Usage
Following is some sample code illustrating the expected usage of these functions.1: System.Data.Odbc.OdbcConnection conn;
2: job new_job;
3:
4: conn = new System.Data.Odbc.OdbcConnection("...");
5: conn.Open();
6:
7: job = new job();
8: job.id = 2;
9: job.title = "Android Team Leader";
10: job.application_date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
11: job.agent_id = 2;
12:
13: orm.Update(conn, job);
14: orm.Delete(conn, job);
15:
16: conn.Close();
Improvements
I've already mentioned possible improvements such as attribute or XML based configuration options along with additional parameters to the various functions. By now though, you might have noticed the repeating occurrence of the OdbcConnection parameter in most functions. This suggests that we could internalise it within the "orm" class and stop declaring its member functions as "static". Along with a shift from ODBC specific classes to their base classes, this would allow us to create child classes representing various database types, such MySQL, MS SQL Server, or Oracle. The child classes could then provide their own SQL implementations.
Another suggested improvement is the addition of functions that return a single object, return a single value, and return a list representing a single column. All these functions can be easily implemented with the existing code.
The Complete Code
1: public class orm
2: {
3: public static void Populate_Object(object obj, System.Data.Odbc.OdbcDataReader data)
4: {
5: int c;
6: string col_name;
7: object col_val;
8: System.Reflection.FieldInfo obj_field;
9: System.Type obj_type;
10:
11: if (obj!=null && data!=null)
12: {
13: obj_type=obj.GetType();
14:
15: // iterate through the datareader columns and look for matching object fields
16: for (c = 0; c < data.FieldCount; c++)
17: {
18: col_name = data.GetName(c);
19:
20: // look for an object field with the same name as the current column
21: obj_field = obj_type.GetField(col_name);
22: if (obj_field != null)
23: {
24: // take the actual value for the current column and...
25: col_val = data.GetValue(c);
26:
27: // write it into the matching object field
28: obj_field.SetValue(obj, col_val);
29: }
30: }
31: }
32: }
33:
34: public static System.Collections.Generic.List<T> Select<T>(System.Data.Odbc.OdbcConnection conn, string sql, params object[] param_vals) where T : new()
35: {
36: System.Collections.Generic.List<T> res = null;
37: T obj;
38: System.Data.Odbc.OdbcDataReader data;
39: System.Data.Odbc.OdbcCommand cmd;
40:
41: // prepare sql query and add parameter values if necessary
42: cmd = Build_Command(conn, sql, param_vals);
43:
44: // execute query and convert result into an object list
45: data = cmd.ExecuteReader();
46: if (data.HasRows)
47: {
48: res = new System.Collections.Generic.List<T>();
49: while (data.Read())
50: {
51: obj = new T();
52: Populate_Object(obj, data);
53:
54: res.Add(obj);
55: }
56: }
57: data.Close();
58:
59: return res;
60: }
61:
62: public static System.Data.Odbc.OdbcCommand Build_Command(System.Data.Odbc.OdbcConnection conn, string sql, params object[] param_vals)
63: {
64: System.Data.Odbc.OdbcCommand cmd = null;
65: int c;
66:
67: cmd = new System.Data.Odbc.OdbcCommand(sql, conn);
68:
69: // cycle through function parameters and insert as sql command parameters
70: if (param_vals != null && param_vals.Length > 0)
71: {
72: for (c = 0; c < param_vals.Length; c++)
73: {
74: cmd.Parameters.AddWithValue(c.ToString(), param_vals[c]);
75: }
76: }
77: return cmd;
78: }
79:
80: public static string Append_Str(string orig_str, string new_str)
81: {
82: string res = orig_str;
83:
84: if (!string.IsNullOrEmpty(new_str))
85: {
86: if (!string.IsNullOrEmpty(orig_str))
87: res = orig_str + ", " + new_str;
88: else
89: res = new_str;
90: }
91: return res;
92: }
93:
94: public static void Insert(System.Data.Odbc.OdbcConnection conn, object obj)
95: {
96: System.Type obj_type;
97: string field_names=null, param_placeholders=null, sql;
98: System.Collections.ArrayList param_vals;
99: System.Data.Odbc.OdbcCommand cmd;
100: object id;
101:
102: obj_type = obj.GetType();
103: param_vals = new System.Collections.ArrayList();
104:
105: // cycle through all public fields of the given object
106: foreach (System.Reflection.FieldInfo obj_field in obj_type.GetFields())
107: {
108: // ignore id field and assume it's an auto-generated key column
109: if (obj_field.Name!="id")
110: {
111: // include object field name as table column name
112: field_names = Append_Str(field_names, obj_field.Name);
113:
114: // add parameter placeholder
115: param_placeholders = Append_Str(param_placeholders, "?");
116:
117: // record object field value to insert as table column value
118: param_vals.Add(obj_field.GetValue(obj));
119: }
120: }
121:
122: // build "insert" sql command
123: sql = "insert into " + obj_type.Name +
124: " (" + field_names + ") output inserted.id values (" + param_placeholders + ")";
125:
126: // execute sql and place new key id value into given object
127: cmd = Build_Command(conn, sql, param_vals.ToArray());
128: id = cmd.ExecuteScalar();
129: if (id!=null)
130: obj_type.GetField("id").SetValue(obj, id);
131: }
132:
133: public static int Update(System.Data.Odbc.OdbcConnection conn, object obj)
134: {
135: string sql, field_names = null;
136: System.Type obj_type;
137: System.Collections.ArrayList param_vals;
138: object id=null;
139: int res;
140: System.Data.Odbc.OdbcCommand cmd;
141:
142: obj_type = obj.GetType();
143: param_vals = new System.Collections.ArrayList();
144:
145: // iterate through all public fields of the given object
146: foreach (System.Reflection.FieldInfo obj_field in obj_type.GetFields())
147: {
148: // if the current field is not the "id" field
149: if (obj_field.Name != "id")
150: {
151: // record the field's name and value
152: field_names = Append_Str(field_names, obj_field.Name + "=?");
153: param_vals.Add(obj_field.GetValue(obj));
154: }
155: // otherwise record the id's value
156: else
157: id = obj_field.GetValue(obj);
158: }
159: // since the id is the last parameter in the sql query, we add it to the end of the parameter list
160: param_vals.Add(id);
161:
162: // build the necessary update query
163: sql = "update " + obj_type.Name + " set " + field_names + " where id=?";
164:
165: // and execute
166: cmd = Build_Command(conn, sql, param_vals.ToArray());
167: res = cmd.ExecuteNonQuery();
168:
169: return res;
170: }
171:
172: public static void Delete (System.Data.Odbc.OdbcConnection conn, object obj)
173: {
174: string sql;
175: System.Type obj_type;
176: System.Data.Odbc.OdbcCommand cmd;
177: object id=null;
178:
179: obj_type = obj.GetType();
180:
181: // iterate through all public fields of the given object to record it's "id"
182: foreach (System.Reflection.FieldInfo field in obj_type.GetFields())
183: if (field.Name == "id")
184: id = field.GetValue(obj);
185:
186: // consruct and execute the necessary sql
187: sql = "delete from " + obj_type.Name + " where id=?";
188: cmd = Build_Command(conn, sql, id);
189: cmd.ExecuteNonQuery();
190: }
191: }
