Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
How to Access Relational Data using LINQ to SQL
|
The article will explain to access relational data using LINQ to SQL. We will create an object model for the Northwind database, and then use the object model to access the database using the new C# 3.0 query expressions and LINQ to SQL APIs.
Creating a LINQ Project
a. Click the Start | Programs | Microsoft Visual Studio 9 | Microsoft Visual
Codename Orcas menu command.
b. In Microsoft Visual Studio, click the File | New | Project… menu command
c. In the New Project dialog, in Visual C# |Templates, click Console Application
d. Provide a name for the new solution by entering “LINQToSQL” in the
Name field
e. Click OK
Adding a reference to the System.Data.Linq assembly
a. In Microsoft Visual Studio, click the Project | Add Reference… menu command
b. In the Add Reference dialog make sure the .NET tab is selected
c. click System.Data.Linq assembly
d. Click OK
e. In Program.cs import the namespace System.Data.Linq adding the following line
just before the namespace declaration:
using System.Data.Linq;
Mapping Northwind Customers
a. Create an entity class to map to the Customer table by entering the following code
in Program.cs (put the Customer class declaration immediately above the Program
class declaration):
[Table(Name="Customers")]
public class Customer
{
[Column (IsPrimaryKey=true)]
public string CustomerID;
}
Note: The Table attribute maps a class to a database table. The Column attribute then
maps each field to a table column. In the Customers table, CustomerID is the primary
key and it will be used to establish the identity of the mapped object. This is
accomplished by setting the IsPrimaryKey parameter to true. An object mapped to
the database through a unique key is referred to as an entity. In this example,
instances of Customer class are entities.
b. Add the following code to declare a City property:
[Table(Name="Customers")]
public class Customer
{
[Column (IsPrimaryKey=true)]
public string CustomerID;
private string _City;
[Column(Storage = "_City")]
public string City
{
get { return this._City; }
set { this._City = value; }
}
}
Note: Fields can be mapped to columns as shown in the previous step, but in most
cases properties would be used instead. When declaring public properties, you must specify the corresponding storage field using the Storage parameter of the Column attribute.
c. Enter the following code within the Main method to create a typed view of the
Northwind database and establish a connection between the underlying database
and the code-based data structures:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(@"Data
Source=.\sqlexpress;Initial Catalog=Northwind");
// Get a typed table to run queries
Table Customers = db.GetTable();
}
Querying Database Data
a. Although the database connection has been established, no data is actually
retrieved until a query is executed. This is known as lazy or deferred evaluation.
Add the following query for London-based customers:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(@"Data
Source=.\sqlexpress;Initial Catalog=Northwind");
// Get a typed table to run queries
Table Customers = db.GetTable();
// Attach the log showing generated SQL to console
// This is only for debugging / understanding the working of LINQ to SQL
db.Log = Console.Out;
// Query for customers in London
var custs =
from c in Customers
where c.City == "London"
select c;
}
Note: This query, which returns all of the customers from London defined in the
Customers table, is expressed in query expression syntax, which the compiler will translate into explicit method-based syntax. Notice that the type for custs is not declared. This is a convenient feature of C# 3.0 that allows you to rely on the compiler to infer the correct data type while ensuring strong typing. This is especially useful since queries can return complex multi-property types that the compiler will infer for you, with no need for explicit declaration.
b. Add the following code to execute the query and print the results:
static void Main(string[] args)
{
// Use a standard connection string
DataContext db = new DataContext(
@"Data Source=.\sqlexpress;Initial Catalog=Northwind");
// Get a typed table to run queries
Table Customers = db.GetTable();
// Query for customers in London
var custs =
from c in Customers
where c.City == "London"
select c;
foreach(var cust in custs)
{
Console.WriteLine("ID={0}, City={1}",
cust.CustomerID, cust.City);
}
Console.ReadLine();
}
c. Press F5 to debug the solution, You will see the console with data.
d. Press ENTER to exit the application
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|