public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Archive for April, 2009

Fetch random set of rows through SQL Query

Posted by Virendra Dugar on April 27, 2009

If you need to fetch random rows from a SQL server table then RAND() function will do the job for you.

For example:

select rand(), productid, productname from product

The problem with this method is that the Rand() function returns the same value for all rows within a given call, See image below.

ScreenShot1

You can also supply a seed value to the rand function to generate a unique value.

select top 5 rand(productid) as val ,productid, productname from product order by val

ScreenShot2

No matter, how many times you run this command, you will always get top 5 rows even though unique random is generated every time.

In such cases how to fetch random rows? SQL Server provides one more mechanism to access random rows. NewID(), which returns a GUID (Globally Unique Identifier).

SELECT TOP <n>
FROM <table>
WHERE <criteria>
ORDER BY NEWID()

select top 5 newid() as val, productid, productname from Product order by val

ScreenShot3

The key is the use of NEWID() function that returns a GUID. Results will differ, since NewID() manufactures a GUID each time it is called. Therefore, if you run the above command two or three times, you will get different result sets.

Enjoy….

Advertisements

Posted in SQL Server | Tagged: | Leave a Comment »

ASP.NET 4.0 Client ID Feature

Posted by Virendra Dugar on April 22, 2009

Hi All,

Check out this article on ASP.NET 4.0 Client ID Feature enhancement…..

Looks really cool….

Posted in ASP.NET, ASP.NET 4.0 | Tagged: | 2 Comments »

Misconception Regarding ViewState in ASP.NET

Posted by Virendra Dugar on April 16, 2009

Common misconception that ViewState holds values for controls such as TextBoxes.

Most ASP.NET developers think that the ASP.NET ViewState is responsible for holding the values of controls such as TextBox so that they are retained even after postback. But this is not the case.

Let’s understand with an example.

Place a web server TextBox control, an HTML server text box control (normal HTML input box with runat=server), a normal HTML input control(without runat=server), and a web server Label control.

Set the Text/Value property to “Initial Text” for the three TextBoxes, and set the Text property of the Label control to “Initial Label”.

Set the EnableViewState property to false for the first text box and the last controls (Label).

Now place two Button controls and set their text as “Change Label’s Text” and “Postback to Server”. On the button click event handler of the first button, write the following code:

private void btnChangeLabel_Click(object sender, System.EventArgs e)
{
   lblTest.Text = "Label's Text Changed";
}

There is no code on the second button’s Click event. It just submits the page to the server.

Now run this application. You can see the initial texts in the controls as you have set.

Now, change the text in all TextBoxes and set them to “Changed Text”. Now click the Post to Server button. What happens is that the first two textboxes retain their values, in spite of the ViewState property being set to false. But the last textbox, which is a simple HTML input control, loses the modified value when the page is reloaded.

Most developers would have expected all three textbox controls to lose their modified values (“Changed Text”), and after page re-loading, they expect “Initial Value” being written on all textboxes as we had disabled the ViewState.

The reason for this behavior is that ViewState is not responsible for storing the modified values for controls such as TextBoxes, dropdowns, CheckBoxList etc., i.e., those controls which inherit from the IPostBackDataHandler interface. After Page_Init(), there is an event known as LoadViewState, in which the Page class loads values from the hidden__VIEWSTATE from the field for those controls (e.g., Label) whose ViewState is enabled. Then the LoadPostBackData event fires, in which the Page class loads the values of those controls which inherit from the IPostBackDataHandler interface, (e.g., TextBox) from the HTTP POST headers.

Now, start the application again, and this time, click the “Change Label’s Text” button. When the page reloads, you will see that the programmatic change (made by our code in the event handler of the button’s Click event) was lost, i.e., we don’t see the Label’s text changed to “Label’s Text Changed”. Instead, we see the initial value of the Label again. This is because the Label control does not inherit from the IPostBackDataHandler interface. So the ViewState is responsible for persisting its value across postbacks, and since it has been disabled, the Label loses its value after clicking the “Change Label’s Text” button.

Now enable the ViewState for the Label control, and you can see the modified value (“Label’s Text Changed”) after clicking the same button.

So we conclude that controls which inherit from the IPostBackDataHandler interface will retain their values even if the ViewState has been disabled, as their values are stored in HTTP POST headers.

Click here to view the original version by Vivek Thakur.

Enjoy….

Posted in ASP.NET | Tagged: | 2 Comments »

Retrieve Table Name from all the Procedures of any database

Posted by Virendra Dugar on April 13, 2009

In Sql Server 2000 or later versions, 

Suppose you have deleted one of the column of any table and now you have to change all your procedures where this column is used.

1. One way is to open every procedure and look for the table and the column name.

2. Second solution is the

Select * from information_Schema.Routines where Routine_Definition like ‘%table1%’

This query will list out all the procedures in which table name specified in where conditoin is used.

INFORMATION_SCHEMA.ROUTINES view is used to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database. 

The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

Enjoy..

Posted in SQL Server | Tagged: | Leave a Comment »

SessionID changes with every request in the asp.net 2.0 application

Posted by Virendra Dugar on April 9, 2009

For any web programmer, its obvious to think and believe that SessionId remains same through out the user session and it was right till asp.net1.1. But in asp.net2.0, this behavior has changed. In the asp.net application new sessionid is returned with the response to every request until session objects are not used.

According to MSDN the reason/solution is:

“When using cookie-based session state, ASP.NET does not allocate storage for session data until the Session object is used. As a result, a new session ID is generated for each page request until the session object is accessed. If your application requires a static session ID for the entire session, you can either implement the Session_Start method in the application’s Global.asax file and store data in the Session object to fix the session ID, or you can use code in another part of your application to explicitly store data in the Session object.”

Click here to view the article.

Enojy…

Posted in ASP.NET | Tagged: | 1 Comment »

Exploring session in ASP.NET

Posted by Virendra Dugar on April 3, 2009

This article describe about session in ASP.Net 2.0. It also explains about different types of session, their configuaration.

Enjoy…..

Posted in ASP.NET | Tagged: | Leave a Comment »

 
%d bloggers like this: