This tutorial starts by looking at creating a Data Access Layer using a TableAdapter. This reduces the lines of code needed for each web page while giving the developer a means to demonstrate a range of SQL statements for assessment purposes.
There are so many choices of how to connect to your database and they can each influence (or completely change) the way you developer your web solution. Each approach has its uses and applications and if you are a student you may need to consider what you are being assessed on.
Consider using a TableAdapter as a Data Access Layer as the middle-ground between embedding code in either C# or the HTML, and using LINQ to SQL or an ORM. The following is a list of methods you may like to review, from least favourable to most (in our opinion).
SQL Connections in HTML/ASP Code
Its common when learning to use SQL in Web Forms to reference connection strings and SQL commands in the markup of ASP objects. This script can be accessed by the user through their web browser, but no passwords should be available.
SQL Connections in Code Behind (C#)
An alternative to the above is to code the connections and SQL statements in the code-behind the web page, alongside any C# or VB.NET code. The decision between these two is often down to personal preference.
TableAdapter, Data Access Layer (DAL)
This is the chosen method of this tutorial, to eliminate some repetition of code that can occur with both the above methods, while allowing a good learning curve for database connectivity and understanding SQL.
LINQ to SQL
Potentially quicker and easier to use than Entity Framework, LINQ to SQL is restricted to Microsoft SQL Server databases and has some restrictions in comparison.
Entity Framework - Object Relational Mapper (ORM):
Entity Framework is Microsoft's Object Relational Mapper tool and can work with various types of databases. It takes a little more effort than LINQ to SQL but provides more flexibility and control. Essentially an ORM reduces the amount of code required to interface with a database.