/// <summary>
/// Update base <see cref="EntityType"/> to database
/// </summary>
/// <param name="sql"></param>
/// <param name="p"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public virtual int Update(string sql, EntityType entity)
{
int returnValue = 0;
Db db = new Db();
DbParameter[] param = new DbParameter[EntityFieldInfoCache <EntityType> .GetEntityFieldInfos().Count];
int i = 0;
// build insert query and parameter
foreach (EntityFieldInfo fieldInfo in EntityFieldInfoCache <EntityType> .GetEntityFieldInfos())
{
if (fieldInfo.FieldAccessMode == FieldAccessMode.ReadWrite)
{
try
{
// retrive entity value
object value = EntityReflector <EntityType> .GetValueFromField(entity, fieldInfo);
// add data parameter
if (value != null)
{
if (fieldInfo.FieldType == FieldType.DateTime && Convert.ToDateTime(value).ToString("dd/MM/yyyy") == "01/01/0001")
{
continue;
}
param[i] = new DbParameter("@" + fieldInfo.FieldName, value);
i++;
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Error occured while getting '{0}' property from data row.", fieldInfo.FieldName), ex);
}
}
else
{
param[i] = new DbParameter("@" + fieldInfo.FieldName, entity.Id);
i++;
}
}
DbParameter[] param2 = new DbParameter[i];
for (int j = 0; j < i; j++)
{
param2[j] = new DbParameter(param[j].ParameterName, param[j].Value);
}
try
{
returnValue = db.Update(sql, param2, CommandType.StoredProcedure);
}
catch (Exception ex)
{
log.Fatal("Update entity has an exception: ", ex);
}
return(returnValue);
}
/// <summary>
/// Update base <see cref="EntityType"/> to database
/// </summary>
/// <param name="entity">Entity</param>
/// <returns>A object of <see cref="EntityType"/> when success, <b>null</b> when error</returns>
public virtual EntityType Update(EntityType entity)
{
Db db = new Db();
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE ").Append(entity.TableName).Append(" SET ");
DbParameter[] param = new DbParameter[EntityFieldInfoCache <EntityType> .GetEntityFieldInfos().Count];
// build update query and parameter
int i = 0;
foreach (EntityFieldInfo fieldInfo in EntityFieldInfoCache <EntityType> .GetEntityFieldInfos())
{
if (fieldInfo.FieldAccessMode == FieldAccessMode.ReadWrite)
{
try
{
object value = EntityReflector <EntityType> .GetValueFromField(entity, fieldInfo);
if (value != null)
{
if (fieldInfo.FieldType == FieldType.DateTime && DateTime.Compare(Convert.ToDateTime(value), new DateTime(1, 1, 1)) == 0)
{
continue;
}
param[i] = new DbParameter("@" + fieldInfo.FieldName, value);
sb.Append(fieldInfo.FieldName).Append("=@").Append(fieldInfo.FieldName).Append(", ");
i++;
}
}
catch (Exception ex)
{
log.Fatal("Error occured while getting '{0}' property from data row: ", ex);
throw new Exception(String.Format("Error occured while getting '{0}' property from data row.", fieldInfo.FieldName), ex);
}
}
else
{
param[i] = new DbParameter("@" + fieldInfo.FieldName, entity.Id);
i++;
}
}
DbParameter[] param2 = new DbParameter[i];
for (int j = 0; j < i; j++)
{
param2[j] = new DbParameter(param[j].ParameterName, param[j].Value);
log.Info(param[j].ParameterName + ":" + param[j].Value);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" WHERE id=@id");
return((db.Update(sb.ToString(), param2, CommandType.Text) > 0) ? entity : null);
}
/// <summary>
/// Insert base <see cref="EntityType"/> to database
/// </summary>
/// <param name="entity">Entity</param>
/// <returns>A object of <see cref="EntityType"/> when success, <b>null</b> when error</returns>
public virtual int Insert(EntityType entity)
{
Db db = new Db();
// string buider query insert
StringBuilder sb = new StringBuilder();
// string builder values to insert
StringBuilder sbValues = new StringBuilder();
// append entity table name
sb.Append("INSERT INTO ").Append(entity.TableName).Append(" (");
sbValues.Append(" VALUES (");
DbParameter[] param = new DbParameter[EntityFieldInfoCache <EntityType> .GetEntityFieldInfos().Count];
int i = 0;
// build insert query and parameter
foreach (EntityFieldInfo fieldInfo in EntityFieldInfoCache <EntityType> .GetEntityFieldInfos())
{
if (fieldInfo.FieldAccessMode == FieldAccessMode.ReadWrite)
{
try
{
// retrive entity value
object value = EntityReflector <EntityType> .GetValueFromField(entity, fieldInfo);
// add data parameter
if (value != null)
{
if (fieldInfo.FieldType == FieldType.DateTime && Convert.ToDateTime(value).ToString("dd/MM/yyyy") == "01/01/0001")
{
continue;
}
if (fieldInfo.FieldType == FieldType.String && value.ToString() == string.Empty)
{
continue;
}
param[i] = new DbParameter("@" + fieldInfo.FieldName, value);
// append to query
sb.Append(fieldInfo.FieldName).Append(", ");
sbValues.Append("@").Append(fieldInfo.FieldName).Append(", ");
i++;
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Error occured while getting '{0}' property from data row.", fieldInfo.FieldName), ex);
}
}
//else
//{
// param[i] = new DbParameter("@" + fieldInfo.FieldName, entity.Id);
// i++;
//}
}
DbParameter[] param2 = new DbParameter[i];
for (int j = 0; j < i; j++)
{
param2[j] = new DbParameter(param[j].ParameterName, param[j].Value);
}
// fix string builder value
sb.Remove(sb.Length - 2, 2); sbValues.Remove(sbValues.Length - 2, 2);
sb.Append(")").Append(sbValues).Append(")");
// execute insert query
return(db.Insert(sb.ToString(), true, param2, CommandType.Text));
}