Thursday, May 28, 2009

Select using SQLParameter in ADO.NET

This artilce explains how to use parameters in the command. I have created a database called Test in SQL which contains a UserDetail table having UserId and Password as column.

1.Import below namespace


using System.Data;
using System.Data.SqlClient;

2. Create and open SQLConnection



string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();


3. Create SQLCommand Objects



SqlCommand cmd = new SqlCommand("Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password", conn);



4. Create SqlParameter Objects and bind it to SQLCommand objects


SqlParameter
[] param = new SqlParameter[2];
param[0] =
new SqlParameter("@UserId", strUserId);
param[1] =
new SqlParameter("@Password", strPassword);
//add parameters to command object
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);


5. Get Data from DB

//get data stream
SqlDataReader reader = cmd.ExecuteReader();
//Check whether record exists or not
if (reader.HasRows)
{
     // Loop through each record
    
while (reader.Read())
     {
          strName = reader[
"UserId"].ToString();
     }
}
else
{
     Response.Write("Record not found.");
}

 


Let's Put all together

string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn =
null;
SqlDataReader reader =
null;
string strUserId = "myName";
string strPassword = "myPassword";
string strName = string.Empty;
try
{
     conn =
new SqlConnection(connectionString);
     conn.Open();
     SqlCommand cmd = new SqlCommand("Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password", conn);
     
SqlParameter[] param = new SqlParameter[2];
     param[0] =
new SqlParameter("@UserId", strUserId);
     param[1] =
new SqlParameter("@Password", strPassword);
     
//add new parameter to command object
     
cmd.Parameters.Add(param[0]);
     cmd.Parameters.Add(param[1]);
     
//get data stream
     reader = cmd.ExecuteReader();
     //Check whether record exists or not
     
if (reader.HasRows)
     {
          
// Loop through each record
          
while (reader.Read())
          {
               strName = reader[
"UserId"].ToString();
          }
     }
     
else
     {
          Response.Write(
"Record not found.");
     }
}
catch (Exception ex)
{
}
finally
{
     //Closing reader and connection in finally block in case  of any exception reader and conn should close.
     //Code in finally block executes in normal scenario as well as in case of exception thrown.
     if (reader != null)
     {
          reader.Close();
     }
     // close connection
     
if (conn != null)
     {
          conn.Close();
     }
}




 


Read this article to implement above scenario using stored procedure.
Select using SQLParameter
I always recommend to use stored procedure instead of inline query in .NET.

Happy Coding :)

Friday, May 22, 2009

SessionID in .NET 2.0

Most of the asp.net developer has assumption that SessionID doesn't change through out the session of any user.
The above statement holds good completely in .NET 1.1 and partially in .NET 2.0.

Let's examine the behaviour of SessionID in .NET 2.0

Create a aspx page, copy and past below code in codebehind of Page_Load.

Response.Write(Session.SessionID);

Now run the application, you will get sessionid on the page.
Now examine below steps:
1. Open new browser using CTRL + N, you will get different sessionid now.
2. Open new browser using File -> New, you will again get different sessionid now.
3. Copy the url and paste it into another tab of the browser, you will again get different sessionid now.

Above behiour only happens in .NET 2.0. In .NET 1.1 sessionid will remain same.

Create another aspx page, Copy and paste below line of code in codebehind of Page_Load

Session["Test"] = "testing";
Response.Write(Session.SessionID);


Now run the application, you will get sessionid on the page.
Now examine all the above three steps, this time you will get same sessionid in all the three steps.
So, whenever Session object will be used, SessionID will be same.

Session information gets stored in the cookie, so when cookie based session is used(Other is url based), ASP.NET does not allocate the cookie until Session object is used.

Therefore SessionID get generated for each page request until Session object is used.

Thursday, May 21, 2009

Multiple Form Tag with runat=Server

Many ASP.NET user believe that there can be only one Form tag with runat=Server, this is party true,there can be multiple form with runat=Server but only one can be visiable at a time.

Lets evaluate:

Create a aspx page and put two form tag with runat=Server like below:

<form id="form1" runat="server">
</form>

<form id="form2" runat="server">
</form>

Now compile the application, you won't get any error.

Now run the application, you will get below error

"A page can have only one server-side Form tag."

Now add a panel and set visible property to false of the panel

<asp:Panel Visible="false" ID="Panel1" runat="server">
<form id="form1" runat="server">
</form>
</asp:Panel>
<form id="form2" runat="server">
</form>

Now compile and run the application. It will run without any error.

Lets put other form also in a panel and add a button on each form tag.

<asp:Panel Visible="false" ID="Panel1" runat="server">
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button in Form1"
onclick="Button1_Click" />
</div>
</form>
</asp:Panel>
<asp:Panel ID="Panel2" runat="server">
<form id="form2" runat="server">
<div>
<asp:Button ID="Button2" runat="server" Text="Button in Form2"
onclick="Button2_Click" />
</div>
</form>
</asp:Panel>

Now we will show and hide form on button click, copy and paste below code on code behind.

protected void Button2_Click(object sender, EventArgs e)
{
Panel2.Visible = false;
Panel1.Visible = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
Panel1.Visible = false;
Panel2.Visible = true;
}

Now when you you can move to different form tag with the button click without any error.

Happy Coding :)

Thursday, May 14, 2009

May 2009 AjaxControlToolkit

Download latest AjaxControlToolk from http://ajaxcontroltoolkit.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=27326

There are three new controls:

HTMLEditor

The HTMLEditor control allows you to easily create and edit HTML content. You can edit in design mode, as a rich text editor, or in source view to edit the HTML markup directly.

ComboBox

The ComboBox control provides a DropDownList of items, combined with TextBox. Different modes determine the interplay between the text entry and the list of items.


ColorPicker

The ColorPicker Control Extender can be attached to any ASP.NET TextBox control. It provides client-side color-picking functionality with UI in a popup control.

Tuesday, May 12, 2009

Beware of IIF

Should we use IIF of VB.NET? I would say no, lets validate it why should we not use IIF.

First reason:

In IIF both true and false part are evaluated, Lets evaluate

Create a Button control and copy and paste below code on the button click event,

Dim i As Integer = CInt(IIf(Session("test") Is Nothing, 0, Session("test").ToString()))

Compile and run the application, Click on the Button, you will get below error,

Object reference not set to an instance of an object.

It made me to believe that both the statements (true and false) are executing 0 as well as Session("test").ToString(), 2nd statement (Session("test").ToString()) which is else part shouldn't have executed but it got executed.

This is serious problem when you are checking an object with Nothing.

Now run the below code in C#

int i = (Session["test"] == null ? 0 : Convert.ToInt32(Session["test"].ToString()));

It won't throw any error because it won't go to else part.

Second reason:

Turn Off Option Strict, Copy and paste below code on top of the page
Option Strict Off

Create a Button control and copy and paste below code on the button click event,

Dim blnIIF As Boolean
Dim frmIIF As Form = IIf(blnIIF, "True", 10)

Compile and run the application, Click on the Button, you will get below error,

"Unable to cast object of type 'System.Int32' to type 'System.Windows.Forms.Form"

The above code compiled, which is quite weired. It should have throw error at compile time.

Why it didn't throw error at compile time?

Actually, IIF is a function, its paraemeters and return type are object, therefore while compiling it looses all type checking.

But if you turn on Option Strict, Copy and paste below code on top of the page

Option Strict ON

Now, you will get compile time error.

But in case of C#, below code won't compile

Form f = (blnIIF?"True":10);

Happy Coding :)

Saturday, May 9, 2009

Visaul Studio 2010 and .NET 4.0

Visual Studio 2010 and .NET 4.0.

I am going to download the CTP version today, will post on the new features.

Download the CTP version from below link:

http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx

http://www.microsoft.com/downloads/details.aspx?FamilyId=922B4655-93D0-4476-BDA4-94CF5F8D4814&displaylang=en

Tuesday, May 5, 2009

Multiple file upload

My last blog
http://technicalsol.blogspot.com/2009/05/gmail-file-upload-and-remove.html
just talked about adding and removing file upload controls in html.

Here is the code to upload multiple files to server along with the adding file upload control using javascript.

Copy and paste the below lines in aspx page between form tags:

<input type="file" name="attachment" runat="server" id="attachment" onchange="document.getElementById('moreUploadsLink').style.display =
'block';" />
<div id="moreUploadsLink" style="display:none;">
<a href="javascript:addElement();">Attach another File</a>
</div>
<input type="hidden" value="0" id="theValue" />
<div id="myDiv"> </div>
<asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />

Copy and paste the below javascript:
<script type="text/javascript">
function addElement()
{
var ni = document.getElementById('myDiv');
var numi = document.getElementById('theValue');
var num = (document.getElementById('theValue').value -1)+ 2;
numi.value = num;
var newdiv = document.createElement('div');
var divIdName = 'my'+num+'Div';
newdiv.setAttribute('id',divIdName);
newdiv.innerHTML = '<input type="file" name="attachment" id="attachment"/><input type="Button" value="Remove" onclick="removeElement(' + divIdName
+ ')"/>';
ni.appendChild(newdiv);
}


function removeElement(divNum)
{
var d = document.getElementById('myDiv');
d.removeChild(divNum);
}

</script>

Copy and paste the below code in the code behind:
protected void Button1_Click(object sender, EventArgs e)
{
HttpFileCollection uploadFiles = HttpContext.Current.Request.Files;
for (int i = 0; i < uploadFiles.Count; i++)
{
HttpPostedFile uploadFile = uploadFiles[i];
uploadFile.SaveAs(@"c:\inetpub\wwwroot\" + Path.GetFileName(uploadFile.FileName));
}
}

Saturday, May 2, 2009

SQL Time Difference

How to get time difference in SQL, if the Date is same?

Here is the way, we can achieve,

DECLARE @date as datetime
DECLARE @date1 as datetime
SET @date = '2009-05-02 22:58:39.037'
SET @date1 = '2009-05-02 23:59:56.087'

The below query will give difference in mili second as i have used ms
SELECT ROUND(cast((datediff(ms, @date, @date1) / 60.0) as FLOAT),2) AS DiffMiliSecond

The below query will give difference in second as i have used ss
SELECT ROUND(cast((datediff(ss, @date, @date1) / 60.0) as FLOAT),2) AS DiffSecond

The below query will give difference in minute as i have used mi SELECT ROUND(cast((datediff(mi, @date, @date1) / 60.0) as FLOAT),2) AS DiffMinute

Gmail Like File upload and Remove

Adding and removing File upload like Gmail

Here is the HTML:
<html>
<input type="file" name="attachment" id="attachment" onchange="document.getElementById('moreUploadsLink').style.display =
'block';" />
<div id="moreUploadsLink" style="display:none;">
<a href="javascript:addElement();">Attach another File</a>
</div>
<input type="hidden" value="0" id="theValue" />
<div id="myDiv"> </div>
</html>

Here is the Javascript:

<script language='Javascript'>
function addElement()
{
var ni = document.getElementById('myDiv');
var numi = document.getElementById('theValue');
var num = (document.getElementById('theValue').value -1)+ 2;
numi.value = num;
var newdiv = document.createElement('div');
var divIdName = 'my'+num+'Div';
newdiv.setAttribute('id',divIdName);
newdiv.innerHTML = '<input type="file" name="attachment" id="attachment"/><a href="#" onclick="removeElement(' + divIdName
+ ')">Remove </a>';
ni.appendChild(newdiv);
}


function removeElement(divNum)
{
var d = document.getElementById('myDiv');
d.removeChild(divNum);
}

</script>

Gmail most probably ajax to achieve this, i have done with javascript.

Site Meter