Q:

What is SQL Connection

What SQL Connection object is and what does actually happen when we open/close it? What resources does it consume and why is it necessary to dispose it (in terms of c#/.net)?

A:

SqlConnection:
Take a look at the MSDN page for SqlConnection. It is stated that:

A SqlConnection object represents a unique session to a SQL Server
   data source. With a client/server database system, it is equivalent to
   a network connection to the server.

SqlConnection.Open: In the MSDN page on SqlConnection.Open, it is stated that:

The SqlConnection draws an open connection from the connection pool if
   one is available. Otherwise, it establishes a new connection to an
   instance of SQL Server.

SqlConnection.Close(and Dispose):
The MSDN page on SqlConnection.Close says that:

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

Also, in the SqlConnection page it is stated that:

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is actually closed.

and:

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block. 

This should answer your questions.
EDIT:
For further readings (also seen in your comments) you can read about Connection-Pooling and of course check out the source code for SqlConnection.

