internal IList executeStored(String storedProcedure, Serializable objeto, List <SqlParameter> extraParameters) { List <string> parameterNames = getProcedureParameterNames(storedProcedure); List <SqlParameter> parameters = new List <SqlParameter>(); extraParameters.ForEach(o => parameterNames.Remove(o.ParameterName)); extraParameters.ForEach(o => parameters.Add(o)); foreach (string parameterName in parameterNames) { object dataValue = objeto.getDataValue(parameterName); if (dataValue != null) { if (typeof(Serializable).IsAssignableFrom(dataValue.GetType())) { Serializable serializableProperty = (Serializable)dataValue; dataValue = serializableProperty.getPkValue(); } } DataBase.Instance.agregarParametro(parameters, parameterName, dataValue); } return(executeStored(storedProcedure, parameters)); }
private void completeSerializableObject(Serializable incompleteObject) { foreach (KeyValuePair <string, object> item in getPropertyValues(incompleteObject, true)) { completeProperty(incompleteObject, item.Key, item.Value, true); } }
//No setea valores null //Si se modifico la pk aunque sea natural no la updatea (usea ignora la pk) private void update(Serializable objeto, String primaryKeyPropertyName, String tableName, bool cascadeMode) { String updateQuery = "update " + tableName + " set "; List <SqlParameter> parametros = new List <SqlParameter>(); Dictionary <string, object> propertyValues = getPropertyValues(objeto); foreach (KeyValuePair <string, object> keyValuePair in propertyValues) { if (keyValuePair.Key != primaryKeyPropertyName) { String dataName = objeto.getMapFromKey(keyValuePair.Key); if (dataName != "") { bool isSerializableProperty = typeof(Serializable).IsAssignableFrom(keyValuePair.Value.GetType()); object parametro = keyValuePair.Value; if (isSerializableProperty) { Serializable serializableProperty = (Serializable)keyValuePair.Value; parametro = serializableProperty.getPropertyValue(serializableProperty.getIdPropertyName()); } updateQuery += dataName + "=@" + dataName + ","; DataBase.Instance.agregarParametro(parametros, "@" + dataName, parametro); } } } updateQuery = updateQuery.Remove(updateQuery.Length - 1); updateQuery += " where " + objeto.getMapFromKey(primaryKeyPropertyName) + "=" + objeto.getPkValue(); if (cascadeMode) { List <String> serializablePropertyNames = listSerializableProperties(objeto); foreach (KeyValuePair <string, object> keyValuePair in propertyValues) { //Aca supongo que no puede ser null if (serializablePropertyNames.Contains(keyValuePair.Key)) { Serializable serializableProperty = (Serializable)keyValuePair.Value; update(serializableProperty, serializableProperty.getIdPropertyName(), serializableProperty.getTableName(), cascadeMode); } } } DataBase.Instance.ejecutarConsulta(updateQuery, parametros); foreach (var item in objeto.getOneToManyPropertyNames()) { updateOneToManyProperty(objeto, item, cascadeMode); } }
private object selectById(object id, Type classType) { Serializable objeto = (Serializable)Activator.CreateInstance(classType); IList result = selectByProperty(objeto.getIdPropertyName(), id, classType); return((result.Count > 0) ? result[0] : null); }
private List <string> getOneToManyTables(Serializable objeto) { List <string> listTables = new List <string>(); objeto.getOneToManyPropertyNames(). ForEach(property => listTables.Add(objeto.getOneToManyTable(property))); return(listTables); }
internal IList selectAll(Type classType) { Serializable objeto = (Serializable)Activator.CreateInstance(classType); String tableName = objeto.getTableName(); List <SqlParameter> parameters = new List <SqlParameter>(); String selectQuery = "select * from " + tableName; return(executeAutoMappedSelect(selectQuery, parameters, classType)); }
private void delete(Serializable objeto, String primaryKeyPropertyName, String tableName) { List <SqlParameter> parameters = new List <SqlParameter>(); string expected = "@" + objeto.getMapFromKey(primaryKeyPropertyName); DataBase.Instance.agregarParametro(parameters, expected, objeto.getPkValue()); String deleteQuery = "delete from " + tableName + " where " + objeto.getMapFromKey(primaryKeyPropertyName) + "=" + expected; DataBase.Instance.ejecutarConsulta(deleteQuery, parameters); }
private void completeOneToManyProperty(Serializable incompleteObject, string propertyName) { Type containingTypeOfProperty = incompleteObject.getOneToManyPropertyType(propertyName); Serializable containingTypeOfPropertyInstance = (Serializable)Activator.CreateInstance(containingTypeOfProperty); string intermediateTable = incompleteObject.getOneToManyTable(propertyName); string currentForeignKey = incompleteObject.getOneToManyFk(propertyName); string currentPrimaryKey = incompleteObject.getOneToManyPk(propertyName); object currentIdValue = incompleteObject.getPkValue(); string expected = "@" + currentPrimaryKey; List <SqlParameter> sqlParameters = new List <SqlParameter>(); DataBase.Instance.agregarParametro(sqlParameters, expected, currentIdValue); string query = "select * from " + intermediateTable + " "; string conditionQuery = "where " + currentPrimaryKey + "=" + expected; if (containingTypeOfPropertyInstance.getTableName() != intermediateTable) { query += "inner join " + containingTypeOfPropertyInstance.getTableName() + " on(" + intermediateTable + "." + currentForeignKey + "=" + containingTypeOfPropertyInstance.getTableName() + "." + containingTypeOfPropertyInstance. getMapFromKey(containingTypeOfPropertyInstance.getIdPropertyName()) + ") "; } query += conditionQuery; Type listType = incompleteObject.getPropertyType(propertyName); object dummyList = Activator.CreateInstance(listType); //Asigno una lista vacia a la propiedad incompleteObject.GetType().GetProperty(propertyName). SetValue(incompleteObject, dummyList); foreach (var item in executeQuery(query, sqlParameters, containingTypeOfProperty)) { List <object> parameters = new List <object> { item }; incompleteObject.GetType().GetProperty(propertyName). PropertyType.GetMethod("Add"). Invoke(incompleteObject.getPropertyValue(propertyName), parameters.ToArray()); } }
private void updateOneToManyProperty(Serializable objeto, string propertyName, bool cascadeMode) { object propertyList = objeto.getPropertyValue(propertyName); //SI es cascade updateo cada elemento de la propiedad oneToMany if (cascadeMode) { foreach (var item in (IEnumerable)propertyList) { updateCascade((Serializable)item); } } object notExistentFkObjects = Activator.CreateInstance(objeto.getPropertyType(propertyName)); List <object> existentFks = new List <object>(); string expected = "@" + objeto.getOneToManyPk(propertyName); List <SqlParameter> sqlParameters = new List <SqlParameter>(); DataBase.Instance.agregarParametro(sqlParameters, expected, objeto.getPkValue()); string existentFksQuery = "select " + objeto.getOneToManyFk(propertyName) + " from " + objeto.getOneToManyTable(propertyName) + " where " + objeto.getOneToManyPk(propertyName) + "=" + expected; foreach (Dictionary <string, object> item in DataBase.Instance.ejecutarConsulta(existentFksQuery, sqlParameters)) { existentFks.Add(item[objeto.getOneToManyFk(propertyName)]); } foreach (var item in (IEnumerable)propertyList) { if (!existentFks.Contains(((Serializable)item).getPropertyValue(((Serializable)item).getIdPropertyName()))) { List <object> parameters = new List <object> { item }; notExistentFkObjects.GetType().GetMethod("Add").Invoke(notExistentFkObjects, parameters.ToArray()); } } //Seteo lista filtrada objeto.GetType().GetProperty(propertyName).SetValue(objeto, notExistentFkObjects); insertOneToManyProperty(objeto, propertyName, false); //Dejo todo como estaba objeto.GetType().GetProperty(propertyName).SetValue(objeto, propertyList); }
private void loadObjectsAndTypes() { Type[] types = Assembly.GetExecutingAssembly().GetTypes(); foreach (var type in types) { if (typeof(Serializable).IsAssignableFrom(type)) { if (type != typeof(Serializable)) { Serializable objeto = (Serializable)Activator.CreateInstance(type); dictionaryObjectsAndTypes.Add(type, objeto); } } } }
private object unSerialize(Dictionary <string, object> dictionary, Type modelClassType) { Serializable objeto = (Serializable)Activator.CreateInstance(modelClassType); Dictionary <string, object> dictionaryAux = copyDictionary(dictionary); foreach (String dataName in dictionary.Keys) { String propertyName = objeto.getMapFromVal(dataName); String keyToRemove = dataName; if (propertyName != "") { Type propertyType = objeto.getPropertyType(propertyName); bool isSerializable = typeof(Serializable).IsAssignableFrom(propertyType); if (dictionary[dataName].ToString() != "")//Esto esta hardcodeado para que no setee cosas en null { if (isSerializable) { Serializable propertyInstance = (Serializable)Activator.CreateInstance(propertyType); keyToRemove = propertyInstance.getMapFromKey(propertyInstance.getIdPropertyName()); object dataValue = dictionaryAux[dataName]; dictionaryAux.Remove(dataName); if (!dictionaryAux.ContainsKey(keyToRemove))//parchado porque rompio en un test diciendo que ya se habia insertado la clave { dictionaryAux.Add(keyToRemove, dataValue); } objeto.GetType().GetProperty(propertyName).SetValue(objeto, unSerialize(dictionaryAux, propertyType)); } else { object dataValue = getCastedValue(dictionary[dataName], objeto.getPropertyType(propertyName)); objeto.GetType().GetProperty(propertyName).SetValue(objeto, dataValue); } dictionaryAux.Remove(keyToRemove); } } } return(objeto); }
private void createOneToManyTables(Serializable objeto) { foreach (var item in objeto.getOneToManyPropertyNames()) { string tableName = objeto.getOneToManyTable(item); if (!existsTable(tableName)) { createOneToManyTable(objeto, item); } else { addOneToManyForeignKey(objeto.getMapFromKey(objeto.getIdPropertyName()), tableName, objeto.getTableName(), objeto.getOneToManyPk(item), objeto.getPropertyType(objeto.getIdPropertyName())); } } }
private void insertOneToManyProperty(Serializable objeto, string propertyName, bool cascade) { Type propertyType = objeto.getOneToManyPropertyType(propertyName); string propertyTable = ((Serializable)Activator.CreateInstance(propertyType)).getTableName(); string oneToManyTable = objeto.getOneToManyTable(propertyName); string expectedPk = "@" + objeto.getOneToManyPk(propertyName); foreach (var item in (IEnumerable)objeto.getPropertyValue(propertyName)) { Serializable serializableItem = (Serializable)item; string expectedFk = "@" + objeto.getOneToManyFk(propertyName); List <SqlParameter> sqlParameters = new List <SqlParameter>(); DataBase.Instance.agregarParametro(sqlParameters, expectedPk, objeto.getPkValue()); DataBase.Instance.agregarParametro(sqlParameters, expectedFk, serializableItem.getPkValue()); string query = ""; if (propertyTable == oneToManyTable) { if (cascade) { insert(serializableItem, serializableItem.getIdPropertyName(), propertyTable, cascade); } query = "update " + propertyTable + " set " + objeto.getOneToManyPk(propertyName) + "=" + expectedPk + "where " + objeto.getOneToManyFk(propertyName) + "=" + expectedFk; } else { query = "insert into " + oneToManyTable + "(" + objeto.getOneToManyPk(propertyName) + "," + objeto.getOneToManyFk(propertyName) + ") values(" + expectedPk + "," + expectedFk + ")"; } DataBase.Instance.ejecutarConsulta(query, sqlParameters); } }
internal IList selectByProperties(Dictionary <string, object> properties, Type classType) { Serializable objeto = (Serializable)Activator.CreateInstance(classType); List <SqlParameter> parameters = new List <SqlParameter>(); string selectQuery = "select * from " + objeto.getTableName() + " where "; foreach (KeyValuePair <string, object> property in properties) { string expected = "@" + objeto.getMapFromKey(property.Key); object pkPropertyValue = typeof(Serializable).IsAssignableFrom(property.Value.GetType()) ? ((Serializable)property.Value).getPkValue() : property.Value; DataBase.Instance.agregarParametro(parameters, expected, pkPropertyValue); selectQuery += objeto.getMapFromKey(property.Key) + "=" + expected + " and "; } selectQuery = selectQuery.Remove(selectQuery.Length - 5); return(executeAutoMappedSelect(selectQuery, parameters, classType)); }
private Dictionary <string, object> getPropertyValues(Serializable objeto, bool nullValuesPermited) { Dictionary <string, object> dictionary = new Dictionary <string, object>(); foreach (MemberInfo info in objeto.GetType().GetMembers()) { if (info.MemberType == MemberTypes.Property) { string propertyName = ((PropertyInfo)info).Name; object propertyValue = ((PropertyInfo)info).GetValue(objeto); if (propertyValue != null || nullValuesPermited) { dictionary.Add(propertyName, propertyValue); } } } return(dictionary); }
private void createIncompleteTable(Serializable objeto) { if (existsTable(objeto.getTableName())) { return; } string createQuery = "create table " + objeto.getTableName() + "("; foreach (var item in listProperties(objeto)) { if (!isSerializableProperty(item, objeto)) { String dataType = getDataTypeName(getPropertyType(item, objeto)); if (dataType != "" && objeto.getMapFromKey(item) != "") { createQuery += objeto.getMapFromKey(item) + " " + dataType; if (objeto.getIdPropertyName() == item) { createQuery += " not null primary key"; if (objeto.getPrimaryKeyType() == PrimaryKeyType.SURROGATE) { createQuery += " identity(1,1)"; } } createQuery += ","; } } } createQuery = createQuery.Remove(createQuery.Length - 1); createQuery += ")"; DataBase.Instance.ejecutarConsulta(createQuery); }
private void createOneToManyTable(Serializable objeto, string propertyName) { Type containingTypeOfProperty = objeto.getOneToManyPropertyType(propertyName); Serializable containingTypeOfPropertyInstance = (Serializable)Activator.CreateInstance(containingTypeOfProperty); string pkName = objeto.getOneToManyPk(propertyName); string tableName = objeto.getOneToManyTable(propertyName); string fkName = objeto.getOneToManyFk(propertyName); string pkDataTypeName = getDataTypeName(objeto.GetType(). GetProperty(objeto.getIdPropertyName()).PropertyType); string fkDataTypeName = getDataTypeName(containingTypeOfPropertyInstance.GetType(). GetProperty(containingTypeOfPropertyInstance. getIdPropertyName()).PropertyType); string createQuery = "create table " + tableName + "(" + pkName + " " + pkDataTypeName + " ," + fkName + " " + fkDataTypeName + ", " + "primary key(" + pkName + "," + fkName + "))"; DataBase.Instance.ejecutarConsulta(createQuery); }
internal void completeProperty(Serializable incompleteObject, string propertyName, object propertyValue, bool ignoreLazyProperty) { if (incompleteObject.getFetchType(propertyName) == FetchType.EAGER || !ignoreLazyProperty) { if (incompleteObject.isOneToManyProperty(propertyName)) { completeOneToManyProperty(incompleteObject, propertyName); } if (typeof(Serializable).IsAssignableFrom(incompleteObject.getPropertyType(propertyName)) && propertyValue != null) { Serializable serializableProperty = (Serializable)propertyValue; Type propertyType = serializableProperty.GetType(); object propertyId = serializableProperty.getPkValue(); serializableProperty = (Serializable)selectById(propertyId, propertyType); incompleteObject.GetType().GetProperty(propertyName).SetValue(incompleteObject, serializableProperty); } } }
internal void insert(Serializable objeto) { insert(objeto, objeto.getIdPropertyName(), objeto.getTableName(), false); }
internal void update(Serializable objeto) { update(objeto, objeto.getIdPropertyName(), objeto.getTableName(), false); }
private void createForeignKeys(Serializable objeto) { int serializablePropertyCounter = listProperties(objeto).Count(property => isSerializableProperty(property, objeto) && objeto.getMapFromKey(property) != ""); if (serializablePropertyCounter == 0) { return; } bool existsFkEqualToPk = listProperties(objeto).Exists(prop => objeto.getMapFromKey(prop) == objeto.getMapFromKey(objeto.getIdPropertyName()) && typeof(Serializable).IsAssignableFrom(getPropertyType(prop, objeto))); //Este es el caso en el que la pk sea una fk y que solo haya 1 fk if (serializablePropertyCounter == 1 && existsFkEqualToPk) { Type propertyType = getPropertyType(listProperties(objeto).Find(prop => isSerializableProperty(prop, objeto)), objeto); Serializable property = dictionaryObjectsAndTypes[propertyType]; DataBase.Instance.ejecutarConsulta("alter table " + objeto.getTableName() + " add foreign key(" + objeto.getMapFromKey(objeto.getIdPropertyName()) + ") references " + property.getTableName() + "(" + property.getMapFromKey(property.getIdPropertyName()) + ")"); return; } string alterQuery = "alter table " + objeto.getTableName() + " add "; string foreignKeys = ""; foreach (var item in listProperties(objeto)) { Type propertyType = getPropertyType(item, objeto); if (isSerializableProperty(item, objeto) && objeto.getMapFromKey(item) != "") { Serializable property = dictionaryObjectsAndTypes[propertyType]; Type idPropertyType = getPropertyType(property.getIdPropertyName(), property); string primaryKeyProperty = property.getMapFromKey(property.getIdPropertyName()); string foreignKeyProperty = objeto.getMapFromKey(item); string tableNameProperty = property.getTableName(); if (objeto.getMapFromKey(item) != objeto.getMapFromKey(objeto.getIdPropertyName()))//Si es la pk, ya se inserto { alterQuery += foreignKeyProperty + " " + getDataTypeName(idPropertyType) + ","; } foreignKeys += "foreign key(" + foreignKeyProperty + ") references " + tableNameProperty + "(" + primaryKeyProperty + "),"; } } foreignKeys = foreignKeys.Remove(foreignKeys.Length - 1); alterQuery += foreignKeys; DataBase.Instance.ejecutarConsulta(alterQuery); }
internal void delete(Serializable objeto) { delete(objeto, objeto.getIdPropertyName(), objeto.getTableName()); }
private bool isSerializableProperty(string propertyName, Serializable objeto) { return(typeof(Serializable).IsAssignableFrom(getPropertyType(propertyName, objeto))); }
internal IList executeStored(String storedProcedure, Serializable objeto) { return(executeStored(storedProcedure, objeto, new List <SqlParameter>())); }
private List <Dictionary <string, string> > getParsedQueryMethod(string methodName) { Serializable objeto = (Serializable)Activator.CreateInstance(getModelClassType()); List <Dictionary <string, string> > columnsAndConnectors = new List <Dictionary <string, string> >(); string unparsedQuery = methodName.Remove(0, 8);//methodName.Split(new string[] { "By" }, StringSplitOptions.None)[1]; List <char> enumerableUnparsedQuery = unparsedQuery.ToList(); int counter = 0; for (int i = 0; i < enumerableUnparsedQuery.Count; i++) { Dictionary <string, string> newDictionary = new Dictionary <string, string>(); string column = ""; string property = ""; if (i + 3 >= enumerableUnparsedQuery.Count) { property = unparsedQuery.Substring(counter, unparsedQuery.Length - counter);//unparsedQuery; column = property; char[] dummyArray = column.ToCharArray(); dummyArray[0] = property[0].ToString().ToLower()[0]; column = new string(dummyArray); column = objeto.getMapFromKey(column); if (column == "") { column = objeto.getMapFromKey(property); } newDictionary.Add("column", column); newDictionary.Add("connector", ""); columnsAndConnectors.Add(newDictionary); break; } if (enumerableUnparsedQuery[i] == 'O' && enumerableUnparsedQuery[i + 1] == 'r') { property = unparsedQuery.Substring(counter, i - counter);//unparsedQuery.Remove(i, unparsedQuery.Length - i); counter += property.Length + 2; column = property; char[] dummyArray = column.ToCharArray(); dummyArray[0] = property[0].ToString().ToLower()[0]; column = new string(dummyArray); column = objeto.getMapFromKey(column); if (column == "") { column = objeto.getMapFromKey(property); } newDictionary.Add("column", column); newDictionary.Add("connector", "or"); //unparsedQuery = unparsedQuery.Remove(0, i + 2); columnsAndConnectors.Add(newDictionary); } if (enumerableUnparsedQuery[i] == 'A' && enumerableUnparsedQuery[i + 1] == 'n' && enumerableUnparsedQuery[i + 2] == 'd') { property = unparsedQuery.Substring(counter, i - counter);//unparsedQuery.Remove(i, unparsedQuery.Length - i); counter += property.Length + 3; column = property; char[] dummyArray = column.ToCharArray(); dummyArray[0] = property[0].ToString().ToLower()[0]; column = new string(dummyArray); column = objeto.getMapFromKey(column); if (column == "") { column = objeto.getMapFromKey(property); } newDictionary.Add("column", column); newDictionary.Add("connector", "and"); //unparsedQuery = unparsedQuery.Remove(0, i + 3); columnsAndConnectors.Add(newDictionary); } } return(columnsAndConnectors); }
private Dictionary <string, object> getPropertyValues(Serializable objeto) { return(getPropertyValues(objeto, false)); }
internal void updateCascade(Serializable objeto) { update(objeto, objeto.getIdPropertyName(), objeto.getTableName(), true); }
internal void insertCascade(Serializable objeto) { insert(objeto, objeto.getIdPropertyName(), objeto.getTableName(), true); }
internal void completeProperty(string propertyName, Serializable incompleteObject) { completeProperty(incompleteObject, propertyName, incompleteObject.getPropertyValue(propertyName), false); }
private void insert(Serializable objeto, String primaryKeyPropertyName, String tableName, bool cascade) { String insertQuery = "insert into " + tableName + "("; String valuesString = " values ("; List <SqlParameter> parametros = new List <SqlParameter>(); Dictionary <string, object> propertyValues = getPropertyValues(objeto); foreach (KeyValuePair <string, object> keyValuePair in propertyValues) { if (keyValuePair.Key != primaryKeyPropertyName || objeto.getPrimaryKeyType() == PrimaryKeyType.NATURAL) { String dataName = objeto.getMapFromKey(keyValuePair.Key); if (dataName != "") { insertQuery += dataName + ","; valuesString += "@" + dataName + ","; bool isSerializableProperty = typeof(Serializable).IsAssignableFrom(keyValuePair.Value.GetType()); Serializable serializableProperty = isSerializableProperty ? (Serializable)keyValuePair.Value : null; if (cascade && isSerializableProperty) { insert(serializableProperty, serializableProperty.getIdPropertyName(), serializableProperty.getTableName(), true); } object parametro = isSerializableProperty ? serializableProperty.getPropertyValue( serializableProperty.getIdPropertyName()) : keyValuePair.Value; DataBase.Instance.agregarParametro(parametros, "@" + dataName, parametro); } } } string primaryKeyName = objeto.getMapFromKey(objeto.getIdPropertyName()); insertQuery = insertQuery.Remove(insertQuery.Length - 1); valuesString = valuesString.Remove(valuesString.Length - 1); insertQuery += ") " + "output inserted." + primaryKeyName + valuesString + ")"; object insertResult = DataBase.Instance.ejecutarConsulta( insertQuery, parametros)[0][primaryKeyName]; Type idType = objeto.getPropertyType(objeto.getIdPropertyName()); object idValue = null; if (typeof(Serializable).IsAssignableFrom(idType)) { idValue = objeto.getIdValue(); Serializable serializableId = (Serializable)idValue; serializableId.GetType().GetProperty(serializableId.getIdPropertyName()). SetValue(serializableId, getCastedValue(insertResult, serializableId. getPropertyType(serializableId.getIdPropertyName()))); } else { idValue = getCastedValue(insertResult, idType); } objeto.GetType().GetProperty(objeto.getIdPropertyName()).SetValue(objeto, idValue); foreach (var item in objeto.getOneToManyPropertyNames()) { insertOneToManyProperty(objeto, item, cascade); } }