LINQ to SQL and Visual Studio 2008 C# Help

LlNQ to SQL in particular is a means to have a strongly typed interface against a SQL Server database, You will find the approach that LlNQ to SQL provides is by far the easiest approach to querying SQL Server available at the moment. It is not just simply about querying single tables within the database, but, for instance, if you call the Customers table of the Northwind database and want to pull a customer’s specific orders from the Orders table in the same database, LINQ will use the relations of the tables and make the query on your behalf, LINQ will query the database arid load up the data for you to work with from your code (again, strongly typed).

It is important to remember that LINQ to SQL is not only about querying data, but you also are able to perform the Insert/Update/Delete statements that you need to perform You can also interact with the entire process and customize the operations performed to add your own business logic to any of the CRUD operations (Create/Read/Update/Delete).

Visual Studio 2008 comes into strong play with LINQ to SQL instance that you will find an extensive user interface that allows you to design the LINQ to SQL classes you will work with The next section of the chapter focuses on showing you how to set up your first LINQ to SQL instance and pull items from the Products table of the Northwind database.

Calling the Products Table Using LINQ to SQL – Creating
the Console Application

For an example of using LINQ to SQL, this chapter starts by calling a single table from the Northwind database and using this table to populate some results to the screen, To start,off, create a console application (using the .NET Framework 3.5) and add the Northwind database file to this project (Northwind. MDF).

The following example makes use of the Northwind. mdf SQL Server Express Database file, To get this database, please search for “Northwind and pubs Sample Databases for SQL Seroer 2000.” You can find this linkathttp://www.microsoft.com/downloads/details .aspx?familyid=06616212-0’356-46aO-8da2-eebc5’3a68034&displaylang=en. Once installed, you will find the Northwind, mdf file in the C: \SQL Server 2000 Sample Databases directory. To add this database to your application, right-click. the solution you are working with and select Add Existing Item, From the provided dialog, you are then able to browse to the location of the Northwind, mdf file that you just installed. If you are having trouble getting permissions to work with the database, make a data connection to the file from the Visual Studio Server Explorer and you will be asked to be made the appropriate user of the database. VS will make the appropriate changes on your behalf for this to occur.

By default now, when creating many of the application types provided in the .NET Framework 3.5 within VisualStudio 2008,you will notice that you already have the proper references in place to work with LINQ, When creating a console application, you will get the following using statements in your code:

using System;
using System.Collections.Generic;
using System.Linq; .
using System.Net;
using System.Net.Sockets;
using System.Runtime.Remoting.Messaging;
using System.Text;

From this, you can see that the LINQ reference that will be required is already in place, The next step is to add a LINQ to SQLclass.

Adding a LINQ to SQL Class

When working with LINQ to SQL, one of the big advantages you will find is that VisualStudio 2008 does an outstanding job of making it as easy as possible. VS2008plQvides an object-relational mapping designer, called the O/R Designer, which allows you to visually design the object to database mapping, To start this task, right-click your solution and select Add New Item from the provided menu. From the items in the Add New Item dialog, you will find LINQ to SQL Classes as an option. This is presented in Figure 27-2.

Figure 27·2

Figure 27·2

Because this example is using the Northwind database, name the file Northwind. dbml.

Click the Add button, and you will see that this operation creates a couple of files for you, Figure 27-3 presents the Solution Explorer after adding the Northwind, dbml file.

Flgure 27-3

Figure 27-3

A number of things were added to your project with this action. The Northwind. dbml file was added and it contains two components, Because the LINQ to SQLclass that was added works With LINQ, the foUowing references were also added on your behalf: System, Core, Sys tem. Data dataSetExtenaions, System data. Linq, and System. Xml. Linq.

Introducing the O/R Designer

Another big addition to the IDE that appeared when you added the LINQ to SQLclass to your project (the Northwind. dbml file), was a visual representation of the dbml file, The new O/R Designer will appear as a tab within the document window directly in the IDE, Figure 27-4 shows a view of the O/R Designer when it is first initiated.

Figure 27-4

Figure 27-4

The O/R Designer is made up of two parts. The first part is for data classes, which can be tables, classes, associations, and inheritances, Dragging such items on this design surface will give you a visual representation of the object that can be worked with. The second part (on the right) is for methods, which map to the stored procedures within a database, When viewing your .dbml file within the O/R Designer, you will also have an Object Relational Designer set of controls in the VisualStudio toolbox, The toolbox is presented in Figure 27-5.

Figure 27-5

Figure 27-5

Creating the Product Object

For this example, you want to work with the Products table from the Northwind database, which means that you are going to have to create a Products table that will use LINQ to SQL to map to this table, Accomplishing this task is simply a matter of opening up a view of the tables contained within the database from the Server Explorer dialog within Visual Studio and dragging and dropping the Products table onto the design surface of the O/R Designer, This action’s results are illustrated in Figure 27-6.

Figure 27-6

Figure 27-6

With this action, a bunch of code is added to the designer files of the .dbml file on your behalf, These classes will give you a strongly typed access to the Products table. For a demonstration of this, turn your attention to the console application’s Program cs file, The following shows the code that is required for this example:

using System;
using System. Linq;
namespace ConsoleApplicationl
{
class Classl •
{
static void Main(string[] args)
{
NorthwindDataContext dc z new NorthwindDataContext();
var query = dc:Products;
foreach (Product item in query)

Console.WriteLine(“{O} I {l} I {2}”,
item.ProductID, item.ProductName, item.UnitslnStock);
Console.ReadLine();
I }

This bit of code does not have many lines to it, but it is querying the Products table within the Northwind database and pulling out the data to display, It is important to step through this code starting with the first line in the Main () method.

NorthwindDataContext de = new NorthwindDataContext();

The NorthwindDataContext object is an object of type DataContext. Basically, you can view this as something that maps to a Connection type object. This object works with the connection string and connects to the database for any required operations, The next line is quite interesting:

var query = dc. Products;

Here, you are using the new var keyword, which is an implicitly typed variable. If you are unsure of the output type, you can use var instead of defining a type and the type will be set into place at compile time, Actually, the code de, Products; returns a System.Data. Linq, Table<ConsoleApplicationl, Product> object and this is what var is set as when the application is compiled, Therefore, this means that you could have also just as easily written the statement as such:

Table<Product> query = dc.Products;

This approach is actually better because programmers coming to look at the code of the application will find it easier to understand what is happening. Using the var keyword has so much of a hidden aspect to it that programmers might find it problematic. To use Table<Product>, which is basically a generic list of Product objects, you should make a reference to the System. Data. Linq namespace, The value assigned to the query object is the value of the Products property, which is of type Table<Product>, From there, the next bit of code iterates through the collection of Product objects found in Table<Product>:

foreach (Product item in query)
{
Console.WriteLine(“{O} I {l} I (2)”,
item.ProductID, item.ProductName, item.UnitslnStock);

The iteration, in this case, pulls out the ProductID, ProductName, and UnitslnStock properties from the Product object and writes them out to the program, Because you are using only a few of the items from the table, you also have the option from the O/R Designer to delete the columns that you are not interested in pulling from the database, The results coming out from the program are presented here:

1 Chai I 39
2 Chang I 17
3 Aniseed Syrup I 13 .
4 Chef Anton’s Cajun Seasoning I 53
5 Chef Anton’s Gumbo Mix I 0
•• Results removed for space reasons ••
73 Rod Kaviar I 101
74 Longlife Tofu I 4
75 Rhonbrau Klosterbier I 125
76 Lakkalikoori I 57
77 Original Frankfurter grtineSoBe I 32

From this example, you can see just how easy it is to query a SQLServer database using LINQ to SQL.

Posted on October 31, 2015 in LINQ to SQL

Share the Story

Back to Top