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