/// <summary>
/// It builds this part of insert command: (attribute_name1, attribute_name2...) VALUES (value1,value2...)
/// </summary>
/// <param name="tableName">Name of table in which record is inserted</param>
/// <param name="modelEntity">Entity (record) that is being inserted</param>
/// <returns>Part of INSERT command text</returns>
private string BuildVariableCommandText(string tableName, T modelEntity)
{
StringBuilder stringBuilderColumnNames = new StringBuilder(WHITE_SPACE + OPENNING_BRACKET + WHITE_SPACE);
StringBuilder stringBuilderCorespondingValues = new StringBuilder(WHITE_SPACE + OPENNING_BRACKET + WHITE_SPACE);
Type entityType = modelEntity.GetType();
// Using reflection get all properties of entity that is being inserted, because
// it corresponds with attributes of table with tableName in database
PropertyInfo[] propertiesInfo = entityType.GetProperties();
// Take all properties, accept for primary key, because it is autoincrement and doesn't need to be inserted
propertiesInfo = propertiesInfo.Where(x => x.Name.CompareTo(modelEntity.GetPrimaryKeyPropertyNames().First()) != 0).ToArray();
// Take first n-1 values, because of the comma at the end, and build
// both parts of command at once
for (int i = 0; i < propertiesInfo.Length - 1; ++i)
{
stringBuilderColumnNames.Append(propertiesInfo[i].Name + COMMA);
stringBuilderCorespondingValues.Append(AttributeValueHelper.IfStringDoQuotaiton(propertiesInfo[i].GetValue(modelEntity)) + COMMA);
}
// Add last piece (attibute and its value)
stringBuilderColumnNames.Append(propertiesInfo.Last().Name + CLOSING_BRACKET);
stringBuilderCorespondingValues.Append(AttributeValueHelper.IfStringDoQuotaiton(propertiesInfo.Last().GetValue(modelEntity)) + CLOSING_BRACKET);
// Put column names and its values together
stringBuilderColumnNames.Append(" VALUES " + stringBuilderCorespondingValues.ToString());
return(stringBuilderColumnNames.ToString());
}
private string BuildVariableCommandText(T modelEntity)
{
StringBuilder stringBuilder = new StringBuilder(WHITE_SPACE + "SET" + WHITE_SPACE);
Type entityType = modelEntity.GetType();
PropertyInfo[] propertiesInfo = entityType.GetProperties();
for (int i = 0; i < propertiesInfo.Length - 1; ++i)
{
stringBuilder.Append(propertiesInfo[i].Name + EQUALS + AttributeValueHelper.IfStringDoQuotaiton(propertiesInfo[i].GetValue(modelEntity)) + WHITE_SPACE + COMMA);
}
stringBuilder.Append(propertiesInfo.Last().Name + EQUALS + AttributeValueHelper.IfStringDoQuotaiton(propertiesInfo.Last().GetValue(modelEntity)) + WHITE_SPACE);
string whereExpression = DbCommand <T> .BuildWhereExpression(modelEntity, modelEntity.GetPrimaryKeyPropertyNames());
return(stringBuilder.Append(whereExpression).ToString());
}
private string BuildVariableCommandText(string tableName, T modelEntity)
{
StringBuilder stringBuilderColumnNames = new StringBuilder(WHITE_SPACE + OPENNING_BRACKET + WHITE_SPACE);
StringBuilder stringBuilderCorespondingValues = new StringBuilder(WHITE_SPACE + OPENNING_BRACKET + WHITE_SPACE);
Type entityType = modelEntity.GetType();
PropertyInfo[] propertiesInfo = entityType.GetProperties();
propertiesInfo = propertiesInfo.Where(x => x.Name.CompareTo(attributeNameLastAutoIncrement) != 0).ToArray();
for (int i = 0; i < propertiesInfo.Length; ++i)
{
stringBuilderColumnNames.Append(propertiesInfo[i].Name + COMMA);
stringBuilderCorespondingValues.Append(AttributeValueHelper.IfStringDoQuotaiton(propertiesInfo[i].GetValue(modelEntity)) + COMMA);
}
stringBuilderColumnNames.Append(attributeNameLastAutoIncrement + CLOSING_BRACKET);
stringBuilderCorespondingValues.Append("LAST_INSERT_ID() " + CLOSING_BRACKET);
stringBuilderColumnNames.Append(" VALUES " + stringBuilderCorespondingValues.ToString());
return(stringBuilderColumnNames.ToString());
}
/// <summary>
/// Every sql command can have WHERE expression, and it si similar in all it's uses. Like:
/// DELETE FROM table_name WHERE some_attribute=some_value and some_other_attribute=some_other_value...
/// </summary>
/// <param name="entity">Entity that is used to build WHERE expression</param>
/// <param name="attributesName">Names of attributes to include in WHERE expression</param>
/// <returns>Built part of command text, that is, WHERE expression</returns>
protected static string BuildWhereExpression(T entity, string[] attributesName)
{
StringBuilder stringBuilder = new StringBuilder(" WHERE ");
// Use reflection to get type of entity
Type entityType = entity.GetType();
// Take last attribute from those sent, because it doesn't need to have ','sign after it, it is end
// of where expression
string lastAttribute = attributesName.Last();
attributesName = attributesName.TakeWhile(x => x != lastAttribute).ToArray();
foreach (string key in attributesName)
{
// Get values of attributes in enitity and append "AND" after it
stringBuilder.Append(WHITE_SPACE + key + EQUALS_SIGN + AttributeValueHelper.IfStringDoQuotaiton(entityType.GetProperty(key).GetValue(entity)) + " AND");
}
// Add last attribute with no "AND" after
stringBuilder.Append(WHITE_SPACE + lastAttribute + EQUALS_SIGN + AttributeValueHelper.IfStringDoQuotaiton(entityType.GetProperty(lastAttribute).GetValue(entity)));
return(stringBuilder.ToString());
}
