C# WhereSqlClauseBuilder.ToSqlString方法代码示例

		protected override void OnBuildQueryCondition(QueryCondition qc)
		{
			qc.FromClause = TimePointContext.Current.UseCurrentTime ? @"SC.UserAndContainerSnapshot_Current C 
INNER JOIN SC.SchemaUserSnapshot_Current U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot_Current R ON R.ID = C.ContainerID 
INNER JOIN SC.SchemaMembersSnapshot_Current S ON S.MemberID = R.ID
INNER JOIN  SC.SchemaApplicationSnapshot_Current A ON A.ID = S.ContainerID" : @"SC.UserAndContainerSnapshot C 
INNER JOIN SC.SchemaUserSnapshot U ON U.ID =C.UserID
INNER JOIN SC.SchemaRoleSnapshot R ON R.ID = C.ContainerID 
INNER JOIN SC.SchemaMembersSnapshot S ON S.MemberID = R.ID
INNER JOIN  SC.SchemaApplicationSnapshot A ON A.ID = S.ContainerID";
			qc.SelectFields = "R.*, A.ID AS  AppID,A.DisplayName AS AppName,A.Name As AppName2,A.CodeName as AppCodeName";
			base.OnBuildQueryCondition(qc);
			WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
			wb.AppendItem("C.UserID", this.userId);
			if (string.IsNullOrWhiteSpace(orderBy))
			{
				qc.OrderByClause = "A.DisplayName";
			}
			else
			{
				qc.OrderByClause = orderBy;
			}
			if (string.IsNullOrWhiteSpace(qc.WhereClause))
			{
				qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
			}
			else
			{
				qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
			}
		}

		protected override void OnBuildQueryCondition(QueryCondition qc)
		{
			base.OnBuildQueryCondition(qc);

			WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder();
			wb.AppendItem("LogID", this.syncID);

			if (string.IsNullOrEmpty(qc.WhereClause))
				qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance);
			else
				qc.WhereClause = " AND " + wb.ToSqlString(TSqlBuilder.Instance);
		}

		/// <summary>
		/// 获取与指定对象关联的条件历史
		/// </summary>
		/// <param name="id">对象ID</param>
		/// <param name="type">类型，如果为<see langword="null"/>则不限。</param>
		/// <returns></returns>
		public IEnumerable<SCCondition> GetConditionHistoryEntries(string id, string type)
		{
			SCConditionCollection entries = new SCConditionCollection();
			using (var context = DbHelper.GetDBContext(this.GetConnectionName()))
			{
				Database db = DatabaseFactory.Create(context);

				WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
				builder.AppendItem("OwnerID", id);

				if (type != null)
				{
					builder.AppendItem("Type", type);
				}

				var sql = @"SELECT *  FROM [SC].[Conditions] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY [VersionEndTime] DESC, SortID ASC";
				var cmd = db.GetSqlStringCommand(sql);
				using (var dr = db.ExecuteReader(cmd))
				{
					ORMapping.DataReaderToCollection(entries, dr);
				}

				return entries;
			}
		}

		public static IEnumerable<SCSimpleObject> QueryParents(string unitID, bool includingSelf, DateTime timePoint)
		{
			if (string.IsNullOrEmpty(unitID) == false)
			{
				string timeString = timePoint == DateTime.MinValue ? TSqlBuilder.Instance.DBCurrentTimeFunction : TSqlBuilder.Instance.FormatDateTime(timePoint);

				WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().AppendCondition("ID", unitID);

				string sql = string.Format(sqlTemplate, timeString, where.ToSqlString(TSqlBuilder.Instance));

				DataTable table = DbHelper.RunSqlReturnDS(sql, ConnectionName).Tables[0];
				DataView view = new DataView(table);
				view.Sort = "ID";

				SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection();

				FillAllParents(unitID, view, includingSelf, tempParents);

				tempParents.Reverse();

				return tempParents;
			}
			else
			{
				return emptyResult;
			}
		}

		public DataView Query(int startRowIndex, int maximumRows, string appName, string programName, WfApplicationAuthType authType, string where, string orderBy, ref int totalCount)
		{
			WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

			if (string.IsNullOrEmpty(appName) == false)
			{
				builder.AppendItem("APPLICATION_NAME", appName);

				if (string.IsNullOrEmpty(programName) == false)
				{
					builder.AppendItem("PROGRAM_NAME", programName);
				}
			}

			if (authType != WfApplicationAuthType.None)
			{
				builder.AppendItem("AUTH_TYPE", authType.ToString());
			}

			string sql = builder.ToSqlString(TSqlBuilder.Instance);

			if (string.IsNullOrEmpty(where) == false)
			{
				sql = (string.IsNullOrEmpty(sql) ? where : sql + " AND (" + where + ")");
			}

			return base.Query(startRowIndex, maximumRows, sql, orderBy, ref totalCount);
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fillAssignees"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        public WfProcessCurrentInfoCollection Load(bool fillAssignees, Action<WhereSqlClauseBuilder> action)
        {
            action.NullCheck("action");

            WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection();

            WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();

            action(whereBuilder);

            string fieldNames = ORMapping.GetSelectFieldsNameSql<WfProcessInstanceData>("Data");

            string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE {1}",
                    fieldNames,
                    whereBuilder.ToSqlString(TSqlBuilder.Instance));

            DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];

            result.LoadFromDataView(table.DefaultView);

            if (fillAssignees)
                FillAssignees(result);

            return result;
        }

		protected override void OnBuildQueryCondition(QueryCondition qc)
		{
			qc.FromClause = "SC.OperationLog";
			qc.SelectFields = "*";
			if (string.IsNullOrEmpty(qc.OrderByClause))
				qc.OrderByClause = "CreateTime DESC";

			WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();

			if (this.catelog.IsNotEmpty())
			{
				builder.AppendItem("SchemaType", this.catelog);
			}

			if (this.operationType.IsNotEmpty())
			{
				builder.AppendItem("OperationType", operationType);
			}

			if (builder.IsEmpty == false && qc.WhereClause.IsNotEmpty())
			{
				qc.WhereClause += " AND ";
			}

			qc.WhereClause += builder.ToSqlString(TSqlBuilder.Instance);
		}

 public Int32 DeleteDepartmentInfo(String deptID)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(DepartmentInfoDBConst.DepartmentID, deptID);
     String sql = String.Format("DELETE FROM {0} WHERE {1} ", DepartmentInfoDBConst.TableName, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 ChangeStatus(String projectClassId, Int32 userFlag)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(ProjectClassInfoDBConst.ProjectClassID, projectClassId);
     String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", ProjectClassInfoDBConst.TableName, userFlag, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 DeleteUserInfo(String userId)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(UserInfoDBConst.UserId, userId);
     String sql = String.Format("DELETE FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 ChangePassword(String userId, String password)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(UserInfoDBConst.UserId, userId);
     String sql = String.Format("UPDATE {0} SET PASSWORD='{1}' WHERE {2} ", UserInfoDBConst.TableName, CryptoHelper.Encode(password), builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 DeleteReportFee(String reportID)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(ReportFeeDBConst.ReportID, reportID);
     String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.TableName, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 ChangeDimission(String personID, bool dimission)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(PersonInfoDBConst.PersonID, personID);
     String sql = String.Format("UPDATE {0} SET DIMISSION={1} WHERE {2} ", PersonInfoDBConst.TableName, dimission ? 1 : 0, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public Int32 ChangeStatus(String userId, Int32 status)
 {
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(UserInfoDBConst.UserId, userId);
     String sql = String.Format("UPDATE {0} SET LOGOUT={1} WHERE {2} ", UserInfoDBConst.TableName, status, builder.ToSqlString());
     int result = _DataHelper.ExecuteSql(sql);
     return result;
 }

 public String CreatePersonCode(String departID)
 {
     DepartmentInfo departInfo = DepartmentInfoAdapter.Instance.LoadDepartmentInfo(departID);
     WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
     builder.AppendItem(PersonInfoDBConst.DepartmentID, departID);
     String sql = String.Format("SELECT ISNULL(MAX(PERSON_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, PersonInfoDBConst.TableName, builder.ToSqlString());
     DataTable dt = _DataHelper.GetDataTable(sql);
     return dt.Rows[0][0].ToString();
 }