public List <Kandidat> GetAllCandidates()
{
try
{
string connectionString = ConnectionStringHelper.GetConnectionString(_configuration);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
ExecutorService repository = new ExecutorService(connection, transaction);
SqlDataReader dataReader = repository.ExecuteProcedure(Constants.GET_ALL_CANDIDATES);
List <Kandidat> candidates = DataReaderConverter.ToList <Kandidat>(dataReader);
dataReader.Close();
return(candidates);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <BirackoMjesto> GetAllPollingStations()
{
try
{
string connectionString = ConnectionStringHelper.GetConnectionString(_configuration);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
ExecutorService repository = new ExecutorService(connection, transaction);
SqlDataReader dataReader = repository.ExecuteProcedure(Constants.GET_ALL_POLLINGSTATIONS);
List <BirackoMjesto> pollingStations = DataReaderConverter.ToList <BirackoMjesto>(dataReader);
dataReader.Close();
return(pollingStations);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Opcina> GetAllMunicipalities()
{
try
{
string connectionString = ConnectionStringHelper.GetConnectionString(_configuration);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
ExecutorService repository = new ExecutorService(connection, transaction);
SqlDataReader dataReader = repository.ExecuteProcedure(Constants.GET_ALL_MUNICIPALITIES);
List <Opcina> opcine = DataReaderConverter.ToList <Opcina>(dataReader);
dataReader.Close();
return(opcine);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public virtual DBResult ProjectsGet(out ProjectList projects)
{
projects = new ProjectList();
DBResult result;
QueryString sql = DB.Format(
$"SELECT * FROM timekeep.projects ORDER by name");
DatabaseDataReader reader;
result = DB.Query(sql, out reader);
using (reader)
{
if (result.ResultCode == DBResult.Result.Success)
{
while (reader.Read())
{
Project_DO project = DataReaderConverter.CreateClassFromDataReader <Project_DO>(reader);
projects.Add(project);
}
}
}
return(result);
}
public List <PolitickiSubjekt> GetAllPoliticalSubjects()
{
try
{
string connectionString = ConnectionStringHelper.GetConnectionString(_configuration);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
ExecutorService repository = new ExecutorService(connection, transaction);
SqlDataReader dataReader = repository.ExecuteProcedure(Constants.GET_ALL_POLITICALSUBJECT);
List <PolitickiSubjekt> politicalSubjects = DataReaderConverter.ToList <PolitickiSubjekt>(dataReader);
dataReader.Close();
return(politicalSubjects);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Generates select command from model and execute that stored procedure
/// </summary>
/// <typeparam name="T">
/// Any object that implements IModel interface
/// </typeparam>
/// <param name="model">
/// IModel object with values for stored procedure input parameters
/// </param>
/// <returns>
/// Imodel object
/// </returns>
public List <T> SelectMultipleFromProcedure <T>(IModel model) where T : IModel
{
SqlDataReader dataReader = null;
List <T> models;
// creates new as command object using stored procedure
SqlCommand command = this.commandBuilder.GetSelectMultipleCommand(model);
command.Transaction = (SqlTransaction)this.transaction;
command.Connection = (SqlConnection)this.connection;
//checking the connection state and making it in Open State
if (connection.State.ToString() == "Closed")
{
connection.Open();
}
// executes select stored procedure
using (dataReader = command.ExecuteReader())
{
try
{
DataReaderConverter converter = new DataReaderConverter(this.modelType);
models = converter.ConvertDataReaderToModels <T>(dataReader);
}
finally
{
dataReader.Close();
if (connection.State.ToString() == "Open")
{
connection.Close();
}
}
}
return(models);
}
/// <summary>
/// Insert a new row to database using stored procedure
/// </summary>
/// <param name="keyValue">
/// Primary key value of record to be deleted.
/// </param>
/// <returns>
/// Returns I model object with new key
/// </returns>
public bool DeleteProcedure(object keyValue)
{
DataReaderConverter converter;
converter = new DataReaderConverter(this.modelDataMap);
// creates new command object with stored procedure parameters
SqlCommand command = this.commandBuilder.GetDeleteCommand(keyValue);
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
// Execute stored procedure
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public virtual DBResult EntriesForDateGet(DateTime day, out TimeCardEntryList entries)
{
entries = new TimeCardEntryList();
DBResult result;
QueryString sql = DB.Format(
$"SELECT \n" +
$"E.id as entry_id, \n" +
$"E.start_time as start_time, \n" +
$"E.end_time as end_time, \n" +
$"E.memo as memo, \n" +
$"P.id as project_id, \n" +
$"P.name as project_name \n" +
$"FROM timekeep.entries E \n" +
$"JOIN timekeep.projects P ON E.project_id = P.id \n" +
$"WHERE \n" +
$" (start_time >= '{day.Date.ToMySqlString()}' AND start_time <= '{(day.Date + TimeSpan.FromDays(1)).ToMySqlString()}') OR \n" +
$" (end_time >= '{day.Date.ToMySqlString()}' AND end_time <= '{(day.Date + TimeSpan.FromDays(1)).ToMySqlString()}') \n" +
$"ORDER BY start_time ASC");
DatabaseDataReader reader;
result = DB.Query(sql, out reader);
using (reader)
{
if (result.ResultCode == DBResult.Result.Success)
{
while (reader.Read())
{
TimeCardEntry_DO entry = DataReaderConverter.CreateClassFromDataReader <TimeCardEntry_DO>(reader);
entry.Project = DataReaderConverter.CreateClassFromDataReader <Project_DO>(reader);
entries.Add(entry);
}
}
}
return(result);
}
/// <summary>
/// Insert a new record to database using given imodel object.
/// </summary>
/// <param name="model">
/// A IModel object to be saved.
/// </param>
/// <returns>
/// A IModel object
/// </returns>
public IModel Insert(IModel model)
{
SqlDataReader reader = null;
IModel newModel = null;
ModelColumnMap c = null;
DataReaderConverter converter;
converter = new DataReaderConverter(this.modelDataMap);
// set the createdDate if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["createdDate"];
if (c != null)
{
converter.SetFieldValue(model, c, DateTime.Now);
}
// set the createdBy if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["createdBy"];
if (c != null)
{
string userName = Thread.CurrentPrincipal.Identity.Name;
converter.SetFieldValue(model, c, userName);
}
SqlCommand command = (SqlCommand)this.connection.CreateCommand();
command.CommandText = this.commandBuilder.GetInsertSql();
foreach (ModelColumnMap col in this.modelDataMap.Columns.Values)
{
if (!col.IsViewOnly)
{
SqlParameter p = new SqlParameter();
p.ParameterName = "@" + col.ColumnName;
if (col.Field != null)
{
p.SourceColumn = col.FieldName;
}
else if (col.Property != null)
{
p.SourceColumn = col.PropertyName;
}
if (!col.IsDatabaseTypeNull)
{
p.DbType = col.DatabaseType;
}
object val = null;
if (col.Field != null)
{
val = col.Field.GetValue(model);
}
else if (col.Property != null)
{
val = col.Property.GetValue(model, null);
}
if (val == null)
{
val = DBNull.Value;
}
p.Value = val;
command.Parameters.Add(p);
}
}
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
FieldInfo keyField = this.modelDataMap.KeyField;
if (keyField != null)
{
object id = reader[0];
converter.SetFieldValue(model, keyField, id);
newModel = model;
}
else if (this.modelDataMap.KeyProperty != null)
{
object id = reader[0];
converter.SetFieldValue(model, this.modelDataMap.KeyProperty, id);
newModel = model;
}
else
{
newModel = model;
}
}
}
finally
{
reader.Close();
}
return(newModel);
}
/// <summary>
/// Update a database record using given imodel object,
/// </summary>
/// <param name="model">
/// A CarrierRate.GlobalTranz.Framework.Common.Models.IModel object to be updated
/// </param>
/// <returns>
/// A CarrierRate.GlobalTranz.Framework.Common.Models.IModel object
/// </returns>
public IModel Update(IModel model)
{
ModelColumnMap c = null;
DataReaderConverter converter;
converter = new DataReaderConverter(this.modelDataMap);
// set the createdDate if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["modifiedDate"];
if (c != null)
{
converter.SetFieldValue(model, c, DateTime.Now);
}
// set the createdBy if it exists
c = (ModelColumnMap)this.modelDataMap.Columns["modifiedBy"];
if (c != null)
{
string userName = Thread.CurrentPrincipal.Identity.Name;
converter.SetFieldValue(model, c, userName);
}
SqlCommand command = (SqlCommand)this.connection.CreateCommand();
command.CommandText = this.commandBuilder.GetUpdateSql();
foreach (ModelColumnMap col in this.modelDataMap.Columns.Values)
{
if (!col.IsViewOnly)
{
string paramName = "@" + col.ColumnName;
command.Parameters.Add(this.CreateParameter(paramName, col, model));
}
}
SqlParameter param = new SqlParameter();
param.ParameterName = "@key";
string keyName = null;
if (this.modelDataMap.KeyField != null)
{
keyName = this.modelDataMap.KeyField.Name;
}
else if (this.modelDataMap.KeyProperty != null)
{
keyName = this.modelDataMap.KeyProperty.Name;
}
ModelColumnMap keyCol;
keyCol = (ModelColumnMap)this.modelDataMap.Columns[keyName];
command.Parameters.Add(this.CreateParameter("@key", keyCol, model));
command.Connection = (SqlConnection)this.connection;
command.Transaction = (SqlTransaction)this.transaction;
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(model);
}
return(null);
}
public Result <IEnumerable <T> > ExecuteSqlQuery(string sql)
=> _context.Command(db =>
db.CreateCommand(sql, CommandType.Text, 500, null)
.ToResult()
.Bind(cmd => cmd.ExecuteReader().Using(reader => DataReaderConverter.ToEnumerable <T>((DbDataReader)reader)).ToResult()))
.Unbind();
