public IDbContext Update(IDbContext ctx, object obj, bool isAuditLog)
{
ctx.CommandText = "";
if (obj != null && obj is DbDataModel)
{
Type type = obj.GetType();
Hashtable hash = ((DbDataModel)obj).OriginalValue;
string fields = "";
foreach (PropertyInfo prop in type.GetProperties())
{
foreach (Attribute attrib in prop.GetCustomAttributes(false))
{
ColumnAttribute schema = attrib as ColumnAttribute;
if (schema != null && !schema.IsPrimaryKey)
{
object oriValue = hash != null ? hash[schema.Name] : null;
object newValue = prop.GetValue(obj, null);
if (schema.IsNullable && IsFieldNullAbleNotAssignValue(newValue, prop.PropertyType))
//(newValue == null && schema.IsNullable && oriValue != null)
{
//Sql Script Update
fields += string.Format(", {0} = null", schema.Name);
}
else if (newValue != null && !newValue.Equals(oriValue))
{
//Sql Script Update
fields += string.Format(", {0} = {1}{0}", schema.Name, _prefixParameter);
//Parameter
ctx.Add(string.Format("{0}{1}", _prefixParameter, schema.Name), newValue);
}
//newValue = CheckIsNull(newValue, prop.PropertyType);
//if (newValue.GetType().FullName.Contains("Int64"))
// if (Convert.ToInt64(newValue) == 0)
// newValue = null;
//if (newValue == null && schema.IsNullable)
//{
// //Sql Script Update
// fields += string.Format(", {0} = null", schema.Name);
//}
//else if (!newValue.Equals(oriValue))
//{
// ////if (newValue.GetType().FullName.Contains("DateTime"))
// //// if (Convert.ToDateTime(newValue).Year < 1900)
// //// newValue = new DateTime(1900, 1, 1);
// newValue = CheckIsNull(newValue, prop.PropertyType);
// //Sql Script Update
// fields += string.Format(", {0} = {1}{0}", schema.Name, _prefixParameter);
// //Parameter
// ctx.Add(string.Format("{0}{1}", _prefixParameter, schema.Name), newValue);
//}
}
}
}
if (!fields.Equals(string.Empty))
{
string keys = "";
foreach (DbFieldValue dbValue in GetKeyValues(obj))
{
keys += string.Format(" AND {0} = {1}{0}", dbValue.FieldName, _prefixParameter);
ctx.Add(string.Format("{0}{1}", _prefixParameter, dbValue.FieldName), dbValue.Current);
}
string query;
query = string.Format("UPDATE {0} SET ", _tableName);
query += string.Format("{0} ", fields.Substring(2));
query += string.Format("WHERE {0} ", keys.Substring(5));
if (isAuditLog)
{
ctx.CommandText = GetUpdateAuditLogScript(obj) + "; " + query;
}
else
{
ctx.CommandText = query;
}
//if (!_tableName.ToLower().Contains("systranslation"))
//{
// SiAuto.Main.LogSql("Update " + _tableName, ctx.CommandText);
// SiAuto.Main.LogObject(_tableName, obj);
//}
}
}
return(ctx);
}
private string GetInsertAuditLogScript(object obj)
{
StringBuilder auditLogScript = new StringBuilder();
string auditLogHeaderScript = "";
if (obj != null && obj is DbDataModel)
{
Type type = obj.GetType();
Hashtable hash = ((DbDataModel)obj).OriginalValue;
string primaryKeyData = "";
foreach (DbFieldValue dbValue in GetKeyValues(obj))
{
primaryKeyData +=
string.Format(" AND [{0}] = ''{1}''", dbValue.FieldName, dbValue.Current);
}
if (primaryKeyData != string.Empty)
{
primaryKeyData = primaryKeyData.Substring(5);
}
auditLogHeaderScript =
@"DECLARE
@l_IdentitySave varchar(50),
@l_AuditLogTransactionID Int,
@l_PrimaryKey varchar(4000),
@l_Inserted bit
SET NOCOUNT ON
SET @l_IdentitySave = CAST(IsNull(@@IDENTITY,1) AS varchar(50))
INSERT INTO AuditLogTransactions (
TableName,AuditActionID,PrimaryKeyData,HostName,AppName,ModifiedBy,ModifiedDate)
VALUES ('" +
_tableName + "','C','" + primaryKeyData + "',HOST_NAME(),APP_NAME(),'" +
HttpContext.Current.Session["_UserName"] +
@"',GETDATE())
SET @l_AuditLogTransactionID = SCOPE_IDENTITY() ";
foreach (PropertyInfo prop in type.GetProperties())
{
foreach (Attribute attrib in prop.GetCustomAttributes(false))
{
ColumnAttribute schema = attrib as ColumnAttribute;
object newValue = prop.GetValue(obj, null);
if (schema.DataType != "Byte[]")
{
auditLogScript.AppendLine(
@"INSERT INTO AuditLogData (AuditLogTransactionID,ColumnName,OldValueVarchar,NewValueVarchar,DataType)
VALUES (@l_AuditLogTransactionID," +
string.Format("'{0}','{1}','{2}','{3}')", schema.Name,
"", newValue == null ? newValue:newValue.ToString().Replace("'", "''"), "A"));
}
}
}
}
string auditLogBody = auditLogScript.ToString();
string retScript;
if (auditLogBody != "")
{
retScript = auditLogHeaderScript + auditLogBody +
@" -- Restore @@IDENTITY Value
DECLARE @l_maxprec AS varchar(2)
SET @l_maxprec=CAST(@@MAX_PRECISION as varchar(2))
EXEC('SELECT IDENTITY(decimal('+@l_maxprec+',0),'+@l_IdentitySave+',1) id INTO #tmp')";
}
else
{
retScript = "";
}
return(retScript);
}
public IDbContext Insert(IDbContext ctx, object obj, bool isAuditLog)
{
string sqlInsert;
string fieldName = "";
string parameter = "";
//Simpan informasi nama table di session untuk informasi error handling bila terjadi duplicate key
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlProcessTable"] = "";
HttpContext.Current.Session["_LastSqlProcessPKField"] = "";
HttpContext.Current.Session["_LastSqlProcessPKValue"] = "";
}
if (obj is DbDataModel)
{
Type type = obj.GetType();
PropertyInfo[] propInfs = type.GetProperties();
foreach (PropertyInfo prop in propInfs)
{
object[] custAttr = prop.GetCustomAttributes(false);
foreach (Attribute attrib in custAttr)
{
ColumnAttribute schema = attrib as ColumnAttribute;
if (schema != null && !schema.IsComputed &&
!schema.IsIdentity &&
!schema.IsTimeStamp)
{
object fieldValue = prop.GetValue(obj, null);
if (!schema.IsNullable)
{
fieldValue = CheckIsNull(fieldValue, prop.PropertyType);
}
//if (fieldValue != null &&
// !(schema.IsNullable && IsFieldNullAbleNotAssignValue(fieldValue, prop.PropertyType)))
//{
// ctx.Add(string.Format("{0}{1}", _prefixParameter, schema.Name), fieldValue);
// fieldName += string.Format(", {0}", schema.Name);
// parameter += string.Format(", {0}{1}", _prefixParameter, schema.Name);
// //Simpan informasi primary key di session untuk informasi error handling bila terjadi duplicate key
// if (schema.IsPrimaryKey)
// {
// HttpContext.Current.Session["_LastSqlProcessPKField"] += ";" + schema.Name;
// HttpContext.Current.Session["_LastSqlProcessPKValue"] += ";" + fieldValue;
// }
//}
fieldName += string.Format(", {0}", schema.Name);
if (fieldValue != null &&
!(schema.IsNullable && IsFieldNullAbleNotAssignValue(fieldValue, prop.PropertyType)))
{
parameter += string.Format(", {0}{1}", _prefixParameter, schema.Name);
if (fieldValue.GetType().FullName.Contains("DateTime"))
{
if (fieldValue is DBNull || Convert.ToDateTime(fieldValue).Year < 1900)
{
fieldValue = new DateTime(1900, 1, 1);
}
}
ctx.Add(string.Format("{0}{1}", _prefixParameter, schema.Name), fieldValue);
}
else
{
parameter += ", NULL";
}
//Simpan informasi primary key di session untuk informasi error handling bila terjadi duplicate key
if (schema.IsPrimaryKey && HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlProcessPKField"] += ";" + schema.Name;
HttpContext.Current.Session["_LastSqlProcessPKValue"] += ";" + fieldValue;
}
}
}
}
}
//Simpan informasi nama table di session untuk informasi error handling bila terjadi duplicate key
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlProcessTable"] = _tableName;
if (HttpContext.Current.Session["_LastSqlProcessPKField"].ToString().Trim() != string.Empty)
{
HttpContext.Current.Session["_LastSqlProcessPKField"] =
HttpContext.Current.Session["_LastSqlProcessPKField"].ToString().Substring(1);
HttpContext.Current.Session["_LastSqlProcessPKValue"] =
HttpContext.Current.Session["_LastSqlProcessPKValue"].ToString().Substring(1);
}
}
fieldName = (fieldName.Length > 2) ? fieldName.Substring(2) : fieldName;
parameter = (parameter.Length > 2) ? parameter.Substring(2) : parameter;
sqlInsert = string.Format("INSERT INTO {0} ", _tableName);
sqlInsert += string.Format("( {0}) ", fieldName);
sqlInsert += string.Format(" {0} ", "VALUES");
sqlInsert += string.Format("( {0} )", parameter);
if (isAuditLog)
{
ctx.CommandText = GetInsertAuditLogScript(obj) + "; " + sqlInsert;
}
else
{
ctx.CommandText = sqlInsert;
}
//if (!_tableName.ToLower().Contains("systranslation"))
//{
// SiAuto.Main.LogSql("Insert " + _tableName, ctx.CommandText);
// SiAuto.Main.LogObject(_tableName, obj);
//}
return(ctx);
}
