LINQ to SQL Classes Generation in Visual Studio 2010

Today I'll explain how to connect to MS-SQL database using LINQ to SQL classes in Visual Studio 2010.

If you building application that will work closely to database, will not only read, but also update the data on SQL server than LINQ to SQL will be extremely comfortable and fast way to work with.

Ok, here is step by step guide how to create LINQ to SQL classes and establish connection with database:

1. Mouse right click on your project in Visual Studio 2010 and choose Add New Item

2. Check LINQ to SQL classes. Visual Studio will generate dbml file for you.

3. Double click on created dbml file , this will open the Object Relational Designer, then click on Server Explorer and connect to a database

4. Once connected to a database you can pick any table or stored procedure and just drag and drop it to the Object Relational Designer. This is it! You now actually may start work with your database.

Now if you will look into your DataClasses1.designer.cs file you will see that Visual Studio generated class for the table that you dragged to the designer. You can also extend this class and even add your own properties. To do this click on DataClasses1.dbml, then mouse right click on the graphical representation of generated class and click View Code

Once you done it Visual Studio will generate DataClasses1.cs file. You can add there your own properties or methods.

Ok, now let's see how we can access and work with our database through the code.

And I must tell you this is super easy! Check it out:

string connectionString = "Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True";
DataClasses1DataContext _db = new DataClasses1DataContext(connectionString);
_db.Connection.Open();
_db.MyTable; //This is our table
ChangeSet changeset = _db.GetChangeSet(); //This is how we know what changes been made to our table
_db.SubmitChanges(); //Submitting changes to DB
_db.Connection.Close();

LINQ to SQL is a very comfortable approach to work with SQL database when you constantly need to update, insert and display the data. However, if all you need is to run an SQL script on database in C# code then I suggest to look at this post - SQL Connection in C# using SqlConnection.

You may also find interesting my recent article about newer ADO.NET approach of LINQ to SQL called Entity Framework - C# Entity Framework in Visual Studio 2010.