public virtual IDbCommand GetInsertCommand(ModelBase model)
{
//preparing the update query
StringBuilder sb = new StringBuilder(),
sb_value = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} ( ", model.GetTableName());
sb_value.Append(" VALUES(");
IDbCommand cmd = GetCommand();
PropertyInfo prop = null;
List <PropertyInfo> FieldList = model.GetFieldList();
PropertyInfo keyField = model.GetKeyPropertyInfo();
for (int i = 0, len = FieldList.Count; i < len; i++)
{
prop = FieldList[i];
if (model.DmlIgnoreFields.Contains(prop.Name))
{
continue;
}
if (FieldList[i].GetCustomAttribute(typeof(NotMappedAttribute)) != null)
{
continue;
}
if (prop.Name == keyField.Name)
{
if (model.GetSequenceName() != null)
{
//sb.AppendFormat(" {0},", prop.Name);
//sb_value.AppendFormat(" {0}.NEXTVAL,", model.GetSequenceName());
continue;
}
}
else
{
if (prop.GetValue(model) == null)
{
//db default will be saved.
continue;
}
else
{
//if (prop.Name == "date_ad" || prop.Name == "date_bs" || prop.Name == "dob_ad")
if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?))
{
if (prop.GetCustomAttributes(typeof(CurrentTimestampAttribute), true).Length > 0)
{
sb.AppendFormat(" {0},", prop.Name);
//sb_value.AppendFormat(" {0},", Connection.TimeStampText);
}
else
{
sb.AppendFormat(" {0},", prop.Name);
//sb_value.AppendFormat(" {0},", prop.Name, Connection.GetDBDate(prop.GetValue(model)));
}
}
else
{
sb.AppendFormat(" {0},", prop.Name);
sb_value.AppendFormat(" {1}{0},", prop.Name, ParamPrefix);
//sb_value.AppendFormat(" trim({1}{0}),", prop.Name, ParamPrefix);
cmd.Parameters.Add(CreateParameter(prop, model));
}
}
}
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
}
if (sb_value.Length > 0)
{
sb_value.Remove(sb_value.Length - 1, 1);
sb_value.Append(") ");
sb.Append(sb_value.ToString());
}
//TODO: this code is Postgre specific so need to be changed in future to make way for other database
// This line return the primary key value which is recently added
sb.AppendFormat(" RETURNING {0}", model.GetAllFields(string.Empty, false, false));
cmd.CommandText = sb.ToString();
return(cmd);
}
public virtual IDbCommand GetUpdateCommand(ModelBase model)
{
//preparing the update query
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE {0} SET ", model.GetTableName());
IDbCommand cmd = GetCommand();
PropertyInfo keyField = model.GetKeyPropertyInfo();
//:NOTE
//THIS has to be done by REPO (its biz logic)
//PropertyInfo is_verified = model.GetType().GetProperty("is_verified");
//if (is_verified != null)
//{
// if (Conversion.ToInt32(is_verified.GetValue(model)) == 0)
// {
// model.GetType().GetProperty("verified_personnel_id").SetValue(model, null);
// model.GetType().GetProperty("verified_date_ad").SetValue(model, null);
// model.GetType().GetProperty("verified_date_bs").SetValue(model, null);
// }
//}
PropertyInfo prop = null;
List <PropertyInfo> FieldList = model.GetFieldList();
for (int i = 0, len = FieldList.Count; i < len; i++)
{
prop = FieldList[i];
//if the field is in Data manipulation Ignore then don't add it in SQL
if (model.DmlIgnoreFields.Contains(prop.Name))
{
continue;
}
if (FieldList[i].GetCustomAttribute(typeof(NotMappedAttribute)) != null)
{
continue;
}
if (prop.GetValue(model) == null)
{
sb.AppendFormat("{0} = NULL,", prop.Name);
continue;
}
if (prop.Name == keyField.Name)
{
continue;
}
if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?))
{
if (prop.GetCustomAttributes(typeof(CurrentTimestampAttribute), true).Length > 0)
{
//sb.AppendFormat(" {0} = {1},", prop.Name, Connection.TimeStampText);
}
else
{
if (prop.GetValue(model).ToString() == "")
{
prop.SetValue(model, null);
}
//sb.AppendFormat(" {0} = {1},", prop.Name, Connection.GetDBDate(prop.GetValue(model)));
}
}
else
{
//sb.AppendFormat("{0} = trim({1}{0}),", prop.Name, ParamPrefix);
sb.AppendFormat("{0} = {1}{0},", prop.Name, ParamPrefix);
cmd.Parameters.Add(CreateParameter(prop, model));
}
//prop.GetCustomAttributes()
}
sb.AppendFormat(" updated_by = {0},", ParamPrefix + "updated_by");
//sb.AppendFormat(" updated_on = {0}", Connection.TimeStampText);
cmd.Parameters.Add(GetCurrentUserParam("updated_by"));
/*if (sb.Length > 0)
* sb.Remove(sb.Length - 1, 1);*/
//sb.AppendFormat(" WHERE {0} = 0", Connection.IsNull("is_deleted"));
if (keyField != null)
{
sb.AppendFormat(" AND {0} = {1}{0}", keyField.Name, ParamPrefix);
cmd.Parameters.Add(CreateParameter(keyField, model));
}
sb.AppendFormat(" RETURNING {0}", model.GetAllFields(string.Empty, false, false));
cmd.CommandText = sb.ToString();
return(cmd);
}