Galin Iliev's blog

Software Architecture & Development

LINQPad and Think in LINQ

I've just found that Joe Albahari, co-author of the forthcoming C# 3.0 in a Nutshell, has released the Beta 2 version of his beta LINQPad - it is like Notepad for LINQ :)

There is also a challenge:

  1. Locate the shortcut for SQL Management Studio on your Start Menu and move it some place else.
  2. In its place, insert a shortcut to LINQPad.
  3. For the next week, perform all your ad-hoc SQL queries using only LINQPad.

At the end of the week, you will actually think in LINQ, rather than thinking in SQL and translating to LINQ. (LINQPad also allows old-fashioned SQL queries for tasks are genuinely impossible or clumsy in LINQ. You'll feel an unpleasant sense of failure, though, should you cheat and use this feature frivolously :)

If you can write all your ad-hoc queries in LINQ, you have a big head-start over everyone else in C# 3.0, LINQ and LINQ to SQL. (Further, LINQPad gives you a knowledge of LINQ that does not depend on Intellisense, and so will not fall to pieces at job interviews!) And continued use of LINQPad will keep your LINQ knowledge alive—if you're not lucky enough to be using LINQ in your current projects.

 

Indexed LINQ

With the upcoming Visual Studio 2008 and new C# and VB syntax the abbreviature LINQ is already wide spread. With LINQ are mentioned a few branches- LINQ to SQL (the most popular one), LINQ to Objects, LINQ to XML.

But adding features for working with data to object oriented language brings its questions. Let's see the definition of LINQ to Objects from hookedonlinq.com:

"LINQ to Objects allows .NET developers to write “queries” over collections of objects. Out of the box there is a large set of query operators that provide a similar depth of functionality to what we expect from any SQL language working with a relational database, and if what we need isn’t present out-of-the-box, we can add our own."

Sounds good, isn't it? But the question arises: By having collection of objects (similar to tables) and language that allow writing queries (similar to SQL) is is there a way to speed up processing (similar to indexes)?

This is reasonable question and there are articles on the subject already. This is called i4o (indexes 4 objects). Here are some articles and links:

I think this is interesting and there is a future for it but in low market segment. I know there is such thing as Object Database and this sounds like one. In the posts I read it seems that MS won't put effort on something that would compete SQL Server 2005 Express if it grows enough. They'll rather improve SQL Server to support mapping and with this they'll achieve part if the goal while having SQL Server in the shape we know it. This is not in far future. It is already known that MS SQL Server 2008 will have Entity Framework built in.

 

 

MS SQL 2005 Service Broker issue

I got an email from a colleague of mine with a nice issue solution:

When you attach or restore a database that has service broker queues on a different database, you MUST rerun this command:

alter database elgdistribution set enable_broker

Without doing that, the queues will fail to write – SILENTLY! They do not throw any sort of exception and appear to be functioning properly. VERY IRRITATING.

Thanks to Brian Romanko

Project Astoria

I am on codenamed projects wave today and I would like to share with you another one - project Astoria (reposted from official blog):

Project Astoria Overview

The goal of the Astoria project is to enable applications to expose data as a data service that can be consumed by web clients within corporate networks and across the internet. Such data services are reachable over regular HTTP requests using standard HTTP verbs such as GET, POST, PUT and DELETE to represent the operations against the service.   The payload format for the data exchanged with the service can be controlled by the client and all options are simple, open formats such as plan XML and JSON. The use of web-friendly technologies make it ideal as a data back-end for AJAX-style applications, Rich Interactive Applications and other applications that need to operate against data that is across the web. 

                Further reading: Astoria Overview, Using Astoria        

By Mike Flasko

For those who are interested in architecture & design & process the team made another post Transparency in the design process which describes how the things happens inside MS (described for this particular project)

Hello "Jasper"

I wrote about the couple new projects that was announced by Microsoft recently and I’ve installed “Jasper” and I had some free time this weekend to play with it.

You may ask What is Jasper? The answer comes from documentation: 

Microsoft® Codename “Jasper” is a set of components aimed at fulfilling the need for a rapid and iterative development experience for data. With “Jasper”, you are able to just point at a database and immediately begin coding against its data using intuitive, domain-specific data classes. No configuration and no source code generation are required. Jasper works with existing application frameworks (including ASP.NET, WinForms, and WPF) and existing, real-world databases.

First thing I noticed is the first CTP comes with samples in Visual Basic and IronPython which is not the best way to impress C# fans J I haven’t given up and I kept investigating and I am going to share with you my findings.

Working with Jasper is very similar to LINQ to SQL. Everything is done through DynamicContext class and using it’s methods GetQuery,  AcceptAllChanges, CreateQuery and many others . The difference is that in LINQ to SQL there is a class that extend DataContext class but in Jasper  DynamicContext class is compiled in MS assemblies. If you play with it you’ll notice there aren’t any properties that represents tables in underlying database. But this is code snippet from samples that comes with Jasper:

Dim connectionString As String

Dim context As Object

 

connectionString = _ ConfigurationManager.ConnectionStrings("Northwind").ConnectionString

context = DynamicContext.CreateDynamicContext(connectionString)

 

'*** get Customer table by late binding

Dim query As Query = context.Customers

 

'*** bind to grid

ResultsGrid.DataSource = query

ResultsGrid.DataBind()

Do you see the row that ends with context.Customers? On this row the content of Customers table is got and can be used in lines below to be bound to GridView control.  As we said the class DynamicContext is same for all applications and the tables are got using late binding because the Jasper data classes are generated dynamically at runtime when DynamicContext.CreateDynamicContext() is called.

 Late binding could be painful in C# and it seems this is reason not having samples in C# for nowJ.

I will cover another feature in Jasper – dynamic queries. There is very easy way to construct queries in Jasper. All you have to do is using Microsoft.Jasper.Query class and it’s methods  Select, OrderBy, Where, Union ( and many others ):

Dim query As Query = context.Customers

query = query.Where("it.Country = 'USA'")

query = query.OrderBy("it.CompanyName")

query = query.Select("it.CustomerID, it.CompanyName, it.City")

 

Dynamic queries are doubtful from architecture standpoint as they could harm the design and easy mess DAL code with business logic. This is why I recommend avoiding dynamic queries when possible or use them very carefully.

This blog entry contains very little code but I can assure you this is enough to get Customer table content and display it in Grid control on ASP.NET page. J

You can get Jasper from Microsoft Downloads.

And don’t forget the prerequisites:

1)      Microsoft Visual Studio® Codename “Orcas” Beta 1.  Install details can be found at http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx.

2)      Microsoft SQL Server™ 2005.  The Microsoft SQL Server 2005 Express Edition can be found at http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en.

3)      (Optional) Microsoft Iron Python 1.1.  The install can be found at  http://www.codeplex.com/IronPython/Release/ProjectReleases.aspx?ReleaseId=2573.

Calculate distance between locations using latitude and longitude

A couple months ago I had to calculate distance between locations using latitude and longitude... at that time I found a T-SQL sample that did the job but I forgot the site... I was impressed and I translated the code to C# and put in .NET Code library class and then, to make thigs complicated ( and usable in MS SQL 2005 Analisys Services- SSAS ) I've put in SSAS and call it from MDX query..

So far so good... but now I had some doubts about data accuracy and I searched the web again :). I havent; found same piece of code but I found better - and even article that describes the whole thing :)

In order not to forget again ( and to benefit you ) and I osting the code here. I recommend reading the article - Using SQL Server Yukon's .NET CLR Features Practically by Kent Tegels MCDBA, MCSE+I, MCP+SB


T-SQL user function:

CREATE FUNCTION dbo.udfComputeDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float
)
RETURNS float
AS
begin
-- dLong represents the differences in longitudes
-- while dLat is the difference in latitudes
declare @dLong float
declare @dLat float
-- To keep the calculation easier to understand,
-- we have simplified it by computing it by parts.
-- This value temporarily holds the value of the
-- first calculation.
declare @temp float
-- Convert the decimal degrees to radians
set @lat2 = radians(@lat2)
set @lon1 = radians(@lon1)
set @lat1 = radians(@lat1)
set @lon2 = radians(@lon2)
-- Compute the degree differences
set @dLong = @lon2 - @lon1
set @dLat = @lat1 - @lat2
-- Compute the first part of the equation
set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))
-- Return the approximate distance in miles
-- Note that 3956 is the approximate median radius of the Earth.
return (2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0
end


And C# function

private const double PI_OVER_180 = 0.0174532925;
private static double radians(double DecimalDegrees)
{
return DecimalDegrees * PI_OVER_180;
}
public static SqlDouble ComputeDistance(SqlDouble FromLat,
SqlDouble FromLong, SqlDouble ToLat, SqlDouble ToLong)
{
double lat1, lat2, lon1, lon2,
dLong = 0.0, dLat = 0.0, subCalc = 0.0;
lat1 = radians((double)(FromLat));
lon1 = radians((double)(FromLong));
lat2 = radians((double)(ToLat));
lon2 = radians((double)(ToLong));
dLong = (double)(lon2 - lon1);
dLat = (double)(lat2 - lat1);
subCalc = (Math.Pow(Math.Sin(dLat / 2.0), 2.0))
+ Math.Cos(lat2) * Math.Cos(lat1)
* (Math.Pow(Math.Sin(dLong / 2.0), 2));
return ((2.0 * Math.Atan2(Math.Sqrt(subCalc),
Math.Sqrt(1.0 - subCalc))) * 3956.0);
}
[STAThread]
public static void Main(string[] args)
{
Console.WriteLine(
ComputeDistance(40.7539,-96.6428, 41.28692,-96.07023));
Console.ReadLine();
}

 

Two more projects in MS with codenames

ADO.NET blog announced that two projects has been set up inside Microsoft recently - both revealed at MIX 07.

First one has codename "Astoria" and it's goal is

to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over HTTP, and URIs are used to identify the various pieces of information available through the service. Interactions with the data service happens in terms of HTTP verbs such as GET, POST, PUT and DELETE, and the data exchanged in those interactions is represented in simple formats such as XML and JSON.

The first early release of Astoria will be a Community Tech Preview that you can download, as well as an experimental online service you can access over the internet.

Check out the Astoria webpage at http://astoria.mslivelabs.com for more information and a link to the download.

 

Second one is called "Jasper" and aim at faciliating data-driven development. Developing data-driven applications could be tedios taks as developers have to spend a lot of time developing supporting infrastructure and Data Access Layer insead of focusing on real business problem. There are many O/R Mapping tools that reduce ammount of work by offering DB-classes mapping along with code generation. I personally though MS try to catch up with LINQ to SQL ( a.k.a DLINQ) and Entity Model in Visual Studio Orcas... but it sounds like they aim higher and use Entity Model for:

    • Dynamic generation of data classes so there is no configuration or design time code-gen to carry around.
    • Rich query and O/R capabilities because “Jasper” is built on top of the Entity Framework.
    • Auto-binding capabilities for ASP.NET, WinForms, and WPF to make binding data to a UI simple and automatic.

Learn more about “Jasper” on the MSDN Data Access Incubation Projects site

MS DevDays 2007

Last week I attended at MS DevDays 2007 - the biggest event organized by Microsoft in Bulgaria. It was impressive as there were four tracks and dev track was so popular that MS decided to open another room where dev lectures could be read for second time :) This was good idea as devs were able to listen more lectures....

This is not all... This year I learned some magic words :) Next Visual Studio Team System has codename - it is Rosario

SQL Server team is working hard too on SQL Server Codename Katmai. The interesting part about it is that there will be Entity model build in it?!?! Yep, I suppose this will be the same Entity Framework that was dropped of VS Orcas release... as SQL team target developers too and they work on make our life easier when we develop data-driven applications.

Here are some pics if the event:

 







Thank you, guys for the interesting presentations!

Connection pooling and application domains

Yesterday I wrote about my Connection pooling tests and I had good comment from Marto Kulov about application domains.

It was easy so I wrote the following code to create new application domain and execute two forms from different assemblies in order to test if they share same connection pool

 

   1:  AppDomain domain = AppDomain.CreateDomain("myNewDomain");
   2:   
   3:  Form frm1 = domain.CreateInstanceFromAndUnwrap("WindowsApplication1.exe", "WindowsApplication1.Form1") as Form;
   4:  Form frm2 = domain.CreateInstanceFromAndUnwrap("WindowsApplication2.exe", "WindowsApplication2.Form1") as Form;
   5:   
   6:  frm1.Show();
   7:  frm2.Show();

And the answer is Yes, They share same connection pool. Both forms executed same for loop and opened 10 connections each to the MS SQL Server. And on the SQL Server side was only one connection alive.

So this is the way to share a connection pool between two processes - put them in one process and in one application domain.

 

 

Connection pooling

Two days ago I decided to dive into connection pooling secrets. What a coincidence with Sahil Malik as he wrote blog article about his research same day J.

In addtition to his article I can describe what I tested. As I have job to do I will miss screenshotsL.

We were considering option to pass data to SQL SPs usign connection string and we wanted to know how this affects connection pooling. So I created simple winform application with a button with the following.

   1:  string connString = @"data source=.\SQLExpress;
   2:                      user id=gu; password= 123; 
   3:                      Initial catalog = master;
   4:                      Workstation ID=test_host{0};
   5:                      Pooling=true;
   6:                      Connect Timeout = 60";
   7:   
   8:  for (int i = 0; i < 10; i++)
   9:  {
  10:      SqlDataReader dr = null;
  11:      string cmdText = "SELECT date=getdate()";
  12:      SqlConnection cn = new SqlConnection(string.Format(connString, 1));
  13:      SqlCommand cmd = new SqlCommand(cmdText, cn);
  14:      try
  15:      {
  16:          cn.Open();
  17:          dr = cmd.ExecuteReader();
  18:          if (dr != null)
  19:          {
  20:              while (dr.Read())
  21:              {
  22:                  Console.WriteLine(dr["date"].ToString());
  23:              }
  24:          }
  25:      }
  26:      finally
  27:      {
  28:          if (dr != null) dr.Close();
  29:          cn.Close();
  30:      }
  31:  }

I used same SQL statement as Sahil to determine number of physical connection to database.

After executed the following code I got only one connection opened. Note that connection string is always same as I pass 1 as parameter in string.Format(connString, 1) on line 12.

Let’s change this to string.Format(connString, i). As expected I ended with 10 opened connections as connection string is different for every SqlConnection object.

Let’s undo change and use connection pooling again. We have for loop with 10 connection objects opened but only one physical connection is opened on SQL side.

Let’s start two instances of our test application and check what happens by clicking on buttons. We executed for loop once in each instance. By executing Sahil’s SQL statement we see two connections  although all 20 connection has same connection string.

I opened Activity Monitor from SQL Management Studio and I was able to see the connections – everything was same except Net Address. According the documentation:

Net Address

Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.

Strang… both instances are on same machine…

So as conclusion the statement from Wikipedia’s Connection Pooling page – “A Connection Pool is a cache of database connections maintained in the database's memory so that the connections can be reused when the database receives future requests for data.

Is wrong… the pool is only on client side

As I missed the screenshots I will add another example in case I lost you somewhere above J

Connection pooling is pool of connection within instance of application! If you have two instances of same application that creates one connection to SQL  Server you will have 2 conenction to the server.

If these 2 instances created 20 connections each (with same connection string within the instance) there will be 2 connections on the server because connection pooling.

If these 2 instances created 20 connections each (different connection string each) there will be 2x20=40 connections on the server because connection pooling is done only on same connection string.