/// <summary>
/// Generates insert of an object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
public void Insert <T>(T item)
{
string tableName = ObjectTypeHelper.GetTableName <T>(item);
// Properties of object that are not primary keys
PropertyInfo[] modelProperties = ObjectTypeHelper.GetModelProperties <T>(item, false);
// Properties of object that are also primary keys
PropertyInfo[] primaryKeyProperties = ObjectTypeHelper.GetPrimaryKeyProperties <T>(item);
if (modelProperties == null || modelProperties.Count() == 0)
{
// Moves composite keys to model properties if they are the only properties of the table/object
if (primaryKeyProperties.Count() > 0)
{
modelProperties = primaryKeyProperties;
}
else
{
throw new Exception("Class has no properties or are missing Member attribute");
}
}
// Generates insert statement
StringBuilder query = new StringBuilder();
query.Append("insert into " + tableName + "(" + String.Join(",", modelProperties.Select(x => x.Name)) + ") ");
query.Append("values (" + String.Join(", ", modelProperties.Select(x => "@" + x.Name + suffix)) + ") ");
query.Append("select @primaryKey = @@identity");
try
{
// Generates and executes sql command
SqlCommand cmd = GenerateSqlCommand <T>(item, query.ToString(), modelProperties, null, ADOCRUDEnums.Action.Insert, suffix);
cmd.ExecuteNonQuery();
// Loads db generated identity value into property with primary key attribute
// if object only has 1 primary key
// Multiple primary keys = crosswalk table which means property already contains the values
if (primaryKeyProperties != null && primaryKeyProperties.Count() == 1)
{
primaryKeyProperties[0].SetValue(item, cmd.Parameters["primaryKey"].Value as object);
}
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw new Exception(ex.Message);
}
}
/// <summary>
/// Generates update of an object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
public void Update <T>(T item)
{
string tableName = ObjectTypeHelper.GetTableName <T>(item);
// Properties of object that are not primary keys
PropertyInfo[] modelProperties = ObjectTypeHelper.GetModelProperties <T>(item, false);
// Properties of object that are also primary keys
PropertyInfo[] primaryKeyProperties = ObjectTypeHelper.GetPrimaryKeyProperties <T>(item);
if (modelProperties == null || modelProperties.Count() == 0)
{
// Moves composite keys to model properties if they are the only properties of the table/object
if (primaryKeyProperties.Count() > 0)
{
modelProperties = primaryKeyProperties;
}
else
{
throw new Exception("Class has no properties or are missing Member attribute");
}
}
// Generates update statement
StringBuilder query = new StringBuilder();
query.Append("update " + tableName + " set ");
query.Append(String.Join(", ", modelProperties.Select(x => x.Name + " = @" + x.Name + suffix)));
query.Append(" where " + String.Join(" and ", primaryKeyProperties.Select(x => x.Name + " = @" + x.Name + suffix)));
try
{
// Generates and executes sql command
SqlCommand cmd = GenerateSqlCommand <T>(item, query.ToString(), modelProperties, primaryKeyProperties, ADOCRUDEnums.Action.Update, suffix);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw new Exception(ex.Message);
}
}