/// <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); }