/// <summary>
/// 执行查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="iTopNumber">TOP N</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="queryOrder">排序集合</param>
/// <param name="conn">数据库连接</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>结果集</returns>
private static DataTable ExcuteQuery(String strTableName, int iTopNumber,
QueryConditionDecorator queryCondition, QueryOrderCollection queryOrder, SqlConnection conn, SqlTransaction tran)
{
string strSql = "SELECT";
if (0 != iTopNumber)
{
strSql += " TOP " + iTopNumber;
}
strSql += " * FROM " + strTableName;
// 生成条件
SqlParameter[] paraQuery = null;
if (null != queryCondition)
{
QueryCondition condition = new QueryCondition();
condition.ConnectTo(queryCondition);
ArrayList lstSqlParameter = new ArrayList();
strSql += condition.MakeCondition(lstSqlParameter);
paraQuery = new SqlParameter[lstSqlParameter.Count];
for (int i = 0; i < lstSqlParameter.Count; i++)
{
paraQuery[i] = lstSqlParameter[i] as SqlParameter;
}
}
// 生成排序
if (null != queryOrder)
{
strSql += queryOrder.MakeOrder();
}
// 执行查询
if (null != paraQuery)
{
if (null != conn)
{
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, strSql, paraQuery));
}
else
{
return(SqlHelper.ExecuteDataTable(tran, CommandType.Text, strSql, paraQuery));
}
}
else
{
if (null != conn)
{
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, strSql));
}
else
{
return(SqlHelper.ExecuteDataTable(tran, CommandType.Text, strSql));
}
}
}
/// <summary>
/// 执行删除查询
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="conn">数据库连接</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>删除行数</returns>
private static int ExcuteDelete(String strTableName, QueryConditionDecorator queryCondition, SqlConnection conn, SqlTransaction tran)
{
string strSql = "DELETE";
strSql += " FROM " + strTableName;
// 生成条件
SqlParameter[] paraQuery = null;
if (null != queryCondition)
{
QueryCondition condition = new QueryCondition();
condition.ConnectTo(queryCondition);
ArrayList lstSqlParameter = new ArrayList();
strSql += condition.MakeCondition(lstSqlParameter);
paraQuery = new SqlParameter[lstSqlParameter.Count];
for (int i = 0; i < lstSqlParameter.Count; i++)
{
paraQuery[i] = lstSqlParameter[i] as SqlParameter;
}
}
// 执行查询
if (null != paraQuery)
{
if (null == conn)
{
return(SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, paraQuery));
}
else
{
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, paraQuery));
}
}
else
{
if (null == conn)
{
return(SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql));
}
else
{
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql));
}
}
}
public QueryConditionDecorator(QueryConditionDecorator queryCondition)
{
this.queryCondition = queryCondition;
}
/// <summary>
/// 执行查询统计行
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>行数量</returns>
public static int ExcuteCount(String strTableName, QueryConditionDecorator queryCondition, SqlTransaction tran)
{
return(ExcuteCount(strTableName, queryCondition, null, tran));
}
/// <summary>
/// 执行删除查询
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="conn">数据库连接</param>
/// <returns>删除行数</returns>
public static int ExcuteDelete(String strTableName, QueryConditionDecorator queryCondition, SqlConnection conn)
{
return(ExcuteDelete(strTableName, queryCondition, conn, null));
}
/// <summary>
/// 执行更新查询
/// </summary>
/// <param name="objEntity">更新值实体</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="conn">数据库连接</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>删除行数</returns>
private static int ExcuteUpdate(EntityObjectBase objEntity, QueryConditionDecorator queryCondition, SqlConnection conn, SqlTransaction tran)
{
TableAttributes taEntity = objEntity.GetEntityColumns();
string strUpColumns = String.Empty;
ArrayList lstPara = new ArrayList();
for (int i = 0; i < taEntity.Columns.Count; i++)
{ // 组装参数
ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes;
if (caCurrent.IsModify)
{
strUpColumns += caCurrent.ColumnName + "=@Up_" + caCurrent.ColumnName + ",";
}
else
{
continue;
}
//创建参数
SqlParameter paraCurrent = new SqlParameter("@Up_" + caCurrent.ColumnName, caCurrent.Value);
lstPara.Add(paraCurrent);
}
if (String.Empty == strUpColumns.Trim())
{
return(0);
}
SqlParameter[] objPara = new SqlParameter[lstPara.Count];
for (int i = 0; i < lstPara.Count; i++)
{
objPara[i] = lstPara[i] as SqlParameter;
}
// 生成更新条件
string strCondition = String.Empty;
if (null != queryCondition)
{
QueryCondition condition = new QueryCondition();
condition.ConnectTo(queryCondition);
ArrayList lstSqlParameter = new ArrayList();
strCondition += condition.MakeCondition(lstSqlParameter);
SqlParameter[] paraQuery = new SqlParameter[lstSqlParameter.Count];
for (int i = 0; i < lstSqlParameter.Count; i++)
{
paraQuery[i] = lstSqlParameter[i] as SqlParameter;
}
// 组合参数
SqlParameter[] paraTemp = new SqlParameter[objPara.Length];
objPara.CopyTo(paraTemp, 0);
objPara = new SqlParameter[paraTemp.Length + paraQuery.Length];
paraTemp.CopyTo(objPara, 0);
paraQuery.CopyTo(objPara, paraTemp.Length);
}
string strSql = "UPDATE [TableName] SET [Columns] [Condition]";
// 替换当前表数据
strSql = strSql.Replace("[TableName]", taEntity.TableName);
strSql = strSql.Replace("[Columns]", strUpColumns.Substring(0, strUpColumns.Length - 1));
strSql = strSql.Replace("[Condition]", strCondition);
// 执行查询
if (null != conn)
{
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, objPara));
}
else
{
return(SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, objPara));
}
}
/// <summary>
/// 执行更新查询
/// </summary>
/// <param name="objEntity">更新值实体</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>删除行数</returns>
public static int ExcuteUpdate(EntityObjectBase objEntity, QueryConditionDecorator queryCondition, SqlTransaction tran)
{
return(ExcuteUpdate(objEntity, queryCondition, null, tran));
}
/// <summary>
/// 执行分页查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="startRecord">开始查询记录</param>
/// <param name="maxRecords">查询记录数量</param>
/// <param name="conn">数据库连接</param>
/// <returns>结果集</returns>
public static DataTable ExcutePage(String strTableName, QueryConditionDecorator queryCondition, int startRecord, int maxRecords, SqlConnection conn)
{
return(ExcutePage(strTableName, queryCondition, null, startRecord, maxRecords, conn, null));
}
/// <summary>
/// 执行查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="iTopNumber">TOP N</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="queryOrder">排序集合</param>
/// <param name="conn">数据库连接</param>
/// <returns>结果集</returns>
public static DataTable ExcuteQuery(String strTableName, int iTopNumber,
QueryConditionDecorator queryCondition, QueryOrderCollection queryOrder, SqlConnection conn)
{
return(ExcuteQuery(strTableName, iTopNumber, queryCondition, queryOrder, conn, null));
}
/// <summary>
/// 执行查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="iTopNumber">TOP N</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="queryOrder">排序集合</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>结果集</returns>
public static DataTable ExcuteQuery(String strTableName, int iTopNumber,
QueryConditionDecorator queryCondition, QueryOrderCollection queryOrder, SqlTransaction tran)
{
return(ExcuteQuery(strTableName, iTopNumber, queryCondition, queryOrder, null, tran));
}
/// <summary>
/// 执行查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>结果集</returns>
public static DataTable ExcuteQuery(String strTableName, QueryConditionDecorator queryCondition, SqlTransaction tran)
{
return(ExcuteQuery(strTableName, 0, queryCondition, null, null, tran));
}
/// <summary>
/// 执行查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="conn">数据库连接</param>
/// <returns>结果集</returns>
public static DataTable ExcuteQuery(String strTableName, QueryConditionDecorator queryCondition, SqlConnection conn)
{
return(ExcuteQuery(strTableName, 0, queryCondition, null, conn, null));
}
/// <summary>
/// 执行分页查询返回表
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="queryOrder">排序集合</param>
/// <param name="startRecord">开始查询记录</param>
/// <param name="maxRecords">查询记录数量</param>
/// <param name="tran">数据库操作事务</param>
/// <returns>结果集</returns>
public static DataTable ExcutePage(String strTableName, QueryConditionDecorator queryCondition,
QueryOrderCollection queryOrder, int startRecord, int maxRecords, SqlTransaction tran)
{
return(ExcutePage(strTableName, queryCondition, queryOrder, startRecord, maxRecords, null, tran));
}
/// <summary>
/// 条件连接 使用默认条件运算符 AND
/// </summary>
/// <param name="queryCondition">被连接条件</param>
public void ConnectTo(QueryConditionDecorator queryCondition)
{
base.queryCondition = queryCondition;
}
/// <summary>
/// 执行更新查询
/// </summary>
/// <param name="objEntity">更新值实体</param>
/// <param name="queryCondition">查询条件</param>
/// <param name="conn">数据库连接</param>
/// <returns>删除行数</returns>
public static int ExcuteUpdate(EntityObjectBase objEntity, QueryConditionDecorator queryCondition, SqlConnection conn)
{
return(ExcuteUpdate(objEntity, queryCondition, conn, null));
}
/// <summary>
/// 条件连接
/// </summary>
/// <param name="queryCondition">被连接条件</param>
/// <param name="condition">条件运算符</param>
public void ConnectTo(QueryConditionDecorator queryCondition, QueryOperationSign.ConditionOperation condition)
{
base.queryCondition = queryCondition;
this.condition = condition;
}
/// <summary>
/// 构造器
/// </summary>
/// <param name="queryCondition">分组包含的条件</param>
public QueryConditionGroup(QueryConditionDecorator queryCondition) : base(null)
{
this.queryCondition = queryCondition;
}