C#: Bosquejo de un ActiveRecord con Insert y Select

Aburrido en una noche de primavera comence a escribir un pequeño programa que era el bosquejo básico pero bien básico un ActiveRecord llegando sólo a implementar las funciones Insert y Select, aun tengo pendiente las funciones Update y Delete.

Este código se conecta a un base de datos Postgres a la tabla test con los campos id, nombre, apellidos y edad, primero inserta y luego lista el dato ingresado.

Antes de probar el código, deben crear la tabla y las columnas en minuscula.

Program.cs

using System;
using System.Collections.Generic; 

namespace MiActiveRecord
{
 public class Program
 {
 static void Main(string[] args)
 {
 Test test = new Test() {Nombre="Alexis", Apellidos="Nuñez Riquelme"}; 

 test.Save(); 

 IList<Test> tests = Test.GetAll(); 

 foreach (Test item in tests)
 {
 Console.WriteLine("{0,2} - {1, 10} - {2, 15} - {3, 2}", item.Id, item.Nombre, item.Apellidos, item.Edad);
 }
 } 

 public class Test : Base<Test>
 {
 public string Nombre { get ; set; } 

 public string Apellidos { get; set; } 

 public int? Edad { get; set; }
 }
 }
}

Base.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Reflection;
using System.Text;
using Npgsql; 

namespace MiActiveRecord
{
 public class Base<T> : BaseSQL<T>
 {
 public long Id { get; set; } 

 public static IList<T> GetAll()
 {
 IList<T> lst = new List<T>(); 

 using (NpgsqlConnection sqlCnn = new NpgsqlConnection("User ID=postgres; Password=postgres; Host=localhost; Port=5432; Database=MiActiveRecord"))
 {
 sqlCnn.Open(); 

 StringBuilder sql = GetSelect(); 

 NpgsqlDataReader reader = new NpgsqlCommand(sql.ToString(), sqlCnn).ExecuteReader(); 

 while (reader.Read())
 {
 T item = (T)typeof(T).Assembly.CreateInstance(typeof(T).FullName); 

 (item as Base<T>).LoadValues(reader); 

 lst.Add(item);
 } 

 reader.Close();
 sqlCnn.Close();
 } 

 return lst;
 } 

 public void LoadValues(NpgsqlDataReader reader)
 {
 for (int i = 0; i < reader.FieldCount; i++)
 {
 foreach (PropertyInfo property in (this).GetType().GetProperties())
 {
 if (property.Name.ToUpper() == reader.GetName(i).ToUpper())
 {
 if (reader[reader.GetName(i).ToUpper()] is DBNull)
 property.SetValue(this, null, null);
 else
 property.SetValue(this, reader[reader.GetName(i).ToUpper()], null); 

 break;
 }
 }
 }
 } 

 public void Save()
 {
 using (NpgsqlConnection sqlCnn = new NpgsqlConnection("User ID=postgres; Password=postgres; Host=localhost; Port=5432; Database=MiActiveRecord"))
 {
 StringBuilder strSQL = GetInsert(); 

 sqlCnn.Open(); 

 NpgsqlCommand sqlcmd = new NpgsqlCommand(strSQL.ToString(), sqlCnn); 

 sqlcmd.ExecuteNonQuery(); 

 sqlCnn.Close();
 }
 }
 }
}

BaseSQL.cs

using System.Text;
using System.Reflection;
using System.Collections.Generic;
using System; 

namespace MiActiveRecord
{
 public class BaseSQL<T>
 {
 internal StringBuilder GetInsert()
 {
 StringBuilder sql = new StringBuilder("INSERT INTO ")
 .AppendFormat("{0} (", this.GetType().Name); 

 foreach (PropertyInfo property in Base<T>.GetProperties(this.GetType(), true))
 {
 if (property.GetValue(this, null) != null)
 sql.AppendFormat("{0}, ", property.Name);
 } 

 sql.Remove(sql.Length - 2, 2); 

 sql.Append(") VALUES ("); 

 foreach (PropertyInfo property in Base<T>.GetProperties(this.GetType(), true))
 {
 if (property.GetValue(this, null) != null)
 {
 if (property.PropertyType == typeof(string) || property.PropertyType == typeof(char))
 sql.AppendFormat("'{0}', ", property.GetValue(this, null));
 else
 sql.AppendFormat("{0}, ", property.GetValue(this, null));
 }
 } 

 sql.Remove(sql.Length - 2, 2); 

 sql.Append(")"); 

 return sql;
 } 

 internal static StringBuilder GetSelect()
 {
 StringBuilder sql = new StringBuilder("SELECT "); 

 foreach (PropertyInfo property in Base<T>.GetProperties(typeof(T), false))
 {
 sql.AppendFormat("{0}, ", property.Name);
 } 

 sql.Remove(sql.Length - 2, 2); 

 sql.AppendFormat(" FROM {0}", typeof(T).Name);
 return sql;
 } 

 private static IList<PropertyInfo> GetProperties(Type type, bool skipBase)
 {
 IList<PropertyInfo> properties = new List<PropertyInfo>(); 

 bool exist; 

 foreach (PropertyInfo proItem in type.GetProperties())
 {
 exist = false; 

 if (skipBase)
 {
 foreach (PropertyInfo proBase in typeof(Base<T>).GetProperties())
 {
 if (proBase.Name == proItem.Name)
 {
 exist = true;
 break;
 }
 }
 } 

 if (!exist)
 properties.Add(proItem);
 } 

 return properties;
 }
 }
}

~ por nitrogeno en 11 Junio 2009.