/// <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));
}
}
public DataTable QueryByPage(string sql, Dictionary <string, object> paramValues, int pageSize, int pageIndex, out int totalCount)
{
//this.dbType = TransactionContext.get().dbType;
totalCount = 0;
//if (this.dbType == ADOTemplate.DB_TYPE_MYSQL)
//{
// MySqlParameter[] parm_temp = convert4MySqlserver(paramValues);
// if (parm_temp == null) parm_temp = new MySqlParameter[] { };
// //Get total count of the result
// string sqlCount = new StringBuilder(sql.Length + 20)
// .Append("select count(*) as total from ( ")
// .Append(sql)
// .Append(" ) t")
// .ToString();
// DataTable dt = MySQLHelper.ExecuteDataset(
// TransactionContext.get().connection as MySqlConnection, CommandType.Text, sqlCount, parm_temp
// ).Tables[0];
// if (dt.Rows.Count > 0)
// {
// totalCount = Convert.ToInt32(dt.Rows[0][0]);
// }
// //Search by paging
// bool hasOffset = (pageIndex != 1);
// sql = new StringBuilder(sql.Length + 20)
// .Append(sql)
// .Append(hasOffset ? " limit @x_rownum_from, @x_row_size" : " limit @x_row_size")
// .ToString();
// MySqlParameter[] parm;
// if (hasOffset)
// {
// parm = new MySqlParameter[parm_temp.Length + 2];
// parm_temp.CopyTo(parm, 0);
// parm[parm_temp.Length] = new MySqlParameter("@x_rownum_from", ((pageIndex - 1) * pageSize));
// parm[parm_temp.Length + 1] = new MySqlParameter("@x_row_size", pageSize);
// }
// else
// {
// parm = new MySqlParameter[parm_temp.Length + 1];
// parm_temp.CopyTo(parm, 0);
// parm[parm_temp.Length] = new MySqlParameter("@x_row_size", pageSize);
// }
// return MySQLHelper.ExecuteDataset(
// TransactionContext.get().connection as MySqlConnection, CommandType.Text, sql, parm
// ).Tables[0];
//}
//else if (this.dbType == ADOTemplate.DB_TYPE_SQLSERVER)
{
SqlParameter[] parm_temp = convert4Sqlserver(paramValues);
if (parm_temp == null)
{
parm_temp = new SqlParameter[] { }
}
;
//Get total count of the result
string sqlCount = "";
int beginPos = 7; // start after select
int nextFormPos = sql.IndexOf("from ", beginPos);
int nextSelectPos = sql.IndexOf("select ", beginPos);
while (nextSelectPos > 0 && nextFormPos > nextSelectPos)
{
beginPos = nextFormPos + 4;
nextFormPos = sql.IndexOf("from ", beginPos);
nextSelectPos = sql.IndexOf("select ", beginPos);
}
string orderString = "";
int endPos = sql.IndexOf("order by");
if (endPos > 0)
{
sqlCount = "SELECT COUNT(*) " + sql.Trim().Substring(nextFormPos, endPos - nextFormPos - 1);
orderString = sql.Trim().Substring(endPos);
}
else
{
sqlCount = "SELECT COUNT(*) " + sql.Trim().Substring(nextFormPos);
}
DataTable dt = SQLHelper.ExecuteDataset(
GetConnection() as SqlConnection, CommandType.Text, sqlCount, parm_temp
).Tables[0];
if (dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0][0]);
}
//Search by paging
bool hasOffset = (pageIndex != 1);
sql = new SQLServerDialect().GetLimitSql(sql, pageIndex, pageSize);
SqlParameter[] parm;
if (hasOffset)
{
parm = new SqlParameter[parm_temp.Length + 2];
parm_temp.CopyTo(parm, 0);
parm[parm_temp.Length] = new SqlParameter("@x_rownum_from", ((pageIndex - 1) * pageSize + 1));
parm[parm_temp.Length + 1] = new SqlParameter("@x_rownum_to", pageSize * pageIndex);
}
else
{
parm = new SqlParameter[parm_temp.Length + 1];
parm_temp.CopyTo(parm, 0);
parm[parm_temp.Length] = new SqlParameter("@x_rownum_to", pageSize);
}
return(SQLHelper.ExecuteDataset(
GetConnection() as SqlConnection, CommandType.Text, sql, parm
).Tables[0]);
}
return(null);
}