how connection is made in asp.net to sql server databse?
With the SqlConnection class and a connection string in the web.config file. There will be tons of readily available examples online. Just google for it
I think it all depends on what choice of front end or patterns you want.
For now Microsoft is pushing entity framework which makes life easy another good alternative is to use ADO.NET which is the older method, but a lower level of abstraction.