jump to navigation

LINQ to SQL – Querying the Pubs Database July 1, 2008

Posted by fofo in general .net, LINQ.
Tags:
trackback

In this post I will demonstrate how to query a sql server database with c# 3.0 and LINQ. When you finish this post you will realise how all these previous posts in C# 3.0 language enhancements relate with LINQ. 

I assume that you are a little bit familiar with what LINQ is. In a nutshell LINQ stand for Language-Integrated Query and is now available as an integral part of Visual Studio 2008 (code name Orcas). We (developers) have native syntax in the form LINQ to C# and VB.Net for accessing data from any repository. The repository could be a memory object, database and XML files. What Microsoft wanted to do with LINQ was to address the current development model as we know it and create a new paradigm where we can think databases in the context of Object Oriented Programming.With LINQ we can query any source of data.
We can easily retrieve data from any object that implements the IEnumerable<T> interface. Microsoft divides LINQ into three areas.

LINQ to Object (Queries performed against the in-memory data)
LINQ to ADO.Net
     LINQ to SQL (Queries performed against SQL Server databases)
     LINQ to DataSet (Supports queries by using ADO.NET data sets and data tables)
     LINQ to Entities
LINQ to XML (Queries performed against an XML source)

To follow this example you must have Visual Studio 2008 and SQL server 2005.I am going to use the Pubs database. You can download the installation scripts of the Pubs database from here. But you can still read on and enrich your knowledge of LINQ to SQL without typing all that code.

1) Create an ASP.NET web application with C# in your filesystem.Add a gridview web server control to the default.aspx page. Add 4 buttons to the default.aspx page. Name them “Select”, “Insert”, “Update”, “Delete”

2) I assume you have installed the Pubs database in your local sql server

3) Add a new item to your project.In the window that fires up select Data and from the templates choose LINQ to SQL classes. Save it with the name Pubs.dbml

4) When you click Add you will see the Object Relational Designer surface.

5) Connect to your Pubs database from Server Explorer.Drag and drop some tables (author,titleauthor,title) from the Server Explorer to the designer area (see the picture below) . You can add as many tables you want. You can add views and stored procedures. For our example these 3 tables suffice.

 

 

 

 

 

 

 

 

 

 

6) If you notice you will see that a new file is added automatically to our poject. It is called Pubs.designer.cs.

7) If you open this file you will see lots of code. In the first few lines PubsDataContext class. In this class all our database objects are scripted – mapped to objects. We will use an instance of this class to create an object and query(select,insert,update,delete) our database by using native c# code.

8) Go to your default.aspx page and double click the select button.In the button event click handler, you can type this code. You can write any select query you want. I will give you some examples. In this example we just want to return all the rows from titleauthors table.

PubsDataContext db = new PubsDataContext();
var tauthors = from ta in db.titleauthors
select ta;

GridView1.DataSource=tauthors;

GridView1.DataBind();

I will try to explain the code above. We create an instance of our PubsDataContext.

In order to query the database we need to have a variable to store the result set and we do that by creating the implicitly typed local variable called tauthors. Then we just assign the Gridview datasource to our variable and then we just use the Databind method of the Gridview object.Run the application and see the results. We see in our browser when we click the select button the whole result set for the titleauthors table.

We can have a more sophisticated example. Comment out all the above lines and type the code below.

PubsDataContext db = new PubsDataContext();

var tauthors = from ta in db.titleauthors
where ta.au_id==”172-32-1176″
select ta;

GridView1.DataSource=tauthors;

GridView1.DataBind();

We see here we can select a single record from our database through the where clause.

Comment out the code above and type the following code in the select button event click handler.

We can use in our query properties(table fields) that are not part of our table but in the background we can access these properties if they are linked through a relationship with our main table. In this instance we access the au_fname field of the author table because this table is linked with the titleauthors table through foreign keys.

PubsDataContext db = new PubsDataContext();

var tauthors = from ta in db.titleauthors

where ta.author.au_fname == “Marjorie”

select ta;

GridView1.DataSource=tauthors;

GridView1.DataBind();
 

Comment out the code above and type the following code. I am not doing anything fancy here. I merely point out the various options we have when we query our database and how they are associated with SQL statements. We can view our results in desceding order.

PubsDataContext db = new PubsDataContext();

var tauthors = from ta in db.titleauthors
                        
where ta.author.au_fname == “Marjorie”
orderby ta.royaltyper descending

select ta;

GridView1.DataSource=tauthors;

GridView1.DataBind();

We can shape our query to return only the fields from titleauthors table that we want(in this case royaltyper).

PubsDataContext db = new PubsDataContext();

var tauthors = from ta in db.titleauthors
where ta.author.au_fname == “Marjorie”
orderby ta.royaltyper descendin

select new { ta.royaltyper };

GridView1.DataSource=tauthors;

GridView1.DataBind();

Now we need to show how to insert an item in the database and in our titleauthors table.

First create a method showauthors and place the code that gives us all the rows from the titleauthors table.

private void showauthors()
        {

PubsDataContext db = new PubsDataContext();

var tauthors = from ta in db.titleauthors
select ta;
GridView1.DataSource = tauthors;
GridView1.DataBind();
        }

9) Double click the Insert button and in the click event hander type this. If your run the application you will see a new record added in our table.

 PubsDataContext db = new PubsDataContext();

//new c# 3.0 feature called object initialiser

titleauthor ta = new titleauthor { au_id = “998-72-3567”, title_id = “BU1111”, au_ord = 6, royaltyper = 400 };

db.titleauthors.InsertOnSubmit(ta);
db.SubmitChanges();
showauthors();

10) Double click the Update button and in the event click handler type the following code. We try to update a value based on an author’s first name.Run the application and click the update button. The new value of 1000 will be updated.

PubsDataContext db = new PubsDataContext();

titleauthor titleauthor = db.titleauthors.First(ta => ta.author.au_fname.StartsWith(“Michael”));
           

titleauthor.royaltyper=1000;
db.SubmitChanges();
showauthors();

11) Double click the Delete button in the .aspx page and type the code below.In this example we just delete the record we updated before.We call the DeleteOnSubmit method and then submit the changes and show the update version of the table again.Run the application to see for yourself.

PubsDataContext db = new PubsDataContext();
titleauthor titleauthor = db.titleauthors.First(ta => ta.author.au_fname.StartsWith(“Michael”));
db.titleauthors.DeleteOnSubmit(titleauthor);
db.SubmitChanges();
showauthors();
 

Hope this post helped you to understand what LINQ is and how we can use it to query a SQL Server database.

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Comments»

1. Database Management » Blog Archive » LINQ to SQL - Querying the Pubs Database - July 1, 2008

[…] Read the rest of this great post here […]

2. How to monitor and debug LINQ to SQL queries « DOT NET RULES - November 19, 2008

[…] November 19, 2008 Posted by fofo in general .net. trackback In one of my previous posts where I tried to explain how to query an Sql Server database with LINQ to SQL, I said that LINQ […]

3. Create an XML file using LINQ and import XML data into an excel spreadsheet « DOT NET RULES - March 31, 2009

[…] If you want to read another post of mine regarding LINQ to SQL click here […]

4. darma - May 11, 2011

indonesia langguage: bagus sekali, terimakasi atas postingannya..
englis langguage: very nice, thank you for post

5. seo search - August 3, 2012

Excellent way of telling, and pleasant paragraph to take information on the topic of my presentation topic, which i am going to convey in academy.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: