public static T ExecuteToEntity <T>(string commandText, RowMapper <T> rowMapper, List <SqlParameter> parameters) where T : class
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader dataReader = null;
T entity = null;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, parameters, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
entity = rowMapper(dataReader);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseDataReader(dataReader);
CloseCommand(command);
CloseConnection(connection);
}
return(entity);
}
public IEnumerable <T> Query <T>(string sql, RowMapper <T> rowMapper, SqlParameter[] parameters = null)
{
using (DbConnection connection = connectionFactory.CreateConnection())
{
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
if (parameters != null)
{
AddParameters(parameters, command);
}
var items = new List <T>();
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
items.Add(rowMapper(reader));
}
}
return(items);
}
}
}
public void MapWithDateTimeStruct()
{
var rowMapper = new RowMapper <Entities.Race>();
var mockedDataRecord = A.Fake <IDataRecord>();
var expectedInt = 15;
var expectedString = "Test";
var expectedDate = new DateTime(2019, 11, 13);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("Id")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("RaceTypeId")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("VenueId")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("FirstStartListId")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("SecondStartListId")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("NumberOfSensors")]).Returns(expectedInt);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("Description")]).Returns(expectedString);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("Date")]).Returns(expectedDate);
A.CallTo(() => mockedDataRecord[A <string> .That.IsEqualTo("GenderSpecificRaceId")]).Returns(expectedInt);
var mappedStartPosition = rowMapper.Map(mockedDataRecord);
Assert.Equal(expectedInt, mappedStartPosition.Id);
Assert.Equal(expectedInt, mappedStartPosition.RaceTypeId);
Assert.Equal(expectedInt, mappedStartPosition.VenueId);
Assert.Equal(expectedInt, mappedStartPosition.FirstStartListId);
Assert.Equal(expectedInt, mappedStartPosition.SecondStartListId);
Assert.Equal(expectedInt, mappedStartPosition.NumberOfSensors);
Assert.Equal(expectedString, mappedStartPosition.Description);
Assert.Equal(expectedDate, mappedStartPosition.Date);
Assert.Equal(expectedInt, mappedStartPosition.GenderSpecificRaceId);
}
public static T ExecuteToEntity <T>(string commandText
, RowMapper <T> rowMapper
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure) where T : class
{
HanaConnection connection = null;
HanaCommand command = null;
HanaDataReader dataReader = null;
T entity = null;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
entity = rowMapper(dataReader);
}
}
catch (HanaException)
{
throw;
}
finally
{
CloseDataReader(dataReader);
CloseCommand(command);
CloseConnection(connection);
}
return(entity);
}
public static List <T> ExecuteToList <T>(string commandText, RowMapper <T> rowMapper, MySqlParameter parameter) where T : class
{
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(parameter);
return(ExecuteToList <T>(commandText, rowMapper, parameters));
}
public void Insert(Client client)
{
RowMapper <Client> rowMapper = RowMapper;
ActiveRecord <Client> dao = new ActiveRecord <Client>("User", rowMapper);
dao.Insert(client);
}
private T FindEntityById(string id, RowMapper <T> row)
{
T result = default(T);
result = Find("select * from [" + _dataBase + "] where Nickname = ?", id, row);
return(result);
}
public static List <T> ExecuteToList02 <T>(string commandText, RowMapper <T> rowMapper, List <MySqlParameter> parameters) where T : class
{
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader dataReader = null;
List <T> listEntity = new List <T>();
try
{
connection = OpenConnection();
command = PrepareCommand02(commandText, parameters, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
T entity = rowMapper(dataReader);
listEntity.Add(entity);
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
CloseDataReader(dataReader);
CloseCommand(command);
CloseConnection(connection);
}
return(listEntity);
}
public IEnumerable <T> Query <T>(string sql, RowMapper <T> rowMapper, params Parameter[] parameters)
{
// mit using wird connection automatisch geschlossen
using (DbConnection connection = connectionFactory.CreateConnection())
{
// durch IConnectionFactory wird bei CreateConnection die Verbindung schon geöffnet
// Connectionstring angeben und Datenbank Verbindung öffnen
// connection.ConnectionString = connectionFactory.ConnectionString;
// connection.Open();
// sql commands
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
AddParameters(command, parameters);
var items = new List <T>();
// IDataReader ist vergleichbar mit Iterator über die Zeilen
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Call delegate
// IDataReader leitet von IDataRecord ab
// Mit IDataRecord kann nicht zur nächsten Zeile gewechselt werden
items.Add(rowMapper(reader));
}
}
return(items);
}
}
}
public async Task <IEnumerable <T> > QueryObjectSetAsync <T>(
string sqlQuery,
RowMapper <T> rowMapper,
params QueryParameter[] queryParameters) where T : new()
{
using var dbConnection = await connectionFactory.CreateConnectionAsync()
.ConfigureAwait(false);
using var dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.AddParameters(queryParameters);
var resultItems = new List <T>();
using (var dbDataReader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await dbDataReader.ReadAsync().ConfigureAwait(false))
{
resultItems.Add(rowMapper.Map(dbDataReader));
}
}
return(resultItems);
}
public Client Select(string id)
{
RowMapper <Client> rowMapper = RowMapper;
ActiveRecord <Client> dao = new ActiveRecord <Client>("User", rowMapper);
return(dao.Select(id));
}
public async Task <IEnumerable <T> > QueryAsync <T>(string sql, RowMapper <T> rowMapper, params Parameter[] parameters)
{
var items = new List <T>();
// Create Connection to DB
using (DbConnection connection = this.connectionFactory.CreateConnection())
{
// Create Commend/Query
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
AddParameters(command, parameters);
try
{
// Read reveived data
using (IDataReader reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
items.Add(rowMapper(reader));
}
}
}
catch (Exception ex)
{
throw new MySqlException("Database not set up or missing table", ex.InnerException);
}
}
}
return(items);
}
private RowPresenter(RowMapper rowMapper, DataRow dataRow, int rawIndex)
{
Debug.Assert(rowMapper != null);
_rowMapper = rowMapper;
_dataRow = dataRow;
RawIndex = rawIndex;
RefreshMatchValueHashCode();
}
public T GetSingleEntity(OleDbDataReader reader, RowMapper <T> row)
{
while (reader.Read())
{
return(rowMapper(reader));
}
return(default(T));
}
public async Task <T> QuerySingleObjectAsync <T>(
string sqlQuery,
RowMapper <T> rowMapper,
params QueryParameter[] queryParameters) where T : new()
{
var resultingSet = await this.QueryObjectSetAsync(sqlQuery, rowMapper, queryParameters)
.ConfigureAwait(false);
return(resultingSet.Count() == 1 ? resultingSet.First() : (default));
private T Find(string query, string id, RowMapper <T> row)
{
T result = default(T);
using (OleDbDataReader reader = GetQueryDataReader(query, id))
{
result = GetSingleEntity(reader, rowMapper);
}
return(result);
}
public List <T> FindEntities(string query, RowMapper <T> row)
{
List <T> result = default(List <T>);
using (OleDbDataReader reader = GetQueryDataReader(query, ""))
{
result = GetEntities(reader, row);
}
return(result);
}
/// <summary>
/// Create a new Bindings from this one, but based on a potentially different schema.
/// Used by the ITransformTemplate.ApplyToData implementation.
/// </summary>
public Bindings ApplyToSchema(IHostEnvironment env, DataViewSchema input)
{
Contracts.AssertValue(input);
Contracts.AssertValue(env);
var bindable = RowMapper.Bindable;
var roles = RowMapper.GetInputColumnRoles();
string suffix = Suffix;
return(Create(env, bindable, input, roles, suffix));
}
private IList <T> ProcessMap(ExcelWorksheet sheet)
{
var result = new List <T>();
if (sheet.Dimension == null)
{
throw new TableMappingException(string.Format(Strings.XlsxPageEmpty, _sheetName), 0);
}
var header = new string[sheet.Dimension.End.Column];
if (MappingOptions.HasHeader)
{
for (var column = 1; column <= sheet.Dimension.End.Column; column++)
{
header[column - 1] = sheet.Cells[1, column]
?.Text
?.Replace("\r", string.Empty)
.Replace("\n", string.Empty)
.Trim()
.ToLower() ?? string.Empty;
}
}
int headerCorrect = (MappingOptions.HasHeader ? 1 : 0);
int startRow = 1 + headerCorrect;
int row = 0;
int indexRow = row + headerCorrect;
for (var rowI = startRow; rowI <= sheet.Dimension.End.Row; rowI++, row++, indexRow++)
{
ThrowIfRowsLimitEnabled(row, indexRow);
var rowResult = new string[sheet.Dimension.End.Column];
for (var column = 1; column <= sheet.Dimension.End.Column; column++)
{
var value = sheet.Cells[rowI, column]?.Value?.ToString();
if (MappingOptions.Trim)
{
value = value?.Trim();
}
rowResult[column - 1] = value;
}
var entity = RowMapper.Map(rowResult, header, indexRow, MappingOptions.SuppressConvertTypeErrors);
ValidateRow(entity, indexRow);
result.Add(entity);
}
return(result);
}
public List <T> GetEntities <T>(OleDbDataReader reader, RowMapper <T> rowMapper)
{
List <T> results = new List <T>();
while (reader.Read())
{
T result = rowMapper(reader);
results.Add(result);
}
return(results);
}
public GenericDao(IConnectionFactory connectionFactory)
{
if (connectionFactory is null)
{
throw new ArgumentNullException(nameof(connectionFactory));
}
template = new AdoTemplate(connectionFactory);
RowMapper = new RowMapper <T>();
SqlQueryGenerator = new SqlQueryGenerator <T>();
}
/// <summary>
/// Формирование списка обектов
/// </summary>
/// /// <param name="query">
/// Список обьектов БД
/// </param>
public Collection <T> GetEntities(OleDbDataReader reader, RowMapper <T> rowMapper)
{
Collection <T> results = new Collection <T>();
while (reader.Read())
{
T result = rowMapper(reader);
results.Add(result);
}
return(results);
}
public static void Initialize(IWindsorContainer container, BootstrapperSettings settings)
{
if (settings == null)
{
settings = new BootstrapperSettings();
}
Settings = settings;
PreventReInitialize();
Initialized = true;
Container = container;
switch (Settings.ORMFrameWork)
{
case ORMFrameWork.EntityFrameWok:
{
Container
.Install(new SubsystemInstaller())
.Install(new DataAccessInstaller(), new ServicesInstaller())
.Register(
Component.For <Database>().ImplementedBy(settings.DataBaseImplementation).LifeStyle.Singleton,
Component.For <DataContext>().ImplementedBy(settings.EntityFramWorkInstallerImplementation).LifeStyle.Singleton
);
}
break;
case ORMFrameWork.NHibernate:
{
Container
.Install(new SubsystemInstaller())
.Install(new DataAccessInstaller(), new ServicesInstaller())
.AddFacility <AutoTxFacility>()
.Register(
Component.For <Database>().ImplementedBy(settings.DataBaseImplementation).LifeStyle.Singleton,
Component.For <INHibernateInstaller>()
.ImplementedBy(settings.NhibernateInstallerImplementation)
.LifeStyle.Singleton
)
.AddFacility <NHibernateFacility>(x =>
{
x.FlushMode = settings.NHibernateFlushMode;
x.DefaultLifeStyle = settings.NHibernateSessionLifeStyle;
x.LifeStyleScoped = settings.NHibernateSessionLifeStyleScoped;
});
}
break;
}
RowMapper.Init(new InitRowMapParam {
MapFileDirectory = PathHelper.BinPath()
});
}
public ColumnMap GetMap()
{
MemberExpression temp = _memberAccess;
string propertyMap = "";
while (temp != null)
{
propertyMap = "." + temp.Member.Name + propertyMap;
temp = temp.Expression as MemberExpression;
}
propertyMap = propertyMap.TrimStart('.');
_propertyName = propertyMap;
if (_mapType == MapType.UseMap && propertyMap.Contains("."))
{
throw new Exception("Nested Property And UseMap Is Not Compatible");
}
ColumnMap columnMap = null;
if (_mapType == MapType.ColumnName)
{
string columnName = _columnName;
if (propertyMap.Contains("."))
{
string[] strings = propertyMap.Split(new[] { "." }, StringSplitOptions.RemoveEmptyEntries);
RowMap tempRm = RowMapper.GetTempRowMap(strings, columnName);
columnMap = new ColumnMap()
{
PropertyName = strings[0],
InnerType = InnerMapType.Internal,
InternalRowMap = tempRm
};
}
else
{
columnMap = new ColumnMap()
{
ColumnName = columnName, PropertyName = propertyMap
}
};
}
else if (_mapType == MapType.UseMap)
{
columnMap = new ColumnMap()
{
PropertyName = propertyMap,
InnerType = InnerMapType.Internal,
};
}
return(columnMap);
}
public ICollection <T> Query <T>(string sql, RowMapper <T> rowMapper, SqlParameter[] parameters = null)
{
try
{
using (DbConnection connection = ConnectionFactory.CreateConnection())
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
if (parameters != null)
{
// Logger.Info("adding parameters " );
AddParameters(parameters, command);
}
var items = new List <T>();
try
{
// Daten auslesen aus dem Reader
using (DbDataReader reader = command.ExecuteReader())
{
int i = 0;
while (reader.Read())
{
//Logger.Info("Reader line " +i+" : " + reader);
i++;
items.Add(
rowMapper(reader)
);
}
Logger.Info("Query '" + sql + "' successfull\t\t results: " + items);
//Logger.Error("Query sleeping for 1 seconds");
//Thread.Sleep(1000);
return(items);
}
}
catch (Exception ex)
{
Logger.Error("error executing an query in AdoTemplate.Query()\t SQL = '" + sql + "' \t Error message: " + ex.Message);
}
return(null);
}
}
catch (Exception ex)
{
Logger.Error($"exception in AdoTemplate.Query: sql = {sql}, exception = {ex.Message}");
}
return(null);
}
/// <summary>
/// Creates a data reader mapper that extracts a list of elements, one for each row, using a row mapper.
/// </summary>
/// <typeparam name="T"> the type of the elements returned</typeparam>
/// <param name="rowMapper">the mapper creating the elements</param>
/// <returns>a data reader extractor</returns>
private static DataReaderExtractor<IList<T>> GetDataReaderExtractor<T>(RowMapper<T> rowMapper)
{
return dataReader =>
{
IList<T> result = new List<T>();
int i = 0;
while(dataReader.Read())
{
result.Add(rowMapper(dataReader, i));
i++;
}
return result;
};
}
public static IList <T> ExecuteList <T>(this DbCommand cmd, RowMapper <T> mapper)
{
cmd.Connection.TryOpen();
using (var reader = cmd.ExecuteReader())
{
IList <T> list = new List <T>();
while (reader.Read())
{
T obj = mapper(reader);
list.Add(obj);
}
return(list);
}
}
/// <summary>
/// Аутентификация пользователя
/// </summary>
private void LoginClick(object obj)
{
RowMapper <User> rowMapper = RowMapper;
ActiveRecord <User> dao = new ActiveRecord <User>("User", rowMapper);
User us = dao.Select(Login);
if (us != null)
{
User.SharedUser = us;
MenuWindow menu = new MenuWindow();
MenuViewModel viewModel = new MenuViewModel(menu);
menu.DataContext = viewModel;
menu.Show();
}
}
/// <summary>
/// Регистрация пользователя;
/// Добавление пользователя в БД;
/// </summary>
private void RegisterClick(object obj)
{
if (!String.IsNullOrEmpty(Nickname) && !String.IsNullOrEmpty(FirstName) &&
!String.IsNullOrEmpty(SecondName))
{
RowMapper <User> rowMapper = RowMapper;
ActiveRecord <User> dao = new ActiveRecord <User>("User", rowMapper);
User user = new User();
user.Nickname = Nickname;
user.SecondName = SecondName;
user.FirstName = FirstName;
dao.Insert(user);
_window.Close();
}
}
private void ProcessImport(TextFieldParser parser, CancellationToken?ct = null)
{
int row = 0;
int indexRow = row + (MappingOptions.HasHeader ? 1 : 0);
string[] header = { };
while (!parser.EndOfData)
{
if (ct.HasValue && ct.Value.IsCancellationRequested)
{
ct.Value.ThrowIfCancellationRequested();
}
ThrowIfRowsLimitEnabled(indexRow);
if (row == 0)
{
if (MappingOptions.HasHeader)
{
header = parser.ReadFields();
header = header?.Select(x => x.ToLower()).ToArray()
?? throw new TableMappingException(Strings.HeaderRowIsEmpty, indexRow);
}
}
else
{
var fields = parser.ReadFields();
if (fields == null)
{
continue;
}
var entity = RowMapper.Map(fields, header, indexRow, MappingOptions.SuppressConvertTypeErrors);
ValidateRow(entity, indexRow);
RowSaver.SaveRow(entity);
}
row++;
indexRow++;
}
RowSaver.SaveRemainder();
}
public void TearDown()
{
dataAccess = null;
rowMapper = null;
}
public void TestJoin()
{
RowMapper<string> mapper = new RowMapper<string>(TestEntityMapper.GetTestJoinEntity);
dataAccess.Join<string, TestAccountEntity>(mapper);
}
public void SetUp()
{
dataAccess = new DataAccess<TestEntity>(database);
rowMapper = new RowMapper<TestEntity>(TestEntityMapper.GetTestEntityEntity);
}
protected virtual void db_DoWork(object sender, DoWorkEventArgs e)
{
MySqlDataReader reader;
int affectedRows = 0;
try
{
switch (this.queryMode)
{
case QueryMode.Schala:
case QueryMode.SelectOne:
case QueryMode.SelectRows:
reader = this.command.ExecuteReader();
if (this.EnableRowBind)
{
var listType = typeof(List<>);
var constructedListType = listType.MakeGenericType(this.type);
IList listInstance = (IList)Activator.CreateInstance(constructedListType);
RowMapper mapper = new RowMapper();
object obj = null;
while (reader.Read())
{
obj = mapper.SetInstance(reader, this.type);
listInstance.Add(obj);
}
reader.Close();
//con.Close();
this.result = listInstance;
}
else
{
this.result = reader;
}
break;
case QueryMode.Delete:
case QueryMode.Insert:
case QueryMode.Update:
affectedRows = this.command.ExecuteNonQuery();
//con.Close();
break;
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
Debug.WriteLine(ex.StackTrace);
MessageBox.Show(ex.Message);
}
}
