private object BuildQuery(bool buildCommand)
{
if (buildCommand && _dbProviderFactory == null)
{
throw new Exception(
"Cannot build a command when the Db Factory hasn't been specified. Call SetDbProviderFactory first.");
}
var usedDbCommand = (DbCommand)null;
if (buildCommand)
{
usedDbCommand = _dbProviderFactory.CreateCommand();
}
var str1 = "UPDATE ";
if (_table.Length == 0)
{
throw new Exception("Table to update was not set.");
}
var str2 = str1 + _table;
if (_fieldValuePairs.Count == 0)
{
throw new Exception("Nothing to update.");
}
var str3 = str2 + " SET ";
foreach (var fieldValuePair in _fieldValuePairs)
{
if (buildCommand && fieldValuePair.Value != null && fieldValuePair.Value != DBNull.Value &&
!(fieldValuePair.Value is SqlLiteral) && !(fieldValuePair.Value is SelectQueryBuilder))
{
var str4 = string.Format("@p{0}_{1}", usedDbCommand.Parameters.Count + 1, fieldValuePair.FieldName);
var parameter = usedDbCommand.CreateParameter();
parameter.ParameterName = str4;
parameter.Value = fieldValuePair.Value;
usedDbCommand.Parameters.Add(parameter);
str3 = str3 + fieldValuePair.FieldName + "=" + str4 + ", ";
}
else
{
str3 = str3 + fieldValuePair.FieldName + "=";
str3 = str3 + WhereStatement.FormatSQLValue(fieldValuePair.Value) + ", ";
}
}
var str5 = str3.Substring(0, str3.Length - 2) + " ";
if (_whereStatement.ClauseLevels > 0)
{
str5 = !buildCommand
? str5 + " WHERE " + _whereStatement.BuildWhereStatement()
: str5 + " WHERE " + _whereStatement.BuildWhereStatement(true, ref usedDbCommand);
}
if (!buildCommand)
{
return(str5);
}
usedDbCommand.CommandText = str5;
return(usedDbCommand);
}
public string GetProperty(object idValue, string idField = null, WhereStatement whereStatement = null)
{
string returnValue = string.Empty;
SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper);
sqlBuilder.SetSelectAllColumns();
sqlBuilder.SetFromTable(this.CurrentTableName);
if (whereStatement == null)
{
whereStatement = new WhereStatement(this.DbHelper);
}
if (idField == null)
{
whereStatement.Add(this._iEntity.PrimaryKey, Comparison.Equals, idValue);
}
else
{
whereStatement.Add(idField, Comparison.Equals, idValue);
}
whereStatement.DbHelper = this.DbHelper;
sqlBuilder.AddWhere(whereStatement);
DbParameter[] parameters = null;
string sql = sqlBuilder.BuildSQL(out parameters);
object returnObject = this.DbHelper.ExecuteScalar(sql, parameters);
if (returnObject != null)
{
returnValue = returnObject.ToString();
}
return(returnValue);
}
/// <summary>
/// 根据条件，获取数据（分页）
/// </summary>
/// <param name="whereStatement">条件对象</param>
/// <param name="recordCount">数据数量</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页容量</param>
/// <param name="orderByStatement">排序对象</param>
/// <returns></returns>
public virtual DataTable GetDataTableByPage(WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null)
{
DataTable dataTable = new DataTable(this.CurrentTableName);
whereStatement.DbHelper = this.DbHelper;
SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper);
sqlBuilder.SetSelectAllColumns();
sqlBuilder.SetFromTable(this.CurrentTableName);
sqlBuilder.AddWhere(whereStatement);
DbParameter[] parameters = null;
string sql = sqlBuilder.BuildSQL(out parameters);
recordCount = Convert.ToInt32(this.DbHelper.ExecuteScalar(PagingBuilder.CreateCountingSql(sql), parameters));
if (pageIndex * 1.0 > Math.Ceiling(recordCount * 1.0 / pageSize * 1.0))
{
//超过总页数
return(dataTable);
}
if (orderByStatement == null)
{
orderByStatement = new OrderByStatement("ID", Sorting.Ascending);
}
string orderBySql = orderByStatement.BuildOrderByStatement();
string pagedSql = PagingBuilder.CreatePagingSql(recordCount, pageSize, pageIndex, sql, orderBySql);
dataTable = this.DbHelper.Fill(pagedSql, parameters);
return(dataTable);
}
/// <summary>
/// 获取我处理中的工单
/// </summary>
/// <returns></returns>
public DataTable GetMyHandlingOrders(int pageIndex, int pageSize, out int count)
{
PermissionService permissionService = new PermissionService();
ViewManager manager = new ViewManager("VO_Order");
//1.0 获取当前员工数据
StaffEntity staff = permissionService.GetCurrentStaff();
//2.0 构建where从句
WhereStatement where = new WhereStatement();
WhereClause clause = new WhereClause(OrderEntity.FieldStatus, Comparison.Equals, (int)OrderStatus.Handling);
clause.AddClause(LogicOperator.Or, Comparison.Equals, (int)OrderStatus.Rejected);
where.Add(clause);
where.Add(OrderEntity.FieldHandlerID, Comparison.Equals, staff.ID);
//2.1 构建orderby 从句
OrderByStatement orderby = new OrderByStatement();
orderby.Add(OrderEntity.FieldPriority, Sorting.Descending);
orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending);
//3.0 获取已发起的数据
DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby);
return(dt);
}
public List <T> GetListByPage(WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null)
{
DataTable dataTable = this.GetDataTableByPage(whereStatement, out recordCount, pageIndex, pageSize, orderByStatement);
List <T> list = ToList(dataTable);
return(list);
}
public ExecuteBuilder()
{
_tablename = "";
_datatable = new DataTable();
_whereStatement = new WhereStatement();
_dbProviderFactory = null;
}
/// <inheritdoc />
public ISelectStringBuilder Where(string columnName, ComparisonOperator @operator, object value)
{
var clause = new WhereClause(this, columnName, @operator, value);
WhereStatement.Add(clause);
return(this);
}
/// <inheritdoc />
public WhereClause Where(string columnName)
{
var clause = new WhereClause(this, columnName);
WhereStatement.Add(clause);
return(clause);
}
public virtual List <T> GetList(WhereStatement whereStatement, out int recordCount, OrderByStatement orderByStatement = null)
{
DataTable dataTable = this.GetDataTable(whereStatement, out recordCount, orderByStatement);
List <T> list = ToList(dataTable);
return(list);
}
private void Clear()
{
JoinStatement.Clear();
WhereStatement.Clear();
StatmentTemplate.Clear();
_parameters.Clear();
}
/// <summary>
/// 获取我的已发起工单
/// </summary>
/// <returns></returns>
public DataTable GetMyStartedOrders(int pageIndex, int pageSize, out int count)
{
WhereStatement where = new WhereStatement();
//string sStatus = JSRequest.GetRequestUrlParm(OrderEntity.FieldStatus, false);
//int? status = JSValidator.ValidateInt(OrderEntity.FieldStatus, sStatus, false);
//if (status != null) { where.Add(OrderEntity.FieldStatus, Comparison.Equals, status); }
PermissionService permissionService = new PermissionService();
ViewManager manager = new ViewManager("VO_Order");
//1.0 获取当前员工数据
StaffEntity staff = permissionService.GetCurrentStaff();
//2.0 构建where从句
where.Add(OrderEntity.FieldStarterID, Comparison.Equals, staff.ID);
where.Add(OrderEntity.FieldStatus, Comparison.NotEquals, (int)OrderStatus.Canceled);
OrderByStatement orderby = new OrderByStatement();
orderby.Add(OrderEntity.FieldPriority, Sorting.Descending);
orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending);
//3.0 获取已发起的数据
DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby);
return(dt);
}
public static bool Exists(string name, Dictionary <string, object> entry)
{
var statement = new WhereStatement();
foreach (var key in entry.Keys)
{
var whereClause = new WhereClause {
FieldName = key, ComparisonOperator = Comparison.Equals, Value = entry[key]
};
statement.Add(whereClause);
}
if (statement.Count == 0)
{
return(false);
}
if (!SQLiteProvider.Provider.GetStoreNames().Any(table => table == name))
{
return(false);
}
return(SQLiteProvider.Provider.Single(name, statement, "*") != null);
}
public SQLSelectQuery(SQLSelectQuery query)
{
this.IsDistinct = query.IsDistinct;
foreach(string column in query._selectedColumns) {
this._selectedColumns.Add(column);
}
foreach (string column in query._selectedTables) {
this._selectedTables.Add(column);
}
this._whereStatement = WhereStatement.Copy(query._whereStatement);
foreach (OrderByClause clause in query._orderByStatement) {
this._orderByStatement.Add(clause);
}
foreach (string column in query._groupByColumns) {
this._groupByColumns.Add(column);
}
this._havingStatement = WhereStatement.Copy(query._havingStatement);
if (query._limitStatement != null)
this._limitStatement = new LimitClause(query._limitStatement.From, query._limitStatement.To);
}
public virtual string[] GetPropertiesByPage(string selectFieldName, WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null)
{
whereStatement.DbHelper = this.DbHelper;
SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper);
sqlBuilder.SetSelectColumns(selectFieldName);
sqlBuilder.SetFromTable(this.CurrentTableName);
sqlBuilder.AddWhere(whereStatement);
DbParameter[] parameters = null;
string sql = sqlBuilder.BuildSQL(out parameters);
recordCount = Convert.ToInt32(this.DbHelper.ExecuteScalar(PagingBuilder.CreateCountingSql(sql), parameters));
if (orderByStatement == null)
{
orderByStatement = new OrderByStatement(this._iEntity.PrimaryKey, Sorting.Ascending);
}
string orderBySql = orderByStatement.BuildOrderByStatement();
string pagedSql = PagingBuilder.CreatePagingSql(recordCount, pageSize, pageIndex, sql, orderBySql);
DataTable dataTable = new DataTable(this.CurrentTableName);
dataTable = this.DbHelper.Fill(pagedSql, parameters);
return(DataTableUtil.FieldToArray(dataTable, selectFieldName));
}
protected BaseSQLBuilder(IDbHelper dbHelper)
: this()
{
this._dbHelper = dbHelper;
this._whereStatement = new WhereStatement(dbHelper);
this._sqlOperation = DbOperation.Select;
}
/// <summary>
/// 获取对应code下一层的button
/// </summary>
/// <param name="role"></param>
/// <param name="resourceCode"></param>
/// <returns></returns>
public DataTable GetButton(RoleEntity role, string resourceCode)
{
ViewManager vmanager = new ViewManager("VP_RolePermission");
WhereStatement where = new WhereStatement();
where.Add("Resource_SortCode", Comparison.Equals, resourceCode);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count);
if (count == 0)
{
throw new JSException(JSErrMsg.ERR_CODE_DATA_MISSING, string.Format(JSErrMsg.ERR_MSG_DATA_MISSING, resourceCode));
}
if (count > 1)
{
throw new JSException(JSErrMsg.ERR_CODE_DATA_REPETITION, string.Format(JSErrMsg.ERR_MSG_DATA_REPETITION, "Resource表的" + resourceCode));
}
WhereStatement where1 = new WhereStatement();
where1.Add("Resource_ParentID", Comparison.Equals, dt.Rows[0]["Resource_ParentID"].ToString());
where1.Add("RolePermission_RoleID", Comparison.Equals, role.ID);
int count1 = 0;
DataTable dt1 = vmanager.GetDataTable(where1, out count1);
return(dt1);
}
public Dictionary <string, List <string> > GetRolePermissionScope(RoleEntity role, string resouceCode)
{
Dictionary <string, List <string> > dic = new Dictionary <string, List <string> >();
ViewManager vmanager = new ViewManager("VP_UserRolePermissionScope");
WhereStatement where = new WhereStatement();
where.Add("Type", Comparison.Equals, ResourceType.Data.ToString());
where.Add("RoleID", Comparison.Equals, role.ID);
where.Add("Resource_Code", Comparison.Equals, resouceCode);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count);// TODO 这里会有性能问题
if (count == 0)
{
throw new JSException(JSErrMsg.ERR_CODE_NotGrantResource, JSErrMsg.ERR_MSG_NotGrantResource);
}
foreach (DataRow dr in dt.Rows)
{
if (!dic.ContainsKey(dr["OrganizeCategory_Code"].ToString()))
{
List <string> list = new List <string>();
list.Add(dr["Organize_Code"].ToString());
dic.Add(dr["OrganizeCategory_Code"].ToString(), list);
continue;
}
dic[dr["OrganizeCategory_Code"].ToString()].Add(dr["Organize_Code"].ToString());
}
return(dic);
}
/// <summary>
/// 更新实体数据，返回受影响的行数
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="this.CurrentTableName">数据表名</param>
/// <param name="targetFields">更新的目标字段 数组</param>
/// <param name="targetValues">更新的目标值 数组</param>
/// <param name="whereClause">WHERE 从句</param>
/// <returns></returns>
public int Update(List <KeyValuePair <string, object> > targetKVPs, WhereStatement whereStatement)
{
whereStatement.DbHelper = this.DbHelper;
NonQueryBuilder sqlBuilder = new NonQueryBuilder(this.DbHelper, DbOperation.Update);
sqlBuilder.SetFromTable(this.CurrentTableName);
foreach (KeyValuePair <string, object> kv in targetKVPs)
{
sqlBuilder.SetValue(kv.Key, kv.Value);
}
if (whereStatement.ClauseLevels > 0)
{
sqlBuilder.AddWhere(whereStatement);
}
else
{
throw new Exception("whereClause contains 0 element");
}
DbParameter[] parameters = null;
string sql = sqlBuilder.BuildSQL(out parameters);
int rows = this.DbHelper.ExecuteNonQuery(sql, parameters);
return(rows);
}
public override Element VisitWhereStatementExp(SqlParser.WhereStatementExpContext context)
{
var whereStatement = new WhereStatement(CreateParseInfo(context));
whereStatement.Children.Add(Visit(context.b));
return(whereStatement);
}
public int Update(WhereClause whereClause, T entity)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
whereStatement.Add(whereClause);
int rows = this.Update(whereStatement, entity);
return(rows);
}
/// <summary>
/// Inserts the initial seed record
/// </summary>
/// <param name="connection">The connection to the database</param>
/// <param name="objectType">The name to associate with the seed</param>
/// <param name="seed">The initial value for id to be generated</param>
public static void InsertSeed(string connection, string objectType, int seed)
{
string sql = string.Format("EXEC dbo.{0} {1}, {2}",
InsertSeedProcName,
WhereStatement.FormatSqlValue(objectType),
WhereStatement.FormatSqlValue(seed));
SimpleDataAccess.ExecuteSql(sql, connection);
}
public int SetDeleted(WhereClause whereClause)
{
WhereStatement statement = new WhereStatement(this.DbHelper);
statement.Add(whereClause);
int rows = this.SetDeleted(statement);
return(rows);
}
public bool Exists(string targetField, object targetValue)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
whereStatement.Add(targetField, Comparison.Equals, targetValue);
bool b = Exists(whereStatement);
return(b);
}
public override IQueryElement VisitWhere_clause([NotNull] QueryGrammarParser.Where_clauseContext context)
{
WhereStatement where = new WhereStatement();
IQueryElement clause = Visit(context.clause());
where.Add(clause);
return(where);
}
public int Update(KeyValuePair <string, object> targetKVP, WhereStatement whereStatement)
{
whereStatement.DbHelper = this.DbHelper;
List <KeyValuePair <string, object> > kvps = new List <KeyValuePair <string, object> >();
kvps.Add(targetKVP);
int rows = this.Update(kvps, whereStatement);
return(rows);
}
public void Test_GetSingleManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
entity = manager.GetSingle(23086, whereStatement: where);
}
private void VisitWhereIgnoreErrors(WhereStatement statement)
{
// visit where to gather query variables.
// We ignore errors as the where statement is visited in the Query
var errors = Errors.ToArray();
VisitChild(statement);
Errors.Clear();
Array.ForEach(errors, x => Errors.Add(x));
}
public Dictionary <string, object> Single(string store, WhereStatement where, params string[] fields)
{
var selectResult = Select(store, where, null, fields: fields);
if (selectResult == null || selectResult.Count == 0)
{
return(null);
}
return(selectResult[0]);
}
public void Test_ExistsManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
bool b = manager.Exists(where);
bool b1 = manager.Exists(entity.FieldF2, "222");
}
public JSDictionary GetRoleDDL()
{
int count = 0;
EntityManager <RoleEntity> roleManager = new EntityManager <RoleEntity>();
WhereStatement where = new WhereStatement();
List <RoleEntity> list = roleManager.GetList(where, out count);
JSDictionary re = list.ToJSDictionary(Key => Key.ID, Value => Value.FullName);
return(re);
}
public void Test_GetCountManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
int count = manager.GetCount(where);
Console.WriteLine(count);
}
/// <summary>
/// 假删除
/// </summary>
/// <returns></returns>
public int SetDeleted(WhereStatement whereStatement)
{
if (this._iEntity.DeletionStateCode == null)
{
throw new ArgumentNullException("DeletionStateCode");
}
KeyValuePair <string, object> kvp = new KeyValuePair <string, object>(this._iEntity.DeletionStateCode, 1);
int rows = this.Update(kvp, whereStatement);
return(rows);
}
private WhereStatement VisitWhereStatements(WhereStatement whereStatement)
{
var fieldOperationStatement = whereStatement as FieldOperationStatement;
if (fieldOperationStatement != null)
{
return VisitFieldOperationStatement(fieldOperationStatement);
}
var booleanOperationStatement = whereStatement as BooleanOperationStatement;
if (booleanOperationStatement != null)
{
return VisitBooleanOperationStatement(booleanOperationStatement);
}
throw new InvalidOperationException("Unknown wherestatement");
}
/// <summary>
/// Initializes a new instance of the BooleanOperationStatement class.
/// </summary>
public BooleanOperationStatement(WhereStatement left, BooleanOperationStatementType type, WhereStatement right)
{
Left = left;
Right = right;
Type = type;
}
public QueryBuilder()
{
_distinct = new bool();
_selectedColumns = new List<string>();
_selectedTables = new List<string>();
_joins = new List<Join>();
_whereStatement = new WhereStatement();
_groupBy = new List<string>();
_having = new WhereStatement();
_orderBy = new List<Orderby>();
_dbProviderFactory = null;
}
