It has been rumored that ORM killed the applicative DBA star in all small-medium .NET + MSSQL information system companies.
I disagree, you still need us to tune whatever madness Entity Framework\ LINQ to SQL\ NHibernate generates.
But if you have a small-medium database and most of your logic resides rightfully in the code, then our real competition is the variety of Micro ORMs that provide the programmers with actual control over the commands executed on our precious DB.
I recommend Dapper or the ironically named Massive.
Peta Poco or Simple Data fell short(er).
The most common use I’ve seen for ORM is for data viewing systems, which makes no sense!
The data schema changes frequently, you need to be able to match different data returns to the same class, you don’t even use the ORM for data update because you need to apply logic and algorithms.
Then why???
Answer – laziness! We all share that illness. We’ll use an overkill solution just to save time\effort.
Cure – write your own! And only implement what you actually need. It will save you so much time later solving performance issues and bottle necks and breaking changes to schema.
Start with our skinny solution!
Maps a stored procedure result row\table to a single class or a class list.
Name your class members with attributes or be lazy and just use member names.
Provide a method to activate after conversion to class completes or don’t.
And save time and money tuning some DAL layer you didn’t even write.
And go …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Reflection; namespace TechedUpORM { [System.AttributeUsage(AttributeTargets.All, Inherited = false, AllowMultiple = true)] public sealed class DBNameAttribute : Attribute { readonly string dbname; public DBNameAttribute(string DBName) { this.dbname = DBName; } public string DBName { get { return dbname; } } } public static class OneWayORM { /// <summary> /// Generate a single class instance from a sinlge data row /// </summary> /// <typeparam name="T">class type</typeparam> /// <param name="Conn">connection string</param> /// <param name="Stp">stored procedure name</param> /// <param name="DBCommand">command type</param> /// <param name="Args">stp params</param> /// <param name="Method">function to activate after conversion</param> /// <returns></returns> public static T GetClassInstance<T>(string Conn, string CommandText, CommandType DBCommand, SqlParameter[] Args, Action<T, IDictionary<string, object>> Method = null) { IDictionary<string, object> dicData = new Dictionary<string, object>(); using (DbCommand cmd = CreateCommand(Conn, DBCommand, CommandText, Args)) { using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { for (var i = 0; i < rdr.FieldCount; i++) { var v = rdr.GetValue(i); dicData.Add(rdr.GetName(i), DBNull.Value.Equals(v) ? null : v); } } } } return ConvertToClass<T>(dicData, Method); } /// <summary> /// Generate a list of class objects from a sinlge data table /// </summary> /// <typeparam name="T">class type</typeparam> /// <param name="Conn">connection string</param> /// <param name="Stp">stored procedure name</param> /// <param name="DBCommand">command type</param> /// <param name="Args">stp params</param> /// <param name="Method">function to activate after conversion</param> /// <returns></returns> public static List<T> GetClassList<T>(string Conn, string Stp, CommandType DBCommand, SqlParameter[] Args, Action<T, IDictionary<string, object>> Method = null) { DataTable dt; using (SqlCommand cmd = CreateCommand(Conn, DBCommand, Stp, Args)) { DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); dt = ds.Tables[0]; } List<T> res = new List<T>(); foreach (DataRow dr in dt.Rows) { IDictionary<string, object> dictRow = new Dictionary<string, object>(); foreach (DataColumn item in dt.Columns) { dictRow.Add(item.ColumnName, dr[item] == DBNull.Value ? null : dr[item]); } res.Add(ConvertToClass<T>(dictRow, Method)); } return res; } private static SqlCommand CreateCommand(string Conn, CommandType CommandType, string DBCommand, SqlParameter[] Args) { SqlConnection cn = new SqlConnection(Conn); SqlCommand cmd = cn.CreateCommand(); cmd.CommandText = DBCommand; cmd.CommandType = CommandType; if (Args != null) { foreach (var item in Args) { cmd.Parameters.Add(item); } } cmd.Connection.Open(); return cmd; } private static T ConvertToClass<T>(IDictionary<string, object> dicData, Action<T, IDictionary<string, object>> Method) { Type dbNameAtt = typeof(DBNameAttribute); Type classType = typeof(T); var obj = Activator.CreateInstance(classType); foreach (PropertyInfo propertyInfo in classType.GetProperties(BindingFlags.Instance | BindingFlags.Public)) { object[] customrAtts = propertyInfo.GetCustomAttributes(dbNameAtt, true); if (customrAtts != null && customrAtts.Length == 1) { DBNameAttribute attribute = (DBNameAttribute)(customrAtts[0]); if (dicData.ContainsKey(attribute.DBName)) { propertyInfo.SetValue(obj, dicData[attribute.DBName]); } } else { if (dicData.ContainsKey(propertyInfo.Name)) { propertyInfo.SetValue(obj, dicData[propertyInfo.Name]); } } } if (Method != null) { Method((T)obj, dicData); } return (T)obj; } } } |