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); }