Very short and simple example of using SqlMetal

Very short and simple example of using SqlMetal

  • Comments 3

A simple sample of how to use SqlMetal.

 

"Code Generation Tool (SqlMetal.exe)"

http://msdn.microsoft.com/en-us/library/bb386987.aspx

 

This tool will create mappings and code for LINQ to SQL.

So, as mentioned, simple and short. Create a database, some tables and insert some data.

 

create database VeryMetal

go

use VeryMetal

go

create table Users (UserId int primary key, FirstName nvarchar(10), LastName nvarchar(10), DepartmentId int)

go

create table Departments(DepartmentId int primary key, DepartmentName nvarchar(10))

go

 

--Insert some users and departments

insert into Users values (1, 'John', 'Johnson', 1)

insert into Users values (2, 'Paul', 'Paulson', 2)

insert into Users values (3, 'Mike', 'Mikeson', 1)

insert into Users values (4, 'Mary', 'Maryson', 2)

 

insert into Departments values (1, 'Support')

insert into Departments values (2, 'Finance')

 

Then fire up the Visual Studio Command Prompt and run the following:

 

Sqlmetal /server:<your server> /database:VeryMetal /code:C:\Temp\MetalCode.cs /map:C:\Temp\MetalMap.xml /namespace:VeryMetalEntities

 

This should now have generated the .cs and .xml files.

 

So, create new console application, add the MetalCode file to the project and enter the following code:

 

        static void Main(string[] args)

        {

            try

            {

                String cs = @"Data Source=<your server>;Initial Catalog=VeryMetal;Integrated Security=True";

                String file = @"C:\Temp\MetalMap.xml";

 

                var dc = new VeryMetal(cs, XmlMappingSource.FromUrl(file));

                var users = from u in dc.Users where u.DepartmentId == 1 select u;

               

                Console.WriteLine("All users in Support Department.");

                foreach (var user in users)

                {

                    Console.WriteLine("{0} - {1}", user.FirstName, user.LastName);

                }

 

                Console.WriteLine("\nGet single user with firstname starting with Pau ");

 

                Table<Users> ut = dc.GetTable<Users>();

                var users2 = ut.Single(x => x.FirstName.StartsWith("Pau"));

                Console.WriteLine("{0} - {1}", users2.FirstName, users2.LastName);

               

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

Output should be:

 

All users in Support Department.

John - Johnson

Mike - Mikeson

 

Get single user with firstname starting with Pau

Paul - Paulson

Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Dude - use variable names which HELP in describing what you are doing, especially since this is an "example".  "String cs" and String file" are truly useless variable names.  File?  What file?  Oh a "Mapping File".  And a "dc", yeah, like Washington DC I guess.  It is supposed to be an 'e'.  What?  Oh yeah, the 'e' stands for 'example' - sorry.

    Lastly, put in a foreign key and/or at least do your parent/child inserts in the right order. Parent always comes first, then the child.

    These are simple, obvious, common sense things that every developer should follow - but few actually do.

  • Wow Bill, try kicking your dog first before commenting.

  • @Bill this code is about as simple and obvious as code gets.  Any novice developer should be able to grasp the basics from this example.

    Device Context = dc Is a pretty standard abbreviation for the DeviceContext.

    C:\Temp\MetalMap.xml = file  i.e. Map File it has the word Map in it have to be pretty obtuse to miss that one.

    In fairness you are correct I would never write production code this way.

    Then again most of my production code has layer after layer of abstraction(easy to understand at each level) sometimes a bit obscure in general.

    Examples are not necessarily the best place to beat people over the head with best practice.  

    Examples are best when simple and to the point without straying to far into extraneous detail.

    Best practice is best left to the developer to do/learn for themselves. Perhaps from a text/whitepaper/course/example about best practice.

    This is a fantastic example of a not necessarily straight forward tool(Just try reading the MS documentation and see).

    A trivial example about creating a class from a DB using SQLMetal shouldn't worry about FKs or order of filling.  

    In fact the data could have been added after the class was created. The only place I see a possible improvement is that Dave might have used his class to insert the data.

Page 1 of 1 (3 items)