Search content within the blog

Friday, May 29, 2009

Refactoring code in c#

Reflectoring into SqlConnection shows it closes open connections in Dispose(). So, here's the before and after code. I think it shows good examples on why the using statement exists, and when to avoid (hide) superfluous try/catches.

BEFORE

private void RunScriptOnDB(string filename,string DB)
{
SqlConnection sqlcon = new SqlConnection();
sqlcon.ConnectionString ="Persist Security Info=False;Integrated Security=SSPI;Initial Catalog="+DB+";Data Source=(local);";
SqlCommand com = new SqlCommand();
com.Connection = sqlcon;
try
{
StreamReader sr = Utility.GetStreamOfFile(filename);
com.CommandText = sr.ReadToEnd();
sr.Close();
}
catch(FileNotFoundException fileex)
{
msg.Text = fileex.Message;
return;
}
try
{
sqlcon.Open();
com.ExecuteNonQuery();
msg.Text = "Successful";
}
catch( SqlException sqlex)
{
msg.Text = sqlex.Message;
}
finally
{
if(closingMethod.SelectedValue == "c") //SDH: He's trying different closing methods based on a Radio Button, this won't be needed in a refactor
{
sqlcon.Close();
}
else if(closingMethod.SelectedValue == "d")
{
sqlcon.Dispose();
}
else
{
sqlcon.Close();
sqlcon.Dispose();
}
}
}

AFTER


private void RunScriptOnDB(string filename, string database)
{
string commandText = String.Empty;
try
{
using (StreamReader sr = Utility.GetStreamOfFile(filename))
{
commandText = sr.ReadToEnd();
}
}
catch (FileNotFoundException fileEx)
{
msg.Text = fileEx.Message;
return;
}
using (SqlConnection connection = new SqlConnection(String.Format("Persist Security Info=False;Integrated Security=SSPI;Initial Catalog={0};Data Source=(local);",database))
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
try
{
connection.Open();
command.ExecuteNonQuery();
msg.Text = "Successful";
}
catch (SqlException sqlEx)
{
msg.Text = sqlEx.Message;
}
}
}
}

Thursday, May 28, 2009

Using Distinct in LINQ

The code is self explanatory.....

List ages = new List { 21, 46, 46, 55, 17, 21, 55, 55 };

IEnumerable distinctAges = ages.Distinct();

Console.WriteLine("Distinct ages:");

foreach (int age in distinctAges)
{
Console.WriteLine(age);
}

/*
This code produces the following output:

Distinct ages:
21
46
55
17
*/

Using Count in LINQ

The code is self explanatory....
string[] fruits = { "apple", "banana", "mango", "orange", "passionfruit", "grape" };

try
{
int numberOfFruits = fruits.Count();
Console.WriteLine(
"There are {0} fruits in the collection.",
numberOfFruits);

}
catch (OverflowException)
{
Console.WriteLine("The count is too large to store as an Int32.");
Console.WriteLine("Try using the LongCount() method instead.");
}

// This code produces the following output:
//
// There are 6 fruits in the collection.

Using Cast in LINQ

Here is an example showing how to use cast function in LINQ using method syntax

System.Collections.ArrayList fruits = new System.Collections.ArrayList();
fruits.Add("apple");
fruits.Add("mango");

IEnumerable<string> query =
fruits.Cast<string>().Select(fruit => fruit);

foreach (string fruit in query)
{
Console.WriteLine(fruit);
}

// This code produces the following output:
//
// apple
// mango

Using query expression and method based syntax in LINQ

The following example shows both the query expression syntax to work with LINQ as well as method based.The source is an string which is split into an array and queried using group by clause.

The code is self explanatory.....

string sentence = "the quick brown fox jumps over the lazy dog";
// Split the string into individual words to create a collection.
string[] words = sentence.Split(' ');

// Using query expression syntax.
var query = from word in words
group word.ToUpper() by word.Length into gr
orderby gr.Key
select new { Length = gr.Key, Words = gr };

// Using method-based query syntax.
var query2 = words.
GroupBy(w => w.Length, w => w.ToUpper()).
Select(g => new { Length = g.Key, Words = g }).
OrderBy(o => o.Length);

foreach (var obj in query)
{
Console.WriteLine("Words of length {0}:", obj.Length);
foreach (string word in obj.Words)
Console.WriteLine(word);
}

// This code example produces the following output:
//
// Words of length 3:
// THE
// FOX
// THE
// DOG
// Words of length 4:
// OVER
// LAZY
// Words of length 5:
// QUICK
// BROWN
// JUMPS

Transforming in-Memory Objects into XML using LINQ

LINQ queries make it easy to transform data between in-memory data structures, SQL databases, ADO.NET Datasets and XML streams or documents. The following example transforms objects in an in-memory data structure into XML elements.

class XMLTransform
{
static void Main()
{
// Create the data source by using a collection initializer.
List<Student> students = new List<Student>()
{
new Student {First="Svetlana", Last="Omelchenko", ID=111, Scores = new List<int>{97, 92, 81, 60}},
new Student {First="Claire", Last="O’Donnell", ID=112, Scores = new List<int>{75, 84, 91, 39}},
new Student {First="Sven", Last="Mortensen", ID=113, Scores = new List<int>{88, 94, 65, 91}},
};

// Create the query.
var studentsToXML = new XElement("Root",
from student in students
let x = String.Format("{0},{1},{2},{3}", student.Scores[0],
student.Scores[1], student.Scores[2], student.Scores[3])
select new XElement("student",
new XElement("First", student.First),
new XElement("Last", student.Last),
new XElement("Scores", x)
) // end "student"
); // end "Root"

// Execute the query.
Console.WriteLine(studentsToXML);

// Keep the console open in debug mode.
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}


The code produces the following XML output:
Copy Code

< Root>
<student>
<First>Svetlana</First>
<Last>Omelchenko</Last>
<Scores>97,92,81,60</Scores>
</student>
<student>
<First>Claire</First>
<Last>O'Donnell</Last>
<Scores>75,84,91,39</Scores>
</student>
<student>
<First>Sven</First>
<Last>Mortensen</Last>
<Scores>88,94,65,91</Scores>
</student>
</Root>

Data Transformations using LINQ

Joining Multiple Inputs into One Output Sequence

You can use a LINQ query to create an output sequence that contains elements from more than one input sequence. The following example shows how to combine two in-memory data structures, but the same principles can be applied to combine data from XML or SQL or DataSet sources. Assume the following two class types:
C#
Copy Code

class Student
{
public string First { get; set; }
public string Last {get; set;}
public int ID { get; set; }
public string Street { get; set; }
public string City { get; set; }
public List<int> Scores;
}

class Teacher
{
public string First { get; set; }
public string Last { get; set; }
public int ID { get; set; }
public string City { get; set; }
}

The following example shows the query:
C#
Copy Code

class DataTransformations
{
static void Main()
{
// Create the first data source.
List<Student> students = new List<Student>()
{
new Student {First="Svetlana",
Last="Omelchenko",
ID=111,
Street="123 Main Street",
City="Seattle",
Scores= new List<int> {97, 92, 81, 60}},
new Student {First="Claire",
Last="O’Donnell",
ID=112,
Street="124 Main Street",
City="Redmond",
Scores= new List<int> {75, 84, 91, 39}},
new Student {First="Sven",
Last="Mortensen",
ID=113,
Street="125 Main Street",
City="Lake City",
Scores= new List<int> {88, 94, 65, 91}},
};

// Create the second data source.
List<Teacher> teachers = new List<Teacher>()
{
new Teacher {First="Ann", Last="Beebe", ID=945, City = "Seattle"},
new Teacher {First="Alex", Last="Robinson", ID=956, City = "Redmond"},
new Teacher {First="Michiyo", Last="Sato", ID=972, City = "Tacoma"}
};

// Create the query.
var peopleInSeattle = (from student in students
where student.City == "Seattle"
select student.Last)
.Concat(from teacher in teachers
where teacher.City == "Seattle"
select teacher.Last);

Console.WriteLine("The following students and teachers live in Seattle:");
// Execute the query.
foreach (var person in peopleInSeattle)
{
Console.WriteLine(person);
}

Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}

==================Output=========
/* Output:
The following students and teachers live in Seattle:
Omelchenko
Beebe
*/
==================End of Output===

Creating and Inserting XML data in SQL Server 2005/2008

With the XML data type introduced in SQL Server 2005/2008, storing XML data is quite simple as you no longer need to now store XML in the database as Binary formats.

Let's quickly see how to create a table with the XML data type and store an XML document in that table.


DECLARE @TT TABLE

(

ID int,

Name varchar(30),

Address XML

)



INSERT @TT

SELECT 1, 'Jason', CAST
('<Address Street1="342A NW AXE STREET" PIN="544333"/>' as
XML) UNION ALL

SELECT 2, 'Brooke', CAST
('<Address Street1="71H BRISBON" PIN="565533"/>' as XML)
UNION ALL

SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783"/>' as
XML)



SELECT * FROM @TT

=============Output=============

================================


As shown in the query above, we created the Address column with the XML data type and inserted XML data using CAST function. The CAST function also ensures that the XML document is well formed. If the XML was not well formed, as in this line shown below,


SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783">' as XML)



then an XML Parsing error would be raised - XML parsing: line 1, character 51, unexpected end of input

You can even add XML Schema definitions to SQL Server and use it to validate XML documents. We will see this feature in one of the forthcoming blog posts.

Wednesday, May 27, 2009

Introduction to LINQ

The following is an console application that explains the basics of writing queries in LINQ.

Add a new console application to your solution and copy paste the code in a class and run the project. Each section describes different clauses we can use while writing queries. Also it shows the following

1.Use query syntax.
2.Use method syntax.
3.Use a combination of query syntax and method syntax.

The code is self explanatory......

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
List<student> students = new List<student>
{
new student {First="Svetlana", Last="Omelchenko", ID=111, Scores= new List<int> {97, 92, 81, 60}},
new student {First="Claire", Last="O’Donnell", ID=112, Scores= new List<int> {75, 84, 91, 39}},
new student {First="Sven", Last="Mortensen", ID=113, Scores= new List<int> {88, 94, 65, 91}},
new student {First="Cesar", Last="Garcia", ID=114, Scores= new List<int> {97, 89, 85, 82}},
new student {First="Debra", Last="Garcia", ID=115, Scores= new List<int> {35, 72, 91, 70}},
new student {First="Fadi", Last="Fakhouri", ID=116, Scores= new List<int> {99, 86, 90, 94}},
new student {First="Hanying", Last="Feng", ID=117, Scores= new List<int> {93, 92, 80, 87}},
new student {First="Hugo", Last="Garcia", ID=118, Scores= new List<int> {92, 90, 83, 78}},
new student {First="Lance", Last="Tucker", ID=119, Scores= new List<int> {68, 79, 88, 92}},
new student {First="Terry", Last="Adams", ID=120, Scores= new List<int> {99, 82, 81, 79}},
new student {First="Eugene", Last="Zabokritski", ID=121, Scores= new List<int> {96, 85, 91, 60}},
new student {First="Michael", Last="Tucker", ID=122, Scores= new List<int> {94, 92, 91, 91} }

};
IEnumerable<student> studentQuery = from student in students
where student.Scores[0] > 90 && (student.Scores[1] > 0 && student.Scores[1] < 90)
orderby student.Last descending
select student;

foreach (student localstudent in studentQuery)
{
Console.WriteLine("First name : {0},lastname :{1},score in paper1:{2}", localstudent.First, localstudent.Last, localstudent.Scores[0]);

}

Console.WriteLine("use of group by");
var studentQuery2 = from objstudents in students
orderby objstudents.Last descending
group objstudents by objstudents.Last[0];


foreach(var localstudentgroup in studentQuery2)
{
Console.WriteLine("Reading from group with key " + localstudentgroup.Key);
{
foreach (student localstudent in localstudentgroup)
{
Console.WriteLine("First name : {0},lastname :{1},score in paper1:{2}", localstudent.First, localstudent.Last, localstudent.Scores[0]);
}

}


}


Console.WriteLine("use of group by with into clause");
var studentQuery3 = from objstudents in students
group objstudents by objstudents.Last[0] into studentGroup
orderby studentGroup.Key ascending
select studentGroup;



foreach (var localstudentgroup in studentQuery3)
{
Console.WriteLine("Reading from group with key " + localstudentgroup.Key);
{
foreach (student localstudent in localstudentgroup)
{
Console.WriteLine("First name : {0},lastname :{1},score in paper1:{2}", localstudent.First, localstudent.Last, localstudent.Scores[0]);
}

}


}

Console.WriteLine("use of let keyword");
var studentQuey4 = from objStudents in students
let totalScore = objStudents.Scores[0] + objStudents.Scores[1] + objStudents.Scores[2] + objStudents.Scores[3]
where totalScore / 4 < objStudents.Scores[0]
select objStudents.First + objStudents.Last;

foreach (string s in studentQuey4)
{
Console.WriteLine(s);
}


Console.WriteLine("use of method syntax");
var studentQuery5 = from objStudent in students
let totalScore = objStudent.Scores[0]
select totalScore;

double averageScoreAtZero = studentQuery5.Average();
Console.WriteLine("Average: " + averageScoreAtZero);


Console.WriteLine("To transform or project in the select clause");
IEnumerable<string> studentQuery6 = from objStudent in students
where objStudent.Last == "Garcia"
select objStudent.First;

foreach (string localStudent in studentQuery6)
{
Console.WriteLine("First name: " + localStudent);
}


Console.WriteLine("Use of annonymous type");
var studentQuery7 = from localStudent in students
let x = localStudent.Scores[0] + localStudent.Scores[1]
where x > 180 && x < 185
select new { id = localStudent.ID, score = x };

foreach (var item in studentQuery7)
{
Console.WriteLine("Id :{0} score {1}", item.id, item.score);
}



Console.WriteLine("Press any key to exit!!!!");
Console.ReadLine();
}
}

public class student
{
public string First { get; set; }
public string Last { get; set; }
public int ID { get; set; }
public List<int> Scores;
}


}

Tuesday, May 26, 2009

Ajax Page Life Cycle Event

Introduction
This article explains the Client Life Cycle of Ajax Script Manager and Update Control. And also it explains the page life cycle when a partial postback happens because of ajax control, and the control over the events as a devoloper.
The Page Life Cycle of Client[Browser]
We all know the normal asp.net page life cycle events. the ajax controls will have more events added to that existing. the events are more related to the client side rather than the server side. the normal page events happens at the server side and these ajax events occur in the client side. i am not going to discuss more about the server page events, we will look the page events of the client side.
Sys.Application Class
The events are related to the Sys.Application Class, which provides a runtime object to take care client events and to manage client components. this object provides access to all his members with out creating instance for it.
• pageInit
• pageLoad
• pageUnload
pageInit
pageInit event occurs only once for the first time when the page is loaded, and for every normal post back. this event will not be called when the postback happens to be partial using update panel ot by any other ajax method. it occurs after the server Page Render event. also after all the script related to the ScriptManager is loaded, but before the object or controls created in the client client, if a we are creating any components on the client side. this is a good place to write our first time intialization code. we can have a hold for this event by adding a function handler to pageInit event. by simply adding the handler our function will be called after the init event. since the page is not loaded, we cannot have refernce to any object or control in the page. we can add any no. of handlers to these events. also we can remove existing handlers, the syntax looks like this..
Sys.Application.add_init(MyInit);
//Sys.Application.remove_init(MyInit);

function MyInit()
{
alert("MyInit added to page init");
}

pageLoad
pageLoad event occurs after client pageInit event. after pageInit event all the objects or controls are created after that pageload will be called. it occurs every time the page is loaded for both normal and partial renders. we can refer any object here in the event, after this event the page is servered to the user. similarly as pageInit event, pageLoad event will also accept handlers to be added after this event. the syntax is almost similar, looks like this..
Sys.Application.add_load(MyLoad);
//Sys.Application.remove_load(MyLoad);

function MyLoad()
{
alert("MyLoad added to page load");
}

pageUnload
pageUnload event occurs only once when page is redirected to another page, or when the browser window is closed. the redirection has to happen in normal postback or using Response.Redirect method, if we try to redirect using Server.Transfer, where the client will not be aware of the page redirection, it throws an client exception named PageRequestManagerParserErrorException. this event is not linked with server Page Unload event, which occurs after Page Render in server side, has nothing to do with client side,by the time all controls will be loaded into the browser[client]. pageUnload event will accept handlers to be added after this event. the syntax looks like this..
Sys.Application.add_unload(MyUnload);
//Sys.Application.remove_unload(MyUnload);

function MyUnload()
{
alert("MyUnload added to page unload");
}

Additional Information
The event handlers and other code should be written is a separate js file and that should be specified under scripts section of ScriptManger control, here the js file named "UsingJScript.js" is attached to ScriptManger control as shown below..
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true">
<Scripts>
<asp:ScriptReference Path="UsingJScript.js" />
</Scripts>
</asp:ScriptManager>

Sys.WebForms.PageRequestManager Class
This class is responsible for partial post back. this object will be intiated on its own, to get this object in client side, the page should have a ScriptManager and minimum one UpdatePanel. this object also has certain events which will be involved in the client page life cycle. this full name of the class along with the namespace is Sys.WebForms.PageRequestManager. let us see the events related to this.
• initializeRequest
• beginRequest
• pageLoading
• pageLoaded
• endRequest
initializeRequest
intializeRequest event will be triggered after the control is triggered for the asynchronous partial postback by the user. this prepares the request, which has to be sent to the server to get the response. the partial postback can be aborted or cancelled at this stage. we can add our handlers to this event as any other life cycle event. our handlers will be called after the intialRequest is called. the intializeRequestEventArgs gives more details about the event, we get the reference of this class as a argument of event. the code snippet is shown below..
//This statement should be inside pageInit or pageLoad event.
//adds the handler MyIntializeRequest to the intializeRequest event
Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(MyIntializeRequest);

//removes the handler MyIntializeRequest from the intializeRequest event
//Sys.WebForms.PageRequestManager.getInstance().remove_initializeRequest(MyIntializeRequest);

function MyIntializeRequest(sender,args)
{
alert("My Request is getting initalized");
}

beginRequest
beginRequest is triggered after the intializeRequest, also before the asynchronous postback is processed. this takes care of sending the request to the server. we can add our handlers to this event. the handlers are called first before the request is posted to the server. the beginRequestEventArgs gives more details about the event and other object related to this. normally this event will be use to display a graphic or any message to keep the user interactive, till the request gets processed in the server. after this particular event the server takes charge of processing the request. the code snippet is given below..
//This statement should be inside pageInit or pageLoad event.
//adds the handler MyBeginRequest to the beginRequest event
Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(MyBeginRequest);

//removes the handler MyBeginRequest from the beginRequest event
//Sys.WebForms.PageRequestManager.getInstance().remove_beginRequest(MyBeginRequest);

function MyBeginRequest(sender,args)
{
alert("My Request is ready about to sent to server");
}

pageLoading
pageLoading is first event called after the server process finished, means after page render event of server. when it is called the client is not loaded with the new response and it is in hold of the response object, we can have refernce of it here for any manipulations. also it gives reference to the panels which are going to get modified after this partial postback. if we require any animation or transition to happen before the updated data is placed in the respective position. the pageLoadingEventArgs gives more details about the events and other objects related to this. normally we will do some control manipulation in this part before it is getting loaded in the page. the code snippet is given below..
//This statement should be inside pageInit or pageLoad event.
//adds the handler MyPageLoading to the pageLoading event
Sys.WebForms.PageRequestManager.getInstance().add_pageLoading(MyPageLoading);

//removes the handler MyPageLoading from the pageLoading event
//Sys.WebForms.PageRequestManager.getInstance().remove_pageLoading(MyPageLoading);

function MyPageLoading(sender, args)
{
alert("My page is started loading");
}

pageLoaded
pageLoaded is event triggered after all the contents of the page is updated due to synchronous or asynchronous postback. we can clear the graphic or animation which we intiated in the beginRequest event here. we get the reference of the response and other object and controls related to the postback using the class pageLoadedEventArgs. we can also do a animation or trasition here instead of pageLoading event. the code snippet is below
//This statement should be inside pageInit or pageLoad event.
//adds the handler MyPageLoaded to the pageLoaded event
Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(MyPageLoaded);

//removes the handler MyPageLoaded from the pageLoaded event
//Sys.WebForms.PageRequestManager.getInstance().remove_pageLoaded(MyPageLoaded);

function MyPageLoaded(sender, args)
{
alert("My page is loaded");
}

endRequest
endRequest event will be the last event to be triggered in the client postback cycle. after this the control is given back to the user. this event can be used to notify the status of request to the user. this is event can be used to log errors. as a finally in the try catch block, this event will be called irrespective of the postback is success or failed. the objects and other data related to this can be refered using the endRequestEventArgs class. the code snippet is shown below..
//This statement should be inside pageInit or pageLoad event.
//adds the handler MyEndRequest to the endRequest event
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(MyEndRequest);

//removes the handler MyEndRequest from the endRequest event
Sys.WebForms.PageRequestManager.getInstance().remove_endRequest(MyEndRequest);

function MyEndRequest(sender, args)
{
alert("My Request has end");
}

Monday, May 25, 2009

Using RowNumber to obtain alternate rows based on column value

This was a strange requirement from the client ...she needed the output from the employee table as follows...

empname      gender
Emp1      M
Emp2      F
Emp3      M
Emp4      F

Thanks to subramanyam and vinod who solved this for me....

select name,gender, Row_number() over (order by gender) AS x from tblEmp Where Gender = 'M'
UNION
select name,gender, Row_number() over (order by gender) AS x from tblEmp Where Gender = 'F'
ORDER by name

=========Output=========
Ashwin      M
Payal      F
Vinod      M
Reena      F

Find First and Last Day of the Current Quarter in SQL Server

I was recently working on a requirement where the user wanted a report with data from the First day to the Last Day of the current Quarter

Here's a simple way to find the Find First and Last Day of the current quarter in SQL Server 2005/2008


SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as FirstDayOfQuarter

SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as LastDayOfQuarter



OUTPUT


FirstDayOfQuarter         LastDayOfQuarter

2009-04-01 00:00:00.000         2009-06-30 00:00:00.000

Finding the date last year based on the current week and weekday using SQL Server

Today is the 21st of May, 2009 and it is a Thursday today. Have you wondered what date was Thursday last year in the same week? Here's a very handy query by Hector which displays the last year's date based on the current weekday and current week


SELECT DATEADD(day, (DATEPART(week, GETDATE()) * 7

+ DATEPART(weekday, GETDATE()))

- (DATEPART(week, DATEADD(year, -1, GETDATE())) * 7

+ DATEPART(weekday, DATEADD(year, -1, GETDATE()))), DATEADD(year, -1, GETDATE())) as LastYearDate



RESULT


LastYearDate

2008-05-22 11:14:26.350

Wednesday, May 20, 2009

Using Sounds and Images For Error Notification

So far, we have been displaying simple textual messages for the error notifications that come from the
validation server controls. In most instances, you are going to do just that — display some simple
textual messages to inform end users that they input something into the form that doesn’t pass your
validation rules.
An interesting tip regarding the validation controls is that you are not limited to just text — you can also
use images and sounds for error notifications.
To do this, you use the Text property of any of the validation controls. To use an image for the error, you
can simply place some appropriate HTML as the value of this property.

Using images for error notifications
<asp:RequiredFieldValidator ID="RequiredFieldValidator1"
Runat="server" Text=’<img src="error.gif">’
ControlToValidate="TextBox1"></asp:RequiredFieldValidator>


Using sound for error notifications
<asp:RequiredFieldValidator ID="RequiredFieldValidator1"
Runat="server" Text=’<bgsound src="C:\Windows\Media\tada.wav">’
ControlToValidate="TextBox1" EnableClientScript="False">
</asp:RequiredFieldValidator>

Find the nth highest or lowest salary in sqlserver

To find the highest or lowest salary from the salary table the query is as follows

To find the 3rd Highest salary
SELECT * FROM salary s1 WHERE 3 = (SELECT COUNT(DISTINCT (s2.salary))FROM salary s2
WHERE s2.salary >= s1.salary)

change 3 to whatever value you want or pass it as a parameter


To fin the 2nd Lowestt salary
SELECT * FROM salary s1 WHERE 2 = (SELECT COUNT(DISTINCT (s2.salary))FROM salary s2
WHERE s2.salary <= s1.salary)

change 3 to whatever value you want or pass it as a parameter

Self join in sqlserver

Did you know that you can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table? Let's take a look at an example.

In our database we have the employees table shown below and we want to obtain a list of all employees who live in the same town as your About.com Guide to Databases.

Table: Employees

* VARCHAR first_name
* VARCHAR last_name
* VARCHAR city
* VARCHAR state
* VARCHAR zip PRIMARY KEY

We could use this SQL query:

SELECT last_name, first_name
FROM employees
WHERE zip in
( SELECT zip
FROM employees
WHERE last_name="Chapple"
AND first_name="Mike")


Or we could simplify the query using a nested join, as shown below:

SELECT e1.last_name, e1.first_name
FROM employees e1, employees e2
WHERE e1.zip = e2.zip
AND e2.last_name="Chapple"
AND e2.first_name="Mike"


You’ll undoubtedly find that using self-joins can simplify many SQL queries that make multiple references to the same table. Relational databases that perform query optimization are also capable of providing great performance enhancement for queries written in this way.

Monday, May 18, 2009

AutoGenerate an AlphaNumeric Sequence in SQL Server

Unique Alphanumeric Sequence Number to be generated using the following business rules:
UniqueID = Code (TT) + Current Datetime + ID

DECLARE @TT TABLE

(

ID int, CircuitName varchar(10),

UniqueID AS 'TT' + REPLACE(CONVERT(varchar, GETDATE(),101),'/','')

+ REPLACE(CONVERT(varchar, GETDATE(),108),':','')

+ CAST(ID as varchar(10))

)



INSERT @TT

SELECT 1, 'Circuit 1' UNION ALL

SELECT 2, 'Circuit 2' UNION ALL

SELECT 3, 'Circuit 3' UNION ALL

SELECT 4, 'Circuit 4' UNION ALL

SELECT 5, 'Circuit 5' UNION ALL

SELECT 6, 'Circuit 6' UNION ALL

SELECT 7, 'Circuit 7' UNION ALL

SELECT 8, 'Circuit 8' UNION ALL

SELECT 9, 'Circuit 9' UNION ALL

SELECT 10, 'Circuit 10'



SELECT * FROM @TT



OUTPUT


ID CircuitName UniqueID

1 Circuit 1 TT051220091517481

2 Circuit 2 TT051220091517482

3 Circuit 3 TT051220091517483

4 Circuit 4 TT051220091517484

5 Circuit 5 TT051220091517485

6 Circuit 6 TT051220091517486

7 Circuit 7 TT051220091517487

8 Circuit 8 TT051220091517488

9 Circuit 9 TT051220091517489

10 Circuit 10 TT0512200915174810

Wednesday, May 13, 2009

Hashed passwords with salt and generating random passwords in asp.net

Hashed poasswords are one-way encryption and hence cannot be decrypted.
The salt is used to prevent the password hacking and make passwords in databse more secured.


logic is as follows....

your password + salt (unique for every user) = total password which will be stored in databse as follows...

username      password-hashed      salt
1      3423784safjkshf      hjh##
2      dfjsdkfjdf32432      jkh&&

so while validating passwords logic is as follows.....

if(supplied-password+ salt(in database)==hashedpassword)
valid data
else
invalid data


The encryption algorithm used is SHA1 Algorithm....
Also logic to generate random passwords exists....

The code is self explanatory.....

ASPX Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="HashedPasswords.aspx.cs" Inherits="Encryption_Decryption_HashedPasswords" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
Enter usename:
<asp:TextBox ID="txtUsername" runat="server"></asp:TextBox><br />
Enter password:
<asp:TextBox ID="txtpassword" runat="server"></asp:TextBox>
<br />
                       
    
<asp:Button ID="Button1" runat="server" Text="Store password" OnClick="Button1_Click" /> 
<br />
<br />
Enter usename:
<asp:TextBox ID="txtverifyusername" runat="server"></asp:TextBox><br />
Enter password:
<asp:TextBox ID="txtverifypassword" runat="server"></asp:TextBox>
<br />
                       
    
<asp:Button ID="Button2" runat="server" Text="Verify password" OnClick="Button2_Click" /> 
</div>

<div>
Click the button to generate random passwords to store in database :
<asp:Button ID="Button3" runat="server" Text="Generate passsword" OnClick="Button3_Click" />
</div>
</form>
</body>
</html>

ASPX.CS Code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Encryption_Decryption_HashedPasswords : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string salt=Password.CreateSalt(5);
string hashedPassword = Password.CreatePasswordHash(txtpassword.Text.Trim(), salt);
Response.Write("Salt generated:"+salt+"<br>");
Response.Write("Hashed password:" + hashedPassword + "<br>");
ViewState["salt"] = salt;
ViewState["Hashedpassword"] = hashedPassword;

}
protected void Button2_Click(object sender, EventArgs e)
{

string suppliedpasswordhash = Password.CreatePasswordHash(txtverifypassword.Text.Trim(), ViewState["salt"].ToString());
if(suppliedpasswordhash.Equals(ViewState["Hashedpassword"].ToString()))
{
Response.Write("valid crendentials....");
}
else{
Response.Write("Invalid crendentials....");
txtpassword.Text=txtUsername.Text=string.Empty;
}
}
protected void Button3_Click(object sender, EventArgs e)
{
string randomPassword = Password.CreateRandomPassword(8);
string salt = Password.CreateSalt(5);
string hashedPassword = Password.CreatePasswordHash(randomPassword, salt);
Response.Write("Random password:" + randomPassword + "<br>");
Response.Write("Salt generated:" + salt + "<br>");
Response.Write("Hashed password:" + hashedPassword + "<br>");
}
}


Add a file called password.cs in appcode section...for the above code to work... the code for the file is as follows...

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;

///
/// Summary description for Password
///

public class Password
{
public Password()
{
//
// TODO: Add constructor logic here
//
}
public static string CreateSalt(int size)
{
//Generate a cryptographic random number.
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);

// Return a Base64 string representation of the random number.
return Convert.ToBase64String(buff);
}
public static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "sha1");

return hashedPwd;
}
public static string CreateRandomPassword(int PasswordLength)
{
String _allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ23456789!@#$%^&*()";
Byte[] randomBytes = new Byte[PasswordLength];
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(randomBytes);
char[] chars = new char[PasswordLength];
int allowedCharCount = _allowedChars.Length;

for (int i = 0; i < PasswordLength; i++)
{
chars[i] = _allowedChars[(int)randomBytes[i] % allowedCharCount];
}

return new string(chars);
}
}

Monday, May 11, 2009

Printing in asp.net - part II

In this section we learn how we can pass controls of our choice for printing and also modify the contents before printing.

Add a helper class called PrintHelper.cs in your appcode first. This class is mainly used for printing the contents of our choice.

It is having a mrthod called webcontrol the control which we want to print.In the following example i group all the controls and contents i want to print into a htmltable and pass that table to this class.

The rest of the code is self explanatory....

PrintHelper.cs


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Web.SessionState;

public class PrintHelper
{
public PrintHelper()
{
}

public static void PrintWebControl(Control ctrl)
{
PrintWebControl(ctrl, string.Empty);
}

public static void PrintWebControl(Control ctrl, string Script)
{
StringWriter stringWrite = new StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
if (ctrl is WebControl)
{
Unit w = new Unit(100, UnitType.Percentage); ((WebControl)ctrl).Width = w;
}
Page pg = new Page();
pg.EnableEventValidation = false;
if (Script != string.Empty)
{
pg.ClientScript.RegisterStartupScript(pg.GetType(),"PrintJavaScript", Script);
}
HtmlForm frm = new HtmlForm();
pg.Controls.Add(frm);
frm.Attributes.Add("runat", "server");
frm.Controls.Add(ctrl);
pg.DesignerInitialize();
pg.RenderControl(htmlWrite);
string strHTML = stringWrite.ToString();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Write(strHTML);
HttpContext.Current.Response.Write("<script>window.print();</script>");
HttpContext.Current.Response.Write("<script>window.close();</script>");
HttpContext.Current.Response.End();
HttpContext.Current.Response.Close();

}
}

Default.aspx page code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PrintControls.aspx.cs" Inherits="Print_PrintControls" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
<asp:Panel ID="Panel1" runat="server" Height="161px" Width="714px">
First Name
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br />
<br />
Last Name
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<br />
Age           
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<br />
</asp:Panel>
</div>
<br />
<asp:Button ID="btnPrint" runat="server" OnClick="btnPrint_Click" Text="Print" />
</div>
<div id="divGatherInfo">
Enter the details and click print to print the contents dynamically......
<br />
Username :<asp:TextBox ID="txtUsername" runat="server"></asp:TextBox>
<br />
         Age :<asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
<br />
<asp:Button ID="btnPrintDynamic" runat="server" OnClick="btnPrintDynamic_Click" OnClientClick="return PrintDataDynamic('divDynamicAre')"
Text="Print" />
</div>
</form>
</body>
</html>

Default.aspx.cs page code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Print_PrintControls : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnPrint_Click(object sender, EventArgs e)
{
Session["ctrl"] = Panel1;
ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('../Print.aspx','PrintMe','height=300px,width=300px,scrollbars=1');</script>");
}
protected void btnPrintDynamic_Click(object sender, EventArgs e)
{
HtmlTable tb = new HtmlTable();
tb.Width = "50%";
tb.Align = "center";
tb.Border = 1;
tb.CellPadding = 0;
tb.CellSpacing = 0;

HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell tc = new HtmlTableCell();
tc.Width = "100%";
tc.InnerHtml = "<u><i>The following details were provided...</i><u>";
tr.Cells.Add(tc);
tb.Rows.Add(tr);

tr = new HtmlTableRow();
tc = new HtmlTableCell();
tc.Width = "50%";
tc.InnerText = "Username";
tc.Align = "center";
tr.Cells.Add(tc);

tc = new HtmlTableCell();
tc.Width = "50%";
tc.InnerText = "Password";
tc.Align = "center";
tr.Cells.Add(tc);
tb.Rows.Add(tr);

tr = new HtmlTableRow();
tc = new HtmlTableCell();
tc.Width = "50%";
tc.InnerText = txtUsername.Text.Trim();
tc.Align = "center";
tr.Cells.Add(tc);

tc = new HtmlTableCell();
tc.Width = "50%";
tc.InnerText = txtAge.Text.Trim();
tc.Align = "center";
tr.Cells.Add(tc);
tb.Rows.Add(tr);

Session["ctrl"] = tb;

ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('Print.aspx','PrintMe','height=300px,width=300px,scrollbars=1');</script>");
}
}


Print.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Print.aspx.cs" Inherits="Print" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Print Control</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
</form>
</body>
</html>

<u><i>Print.aspx.cs page code</i></u>

Print.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Print : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Control ctrl = (Control)Session["ctrl"];
PrintHelper.PrintWebControl(ctrl);
}
}

Friday, May 8, 2009

Printing in ASP.NET- Part I

In this part we can see two different ways of printing

1) printing the entire contents of the page.
2) printing only seleced area of the page.

to see the first one in action copy paste the ASPX code and its page behind and press Button1
and to print only required contents press Button2

The code is self explanatory....



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Print1.aspx.cs" Inherits="Print_Print1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>

<script type="text/javascript" language="javascript">
function PrintData()
{
window.print();
return false;
}

function PrintDataSelected(printarea)
{
var localprintarea=document.getElementById("printarea");
if(localprintarea!=null)
{
var WinPrint = window.open('','','letf=0,top=0,width=1,height=1,toolbar=0,scrollbars=0,status=0');
WinPrint.document.write(localprintarea.innerHTML);
WinPrint.document.close();
WinPrint.focus();
WinPrint.print();
WinPrint.close();


}


}
</script>

</head>
<body>
<form id="form1" runat="server">
<div>
Click on this button to print entire page.....
<asp:Button ID="Button1" runat="server" OnClientClick="return PrintData();" Text="Print" />
</div>
<div id="printarea">
Click on this button to print only the contents within div....
<asp:Button ID="Button2" runat="server" OnClientClick="return PrintDataSelected('printarea');"
Text="Contents only within div" />
</div>

</form>
</body>
</html>


ASPX.CS

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Print_Print1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Page ready for printing!!!...");
}

}

Wednesday, May 6, 2009

Showing progress bar while uploading file in ASP.net

The following code uses javascript setinterval, clearTimeout to show an progress bar while uploading a file in asp.net.

The code is self explanatory....

ASPX Page code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<script language="javascript" type="text/javascript">
var size = 2;
var id= 0;

function ProgressBar()
{
if(document.getElementById('<%=FileUpload1.ClientID %>').value != "")
{
document.getElementById("divProgress").style.display = "block";
document.getElementById("divUpload").style.display = "block";
id = setInterval("progress()",20);
return true;
}
else
{
alert("Select a file to upload");
return false;
}

}

function progress()
{
//alert(id);
size = size + 1;
if(size > 299)
{
clearTimeout(id);
}
document.getElementById("divProgress").style.width = size + "pt";
document.getElementById("<%=lblPercentage.ClientID %>").firstChild.data = parseInt(size / 3) + "%";

}

</script>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align:left">
<asp:FileUpload ID="FileUpload1" runat="server" /> <br />
<br />
<asp:Button ID="Button1" runat="server" Text="Upload" OnClientClick="return ProgressBar()" OnClick="Button1_Click" /><br /><br />
<div id="divUpload" style="display:none">
<div style="width:300pt;;text-align:center;">Uploading...</div>
<div style="width:300pt;height:20px; border:solid 1pt gray">
<div id="divProgress" runat="server" style="width: 1pt; height: 20px; background-color:Gray;display:none">
</div>
</div>
<div style="width:300pt;;text-align:center;">
<asp:Label ID="lblPercentage" runat="server" Text="Label"></asp:Label></div>
</div>
</div>
<br />
<asp:Label ID="Label1" runat="server" ForeColor="Red" Text=""></asp:Label>
</form>
</body>
</html>


ASPX.CS Code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
// Do code here to saving a file from fileupload control
//FileUpload1.PostedFile.SaveAs("path");
System.Threading.Thread.Sleep(8000);
Label1.Text = "Upload successfull!";
}
}

Monday, May 4, 2009

Set identity insert to on

This function is used to insert values to identity columns.
Note : Only one table at a time can use this function. After inserting the value to the identity column the identity insert must be turned off.

The following example shows how to use it

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

Implementing Nullable Types in C#

There may be situations where you might have to assign data from database columns containing null values to equivalent C# types. This section presents a simple application that will illustrate how to use nullable types to mitigate such problems.

Figure below shows the structure of a table called Patient that contains three nullable fields.



Executing the SQL script in Listing 1 creates the above mentioned Patient table in your database

Listing-1
T-SQL
Listing 1. Sample Table Script:
Run this SQL to create the sample table containing three columns that allow nulls.

CREATE TABLE [dbo].[PatientInfo](
[PatientID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactPerson] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSN] [int] NULL,
[BilledAmount] [int] NULL,
[Address] [varchar](50) COLLATE NOT NULL,
[Sex] [varchar](6) COLLATE NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[AdmissionDate] [datetime] NOT NULL,
[ReleaseDate] [datetime] NOT NULL,
CONSTRAINT [PK_PatientInfo] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO


You need some sample data, too; the script shown below inserts several records into the Patient table:
SET IDENTITY_INSERT [PatientInfo] ON

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex],
[DateOfBirth], [AdmissionDate],
[ReleaseDate])
VALUES (
1, 'Joydip', NULL, 4234234, NULL,
'Flat 20, Suvarna Aparments, Gachiboli, Hyderabad',
'Male', '19671102', '20070902', '20070910')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
2, 'Vinay', NULL, NULL, 500,
'Saptagiri Towers, Begumpet, Secunderabad',
'Male', '19770808', '20070821', '20070910')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
3, 'Sriram', NULL, NULL, NULL,
'2-4/A,Parklane Road, Hyderabad', 'Male',
'19790503', '20070608', '20070621')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
4, 'Nageswar', 'Ashant', 83283493, 25000,
'3-6/W, Keys Marg, Secunderabad', 'Male',
'19670522', '20070406', '20070422')

SET IDENTITY_INSERT [PatientInfo] OFF

GO

The three nullable fields in the Patient table are ContactPerson, BilledAmount, and SSN; those fields may or may not have values.

A Simple Null-Aware Application
With the sample data available, here's a simple application that illustrates how and where you can use nullable types.

The application has two classes called PatientNullable and PatientNonNullable. These classes read data from the Patient table discussed in the preceding section, and use that data to populate class members that correspond to the table columns. Both classes have a GetPatient() method that accepts a PatientID parameter, queries the database for the patient with that PatientID, and populates the class instance with the retrieved data.

The difference between the two classes, of course, is that the PatientNonNullable class does not handle nullable types; therefore, it's prone to problems at run time if you attempt to populate it from from database columns that can contain null values. In contrast, the PatientNullable class does accept null types. The differences in the two GetPatient() methods should clarify where nullable types can help simplify your database application development

Listing 2 and Listing 3 show the full code for the PatientNullable and PatientNonNullable classes, respectively, but here's the relevant portion from the getPatient() methods in both classes.

Listing 2
Listing 2. PatientNonNullable Class:
This class does not handle null values.

public class PatientNonNullable
{
long patientid;
string name;
string contactperson;
long ssn;
int billedamount;
string address;
string sex;
DateTime dateofbirth;
DateTime admissiondate;
DateTime releasedate;

public PatientNonNullable()
{
}

public void GetPatient(long patientID)
{
string connectionString =
"Server=.;Database=Patient;Integrated Security=SSPI";
string queryString = String.Format(
"SELECT PatientID, Name, ContactPerson, SSN, " +
"BilledAmount, Address, Sex, DateOfBirth, " +
"AdmissionDate, ReleaseDate FROM PatientInfo " +
"WHERE PatientID = {0}", patientID);
using (SqlConnection dbConnection = new
SqlConnection(connectionString))
{
dbConnection.Open();
SqlCommand dbCommand = new SqlCommand(
queryString, dbConnection);
SqlDataReader dataReader = dbCommand.ExecuteReader();
if(dataReader.HasRows)
while (dataReader.Read())
{
patientid = Convert.ToInt32(
dataReader["PatientID"]);
name = dataReader["Name"].ToString();
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
if (dataReader["SSN"] == DBNull.Value)
ssn = -1; //NULL replaced with -1
else
ssn = Convert.ToInt64(dataReader["SSN"]);
if (dataReader["BilledAmount"] == DBNull.Value)
billedamount = 0; //NULL Replaced with Zero
else
billedamount = Convert.ToInt32(
dataReader["BilledAmount"]);

address = dataReader["Address"].ToString();
sex = dataReader["Sex"].ToString();
dateofbirth = Convert.ToDateTime(
dataReader["DateOfBirth"]);
admissiondate = Convert.ToDateTime(
dataReader["AdmissionDate"]);
releasedate = Convert.ToDateTime(
dataReader["ReleaseDate"]);
}
}
}

public long PatientID { get { return patientid; } }
public string Name { get { return name; }}
public string ContactPerson { get { return contactperson; } }
public long SSN { get { return ssn; } }
public int BilledAmount { get { return billedamount; } }
public string Address { get { return address; } }
public string Sex { get { return sex; } }
public DateTime DateOfBirth { get { return dateofbirth; } }
public DateTime AdmissionDate { get { return admissiondate; } }
public DateTime ReleaseDate { get { return releasedate; } }
}

Listing-3
Listing 3. PatientNullable Class:
This class handles null database values using nullable types.

public class PatientNullable
{
long patientid;
string name;
string contactperson;
long? ssn;
int? billedamount;
string address;
string sex;
DateTime dateofbirth;
DateTime admissiondate;
DateTime releasedate;

public PatientNullable()
{
}

public void GetPatient(long patientID)
{
string connectionString =
"Server=.;Database=Patient;Integrated Security=SSPI";
string queryString = String.Format(
"SELECT PatientID, Name, ContactPerson, SSN, " +
"BilledAmount, Address, Sex, DateOfBirth, " +
"AdmissionDate, ReleaseDate " +
"FROM PatientInfo " +
"WHERE PatientID = {0}", patientID);
using (SqlConnection dbConnection = new
SqlConnection(connectionString))
{
dbConnection.Open();
SqlCommand dbCommand = new SqlCommand(
queryString, dbConnection);
SqlDataReader dataReader = dbCommand.ExecuteReader();
if (dataReader.HasRows)
while (dataReader.Read())
{
patientid = Convert.ToInt32(
dataReader["PatientID"]);
name = dataReader["Name"].ToString();
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
ssn = dataReader["SSN"] ==
DBNull.Value ? (long?)null :
Convert.ToInt64(dataReader["SSN"]);
billedamount = dataReader["BilledAmount"] ==
DBNull.Value ? (int?)null :
Convert.ToInt32(dataReader["BilledAmount"]);
address = dataReader["Address"].ToString();
sex = dataReader["Sex"].ToString();
dateofbirth = Convert.ToDateTime(
dataReader["DateOfBirth"]);
admissiondate = Convert.ToDateTime(
dataReader["AdmissionDate"]);
releasedate = Convert.ToDateTime(
dataReader["ReleaseDate"]);
}
}
}

public long PatientID { get { return patientid; } }
public string Name { get { return name; } }
public string ContactPerson { get { return contactperson; } }
public long? SSN { get { return ssn; } }
public int? BilledAmount { get { return billedamount; } }
public string Address { get { return address; } }
public string Sex { get { return sex; } }
public DateTime DateOfBirth { get { return dateofbirth; } }
public DateTime AdmissionDate { get { return admissiondate; } }
public DateTime ReleaseDate { get { return releasedate; } }
}
The two fragments below assign values from from a DataReader containing query fields that might be null to the ContactPerson, BilledAmount, and SSN class properties.

Here's the PatientNonNullable.getPatient() code excerpt:

...
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
if (dataReader["SSN"] == DBNull.Value)
ssn = -1; //NULL replaced with -1

else
ssn = Convert.ToInt64(dataReader["SSN"]);
if (dataReader["BilledAmount"] == DBNull.Value)
billedamount = 0; //NULL Replaced with Zero
else
billedamount = Convert.ToInt32(
dataReader["BilledAmount"]);
...

The ContactPerson string field has no problems with accepting null values, but when the SSN or BilledAmount database columns contain a nullable value, the PatientNonNullable class must substitute something to provide a valid value for the associated property's non-nullable value type.

In contrast, the PatientNullable class uses nullable types to overcome such problems. Here's the equivalent relevant portion of the PatientNullable.getPatient() method:

...
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
ssn = dataReader["SSN"] ==
DBNull.Value ? (long?)null :
Convert.ToInt64(dataReader["SSN"]);
billedamount = dataReader["BilledAmount"] ==
DBNull.Value ? (int?)null :
Convert.ToInt32(dataReader["BilledAmount"]);
...


Note how the code ensures that nullable types used for properties containing null values get assigned without run-time exceptions, and without providing substitute values.

With those classes in place, Listing 4 shows the code for a simple console application that instantiates the PatientNullable and PatientNonNullable classes and calls the GetPatient() method of each class:

Listing -4
Listing 4. Simple Console Application:
This application retrieves a patient's data using both PatientNullable and PatientNonNullable and prints the results to the console.

class Program
{
static void Main(string[] args)
{
Program program = new Program();
program.GetNonNullablePatient(1);
program.GetNullablePatient(3);

Console.ReadKey();
}

public void GetNonNullablePatient(int patientID)
{
PatientNonNullable patientNonNull = new
PatientNonNullable();
patientNonNull.GetPatient(patientID);

Console.WriteLine("\n\n\nPatientID = {0}",
patientNonNull.PatientID);
Console.WriteLine("Name = {0}", patientNonNull.Name);

if (patientNonNullable.ContactPerson != null)
Console.WriteLine("ContactPerson = {0}",
patientNonNullable.ContactPerson);

Console.WriteLine("SSN = {0}", patientNonNull.SSN);
Console.WriteLine("BilledAmount = {0}",
patientNonNull.BilledAmount);
Console.WriteLine("Address = {0}", patientNonNull.Address);
Console.WriteLine("Sex = {0}", patientNonNull.Sex);
Console.WriteLine("DateOfBirth = {0}",
patientNonNull.DateOfBirth);
Console.WriteLine("AdmissionDate = {0}",
patientNonNull.AdmissionDate);
Console.WriteLine("ReleaseDate = {0}",
patientNonNull.ReleaseDate);
}

public void GetNullablePatient(int patientID)
{
PatientNullable patientNullable = new PatientNullable();
patientNullable.GetPatient(patientID);
Console.WriteLine("\nApplying Nullable Types:\n\nPatientID = {0}",
patientNullable.PatientID);
Console.WriteLine("Name = {0}", patientNullable.Name);
if (patientNullable.ContactPerson != null)
Console.WriteLine("ContactPerson = {0}",
patientNullable.ContactPerson);
if (patientNullable.SSN.HasValue)
{
long ssn = (long)patientNullable.SSN;
Console.WriteLine("SSN = {0}", ssn);
}

if (patientNullable.BilledAmount.HasValue)
{
int billedAmount = (int)patientNullable.BilledAmount;
Console.WriteLine("BilledAmount = {0}",
patientNullable.BilledAmount);
}
Console.WriteLine("Address = {0}", patientNullable.Address);
Console.WriteLine("Sex = {0}", patientNullable.Sex);
Console.WriteLine("DateOfBirth = {0}",
patientNullable.DateOfBirth);
Console.WriteLine("AdmissionDate = {0}",
patientNullable.AdmissionDate);
Console.WriteLine("ReleaseDate = {0}", patientNullable.ReleaseDate);
}
}

Nullable Type in c# - Part II

Comparisons

Comparisons occur in a similar manner to the mathematical operations. The operands in a comparison are lifted to both being nullable. Then, the comparison is done. If one of the operands contains a null, the result of the comparison will be false.

If comparing for equality (or non-equality), two variables containing null will be considered equal. A variable containing null compared to a variable with any value will be considered not equal. The following are some examples of comparisons:

int abc = 123;
int xyz = 890;
int? def = null;
int? uvw = 123;

Comparison Result
abc == xyz // false
abc == def // false
def == null // true
abc == uvw // true
uvw == null // false
uvw != null // true

In all comparisons, the result is a Boolean value of true or false. When doing relative comparisons, you also will get a returned Boolean value of true or false; however, a null value impacts things. In the case of relative comparisons, if either (or both) of the value is null, a value of false will be returned. The following are some examples of relative comparisons and their results. These use the same defined variables from above.

Comparison Result
abc > uvw // false, they are equal
abc < def // false, def is null
uvw < def // false, because def is null
def > null // false, because right side is null
uvw > null // false, because right side is null

Removing Nullability

C# also gets an additional operator in its newest version. This is the ?? operator used for null coalescing. The null coalescing operator takes the following format:

returnValue = first ?? second;

In this case, if first is not null, its value will be returned to returnValue. If first is null, then the value of second will be returned. You should note that returnValue can be either a nullable or non-nullable variable.

If you wanted to move a nullable varaible's value to a non-nullable version, you could do the following:

int? ValA= 123;
int? ValB = null;

int NewVarA = ValA ?? -1;
int NewVarB = ValB ?? -1;

When the above is completed, NewVarA will contain the value of 123 because ValA was not null. NewVarB will contain the value of -1 because ValB was null. As you can see, this allows you to change variables with a null value to a defaulted value. In this case, the defaulted value is -1.


In Conclusion...

In conclusion, the newest version of C# allows for a nullable type. These types can be used with non-nullable types with little thought and little effort because the conversions will be built into the language. The nullable types should make working with database records and other optional information much easier going forward.

Nullable types is a part of the ECMA-334 version of C#. You'll need a compiler that supports this C# standard in order to use nullable types. Visual Studio 2005 supports this standard.

Nullable type in c#--part I

Using Nullable Types in C#
With the newest standard for the C# language, there is now support for nullable data types. This small change could be a huge help for those who deal with databases containing fields that are optional. Nullable data types can also be helpful in other situations as well.

In basic terms, a nullable data type is one that contain the defined data type or the value of null. The ECMA-334 standard for C# provides nullable versions of all the C# value types.

Defining Nullable Types

Defining a nullable type is very similar to defining the equivalent non-nullable type. The difference is in the use of the ? type modifier. To define an integer, you would normally do a simple declaration:

int myInt = 1;

To make myInt be able to store a null value, you would declare it as such:

int? myNullableInt = 1;

As you can see, these two variables look as though they are the same. The nullable version, however, is much different. The nullable version is actually a structure that combines a value type with a flag to indicate whether the value is null. Additionally, a nullable type has two publicly readable properties, HasValue and value. HasValue is a bool that is true if there is a value stored; otherwise, it is false if the variable is null. If HasValue is true, you can get the value of the variable. If it is false and you attempt to get the value, then an exception will be thrown.

null is now a keyword for C#. Additionally, it can be assigned to a nullable variable. The following are two valid assignments for a nullable variable:

double? myDouble = 3.14159;
double? myOtherDouble = null;

As you can see, myDouble is assigned a value, but could also be assigned null. In the second statement, myOtherDouble is initialized to contain a null value — something you can't do with a non-nullable type.
Using a Nullable Type

A nullable type can be used in the same way that a regular value type can be used. In fact, implicit conversions are built in for converting between a nullable and non-nullable variable of the same type. This means you can assign a standard integer to a nullable integer and vice-versa:

int? nFirst = null;
int Second = 2;

nFirst = Second; // Valid
nFirst = 123; // Valid
Second = nFirst; // Also valid

nFirst = null; // Valid
Second = nFirst; // Exception, Second is nonnullable.

In looking at the above statements, you can see that a nullable and nonnullable variable can exchange values as long as the nullable variable does not contain a null. If it contains a null, an exception is thrown. To help avoid throwing an exception, you can use the nullable's HasValue property:

if (nFirst.HasValue) Second = nFirst;

As you can see, if nFirst has a value, the assignment will happen; otherwise, the assignment is skipped.

Using Operators with Nullable Values: Lifted Operators


In addition to the automatic conversions between a nullable and non-nullable variable of the same value type, there are also changes with the operators to allow them to work with nullable and non-nullable values. These operators are called lifted operators.

Consider the following code:

int ValA = 10;
int? ValB = 3;

int? ValC = ValA * ValB;

What is stored in Val C? The value of 30 would be stored into ValC. The standard operators have been modified so that they "lift" the non-nullable values to being nullable and thus allow the standard operations to work. Now, consider the following change:

int ValA = 10;
int? ValB = null;

int? ValC = ValA * ValB;

What would ValC contain this time? ValC would contain null. In the case where either operand is null, the result of a lifted operation will also be null. Even if you were doing addition or subtraction, it would still be null. So, ValA + ValB using the above values would result in null, not 10.

What if ValC were not a nullable type? What does the following do then?

int ValA = 10;
int? ValB = null;

int ValC = ValA * ValB; // ValC not nullable

This code would actually throw an exception. The result of ValA * ValB is null and a null can't be assigned to a non-nullable type. As such, an exception is thrown.