public List <DskWor00> RunQuery(string query, params OleDbParameter[] parameters) { using (var connection = new OleDbConnection(connectionString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = query.ToString(); foreach (var oleDbParameter in parameters) { command.Parameters.Add(oleDbParameter); } var reader = command.ExecuteReader(); var entities = new List <DskWor00>(); while (reader.Read()) { DskWor00 entity = Activator.CreateInstance <DskWor00>(); foreach (var propertyModel in propertyModels) { propertyModel.PropertyInfo.SetValue(entity, (reader[propertyModel.PropertyName] is DBNull ? null : reader[propertyModel.PropertyName])); } entities.Add(entity); } return(entities); } }
public int Remove(DskWor00 entity) { var primaryKeys = propertyModels.Where(property => property.IsPrimaryKey); StringBuilder deleteStatment = new StringBuilder($"DELETE FROM {tableName}"); List <string> whereParts = new List <string>(); List <OleDbParameter> oleDbParameters = new List <OleDbParameter>(); List <string> sqlParameterName = new List <string>(); foreach (var propety in primaryKeys) { var parmeterName = propety.ColumnName; whereParts.Add(propety.ColumnName + "=?"); var parameterValue = propety.PropertyInfo.GetValue(entity); oleDbParameters.Add(new OleDbParameter(parmeterName, parameterValue)); } deleteStatment.Append(" WHERE " + string.Join(" AND ", whereParts)); using (var connection = new OleDbConnection(connectionString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = deleteStatment.ToString(); foreach (var sqlParameter in oleDbParameters) { command.Parameters.Add(sqlParameter); } return(command.ExecuteNonQuery()); } }
public int Add(DskWor00 entity) { StringBuilder insertStatment = new StringBuilder($@"INSERT INTO {tableName}.{schema} (#columns#) VALUES (#values#)"); var columns = new List <string>(); var oleDbParameters = new List <OleDbParameter>(); var valueNames = new List <string>(); foreach (var property in propertyModels) { if (property.PropertyInfo.GetValue(entity) == null && property.IsNullable) { continue; } if (property.IsComputed) { continue; } columns.Add(property.ColumnName); var propertyValue = property.PropertyInfo.GetValue(entity); if (property.PropertyInfo.PropertyType == typeof(string)) { propertyValue = ((string)property.PropertyInfo.GetValue(entity)).ToIranSystem(); } valueNames.Add("?"); oleDbParameters.Add(new OleDbParameter(property.ColumnName, propertyValue)); } insertStatment.Replace("#columns#", string.Join(",", columns)); insertStatment.Replace("#values#", string.Join(",", valueNames)); using (var connection = new OleDbConnection(connectionString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = insertStatment.ToString(); foreach (var dbParameter in oleDbParameters) { command.Parameters.Add(dbParameter); } return(command.ExecuteNonQuery()); } }
public int Update(DskWor00 entity) { var query = new StringBuilder($"UPDATE {tableName} "); var nonComputedColumn = propertyModels.Where(p => !p.IsComputed); var primaryKeys = propertyModels.Where(p => p.IsPrimaryKey); List <string> updateStatment = new List <string>(); List <OleDbParameter> oleDbParameters = new List <OleDbParameter>(); foreach (var model in nonComputedColumn) { var propertyValue = model.PropertyInfo.GetValue(entity); if (model.PropertyInfo.PropertyType == typeof(string) && !propertyValue.ToString().Contains("/")) { propertyValue = ((string)model.PropertyInfo.GetValue(entity)).ToIranSystem(); } updateStatment.Add(model.PropertyName + "=?"); oleDbParameters.Add(new OleDbParameter(model.ColumnName, propertyValue)); } query.Append(" SET " + string.Join(",", updateStatment)); var whereParts = new List <string>(); foreach (var key in primaryKeys) { whereParts.Add(key.ColumnName + "=?"); oleDbParameters.Add(new OleDbParameter(key.ColumnName, key.PropertyInfo.GetValue(entity))); } query.Append(" WHERE " + String.Join(" AND ", whereParts) + ";"); using (var connection = new OleDbConnection(connectionString)) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = query.ToString(); foreach (var parameter in oleDbParameters) { command.Parameters.Add(parameter); } return(command.ExecuteNonQuery()); } }