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.

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.

Generate Serial No in Gridview

Here is the Code to Generate Serial No in Gridview :

<asp:GridView  runat=”server” id=”gvEmp”>
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
—
—
—
</asp:GridView>

Generate a unique number in Sql Server 2005

We often find ourselves in the need of generating unique numbers in our database applications.

Let us quickly take a tour of how to do that using Sql server 2005.

SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :

Generate GUID’s :

UniqueIndentifiers are also knows as GUID’s. To generate a GUID use :

SELECT NEWID() as GuidNo

generates FBF2D8E9-F8BE-4F0B-9D49-7CA7C2E3F22C on my machine

Generate only digits :

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo

generates 427357674589 on my machine

Generate fixed digit unique numbers :

At times, we may also need to generate fixed digit numbers. You can do that in the following manner :

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)

generates 470370453006 on my machine

Generating Random Number and String in C#

The Random class defined in the .NET Framework class library provides functionality to generate random numbers.

The Random class constructors have two overloaded forms. It takes either no value or it takes a seed value.

The Random class has three public methods – Next, NextBytes, and NextDouble. The Next method returns a random number, NextBytes returns an array of bytes filled with random numbers, and NextDouble returns a random number between 0.0 and 1.0. The Next method has three overloaded forms and allows you to set the minimum and maximum range of the random number.

The following code returns a random number:

int num = random.Next();

The following code returns a random number less than 1000.

int num = random.Next(1000);

The following code returns a random number between min and max:

private int RandomNumber(int min, int max)
{
Random random = new Random();
return random.Next(min, max);
}

At some point, you may also want to generate random strings. I have created a method, which takes first parameter as the size of string and second parameter if you want the string to be lowercase.

/// <summary>
/// Generates a random string with the given length
/// </summary>
/// <param name=”size”>Size of the string</param>
/// <param name=”lowerCase”>If true, generate lowercase string</param>
/// <returns>Random string</returns>
private string RandomString(int size, bool lowerCase)
{
StringBuilder builder = new StringBuilder();
Random random = new Random();
char ch ;
for(int i=0; i<size; i++)
{
ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65))) ;
builder.Append(ch);
}
if(lowerCase)
return builder.ToString().ToLower();
return builder.ToString();
}

You can even combine the two methods – RandomNumber and RandomString to generate a combination of random string and numbers. For example, the following code generates a password of length 10 with first 4 letters lowercase, next 4 letters numbers, and last 2 letters as uppercase.

public string GetPassword()
{
StringBuilder builder = new StringBuilder();
builder.Append(RandomString(4, true));
builder.Append(RandomNumber(1000, 9999));
builder.Append(RandomString(2, false));
return builder.ToString();
}

What is AJAX?

AJAX stands for Asynchronous JavaScript and XML. AJAX is not a new technology; it is a new way of combining existing technologies. What we do in AJAX is create an asynchronous request to the web server using client-side JavaScript and an XmlHttpRequest object, and map a function to be executed when the response is received. An asynchronous request means that the browser does not need to wait for the response after sending the request to the server. What we gain using AJAX is a responsive user interface, and we get this by sending a request to the web server for a small amount of information, as many times as we want, without sending the complete information on the form. In AJAX, the request is for data, not for a GUI element, so an AJAX request can be handled by an ASP.NET page without any HTML content, a custom HTTP module, or a web service. The core component in AJAX is the XMLHTTPRequest object. As many web developers are not familiar with this object, we will discuss it in detail in the next section. Following are the advantages and disadvantages of AJAX.

Advantages:

1. The use of AJAX the increases the richness and responsiveness of the web page interface.
2. It reduces the network traffic and CPU usage on web server. This is because there will be no post back to the server that will render a complete HTML page. For example, if you are displaying a lot of data in a web page, the HTML page size may be about 100 kilobytes. This big string has to be created on the server and then sent back to the client.

Disadvantages:

1.
The use of AJAX requires users to have JavaScript enabled on their browser. Because of this, an AJAX website should provide a non-AJAX alternative for users without JavaScript enabled.
2.
AJAX breaks the normal browsers’ Back button behavior. When a page is updated dynamically, returning to the previous state may not be possible, since browsers typically record only complete page requests in their history lists.
3.
As not all browsers are complying with W3C standards, AJAX applications have to be tested rigorously to deal with the quirks of different browsers.

Send Email in ASP.NET 2.0

========================================
Method 1
========================================
This is a simple article which tells you how you can embed images in emails.This can be very useful when you want to send emails from your website.A common scenario would be when a new user registers with your site and you want to send a welcome email with your site’s logo on top.

I searched the net when I faced this issue. I am grateful to many authors from where I could make a  start. I have used their code while writing this.Due credit goes to them all. The intention is to make the code available for the others too.  To use this all you have to so is copy the code and paste it in any button click event.

This may only work with ASP.NET 2.0 & C#. I have tested it too.It will not run in ASP.NET 1.1.

Namespaces Used

using System.Net.Mail;
using System.Net.Mime;

CODE

// send mail to the new user who has registered.
protected void yourButton_Click(object sender, EventArgs e)
{

string strMailContent = “Welcome new user”;
string fromAddress = “yourname@yoursite.com”;
string toAddress = “newuser@hisdomain.com”;
string contentId  = “image1″;

// my logo is placed in images folder
string path = Server.MapPath(@”images/Logo.jpg”);

MailMessage mailMessage = new MailMessage( fromAddress, toAddress );
mailMessage.Bcc.Add(“inkrajesh@hotmail.com”); // put your id here
mailMessage.Subject = “Welcome new User”;

LinkedResource logo = new LinkedResource(path);
logo.ContentId = “companylogo”;

// done HTML formatting in the next line to display my logo

AlternateView av1 = AlternateView.CreateAlternateViewFromString
(“<html><body><img src=cid:companylogo/><br></body></html>” +
strMailContent, null, MediaTypeNames.Text.Html);

av1.LinkedResources.Add(logo);

mailMessage.AlternateViews.Add(av1);
mailMessage.IsBodyHtml = true;

//use this if you are in the development server

SmtpClient mailSender = new SmtpClient(“localhost”);

mailSender.Send(mailMessage);

}

========================================
Method 2 :
========================================
To show an image inside the body of the email without link to external site is necessary to add the attachment in the header of message and call it from the HTML section of the body. Using the MailMessage and the SmtpClient classes is not enough set the Inline property to True to show the image:

// creating the attachment
System.Net.Mail.Attachment inline = new System.Net.Mail.Attachment(@”c:\\test.png”);
inline.ContentDisposition.Inline = true;

// sending the message
MailMessage email = new MailMessage();
// set the information of the message (subject, body ecc…)

// send the message
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(“localhost”);
smtp.Send(email);
email.Dispose();

In this way, the sent message misses the right Content-Type section in the header (to have an Inline attachment is necessary the “multipart/related” Content-Type).To resolve this situation is possible to by-pass the SmtpClient and use one custom class for SMTP client. This class provides to connect/communicate with the SMTP server and to add the right Content-Type to the message.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Net.Mail;
using System.Net.Mime;

namespace devSmtp {

class Program {

static void Main(string[] args)
{

// This example show you how to send one email message with an INLINE attachment.
// You can use this example also without the support of CDO or other type of SmtpClient.

// creating the email message
MailMessage email = new MailMessage(“test@yourdomain.something”, “test@yourdomain.something”);

// information
email.Subject = “INLINE attachment TEST”;
email.IsBodyHtml = true;
email.Body = “<div style=\”font-family:Arial\”>This is an INLINE attachment:<br /><br /><img

src=\”@@IMAGE@@\” alt=\”\”><br /><br />Thanks for downloading this example.</div>”;

// generate the contentID string using the datetime

string contentID = Path.GetFileName(attachmentPath).Replace(“.”, “”) + “@zofm”;

// create the INLINE attachment

string attachmentPath = Environment.CurrentDirectory + @”\test.png”;
Attachment inline = new Attachment(attachmentPath);
inline.ContentDisposition.Inline = true;
inline.ContentDisposition.DispositionType = DispositionTypeNames.Inline;
inline.ContentId = contentID;
inline.ContentType.MediaType = “image/png”;
inline.ContentType.Name = Path.GetFileName(attachmentPath);
email.Attachments.Add(inline);

// replace the tag with the correct content ID

email.Body = email.Body.Replace(“@@IMAGE@@”, “cid:” + contentID);

// sending the email with the SmtpDirect class
//(not using the System.Net.Mail.SmtpClient class)

SmtpDirect smtp = new SmtpDirect(“localhost”);
smtp.Send(email);

email.Dispose();
}

}

}

SessionWrapper Class in C#

ASP.NET provides mechanisms for storing information for a single user session or across multiple sessions.This is done using the HttpSessionState and HttpApplicationState classes.The Page class has Application and Session attributes to provide access to current objects.

The simple way to access them is as following:

if (Session["FirstName"] == null)

{

    LabelFirstName.Text = “FirstName”;

}

else

{

    LabelFirstName.Text = (string)Session["FirstName"];

}

if (Session["LastName"] == null)

{

    LabelLastName.Text = “LastName”;

}

else

{

    LabelLastName.Text = (string)Session["LastName"];

}

This method has several drawbacks:

•Type Safety: Session contains objects, so you need to cast.

•Null reference: You must check, it might be null

•Variable name: don’t use hard coded strings, beware of typos…

So Lets see the wrapper object and how it solves those issues:

public static class SessionWrapper

{

    private static T GetFromSession<T>(string key)

    {

        object obj = HttpContext.Current.Session[key];

        if (obj == null)

        {

            return default(T);

        }

        return (T)obj;

    }

    private static void SetInSession<T>(string key, T value)

    {

        if (value == null)

        {

            HttpContext.Current.Session.Remove(key);

        }

        else

        {

            HttpContext.Current.Session[key] = value;

        }

    }

    private static T GetFromApplication<T>(string key)

    {

        return (T)HttpContext.Current.Application[key];

    }

    private static void SetInApplication<T>(string key, T value)

    {

        if (value == null)

        {

            HttpContext.Current.Application.Remove(key);

        }

        else

        {

            HttpContext.Current.Application[key] = value;

        }

    }

    public static string FirstName

    {

        get { return GetFromSession<string>(“FirstName”); }

        set { SetInSession<string>(“FirstName”, value); }

    }

    public static string LastName

    {

        get { return GetFromSession<string>(“LastName”); }

        set { SetInSession<string>(“LastName”, value); }

    }

    public static User User

    {

        get { return GetFromSession<User>(“User”); }

        set { SetInSession<User>(“User”, value); }

    }

}

It contains a few generic private functions to read and write objects from/to the Session or the Application objects, and public methods to be used from the code to access required properties. Note that the use of HttpContext.Current requires a valid request, or Current might be null!

This wrapper provides a safe typed access from all over the application, one place to define all objects that might be stored in the session. If you want to move objects from the Session to other storing mechanism this would be solved in the wrapper only.

The usage is also much easier:

LabelFirstName.Text = SessionWrapper.FirstName;

LabelLastName.Text = SessionWrapper.LastName;

Note that you can add any initialization code or default values in the wrapper:

public static string FirstName

{

    get

    {

        string firstName = GetFromSession<string>(“FirstName”);

        if( string.IsNullOrEmpty(firstName))

        {

            firstName = “FirstName”;

            SetInSession<string>(“FirstName”, firstName);

        }

        return firstName;

    }

    set { SetInSession<string>(“FirstName”, value); }

}

Change Row Color of Gridview in ASP.NET 2.0

Method – 1 : DataBound Event

This article is used to Change Color of Gridview datarow when data is binding.

When Date in Gridview datarow’s field is today’s date.

Note : lblPostedOn is label which boundfield which is of date type.

protected void gvQuery_DataBound(object sender, EventArgs e)
{
Label sDate = null;

foreach (GridViewRow r in gvQuery.Rows)
{
sDate = (Label)r.FindControl(“lblPostedOn”);
System.DateTime dt = Convert.ToDateTime(sDate.Text);
if (dt.Date == System.DateTime.Today.Date)
r.BackColor = System.Drawing.Color.Gray;
}

}

Method – 2 : RowCreated Event

Here’s the code for that :

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
e.Row.Attributes.Add(“onMouseOver”, “this.style.background=’#eeff00′”);
e.Row.Attributes.Add(“onMouseOut”, “this.style.background=’#ffffff’”);
}