C# - Build Your Own ORM - Part 4 - Insert
Following on from the previous post of my series "Build Your Own ORM", we will be adding an "insert" function that, when given any object, will create an appropriate SQL Insert command and insert the object's data into the appropriate table.
Some Changes First
Before we begin let's go over a couple of refactorings that will make the existing code easier to work with. We will be adding two functions. One to create comma delimited strings and the second to build an OdbcCommand object along with associate SQL parameters.
The Append_Str function takes a string with potentially existing comma delimited data like "one,two,three" and appends a given string inserting a comma if appropriate. Alternative approaches are available if you don't mind additional code dependencies and/or want something faster or more efficient.
1: public static string Append_Str(string orig_str, string new_str)
2: {
3: string res = orig_str;
4:
5: if (!string.IsNullOrEmpty(new_str))
6: {
7: if (!string.IsNullOrEmpty(orig_str))
8: res = orig_str + ", " + new_str;
9: else
10: res = new_str;
11: }
12: return res;
13: }
With the second function, Build_Command, we have moved code from the Select function that creates an OdbcCommand object to the new function. The new function simplifies the process of iterating through the parameters of a variable parameter function for the purpose of creating equivalent SQL parameter objects. This new function is then utilised by the existing Select function and the new Insert function.
1: public static System.Data.Odbc.OdbcCommand Build_Command(System.Data.Odbc.OdbcConnection conn, string sql, params object[] param_vals)
2: {
3: System.Data.Odbc.OdbcCommand cmd = null;
4: int c;
5:
6: cmd = new System.Data.Odbc.OdbcCommand(sql, conn);
7:
8: // cycle through function parameters and insert as sql command parameters
9: if (param_vals != null && param_vals.Length > 0)
10: {
11: for (c = 0; c < param_vals.Length; c++)
12: {
13: cmd.Parameters.AddWithValue(c.ToString(), param_vals[c]);
14: }
15: }
16: return cmd;
17: }
The Insert Function
With that out of the way, we can now have a look at the new Insert function. For the purposes of this simple demonstration, I have made various assumptions. Our assumptions are that an object's type name must be the same as its corresponding table name, object field names must match table column names, and the generated SQL insert statement is for MS SQL Server only (see diagram). If we wanted to build a general purpose ORM, these assumptions would be "deal breakers". Fortunately we are not. In fact, the concept of "Convention over Configuration" might view our assumptions as a good thing. In the end, it's up to you to decide, implement, and extend as your case requires.
The algorithm itself, is fairly straight forward. The function is passed an object that we wish to save to the database. We iterate through the various public fields of the object, and for each field, we extract and record its name and data. This is then used to construct an SQL statement that will also return the new "id" of the record inserted into the database. The final step executes the query and writes the new "id" value back into the given object.
1: public static void Insert(System.Data.Odbc.OdbcConnection conn, object obj)
2: {
3: System.Type obj_type;
4: string field_names=null, param_placeholders=null, sql;
5: System.Collections.ArrayList param_vals;
6: System.Data.Odbc.OdbcCommand cmd;
7: object id;
8:
9: obj_type = obj.GetType();
10: param_vals = new System.Collections.ArrayList();
11:
12: // cycle through all public fields of the given object
13: foreach (System.Reflection.FieldInfo obj_field in obj_type.GetFields())
14: {
15: // ignore id field and assume it's an auto-generated key column
16: if (obj_field.Name!="id")
17: {
18: // include object field name as table column name
19: field_names = Append_Str(field_names, obj_field.Name);
20:
21: // add parameter placeholder
22: param_placeholders = Append_Str(param_placeholders, "?");
23:
24: // record object field value to insert as table column value
25: param_vals.Add(obj_field.GetValue(obj));
26: }
27: }
28:
29: // build "insert" sql command
30: sql = "insert into " + obj_type.Name +
31: " (" + field_names + ") output inserted.id values (" + param_placeholders + ")";
32:
33: // execute sql and place new key id value into given object
34: cmd = Build_Command(conn, sql, param_vals.ToArray());
35: id = cmd.ExecuteScalar();
36: if (id!=null)
37: obj_type.GetField("id").SetValue(obj, id);
38: }
Following is some code demonstrating how the Insert function would be used and the complete ORM code so far can be found at the end of the post.
1: System.Data.Odbc.OdbcConnection conn;
2: job new_job;
3:
4: conn = new System.Data.Odbc.OdbcConnection("Driver={SQL Server Native Client 11.0}; Server=(LocalDB)\\v11.0; Database=JobBuddy.Db; Trusted_Connection=Yes;");
5: conn.Open();
6:
7: new_job = new job();
8: new_job.title = "Android Team Leader";
9: new_job.application_date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
10: new_job.agent_id = 2;
11:
12: orm.Insert(conn, new_job);
13:
14: conn.Close();
Improvements
Now let's look at the various options for extending the functionality provided. Our first assumption was that an object's type name and field names had to match the corresponding table and columns names. If we wanted to explicitly define these mappings, we could do so via additional function parameters or configuration files (great for those of you obsessed with XML). My favourite technique is via the use of attributes.
The second assumption was that the generated SQL was for MS SQL Server only. If we wanted our ORM to operate on a particular set of databases we could modify the generated SQL based on an additional function parameter or by studying the type of the given connection. My preferred approach is to abstract the relevant functionality and implement it in corresponding child classes that represent each database type.
In the next post I will go over an Update function that will save an object's data to an existing record. No points for guessing that the algorithm will not differ much from that of the Insert function.
Complete Code So Far
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: }
