C#: Bosquejo de un ActiveRecord con Insert y Select
11/06/2009
Deja un comentario
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;
}
}
}
Categorías:C#, Generico, Programación, Reflection
