/// <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()); }