stevenmcd.net

I have been trying to implement LiNQ to SQL where ever and whenever I can. It just saves so much time and effort with standard CRUD applications and especially for little import utilities. The company I work for gets multiple daily updates from numerous companies in .xls format and I’ve found that moving the import utilities over to the .Net framework 3.5 is saving me a lot of time and effort.

LiNQ is so easy to understand and quick to implement so its basically become the office benchmark. No more stored procedures for updating records and/or inserting records. Now I’ve heard about the Entity Framework for quite a while but haven’t really looked at it much. Since I am off work sick today I decided to have a look at it.

To be honest, it hasn’t immediately grabbed my attention. It seems to be very similar to LiNQ to SQL. I realize that its a rather broad generalization but in my case its true. So far all I’ve seen that the Entity framework basically just provides an abstraction layer for your database model so that when you make changes, you don’t need to rebuild your DBML structures. Please keep in mind I have only looked at this for an hour or so.

The biggestest benefit I can see is the interaction with databases other than SQL Server. This obviously has huge implications for those developers who don’t use SQL but since the company I work for purely works on SQL Server it doesn’t hold much appeal to me yet. There seems to be a massive overlap between LiNQ to SQL and LiNQ to Entities so my guess is after a year or two LiNQ to SQL is probably going to fall away quickly. I firmly believe Microsoft had their exceptionally lofty ambitions for LiNQ to Entities but they knew they would not be able to deliver on time so they pushed out LiNQ to SQL for the meantime to developers used to the LiNQ syntax.

From what I can see, the database modeling in LiNQ to SQL seems to be closer than that of the Entity Framework. The entity framework provides numerous other functions but I’ll include that in a later blog post.

Mark my words:
“LiNQ to SQL will give way to The Entity Framework withing 2-3 years”

There are quite a few of us that don’t have the luxury of sitting with Visual Studio 2008 and rightfully so many of us make use of the Express editions. Heres the problem though: What happens you want to generate a DBML file for a database that does not reside on your local machine and you cannot access the mdf and ldf files directly?

You use SqlMetal! SqlMetal is the tool used by LiNQ and VS to generate your DBML files. The good news is that you can use this manually! To generate the DBML file for a database on the server you simply to the following:

Step 1:
Open a command prompt and navigate to “C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\”

Step 2:
Make sure you have a connectionstring handy to copy

Step 3:
Run the following command at the prompt –> SqlMetal /conn:”connectionStringHere” /dbml:DatabaseName.dbml

And thats it! Obviously where I’ve entered “connectionStringHere” you need to enter your connection string between the inverted commas and where I’ve said DatabaseName.dbml you can enter the name you want for you DBML file.

Once this is done you can simply include this DBML in your Express Project and all the will generated as per normal procedure. What does confuse me however is why Microsoft gives us this file but doesn’t implement its full use in the express editions. It makes no sense to me!

Since starting my journey into seeing what LiNQ can do I have been amazed. It is truly amazing and this will definitely shorten development time on so many different projects! Here’s what I did tonight and you decide for yourself whether LiNQ is going to be useful or not.

I first created my database tables and set the relationship between the tables. Once you have done this, from your database server tab in Visual Studio, you simply drag and drop both tables into the LiNQ to SQL designer in Visual studio. This is what it looked like :

Once I had saved the dbml file I made a very simple ASP.Net Page for my Task Entry. I included a RadionButtonList to list all of the users in the database. Unfortunately you cannot bind directly to a linq table but I believe this is for security purposes. Once I can find the reason I will definitely post it because personally I feel one should be allowed to bind directly to a linq table. Once the page had been completed it looks like this:

Now as I previously stated, you cannot bind directly onto the linq table so you have to retrieve all the users and loop through them and add them individually to the RadioButtonList. Here is how I did it. This is by no means the perfect way of doing it but if you do know of a better way please let me know! This is the only way we learn is by learning from eachother!

TaskDataContext is the name that I gave to my LiNQ to SQL dbml class. You have to declare this to do any work on the database. This is basically your connection to your Database. Once we have our connection we want to retrieve all our users. I have declared a variable called _UserList to hold the list of users. The “var” keyword is new to Framework 3. The “var” keyword represents the “Implicitly Typed Local Variables” feature. The way this works is that when you declare the variable you don’t need to specify the type *BUT* the variable *MUST* be initialized on declaration. The compiler will interpret the value and assign its data type accordingly.

Once the information is retrieved from the database that _UserList variable is assigned to a List<USER> variable containing a generic collection of USER classes. I think the rest of the code is self explanatory. I loop through the collection creating a new list item for each USER in the _UserList collection and add those items to the RadioButtonList.

Now comes the magic. Adding the Task to the database. This is the part of LiNQ that has absolutely amazed me thus far. We write the code for the “Add Task” button:

This is it! No SQL code typed whatsoever! No Insert SPROC needed. No Inline SQL needed, nothing! We simply declare our TasksDataContext and then declare a new task. We set the properties needed by the class and add the new TASK class to our TaskContext and then call the SubmitChanges() Method. Lets have a look whats in the database shall we?

And there we go. And I’ll say it again, NO SQL CODE NEEDED FOR THE INSERT!!!!! I realize this makes me seem incredibly lazy which I am. Please feel free to correct anything I might’ve done wrong in your opinion or something I could’ve done better.

Next Article will cover Updating and Deleting using LiNQ.

Blogged with Flock

Read the rest of this entry »

I finally got around to installing Visual Studio Orcas Beta 2 again. Recently I wanted to start looking into making use of O/RM (Object relational mapping) for a private project I was starting up at home.

First I tried to install NHibernate and use that but since I was too lazy to figure out exactly how it worked I moved onto ActiveRecord. That was going well till I actually tried to run the damn project. ActiveRecord for some reason would not connect to my database. After checking everything and posting on the ActiveRecord forums nothing was resolved. It is strange though as the connectionstring ActiveRecord was using was being used for a standard SqlConnection which worked perfectly.

I have no decided to move my focus from third party “plug-ins” to a Microsoft Beta. For those of you have no heard of or read about LINQ then take a seat and get ready.

LiNQ (Language Integrated Query)

The LINQ Project is a codename for a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.

http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx

The LiNQ project covers several areas each naturally has a different function:

• LiNQ to Entities
• LiNQ to SQL Designer
• LiNQ to XML
• LiNQ Over DataSet

If you don’t have Orcas Beta 2 yet you can download it here : http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx. Now I realise its quite a massive download so if you want me to copy it to DVDs for you then drop me a mail here at “StevenMcd at StevenMcD.Net” and we’ll work something out to get these DVDs to you.

LiNQ to SQL

LiNQ to SQL is what I’m doing to be concentrating on first since this is basically and O/RM as previously stated. An amazing set of tutorials are available from ScottGu’s blog located here. His tutorials on LiNQ to SQL start here. His blog is wealth of information and if your aren’t subscribed to his RSS feed you are seriously missing out! Now what do I mean by saying that LiNQ is an O/RM? Imagine spending more time designing your classes to reflect your database structure? So take for an example an Employee Class. You would have an Employee Name, LastName, and probably ID Number. Imagine not having to write the general Select, Insert, Update and Delete SQL statements that makes our jobs so tedious?! Well imagine no more!

By constructing your class properly, and using a set of LiNQ attributes on your classes and properties you would not have to write these boring and tedious tasks! Obviously with more complex queries to the database you would still need to write your own Stored Procedures. In a future article I will cover this topic in more detail but for now I’ll end this off. I hope to have my next LiNQ article up by the weekend but due to other commitments it might only be early next week. For now, check out Scott’s blog and at the links listed below!

LINQ: .NET Language-Integrated Query
101 LiNQ Samples
LiNQ Wikipedia Entry

Blogged with Flock

Saturday the 28th of July there will be a study group on LiNQ at IT Intellect in Bryanston. SADeveloper.Net has organised half a dozen PCs with Visual Studio Orcas Beta setup on them. We will be messing around on them and learning and sharing any experiences with LiNQ and the implementations of it.

If you would like to join us, RSVP at http://sadeveloper.net/forums/thread/6764.aspx

Edit: I see Andre has now posted this on his blog and since he’s organizing it deserves blog credit Check his entry here