public String GetInsertOdbc(string TN, OdbcDBType type)
{
if (_schema == null)
{
String message = SysMsg.GetSystemMessage(((DataModule)_updateComonent.OwnerComp).Language, "Srvtools", "UpdateComponent", "msg_SchemaIsNull");
throw new ArgumentException(String.Format(message, _updateComonent.Name));
}
if (_insertSQL != null && _insertSQL.Length > 0) return _insertSQL;
// Create the table name and schema name.
CreateTableName(); CreateSchemaName();
StringBuilder columnsSB = new StringBuilder();
StringBuilder valuesSB = new StringBuilder();
Int32 index = 0;
foreach (DataRow schemaRow in _schema.Rows)
{
String columnName = (String)schemaRow["ColumnName"];
String columnType = schemaRow["DataType"].ToString();
//String columnTypeName = schemaRow["DataTypeName"].ToString();
if (IsLeftJoinClumns(schemaRow))
{ index++; continue; }
else
{ index++; }
FieldAttr attr = GetFieldAttrByColumnName(columnName);
if (attr != null && !attr.UpdateEnable)
{
continue;
}
//if (IsReadOnly(schemaRow)) continue;
Object columnValue = GetColumnIOrUValue(columnName, columnType);
if (columnValue == null || columnValue == DBNull.Value)
{ continue; }
if (columnsSB.Length > 0)
{ columnsSB.Append(", "); valuesSB.Append(", "); }
//columnsSB.Append(Quote(columnName));
//valuesSB.Append(Mark(columnTypeName, columnType, TransformMarkerInColumnValue(columnType, columnValue)));
columnsSB.Append(columnName);
valuesSB.Append(MarkOdbc(columnType, TransformMarkerInColumnValue(columnType, columnValue), type));
}
if (columnsSB.ToString() != null && columnsSB.ToString().Length > 0)
{
String insertSQL = "";
if (_tableName == "")
insertSQL = "insert into " + TN + " (" + columnsSB.ToString() + ") " +
" values (" + valuesSB.ToString() + ")";
else
insertSQL = "insert into " + _tableName + " (" + columnsSB.ToString() + ") " +
" values (" + valuesSB.ToString() + ")";
_insertSQL = insertSQL;
}
return _insertSQL;
}
private String MarkOdbc(String type, Object columnValue, OdbcDBType odbcType)
{
if (Type.GetType(type).Equals(typeof(Char)) || Type.GetType(type).Equals(typeof(String)))
{
return _marker.ToString() + columnValue.ToString() + _marker.ToString();
}
else if (Type.GetType(type).Equals(typeof(Boolean)))
{
Boolean b = (Boolean)columnValue;
if (b)
return "1";
else
return "0";
}
else if (Type.GetType(type).Equals(typeof(DateTime)))
{
string s = "";
if (odbcType == OdbcDBType.InfoMix)
{
DateTime t = Convert.ToDateTime(columnValue);
s = t.Year.ToString() + checkDate(t.Month.ToString()) + checkDate(t.Day.ToString()) + checkDate(t.Hour.ToString()) + checkDate(t.Minute.ToString()) + checkDate(t.Second.ToString());
s = "to_date('" + s + "', '%Y%m%d%H%M%S')";
//return _marker.ToString() + s + _marker.ToString();
}
else if (odbcType == OdbcDBType.FoxPro)
{
DateTime t = Convert.ToDateTime(columnValue);
s = t.Month.ToString() + "/" + t.Day.ToString() + "/" + t.Year.ToString();
s = "{" + s + "}";
//return _marker.ToString() + s + _marker.ToString();
}
else
{
DateTime t = Convert.ToDateTime(columnValue);
s = "'" + t.Month.ToString() + "/" + t.Day.ToString() + "/" + t.Year.ToString() + "'";
}
return s;
}
else if (Type.GetType(type).Equals(typeof(Byte[])))
{
StringBuilder builder = new StringBuilder("0x"); // 16进制、Oracle里的Binary没有测试。
foreach (Byte b in (Byte[])columnValue)
{
string tmp = Convert.ToString(b, 16);
if (tmp.Length < 2)
tmp = "0" + tmp;
builder.Append(tmp);
}
return builder.ToString();
}
else
{
return columnValue.ToString();
}
}
private String CreateOriginalOdbcWherePart(OdbcDBType type)
{
StringBuilder originalWherePartSB = new StringBuilder();
List<DataRow> columnsList = GetWhereColumnsList();
if (columnsList == null || columnsList.Count == 0)
{ return null; }
foreach (DataRow schemaRow in columnsList)
{
String columnName = (String)schemaRow["ColumnName"];
String columnType = schemaRow["DataType"].ToString();
//String columnTypeName = schemaRow["DataTypeName"].ToString();
Object originalValue = _row[columnName, DataRowVersion.Original];
if (originalValue == null || originalValue == DBNull.Value)
{ originalWherePartSB.Append(columnName + " is null and "); }
else
{
originalWherePartSB.Append(columnName + " = "
+ MarkOdbc(columnType, TransformMarkerInColumnValue(columnType, originalValue), type) + " and ");
//originalWherePartSB.Append(Quote(columnName) + " = "
// + Mark(columnTypeName, columnType, TransformMarkerInColumnValue(columnType, originalValue)) + " and ");
}
}
originalWherePartSB.Remove(originalWherePartSB.Length - 5, 5);
return originalWherePartSB.ToString();
}
public String GetDeleteOdbc(string TN, OdbcDBType type)
{
if (_schema == null)
{
String message = SysMsg.GetSystemMessage(((DataModule)_updateComonent.OwnerComp).Language, "Srvtools", "UpdateComponent", "msg_SchemaIsNull");
throw new ArgumentException(String.Format(message, _updateComonent.Name));
}
if (_deleteSQL != null) return _deleteSQL;
CreateTableName(); CreateSchemaName();
String originalWherePart = CreateOriginalOdbcWherePart(type);
if (originalWherePart == null || originalWherePart.Length == 0)
{ return null; }
//String deleteSQL = "delete from " + TableName + " where " + originalWherePart;
String deleteSQL = "";
if (_tableName != "")
deleteSQL = "delete from " + _tableName + " where " + originalWherePart;
else
deleteSQL = "delete from " + TN + " where " + originalWherePart;
_deleteSQL = deleteSQL;
return _deleteSQL;
}
private String CreateOdbcUpdatePart(OdbcDBType type)
{
// create updateColumns.
StringBuilder updatePartSB = new StringBuilder();
List<DataRow> columnsList = GetOracleUpdateColumnsList();
foreach (DataRow schemaRow in columnsList)
{
String columnName = (String)schemaRow["ColumnName"];
String columnType = schemaRow["DataType"].ToString();
//String columnTypeName = schemaRow["DataTypeName"].ToString();
Object currentValue = _row[columnName, DataRowVersion.Current];
Object originalValue = _row[columnName, DataRowVersion.Original];
//由原来的DBNull.Value和Null才带DefaultValue —> 任何时候都带DefaultValue
//if ((originalValue == null || originalValue == DBNull.Value) && (currentValue == null || currentValue == DBNull.Value))
//{
currentValue = GetColumnIOrUValue(columnName, columnType);
if ((currentValue == null || currentValue == DBNull.Value) &&
(originalValue == null || originalValue == DBNull.Value))
{
continue;
}
//}
//else
//{
if (currentValue.Equals(originalValue)) continue;
//}
if (columnType == "System.Byte[]")
{
if (currentValue != DBNull.Value && originalValue != DBNull.Value)
{
byte[] btc = (byte[])currentValue;
byte[] bto = (byte[])originalValue;
if (btc.Length == bto.Length)
{
bool blequal = true;
for (int i = 0; i < btc.Length; i++)
{
if (!btc[i].Equals(bto[i]))
{
blequal = false;
break;
}
}
if (blequal)
{
continue;
}
}
}
}
if (updatePartSB.Length > 0)
updatePartSB.Append(", ");
if (currentValue == null || currentValue == DBNull.Value)
{ updatePartSB.Append(columnName + " = null"); }
else
{
updatePartSB.Append(columnName + " = " + MarkOdbc(columnType, TransformMarkerInColumnValue(columnType, currentValue), type));
}
continue;
}
return updatePartSB.ToString();
}
public String GetWriteBackOdbcWherePart(OdbcDBType type)
{
StringBuilder wBWhereSQLSB = new StringBuilder();
List<DataRow> whereColumnsList = GetWhereColumnsList();
foreach (DataRow r in whereColumnsList)
{
String columnName = r["ColumnName"].ToString();
String columnType = r["DataType"].ToString();
//String columnTypeName = r["DataTypeName"].ToString();
Object columnValue = null;
if (_row.RowState == DataRowState.Deleted)
{ columnValue = _row[columnName, DataRowVersion.Original]; }
else
{ columnValue = _row[columnName, DataRowVersion.Current]; }
if (wBWhereSQLSB.Length > 0)
{ wBWhereSQLSB.Append(" and "); }
if (columnValue == null || columnValue == DBNull.Value)
{ wBWhereSQLSB.Append(columnName + "is null"); }
else
{ wBWhereSQLSB.Append(columnName + " = " + MarkOdbc(columnType, TransformMarkerInColumnValue(columnType, columnValue), type)); }
//{ wBWhereSQLSB.Append(Quote(columnName) + " = " + Mark(columnTypeName, columnType, TransformMarkerInColumnValue(columnType, columnValue))); }
}
return wBWhereSQLSB.ToString();
}
