public QueryResult <WorkItem> FindAll(int pageIndex = 1, int pageSize = 10)
{
try
{
using (var conn = SqldaoFactory.GetConnection())
{
var pagingInfo = QueryHelper.GetPagingRowNumber(pageIndex, pageSize);
var result = conn.QueryMultiple("work_item_findall",
new
{
pagingInfo.RowStart,
pagingInfo.RowEnd
},
commandType: CommandType.StoredProcedure);
var workItems = result.Read <WorkItem>();
return(new QueryResult <WorkItem>(workItems, result.Read <int>().First()));
}
}
catch (Exception exception)
{
_appLogRepository.Log(exception);
}
return(null);
}
public void BulkInsert(DataTable workItem)
{
using (var connection = SqldaoFactory.GetConnection())
{
var startTime = DateTime.Now;
connection.Open();
var transaction = connection.BeginTransaction();
try
{
_appLogRepository.Log("About to start Upload", ".....Upload in progress");
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
sqlBulkCopy.BulkCopyTimeout = 0;
sqlBulkCopy.BatchSize = 10000;
sqlBulkCopy.DestinationTableName = ApplicationConstants.WorkItem;
sqlBulkCopy.WriteToServer(workItem);
}
transaction.Commit();
}
catch (Exception exception)
{
_appLogRepository.Log(exception);
transaction.Rollback();
}
finally
{
transaction.Dispose();
connection.Close();
var stoppageTime = DateTime.Now;
_appLogRepository.Log("Total Time taken", string.Format("Time In Seconds {0}", (stoppageTime - startTime).TotalSeconds));
}
}
}
public QueryResult <WorkItem> WorkItemsReport(DateTime dateFrom, DateTime dateTo, int userId = 0, int exerciseId = 0,
string difficulty = null, string subject = null, int pageIndex = 1, int pageSize = 10)
{
using (var conn = SqldaoFactory.GetConnection())
{
var pagingInfo = QueryHelper.GetPagingRowNumber(pageIndex, pageSize);
var result = conn.QueryMultiple("work_item_report",
new
{
dateFrom,
dateTo,
subject,
exerciseId,
userId,
difficulty,
pagingInfo.RowStart,
pagingInfo.RowEnd
},
commandType: CommandType.StoredProcedure);
var workItems = result.Read <WorkItem>();
return(new QueryResult <WorkItem>(workItems, result.Read <int>().First()));
}
}
public void Save(WorkItem workItem)
{
try
{
using (var conn = SqldaoFactory.GetConnection())
{
conn.ExecuteAsync("work_item_insert", new
{
workItem.Correct,
workItem.Difficulty,
workItem.ExerciseId,
workItem.SubmittedAnswerId,
workItem.UserId,
workItem.LearningObjective,
workItem.Progress,
workItem.SubmitDateTime,
workItem.Subject,
workItem.Domain
});
}
}
catch (Exception exception)
{
_appLogRepository.Log(exception);
}
}
public void DeleteErrorLogs(long logId = 0)
{
using (var conn = SqldaoFactory.GetConnection())
{
var command = logId == 0 ? @"TRUNCATE TABLE application_logs" : @"DELETE application_logs WHERE Id =" + logId;
conn.ExecuteAsync(command);
}
}
public AppSettings GetByName(string settingsName)
{
using (var conn = SqldaoFactory.GetConnection())
{
var appSettings = conn.Query <AppSettings>("appsettings_findbyname",
new { settingsName }, commandType: CommandType.StoredProcedure);
return(appSettings.SingleOrDefault());
}
}
public void DailyScripturesDaoTest()
{
var sampleConnectionString = SqldaoFactory.CreateConnection();
var scriptures = new DailyScriptures();
/// ExtrasDao.GetDailyScripturesAsync(scriptures, sampleConnectionString.ConnectionString);
Assert.AreEqual(true, true);
}
public WorkItem Find(long submittedAnswerId)
{
using (var conn = SqldaoFactory.GetConnection())
{
var payments = conn.Query <WorkItem>("work_item_findbysubmitted_answerId",
new { submittedAnswerId }, commandType: CommandType.StoredProcedure);
return(payments.SingleOrDefault());
}
}
public IEnumerable <string> FindAllGroups()
{
using (var conn = SqldaoFactory.GetConnection())
{
var result = conn.QueryMultiple("appsettings_findallgroups",
commandType: CommandType.StoredProcedure);
var groupNames = result.Read <string>();
return(groupNames);
}
}
public async Task Save(AppSettings appSettings)
{
using (var conn = SqldaoFactory.GetConnection())
{
await conn.QueryAsync("appsetting_insertupdate", new
{
appSettings.Id,
appSettings.Name,
appSettings.Value,
appSettings.GroupName,
appSettings.Description
}, commandType : CommandType.StoredProcedure);
}
}
public void DeleteWorkItem(long id)
{
try
{
using (var conn = SqldaoFactory.GetConnection())
{
conn.ExecuteAsync("work_item_delete", new
{
id
});
}
}
catch (Exception exception)
{
_appLogRepository.Log(exception);
}
}
public QueryResult <string> GetAllSubject()
{
using (var conn = SqldaoFactory.GetConnection())
{
var result = conn.QueryMultiple("work_item_getallsubjects"
, commandType: CommandType.StoredProcedure);
var subjects = result.Read <string>();
if (subjects != null)
{
return(new QueryResult <string>(subjects, subjects.Count()));
}
}
return(null);
}
public QueryResult <AppSettings> FindAll(int pageIndex = 1, int pageSize = 10)
{
using (var conn = SqldaoFactory.GetConnection())
{
var pagingInfo = QueryHelper.GetPagingRowNumber(pageIndex, pageSize);
var result = conn.QueryMultiple("appsettings_findall",
new
{
rowStart = pagingInfo.RowStart,
rowEnd = pagingInfo.RowEnd
},
commandType: CommandType.StoredProcedure);
var appSettings = result.Read <AppSettings>();
return(new QueryResult <AppSettings>(appSettings, result.Read <int>().First()));
}
}
public void Log(Exception exception)
{
try
{
using (var conn = SqldaoFactory.GetConnection())
{
conn.ExecuteAsync(@"INSERT INTO application_logs(logmessage, stacktrace, logdate)
VALUES(@message, @stacktrace, @logdate)", new
{
logdate = DateHelper.GetCurrentDate(),
message = exception.Message,
stacktrace = exception.StackTrace
});
}
}
catch (Exception)
{
//Do nothing
}
}
public void Log(string message, string stackTrace)
{
try
{
using (var conn = SqldaoFactory.GetConnection())
{
conn.ExecuteAsync(@"application_logs_insert",
new
{
logdate = DateHelper.GetCurrentDate(),
message,
stackTrace
});
}
}
catch (Exception)
{
//Do nothing
}
}
public QueryResult <WorkItem> FindBySubject(string subject, int pageIndex = 1, int pageSize = 10)
{
using (var conn = SqldaoFactory.GetConnection())
{
var pagingInfo = QueryHelper.GetPagingRowNumber(pageIndex, pageSize);
var result = conn.QueryMultiple("work_item_findbysubject",
new
{
pagingInfo.RowStart,
pagingInfo.RowEnd,
subject
},
commandType: CommandType.StoredProcedure);
var workItems = result.Read <WorkItem>();
return(new QueryResult <WorkItem>(workItems, result.Read <int>().First()));
}
}
