private void Update(BizDataBaseObject dbObject)
{
string strCommandText = "";
try
{
string debugRecordStr = dbObject.debug("");
strCommandText = "UPDATE " + dbObject.table + " SET ";
ArrayList attributes = dbObject.attributes;
bool isFirst = true;
bool updatedAttributeFound = false;
for (int i = 0; i < attributes.Count; i++)
{
BizDataBaseColumn column = (BizDataBaseColumn)attributes[i];
if (column.isDirty())
{
if (column.isDerrived || column.isKey)
continue;
if (!isFirst)
strCommandText = strCommandText + " , ";
// Column
strCommandText = strCommandText + "[" + column.name + "]= ";
// Value
String columnValue = null;
if (column.getValue() != null)
{
if (column.type == BizDataBaseColumn.STRING)
{
String value = column.getValue("").ToString().Replace("'", "''");
columnValue = "N'" + value + "'";
}
else if (column.type == BizDataBaseColumn.INTEGER)
columnValue = column.getValue().ToString();
else if (column.type == BizDataBaseColumn.DECIMAL)
columnValue = column.getValue().ToString();
else if (column.type == BizDataBaseColumn.DATE)
{
String value = ((DateTime)column.getValue()).ToString("dd/MM/yyyy HH:mm:ss");
columnValue = "'" + value + "'";
}
else if (column.type == BizDataBaseColumn.BOOL)
{
if ((bool)column.getValue() == true)
columnValue = "1";
else
columnValue = "0";
}
else if (column.type == BizDataBaseColumn.BINARY)
{
columnValue = "0x" + ToHexString((byte[])column.getValue());
}
else if (column.type == BizDataBaseColumn.CLOB)
{
Encoding u16LE = Encoding.Unicode;
byte[] byteValue = (byte[])u16LE.GetBytes((string)column.getValue());
columnValue = "0x" + ToHexString(byteValue);
}
else if (column.type == BizDataBaseColumn.IMAGE)
{
columnValue = "0x" + ToHexString((byte[])column.getValue());
}
else
throw new BizDataAccessException("Not supported Data Type:" + column.type);
}
else
{
columnValue = "null";
}
strCommandText = strCommandText + columnValue;
isFirst = false;
updatedAttributeFound = true;
}
}
// Return (No modified data found)
if (!updatedAttributeFound)
return;
//
strCommandText = strCommandText + " WHERE ";
isFirst = true;
for (int i = 0; i < attributes.Count; i++)
{
BizDataBaseColumn column = (BizDataBaseColumn)attributes[i];
if (column.isKey & column.isSet)
{
if (!isFirst)
strCommandText = strCommandText + " and ";
if (column.type == BizDataBaseColumn.STRING)
strCommandText = strCommandText + "[" + column.name + "]= N'" + column.getValue() + "'";
else
strCommandText = strCommandText + "[" + column.name + "]= '" + column.getValue() + "'";
isFirst = false;
}
}
// Check Object Version first
object oldObjVersion = GetObjectVersion(dbObject);
if (oldObjVersion != null)
{
string oldVersion = Convert.ToBase64String((byte[])oldObjVersion);
if (dbObject.OBJ_VERSION.getValue() == null)
throw new BizDataAccessException("Object [" + dbObject.displayName + "] has empty OBJECT_VERSION!");
string currentVersion = Convert.ToBase64String((byte[])dbObject.OBJ_VERSION.getValue());
if (!oldVersion.Equals(currentVersion))
throw new BizDataAccessException("Object [" + dbObject.displayName + "] has been changed by another user!");
}
validateBeforSave(dbObject);
// Update record
SqlCommand objCommand = new SqlCommand(strCommandText, SESSION.SqlConn, SESSION.SqlTrans);
objCommand.ExecuteNonQuery();
addDebugItem(new BizDataAccessDebugItem("Server Call : Update( " + dbObject.table + " )", strCommandText + "\n\n" + debugRecordStr));
}
catch (SqlException e)
{
addDebugItem(new BizDataAccessDebugItem("Error: Server Call : Update( " + dbObject.table + " )", strCommandText + "\n\n" + e.Message + "\n" + e.StackTrace.ToString()));
if (e.Number == 8152)
{
throw new BizDataAccessException("Trying to insert large text! Object [" + dbObject.displayName + "] Key:" + dbObject.getKeyValue());
}
throw new BizDataAccessException("Error Saving [ " + dbObject.displayName + " ]", e.Message, e);
}
catch (BizDataAccessException e)
{
addDebugItem(new BizDataAccessDebugItem("Error: Server Call : Update( " + dbObject.table + " )", strCommandText + "\n\n" + e.Message + "\n" + e.StackTrace.ToString()));
throw new BizDataAccessException("Error Saving [ " + dbObject.displayName + " ]", e.Message, e);
}
catch (Exception e)
{
addDebugItem(new BizDataAccessDebugItem("Error: Server Call : Update( " + dbObject.table + " )", strCommandText + "\n\n" + e.Message + "\n" + e.StackTrace.ToString()));
throw new BizDataAccessException("Error Saving [ " + dbObject.displayName + " ]", e.Message, e);
}
}
private void Insert(BizDataBaseObject dbObject)
{
string strCommandText = "";
try
{
string debugRecordStr = dbObject.debug("");
strCommandText = "INSERT INTO [" + dbObject.table + "] (";
ArrayList attributes = dbObject.attributes;
bool addSeperator = false;
for (int i = 0; i < attributes.Count; i++)
{
BizDataBaseColumn column = (BizDataBaseColumn)attributes[i];
if (column.useSysDate && BizDataBaseColumn.DATE.Equals(column.type))
{
// Only set System date if the value is null. Otherwise keep the value that client set
if (column.getValue() == null)
{
column.setValue(GetSystemDate());
}
}
if (column.isSystemGenerated)
{
}
else
{
if (column.isDerrived)
continue;
if (!column.isSet)
continue;
}
if (addSeperator)
{
strCommandText = strCommandText + ",";
}
strCommandText = strCommandText + "[" + column.name + "]";
addSeperator = true;
}
strCommandText = strCommandText + ") VALUES(";
addSeperator = false;
for (int i = 0; i < dbObject.attributes.Count; i++)
{
BizDataBaseColumn column = (BizDataBaseColumn)attributes[i];
if (column.isSystemGenerated)
{
}
else
{
if (column.isDerrived || column.isNonPersistant)
continue;
if (!column.isSet)
continue;
}
if (addSeperator)
{
strCommandText = strCommandText + ",";
}
String columnValue = null;
if (column.name.Equals("timestamp"))
{
columnValue = "NULL"; // NULL for OBJ_VERSION
}
else if (column.isSystemGenerated)
{
string key = GetNextKey(dbObject, column);
columnValue = "'" + key + "'";
column.setValue(key);
}
else if (column.getValue() != null)
{
string strValue = column.getValue().ToString();
if (column.isUpperCase)
strValue = myTI.ToUpper(strValue);
if (column.type == BizDataBaseColumn.STRING)
{
String value = strValue.Replace("'", "''");
columnValue = "N'" + value + "'";
}
else if (column.type == BizDataBaseColumn.INTEGER)
columnValue = column.getValue().ToString();
else if (column.type == BizDataBaseColumn.DECIMAL)
columnValue = column.getValue().ToString();
else if (column.type == BizDataBaseColumn.DATE)
{
String value = ((DateTime)column.getValue()).ToString("dd/MM/yyyy HH:mm:ss");
columnValue = "'" + value + "'";
}
else if (column.type == BizDataBaseColumn.BOOL)
{
if ((bool)column.getValue() == true)
columnValue = "1";
else
columnValue = "0";
}
else if (column.type == BizDataBaseColumn.BINARY)
{
columnValue = "0x" + ToHexString((byte[])column.getValue());
}
else if (column.type == BizDataBaseColumn.CLOB)
{
Encoding u16LE = Encoding.Unicode;
byte[] byteValue = (byte[])u16LE.GetBytes((string)column.getValue());
columnValue = "0x" + ToHexString(byteValue);
}
else if (column.type == BizDataBaseColumn.IMAGE)
{
columnValue = "0x" + ToHexString((byte[])column.getValue());
}
else
throw new BizDataAccessException("Not supported Data Type:" + column.type);
}
else
{
columnValue = "null";
}
strCommandText = strCommandText + columnValue;
addSeperator = true;
}
strCommandText = strCommandText + ")";
validateBeforSave(dbObject);
SqlCommand objCommand = new SqlCommand(strCommandText, SESSION.SqlConn, SESSION.SqlTrans);
objCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
if (e.Number == 2627)
{
throw new BizDataAccessException("Cannot Insert duplicate [" + dbObject.displayName + "] Key:" + dbObject.getKeyValue());
}
throw new BizDataAccessException("Error Saving [ " + dbObject.displayName + " ]", e.Message, e);
}
catch (Exception e)
{
throw new BizDataAccessException("Error Saving [ " + dbObject.displayName + " ]", e.Message, e);
}
}