Home > Uncategorized > Create ORM using Reflection C#

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..

 

Advertisements
Categories: Uncategorized
  1. El Pastor
    December 5, 2013 at 3:08 pm

    Do you have an example of the delete method? or a project to download?

  2. La Oveja
    September 23, 2015 at 3:55 pm

    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;
    }

    }
    }

  3. La Oveja
    September 24, 2015 at 2:31 pm

    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; }
    }

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: