/// <summary>
/// 没有TenantCode查找单一Job
/// </summary>
/// <param name="whereClause"></param>
/// <returns></returns>
public JobBase LoadSingleDataByJobID(IConnectiveSqlClause whereClause)
{
JobBase result = null;
if (whereClause.IsEmpty == false)
{
using (DbContext context = DbHelper.GetDBContext(GetConnectionName()))
{
//using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SINGLEATA_JOB,
// whereClause.AppendTenantCodeSqlClause(typeof(JobBase)).ToSqlString(TSqlBuilder.Instance)), GetConnectionName()))
//{
// while (dr.Read())
// {
// result = new JobBase();
// ORMapping.DataReaderToObject(dr, result);
// break;
// }
//}
using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SINGLE_DATA_JOB,
whereClause.ToSqlString(TSqlBuilder.Instance)), GetConnectionName()))
{
while (dr.Read())
{
result = new JobBase();
ORMapping.DataReaderToObject(dr, result);
break;
}
}
}
}
return(result);
}
public InvokeWebServiceJob LoadSingleData(IConnectiveSqlClause whereClause)
{
InvokeWebServiceJob result = null;
if (whereClause.IsEmpty == false)
{
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SingleData_InvokeWebService,
whereClause.ToSqlString(TSqlBuilder.Instance)), GetConnectionName()))
{
while (dr.Read())
{
result = new InvokeWebServiceJob();
ORMapping.DataReaderToObject(dr, result);
break;
}
}
if (result != null)
{
XElementFormatter formatter = new XElementFormatter();
formatter.OutputShortType = WorkflowSettings.GetConfig().OutputShortType;
XElement root = XElement.Parse(result.XmlData);
result.SvcOperationDefs = (WfServiceOperationDefinitionCollection)formatter.Deserialize(root);
result.InitJobBaseData(JobBaseAdapter.Instance.LoadSingleDataByJobID(whereClause));
}
}
}
return(result);
}
public static string[] QueryGuidsByCondition(IConnectiveSqlClause condition)
{
QueryCondition qc = new QueryCondition();
qc.FromClause = "WF.PROCESS_INSTANCES (NOLOCK)";
qc.SelectFields = "INSTANCE_ID";
qc.OrderByClause = "CREATE_TIME DESC";
qc.WhereClause = condition.ToSqlString(TSqlBuilder.Instance);
string sql = string.Format("SELECT {0} FROM {1} WHERE 1 = 1 {2} {3} ORDER BY {4}",
qc.SelectFields,
qc.FromClause,
qc.WhereClause.IsNotEmpty() ? " AND " + qc.WhereClause : string.Empty,
qc.GroupBy.IsNotEmpty() ? "GROUP BY " + qc.GroupBy : string.Empty,
qc.OrderByClause);
using (DbContext context = DbContext.GetContext(WorkflowSettings.GetConfig().ConnectionName))
{
Database db = DatabaseFactory.Create(context);
using (var dr = db.ExecuteReader(CommandType.Text, sql))
{
List <string> guids = new List <string>();
while (dr.Read())
{
guids.Add(dr.GetString(0));
}
return(guids.ToArray());
}
}
}
public JobCollection UPDLOCKLoadJobs(int batchCount, IConnectiveSqlClause whereClause)
{
JobCollection result = new JobCollection();
if (whereClause.IsEmpty == false)
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty;
Database db = DatabaseFactory.Create(context);
using (IDataReader dr = db.ExecuteReader(CommandType.Text,
string.Format(UPDLOCK_LOAD_JOBS, top,
whereClause.ToSqlString(TSqlBuilder.Instance))))
{
ORMapping.DataReaderToCollection(result, dr);
}
}
AfterLoad(result);
}
return(result);
}
/// <summary>
/// 得到更新当前快照的SQL
/// </summary>
/// <param name="srcTableName"></param>
/// <param name="targetTableName"></param>
/// <param name="builder"></param>
/// <returns></returns>
private string GetInternalUpdateCurrentSnapshotSql(string srcTableName, string targetTableName, IConnectiveSqlClause builder)
{
builder.NullCheck("builder");
StringBuilder strB = new StringBuilder();
if (builder.IsEmpty == false)
{
IConnectiveSqlClause timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE Status = {1} AND {2}",
srcTableName,
(int)SchemaObjectStatus.Normal,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
if (table.Rows.Count > 0)
{
UpdateSqlClauseBuilder updateBuilder = new UpdateSqlClauseBuilder();
DataRowToBuilder(table.Rows[0], updateBuilder, "RowUniqueID");
InsertSqlClauseBuilder insertBuilder = new InsertSqlClauseBuilder();
DataRowToBuilder(table.Rows[0], insertBuilder);
strB.AppendFormat("UPDATE {0} SET {1} WHERE {2}",
targetTableName,
updateBuilder.ToSqlString(TSqlBuilder.Instance),
builder.ToSqlString(TSqlBuilder.Instance));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append("IF @@ROWCOUNT = 0");
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("INSERT INTO {0} {1}",
targetTableName,
insertBuilder.ToSqlString(TSqlBuilder.Instance));
}
else
{
strB.AppendFormat("DELETE {0} WHERE {1}", targetTableName, builder.ToSqlString(TSqlBuilder.Instance));
}
}
return(strB.ToString());
}
private void BindGrid(IConnectiveSqlClause whereSqlClause)
{
LastQueryRowCount = -1;
var whereCondition = (HtmlInputHidden)this.FindControlByID("whereCondition", true);
whereCondition.Value = whereSqlClause.ToSqlString(TSqlBuilder.Instance);
this.ProcessDescInfoDeluxeGrid.SelectedKeys.Clear();
this.ProcessDescInfoDeluxeGrid.PageIndex = 0;
}
/// <summary>
/// 批量更新某些字段的值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="action"></param>
/// <param name="where"></param>
/// <returns></returns>
public static string BathUpdate <T>(IConnectiveSqlClause where, Action <UpdateSqlClauseBuilder> action)
{
var updateBuilder = new UpdateSqlClauseBuilder();
action(updateBuilder);
return(String.Format("UPDATE {0} SET {1} WHERE {2}",
ORMapping.GetMappingInfo <T>().TableName,
updateBuilder.ToSqlString(TSqlBuilder.Instance),
where.ToSqlString(TSqlBuilder.Instance)));
}
/// <summary>
///
/// </summary>
/// <param name="obj"></param>
/// <param name="mapping"></param>
/// <param name="ignoreProperties">需要忽略的参数</param>
/// <returns></returns>
public virtual string PrepareUpdateSql(T obj, ORMappingItemCollection mapping, params string[] ignoreProperties)
{
IConnectiveSqlClause primaryKeyBuilder = this.PrepareWhereSqlBuilder(obj, mapping, ignoreProperties);
UpdateSqlClauseBuilder updateBuilder = this.PrepareUpdateSqlBuilder(obj, mapping, ignoreProperties);
return(string.Format("UPDATE {0} SET {1} WHERE {2}",
GetTableName(obj, mapping),
updateBuilder.ToSqlString(TSqlBuilder.Instance),
primaryKeyBuilder.ToSqlString(TSqlBuilder.Instance)));
}
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action<DataView> action)
{
StringBuilder strB = new StringBuilder();
strB.AppendFormat("SELECT * FROM {0} WHERE {1}",
tableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataView view = DbHelper.RunSqlReturnDS(strB.ToString(), connectionName).Tables[0].DefaultView;
if (action != null)
action(view);
}
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action<IDataReader> action)
{
StringBuilder strB = new StringBuilder();
strB.AppendFormat("SELECT * FROM {0} WHERE {1}",
tableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
using (IDataReader reader = DbHelper.RunSqlReturnDR(strB.ToString(), connectionName))
{
if (action != null)
action(reader);
}
}
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action <DataView> action)
{
StringBuilder strB = new StringBuilder();
strB.AppendFormat("SELECT * FROM {0} WHERE {1}",
tableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataView view = DbHelper.RunSqlReturnDS(strB.ToString(), connectionName).Tables[0].DefaultView;
if (action != null)
{
action(view);
}
}
/// <summary>
/// 根据外界的builder加载数据
/// </summary>
/// <param name="builder"></param>
/// <param name="orderByBuilder"></param>
/// <returns></returns>
public virtual TCollection LoadByBuilder(IConnectiveSqlClause builder, OrderBySqlClauseBuilder orderByBuilder)
{
builder.NullCheck("builder");
TCollection result = null;
PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("LoadByBuilder({0})", this.GetType().FullName), () =>
{
result = InnerLoadByBuilder(builder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder,
this.GetQueryMappingInfo());
});
return(result);
}
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action <IDataReader> action)
{
StringBuilder strB = new StringBuilder();
strB.AppendFormat("SELECT * FROM {0} WHERE {1}",
tableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
using (IDataReader reader = DbHelper.RunSqlReturnDR(strB.ToString(), connectionName))
{
if (action != null)
{
action(reader);
}
}
}
private static string ConditionObjectToWhereString(object conditionObject)
{
string result = string.Empty;
if (conditionObject != null)
{
if (conditionObject is string)
{
result = (string)conditionObject;
}
else
{
IConnectiveSqlClause connectiveBuilder = ConditionMapping.GetConnectiveClauseBuilder(conditionObject);
result = connectiveBuilder.ToSqlString(TSqlBuilder.Instance);
}
}
return(result);
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause wBuilder, OrderBySqlClauseBuilder orderByBuilder, int count, bool nolock = false)
{
UserTaskCollection result = new UserTaskCollection();
string top = string.Empty;
if (count >= 0)
{
top = string.Format("TOP {0}", count);
}
if (wBuilder.IsEmpty == false)
{
if (nolock)
{
tableName += " (NOLOCK)";
}
string sql = string.Format("SELECT {0} * FROM {1} WHERE {2}",
top,
tableName,
wBuilder.ToSqlString(TSqlBuilder.Instance));
if (orderByBuilder.Count > 0)
{
sql += " ORDER BY " + orderByBuilder.ToSqlString(TSqlBuilder.Instance);
}
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
return(result);
}
public string GetUpdateCurrentSnapshotSql(string srcTableName, string targetTableName, IConnectiveSqlClause builder)
{
builder.NullCheck("builder");
StringBuilder strB = new StringBuilder();
if (builder.IsEmpty == false)
{
strB.AppendFormat("DELETE {0} WHERE {1}", targetTableName, builder.ToSqlString(TSqlBuilder.Instance));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
IConnectiveSqlClause timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, timePointBuilder);
strB.AppendFormat("INSERT INTO {0} SELECT * FROM {1} WHERE Status = {2} AND {3}",
targetTableName, srcTableName, (int)SchemaObjectStatus.Normal, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
}
return(strB.ToString());
}
/// <summary>
/// 将所有的构造项生成一个SQL
/// </summary>
/// <param name="sqlBuilder">Sql语句的构造器</param>
/// <returns></returns>
public string ToSqlString(ISqlBuilder sqlBuilder)
{
StringBuilder strB = new StringBuilder(256);
for (int i = 0; i < this.Count; i++)
{
IConnectiveSqlClause clause = this[i];
if (clause.IsEmpty == false)
{
if (strB.Length > 0)
{
strB.AppendFormat(" {0} ", EnumItemDescriptionAttribute.GetAttribute(this.logicOperator).ShortName);
}
strB.AppendFormat("({0})", clause.ToSqlString(sqlBuilder));
}
}
return(strB.ToString());
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause builder)
{
UserTaskCollection result = new UserTaskCollection();
if (builder.IsEmpty == false)
{
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
tableName, builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
return(result);
}
private void BindGrid(IConnectiveSqlClause whereSqlClause)
{
LastQueryRowCount = -1;
var whereCondition = (HtmlInputHidden)this.FindControlByID("whereCondition", true);
whereCondition.Value = whereSqlClause.ToSqlString(TSqlBuilder.Instance);
this.ProcessDescInfoDeluxeGrid.SelectedKeys.Clear();
this.ProcessDescInfoDeluxeGrid.PageIndex = 0;
}
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause builder)
{
UserTaskCollection result = new UserTaskCollection();
if (builder.IsEmpty == false)
{
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
tableName, builder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
}
return result;
}