public void InsertRelationMany(System.Collections.IList entityMany, T t, EntityMany attr, bool del)
{
try
{
SqlCommand command = new SqlCommand();
var queryDel = (del ? "DELETE FROM #TABLE# WHERE #WHERE#" : "");;
var queryIns = "INSERT INTO #TABLE# (#COLUMNS#) VALUES #PARAMETERS#";
var queryParam = "";
if (entityMany == null)
{
throw new Exception("La entidad de relación: " + attr.TableRela + " no puede ser nulla, debe contener una entidad vacía.");
}
foreach (var entity in entityMany)
{
queryParam += "(" + t.GetType().GetProperty(attr.Field1).GetValue(t) + "," + entity.GetType().GetProperty(attr.Field2).GetValue(entity) + "),";
}
//Borro todos los datos
queryDel = queryDel
.Replace("#TABLE#", attr.TableMany)
.Replace("#WHERE#", attr.Field1 + " = " + t.GetType().GetProperty(attr.Field1).GetValue(t));
//Si la lista está vacía, no ejecuto nada en el insert.
if (entityMany.Count > 0)
{
queryIns = queryIns
.Replace("#TABLE#", attr.TableMany)
.Replace("#COLUMNS#", attr.Field1 + "," + attr.Field2)
.Replace("#PARAMETERS#", queryParam.Remove(queryParam.Length - 1));
}
else
{
queryIns = "";
}
using (var conn = Connection.GetSQLConnection())
{
//Ejecuto los querys para borrar e insertar los datos.
command.Connection = conn;
command.CommandText = queryDel + " ; " + queryIns;
conn.Open();
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Connection.GetSQLConnection().State == ConnectionState.Open)
{
Connection.GetSQLConnection().Close();
}
}
}
public List <Y> GetListEntityMany(int Id)
{
var query = GetManyQuery;
var parentEntity = (T)Activator.CreateInstance(typeof(T));
var childEntity = (Y)Activator.CreateInstance(typeof(Y));
var propId = parentEntity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(PrimaryKey)));
//Recorro las propiedades para armar
foreach (var _property in parentEntity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(EntityMany))))
{
EntityMany attr = (EntityMany)_property.GetCustomAttribute(typeof(EntityMany));
if (attr.TableRela == childEntity.GetType().Name)
{
query = query.Replace("#TABLE_PARENT#", parentEntity.GetType().Name);
query = query.Replace("#TABLE_MANY#", attr.TableMany);
query = query.Replace("#TABLE_CHILD#", childEntity.GetType().Name);
query = query.Replace("#ID_PARENT#", attr.Field1);
query = query.Replace("#ID_CHILD#", attr.Field2);
break;
}
}
try
{
Connection.GetSQLConnection().Open();
_command = new SqlCommand();
_command.Connection = Connection.GetSQLConnection();
_command.CommandText = query;
_command.Parameters.Add(new SqlParameter("@" + propId.FirstOrDefault().Name, Id));
SqlDataReader reader = _command.ExecuteReader();
return(EntityManyList(reader));
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.GetSQLConnection().Close();
}
}
public void InsertRelation(T entity, string property)
{
try
{
var propertys = entity.GetType().GetProperties().Where(x => x.PropertyType.IsGenericType == false && Attribute.IsDefined(x, typeof(Insertable)));
//Controlo que tenga las propiedades definidas.
if (propertys.Count() == 0)
{
throw new Exception("La entidad " + entity.GetType().Name + " no tiene las propiedades \"Insertable\" definida");
}
//Inserto las entidades relacionadas.
foreach (var _property in entity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(EntityMany))))
{
EntityMany attr = (EntityMany)_property.GetCustomAttribute(typeof(EntityMany));
if (attr.TableRela == property)
{
System.Collections.IList list = (System.Collections.IList)_property.GetValue(entity, null);
InsertRelationMany(list, entity, attr, false);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Connection.GetSQLConnection().State == ConnectionState.Open)
{
Connection.GetSQLConnection().Close();
}
}
}
public void Update(T entity)
{
try
{
var updateCommand = new SqlCommand();
var columnsValues = "";
var where = "";
int paramCount = 0;
var propertys = entity.GetType().GetProperties().Where(x => x.PropertyType.IsGenericType == false && Attribute.IsDefined(x, typeof(Updatable)));
var propId = entity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(PrimaryKey)));
//Controlo que tenga las propiedades definidas.
if (propertys.Count() == 0)
{
throw new Exception("La entidad " + entity.GetType().Name + " no tiene las propiedades \"Updatable\" definida");
}
foreach (var _property in propertys)
{
columnsValues += _property.Name + " = " + "@" + paramCount.ToString() + ",";
// In the command, there are some parameters denoted by @, you can change their value on a condition, in my code they're hardcoded.
var value = _property.GetValue(entity);
switch (_property.PropertyType.Name)
{
case "DateTime":
if (value.ToString() == DateTime.MinValue.ToString())
{
updateCommand.Parameters.Add(new SqlParameter("@" + paramCount.ToString(), DBNull.Value));
}
else
{
updateCommand.Parameters.Add(new SqlParameter("@" + paramCount.ToString(), value));
}
break;
default:
if (value == null)
{
updateCommand.Parameters.Add(new SqlParameter("@" + paramCount.ToString(), DBNull.Value));
}
else
{
updateCommand.Parameters.Add(new SqlParameter("@" + paramCount.ToString(), value));
}
break;
}
paramCount++;
}
//Armo el where del query.
foreach (var prop in propId)
{
PropertyInfo propertyInfo = entity.GetType().GetProperty(prop.Name);
where += prop.Name + " = " + propertyInfo.GetValue(entity, null) + " AND ";
}
var query = UpdateQuery;
query = query.Replace("#COLUMNS_VALUES#", columnsValues.Remove(columnsValues.Length - 1));
query = query.Replace("#TABLE#", entity.GetType().Name.ToString());
query = query.Replace("#WHERE#", where.Remove(where.Length - 5));
using (var conn = Connection.GetSQLConnection())
{
updateCommand.Connection = conn;
updateCommand.CommandText = query;
updateCommand.CommandType = CommandType.Text;
conn.Open();
updateCommand.ExecuteNonQuery();
}
//Inserto las entidades relacionadas.
foreach (var _property in entity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(EntityMany))))
{
EntityMany attr = (EntityMany)_property.GetCustomAttribute(typeof(EntityMany));
System.Collections.IList list = (System.Collections.IList)_property.GetValue(entity, null);
InsertRelationMany(list, entity, attr, true);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Connection.GetSQLConnection().State == ConnectionState.Open)
{
Connection.GetSQLConnection().Close();
}
}
}
public Int32 Insert(T entity)
{
try
{
var insertCommand = new SqlCommand();
var columns = "";
var parameters = "";
int paramCount = 0;
int identity = 0;
var propertys = entity.GetType().GetProperties().Where(x => x.PropertyType.IsGenericType == false && Attribute.IsDefined(x, typeof(Insertable)));
var propId = entity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(PrimaryKey)));
//Controlo que tenga las propiedades definidas.
if (propertys.Count() == 0)
{
throw new Exception("La entidad " + entity.GetType().Name + " no tiene las propiedades \"Insertable\" definida");
}
foreach (var _property in propertys)
{
columns += "[" + _property.Name + "],";
parameters += "@" + paramCount.ToString() + ",";
// In the command, there are some parameters denoted by @, you can change their value on a condition, in my code they're hardcoded.
insertCommand.Parameters.Add(new SqlParameter(paramCount.ToString(), _property.GetValue(entity)));
paramCount++;
}
//Format the insert query to execute.
var query = InsertQuery;
query = query.Replace("#COLUMNS#", columns.Remove(columns.Length - 1));
query = query.Replace("#PARAMETERS#", parameters.Remove(parameters.Length - 1));
query = query.Replace("#TABLE#", entity.GetType().Name.ToString());
query = query + " SELECT @@IDENTITY";
using (var conn = Connection.GetSQLConnection())
{
insertCommand.Connection = conn;
insertCommand.CommandText = query;
conn.Open();
identity = Convert.ToInt32(insertCommand.ExecuteScalar());
conn.Close();
}
//Guardo el id del objeto generado, para poder utilizarlo en las entidades relacionadas.
entity.GetType().GetProperty(propId.FirstOrDefault().Name).SetValue(entity, identity);
var logMessage = "Insertar {0} - Id: {1}";
//Inserto las entidades relacionadas.
foreach (var _property in entity.GetType().GetProperties().Where(x => Attribute.IsDefined(x, typeof(EntityMany))))
{
EntityMany attr = (EntityMany)_property.GetCustomAttribute(typeof(EntityMany));
System.Collections.IList list = (System.Collections.IList)_property.GetValue(entity, null);
InsertRelationMany(list, entity, attr, true);
}
return(identity);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Connection.GetSQLConnection().State == ConnectionState.Open)
{
Connection.GetSQLConnection().Close();
}
}
}