Monthly Archives: October 2008

How to retrieve the identity value when inserting a record into a Microsoft SQL Server table

A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.

Why @@IDENTITY and IDENT_CURRENT should not be used

Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.

Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.

How and why to use SCOPE_IDENTITY

Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.

CREATE PROCEDURE [dbo].[InsertCases]
    @CaseID int output,
    @CaseName nvarchar(255),
    @CaseDescription ntext 
AS
   SET NOCOUNT ON
  INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription])
          VALUES (@CaseName,@CaseDescription)

  SET @CaseID=SCOPE_IDENTITY()
  SET NOCOUNT OFF

The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to arameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.

C#

 Command.Parameters.Add(new System.Data.SqlClient.SqlParameter(“@CaseID”,System.Data.SqlDbType.Int));
 Command.ExecuteNonQuery();
 CaseID = System.Convert.ToInt32(Command.Parameters["@CaseID"].Value);

Conclusion

By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.

About these ads

How to truncate text in label control

C#:

Text=’<%# Eval(“Longtitle”)==DBNull.Value ? “”

:Eval(“Longtitle”).ToString().Substring(0,Math.Min(400,Eval(“Longtitle”).

ToString().Length))+”….” %>’>

A sample for VB.NET(use IIF function):

Text=’<%# IIF(CONVERT.ToString(Eval(“Longtitle”))=”",

“”,Eval(“Longtitle”).ToString().Substring(0,Math.Min(400,Eval(“Longtitle”

).ToString().Length))& “….”) %>’

Generating Unique Keys in .Net

Introduction :

I have few applications where I need to create some unique IDs. GUIDs are great as they give Globally Unique identifier but they are big. I mean if you want to issue unique number in your application which you want to give as Booking Number or any reference number then GUIDs is obviously not a solution. Therefore, I need some simple Id which is unique too. For example when I send a request to my Credit Card Processor there’s an ID that correlates my invoice with the transaction at the provider. A GUID isn’t what I’d want here.

But one relatively simple solution is to create sequential ID which will be of appropriate size as well as it will guarantee uniqueness. Very Good and Simple solution!!! Isn’t it !! My answer would be NO !! Because it is security threat for your website. In case of Web Application where you can Retrieve your Order or Booking Details by just giving Order Reference number, you can easily BruteForce Sequential Reference numbers to retrieve records.

In this article I will discuss some of techniques.

Using DateTime and HashCode:

Using DateTime for generating unique keys is very common practice. I have remixed this approach by inducing HashCode in it also. Like

DateTime.Now.ToString().GetHashCode().ToString(“x”);

It will give you some key like 496bffe0. At the first glance it seems to be satisfied Unique key as its using current time and hashing to generate key but GetHashCode() doesn’t procduce unique code everytime. Althought Microsoft is using Double Hashing algorithms with N Number of collision resolving double hash function but during my experimentation I found lot of collisions.

Using GUIDs and HashCode:

So then I tried

Guid.NewGuid().ToString().GetHashCode().ToString(“x”);

It gives key something like 649cf2e3.Somehow I don’t think that this string representation at least is unique… 38 characters represented as 8? Ok 32 bits, but still it’s 8 digits and characters limited to hex values and yes my doubt got right as I wrote program which generated 100,000 keys and checked it for collisions and found several keys duplicated.

Using RNGCryptoServiceProvider and Character Masking :

The .net Framwork provides RNGCryptoServiceProvider class which Implements a cryptographic Random
Number Generator (RNG) using the implementation provided by the cryptographic service provider (CSP). This class is usually used to generate random numbers. Although I can use this class to generate unique number in some sense but it is also not collision less. Moreover while generating key we can make key more complicated by making it as alpha numeric rather than numeric only. So, I used this class along with some character masking to generate unique key of fixed length.

Below is code sample:

private string GetUniqueKey()
{
int maxSize  = 8 ;
int minSize = 5 ;
char[] chars = new char[62];
string a;
a = “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890″;
chars = a.ToCharArray();
int size  = maxSize ;
byte[] data = new byte[1];
RNGCryptoServiceProvider  crypto = new RNGCryptoServiceProvider();
crypto.GetNonZeroBytes(data) ;
size =  maxSize ;
data = new byte[size];
crypto.GetNonZeroBytes(data);
StringBuilder result = new StringBuilder(size) ;
foreach(byte b in data )
{
result.Append(chars[b % (chars.Length - 1)]);
}
return result.ToString();
}

Analysis shows that RNGCrypto with Character Masking is best method to generate Unique keys.