/// <summary> /// 根据实例主键进行更新 /// </summary> /// <param name="action">更新动作</param> /// <param name="conditions">更新的条件</param> public static bool UpdateWhere <T>(Expression <Action <T> > action, Expression <Func <T, bool> > conditions) where T : OrmBaseModel { SqlVisitor visitor = new SqlVisitor(); visitor.Visit(action); var setStr = visitor.GetSql(); var setPrarmes = visitor.GetParameters(); visitor.Visit(conditions); var whereStr = visitor.GetSql(); var wherePrarmes = visitor.GetParameters(); var sqlStr = SqlCache <T> .GetSql(SqlType.UpdateWhere) + setStr + " WHERE " + whereStr; var allParames = new List <MySqlParameter>(); allParames.AddRange(setPrarmes); allParames.AddRange(wherePrarmes); return(ExceteSql <bool>(sqlStr, allParames.ToArray(), command => { var result = command.ExecuteNonQuery(); return result >= 1; })); }
///<summary> /// 按照主键进行查找对应的数据库数据 ///</summary> public static T Find <T>(string key) where T : OrmBaseModel { if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException("primary key can't be null"); } var sqlStr = SqlCache <T> .GetSql(SqlType.Find); var parameters = SqlCache <T> .GetFindMySqlParameter(key); return(ExceteSql <T>(sqlStr, parameters, command => { MySqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { Type type = typeof(T); PropertyInfo[] properies = SqlCache <T> .AllProperties; T result = Activator.CreateInstance <T>(); foreach (var proerty in properies) { var value = reader[proerty.GetMappingName()]; proerty.SetValue(result, value is DBNull ? null : value); } return result; } return default(T); })); }
public override void Update(UserModel model) { var Params = new ArrayList() { model.loginName, model.Password, model.AccessLevel }; if ((int)Enums.AccessLevel.Admin == model.AccessLevel) { Params.Add(DBNull.Value); Params.Add(DBNull.Value); } else if ((int)Enums.AccessLevel.CompanyAdmin == model.AccessLevel) { Params.Add(model.CompanyKey); Params.Add(DBNull.Value); } else if ((int)Enums.AccessLevel.PharmacyAdmin == model.AccessLevel || (int)Enums.AccessLevel.Operator == model.AccessLevel) { Params.Add(DBNull.Value); Params.Add(model.CompanyKey); } Params.Add(model.ID); Params.Add(model.UserName); Params.Add(model.Email); Params.Add(model.ContactNumber); SQLHandler sql = new SQLHandler(Params); sql.ExecuteNonQuery(SqlCache.GetSql("UpdateUser")); MessageCollection.copyFrom(sql.Messages); }
/// <summary> Gets delete statememt. </summary> /// <param name="type"> The type. </param> /// <returns> The delete statememt. </returns> public virtual string GetDeleteStatememt(Type type) { var key = SqlCache.TypeKeyPropertiesCache(type).Single(); return ($"DELETE FROM {RenderTableName(type)} WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}"); }
public void FillCompanyDllAction(int accessLevel) { SQLHandler sql = new SQLHandler(); if ((int)Enums.AccessLevel.CompanyAdmin == accessLevel) { dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetUserPharmaCompany")); } else if ((int)Enums.AccessLevel.PharmacyAdmin == accessLevel || (int)Enums.AccessLevel.Operator == accessLevel) { dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetUserPharmacy")); } MessageCollection.copyFrom(sql.Messages); if (dt == null || dt.Rows.Count <= 0) { MessageCollection.addMessage(new Message() { Context = "UsersHandler", ErrorCode = ErrorCache.RecordsNotFound, ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.RecordsNotFound) + " in Company/Pharmacy Table", isError = true, LogType = Enums.LogType.Exception, WebPage = "Users" }); } }
public override void DoAction() { ArrayList Params = new ArrayList() { userName, password }; SQLHandler sql = new SQLHandler(Params); dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("UserLogin")); MessageCollection.copyFrom(sql.Messages); if (!MessageCollection.isErrorOccured) { if (dt != null && dt.Rows.Count > 0) { Session[Enums.SessionName.UserDetails.ToString()] = dt; accessLevel = Convert.ToInt16(dt.Rows[0]["accesslevel"].ToString()); } else if (dt == null || dt.Rows.Count <= 0) { MessageCollection.addMessage(new Message() { Context = "LoginHandler", ErrorCode = ErrorCache.LoginFailed, ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.LoginFailed), isError = true, LogType = Enums.LogType.Exception, WebPage = "Login" }); } } }
/// <summary> Gets by identifier statement. </summary> /// <param name="type"> The type. </param> /// <returns> The by identifier statement. </returns> public virtual string GetByKeyStatement(Type type) { var key = SqlCache.TypeKeyPropertiesCache(type).Single(); return ($"SELECT {RenderPropertyList(SqlCache.TypePropertiesChache(type).ToArray())} FROM {RenderTableName(type)} WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}"); }
public void GetMedicineAgainstQRCode(string qrcode, DataTable viewData, int pharmacyid) { SQLHandler sql = new SQLHandler(new ArrayList() { qrcode }); dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetMedicinesForQRCode")); MessageCollection.copyFrom(sql.Messages); if (dt == null || dt.Rows.Count <= 0) { MessageCollection.addMessage(new Message() { Context = "MedicineHandler", ErrorCode = ErrorCache.RecordsNotFound, ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.RecordsNotFound), isError = true, LogType = Enums.LogType.Exception, WebPage = "Medicine" }); } Decrementquantity(dt.Rows[0].Field <int>("pharmacyid"), dt.Rows[0].Field <int>("medicineid")); viewData.Rows.Add(dt.Rows[0]); dt = viewData; }
/// <summary> /// 批量获取符合条件的数据 /// </summary> /// <param name="conditions">查找的条件</param> public static IList <T> FindWhere <T>(Expression <Func <T, bool> > conditions) where T : OrmBaseModel { SqlVisitor visitor = new SqlVisitor(); visitor.Visit(conditions); var sqlStr = SqlCache <T> .GetSql(SqlType.FindWhere) + visitor.GetSql(); var parameters = visitor.GetParameters(); return(ExceteSql <IList <T> >(sqlStr, parameters, command => { var result = new List <T>(); var type = typeof(T); var properies = SqlCache <T> .AllProperties; var adapter = new MySqlDataAdapter(command); var dataSet = new DataSet(); var count = adapter.Fill(dataSet); for (var i = 0; i < dataSet.Tables[0].Rows.Count; i++) { T t = Activator.CreateInstance <T>(); foreach (var proerty in properies) { var value = dataSet.Tables[0].Rows[i][proerty.GetMappingName()]; proerty.SetValue(t, value is DBNull ? null : value); } result.Add(t); } return result; })); }
/// <summary> Gets by filter statement. </summary> /// <param name="type"> The type. </param> /// <param name="filterProperties"> The filter properties. </param> /// <param name="selectFields"> The select fields. </param> /// <returns> The by filter statement. </returns> public virtual string GetByFilterStatement(Type type, string[] filterProperties, string[] selectFields) { var fProps = SqlCache.TypePropertiesChache(type).Where(pi => filterProperties.Contains(pi.Name)).ToArray(); var sb = new StringBuilder(); for (var i = 0; i < filterProperties.Length; i++) { if (i > 0) { sb.Append(" AND "); } sb.Append($"{RenderPropertyName(fProps[i])} = {RenderParameterProperty(fProps[i])}"); } var filterSql = sb.ToString(); if (selectFields == null || selectFields.Length < 1) { return ($"SELECT {RenderPropertyList(SqlCache.TypePropertiesChache(type).ToArray())} FROM {RenderTableName(type)} WHERE {filterSql}"); } var sProps = SqlCache.TypePropertiesChache(type).Where(pi => selectFields.Contains(pi.Name)).ToArray(); return ($"SELECT {RenderPropertyList(sProps)} FROM {RenderTableName(type)} WHERE {filterSql}"); }
public string GetSQL(string name) { if (SqlCache.TryGetValue(name, out var value)) { return(value[0]); } throw new KeyNotFoundException($"SQL element with name {name} is not found."); }
/// <summary> Gets the latest. </summary> /// <returns> The latest. </returns> public override SigningCredentialEntity GetLatest() { var command = $"SELECT TOP 1 {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(SigningCredentialEntity)).ToArray())} FROM {TableName} ORDER BY {nameof(SigningCredentialEntity.Issued)} DESC"; return(UnitOfWork.Connection.QuerySingleOrDefault <SigningCredentialEntity>(command, null, UnitOfWork.Transaction)); }
/// <summary> Finds the identifiers in this collection. </summary> /// <param name="roleIds"> List of identifiers for the roles. </param> /// <returns> /// An enumerator that allows foreach to be used to process the identifiers in this collection. /// </returns> public override IEnumerable <IdentityRoleEntity> FindByIds(IEnumerable <int> roleIds) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityRoleEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(IdentityUserEntity.Id)}\" = ANY(@Ids)"; return(UnitOfWork.Connection.Query <IdentityRoleEntity>(command, new { Ids = roleIds.ToArray() }, UnitOfWork.Transaction)); }
/// <summary> Gets the scope identifiers in this collection. </summary> /// <param name="ids"> The identifiers. </param> /// <returns> /// An enumerator that allows foreach to be used to process the scope identifiers in this /// collection. /// </returns> public override IEnumerable <IdentityResourceScopeEntity> GetByScopeIds(int[] ids) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityResourceScopeEntity)).ToArray())} FROM {TableName} WHERE {nameof(IdentityResourceScopeEntity.ScopeId)} IN @Ids"; return(UnitOfWork.Connection.Query <IdentityResourceScopeEntity>(command, new { Ids = ids }, UnitOfWork.Transaction)); }
/// <summary> Gets validation valid. </summary> /// <param name="validSince"> The valid since Date/Time. </param> /// <returns> The validation valid. </returns> public override IList <SigningCredentialEntity> GetValidationValid(DateTime validSince) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(SigningCredentialEntity)).ToArray())} FROM {TableName} WHERE {nameof(SigningCredentialEntity.Issued)} > @validSince"; return(UnitOfWork.Connection.Query <SigningCredentialEntity>(command, new { ValidSince = validSince }, UnitOfWork.Transaction).ToList()); }
/// <summary> Gets the names in this collection. </summary> /// <param name="names"> The names. </param> /// <returns> /// An enumerator that allows foreach to be used to process the names in this collection. /// </returns> public override IEnumerable <ScopeEntity> GetByNames(string[] names) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(ScopeEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(ScopeEntity.Name)}\" = ANY(@Names)"; return(UnitOfWork.Connection.Query <ScopeEntity>(command, new { Names = names }, UnitOfWork.Transaction)); }
/// <summary> Gets the name compounts in this collection. </summary> /// <param name="name"> The name. </param> /// <returns> /// An enumerator that allows foreach to be used to process the name compounts in this collection. /// </returns> public override CompoundApiResource GetByNameCompount(string name) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(ApiResourceEntity)).ToArray())} FROM {TableName} AS aRes" + $" LEFT JOIN {UnitOfWork.GetRepository<IApiResourceScopeRepository>().TableName} AS aScope" + $" ON aRes.{nameof(ApiResourceEntity.Id)} = aScope.{nameof(ApiResourceScopeEntity.ApiResourceId)}" + $" LEFT JOIN {UnitOfWork.GetRepository<IApiResourceClaimRepository>().TableName} AS aClaim" + $" ON aRes.{nameof(ApiResourceEntity.Id)} = aClaim.{nameof(ApiResourceClaimEntity.ApiResourceId)}" + $" LEFT JOIN {UnitOfWork.GetRepository<IScopeRepository>().TableName} AS scope" + $" ON aScope.{nameof(ApiResourceScopeEntity.ScopeId)} = scope.{nameof(ScopeEntity.Id)}" + $" WHERE aRes.{nameof(ApiResourceEntity.Name)} = @ResName"; var lookup = new Dictionary <int, CompoundApiResource>(); UnitOfWork.Connection .Query <ApiResourceEntity, ApiResourceScopeEntity, ApiResourceClaimEntity, ScopeEntity, CompoundApiResource>(command, (entity, apiScope, apiClaim, scope) => { // make sure the pk exists if (entity == null || entity.Id == default(int)) { return(null); } // make sure our list contains the pk if (!lookup.ContainsKey(entity.Id)) { lookup.Add(entity.Id, new CompoundApiResource { ApiResource = entity }); } // fetch the real element var tempElem = lookup[entity.Id]; // add api-scope if (apiScope != null) { tempElem.ApiResourceScopes.Add(apiScope); } // add claim if (apiClaim != null) { tempElem.ApiResourceClaims.Add(apiClaim); } // add scope if (scope != null) { tempElem.Scopes.Add(scope); } return(tempElem); }, new { ResName = name }, UnitOfWork.Transaction); return(lookup.Values.SingleOrDefault()); }
/// <summary> Gets insert automatic key statement. </summary> /// <param name="type"> The type. </param> /// <returns> The insert automatic key statement. </returns> public virtual string GetInsertAutoKeyStatement(Type type) { var key = SqlCache.TypeKeyPropertiesCache(type).Single(); var columnList = RenderAutoKeyColumnList(type).ToString(); var parameterList = RenderAutoKeyParameterList(type).ToString(); return ($"INSERT INTO {RenderTableName(type)} ({columnList}) VALUES ({parameterList}){GetAutoKeyStatement(key)}"); }
/// <summary> Gets an identity user entity using the given identifier. </summary> /// <param name="identifier"> The identifier to get. </param> /// <returns> An IdentityUserEntity. </returns> public override IdentityUserEntity Get(string identifier) { var command = $"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityUserEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(IdentityUserEntity.Identifier)}\" = @Identifier"; return(UnitOfWork.Connection.QuerySingleOrDefault <IdentityUserEntity>(command, new { Identifier = Guid.Parse(identifier) }, UnitOfWork.Transaction)); }
public void Init(SqlCache sqlCache) { var name = GetServerName(); sqlCache.AddSqlType(name); sqlCache.AddProvider(name, () => new SqliteCommonProvider()); sqlCache.AddProvider(name, () => new SqliteFunctionProvider()); sqlCache.AddProvider(name, () => new SqliteProcedureProvider()); sqlCache.AddProvider(name, () => new SqliteViewProvider()); }
public void Init(SqlCache sqlCache) { var name = GetServerName(); sqlCache.AddSqlType(name); sqlCache.AddProvider(name, () => new PostgreSQLCommonProvider()); sqlCache.AddProvider(name, () => new PostgreSQLFunctionProvider()); sqlCache.AddProvider(name, () => new PostgreSQLProcedureProvider()); sqlCache.AddProvider(name, () => new PostgreSQLViewProvider()); }
/// <summary> Gets update statement. </summary> /// <param name="type"> The type. </param> /// <returns> The update statement. </returns> public virtual string GetUpdateStatement(Type type) { var key = SqlCache.TypeKeyPropertiesCache(type).Single(); var setClauses = RenderSetStatements(type).ToString(); return ($"UPDATE {RenderTableName(type)} " + $"SET {setClauses} " + $"WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}"); }
public override void Update(PharmacyInventoryModel Model) { SQLHandler sql = new SQLHandler(new ArrayList() { Model.ID }); sql.ExecuteNonQuery(SqlCache.GetSql("UpdatePharmacyPOSInventory")); MessageCollection.copyFrom(sql.Messages); }
public static List <InvoiceCatalogModel> FindInvoices(DSModel db, InvoiceCatalogFilter filter) { if (db == null) { throw new ArgumentNullException("db"); } if (filter == null) { throw new ArgumentNullException("filter"); } string sql = SqlCache.Get(db, "invoice catalog load"); List <MySqlParameter> par = new List <MySqlParameter>(); if (!string.IsNullOrWhiteSpace(filter.InvoiceNumber)) { sql = sql.Replace("#InvoiceNumber", string.Empty); par.Add(new MySqlParameter("InvoiceNumber", filter.InvoiceNumber + "%")); } if (filter.IssuedFrom.HasValue && filter.IssuedFrom.Value != DateTime.MinValue) { sql = sql.Replace("#IssuedFrom", string.Empty); par.Add(new MySqlParameter("IssuedFrom", filter.IssuedFrom.Value.Date)); } if (filter.IssuedTo.HasValue && filter.IssuedTo.Value != DateTime.MinValue) { sql = sql.Replace("#IssuedTo", string.Empty); par.Add(new MySqlParameter("IssuedTo", filter.IssuedTo.Value.Date)); } if (filter.PeriodFrom.HasValue && filter.PeriodFrom.Value != DateTime.MinValue) { sql = sql.Replace("#PeriodFrom", string.Empty); par.Add(new MySqlParameter("PeriodFrom", filter.PeriodFrom.Value.Date)); } if (filter.PeriodTo.HasValue && filter.PeriodTo.Value != DateTime.MinValue) { sql = sql.Replace("#PeriodTo", string.Empty); par.Add(new MySqlParameter("PeriodTo", filter.PeriodTo.Value.Date)); } if (!string.IsNullOrWhiteSpace(filter.CompanyID)) { sql = sql.Replace("#CompanyID", string.Empty); sql = sql.Replace("@CompanyID", filter.CompanyID); } if (!string.IsNullOrWhiteSpace(filter.LocationID)) { sql = sql.Replace("#LocationID", string.Empty); sql = sql.Replace("@LocationID", filter.LocationID); } return(db.ExecuteQuery <InvoiceCatalogModel>(sql, par.ToArray()).ToList()); }
public void AddAndGetTest() { var cache = new SqlCache(); cache.Add(typeof(TestModel), "SELECT", null, null, "Test"); Assert.AreEqual(1, cache.Count); var sql = cache.Get(typeof(TestModel), "SELECT", null, null); Assert.AreEqual("Test", sql.SqlString); }
public (string CountSQL, string QuerySQL) GetPagingSQL(string name) { if (!SqlCache.TryGetValue(name, out var value)) { throw new KeyNotFoundException($"SQL element with name {name} is not found."); } if (value.Length != 2) { throw new ArgumentException("Wrong paging SQL configuration."); } return(value[0], value[1]); }
public void GetItem() { string key = "GetItem"; string data = "data"; SqlCache cache = new SqlCache(connectionString); cache.Add(key, data, DateTime.Now.AddMinutes(30)); var getData = cache.Get(key); Assert.AreEqual(data, getData); cache.Remove(key); // Clean DB for further testing }
public void Decrementquantity(int pharmID, int medid) { var Params = new ArrayList() { pharmID, medid }; SQLHandler sql = new SQLHandler(Params); sql.ExecuteNonQuery(SqlCache.GetSql("DecrementPharmacyInventory")); MessageCollection.copyFrom(sql.Messages); }
public override void Delete(int ID) { var Params = new ArrayList() { ID }; SQLHandler sql = new SQLHandler(Params); sql.ExecuteNonQuery(SqlCache.GetSql("DeleteMedicine")); MessageCollection.copyFrom(sql.Messages); }
public void AddItem() { string key = "AddItem"; string data = "data"; SqlCache cache = new SqlCache(connectionString); cache.Add(key, data, DateTime.Now.AddMinutes(30)); var result = cache.Contains(key); Assert.AreEqual(result, true); cache.Remove(key); // Clean DB for further testing }
public void RemoveItem() { var key = "RemoveItem"; var data = "data"; var cache = new SqlCache(connectionString, serializer); cache.Add(key, data, DateTime.Now.AddMinutes(30)); cache.Remove(key); var result = cache.Contains(key); Assert.AreEqual(result, false); }
public void RemoveItem() { string key = "RemoveItem"; string data = "data"; SqlCache cache = new SqlCache(connectionString); cache.Add(key, data, DateTime.Now.AddMinutes(30)); cache.Remove(key); var result = cache.Contains(key); Assert.AreEqual(result, false); }
public void GetItem() { var key = "GetItem"; var data = "data"; var cache = new SqlCache(connectionString, serializer); cache.Add(key, data, DateTime.Now.AddMinutes(30)); var getData = cache.Get(key); Assert.AreEqual(data, getData); cache.Remove(key); // Clean DB for further testing }
public void AddOrGetExisting() { string key = "AddOrGetExisting"; string data = "data"; SqlCache cache = new SqlCache(connectionString); // Try to get data var getData = cache.Get(key); Assert.IsNull(getData); // Add new entry getData = cache.AddOrGetExisting(key, data, DateTime.Now.AddMinutes(30)); Assert.IsNull(getData); // Retrieve added entry getData = cache.AddOrGetExisting(key, data, DateTime.Now.AddMinutes(30)); Assert.IsNotNull(getData); cache.Remove(key); // Clean DB for further testing }