query the sql server database using linq to sql

LINQ to SQL is used to query the sql server database. Unlike Linq to objects, using LINQ to SQL we can also insert, update and delete data in the sql server database.

To query the SQL server database we use LINQ to SQL classes. These classes implement IEnumerable interface and become eligible for implementing queries. These classes can handle queries to access the tables in the database.

By using these queries, programmers can access SQL databases from their applications. Note that by using LINQ to SQL, we can access only sql databases and not other databases. To access SQL database, developers can write queries in any .Net language whether vb.net or c# which are translated and run on the sql databases to retrieve data.

When we create a project in C#, classes are created in C# based on a database. Similarly, when we create a project in vb.net, these classes are created in vb.net. After the classes are created, we can retrieve the data from the database.

Firstly, we need to create the object model and then code the queries. To create the object model, we use the Object Relational Designer. This designer is part of the Visual Studio IDE for creating an object model from an existing database.

Using the Object Relational Designer, we can select the tables that we want to include in the object model. A separate class ( entity class) for each table is added in the Object Relational Designer and one DataContext class for the database. After this step, we can instantiate the DataContext class and write LINQ to SQL queries to access the SQL server tables. Given below is a simple query example. This query selects all customers from the Customer table.

using System; using System.Collections.Generic; using System.Linq; using System.Text;
using System.Data; using System.Data.SqlClient; using System.Data.Linq; 
using System.Data.Linq.Mapping;
namespace LinqExamples
{
	class Program
	{
		static void Main(string[] args)
		{
			SalesDBDataContext dataContext = new SalesDBDataContext();
			var query = from c in dataContext.Customers
    					select c;
			foreach(var customer in query)
			{
				Console.WriteLine(customer.CustomerId + customer.Name);
			}
			Console.ReadLine();
		}  
     }  
}

The DataContext object is used to access the data from the database. To access the data, we instantiate the DataContext class. Also, in app.config, we initialize the DataContext class using the connectionString. The below statement instantiate the DataContext class.
SalesDBDataContext dataContext = new SalesDBDataContext();

The query will not be executed until we use for each statement and iterate over the query variable.