Connect to a SQL Database and Use the LINQ to SQL Designer


To begin working with LINQ to SQL in Visual Studio 2008 you will need a database that you can query and a copy of MS SQL Server or SQL Express. In this post, I’m going to focus on SQL Express since it is free and since it gets installed by default when you install all versions of Visual Studio except for some forms of Visual Studio Express. If for any reason you don’t have SQL Express installed on your system, you can download it here.

Follow these steps to install and access the copy of the Northwind database that accompanies the samples that ship with Visual Studio 2008:

  1. Open the Help menu in Visual Studio, and choose Samples.
  2. Follow the directions there to install the Visual Studio 2008 C# Samples to a sub-folder of your Documents directory. Note that the latest copies of the samples are always available online at this address: http://go.microsoft.com/fwlink/?LinkID=85559.
  3. After installation, the Northwnd.mdf database file will be found in a directory called CSharpSamples\LinqSamples\Data, where CSharpSamples is a subdirectory created when you installed the samples.

NOTE: Additional Information on installing the Northwind database is available here.

Choose New Project from the File menu (Ctrl + Shift + N) and create a new console application:

  • In Project Types, select Windows
  • In Templates, select Console Application

Figure01

Figure 1: Creating a new console application.

Create a connection to the Northwind database:

  1. Choose the Server Explorer from the View menu (Ctrl + W, L). In Express editions of Visual Studio, this tool is called the Database Explorer.
  2. Right click on the Data Connections node shown in Figure 3 and choose Add Connection.
  3. In the Add Connection dialog select the Browse button and navigate to and select your copy of Northwnd.mdf.
  4. Select the OK button.

At this stage Northwnd.mdf should appear in your server or database explorer, as shown in Figure 3.

Figure02

Figure 2: Select the Browse button in the Add Connection dialog and locate your copy of Northwnd.mdf

 ServerExplorer

Figure 3: The Server Explorer provides a view of the Northwind database.

 

Using the SQL Designer

The LINQ to SQL Designer allows you to configure and view the metadata of the tables from the database that you want to query. There is a command line version of this tool called SqlMetal that is not covered in this document. By default, SqlMetal is part of the Visual Studio and .NET framework 3.5 install and is stored here: %ProgramFiles%\Microsoft SDKs\Windows\v6.0A\bin.

From the Project menu select Add New Item (Ctrl+Shift+A). Select LINQ to SQL Classes from the list of Visual Studio Installed Templates, as shown in Figure 4.

SelectLinqToSqlDesigner

Figure 4: Choose the LINQ to SQL Designer from the list of available templates available in the Add New Item dialog.

Drag the Customer table from the Server Explorer onto the designer, as shown in Figure 5.

Figure02

Figure 5: The LINQ to SQL designer with the Server Explorer on the left and the Solution Explorer on the right.

In Figure 5 the Customer table has been dragged from the Server Explorer onto the SQL Designer. Stored Procedures can be dragged onto the area where you see the text that begins “Create methods by dragging items…”

Several things happened as you completed the steps outlined above:

  1. When you added the SQL Designer to your project, a new node in the Solution Explorer called DataClasses1.dbml was added to your project. As shown in Figure 5, it contains two files, called DataClasses1.dbml.layout and DataClasses1.designer.cs.
  2. When you dragged the Customer table onto the designer an object relational mapping (ORM) was created between the Customer table in the database and a Customer class generated by the SQL Designer and placed in DataClasses1.designer.cs. This object is called an entity class and it allows you to access the data and fields of the Customer tables as if they were an object in your project.
  3. A second class, referred to as a DataContext, was also created in DataClasses1.designer.cs. As you will see in the next section, you can use this class to automatically connect to your database and to easily access the data and fields in the Customer table.
  4. A file called app.config was added to your project. It contains an automatically generated connection string for your database.

This is not the place to fully explore the SQL Designer and the code it generates. However, the steps shown above give you two key benefits:

  1. They ensure that you can automatically connect to the database.
  2. They let you access the Customers table in the database as if it were an object in your program. An example of how to do this is shown in the next section.

After you drag items from the Server Explorer onto the SQL Designer, you can modify the view that your program will have of the data. For instance, you can delete some of the fields from the Customers table, as shown in Figure 5. This operation modifies the classes generated, and not the actual table on the server.

ModifiedCustomerTable

Figure 6: A modified view of the Customer table with only three fields visible.

You can now test your work by opening up Program.cs in the Visual Studio editor and typing in the following code:

using System;
using System.Linq;

namespace ConsoleApplication41
{
    class Program
    {
        static void Main(string[] args)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();

            var query = from c in db.Customers
                        where c.City == "London"
                        select c.City;

            foreach (var q in query)
            {
                Console.WriteLine(q);
            }
        }
    }
}

Summary

In the post, you have seen how to:

  1. Download and install the Northwind database from the samples that ship with Visual Studio
  2. Access the database through the SQL Designer

You can learn more about LINQ by running the SampleQueries project that ships with Visual Studio samples referenced in this article.

kick it on DotNetKicks.com

Comments (23)

  1. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  2. Mark Dykun says:

    Charlie, the first statement seems strange. You need a database you can connect to and also SQL Express. In my experience I have not required SQL Express to use LINQ to SQL. Am I in error on this?

    Mark

  3. ccalvert says:

    Mark,

    Thanks for your comment. I did not mean to imply that MS SQL Server would not work with LINQ to SQL. It certainly does. I’ve rewritten the first paragraph to make it clear that you can use either MS SQL Server or SQL Express, but that this post focuses on using SQL Express.

    – Charlie

  4. Robert says:

    Charlie,

    Thanks for the brief presentation of linq.

    I have not tried the VS 2008 yet.

    However, I have some question regarding the linq and linq to sql designer.

    What will happen if someone changes the underlying table structure (column data types, column names,…).

    A runtime error will be raised?

    Is there any possibility of automatic updates and synchronization in the linq designer ?

    Robert

  5. In an earlier post I showed how LINQ developers can connect to a database and write a simple query. This

  6. In an earlier post I showed how LINQ developers can connect to a database and write a simple query. This

  7. S Saxon says:

    Very informative for me, I’m very new to C# and Linq. Thanks, Sid

  8. ccalvert says:

    Robert,

    If the metadata for a table changes, then you can delete it from the designer and drag it on again to regenerate the code. If you think you will need to do this kind of thing often, then you can use the command line tool called SqlMetal, which will generate the code automatically. The classes SQLMetal and the SQL Designer create are all declared to be partial so that any code you write to customize the behavior of the autogenerated code will not be overwritten.

    – Charlie

  9. Pablo says:

    I can’t find where in the code the application actually reads APP.CONFIG.

    How can I use a custom configuration file?

    I saw that the Settings class saves the connection string in a hard-coded attribute: how can I change that?

    Can you point me to a URL where I can learn ‘LINQ in depth’?

    Thanks a lot,

    Pablo.

  10. eve says:

    hi, i would like to ask that do u know the code of using visual C# in window Forms application connection to SQL server (database)? Because i’m not using Console Application to connection to database.

  11. billy bob says:

    Exactly the info I wanted to get started – thanks!

    There’s good follow-up info on usage here (among a lot of other places):

    http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

    For anyone who’s interested.

  12. billy bob says:

    A question:

    I successfully carried out the steps you gave, but I’m wondering exactly where the database *is*. I don’t see it in bindebug.

    More generally, I’m wondering how deployment works. I want my app to carry its own little sqlexpress db with it, and populate itself from data in files on whatever computer it’s running on.

  13. ccalvert says:

    Pablo,

    The DataContext that is created by the LINQ to SQL Designer (aka the Object Relational Designer) has code for reading your config file. This code will be executed if you use the default constructor for the DataContext If you prefer to pass in your own connection string, you can use the second constructor for the DataContext, which takes a connection string as a parameter:

    DataClasses1DataContext db =

     new DataClasses1DataContext(MyConnectionString);

    The samples that ship with the product, such as the Northwind sample, show how to use this second parameter, and how to construct a connection string that will work with it.

    – Charlie

  14. ccalvert says:

    Billy Bob,

    Unless you explicitly ask Visual Studio to copy it someplace else, the database will stay where it was when you browsed for it as shown above in Figure 2. In other words, the database *is* Northwnd.mdf, and it stays in the Data directory that is part of the samples.

    – Charlie

  15. Mitsu's blog says:

    Hi all, After almost one year of work and organization, I am very happy to share this project with you:

  16. Bonjour à tous, Après quasiment un an de travail et d’organisation, je suis très heureux de partager

  17. The3Factory says:

    Visual Linq query builder for Linq to Sql

  18. Lexapro. says:

    Lexapro side effects. Lexapro anxiety. Lexapro. Lexapro study.

  19. Praveen says:

    Hello

    I want to know how will i connet to any table in the database dynamically when database having many thousands of tables because its not possible to craete a class entity for each table.

    Thanks

  20. anuj says:

    when i add the more than 1 table in dataclasses then it works only for 1 table not for other. should i add to multiple dataclasses for adding multiple table???

  21. Santosh Kumar Singh says:

    Thank you sir…

    i am very happy after reading your blog…

    this is very help full for me because this give very clear step by step discription…

    Thank you very much sir…

  22. Ahmad says:

    Thank you.

    In my project linqNameDataContex not generated please help me.

    ahmadm0812@yahoo.com