private static string BuildSqlString(Type entityType, string queryClause)
{
EntityMetadata entityMetaData = ExtensibleEntity.GetEntityMetadata(entityType);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
FieldMetadata[] fields = entityMetaData.PreDefinedFields;
for (int i = 0; i < fields.Length; i++)
{
sb.Append(string.Format("P.{0} AS {1}", fields[i].ColumnName, fields[i].Name));
if (i != fields.Length - 1)
{
sb.Append(",");
}
}
fields = entityMetaData.ExtensionFields;
if (fields.Length > 0)
{
sb.Append(",");
}
for (int i = 0; i < fields.Length; i++)
{
sb.Append(string.Format("E{0}.Value AS {1}", i, fields[i].Name));
if (i != fields.Length - 1)
{
sb.Append(",");
}
}
sb.Append(string.Format(" FROM {0} AS P", entityMetaData.SourceName));
string extensionTableName = string.IsNullOrEmpty(entityMetaData.ExtensionTable) ? Consts.DefExtensionTable : entityMetaData.ExtensionTable;
if (fields.Length > 0)
{
for (int i = 0; i < fields.Length; i++)
{
sb.Append(string.Format(" LEFT OUTER JOIN {0} AS E{1}", extensionTableName, i));
sb.Append(string.Format(" ON P.{0} = E{1}.RecordId AND E{1}.FieldId = {2}",
entityMetaData.Key, i, SqlFormat.ToSqlValueString(fields[i].Id)));
}
}
if (!string.IsNullOrEmpty(queryClause))
{
sb.Append(" WHERE ").Append(queryClause);
}
return(sb.ToString());
}
protected IEnumerable <string> GenerateDeleteScripts(IExtDataRecord dataRow, EntityMetadata rowMetaData)
{
List <string> scripts = new List <string>();
string[] keyFields = rowMetaData.KeyFieldNames;
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM ").Append(rowMetaData.SourceName).
Append(" WHERE ").Append(GetConditionClause(dataRow, rowMetaData.KeyFieldNames));
scripts.Add(sb.ToString());
sb = new StringBuilder();
string extensionTable = string.IsNullOrEmpty(rowMetaData.ExtensionTable) ? Consts.DefExtensionTable : rowMetaData.ExtensionTable;
sb.Append("DELETE FROM ").Append(extensionTable).
Append(" WHERE RecordId=").
Append(SqlFormat.ToSqlValueString(dataRow.GetValue(rowMetaData.Key)));
scripts.Add(sb.ToString());
return(scripts);
}
private string GetConditionClause(IExtDataRecord dataRow, string[] conditionFields)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < conditionFields.Length; i++)
{
object value = dataRow.GetValue(conditionFields[i]);
string op = "=";
if (value == null || value is DBNull)
{
op = " IS ";
}
sb.Append(conditionFields[i]).Append(op).Append(SqlFormat.ToSqlValueString(value));
if (i != conditionFields.Length - 1)
{
sb.Append(" AND ");
}
}
return(sb.ToString());
}
private static string ToSqlClause(KeyValuePair <string, object>[] conditions)
{
if (conditions == null)
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < conditions.Length; i++)
{
string op = (conditions[i].Value == null || conditions[i].Value is DBNull)?" IS ":"=";
sb.Append(string.Format("{0}{1}{2}",
SqlFormat.EscapeSql(conditions[i].Key), op,
SqlFormat.ToSqlValueString(conditions[i].Value)));
if (i != conditions.Length - 1)
{
sb.Append(" AND ");
}
}
return(sb.ToString());
}
protected IEnumerable <string> GenerateInsertScripts(IExtDataRecord dataRow, EntityMetadata rowMetaData)
{
List <string> scripts = new List <string>();
FieldMetadata[] preDefFields = rowMetaData.PreDefinedFields;
FieldMetadata[] extFields = rowMetaData.ExtensionFields;
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(rowMetaData.SourceName).Append(" ( ");
bool addFlag = false;
for (int i = 0; i < preDefFields.Length; i++)
{
object value = null;
if (!dataRow.TryGetValue(preDefFields[i].Name, out value))
{
continue; //value not exists. Insert the least values
}
if (addFlag)
{
sb.Append(", ");
}
sb.Append(preDefFields[i].ColumnName);
addFlag = true;
}
sb.Append(") VALUES ( ");
addFlag = false;
for (int i = 0; i < preDefFields.Length; i++)
{
object value = null;
if (!dataRow.TryGetValue(preDefFields[i].Name, out value))
{
continue; //value not exists. Insert the least values
}
if (addFlag)
{
sb.Append(", ");
}
sb.Append(SqlFormat.ToSqlValueString(value));
addFlag = true;
}
sb.Append(")");
scripts.Add(sb.ToString());
if (extFields == null || extFields.Length == 0)
{
return(scripts);
}
string recordIdStringValue = SqlFormat.ToSqlValueString(dataRow.GetValue(rowMetaData.Key));
string extensionTable = string.IsNullOrEmpty(rowMetaData.ExtensionTable) ? Consts.DefExtensionTable : rowMetaData.ExtensionTable;
foreach (FieldMetadata extField in extFields)
{
object value = null;
if (!dataRow.TryGetValue(extField.Name, out value))
{
continue;
}
value = TypeConvert.ChangeType <string>(value);
sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(extensionTable).
Append("(RecordId, FieldId, Value) VALUES (").Append(recordIdStringValue).
Append(",").Append(SqlFormat.ToSqlValueString(extField.Id)).
Append(",").Append(SqlFormat.ToSqlValueString(value)).Append(")");
scripts.Add(sb.ToString());
}
return(scripts);
}
protected IEnumerable <string> GenerateUpdateScripts(IExtDataRecord dataRow, EntityMetadata rowMetaData, IEnumerable <string> dirtyFields)
{
List <string> scripts = new List <string>();
List <string> preDefDirtyFields = new List <string>();
List <string> extDirtyFields = new List <string>();
foreach (string dirtyField in dirtyFields)
{
if (!rowMetaData.ContainsField(dirtyField))
{
continue;
}
if (!rowMetaData[dirtyField].IsExtension)
{
preDefDirtyFields.Add(dirtyField);
}
else
{
extDirtyFields.Add(dirtyField);
}
}
//update predefined fields
if (preDefDirtyFields.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE ").Append(rowMetaData.SourceName).Append(" SET ");
for (int i = 0; i < preDefDirtyFields.Count; i++)
{
sb.Append(preDefDirtyFields[i]).Append(" = ").
Append(SqlFormat.ToSqlValueString(dataRow.GetValue(preDefDirtyFields[i])));
if (i != preDefDirtyFields.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(" WHERE ").Append(GetConditionClause(dataRow, rowMetaData.KeyFieldNames));
scripts.Add(sb.ToString());
}
//uppdate extension values
if (extDirtyFields.Count > 0)
{
string recordIdStringValue = SqlFormat.ToSqlValueString(dataRow.GetValue(rowMetaData.Key));
string extensionTable = string.IsNullOrEmpty(rowMetaData.ExtensionTable) ? Consts.DefExtensionTable : rowMetaData.ExtensionTable;
foreach (string extFieldName in extDirtyFields)
{
object value = dataRow.GetValue(extFieldName);
value = TypeConvert.ChangeType <string>(value);
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM ").Append(extensionTable).
Append(" WHERE RecordId=").Append(recordIdStringValue).
Append(" AND FieldId=").Append(SqlFormat.ToSqlValueString(rowMetaData[extFieldName].Id));
scripts.Add(sb.ToString());
sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(extensionTable).
Append("(RecordId, FieldId, Value ) VALUES (").Append(recordIdStringValue).
Append(",").Append(SqlFormat.ToSqlValueString(rowMetaData[extFieldName].Id)).
Append(",").Append(SqlFormat.ToSqlValueString(value)).Append(")");
scripts.Add(sb.ToString());
}
}
//updates extension fields
return(scripts);
}