Create ORM using Reflection C#
Here is a simple bit of code that can create an ORM (Object Relational Mapping) using reflection on the types being inserted or updated. It’s still a work in progress, since it assumes a flat object structure, each field being stored as a varchar(1000) and field names in the database must exactly match that in the object. I’ve based this on MySQL, but by simply changing “Limit 1” to “top 1”, that should be MSSQL sorted too.
using System;
using System.Reflection;
using MySql.Data.MySqlClient;/// <summary>
/// Creates an object / relational mapping using reflection
/// </summary>
public class ReflectiveORM
{
private static string sqlConnection = “Server=…;Database=…;Uid=…;Pwd=…;”;public static void InsertOrUpdateObjectInDB<T>(T o, string table, string uniqueField)
{
if (CheckIfAlreadyExists(o, table, uniqueField))
{
updateObjectInDB(o, table, uniqueField);
}
else
{
insertObjectIntoDB(o, table);
}
}public static bool CheckIfAlreadyExists<T>(T o, string table, string uniqueField)
{
PropertyInfo piUnique = typeof(T).GetProperty(uniqueField);
string strSQL = “select ” + uniqueField + ” from ” + table + ” where “;
strSQL += uniqueField + “='” + piUnique.GetValue(o, null).ToString().Replace(“‘”, “””) + “‘”;
strSQL += ” limit 1″;
string strReadBack = getSingleValue(strSQL, uniqueField);
if (string.IsNullOrEmpty(strReadBack))
{
// No record in db, do insert.
return false;
}
// record in db, do update
return true;
}public static void updateObjectInDB<T>(T o, string table, string uniqueField)
{
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
string strSql = “update ” + table + ” set “;
string strSeperator = “”;
foreach (PropertyInfo field in fields)
{
string strValue = field.GetValue(o, null).ToString();
strSql += strSeperator + field.Name;
strSql += ” = ‘” + strValue.Replace(“‘”, “””) + “‘”;
strSeperator = “,”;
}
PropertyInfo piUnique = typeof(T).GetProperty(uniqueField);
string strUnique = piUnique.GetValue(o, null).ToString();
strUnique = strUnique.Replace(“‘”, “””);
strSql += ” where ” + uniqueField + “='” + strUnique + “‘”;
ExecuteNonQuery(strSql);
}public static void insertObjectIntoDB<T>(T o, string table)
{
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
string strSql = “insert into ” + table + ” (“;
string strSeperator = “”;
foreach (PropertyInfo field in fields)
{
strSql += strSeperator + field.Name;
strSeperator = “,”;
}
strSql += “) values (“;
strSeperator = “”;
foreach (PropertyInfo field in fields)
{
string strValue = field.GetValue(o, null).ToString();
strSql += strSeperator + “‘” + strValue.Replace(“‘”, “””) + “‘”;
strSeperator = “,”;
}
strSql += “);”;
ExecuteNonQuery(strSql);
}/// <summary>
/// Executes a query that returns no data
/// </summary>
/// <param name=”sql”>The sql statement to execute</param>
public static void ExecuteNonQuery(string sql)
{
using (var conn = new MySqlConnection(sqlConnection))
{
var cmd = new MySqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
return;
}public static string getSingleValue(string sql, string field)
{
string returnVal = null;
MySqlConnection DSN = new MySqlConnection(sqlConnection);
DSN.Open();
MySqlCommand Database = new MySqlCommand(sql, DSN);
MySqlDataReader dr = Database.ExecuteReader();
if (dr.Read())
{
returnVal = dr[field].ToString();
}
DSN.Close();
return returnVal;
}}
To use it, set the connection string in this class, then call
ReflectiveORM.InsertOrUpdateObjectInDB(product, “product”, “Code”);
The table “product” must be set up in advance..
Do you have an example of the delete method? or a project to download?
LikeLike
If someone is interrested some littles changes…
// Create
ReflectiveORM.InsertOrUpdateObjectInDB(new Car() { Name=”Olds”, Power=10, Cost=0.78M });
// Select.
var cars = ReflectiveORM.GetObjectsFromDB(“Name”, “Olds”);
// Delete
ReflectiveORM.DeleteObjectIntoDB(new Car() { Name = “Olds” }, “Name”);
namespace ORMConsoleApplication
{
using System;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
///
/// Creates an object / relational mapping using reflection
/// https://blog.dotnetframework.org/2011/03/27/create-orm-using-reflection-c/
///
public class ReflectiveORM
{
private static string sqlConnection = “data source=.;initial catalog=OverMars;integrated security=SSPI;MultipleActiveResultSets=true;”;
public static void InsertOrUpdateObjectInDB(T o)
{
if (CheckIfAlreadyExists(o))
{
UpdateObjectInDB(o);
}
else
{
InsertObjectIntoDB(o);
}
}
public static List GetObjectsFromDB(string criteria)
{
PropertyInfo primaryKey = GetPrimaryKey();
return GetObjectsFromDB(primaryKey, criteria);
}
public static List GetObjectsFromDB(string field, string criteria) {
return GetObjectsFromDB(GetField(field), criteria);
}
private static List GetObjectsFromDB(PropertyInfo field, string criteria)
{
List objects = null;
string table = GetTableName();
PropertyInfo piUnique = field;
string strSQL = “select * from ” + table + ” where “;
strSQL += field.Name + “=” + FormatForSQLQuery(criteria, field);
objects = GetValues(strSQL);
return objects;
}
private static bool CheckIfAlreadyExists(T o)
{
string table = GetTableName();
string uniqueField = GetPrimaryKey().Name;
PropertyInfo piUnique = typeof(T).GetProperty(uniqueField);
string strSQL = “select ” + uniqueField + ” from ” + table + ” where “;
strSQL += uniqueField + “=” + FormatForSQLQuery(piUnique.GetValue(o, null).ToString(), piUnique);
string strReadBack = GetSingleValue(strSQL, uniqueField);
if (string.IsNullOrEmpty(strReadBack))
{
// No record in db, do insert.
return false;
}
// record in db, do update
return true;
}
public static void UpdateObjectInDB(T o)
{
string table = GetTableName();
string uniqueField = GetPrimaryKey().Name;
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
string strSql = “update ” + table + ” set “;
string strSeperator = “”;
foreach (PropertyInfo field in fields)
{
if (IsPrimaryKeyWithIdentity(field)) continue; // No update for indentity primary key
string strValue = field.GetValue(o, null).ToString();
strSql += strSeperator + field.Name;
strSql += ” = ” + FormatForSQLQuery(strValue, field);
strSeperator = “,”;
}
PropertyInfo piUnique = typeof(T).GetProperty(uniqueField);
string strUnique = piUnique.GetValue(o, null).ToString();
strUnique = strUnique.Replace(“‘”, “\””);
strSql += ” where ” + uniqueField + “='” + strUnique + “‘”;
ExecuteNonQuery(strSql);
}
public static void InsertObjectIntoDB(T o)
{
string table = GetTableName();
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
string strSql = “insert into ” + table + ” (“;
string strSeperator = “”;
foreach (PropertyInfo field in fields)
{
if (IsPrimaryKeyWithIdentity(field)) continue; // No insert for indentity primary key
strSql += strSeperator + field.Name;
strSeperator = “,”;
}
strSql += “) values (“;
strSeperator = “”;
foreach (PropertyInfo field in fields)
{
if (IsPrimaryKeyWithIdentity(field)) continue; // No insert for indentity primary key
string strValue = field.GetValue(o, null).ToString();
strSql += strSeperator + FormatForSQLQuery(strValue, field);
strSeperator = “,”;
}
strSql += “);”;
ExecuteNonQuery(strSql);
}
public static void DeleteObjectIntoDB(T o)
{
PropertyInfo primaryKey = GetPrimaryKey();
DeleteObjectIntoDB(o, primaryKey);
}
public static void DeleteObjectIntoDB(T o, string field)
{
DeleteObjectIntoDB(o, GetField(field));
}
private static void DeleteObjectIntoDB(T o, PropertyInfo field)
{
string table = GetTableName();
string strSql = “delete from ” + table + ” where “;
strSql += field.Name + ” = ” + FormatForSQLQuery(field.GetValue(o, null).ToString(), field);
ExecuteNonQuery(strSql);
}
private static PropertyInfo[] GetFields()
{
PropertyInfo[] fields = null;
fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
return fields;
}
private static PropertyInfo GetField(string name)
{
PropertyInfo field = null;
field = typeof(T).GetProperty(name);
return (PropertyInfo)field;
}
private static PropertyInfo GetPrimaryKey()
{
PropertyInfo primaryKey = null;
foreach (PropertyInfo field in GetFields())
{
Attribute customAttribute = field.GetCustomAttribute(typeof(PrimaryKeyAttribute));
if (customAttribute != null)
{
primaryKey = field;
break;
}
}
return primaryKey;
}
private static string GetTableName()
{
string tableName = string.Empty;
Attribute tableAttribute = typeof(T).GetCustomAttribute(typeof(TableAttribute));
if (tableAttribute != null)
{
tableName = ((TableAttribute)tableAttribute).Name;
}
return tableName;
}
///
/// Check if a field has an identity.
///
///
///
private static bool IsPrimaryKeyWithIdentity(PropertyInfo field)
{
bool isPrimaryKeyWithIdentity = false;
Attribute customAttribute = field.GetCustomAttribute(typeof(PrimaryKeyAttribute));
if (
customAttribute != null &&
customAttribute.GetType() == typeof(PrimaryKeyAttribute) &&
((PrimaryKeyAttribute)customAttribute).IsIdentity)
{
isPrimaryKeyWithIdentity = true;
}
return isPrimaryKeyWithIdentity;
}
private static bool IsPrimaryKey(PropertyInfo field)
{
bool isPrimaryKey = false;
Attribute customAttribute = field.GetCustomAttribute(typeof(PrimaryKeyAttribute));
if (
customAttribute != null &&
customAttribute.GetType() == typeof(PrimaryKeyAttribute) )
{
isPrimaryKey = true;
}
return isPrimaryKey;
}
///
/// Check if a field has a string type.
///
///
///
private static bool IsStringType(PropertyInfo field)
{
bool isStringType = false;
if (field.PropertyType == typeof(System.String))
{
isStringType = true;
}
return isStringType;
}
private static bool IsDecimalType(PropertyInfo field)
{
bool isDecimalType = false;
if (field.PropertyType == typeof(System.Decimal))
{
isDecimalType = true;
}
return isDecimalType;
}
private static string FormatForSQLQuery(string value, PropertyInfo field)
{
string formatForSQLQuery = string.Empty;
if (IsStringType(field))
{
formatForSQLQuery = “‘” + value.Replace(“‘”, “\””) + “‘”;
}
else if (IsDecimalType(field))
{
formatForSQLQuery = value.Replace(“,”, “.”);
}
else
{
formatForSQLQuery = value;
}
return formatForSQLQuery;
}
///
/// Executes a query that returns no data
///
/// The sql statement to execute
public static void ExecuteNonQuery(string sql)
{
using (var conn = new SqlConnection(sqlConnection))
{
var cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
return;
}
public static string GetSingleValue(string sql, string field)
{
string returnVal = null;
SqlDataReader dr = GetSqlDataReader(sql);
if (dr.Read())
{
returnVal = dr[field].ToString();
}
return returnVal;
}
public static List GetValues(string sql)
{
List values = new List();
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
SqlDataReader dr = GetSqlDataReader(sql);
if (dr.Read())
{
Dictionary args = new Dictionary();
for (int i = 0; i < fields.Length; i++)
{
args.Add(fields[i].Name, dr[fields[i].Name]);
}
values.Add((T)Activator.CreateInstance(typeof(T), args));
}
return values;
}
private static SqlDataReader GetSqlDataReader(string sql)
{
SqlConnection DSN = new SqlConnection(sqlConnection);
DSN.Open();
SqlCommand Database = new SqlCommand(sql, DSN);
SqlDataReader dr = Database.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
}
LikeLike
I have forgotten it
[TableAttribute(“Car”)]
public class Car
{
public Car()
{
}
public Car(string name, int power, decimal cost)
: this(0, name, power, cost)
{
}
public Car(int carID, string name, int power, decimal cost)
{
CarID = carID;
Name = name;
Power = power;
Cost = cost;
}
public Car(Dictionary args)
: this(int.Parse(args[“CarID”].ToString()), args[“Name”].ToString(), int.Parse(args[“Power”].ToString()), decimal.Parse(args[“Cost”].ToString()))
{
}
[PrimaryKeyAttribute(true)]
public int CarID { get; set; }
public string Name { get; set; }
public int Power { get; set; }
public decimal Cost { get; set; }
}
and
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : System.Attribute
{
public TableAttribute()
{
}
public TableAttribute(string name)
{
this.Name = name;
}
public string Name { get; set; }
}
LikeLike