Suddenelfilio’s Weblog

Okay I had some free time to spend on LINQ  To SQL and because I’m working on a new personal project for the future (God only knows if I’ll ever finish it ). I need in my database a table “Countries”. Because I don’t want to create a script to load this table  and I certainly don’t want to fill it manually I started looking for a web service that could provide me the data I need. I want to keep the country’s Name and its ISO code.
For this I found a web service on http://www.oorsprong.org [WSDL] which does exactly what I need.

The web service has got a web method - ListOfCountryNamesByCode - that returns all the countries with their ISO codes. Just perfect!

Knowing where to get the data, we need to create the table that holds the data I’m getting from the web service. For this I only need a very basic table:

CREATE TABLE

[dbo].[Countries]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[ISO] [nvarchar] (3) NULL,
[Name] [nvarchar] (50) NULL,
[InsertDate] [datetime] NULL
) ON [PRIMARY]

Okay I admit maybe it’s not the best table structure ever, but it will do the trick.

So now the table is ready to store the data gotten from the web service all we need is to get the data.

First thing you need is to add a LINQ To SQL Mapping file (dbml file). Doing this is very simple. Just click somewhere in the solution explorer’s related project and select “Add new item”.

 From the list you can select LINQ To SQL Classes. When the dbml file is created you just drag-n-drop the tables from the server explorer onto the designer’s surface.

That’s all you need to do for the object relational mapping for the Countries table. Also notice that when you drop the Countries table onto the surface of the designer it is smart enough to rename the autogenerated object to Country because it will represent the Country entity that is stored in the Countries table.

Next you need to fetch the countries using the web service. So just add a web service reference to your project, create an instance and call the necessary method, in this case it’s the ListOfCountryNamesByCode which will return an array of tCountryCodeAndName instances.

org.oorsprong.webservices.CountryInfoService serv =    new org.oorsprong.webservices.CountryInfoService();
org.oorsprong.webservices.tCountryCodeAndName[] countries =    serv.ListOfCountryNamesByCode();

To work with the generated Country entity you will first need to create a database context. The designer already generated all the necessary code for you. All you need to do is create an instance like this:

CountryTableDataContext db = new CountryTableDataContext();

Be honest it couldn’t be much easier

The instace db contains a property called Countries which is a Linq table for Country entities. Because we have a type difference from what is returned by the web service and what needs to get stored in the database we will convert the array of tCountryCodeAndName instances to an IEnumerable of Country entities. For this we can use the LINQ to Objects technology together with anonymous typing and object initialization expressions.

 var ctr = from c in countries
           select new Country() { ISO = c.sISOCode,            Name = c.sName, InsertDate = DateTime.Now };

As you can see we don’t define the type of ctr it will be implicitly defined as whatever is returned from the LINQ query. The part after the equals sign is the same as writing a foreach statement.
The second part of the code is where we create a new Country instance and assign it’s values using object initialization expressions. For example the Country entity has got an ISO property so you can say ” new Country() { ISO = c.sISOCode} ” to assign it the value of the property sISOCode on the tCountryNameAndCode instance c.

In C# 2.0 the previous code would look like this:

 List<Country> countryList = new List<Country>();
  foreach (tCountryCodeAndName c in countries)
  {
      Country ctr = new Country();
      ctr.ISO = c.sISOCode;
      ctr.Name = c.sName;
      ctr.InsertDate = DateTime.Now();

      countryList.Add(ctr);
  }

Of course there are some other techniques in the new way of coding that I didn’t explain like local type inference, lambda expression (behind the scenes)…

Now that we have got an IEnumerable of Country entities “ctr” we can add them to the database context very easily:

db.Countries.AddAll(ctr);

That’s it, just do this last line of code below and all the countries with their respective ISO code will be stored nicely in the database. Isn’t that cool ?!?

db.SubmitChanges();

If you have any questions on this post you can use the comment option to contact me about this.