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