April 2006 - Posts

How to get an Identity value with SQL Server 2005

It's usual (actually, I'd say recommended) to have an Identity int column as primary key. I.e., it's a common thing to define a table like this:

CREATE TABLE HumanResources.Employees(

  Id int IDENTITY(1,1) NOT NULL,

  -- Other columns

,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (Id ASC)

)

Now what is an interesting question is how to find out the Id of a newly inserted row; usually you write code like this:

DECLARE @NewId AS int

INSERT INTO HumanResources.Employees

  ( /* column names */)

  VALUES ( /* column values */)

SELECT @NewId = @@Identity

In this way we get the newly generated identity in @NewId . First of all *this code sample is wrong* Why? Because @@Identity returns the last identity value inserted in *any table or session of the database*, and so in a high (or even medium) concurrency environment @NewId will give you unexpected values. For this reason, it's a far better idea to use such functions as Scope_Identity() or Ident_Current().

Interestingly, SQL Server 2005 introduces the OUTPUT clause that allows us to find out the values (original or new) for columns manipulated by any INSERT, UPDATE or DELETE statement. With OUTPUT's help, the previous example can be rewritten like this:

DECLARE @InsertedRows AS TABLE (Id int)

DECLARE @NewId AS INT

INSERT INTO HumanResources.Employees

  ( /* column names */)

OUTPUT Inserted.Id INTO @InsertedRows

  VALUES (/* column values */)

SELECT @NewId = Id FROM @InsertedRows

Note the @InsertedRows temporary table declaration and the use of the OUTPUT clause right before the VALUES clause inside the INSERT statement. This code is not only concurrency-safe, but it allows us to get the values of other columns that could have been generated (e.g. by DEFAULTs or TRIGGERs). Moreover, as I already mentioned, it can also be used with UPDATEs and DELETEs which makes it useful for those who like to have record-level logs (even though I prefer to use business level logs, but that's another story...)

Posted by Edgar Sánchez with 17 comment(s)
Filed under:

The Developer Abstraction Layer

You can always count on Joel Spolsky for some good reading. Now Joel blogs about why it's so hard for great developers to start their own company, and it really resonates to me as we started Logic Studio 3 years ago and we are still in the way of going from developers (I just don't want let go) to software company owners (it's so hard to learn).

NB. Did you notice how subtly I put myself in the "great developers" camp?

Posted by Edgar Sánchez with no comments

TechEd 2006: I'll be there

Last thing on Thursday, April 13th, the Regional Director Program sent  me an invitation for a free pass to TechEd 2006. Well, almost free: in exchange of doing some community activities actually, but as I love doing that kind of stuff who cares. The thing is that by then I was on a short holiday trip (Viernes Santo is a holiday down here) so I didn't get the e-mail until Monday, April 17th. The mail urged me to register since TechEd 2006 was selling out fast, so I rushed to the registration site and here I am ready to get to Boston (now I only need to collect the money for the plane tickets and the hotel, but hey! One thing at a time.)
Posted by Edgar Sánchez with 1 comment(s)
Filed under:

Atlas Toolkit released

The first official release of the Atlas Toolkit is ready! Well, what nowadays is called a release, what with the release-early-get-feedback-CTP-like-hell-release-again cycle. But I won't rant at the moment because these are actually good news, so go download Atlas right now (before another early release replaces it ;-)

Posted by Edgar Sánchez with no comments

Faster databinding

My friend Harley Jácome has started blogging (hey, ¡está en español!). There he describes a neat Windows Forms databinding trick that is probably very old but that I wasn't aware of. Which one do you think is faster?

CitiesComboBox.DataSource = CitiesDataTable;
CitiesComboBox.ValueMember = "Id";
CitiesComboBox.DisplayMember = "Name";

Or

CitiesComboBox.ValueMember = "Id";
CitiesComboBox.DisplayMember = "Name";
CitiesComboBox.DataSource = CitiesDataTable;

It turns out that the later is faster. According to Harley's tests, more than twice as fast. The reason? When the combobox has a null datasource, changing ValueMember or DisplayMember do basically nothing but when it points to something, the same changes repopulate the combobox. Hence, the second option is more efficient. The funny thing is that, out of trying to be hierarchically organized, I always set the datasource first, silly me.

Do that come with an E1^n! line?

In an announcement on the future of SQL Server and related technologies, Paul Flessner mentions that:

Today the cost of storage continues to be on an amazing trajectory; one could reasonably expect that the cost of a terabyte will be reduced from about $1000 today to $100 in 2007.  Historical trends imply that in 20 years or less we will be able to store literally everything digitally, and the petabyte will be a standard measure of personal storage.

[The bolds are mine]

Terabytes? Petabytes?! I don't even know how many 0's they've got, and supposedly my crappy portable will have several of those... What will we be using so much space for? What kind of systems will be possible or will leverage so much room? What kind of bandwidth will I need for filling my hard drive at 95% (I can't stop downloading until I get to that level.)

By the way, for those of you that prefer it in Español...

He cambiado de casa a la versión en español de este blog .NET a 2.860 metros de altura. Si quieren leer casi lo mismo que escribo aquí, solo que en un idioma bonancible, pueden visitarme en http://ecuador.latindevelopers.net/blogs/edgarsanchez. Por cierto, gracias a los buenos amigos de http://www.communityserver.org por su chévere producto.

Posted by Edgar Sánchez with 3 comment(s)
Filed under:

Slides for the JSP - ASP.NET Architecture Comparison Webcast

As promised I just uploaded the demo files for the JSP - ASP.NET Architecture Comparison webcast. You can now download the JSP demo project (made with Eclipse) and the ASP.NET demo project (made with Visual Web Developer). Both projects connect to the Northwind database running on SQL Server Express, you will most probably have to modify the connection strings (in the Java data access classes and the ASP.NET web.config file).  Do let me know if you have any problems.

Isolated Storage

Say you want that every user save her preferences, last actions, last login time, etc. in her PC, where would you put that information? In isolated storage of course, this is like a .NET managed folder where you can create a number of files, there is one store per user/application/assembly, and the use couldn't be simpler:

IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForDomain();

IsolatedStorageFileStream archivo = new IsolatedStorageFileStream("personal.txt", System.IO.FileMode.Create, store);

StreamWriter writer = new StreamWriter(archivo);

writer.WriteLine("Some info valid only for this user and app domain");

writer.Close();

Later (or the next day) the user can retrieve the information like so:

IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForDomain();

IsolatedStorageFileStream archivo = new IsolatedStorageFileStream("personal.txt", System.IO.FileMode.Open, store);

StreamReader reader = new StreamReader(archivo);

string linea = reader.ReadLine();

reader.Close();

this.label1.Text = linea;

A user needs fewer rights for writing/reading isolated storage than for the standard filesystem which is a good security thing. On the other hand, you shouldn't put any sensitive information there, because is not hard to find it. Happy isolated storaging!

Posted by Edgar Sánchez with no comments
Filed under:

A revisit to a tiny C# shortcut

My blog on string.IsNullOrEmpty() caused quite a stir (in my micro-scale, anyway), I got a number of comments, most of them interesting, and I feel happy of *not* being the only guy interested in silly small details. The feedback forced me to polish my benchmark, first of all James Bogosian is right: the performance differences are really small, I had to do 20'000.000 and 30'000.000 comparisons to see some stable differences. I also introduced the name.Length > 0 comparison out of several suggestions; after some cleaning, the results for the not null and not empty string tests were these:

.Length > 0 80 ms
string.IsNullOrEmpty() 110 ms
!= "" 270 ms
!= string.Empty 440 ms

And, surprise Edgar, name.Length > 0 is the best so Ramon Smits and other guys get a point here. The other  unexpected result is that comparing to string.Empty is a real bummer, so Darren Kopp suggestion is not really good (but he also supported the use of the Length property, so you're just OK, Darren). Someone suggested that comparing with "" is bad because a lot of string objects are generated, Ildasm shows that a ldstr "" instruction is generated just before the comparison, and although I'm far from an IL expert, I suspect no extra strings are being instantiated. Personally, I will stick to string.IsNullOrEmpty() because, as James pointed out, it's not only a matter of performance but of readability (or perhaps I am just rationalizing my bad habits ;-) ). You can download the test class from here, try it, mangle it, feedback me.

Posted by Edgar Sánchez with 14 comment(s)
Filed under:

Comparing ASP.NET and JSP architecture and components

Next Tuesday 4th I'll be doing a webcast by the title of Comparing ASP.NET and Java ServerPages (JSP) Architecture and Components, as usual I'll try to minimize the quantity of slides and show as many code as possible. The webcast will be at 9:00 A.M. PST (that is, 12h00 Panamá-Bogotá-Quito-Lima time). If you would like to join us you can register here.
More Posts