public DbCommonCommand GetNewItemCommand()
{
string query;
if (m_linkConfig.LeftRelation == eRelation.Many && m_linkConfig.RightRelation == eRelation.Many) // n-n
{
query = String.Format("INSERT INTO {0} ({1}, {2}) VALUES (@{{LEFT_ID}}, @{{RIGHT_ID}})", m_linkConfig.LinkTable, m_linkConfig.LeftObjectIdField, m_linkConfig.RightObjectIdField);
}
else
{
if (m_linkConfig.LeftRelation == eRelation.One) // 1-n
{
query = String.Format("UPDATE {0} SET {1} = @{{LEFT_ID}} WHERE {2} = @{{RIGHT_ID}}", m_linkConfig.RightObject.TableName, m_linkConfig.LeftObjectIdField, m_linkConfig.RightObject.IdField);
}
else // n-1
{
query = String.Format("UPDATE {0} SET {1} = @{{RIGHT_ID}} WHERE {2} = @{{LEFT_ID}}", m_linkConfig.LeftObject.TableName, m_linkConfig.RightObjectIdField, m_linkConfig.LeftObject.IdField);
}
}
DbCommonCommand command = new DbCommonCommand(query, m_dbConnection);
command.AddParameter(DomainLinkBroker.LeftObjectIdParam, typeof(long));
command.AddParameter(DomainLinkBroker.RightObjectIdParam, typeof(long));
return command;
}
public DbCommonCommand GetDeleteItemsCommand()
{
string sql = String.Format("UPDATE {0} SET REMOVED = {1} WHERE {2} IN (@{{ID}})", m_objectConfig.TableName, m_dbConnection.GetTypeValue(true), m_objectConfig.IdField);
DbCommonCommand command = new DbCommonCommand(sql, m_dbConnection);
command.AddParameter("ID", typeof(long), true);
return command;
}
public DbCommonCommand GetLoadItemsCommand()
{
StringBuilder query = new StringBuilder();
query.AppendFormat("SELECT {0} as ID", m_objectConfig.IdField);
foreach (DomainPropertyConfig property in m_objectConfig.Property)
{
query.AppendFormat(", {0} AS {1}", m_dbConnection.GetReadTypeString(property.DataType, property.FieldName), property.FieldName);
}
query.AppendFormat(" FROM {0} WHERE REMOVED = {1} AND {2} IN(@{{ID}})", m_objectConfig.TableName, m_dbConnection.GetTypeValue(false), m_objectConfig.IdField);
DbCommonCommand command = new DbCommonCommand(query.ToString(), m_dbConnection);
command.AddParameter("ID", typeof(long), true);
return command;
}
private DbCommonCommand CreateRemoveObjectCommand()
{
const string sql = "DELETE FROM ORM_CONFIG_OBJECT WHERE ID_ORM_CONFIG_OBJECT = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
return command;
}
private DbCommonCommand CreateNewPropertyCommand()
{
const string sql = "INSERT INTO ORM_CONFIG_PROPERTY (ID_ORM_CONFIG_PROPERTY, CODE, CODE_NAME, DESCRIPTION, ID_ORM_CONFIG_OBJECT, DATA_TYPE, ASSEMBLY_FILE, FIELD_NAME, DEFAULT_VALUE, FIELD_LENGTH) VALUES (@{ID}, @{CODE}, @{CODE_NAME}, @{DESCRIPTION}, @{ID_ORM_CONFIG_OBJECT}, @{DATA_TYPE}, @{ASSEMBLY_FILE}, @{FIELD_NAME}, @{DEFAULT_VALUE}, @{FIELD_LENGTH})";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE", typeof(string));
command.AddParameter("CODE_NAME", typeof(string));
command.AddParameter("DESCRIPTION", typeof(string));
command.AddParameter("ID_ORM_CONFIG_OBJECT", typeof(long));
command.AddParameter("DATA_TYPE", typeof(string));
command.AddParameter("ASSEMBLY_FILE", typeof(string));
command.AddParameter("FIELD_NAME", typeof(string));
DbCommonCommandParameter defaultValueParam = command.AddParameter("DEFAULT_VALUE", typeof(object));
defaultValueParam.AllowNull = true;
command.AddParameter("FIELD_LENGTH", typeof(int));
return command;
}
private DbCommonCommand CreateNewObjectQueryCommand()
{
string sql = String.Format("INSERT INTO ORM_CONFIG_QUERY (ID_ORM_CONFIG_QUERY, CODE, OBJECT_TYPE, SOURCE, NOTES, REMOVED) VALUES (@{{ID}}, @{{CODE}}, @{{OBJECT_TYPE}}, @{{SOURCE}}, @{{NOTES}}, {0})", m_connection.GetTypeValue(false));
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE", typeof(string));
command.AddParameter("OBJECT_TYPE", typeof(string));
command.AddParameter("SOURCE", typeof(object));
command.AddParameter("NOTES", typeof(string));
return command;
}
private DbCommonCommand CreateNewObjectCommand()
{
const string sql = "INSERT INTO ORM_CONFIG_OBJECT (ID_ORM_CONFIG_OBJECT, CODE, CODE_NAME, DESCRIPTION, TABLE_NAME, ID_FIELD) VALUES (@{ID}, @{CODE}, @{CODE_NAME}, @{DESCRIPTION}, @{TABLE_NAME}, @{ID_FIELD})";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE", typeof(string));
command.AddParameter("CODE_NAME", typeof(string));
command.AddParameter("DESCRIPTION", typeof(string));
command.AddParameter("TABLE_NAME", typeof(string));
command.AddParameter("ID_FIELD", typeof(string));
return command;
}
private DbCommonCommand CreateNewLinkCommand()
{
const string sql = "INSERT INTO ORM_CONFIG_LINK(ID_ORM_CONFIG_LINK, CODE, LEFT_RELATION, RIGHT_RELATION, ID_LEFT_OBJECT, ID_RIGHT_OBJECT, LINK_TABLE_NAME, LEFT_ID_FIELD, RIGHT_ID_FIELD) VALUES(@{ID}, @{CODE}, @{LEFT_RELATION}, @{RIGHT_RELATION}, @{ID_LEFT_OBJECT}, @{ID_RIGHT_OBJECT}, @{LINK_TABLE_NAME}, @{LEFT_ID_FIELD}, @{RIGHT_ID_FIELD})";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE", typeof(string));
command.AddParameter("LEFT_RELATION", typeof(string));
command.AddParameter("RIGHT_RELATION", typeof(string));
command.AddParameter("ID_LEFT_OBJECT", typeof(long));
command.AddParameter("ID_RIGHT_OBJECT", typeof(long));
command.AddParameter("LINK_TABLE_NAME", typeof(string));
command.AddParameter("LEFT_ID_FIELD", typeof(string));
command.AddParameter("RIGHT_ID_FIELD", typeof(string));
return command;
}
private DbCommonCommand CreateEditPropertyCommand()
{
const string sql = "UPDATE ORM_CONFIG_PROPERTY SET CODE_NAME = @{CODE_NAME}, DESCRIPTION = @{DESCRIPTION}, DEFAULT_VALUE = @{DEFAULT_VALUE} WHERE ID_ORM_CONFIG_PROPERTY = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE_NAME", typeof(string));
command.AddParameter("DESCRIPTION", typeof(string));
command.AddParameter("DEFAULT_VALUE", typeof(object));
return command;
}
public DbCommonCommand GetNewItemCommand()
{
StringBuilder query = new StringBuilder();
query.AppendFormat("INSERT INTO {0} ({1}", m_objectConfig.TableName, m_objectConfig.IdField);
foreach (DomainPropertyConfig property in m_objectConfig.Property)
query.AppendFormat(", {0}", property.FieldName);
query.Append(") VALUES (@{ID} ");
foreach (DomainPropertyConfig property in m_objectConfig.Property)
query.AppendFormat(", @{{{0}}}", property.Code);
query.Append(")");
DbCommonCommand command = new DbCommonCommand(query.ToString(), m_dbConnection);
command.AddParameter("ID", typeof(long));
foreach (DomainPropertyConfig property in m_objectConfig.Property)
command.AddParameter(property.Code, property.DataType);
return command;
}
private DbCommonCommand CreateEditObjectCommand()
{
const string sql = "UPDATE ORM_CONFIG_OBJECT SET CODE_NAME = @{CODE_NAME}, DESCRIPTION = @{DESCRIPTION} WHERE ID_ORM_CONFIG_OBJECT = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE_NAME", typeof(string));
command.AddParameter("DESCRIPTION", typeof(string));
return command;
}
private DbCommonCommand CreateEditLinkCommand()
{
const string sql = "UPDATE ORM_CONFIG_LINK SET IS_LEFT_TO_RIGHT_ACTIVE = @{IS_LEFT_TO_RIGHT_ACTIVE}, IS_RIGHT_TO_LEFT_ACTIVE = @{IS_RIGHT_TO_LEFT_ACTIVE}, LEFT_COLLECTION_NAME = @{LEFT_COLLECTION_NAME}, RIGHT_COLLECTION_NAME = @{RIGHT_COLLECTION_NAME}, LEFT_TO_RIGHT_DESCRIPTION = @{LEFT_TO_RIGHT_DESCRIPTION}, RIGHT_TO_LEFT_DESCRIPTION = @{RIGHT_TO_LEFT_DESCRIPTION} WHERE ID_ORM_CONFIG_LINK = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("IS_LEFT_TO_RIGHT_ACTIVE", typeof(bool));
command.AddParameter("IS_RIGHT_TO_LEFT_ACTIVE", typeof(bool));
command.AddParameter("LEFT_COLLECTION_NAME", typeof(string));
command.AddParameter("RIGHT_COLLECTION_NAME", typeof(string));
command.AddParameter("LEFT_TO_RIGHT_DESCRIPTION", typeof(string));
command.AddParameter("RIGHT_TO_LEFT_DESCRIPTION", typeof(string));
return command;
}
public DbCommonCommand GetLoadLinkedItemsCommand()
{
StringBuilder query = new StringBuilder();
foreach (DomainLinkConfig link in m_links.Values)
{
if (link.LeftRelation == eRelation.Many && link.RightRelation == eRelation.Many)// n-n
{
if (link.LeftObject == m_objectConfig)
{
query.AppendFormat("SELECT LT.{0} AS LEFT_ID, LT.{1} AS RIGHT_ID, {2} AS LINK_CODE FROM {3} LT, {4} OT WHERE LT.{0} IN (@{{ID}}) AND LT.{1} = OT.{5} AND OT.REMOVED = {6}",
link.LeftObjectIdField, link.RightObjectIdField, m_dbConnection.GetTypeValue(link.Code), link.LinkTable, link.RightObject.TableName, link.RightObject.IdField, m_dbConnection.GetTypeValue(false));
}
else
{
query.AppendFormat("SELECT LT.{0} AS LEFT_ID, LT.{1} AS RIGHT_ID, {2} AS LINK_CODE FROM {3} LT, {4} OT WHERE LT.{1} IN (@{{ID}}) AND LT.{0} = OT.{5} AND OT.REMOVED = {6}",
link.LeftObjectIdField, link.RightObjectIdField, m_dbConnection.GetTypeValue(link.Code), link.LinkTable, link.LeftObject.TableName, link.LeftObject.IdField, m_dbConnection.GetTypeValue(false));
}
}
else
{
throw new NotImplementedException();
}
}
DbCommonCommand command = new DbCommonCommand(query.ToString(), m_dbConnection);
command.AddParameter("ID", typeof(long), true);
return command;
}
public DbCommonCommand GetSaveItemCommand()
{
StringBuilder query = new StringBuilder();
query.AppendFormat("UPDATE {0} SET ", m_objectConfig.TableName);
bool isFirst = true;
foreach (DomainPropertyConfig property in m_objectConfig.Property)
{
if (isFirst == false)
query.Append(", ");
query.AppendFormat("{0} = @{{{1}}}", property.FieldName, property.Code);
isFirst = false;
}
query.AppendFormat(" WHERE {0} = @{{ID}}", m_objectConfig.IdField);
DbCommonCommand command = new DbCommonCommand(query.ToString(), m_dbConnection);
command.AddParameter("ID", typeof(long));
foreach (DomainPropertyConfig property in m_objectConfig.Property)
{
command.AddParameter(property.Code, property.DataType);
}
return command;
}
private DbCommonCommand CreateRemoveObjectQueryCommand()
{
string sql = String.Format("UPDATE ORM_CONFIG_QUERY SET REMOVED = {0} WHERE ID_ORM_CONFIG_QUERY = @{{CODE}}", m_connection.GetTypeValue(true));
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
return command;
}
private DbCommonCommand CreateRemovePropertyCommand()
{
const string sql = "DELETE FROM ORM_CONFIG_PROPERTY WHERE ID_ORM_CONFIG_PROPERTY = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
return command;
}
private DbCommonCommand CreateEditObjectQueryCommand()
{
const string sql = "UPDATE ORM_CONFIG_QUERY SET CODE = @{CODE}, OBJECT_TYPE = @{OBJECT_TYPE}, SOURCE = @{SOURCE}, NOTES = @{NOTES} WHERE ID_ORM_CONFIG_QUERY = @{ID}";
DbCommonCommand command = new DbCommonCommand(sql, m_connection);
command.AddParameter("ID", typeof(long));
command.AddParameter("CODE", typeof(string));
command.AddParameter("OBJECT_TYPE", typeof(string));
command.AddParameter("SOURCE", typeof(object));
command.AddParameter("NOTES", typeof(string));
return command;
}