Since the Curvature tensor depends on a connection(not metric), is it the relevant quantity to characterize the curvature of Riemannian manifolds?

I think the point that should be understood is that there are three different levels of structure here: a Riemannian structure (a choice of metric) is richer than a choice of affine connection, which in turn is stronger than a smooth structure (a choice of atlas). A smooth structure lets you define the notion of differentiable curves and tangent vectors. A connection just lets you define the notion of parallel transport (and thus geodesics). Enriching this to a Riemannian structure adds the notions of length, angle, volume, etc.

Since a metric determines a unique compatible Levi-Civita connection, if you have a Riemannian structure then you get a connection for free; but the converse is not true - given an affine connection, there may be many different Riemannian metrics having it as their Levi-Civita connection. (Of course, some connections aren't Levi-Civita for any metric at all, for example those with torsion.)

Whenever we're talking about parallel transport, geodesics, etc. in the context of Riemannian geometry, we're always using the Levi-Civita connection. The Ricci and scalar curvatures are defined as metric contractions of the curvature of the Levi-Civita connection. If you're forming the same contractions with the curvature of a different connection, then these are not what we normally call Ricci/scalar curvatures, and they have dubious geometric significance. The only sense in which the Riemannian curvature is "independent of the metric" is that it only depends on the metric via the connection; i.e. we can write in some sense $R(g) = R(\nabla(g))$. The dependence of $\nabla$ on $g$ should not be forgotten! To answer your first question directly, I would say that the relevant curvature information for a Riemannian manifold is the curvature tensor along with the metric, from which you can form not only the scalar and Ricci curvatures, but also more interesting things like the sectional curvature and curvature operator.

Your second question is kind of getting at something, though it needs a bit of tweaking. (A smooth structure doesn't provide a distinguished choice of connection!) What is true is that if you have two different metrics with the same Levi-Civita connection, then parallel transporting the same vector along the same loop will give you the same resulting vector. (If you choose to use some non Levi-Civita connection to do this instead, then of course this is still true - in this case you're not even using the Riemannian structure in any way.) In general, however, the two metrics will measure a different angle between the same pair of vectors.

I think the word "curvature" causes a bit of confusion to you. The curvature of a connection is something related a the connection you put on a manifold that in principle do not depend on the metric you have put on the manifold. If you want to rediscover the intuitive concept of curvature (Gaussian curvature* for example) used in the study of surfaces embedded in $\mathbb{R}^3$ (which is a metric concept), you start with a metric $g$ and take the curvature tensor $R$ of the induced Levi-Civita connection of $g$. In this case $R$ contains all the geometric information you expect. For example from $R$, you can construct the Sectional curvature $K$. $K(X,Y)$ will give you the Gaussian curvature of an embedded surface generated geodesically by the vectors $X,Y$. It is a very geometric interpretation. Also the Ricci tensor is a sum of sectional curvatures and the Scalar curvature is another sum of sectional curvatures. So the curvature tensor $R$ will give you really geometric informations coming from the metric but you have to consider the L-C connection.

Consider for example $(S^2,g_1)$ and $(S^2,g_2)$ Riemannian where $g_1$ is the metric given by the standard embedding in $\mathbb{R^3}$ with the standard flat metric and $g_2$ is the metric pulled back from an ellipsoid embedded in $\mathbb{R^3}$. You have two L-C connections, $C_1$ and $C_2$. If you compute the curvature tensor for $C_2$ (or another random connection not related to $g_1$) it will not give the intuitive geometric informations relevant for $(S^2,g_1)$ wich is (iso)metrically a sphere, even if you consider the scalar curvature.

2) No, because if you parallel transport a two vectors along a curve using a connection not even compatible with the metric (compatible means that $\nabla_X \langle Y,Z\rangle = \langle \nabla_X Y , Z \rangle + \langle Y , \nabla_X Z \rangle$ notice also that the L-C connection is by definition compatible with the metric), you can get different angles.
Instead if you use a connection compatible with the metric the parallel transport preserves the scalar product (given by the metric).

*I do not know if you are familiar with the Gaussian curvature of surfaces embedded in $\mathbb{R}^3$ but it is a fantastic scalar that for example if positive tells that the surface locally resembles a sphere, if negative instead tells that it resembles a saddle (like the saddle of a torus for example), the cylinder instead and the plane have Gaussian curvature 0. Probably it is really what fit best the layman term "curvature".
The Gaussian curvature is a metric concept (because it depends only on the first fundamental form) and is nothing more than the Sectional curvature of the Levi-Civita connection associated to the metric obtained restricting the standard flat metric of $\mathbb{R}^3$ to the surface. If you for example give another connection to the surface and compute the sectional curvature of it, it would be like computing the Gaussian curvature of another embedding (well, provided that the connection you are using is the L-C of some other metric).

Answers to the comments

1) I don't know any geometric interpretation in this case. The only thing I can think is that the curvature is linked (as you pointed out) to the holonomy i.e. what happens when you parallel transport a vector along a loop. In general if the curvature tensor is not trivial then the vector can be different.

2) The fact that the vector can be different can be stated for example in terms of angles onece you consider an auxiliary Riemannian metric. But this is just to state it in terms of angles. The point is that the vector after a loop is different.

2b) they will not result in the same change of angles, the two Riemannian metrics could measure different angles, if the transported vector is different they will measure it but the effective angle measured can be different. (Notice that this is not the case I was dealing with when talking about compatibility, in that case I was considering the parallel transport of two vectors and studying how the angle between them changes, in this case we are studying a single vector and after a loop we see how it change)

Maybe you should take a look at some books about Holonomy groups, unfortunately I know almost nothing about them.