DLinq Tutorial Using External Mapping File and SqlMetal to Generate Classes and Mapping File
by David Hayden ( .NET Developer )
I just wrote a DLing Tutorial on my other blog where I hand-coded everything from scratch. I did this partly because it helps me better understand how DLinq works and partly because I didn't realize that SqlMetal would create the external mapping file :) You can read my other post here:
Now that I realize SqlMetal is even more powerful than once thought, I thought I would let it generate my classes and XML Mapping File for me as opposed to doing it myself. Here is a quick DLinq Tutorial if you haven't tried this yourself.
Download LINQ May 2006 CTP
Before you can do any of this, you need to download and install the LINQ May 2006 CTP from Microsoft. It is a CTP, so I would not recommend running it on a production machine just to be safe. I, personally, have it running in a virtual machine. Download the LINQ Preview here.
Creating my DVDLibrary Database
I created a simple database, called DVDLibrary, that has 2 tables, Genres and Movies.
I then point SqlMetal to my database and ask it to create both the classes and external XML Mapping File. SqlMetal is located in C:\Program Files\LINQ Preview\Bin if you accepted the default during installation. Here is the command:
sqlmetal /server:. /database:DVDLibrary
/map:Mappings.xml /code:Classes.cs
The database and classes generated by SqlMetal look something like this:
DLinq XML External Mapping File
SqlMetal has also generated an external XML Mapping File that maps the classes generated to the database tables, which is much more complete than what I had done by hand:
xml version="1.0" encoding="utf-8"?>
<Database xmlns:xsi="http://www.w3.org/
2001/XMLSchema-instance" xmlns:xsd=
"http://www.w3.org/2001/XMLSchema"
Name="DVDLibrary">
<Table Name="Genres">
<Type Name="Genre">
<Column Name="GenreId" Member="Id"
Storage="_GenreId" DbType="Int NOT NULL
IDENTITY" IsIdentity="True" IsAutoGen="True" />
<Column Name="Name" Member="Name" Storage="_Name"
DbType="VarChar(100) NOT NULL" />
<Association Name="FK_Movies_Genres" Member="Movies"
Storage="_Movies" ThisKey="Id"
OtherTable="Movies" OtherKey="GenreId" />
Type>
Table>
<Table Name="Movies">
<Type Name="Movie">
<Column Name="MovieId" Member="Id" Storage="_MovieId"
DbType="Int NOT NULL IDENTITY" IsIdentity="True"
IsAutoGen="True" />
<Column Name="GenreId" Member="GenreId"
Storage="_GenreId" DbType="Int NOT NULL" />
<Column Name="Name" Member="Name" Storage="_Name"
DbType="VarChar(100) NOT NULL" />
<Association Name="FK_Movies_Genres" Member="Genre"
Storage="_Genre" ThisKey="GenreId"
OtherTable="Genres" OtherKey="Id"
IsParent="True" />
Type>
Table>
Database>
Creating an Instance of DVDLibrary DataContext Class
SqlMetal creates a strongly typed DataContext Class. To instantiate it, we need to pass in the connection string of the database as well as the XML Mapping File. In this example, I have embedded the XML File in my winform applications.
XmlMappingSource mapping;
using (Stream stream = Assembly.
GetExecutingAssembly().GetManifest
ResourceStream("DLinqTutorial.Mappings.xml"))
{
mapping = XmlMappingSource.FromStream(stream);
}
string connectionString = "Data Source=.;Initial
Catalog=DVDLibrary;Integrated Security=True";
DVDLibrary dvds =
new DVDLibrary(connectionString, mapping);
Querying the Database and Adding Records Using DLinq
Now we can just start querying for records in the database and saving new records in the database without using any SQL!
// Get Action Genre
Genre genre = dvds.Genres.Single(c =>
c.Name.Equals("Action"));
// Get All Action Movies
ICollection<Movie> actionMovies = genre.Movies;
// Create a New Drama Genre
Genre drama = new Genre();
drama.Name = "Drama";
// Ransom with Mel Gibson
Movie ransom = new Movie();
ransom.Name = "Ransom";
// Add Ransom to the Action Genre
drama.Movies.Add(ransom);
// Add This New Genre to the Library
dvds.Genres.Add(drama);
// Save it All.
// This Will Save New Genre - Drama
// and Movie Ransom.
dvds.SubmitChanges();
Conclusion
DLinq is just plain cool and I look forward to its release.
Source: David Hayden ( .NET Developer )
Filed: LINQ, O/R Mappers