Given a parametrized curve $\gamma$ on a manifold $M$ with metric $g$ and some affine connection on it, we can transport vectors of tangent spaces $T_{p} M$ and $T_{q}M$ to each other (when $p, q \in \gamma$). Then scalar product of vectors (w.r.t. $g$) is preserved under this transport and we can see it algebraically just because of Leibniz rule.
The question is: Can we see the fact that scalar product is preserved in a more geometric way?
Any notes on geodesics will also be appreciated.
Not all connections preserve the scalar product (the "metric"). We need to impose this condition - if, furthermore, we also ask the connection to be torsion-free, then we get the Levi-Civita connection, which is likely the one you have in mind.
One may consider $M$ as a submanifold in some ${\mathbb R}^n$ and assume $X, Y$ are two vector fields on $M$. Then the Levi-Civita connection (at $x\in M$) $\nabla_XY$ is just taking the ordinary Euclidean directional derivative for $Y$ in the $X$ direction, then project to the tangent space $T_x M$. Euclidean derivative preserves the scalar product (by calculus), and this is still true after projecting to $T_x M$.