We’ll use the “Northwind” database sample that comes with
SQL Server for our LINQ/DLINQ code below.
We’ll start by creating an object model that maps to the
Northwind database. We can do this one of three ways:
1) Write this by hand
2) Use the new VS DLINQ designer to
graphically define the class organization and relationships
3) Use the built-in “SQLMetal.exe”
command-line utility to create it for us
For this sample walkthrough I’m going to use #3. To create
the mapping I simply need to type the below statements in a command-prompt to create
this for the Northwind database on my local box:
Listing 4
>> cd c:\Program Files\LINQ Preview\Bin
>> sqlmetal /database:Northwind /pluralize /namespace:Northwind /code:Northwind.cs
SqlMetal will infer the appropriate classes from the
database metadata, and by default add appropriate validation constraint checks
(NOT NULL, Column Types and Size Limits, etc) to the object model. Because
I’ve specified the /pluralize parameter option, it will use some default naming
logic conventions to create the property and table names – for example creating
a “Customer” class that maps to the “Customers” table (obviously you can go in
and override these if you don’t like the defaults).
We could optionally also specify a /map:[filename] parameter
to control whether the database mappings are stored using code attributes or in
an XML metadata file. We could also indicate that stored procedures, functions
and views should be mapped via the /views, /sprocs, /functions parameter
switches.
The result of the SQLMetal’s code generation will be saved
within the “Northwind.cs” file we specified and scoped within the “Northwind”
code namespace above. We’ll then want to copy this file under the “/app_code”
directory within our new ASP.NET project:
Figure 2
The last step we’ll then want to do is to add a
“<connectionStrings>” section within the web.config of our application to
configure our database connection information:
Listing 5
<connectionStrings>
<add name="Northwind"
connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
This will allow us to easily change the database deployment
location later. With ASP.NET 2.0 we can also now optionally encrypt the
web.config values for secure deployment. ASP.NET 2.0 also installs a
built-in admin extension to the IIS admin tool that allows IT administrators
easily manage the connection strings during and after deployment as well.
Once the above steps are done, we are ready to use our newly
created Northwind DLINQ data layer anywhere within our application.
