Network Programming in .NET

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

}
}