public void Or() { if (WhereConditions.Count > 0) { WhereConditions.Add(" OR "); } }
public void AddDSCondition(ActDSConditon.eCondition wCond, string wColName, ActDSConditon.eOperator wOper, string wValue, List <string> mColName) { ActDSConditon ADSC = new ActDSConditon(); ObservableList <string> Condition = new ObservableList <string>(); if (wCond != ActDSConditon.eCondition.EMPTY) { foreach (ActDSConditon.eCondition item in Enum.GetValues(typeof(ActDSConditon.eCondition))) { if (item.ToString() != "EMPTY") { Condition.Add(item.ToString()); } } } List <string> colNames = new List <string>(); foreach (string sColName in mColName) { colNames.Add(sColName); } ADSC.PossibleCondValues = Condition; ADSC.PossibleColumnValues = colNames; WhereConditions.Add(ADSC); ADSC.wCondition = wCond; ADSC.wTableColumn = wColName; ADSC.wOperator = wOper; ADSC.wValue = wValue; }
public void QueryByConstant(object fieldValue) { var paramId = NextParamId(); WhereConditions.Add(Adapter.Parameter(paramId)); AddParameter(paramId, fieldValue); }
public void And() { if (WhereConditions.Count > 0) { WhereConditions.Add(" AND "); } }
private void Update(string parameters) { var(newProp, whereProp) = this.Parse(parameters); var set = newProp; var where = whereProp; var condition = new WhereConditions() { FirstName = null, LastName = null, DateOfBirth = null, Experience = null, Balance = null, EnglishLevel = null, }; this.CreateCondition(where, ref condition); var updated = this.fileCabinetService.FindByAnd(condition); foreach (var record in updated) { RecordData data = this.CreateDataForEditing(record, set); this.fileCabinetService.EditRecord(record.Id, data); } }
private void Select(string parameters) { var(prop, whereProp) = this.Parse(parameters); if (whereProp.Count == 0) { this.printer(this.fileCabinetService.GetRecords(), prop); return; } var condition = new WhereConditions() { FirstName = null, LastName = null, DateOfBirth = null, Experience = null, Balance = null, EnglishLevel = null, }; this.CreateCondition(whereProp, ref condition); if (this.or) { var selected = this.fileCabinetService.FindByOr(condition); this.printer(selected, prop); } else if (this.and) { var selected = this.fileCabinetService.FindByAnd(condition); this.printer(selected, prop); } }
public void QueryByFieldComparison(string leftTableName, string leftFieldName, string op, string rightTableName, string rightFieldName) { var newCondition = $"{Adapter.Field(leftTableName, leftFieldName)} {op} {Adapter.Field(rightTableName, rightFieldName)}"; WhereConditions.Add(newCondition); }
public void QueryByFieldLike(string tableName, string fieldName, string fieldValue) { var paramId = NextParamId(); var newCondition = $"{Adapter.Field(tableName, fieldName)} LIKE {Adapter.Parameter(paramId)}"; WhereConditions.Add(newCondition); AddParameter(paramId, fieldValue); }
/// <summary> /// Find all records, who is mathes the conditions. /// </summary> /// <param name="conditions">Find condtions.</param> /// <returns>Records sequance.</returns> public IEnumerable <FileCabinetRecord> FindByOr(WhereConditions conditions) { var collection = this.service.FindByOr(conditions); this.WriteLogInFile(nameof(this.service.FindByOr), conditions.ToString()); this.WriteLogReturnInFile(nameof(this.service.FindByOr), conditions.ToString()); return(collection); }
public void Case() { if (SelectionList.Count > 0) { throw new Exception("Case should not be called twice"); } WhereConditions.Add(" CASE "); }
/// <summary> /// 该方法没有对sql注入进行参数化过滤 /// </summary> /// <param name="expression"></param> /// <returns></returns> public virtual void Where(string expression) { if (string.IsNullOrEmpty(expression)) { throw new ArgumentNullException("必须传递参数 expression"); } WhereConditions.Add($"({expression})"); }
public void QueryByFieldLike(string tableName, string fieldName, string fieldValue) { var paramId = NextParamId(); var newCondition = string.Format("{0} LIKE {1}", Adapter.Field(tableName, fieldName), Adapter.Parameter(paramId)); WhereConditions.Add(newCondition); AddParameter(paramId, fieldValue); }
public void QueryByFieldComparison(string leftTableName, string leftFieldName, string op, string rightTableName, string rightFieldName) { var newCondition = string.Format("{0} {1} {2}", Adapter.Field(leftTableName, leftFieldName), op, Adapter.Field(rightTableName, rightFieldName)); WhereConditions.Add(newCondition); }
/// <summary> /// 获取维度数据的文本 /// </summary> /// <param name="pIDs">维度数据ID数组</param> /// <param name="pLanguage">用户的语言选择</param> /// <returns>维度数据的文本</returns> public Dictionary <string, string> GetTexts(string[] pIDs, Languages pLanguage) { if (pIDs == null || pIDs.Length <= 0) { return(new Dictionary <string, string>()); } //组织SQL语句 List <IWhereCondition> wheres = new List <IWhereCondition>(); wheres.Add(new InCondition <string>() { FieldName = this.IDFieldName, Values = pIDs }); if (this.HasIsDelete.HasValue && this.HasIsDelete.Value == true) { wheres.Add(new EqualsCondition() { FieldName = this.IsDeleteFieldName, Value = 1 }); } string textFieldName = this.GetTextFieldNameByLanguage(pLanguage); StringBuilder sql = new StringBuilder(); sql.AppendFormat("select distinct {0},{1} ", this.IDFieldName, textFieldName); sql.AppendFormat(" from ({0}) a where 1=1 ", this.SqlTemplate); sql.AppendFormat(WhereConditions.GenerateWhereSentence(wheres.ToArray())); //执行并返回结果 Dictionary <string, string> result = new Dictionary <string, string>(); // using (SqlDataReader rdr = this.SqlHelper.ExecuteReader(sql.ToString())) { while (rdr.Read()) { string key = null, value = null; if (rdr[0] != DBNull.Value) { key = rdr[0].ToString(); } if (rdr[1] != DBNull.Value) { value = rdr[1].ToString(); } if (key != null) { result.Add(key, value); } } } //返回结果 return(result); }
/// <summary> /// Find all records, who is mathes the conditions. /// </summary> /// <param name="conditions">Find condtions.</param> /// <returns>Records sequance.</returns> public IEnumerable <FileCabinetRecord> FindByOr(WhereConditions conditions) { if (conditions is null) { throw new ArgumentNullException($"{nameof(conditions)} cannot be null."); } var collection = this.service.FindByOr(conditions); this.WriteLogInFile(nameof(this.service.FindByOr), conditions.ToString()); this.WriteLogReturnInFile(nameof(this.service.FindByOr), conditions.ToString()); return(collection); }
public void QueryByIsIn(string tableName, string fieldName, IEnumerable <object> values) { var paramIds = values.Select(x => { var paramId = NextParamId(); AddParameter(paramId, x); return(Adapter.Parameter(paramId)); }); var newCondition = $"{Adapter.Field(tableName, fieldName)} IN ({string.Join(",", paramIds)})"; WhereConditions.Add(newCondition); }
public void QueryByIsIn <T>(string tableName, string fieldName, IEnumerable <T> values) { var paramIds = values.Select(x => { var paramId = NextParamId(); AddParameter(paramId, x); return(Adapter.Parameter(paramId)); }); var newCondition = string.Format("{0} IN ({1})", Adapter.Field(tableName, fieldName), string.Join(",", paramIds)); WhereConditions.Add(newCondition); }
/// <summary> /// 更新抽检规则数据。 /// </summary> /// <param name="dsParams">包含抽检规则的数据集对象。</param> /// <returns>包含执行结果的数据集对象。</returns> public DataSet UpdateSamp(DataSet dsParams) { DataSet dsReturn = new DataSet(); try { if (null != dsParams && dsParams.Tables.Contains(EDC_SP_FIELDS.DATABASE_TABLE_NAME)) { DataTable dtParams = dsParams.Tables[EDC_SP_FIELDS.DATABASE_TABLE_NAME]; Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams); using (DbConnection dbConn = db.CreateConnection()) { //Open Connection dbConn.Open(); //Create Transaction DbTransaction dbTran = dbConn.BeginTransaction(); try { EDC_SP_FIELDS spFields = new EDC_SP_FIELDS(); WhereConditions wc = new WhereConditions(EDC_SP_FIELDS.FIELD_SP_KEY, htParams[EDC_SP_FIELDS.FIELD_SP_KEY].ToString()); htParams.Remove(EDC_SP_FIELDS.FIELD_SP_KEY); string sqlCommand = DatabaseTable.BuildUpdateSqlStatement(spFields, htParams, wc); db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand); //Commit Transaction dbTran.Commit(); FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ""); } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message); //Rollback Transaction dbTran.Rollback(); LogService.LogError("UpdateSamp Error: " + ex.Message); } finally { dbTran = null; //Close Connection dbConn.Close(); } } } } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message); LogService.LogError("UpdateSamp Error: " + ex.Message); } return(dsReturn); }
/// <summary> /// 更新角色。 /// </summary> /// <param name="dsParams">包含角色数据的数据集对象。</param> /// <returns>包含执行结果的数据集对象。</returns> public DataSet UpdateRole(DataSet dsParams) { DataSet dsReturn = new DataSet(); DbConnection dbconn = null; DbTransaction dbtran = null; string sql = ""; try { dbconn = db.CreateConnection(); dbconn.Open(); //Create Transaction dbtran = dbconn.BeginTransaction(); RBAC_ROLE_FIELDS roleFields = new RBAC_ROLE_FIELDS(); if (null != dsParams && dsParams.Tables.Contains(RBAC_ROLE_FIELDS.DATABASE_TABLE_NAME)) { DataTable dataTable = dsParams.Tables[RBAC_ROLE_FIELDS.DATABASE_TABLE_NAME]; Hashtable hashData = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable); hashData.Add(RBAC_ROLE_FIELDS.FIELD_EDIT_TIME, null); string roleKey = Convert.ToString(hashData[RBAC_ROLE_FIELDS.FIELD_ROLE_KEY]); WhereConditions wc = new WhereConditions(RBAC_ROLE_FIELDS.FIELD_ROLE_KEY, roleKey); hashData.Remove(RBAC_ROLE_FIELDS.FIELD_ROLE_KEY); sql = DatabaseTable.BuildUpdateSqlStatement(roleFields, hashData, wc); db.ExecuteNonQuery(dbtran, CommandType.Text, sql); dbtran.Commit(); } FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty); } catch (Exception ex) { dbtran.Rollback(); FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message); LogService.LogError("UpdateRole Error: " + ex.Message); } finally { if (dbtran != null) { dbtran = null; } //Close Connection if (dbconn != null) { dbconn.Close(); dbconn = null; } } return(dsReturn); }
/// <summary> /// Find all records, who is mathes the conditions. /// </summary> /// <param name="conditions">Find condtions.</param> /// <returns>Records sequance.</returns> public IEnumerable <FileCabinetRecord> FindByOr(WhereConditions conditions) { if (conditions is null) { throw new ArgumentNullException($"{nameof(conditions)} cannot be null."); } this.stopwatch.Restart(); var collection = this.service.FindByOr(conditions); this.stopwatch.Stop(); this.Information(nameof(this.service.FindByOr), this.stopwatch.ElapsedTicks); return(collection); }
public void QueryByIsIn(string tableName, string fieldName, SqlBuilderBase sqlQuery) { var innerQuery = sqlQuery.CommandText; foreach (var param in sqlQuery.CommandParameters) { var innerParamKey = "Inner" + param.Key; innerQuery = Regex.Replace(innerQuery, param.Key, innerParamKey); AddParameter(innerParamKey, param.Value); } var newCondition = string.Format("{0} IN ({1})", Adapter.Field(tableName, fieldName), innerQuery); WhereConditions.Add(newCondition); }
public void QueryByIsIn(string tableName, string fieldName, SqlLamBase sqlQuery) { var innerQuery = sqlQuery.QueryString; foreach (var param in sqlQuery.QueryParameters) { var innerParamKey = "Inner" + param.Key; innerQuery = Regex.Replace(innerQuery, param.Key, innerParamKey); AddParameter(innerParamKey, param.Value); } var newCondition = $"{Adapter.Field(tableName, fieldName)} IN ({innerQuery})"; WhereConditions.Add(newCondition); }
public void DeExpression() { if (WhereExpressions.Count > 0) { foreach (var item in WhereExpressions) { DbExpressionVisitor expression = new DbExpressionVisitor(); expression.Visit(item.Body); WhereConditions.Add(expression.SqlText.Builder.ToString().ToLower()); foreach (var p in expression.SqlText.Parameters) { AddParameter(p.Name, p.Value); } } } }
public void QueryBetween <T>(string tableName, string fieldName, T start, T end, bool not) { var paramStart = NextParamId(); AddParameter(paramStart, start); var pStart = Adapter.Parameter(paramStart); var paramEnd = NextParamId(); AddParameter(paramEnd, end); var pEnd = Adapter.Parameter(paramEnd); var newCondition = $"({ Adapter.Field(tableName, fieldName) } { (not ? "NOT " : "") }BETWEEN { pStart } AND { pEnd })"; WhereConditions.Add(newCondition); }
/// <summary> /// Find all records, who is mathes the conditions. /// </summary> /// <param name="conditions">Find condtions.</param> /// <returns>Records sequance.</returns> public IEnumerable <FileCabinetRecord> FindByOr(WhereConditions conditions) { if (conditions is null) { throw new ArgumentNullException($"{nameof(conditions)} cannot be null."); } bool isMath = false; foreach (var item in this.GetRecords()) { if (conditions.FirstName != null) { isMath = conditions.FirstName == item.FirstName || isMath; } if (conditions.LastName != null) { isMath = conditions.LastName == item.LastName || isMath; } if (conditions.DateOfBirth != null) { isMath = conditions.DateOfBirth == item.DateOfBirth || isMath; } if (conditions.Experience != null) { isMath = conditions.Experience == item.Experience || isMath; } if (conditions.Balance != null) { isMath = conditions.Balance == item.Balance || isMath; } if (conditions.EnglishLevel != null) { isMath = conditions.EnglishLevel == item.EnglishLevel || isMath; } if (isMath) { yield return(item); } } }
/// <summary> /// 保存用户权限。 /// </summary> private void SaveUserPrivilege(Database db, DbTransaction dbtran, DataSet dsParams) { string sqlCommand = ""; if (dsParams.Tables.Contains(RBAC_USER_OWN_PRIVILEGES_FIELDS.DATABASE_TABLE_NAME)) { DataTable dtParams = dsParams.Tables[RBAC_USER_OWN_PRIVILEGES_FIELDS.DATABASE_TABLE_NAME]; RBAC_USER_OWN_PRIVILEGES_FIELDS userOwnPrivilegeFields = new RBAC_USER_OWN_PRIVILEGES_FIELDS(); RBAC_PRIVILEGE_FIELDS privilegeFields = new RBAC_PRIVILEGE_FIELDS(); for (int i = 0; i < dtParams.Rows.Count; i++) { string privilegeKey = Convert.ToString(dtParams.Rows[i][RBAC_USER_OWN_PRIVILEGES_FIELDS.FIELD_PRIVILEGE_KEY]); WhereConditions delCondition = new WhereConditions(RBAC_USER_OWN_PRIVILEGES_FIELDS.FIELD_PRIVILEGE_KEY, privilegeKey); OperationAction action = (OperationAction)Convert.ToInt32(dtParams.Rows[i][COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION]); switch (action) { case OperationAction.New: sqlCommand = DatabaseTable.BuildInsertSqlStatement(userOwnPrivilegeFields, dtParams, i, new Dictionary <string, string>() { { RBAC_USER_OWN_PRIVILEGES_FIELDS.FIELD_EDIT_TIME, null }, }, new List <string>() { COMMON_FIELDS.FIELD_COMMON_OPERATION_ACTION }); db.ExecuteNonQuery(dbtran, CommandType.Text, sqlCommand); break; case OperationAction.Delete: string userKey = Convert.ToString(dtParams.Rows[i][RBAC_USER_OWN_PRIVILEGES_FIELDS.FIELD_USER_KEY]); sqlCommand = DatabaseTable.BuildDeleteSqlStatement(userOwnPrivilegeFields, delCondition); sqlCommand += " AND USER_KEY='" + userKey.PreventSQLInjection() + "'"; db.ExecuteNonQuery(dbtran, CommandType.Text, sqlCommand); sqlCommand = DatabaseTable.BuildDeleteSqlStatement(privilegeFields, delCondition); db.ExecuteNonQuery(dbtran, CommandType.Text, sqlCommand); break; default: break; } } } }
private string DeleteSql() { StringBuilder sb = new StringBuilder(); sb.AppendFormat("DELETE {0}\r\n", BaseTable().Alias); sb.AppendFormat(" FROM {0}\r\n", BaseTable().ReferenceName); foreach (Join j in JoinConditions) { sb.AppendFormat("{0}\r\n", j.ToSql()); } string where = WhereConditions.ToSql(); if (!string.IsNullOrEmpty(where)) { sb.AppendFormat("WHERE {0}\r\n", where); } return(sb.ToString()); }
/// <summary> /// Find all records, who is mathes the conditions. /// </summary> /// <param name="conditions">Find condtions.</param> /// <returns>Records sequance.</returns> public IEnumerable <FileCabinetRecord> FindByAnd(WhereConditions conditions) { if (conditions is null) { throw new ArgumentNullException($"{nameof(conditions)} cannot be null."); } foreach (var item in this.GetRecords()) { bool isMath = true; if (conditions.FirstName != null) { isMath = conditions.FirstName == item.FirstName && isMath; } if (conditions.LastName != null) { isMath = conditions.LastName == item.LastName && isMath; } if (conditions.DateOfBirth != null) { isMath = conditions.DateOfBirth == item.DateOfBirth && isMath; } if (conditions.Experience != null) { isMath = conditions.Experience == item.Experience && isMath; } if (conditions.Account != null) { isMath = conditions.Account == item.Account && isMath; } if (conditions.Gender != null) { isMath = conditions.Gender == item.Gender && isMath; } if (isMath) { yield return(item); } } }
private string UpdateSql() { StringBuilder sb = new StringBuilder(); UpdateTable BaseTable = this.BaseTable() as UpdateTable; string declare = ""; string set = ""; string outputSelect = ""; SqlBuilder tb = this.TopBuilder; foreach (ParameterField field in BaseTable.FieldList.OfType <ParameterField>()) { tb.AddDeclaration(field.ParameterName, field.DeclareParameter()); set += field.SetParameter() + "\r\n"; } if (BaseTable.Output != null && BaseTable.Output.ParameterTable.FieldList.Count > 0) { tb.AddDeclaration(BaseTable.Output.ParameterName, BaseTable.Output.DeclareParameter()); outputSelect += BaseTable.Output.SetParameter() + "\r\n"; } sb.AppendLine(declare); sb.AppendLine(set); sb.AppendFormat("UPDATE {0}\r\n", BaseTable.Name); sb.AppendFormat(" SET {0}\r\n", BaseTable.ToSql()); if (BaseTable.Output != null && BaseTable.Output.ParameterTable.FieldList.Count > 0) { sb.AppendFormat("OUTPUT {0}\r\n", BaseTable.Output.ToSql()); } sb.AppendFormat(" FROM {0}\r\n", BaseTable.ReferenceName); foreach (Join j in JoinConditions) { sb.AppendFormat("{0}\r\n", j.ToSql()); } string where = WhereConditions.ToSql(); if (!string.IsNullOrEmpty(where)) { sb.AppendFormat("WHERE {0}\r\n", where); } if (!string.IsNullOrEmpty(outputSelect)) { sb.AppendLine(outputSelect); } return(sb.ToString()); }
/// <summary> /// 更新批次基础数据。 /// </summary> /// <param name="db">数据库对象。</param> /// <param name="dbtran">数据库事务对象。</param> /// <param name="dsParams">包含批次基础数据的数据集对象。</param> public static void UpdateLotBasicInfo(Database db, DbTransaction dbtran, DataSet dsParams) { DataSet dsReturn = new DataSet(); string sql = ""; if (dsParams.Tables.Contains(POR_LOT_FIELDS.DATABASE_TABLE_NAME)) { DataTable dtParams = dsParams.Tables[POR_LOT_FIELDS.DATABASE_TABLE_NAME]; Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams); //initialize tablefields POR_LOT_FIELDS porLotFields = new POR_LOT_FIELDS(); string lotKey = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_KEY]); WhereConditions wc = new WhereConditions(POR_LOT_FIELDS.FIELD_LOT_KEY, lotKey); //detail deal sql = DatabaseTable.BuildUpdateSqlStatement(porLotFields, htParams, wc); db.ExecuteNonQuery(dbtran, CommandType.Text, sql); } }
/// <summary> /// Build custom where /// </summary> /// <param name="columnName">collumn</param> /// <param name="condition">condition</param> /// <param name="value">Value</param> /// <param name="comparingCollumns">True if comparing collumns</param> /// <returns>SQL query</returns> public string BuildWhere(string columnName, WhereConditions condition, object value, bool comparingCollumns) { return "SELECT * FROM " + TableName + " where `" + columnName + "` " + ConditionToString(condition) + (comparingCollumns ? value.ToString() : NormalizeInput(value)); }
private string ConditionToString(WhereConditions condition) { string @operator = ""; switch (condition) { case WhereConditions.Equal: @operator = " = "; break; case WhereConditions.Diferent: @operator = " <> "; break; case WhereConditions.Hihger: @operator = " > "; break; case WhereConditions.Lower: @operator = " < "; break; case WhereConditions.HigherOrEqual: @operator = " >= "; break; case WhereConditions.LowerOrEqual: @operator = " <= "; break; case WhereConditions.Like: @operator = " LIKE "; break; } return @operator; }