Partager via

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

  • Article

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: https://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

Figure6: 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

  • Anonymous
    November 19, 2007
    You've been kicked (a good thing) - Trackback from DotNetKicks.com

  • Anonymous
    November 19, 2007
    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

  • Anonymous
    November 19, 2007
    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

  • Anonymous
    November 20, 2007
    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

  • Anonymous
    December 10, 2007
    In an earlier post I showed how LINQ developers can connect to a database and write a simple query. This

  • Anonymous
    December 10, 2007
    In an earlier post I showed how LINQ developers can connect to a database and write a simple query. This

  • Anonymous
    December 11, 2007
    Very informative for me, I'm very new to C# and Linq. Thanks, Sid

  • Anonymous
    December 12, 2007
    The comment has been removed

  • Anonymous
    December 16, 2007
    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.

  • Anonymous
    January 01, 2008
    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.

  • Anonymous
    March 01, 2008
    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.

  • Anonymous
    March 01, 2008
    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.

  • Anonymous
    March 18, 2008
    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
  • Anonymous
    March 18, 2008
    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

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

  • Anonymous
    April 01, 2008
    Bonjour à tous, Après quasiment un an de travail et d'organisation, je suis très heureux de partager

  • Anonymous
    April 02, 2008
    Visual Linq query builder for Linq to Sql

  • Anonymous
    April 02, 2008
    Ref:http://blogs.msdn.com/mitsu/archive/2008/04/02/visual-linq-query-builder-for-linq-to-sql-vlinq....

  • Anonymous
    August 25, 2008
    Lexapro side effects. Lexapro anxiety. Lexapro. Lexapro study.

  • Anonymous
    July 05, 2010
    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

  • Anonymous
    December 30, 2010
    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???

  • Anonymous
    October 17, 2013
    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...

  • Anonymous
    March 27, 2014
    Thank you. In my project linqNameDataContex not generated please help me. ahmadm0812@yahoo.com