Microsoft Dot Net Master: February 2011

Monday, February 28, 2011

Convert String To Decimal

string qty;
decimal qtyy = ToDecimal(qty);
private decimal ToDecimal(string Value)
{
if (Value.Length == 0)
return 0;
else
return Decimal.Parse(Value);
}

Insert Multiple Table Values in a Single Table Using Cursor Sql Server

declare @str1 varchar(50)
declare @tot1 int
declare @tot2 int
declare @tot3 int
DECLARE @USERID INT
CREATE TABLE #report(Name Varchar(50),Account_Count int,Physicisn_Count int,Completed int)

DECLARE getID CURSOR FOR
SELECT ID FROM adminlogin where TypeofUser<>'A'
OPEN getID
FETCH NEXT
FROM getID INTO @USERID
WHILE @@FETCH_STATUS = 0
BEGIN
set @str1=( select Name from adminlogin where id=@USERID)
set @tot1=( select count(*) as Account_Count from View_Rpt_EmpVsHospital where id=@USERID)
set @tot2=( select count(*) as Physician_Count from View_Rpt_PhyVsHospital where Hospitalid in (select Hospitalid from View_Rpt_EmpVsHospital where id=@USERID))
set @tot3=(select count(distinct Physicianid) as Completed from View_SalesEntry where Physicianid in (select Physicianid from View_Rpt_PhyVsHospital where Month(DateFrom)=12 and Month(DateTo)= 12 and Year(DateFrom)=2008 and Year(DateTo)= 2008 and LocationId=24
and Hospitalid in (select Hospitalid from View_Rpt_EmpVsHospital where id=@USERID)))

INSERT INTO #report(Name,Account_Count,Physicisn_Count,Completed) VALUES ((@str1),(@tot1),(@tot2),(@tot3));
FETCH NEXT
FROM getID INTO @USERID

END
CLOSE getID
DEALLOCATE getID
--SELECT * FROM #report
--drop table #report

Monday, February 21, 2011

Downloading Files C#

A lot of questions are being asked about downloading a file from the web server to the client in ASP.NET. I have updated this blog post due to the high number of view & comments. You will realize i added a function called "ReturnExtension" which will return the proper content type and set it to the Response.ContentType property. Almost well known file types are supported.

C# Code

// Get the physical Path of the file(test.doc)
string filepath = Server.MapPath("test.doc");

   // Create New instance of FileInfo class to get the properties of the file being downloaded
   FileInfo file = new FileInfo(filepath);

   // Checking if file exists
   if (file.Exists)
   {
    // Clear the content of the response
    Response.ClearContent();

    // LINE1: Add the file name and attachment, which will force the open/cance/save dialog to show, to the header
    Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);

    // Add the file size into the response header
    Response.AddHeader("Content-Length", file.Length.ToString());

// Set the ContentType
Response.ContentType = ReturnExtension(file.Extension.ToLower());

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)
Response.TransmitFile(file.FullName);

    // End the response
    Response.End();
   }

private string ReturnExtension(string fileExtension)
{
     switch (fileExtension)
            {
                case ".htm":
                case ".html":
                case ".log":
                    return "text/HTML";
                case ".txt":
                    return "text/plain";
                case ".doc":
                    return "application/ms-word";
                case ".tiff":
                case ".tif":
                    return "image/tiff";
                case ".asf":
                    return "video/x-ms-asf";
                case ".avi":
                    return "video/avi";
                case ".zip":
                    return "application/zip";
                case ".xls":
                case ".csv":
                    return "application/vnd.ms-excel";
                case ".gif":
                    return "image/gif";
                case ".jpg":
                case "jpeg":
                    return "image/jpeg";
                case ".bmp":
                    return "image/bmp";
                case ".wav":
                    return "audio/wav";
                case ".mp3":
                    return "audio/mpeg3";
                case ".mpg":
                case "mpeg":
                    return "video/mpeg";
                case ".rtf":
                    return "application/rtf";
                case ".asp":
                    return "text/asp";
                case ".pdf":
                    return "application/pdf";
                case ".fdf":
                    return "application/vnd.fdf";
                case ".ppt":
                    return "application/mspowerpoint";
                case ".dwg":
                    return "image/vnd.dwg";
                case ".msg":
                    return "application/msoutlook";
                case ".xml":
                case ".sdxl":
                    return "application/xml";
                case ".xdp":
                    return "application/vnd.adobe.xdp+xml";
                default:
                    return "application/octet-stream";
}

N.B: If you want to bypass the Open/Save/Cancel dialog you just need to replace LINE1 by the below code

Response.AddHeader("Content-Disposition", "inline; filename=" + file.Name);

Response.TransmitFile VS Response.WriteFile:
1- TransmitFile: This method sends the file to the client without loading it to the Application memory on the server. It is the ideal way to use it if the file size being download is large.
2- WriteFile: This method loads the file being download to the server's memory before sending it to the client. If the file size is large, you might the ASPNET worker process might get restarted.

Hope this helps,

Friday, February 18, 2011

Error Handling in SQL Server 2005

Introduction

During development of any application, one of the most common things we need to take care of is Exception and Error handling. Similarly we need to take care of handling error and exception while designing our database like inside stored procedure. In SQL Server 2005, there are some beautiful features available using which we can handle the error.

When We Need To Handle Error in SQL Server

Generally a developer tries to handle all kinds of exception from the code itself. But sometimes we need to handle the same from the DB site itself. There are some scenarios like, we are expecting some rows should come when we will execute the store procedure, but unfortunately SP returns none of them. Below points can be some possible scenarios where we can use error handling:

While executing some DML Statement like INSERT, DELETE, UPDATE we can handle the error for checking proper output
If transaction fails, then we need to rollback - This can be done by error handling
While using Cursor in SQL Server

Error Handling Mechanism

The two most common mechanisms for error handling in SQL Server 2005 are:

@@ERROR
TRY-CATCH Block

Let's have a look at how we can implement both @@Error and Try-Catch block to handle the error in SQL Server 2005.

Using @@ERROR

We can consider @@ERROR as one of the basic error handling mechanisms in SQL Server. @@Error is a Global Variable in SQL Server. This variable automatically populates the error message when a certain error occurred in any statement. But we have to trace it within just after the next line where the actual error occurred, otherwise, it will reset to 0.

General Syntax

General syntax for @@ERROR is as follows:

Select @@ERROR

Return Type

int

It returns the Error Number.

Sample Example

I have a table named StudentDetails with columns, Roll (int), Name (varchar) and Address (varchar). Now I am intentionally trying to insert a char in Roll field:

insert into StudentDetails (roll,[Name],Address)
 values ('a','Abhijit','India')

This will throw the following Error :

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'a' to data type int.

Check out the Message and number, it is 245. Now, I am executing the @@Error statement just after this statement and check out the output:

Select  @@Error

The output is:

So, @@Error returns the same error as return by insert command. As I have already said, @@Error returns the error number for the last Transact-SQL statement executed, so if we execute any @@Error statement, we will get output 0.

When We Should Use @@Error

There are some scenarios where we should use @@ERROR:

With Insert, Delete, Update, Select Into Statement
While using Cursor in SQL Server (Open, Fetch Cursor)
While executing any Stored Procedure

Using Try...Catch Block

This is available from SQL Server 2005 Onwards. This is generally used where want to trap or catch error for multiple SQL statements like or a SQL Block of statement. TRY...CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.

General Syntax

Below is the general syntax for Try-Catch block:

-- SQL Statement-- SQL StatementBEGIN TRY
   -- SQL Statement or BlockEND TRY
BEGIN CATCH
   -- SQL Statement or Block
END CATCH
-- SQL Statement

Whenever there are some errors in TRY Block, execution will moved to CATCH block.

Sample Example

As I have already discussed about the studentDetails table, I am now going to insert one record in the table with Roll='a'.

BEGIN TRY
     INSERT INTO StudentDetails(Roll, [Name])
     VALUES('a', 'Abhijit')

END TRY
BEGIN CATCH
   SELECT 'There was an error while  Inserting records in DB '
END CATCH

As Roll is an int type but I am trying to insert a char type data which will violate the type conversion rule, an error will be thrown. So the execution pointer will jump to Catch block. And below is the output:

There was an error while  Inserting records in DB

Now, to get the details of the error SQL Server provides the following System function that we can use inside our Catch-block for retrieving the details of the error. Please check the below table:

Function Name	Description
`ERROR_MESSAGE()`	Returns the complete description of the error message
`ERROR_NUMBER()`	Returns the number of the error
`ERROR_SEVERITY()`	Returns the number of the Severity
`ERROR_STATE()`	Returns the error state number
`ERROR_PROCEDURE()`	Returns the name of the stored procedure where the error occurred
`ERROR_LINE()`	Returns the line number that caused the error

Here is one simple example of using System Function:

BEGIN TRY
   INSERT INTO StudentDetails(Roll, [Name])
    VALUES('a', 'Abhijit')

END TRY
BEGIN CATCH
   SELECT  ' Error Message: '  + ERROR_MESSAGE() as ErrorDescription
END CATCH

I have executed the same code block here but rather than showing custom message, I am showing the internal Error message by Calling ERROR_MESSAGE() System function. Below is the output:

Nested TRY-CATCH Block

Like other programming languages, we can use Nested Try catch block in SQL Server 2005.

BEGIN TRY
  print 'At Outer Try Block'

   BEGIN TRY
      print 'At Inner Try Block'
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block'

   END CATCH
END TRY
BEGIN CATCH
   print 'At Outer catch block'
END CATCH

If we execute this, the output will look like:

At Outer Try Block
At Inner Try Block

Now, Inner catch blocks throw an error:

BEGIN TRY
  print 'At Outer Try Block'

   BEGIN TRY
      print 'At Inner Try Block'
      INSERT INTO StudentDetails(Roll, [Name])   _
  VALUES('a', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block'
   END CATCH
END TRY

BEGIN CATCH
   print 'At Outer catch block'
END CATCH

Which gives the following output:

At Outer Try Block
At Inner Try Block
At Inner catch Block

Try-Catch Block For Transaction Roll Back

Here I am going to explain one real life scenario of using TRY-CATCH block. One of the common scenarios is using Transaction. In a Transaction, we can have multiple operations. If all operations executed successfully, then database will commit otherwise we need to ROLLBACK.

/*

  I want to delete a Particular Records from Both Student
  Details and Library. Database will only commit, iff both 
  delete statement execute successfully, If fails it will Roll
  back. Intentionally  I have passed a wrong roll ( Which causes)
  the exception and transaction will rollback.
*/
BEGIN TRY
   -- Start A Transaction   BEGIN TRANSACTION   

   -- Delete Student From StudenDetails Table
   DELETE FROM StudentDetails WHERE Roll = '1'
   Print 'Delete Record from Student Details Table'

   -- Delete The Same Student Records From Library Table also   DELETE FROM Library  WHERE Roll = 'a'
   Print 'Delete Record from Library Table'

   -- Commit if Both Success   COMMIT
   -- Update Log Details   Insert into LogDetails(ID,Details) values ('1','Transaction Successful');

END TRY
BEGIN CATCH
 Print 'Transaction Failed - Will Rollback'
  -- Any Error Occurred during Transaction. Rollback  IF @@TRANCOUNT > 0

    ROLLBACK  -- Roll backEND CATCH

Below is the output:

Delete Record from Student Details Table
Transaction Failed - Will Rollback

Points of Interest

I have written and explained each and every thing very easily and with a practical example. Hope this will help you.

Please give your feedback and suggestions.

Thursday, February 17, 2011

Google Search API implementation in asp.net

Google API in ASP.NET

Here is an article to show how to implement or use Google API in Asp.NET.

Background

Using Google API means you are using google searching in your website

First Step:-

For using Google API you should have a Google Account for this just simply create a Gmail Account which is free of cost.
Then you have to get the Google API key this is also free, simply search on google for Google API key.

Secong Step:-

Now create an ASP.Net website and add this code to Defaul.aspx page

Default.aspx
view source

    <%@ 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>Google API:</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <strong><span style="font-size: 16pt; color: #ff0000; font-family: Courier New CE">
          Google API In ASP.NET </span></strong>
                <br />
                <br />
            <hr style="font-weight: bold; color: #006600; height: 5px"/>
          <asp:Label ID="lblmsg" runat="server" Text=""></asp:Label>
            <br />
                <br />
           <asp:TextBox ID="txtsearch" runat="server" BackColor="#FFFFC0" BorderColor="Black" Width="321px" Height="18px" ></asp:TextBox>
           <asp:Button ID="btn1" runat="server" Text="Search" BackColor="#FFFFC0" BorderColor="#80FF80" ForeColor="#004000" OnClick="btn1_Click" Width="95px" />
            <br />
            <br />
            <hr style="font-weight: bold; color: #ff3333; height: 1px; text-decoration: line-through" />
        </div>
        </form>
    </body>
    </html>

    Interface (Defaul.aspx)

    Default.aspx.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;

    public partial class _Default : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btn1_Click(object sender, EventArgs e)
    {
    if (txtsearch.Text.Trim().Length == 0)
    {
    lblmsg.Text = "Please enter your query to search...";
    }
    else
    {
    Response.Redirect("SearchDetails.aspx?q="+txtsearch.Text);
    }
    }
    }

Third Step:-

Add a new .aspx page and give the name "SearchResult.aspx".

Fourth Step:-

Add this code to

SearchResult.aspx:-
view source
print?
    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="SearchDetails.aspx.cs" Inherits="SearchDetails" %>

    <!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="Head2" runat="server">
    <title>Google Search...</title>
    <script src="http://www.google.com/jsapi?key=Add your key here" type="text/javascript"></script>

    <script language="Javascript" type="text/javascript">
    //<![CDATA[
    google.load('search', '1');

    function OnLoad() {


    var searchControl = new google.search.SearchControl();

    // Add in a full set of searchers
    var localSearch = new google.search.LocalSearch();
    searchControl.addSearcher(localSearch);
    searchControl.addSearcher(new google.search.WebSearch());
    searchControl.addSearcher(new google.search.VideoSearch());
    searchControl.addSearcher(new google.search.BlogSearch());
    searchControl.addSearcher(new google.search.NewsSearch());
    searchControl.addSearcher(new google.search.ImageSearch());
    searchControl.addSearcher(new google.search.BookSearch());
    searchControl.addSearcher(new google.search.PatentSearch());

    searchControl.draw(document.getElementById("myCtrl"));

    // execute an inital search
    searchControl.execute(GetQueryString("q"));
    }
    google.setOnLoadCallback(OnLoad);

    //]]>
    // Function to get Query String Value
    function GetQueryString(query) {
    hu = window.location.search.substring(1);
    gy = hu.split("&");
    for (i = 0; i < gy.length; i++) {
    ft = gy[i].split("=");
    if (ft[0] == query) {
    return ft[1];
    }
    }
    }

    </script>

    </head>
    <body>
    <form id="form2" runat="server">
    <div id="myCtrl" style="width:100%">

    </div>
    </form>
    </body>
    </html>
NOTE: Add you key here = Copy your key from Google and paste in place of "add your key here"
If you feel any problem in getting Google API key, then contact me I will be happy to help you

OutPut:-

When you enter some query in textbox and click the search button it will redirect this page to SearchResult.aspx page where we are using script provided by Google.

Wednesday, February 2, 2011

Moving ListItems between Two ListBox – Client Side Approach

This example demonstrates on how to move items between two ListBox using JavaScript.

Here are the code blocks below:

<title>ListBox Demo</title>

function AddItemInList(fromLeftToRight, isAll)

{

var list1 = document.getElementById('<%= ListBox1.ClientID %>');

var list2 = document.getElementById('<%= ListBox2.ClientID %>');

if(Boolean(fromLeftToRight) == true)

{

MoveItems(list1,list2,isAll);

}

else

{

MoveItems(list2,list1,isAll);

}

return false;

}

function MoveItems(listFrom, listTo, isAll)

{

var toBeRemoved = "";

if(listFrom.options.length > 0)

{

for (i=0; i<listFrom.length; i++)

{

if (listFrom.options[i].selected || (isAll == true))

{

if(Exist(listTo, listFrom.options[i].value) == 0)

{

listTo[listTo.length] = new Option(listFrom.options[i].text, listFrom.options[i].value, true);

toBeRemoved = toBeRemoved + listFrom.options[i].value + ',';

}

ClearSelection(listTo);

RemoveFromList(listFrom, toBeRemoved);

}

else

{

alert('Unable to Move Items. List is Empty!');

}

function RemoveFromList(listFrom, items)

{

var toBeRemoved = items.split(',');

for (var i=0; i < toBeRemoved.length; i++)

{

for (var j = 0; j < listFrom.length; j++)

{

if (listFrom.options[j] != null && listFrom.options[j].value == toBeRemoved[i])

{

listFrom.options[j] = null;

}

function ClearSelection(list)

{

list.selectedIndex = -1;

}

function Exist(list, value)

{

var flag = 0;

for (var i=0; i < list.length; i++)

{

if (list.options[i].value == value)

{

flag = 1;

break;

}

return flag;

}

</script>

</head>

<body>

<div>

<table>

<tr>

<td >

<asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple" Width="150px" Height="150px">

<asp:ListItem>A</asp:ListItem>

<asp:ListItem>B</asp:ListItem>

<asp:ListItem>C</asp:ListItem>

<asp:ListItem>D</asp:ListItem>

<asp:ListItem>E</asp:ListItem>

</asp:ListBox>

</td>

<asp:Button ID="ButtonAdd" runat="server" Text=">" Width="50px" OnClientClick="return AddItemInList(true,false);"/><br />

<asp:Button ID="ButtonRemove" runat="server" Text="<" Width="50px" OnClientClick="return AddItemInList(false,false);"/> <br />

<asp:Button ID="ButtonAddAll" runat="server" Text =">>>" Width="50px" OnClientClick="return AddItemInList(true,true);"/><br />

<asp:Button ID="ButtonRemoveAll" runat="server" Text ="<<<" Width="50px" OnClientClick="return AddItemInList(false,true);"/>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Method Definitions:

AddItemInList(fromLeftToRight, isAll) – is a method that accepts two parameters. The first parameter indicates if the Lists are to be moved from Left going to the right; the second parameter indicates if all the lists are going to be moved. Check the code above on how to call this method.

MoveItems(listFrom,listTo,isAll) – is a method that accepts three parameters. The first two parametes indicates the ListBox objects – the left and right ListBox; the third parameter indicates if all the lists are going to be moved. Basically this method is where the moving of ListItems is being executed.

RemovedFromList(listFrom, items) – is a method that accepts two parameters. The first one is the ListBox object that contains the ListItems to be removed; the second one contains the concatenated items to be removed.

ClearSelection(list) – a method that resets the Selection of the ListBox.

Exist(list, value) – a method that takes two parameters. The first one indicates the ListBox object to where the item is to be moved; the second one indicates the value to compare.

Running the code above will show this output below in the page.

Moving ListItems between Two ListBox – Server Side Approach

This example shows how to move items between two ListBox. Basically it allows you to add, remove and move multiple list items at a time.

To start, let’s set up our GUI. Just for simplicity of this demo, I set up the web form like this:

<title>Moving ListItems between two ListBox</title>

</head>

<body>

<div>

<table>

<tr>

<asp:Button ID="ButtonAdd" runat="server" Text=">" OnClick="ButtonAdd_Click" Width="50px"/><br />

<asp:Button ID="ButtonRemove" runat="server" Text="<" OnClick="ButtonRemove_Click" Width="50px"/> <br />

<asp:Button ID="ButtonAddAll" runat="server" Text =">>>" OnClick="ButtonAddAll_Click" Width="50px"/>

<br />

<asp:Button ID="ButtonRemoveAll" runat="server" Text ="<<<" OnClick="ButtonRemoveAll_Click" Width="50px"/>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Please note that we need to set property SelectionMode to Multiple to allow you select and move multiple items by holding Ctrl + Click.

In this demo, I presumed that you already bind your ListBox with data from the database.
Now let’s, create a method for Moving the items between two ListBox. Here’s the code block below:

private void MoveItems(bool isAdd)

{

if (isAdd)// means if you add items to the right box

{

for (int i = ListBox1.Items.Count - 1; i >= 0; i--)

{

if (ListBox1.Items[i].Selected)

{

ListBox2.Items.Add(ListBox1.Items[i]);

ListBox2.ClearSelection();

ListBox1.Items.Remove(ListBox1.Items[i]);

}

else // means if you remove items from the right box and add it back to the left box

{

for (int i = ListBox2.Items.Count - 1; i >= 0; i--)

{

if (ListBox2.Items[i].Selected)

{

ListBox1.Items.Add(ListBox2.Items[i]);

ListBox1.ClearSelection();

ListBox2.Items.Remove(ListBox2.Items[i]);

}

private void MoveAllItems(bool isAddAll)

{

if (isAddAll)// means if you add ALL items to the right box

{

for (int i = ListBox1.Items.Count - 1; i >= 0; i--)

{

ListBox2.Items.Add(ListBox1.Items[i]);

ListBox2.ClearSelection();

ListBox1.Items.Remove(ListBox1.Items[i]);

}

else // means if you remove ALL items from the right box and add it back to the left box

{

for (int i = ListBox2.Items.Count - 1; i >= 0; i--)

{

ListBox1.Items.Add(ListBox2.Items[i]);

ListBox1.ClearSelection();

ListBox2.Items.Remove(ListBox2.Items[i]);

}

MoveItems() method takes a bool parameter which tells the method to execute add or remove ListItems. True if you Add and False if you Remove.

MoveAllItems() method also takes a bool parameter which tells the method to execute "add al"l or "remove all ListItems" between ListBox. True if you Add All and False if you Remove All. The main difference of the method MoveAllItems() compared to MoveItems() method is we don’t need to check the Selected ListItems in the ListBox, but instead we simply loops through the ListItems to execute Add All or Remove All ListItems at once.

Here are the code blocks for calling those methods at the corresponding Button Click events.

protected void ButtonAdd_Click(object sender, EventArgs e)
{
        MoveItems(true);// true since we add
    }
    protected void ButtonRemove_Click(object sender, EventArgs e)
    {
        MoveItems(false); // false since we remove
    }

    protected void ButtonAddAll_Click(object sender, EventArgs e)
    {
        MoveAllItems(true); // true since we add all
    }
    protected void ButtonRemoveAll_Click(object sender, EventArgs e)
    {
        MoveAllItems(false); // false means re remove all
    }

The output would look like below when you run the page.