public void should_use_a_db_connection()
{
var connectionMock = Substitute.For<IDbConnection>();
var context = new DapperContext(connectionMock);
Assert.NotNull(context);
}
public void should_throw_if_BeginScope_called_after_Dispose()
{
var mock = Mocks.New();
var context = new DapperContext(mock.Connection);
context.Dispose();
Assert.Throws<ObjectDisposedException>(() => context.Begin());
}
public void should_open_connection_for_BeingScope()
{
var mock = Mocks.New();
var context = new DapperContext(mock.Connection);
mock.Connection.State.Returns(ConnectionState.Closed); //close the connection to test since it will open in the constructor
mock.Connection.ClearReceivedCalls();
context.Begin();
mock.Connection.Received(1).Open();
}
public void should_open_connection_for_BeingScope()
{
var mock = Mocks.New();
var context = new DapperContext(mock.Connection);
mock.Connection.State.Returns(ConnectionState.Closed); //close the connection to test since it will open in the constructor
mock.Connection.ClearReceivedCalls();
context.Begin();
mock.Connection.Received(1).Open();
}
public async Task <Currency> GetCurrencyByNumericCodeAsync(int?currencyNumericCode)
{
using (var _context = new DapperContext(DefaultConnection))
{
var parameters = new DynamicParameters();
parameters.Add("@IN_CurrencyNumericCode", currencyNumericCode);
// Dapper is used to execute MMCCurrencies_Get sp and CurrencyDTO is only option to do it.
// You aren't allowed to apply Currency to output object because it's properties have private setter
CurrencyDTO currencyDTO = await _context.Connection.QueryFirstOrDefaultAsync <CurrencyDTO>("MMCCurrencies_Get", parameters, commandType : CommandType.StoredProcedure);
return(_mapper.Map <CurrencyDTO, Currency>(currencyDTO));
}
}
public void Update(Currency currency)
{
using (var _context = new DapperContext(DefaultConnection))
{
var parameters = new DynamicParameters();
parameters.Add("@IN_CurrencyNumericCode", currency.CurrencyNumericCode);
parameters.Add("@IN_Entity", currency.Country);
parameters.Add("@IN_CurrencyType", currency.CurrencyType);
parameters.Add("@IN_AlphabeticCode", currency.AlphabeticCode);
parameters.Add("@IN_ExchangeRate", currency.ExchangeRate);
parameters.Add("@IN_UserID", currency.UserID);
_context.Connection.Query <Currency>("MMCCurrencies_Update", parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
public ImagemRepository(Resposta <Foto> response,
DapperContext dapperContext,
Resposta <FotoLista> responseLista,
Resposta <FotoDetalhe> responseDetalhe,
IConfiguration config,
IAxWebConfiguracoesRepository _configuracoes) : base(response, dapperContext)
{
this.response = response;
this.dapperContext = dapperContext;
this.config = config;
this.responseLista = responseLista;
this.responseDetalhe = responseDetalhe;
this.configuracoes = _configuracoes;
}
public ActionResult Index()
{
var temp = DapperContext.Query <Models.Device>($"SELECT * FROM Device").FirstOrDefault();
if (temp != null)
{
ViewBag.loginip = temp.IP;
ViewBag.port = temp.Port;
ViewBag.username = temp.Name;
ViewBag.password = temp.Password;
ViewBag.autoPlay = "true";
}
return(View());
}
/// <summary>
/// Ctor
/// </summary>
public ServiceRequestService(IRepository <ServiceRequest> serviceRequestRepository,
IRepository <User> userRepository,
IRepository <Assignment> assignmentRepository,
DapperContext dapperContext,
IWorkContext workContext,
IDbContext dbContext)
{
this._serviceRequestRepository = serviceRequestRepository;
this._userRepository = userRepository;
this._assignmentRepository = assignmentRepository;
this._dapperContext = dapperContext;
this._workContext = workContext;
this._dbContext = dbContext;
}
public static async Task <ReadOnlyCollection <SupervisorLookup> > Query(DapperContext ctx)
{
var sql = "SELECT ee.EmployeeId AS ManagerId,";
sql += " CONCAT(ee.FirstName,' ',COALESCE(ee.MiddleInitial,''),' ',ee.LastName) as ManagerName ";
sql += " FROM HumanResources.Employees ee";
sql += " WHERE ee.EmployeeId IN (SELECT DISTINCT SupervisorId FROM HumanResources.Employees)";
sql += " ORDER BY ee.LastName, ee.FirstName, ee.MiddleInitial";
using var connection = ctx.CreateConnection();
var items = await connection.QueryAsync <SupervisorLookup>(sql);
return(new ReadOnlyCollection <SupervisorLookup>(items.ToList()));
}
public int Update(Pengguna obj)
{
var result = 0;
obj.pass_pengguna = obj.konf_pass_pengguna;
using (IDapperContext context = new DapperContext())
{
IUnitOfWork uow = new UnitOfWork(context, _log);
result = uow.PenggunaRepository.Update(obj);
}
return(result);
}
private static async Task <bool> IsValidEmployeeID(Guid employeeId, DapperContext ctx)
{
string sql = $"SELECT EmployeeID FROM HumanResources.Employees WHERE EmployeeId = @ID";
var parameters = new DynamicParameters();
parameters.Add("ID", employeeId, DbType.Guid);
using (var connection = ctx.CreateConnection())
{
var result = await connection.QueryFirstOrDefaultAsync(sql, parameters);
return(result != null);
}
}
// GET: SystemManagement
public ActionResult Index()
{
DapperContext dapper = new DapperContext("docareConnString");
SystemConfig config = new SystemConfig();
MED_SYSTEM_CONFIG entity = dapper.Set <MED_SYSTEM_CONFIG>().Select().FirstOrDefault();
if (entity != null)
{
JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();
config = jsonSerializer.Deserialize <SystemConfig>(entity.CONFIG_JSON);
ViewBag.SYS_ID = entity.SYS_ID;
}
return(View(config));
}
public MessageService(IRepository <Message> messageRepository,
IRepository <MessageTemplate> messageTemplateRepository,
IRepository <Attachment> attachmentRepository,
IUserService userService,
IDbContext dbContext,
DapperContext dapperContext)
{
this._messageRepository = messageRepository;
this._messageTemplateRepository = messageTemplateRepository;
this._attachmentRepository = attachmentRepository;
this._userService = userService;
this._dbContext = dbContext;
this._dapperContext = dapperContext;
}
/// <summary>
/// Ctor
/// </summary>
/// <param name="cacheManager">Cache manager</param>
/// <param name="languageRepository">Language repository</param>
/// <param name="settingService">Setting service</param>
/// <param name="localizationSettings">Localization settings</param>
/// <param name="eventPublisher">Event published</param>
public LanguageService(ICacheManager cacheManager,
IRepository <Language> languageRepository,
ISettingService settingService,
LocalizationSettings localizationSettings,
IEventPublisher eventPublisher,
DapperContext dapperContext)
{
this._cacheManager = cacheManager;
this._languageRepository = languageRepository;
this._settingService = settingService;
this._localizationSettings = localizationSettings;
this._eventPublisher = eventPublisher;
this._dapperContext = dapperContext;
}
public void should_commit_transaction()
{
var mock = Mocks.New();
var context = new DapperContext(mock.Connection);
context.Begin();
using (context)
{
context.Commit();
mock.Transaction.Received(1).Commit();
}
}
/// <summary>
/// 考勤状态(以船员为主表)
/// </summary>
/// <returns></returns>
public JsonResult GetAttendance()
{
//无月考勤率
//var result = _context.Crew.FromSqlRaw("SELECT a.*,a.Name as CrewName,b.Name as ShipName,c.Time,c.Behavior from Crew a LEFT JOIN Ship b on a.ShipId=b.Id LEFT JOIN Attendance c on a.Id=c.CrewId ORDER BY c.Time DESC LIMIT 100");
//增加月考勤率
DateTime now = DateTime.Now;
DateTime startTime = now.AddDays(1 - now.Day); //本月月初
DateTime endTime = startTime.AddMonths(1).AddDays(-1); //本月月末
string sql = $"SELECT DISTINCT(a.id) as CrewId,a.ShipId,a.Name as CrewName,b.Name as ShipName,c.Time,c.Behavior, TRUNCATE((select ((select count(*) from(select aa.Time, aa.CrewId from Attendance as aa where aa.CrewId = a.Id and aa.Time >= DATE('{startTime:yyyy-MM-dd 00:00:00}') and aa.Time <= DATE('{endTime:yyyy-MM-dd 23:59:59}'))tt) / 22 * 100.00) as chuqin),2) as Rate from Crew a LEFT JOIN Ship b on a.ShipId = b.Id LEFT JOIN Attendance c on a.Id = c.CrewId ORDER BY c.Time DESC LIMIT 100";
var result = DapperContext.Query <CrewAttendance>(sql);
return(Json(result));
}
public bool ExecSQL(string sql)
{
var result = false;
using (IDapperContext context = new DapperContext())
{
if (context.IsOpenConnection())
{
result = context.ExecSQL(sql);
}
}
return(result);
}
public void TestAddMovieMagnetHistory()
{
var dbContext = new DapperContext(new ConfigBuilder().Build());
var movieHistoryRepo = new MovieHistoryRepo(dbContext);
var movieMagRepo = new MovieMagnetRepo(dbContext, movieHistoryRepo);
MovieMagnet magnet = new MovieMagnet()
{
IdMovieMag = 9, IdMovie = 73, MovieNumber = "FSDSS-298", MagName = "magnamesf", Size = 5400, DtMagnet = new DateTime(2020, 1, 1)
};
List <MovieHistory> movieHistories = movieMagRepo.AddHistory(magnet);
movieHistories.ForEach(h => _output.WriteLine(h.DescHistory));
}
public bool IsOpenConnection()
{
using (IDapperContext context = new DapperContext())
{
if (context.IsOpenConnection())
{
return(true);
}
else if (!string.IsNullOrEmpty(((DapperContext)context).DbError))
{
_dbError = ((DapperContext)context).DbError;
}
return(false);
}
}
public ListEntity <ListResignEntity> GetList(string key, int pageIndex, int pageSize)
{
List <ListResignEntity> list = new List <ListResignEntity>();
int total = this.context.Resign.Where(c => c.Name.Contains(key)).Count();
if (total <= 0)
{
return(new ListEntity <ListResignEntity>(list, total, pageIndex, pageSize));
}
List <string> ids = this.context.Resign.Where(c => c.Name.Contains(key)).OrderBy(c => c.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).Select(c => c.Id).ToList();
BaseQuery query = new BaseQuery("SELECT Id,Name,Description,CreateTime,Responsibility FROM Resign as de where id in @ids", new { ids = ids });
list = DapperContext.BaseGetListByParam <ListResignEntity>(query);
return(new ListEntity <ListResignEntity>(list, total, pageIndex, pageSize));
}
private bool isConnected()
{
DapperContext context = new DapperContext();
if (context.db != null)
{
connectionStatusBtn.Text = "Connection is OK";
return(true);
}
else
{
connectionStatusBtn.Text = "Connection is Closed";
return(false);
}
}
/// <summary>
/// Populates the data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="cachePath">The cache path.</param>
/// <returns>
/// Content model with populated data stream.
/// </returns>
public virtual ContentModel PopulateData(DapperContext context, ContentModel content, string tableName, string cachePath)
{
this.CacheData(context, content, tableName, cachePath);
if (!string.IsNullOrEmpty(cachePath))
{
var localPath = this.ResolvePath(content, cachePath);
// return a stream
content.DataStream = new System.IO.FileStream(
localPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
}
return content;
}
//public async Task<bool> AddRefreshToken(Sys_RefreshToken token)
//{
// var existingToken = _sysctx.Sys_RefreshToken.SingleOrDefault(r => r.Subject == token.Subject);
// if (existingToken != null)
// {
// var result = await RemoveRefreshToken(existingToken.Id);
// }
// _sysctx.Sys_RefreshToken.Add(token);
// return await _sysctx.SaveChangesAsync() > 0;
//}
public async Task <bool> RemoveRefreshToken(string refreshTokenId)
{
// var refreshToken = _sysctx.Sys_RefreshToken.SingleOrDefault(r=>r.Id==refreshTokenId);
var refreshToken = DapperContext.QueryNormal(string.Format("select * from Sys_RefreshToken where Id={0}", refreshTokenId));
if (refreshToken.total > 0)
{
return(await DapperContext.ExecuteAsync(string.Format("delete from Sys_RefreshToken where Id={0}", refreshTokenId)) > 0);
// _sysctx.Sys_RefreshToken.Remove(refreshToken);
//return await _sysctx.SaveChangesAsync() > 0;
}
return(false);
}
/// <summary>
/// Ctor
/// </summary>
public AssignmentService(IRepository <Assignment> assignmentRepository,
IRepository <AssignmentHistory> assignmentHistoryRepository,
IRepository <User> userRepository,
IRepository <WorkflowDefinition> workflowDefinitionRepository,
IMessageService messageService,
DapperContext dapperContext,
IDbContext dbContext)
{
this._assignmentRepository = assignmentRepository;
this._assignmentHistoryRepository = assignmentHistoryRepository;
this._userRepository = userRepository;
this._workflowDefinitionRepository = workflowDefinitionRepository;
this._messageService = messageService;
this._dapperContext = dapperContext;
this._dbContext = dbContext;
}
/// <summary>
/// Ctor
/// </summary>
public RequestForQuotationService(IRepository <RequestForQuotation> requestForQuotationRepository,
IRepository <User> userRepository,
IAutoNumberService autoNumberService,
DapperContext dapperContext,
IWorkContext workContext,
IDbContext dbContext,
IDateTimeHelper dateTimeHelper)
{
this._requestForQuotationRepository = requestForQuotationRepository;
this._userRepository = userRepository;
this._autoNumberService = autoNumberService;
this._dapperContext = dapperContext;
this._workContext = workContext;
this._dbContext = dbContext;
this._dateTimeHelper = dateTimeHelper;
}
public async Task <Currency> Add(Currency currency)
{
using (var _context = new DapperContext(DefaultConnection))
{
var parameters = new DynamicParameters();
parameters.Add("@IN_CurrencyNumericCode", currency.CurrencyNumericCode);
parameters.Add("@IN_Entity", currency.Country);
parameters.Add("@IN_CurrencyType", currency.CurrencyType);
parameters.Add("@IN_AlphabeticCode", currency.AlphabeticCode);
parameters.Add("@IN_ExchangeRate", currency.ExchangeRate);
parameters.Add("@IN_UserID", currency.UserID);
await _context.Connection.QueryAsync <Currency>("MMCCurrencies_Insert", parameters, commandType : CommandType.StoredProcedure);
return(currency);
}
}
public List <User> SearchList(UserSearchCondition condition)
{
StringBuilder sql = new StringBuilder(@"
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY CreateTime DESC ) AS RowNum,*
FROM dbo.[User]
) tb
WHERE RowNum BETWEEN STR(@PageIndex - 1) * @PageSize + 1
AND STR(@PageIndex * @PageSize)
");
var list = new List <User>();
using (IDbConnection conn = DapperContext.GetSqlConnection())
{
return(conn.Query <User>(sql.ToString(), condition).ToList());
}
}
public int Delete(Role obj)
{
var result = 0;
if (obj == null)
{
throw new NullReferenceException("Object yang ingin dihapus tidak bisa null.");
}
using (IDapperContext context = new DapperContext())
{
_repository = new RoleRepository(context);
result = _repository.Delete(obj);
}
return(result);
}
//public int UpdatePassword(Operator obj)
//{
// var result = 0;
// using (IDapperContext context = new DapperContext())
// {
// _repository = new OperatorRepository(context);
// obj.password = PasswordHash.CreateHash(obj.password);
// result = _repository.UpdatePassword(obj);
// }
// return result;
//}
//public int UpdatePassword(Operator obj, ref ValidationError validationError)
//{
// var passValidator = new PasswordValidator(this);
// var result = passValidator.Validate(obj);
// if (!result.IsValid)
// {
// foreach (var error in result.Errors)
// {
// validationError.PropertyName = error.PropertyName;
// validationError.Message = error.ErrorMessage;
// }
// return 0;
// }
// return UpdatePassword(obj);
//}
public bool LoginIDIsUnique(string loginID, int currentID = default(int))
{
Operator obj = null;
using (IDapperContext context = new DapperContext())
{
_repository = new OperatorRepository(context);
obj = _repository.GetByLoginID(loginID);
}
if (obj == null || obj.id == currentID)
{
return(true); // unique
}
return(false);
}
public IList <ReportPiutangPenjualanProduk> PerProdukGetByTanggal(DateTime tanggalMulai, DateTime tanggalSelesai)
{
IList <ReportPiutangPenjualanProduk> oList = null;
using (IDapperContext context = new DapperContext())
{
_unitOfWork = new UnitOfWork(context, _log);
oList = _unitOfWork.ReportPiutangJualProdukRepository.PerProdukGetByTanggal(tanggalMulai, tanggalSelesai);
}
if (oList.Count > 0)
{
HitungGrandTotalCustomer(oList);
}
return(oList);
}
public bool ValidateOperator(string loginID, string password)
{
Operator obj = null;
using (IDapperContext context = new DapperContext())
{
_repository = new OperatorRepository(context);
obj = _repository.GetByLoginID(loginID);
}
if (obj != null && PasswordHash.ValidatePassword(password, obj.password))
{
return(true);
}
return(false);
}
public IList <ReportPiutangPenjualanProduk> PerProdukGetByBulan(int bulanAwal, int bulanAkhir, int tahun)
{
IList <ReportPiutangPenjualanProduk> oList = null;
using (IDapperContext context = new DapperContext())
{
_unitOfWork = new UnitOfWork(context, _log);
oList = _unitOfWork.ReportPiutangJualProdukRepository.PerProdukGetByBulan(bulanAwal, bulanAkhir, tahun);
}
if (oList.Count > 0)
{
HitungGrandTotalCustomer(oList);
}
return(oList);
}
public bool NamaIsUnique(string nama, int currentID = default(int))
{
Role obj = null;
using (IDapperContext context = new DapperContext())
{
_repository = new RoleRepository(context);
obj = _repository.GetByNama(nama);
}
if (obj == null || obj.id == currentID)
{
return(true); // unik
}
return(false);
}
/// <summary>
/// Caches the data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="cachePath">The cache path.</param>
public virtual void CacheData(DapperContext context, ContentModel content, string tableName, string cachePath)
{
if (!string.IsNullOrEmpty(content.DataIdString))
{
var data =
context.Query<ContentModel>(
string.Format("SELECT Data, DataLength FROM {0} WHERE IdString = @DataIdString", tableName), content).FirstOrDefault();
if (data != null)
{
content.Data = data.Data;
content.DataLength = data.DataLength;
}
}
if (string.IsNullOrEmpty(cachePath))
{
return;
}
// determine if local content exists or is out of date
var localPath = this.ResolvePath(content, cachePath);
var canCache = !File.Exists(localPath);
if (!canCache)
{
var lastWriteTime = File.GetLastWriteTime(localPath);
canCache = lastWriteTime < (content.ModifyDate ?? content.CreateDate);
}
if (canCache)
{
var localDir = System.IO.Path.GetDirectoryName(localPath);
if (!System.IO.Directory.Exists(localDir))
{
System.IO.Directory.CreateDirectory(localDir);
}
System.IO.File.WriteAllBytes(localPath, content.Data ?? new byte[0]);
}
}
/// <summary>
/// Sets the password.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <param name="newPassword">The new password.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentException"></exception>
private bool SetPassword(DapperContext db, int userId, string newPassword)
{
string hashedPassword = Crypto.HashPassword(newPassword);
if (hashedPassword.Length > 128)
{
throw new ArgumentException("Password is too long.");
}
// Update new password
db.Execute(
string.Format(
"UPDATE {0} SET Password= @Password, PasswordSalt = @PasswordSalt, PasswordChangedDate = @ChangeDate WHERE UserId = @UserId", this.MembershipTableName),
new
{
Password = hashedPassword,
PasswordSalt = string.Empty,
ChangeDate = DateTime.UtcNow,
UserId = userId
});
return true;
}
public void should_commit_transaction()
{
var mock = Mocks.New();
var context = new DapperContext(mock.Connection);
context.Begin();
using (context)
{
context.Commit();
mock.Transaction.Received(1).Commit();
}
}
/// <summary>
/// Ensures the schema.
/// </summary>
protected virtual void EnsureSchema()
{
var phunDataSchema = string.Format(
@"CREATE TABLE {0}Data(
IdString varchar(38) NOT NULL PRIMARY KEY,
Host varchar(200) NOT NULL,
Path varchar(250) NOT NULL,
Data bytea,
DataLength bigint,
CreateDate timestamp,
CreateBy varchar(50)
)
GO
CREATE INDEX IX_{0}Data_Host ON {0}Data (Host)
GO
CREATE INDEX IX_{0}Data_Path ON {0}Data (Path)
",
this.TableName);
using (var db = new DapperContext(this.ConnectionStringName))
{
if (this.DataRepository is SqlDataRepository)
{
var dataTableExists =
db.Query<string>(
string.Format(
@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}Data'",
this.TableName), null).Any();
if (!dataTableExists)
{
foreach (var sql in
phunDataSchema.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
{
db.ExecuteSchema(sql);
}
}
}
if (db.Query<string>(string.Format(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", this.TableName), null).Any())
{
return;
}
foreach (var sql in this.SchemaSql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
{
db.ExecuteSchema(sql);
}
}
}
/// <summary>
/// Retrieves the history.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns>
/// The content history.
/// </returns>
public virtual IQueryable<ContentModel> RetrieveHistory(DapperContext context, ContentModel content, string tableName)
{
var result = context.Query<ContentModel>(string.Format("SELECT IdString AS DataIdString, Host, Path, DataLength, CreateDate, CreateBy FROM {0} WHERE Host = @Host and Path = @Path", tableName), content);
return result.AsQueryable();
}
/// <summary>
/// Finds the role id.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="roleName">Name of the role.</param>
/// <returns>RoleId or -1 if not exists.</returns>
private int FindRoleId(DapperContext db, string roleName)
{
var result = db.Query<int>(string.Format("SELECT RoleId FROM {0} WHERE RoleName = @RoleName", this.RoleTableName), new {RoleName = roleName}).FirstOrDefault();
return result > 0 ? result : -1;
}
/// <summary>
/// Gets the user ids from names.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="usernames">The usernames.</param>
/// <returns>List of user ids.</returns>
private List<int> GetUserIdsFromNames(DapperContext db, string[] usernames)
{
var userIds = new List<int>(usernames.Length);
foreach (string username in usernames)
{
var provider = new PhunSimpleMembershipProvider();
var id = provider.GetUserId(db, this.UserTableName, this.UserNameColumn, this.UserIdColumn, username);
if (id == -1)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, "No user found '{0}'.", username));
}
userIds.Add(id);
}
return userIds;
}
/// <summary>
/// Saves the specified content.
/// </summary>
/// <param name="content">The content - requires host, path, and name property.</param>
public virtual void Save(ContentModel content)
{
content.Path = this.NormalizedPath(content.Path);
bool isFile = !content.Path.EndsWith("/");
// this is an upsert
using (var db = new DapperContext(this.ConnectionStringName))
{
// save data first
if (isFile)
{
content.DataLength = content.DataLength ?? content.Data.Length;
this.DataRepository.SaveData(db, content, this.TableName + "Data", this.CachePath);
}
else
{
// set null to help with upsert for folder
content.DataId = null;
content.DataLength = null;
}
this.Save(content, db);
// create parent folders
var parentPath = content.ParentPath;
while (parentPath != "/")
{
var model = new ContentModel()
{
Path = parentPath,
Host = content.Host,
CreateBy = content.CreateBy,
CreateDate = content.CreateDate,
ModifyBy = content.ModifyBy,
ModifyDate = content.ModifyDate
};
this.Save(model, db);
parentPath = model.ParentPath;
}
}
}
/// <summary>
/// Verifies the user name has confirmed account.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="username">The username.</param>
/// <param name="throwException">if set to <c>true</c> [throw exception].</param>
/// <returns></returns>
/// <exception cref="System.InvalidOperationException">
/// </exception>
private int VerifyUserNameHasConfirmedAccount(DapperContext db, string username, bool throwException)
{
int userId = this.GetUserId(db, this.UserTableName, this.UserNameColumn, this.UserIdColumn, username);
if (userId == -1)
{
if (throwException)
{
throw new InvalidOperationException(
String.Format(CultureInfo.CurrentCulture, "User '{0}' not found.", username));
}
else
{
return -1;
}
}
int result =
db.Query<int>(string.Format("SELECT COUNT(*) FROM {0} WHERE UserId = @UserId AND ConfirmDate is not null", this.MembershipTableName), new {UserId = userId}).FirstOrDefault();
if (result == 0)
{
if (throwException)
{
throw new InvalidOperationException(
String.Format(CultureInfo.CurrentCulture, "No account found with user name '{0}'", username));
}
else
{
return -1;
}
}
return userId;
}
/// <summary>
/// Lists the specified content.Path
/// </summary>
/// <param name="content">The content.</param>
/// <returns>
/// Enumerable to content model.
/// </returns>
public override IQueryable<ContentModel> List(ContentModel content)
{
content.Path = this.NormalizedPath(content.Path);
// completely prevent any wildcard in this search since we're doing our own wildcard
content.Path = content.Path.Replace("*", string.Empty).TrimEnd('/') + "/";
using (var db = new DapperContext(this.ConnectionStringName))
{
return
db.Query<ContentModel>(
string.Format(
@"SELECT Path, CreateDate, CreateBy, ModifyDate, ModifyBy FROM {0} WHERE Host = @Host AND ParentPath = @ParentPath ORDER BY Path",
this.TableName),
new { Host = content.Host, ParentPath = content.Path }).AsQueryable();
}
}
/// <summary>
/// Removes the specified content path or route.
/// </summary>
/// <param name="content">The content - requires host, path, and name property. If name is set to "*" then removes all for host and path.</param>
public void Remove(ContentModel content)
{
content.Path = this.NormalizedPath(content.Path);
var path = content.Path;
var operString = "=";
if (path.EndsWith("/", StringComparison.OrdinalIgnoreCase))
{
operString = "LIKE";
path = path.TrimEnd('*').TrimEnd('/') + "%";
}
using (var db = new DapperContext(this.ConnectionStringName))
{
db.Execute(
string.Format(
"DELETE FROM {0} WHERE Host = @Host AND (Path {1} @Path)", this.TableName, operString),
new { Path = path, Host = content.Host });
}
}
/// <summary>
/// Gets the user id.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userTableName">Name of the user table.</param>
/// <param name="userNameColumn">The user name column.</param>
/// <param name="userIdColumn">The user id column.</param>
/// <param name="userName">Name of the user.</param>
/// <returns>The user id.</returns>
protected internal virtual int GetUserId(
DapperContext db, string userTableName, string userNameColumn, string userIdColumn, string userName)
{
var result =
db.Query<int>(
string.Format(
"SELECT {0} FROM {1} WHERE {2} = @UserName", userIdColumn, userTableName, userNameColumn),
new { UserName = userNameColumn }).FirstOrDefault();
return result > 0 ? result : -1;
}
/// <summary>
/// Gets the role ids from names.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="roleNames">The role names.</param>
/// <returns>List of role ids.</returns>
/// <exception cref="System.InvalidOperationException"></exception>
private List<int> GetRoleIdsFromNames(DapperContext db, string[] roleNames)
{
var roleIds = new List<int>(roleNames.Length);
foreach (string role in roleNames)
{
int id = this.FindRoleId(db, role);
if (id == -1)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, "No role found '{0}'.", role));
}
roleIds.Add(id);
}
return roleIds;
}
/// <summary>
/// Checks the password.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <param name="password">The password.</param>
/// <returns></returns>
private bool CheckPassword(DapperContext db, int userId, string password)
{
string hashedPassword = GetHashedPassword(db, userId);
bool verificationSucceeded = (hashedPassword != null
&& Crypto.VerifyHashedPassword(hashedPassword, password));
if (verificationSucceeded)
{
// Reset password failure count on successful credential check
db.Execute(string.Format("UPDATE {0} SET PasswordFailureCount = 0 WHERE UserId = @UserId", this.MembershipTableName),
new { UserId = userId });
}
else
{
int failures = this.GetPasswordFailureCount(db, userId);
if (failures != -1)
{
db.Execute(
string.Format(
"UPDATE {0} SET PasswordFailureCount = @Failures, LastPasswordFailureDate = @LastFailureDate WHERE UserId = @UserId",
this.MembershipTableName),
new { UserId = userId, Failures = failures + 1, LastFailureDate = DateTime.UtcNow });
}
}
return verificationSucceeded;
}
/// <summary>
/// Populates the history data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
public void PopulateHistoryData(DapperContext context, ContentModel content, string tableName)
{
if (string.IsNullOrEmpty(content.DataIdString))
{
throw new ArgumentException("PopulateHistoryData content.DataIdString is required.", "content");
}
var data =
context.Query<ContentModel>(
string.Format("SELECT Data, DataLength FROM {0} WHERE IdString = @DataIdString AND Host = @Host AND Path = @Path", tableName),
content).FirstOrDefault();
if (data == null)
{
return;
}
content.Data = data.Data;
content.DataLength = data.DataLength;
}
/// <summary>
/// Creates the O auth token table if needed.
/// </summary>
/// <param name="db">The db.</param>
private void CreateOAuthTokenTableIfNeeded(DapperContext db)
{
if (!db.TableExists(OAuthTokenTableName))
{
db.ExecuteSchema(
string.Format(
"create table {0} (Secret varchar(128) not null, Token varchar(128) not null primary key)",
this.OAuthTokenTableName));
}
}
/// <summary>
/// Saves the data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="cachePath">The cache path.</param>
/// <param name="keepHistory">if set to <c>true</c> [keep history].</param>
public virtual void SaveData(DapperContext context, ContentModel content, string tableName, string cachePath)
{
var newDataId = Guid.NewGuid();
var newContent = new ContentModel()
{
DataId = newDataId,
Host = content.Host,
Path = content.Path,
CreateBy =
string.IsNullOrEmpty(content.ModifyBy)
? content.CreateBy
: content.ModifyBy,
Data =
content.Data ?? content.DataStream.ReadAll(),
};
newContent.DataLength = newContent.Data.Length;
content.DataLength = newContent.DataLength;
newContent.ModifyDate = DateTime.UtcNow;
if (!newContent.CreateDate.HasValue || newContent.CreateDate.Value == DateTime.MinValue)
{
newContent.CreateDate = DateTime.UtcNow;
}
context.Execute(string.Format("DELETE FROM {0} WHERE Host = @Host AND Path = @Path", tableName), newContent);
context.Execute(
string.Format("INSERT INTO {0} (IdString, Host, Path, Data, DataLength, CreateDate, CreateBy) VALUES (@DataIdString, @Host, @Path, @Data, @DataLength, @CreateDate, @CreateBy)", tableName), newContent);
content.Data = newContent.Data;
content.DataLength = newContent.DataLength;
content.DataId = newContent.DataId;
this.CacheData(context, content, tableName, cachePath);
}
/// <summary>
/// Gets the hashed password.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <returns>
/// Hashed password.
/// </returns>
private string GetHashedPassword(DapperContext db, int userId)
{
var pwdQuery =
db.Query<string>(
string.Format("SELECT Password FROM {0} WHERE UserId = @UserId", this.MembershipTableName),
new { UserId = userId }).ToList();
if (pwdQuery.Any())
{
return pwdQuery.First();
}
return null;
}
private void Save(ContentModel content, DapperContext db)
{
// upsert means we have to use two separate statement since this would support sqlce
content.ModifyDate = DateTime.UtcNow;
if (!content.CreateDate.HasValue || content.CreateDate.Value == DateTime.MinValue)
{
content.CreateDate = DateTime.UtcNow;
}
var sqlCommand = string.Format(
@"UPDATE {0} SET ModifyDate = @ModifyDate, ModifyBy = @ModifyBy, DataIdString = @DataIdString, DataLength = @DataLength WHERE Host = @Host AND Path = @Path",
this.TableName);
db.Execute(sqlCommand, content);
sqlCommand =
string.Format(
"INSERT INTO {0} (Host, Path, ParentPath, CreateDate, CreateBy, ModifyDate, ModifyBy, DataIdString, DataLength) SELECT @Host, @Path, @ParentPath, @CreateDate, @CreateBy, @ModifyDate, @ModifyBy, @DataIdString, @DataLength WHERE NOT EXISTS (SELECT 1 FROM {0} WHERE Host = @Host AND Path = @Path)",
this.TableName);
db.Execute(sqlCommand, content);
}
/// <summary>
/// Gets the password failures since last success.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <returns></returns>
private int GetPasswordFailureCount(DapperContext db, int userId)
{
var failure =
db.Query<int>(
string.Format("SELECT PasswordFailureCount FROM {0} WHERE UserId = @UserId", this.MembershipTableName), new { User = userId}).FirstOrDefault();
return failure >= 0 ? failure : -1;
}
/// <summary>
/// Populate or gets the content provided specific host, path, and name property.
/// </summary>
/// <param name="content">The content - requires host, path, and name property.</param>
/// <param name="includeData">if set to <c>true</c> [include data].</param>
/// <returns>
/// The <see cref="ContentModel" /> that was passed in.
/// </returns>
public override ContentModel Retrieve(ContentModel content, bool includeData = true)
{
content.Path = this.NormalizedPath(content.Path);
var sqlCommand =
string.Format(
"SELECT CreateDate, CreateBy, ModifyDate, ModifyBy, DataIdString, DataLength FROM {0} WHERE Host = @Host AND Path = @Path",
this.TableName);
using (var db = new DapperContext(this.ConnectionStringName))
{
var result =
db.Query<ContentModel>(sqlCommand, new { Host = content.Host, Path = content.Path })
.FirstOrDefault();
if (result != null)
{
content.CreateDate = result.CreateDate;
content.CreateBy = result.CreateBy;
content.ModifyDate = result.ModifyDate;
content.ModifyBy = result.ModifyBy;
content.DataIdString = result.DataIdString;
content.DataLength = result.DataLength;
if (includeData && content.DataId.HasValue)
{
this.DataRepository.PopulateData(db, content, this.TableName + "Data", this.CachePath);
}
}
}
return content;
}
/// <summary>
/// Check for exist of content.
/// </summary>
/// <param name="content">The content - requires host, path, and name property.</param>
/// <returns>
/// true if content exists.
/// </returns>
public bool Exists(ContentModel content)
{
content.Path = this.NormalizedPath(content.Path);
using (var db = new DapperContext(this.ConnectionStringName))
{
return db.Query<string>(
string.Format(
"SELECT Host FROM {0} WHERE Host = @Host AND Path = @Path",
this.TableName),
new { Host = content.Host, Path = content.Path }).Any();
}
}
