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

Artikkeli
11/19/2007

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:

Open the Help menu in Visual Studio, and choose Samples.
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.
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

Figure 1: Creating a new console application.

Create a connection to the Northwind database:

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

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

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

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.

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.

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:

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

They ensure that you can automatically connect to the database.
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.

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:

Download and install the Northwind database from the samples that ship with Visual Studio
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.

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

Jaa

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

Using the SQL Designer

Summary

Comments

Lisäresursseja