The Code Captain

Recovery Models in SQL Server

2011-12-05T22:45:00.000-08:00

Here we are going to discuss about database recovery model in SQL Server.

Recovery Models:
1. Simple
2. Bulk Logged
3. Full

Simple Recovery Model:
a)All the operations are not logged in Log file.
b)Log file size won't grow much.
c)Suitable for LOCAL and STAGE Environment.
d)Can't recover the database to certain point in time state.

Bulk Logged Recovery Model:
a)Only minimal information logged in log file during BULK operation. So, its also called as "Minimal Logging"
b)Log File won't grow much during bulk operation.
c)Suitable during bulk operation, migration.
d)Can't recover the bulk operation data.

Full Recovery Model:
a)All the operation logged in log file.
b)Log file size grow rapidly depends upon transactions.
c)Suitable for PRODUCTION environment.
c)Can recover the database to certain point in time (if we followed proper backup plan).

Here is script to switch to different recovery model.

 ALTER DATABASE testdb SET RECOVERY SIMPLE  
 GO  
 ALTER DATABASE testdb SET RECOVERY BULK_LOGGED  
 GO  
 ALTER DATABASE testdb SET RECOVERY FULL  
 GO

Rebuilding and Reorganizing Indexes based on Fragmentation in SQL Server

2011-12-01T09:19:00.000-08:00

Why we have to Rebuild and Reorganize the indexes?
As the time passes, data pages are scattered/fragmented and not in order due to insert, update and delete operation. So, its better the rebuild / reorganize the indexes to improve the query performance. Otherwise, query response would be slow because its would take more time to collect the data's which is scattered.

Rebuilding Indexes:
1. Its nothing but dropping and creating the indexes.
2. More CPU resources need.
3. Deadlock would happen while rebuilding the indexes.

Reorganizing Indexes:
1. Rearranging the leaf pages in correct order.
2. Less CPU resources need.
3. Deadlock possibility is very less.

When to use REORGANIZE and REBUILD?
Generally if fragmentation percentage for each index is from 5 - 30 percent, reorganize the index. if fragmentation percentage is more than 30 percent, its better to REBUILD the index.

SQL Script to REORGANIZE and REBUILD each index in each table:

 DECLARE @ReOrgLowPercent INT,  
   @ReOrgHighPercent INT,  
   @TableName VARCHAR(200)  
 SET @ReOrgLowPercent = 5  
 SET @ReOrgHighPercent = 30  
 DECLARE @IndexTable TABLE ( AlterStatement VARCHAR(4000) )  
 DECLARE C CURSOR  
   FOR SELECT name  
     FROM  sys.tables  
 OPEN C  
 FETCH NEXT FROM C INTO @TableName  
 WHILE ( @@FETCH_STATUS = 0 )  
   BEGIN  
     --PRINT @TableName  
     INSERT INTO @IndexTable ( AlterStatement )  
         SELECT CASE when avg_fragmentation_in_percent between @ReOrgLowPercent and @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REORGANIZE;'  
                when avg_fragmentation_in_percent > @ReOrgHighPercent  
                then 'ALTER INDEX ' + i.name + ' ON ' + t.name  
                  + ' REBUILD WITH(ONLINE=ON);' --- enterpise version obly  
             END  
         FROM  sys.dm_db_index_physical_stats(DB_ID(),  
                             OBJECT_ID(@TableName),  
                             NULL, NULL, NULL) AS a  
             JOIN sys.indexes AS i ON a.object_id = i.object_id  
                          AND a.index_id = i.index_id  
             JOIN sys.tables AS t ON t.object_id = i.object_id  
     FETCH NEXT FROM C INTO @TableName  
   END  
 CLOSE C  
 DEALLOCATE C  
 SELECT AlterStatement  
 FROM  @IndexTable  
 WHERE  
      AlterStatement IS NOT NULL

SQL Server 2012 - Improved Version of EXECUTE keyword

2011-11-25T02:21:00.000-08:00

In Previous version of SQL Server 2005/2008, if you execute the SELECT Stored Procedure using EXECUTE keyword, it will return all the records as it is. if you want to edit the column name and data type of result set, you have to modify the stored procedure respectively.

But in SQL Server 2012, improved version of EXECUTE keyword allows to modify the result set with changing the column name and data type without modifying the stored procedure.

To explore this features, execute the following script to create a table, insert the record and creating the stored procedure.

 CREATE TABLE Customers  
 (  
      ID INT IDENTITY(1,1),  
      FirstName VARCHAR(50),  
      LastName VARCHAR(50),  
      DOB DATE  
 )  
 GO  
 INSERT INTO Customers (FirstName, LastName, DOB)  
 SELECT 'Ayyanar', 'Jayabalan', '08/12/1982'  
 UNION ALL  
 SELECT 'Greg', 'Damico', '08/12/1970'  
 GO  
 CREATE PROCEDURE Customers_SelectAll  
 AS  
 BEGIN  
      SELECT  
           ID,  
           FirstName,  
           Lastname,  
           DOB  
      FROM dbo.Customers  
 END  
 GO

In SQL Server 2005/2008:

 -- SQL 2005/2008  
 EXECUTE Customers_SelectAll

RESULT:

In SQL Server 2012:
Here i am changing the column "id" into "CustomerId" and also changing the data type of firstname from VARCHAR(50) to VARCHAR(4). Since i reduced the length of FirstName to 4, it shows truncated firstname value.

 -- SQL 2012  
 EXEC Customers_SelectAll  
 WITH RESULT SETS  
 (  
 (  
      CustomerID SMALLINT,  
      FirstName VARCHAR(4),  
      Lastname VARCHAR(20),  
      DateOfBirth DATE  
 )  
 );

RESULT:

SQL Server 2012 - Throwing Exception using THROW in TRY CATCH BLOCK instead of using RAISERROR

2011-11-25T01:39:00.000-08:00

In SQL Server 2005/2008, if you want to throw an error again in catch block of TRY CATCH statement, you have to use RAISERROR with ERROR_MESSAGE(), ERROR_SEVERITY().

In SQL Server 2005/2008:

 -- EXCEPTION HANDLING in SQL Server 2005/2008  
 BEGIN TRY  
   DECLARE @VALUE INT  
   SET @VALUE = 1 / 0  
 END TRY   
 BEGIN CATCH  
   DECLARE @Message nvarchar(4000), @Severity int  
   SELECT @Message = ERROR_MESSAGE(), @Severity = ERROR_SEVERITY()  
   RAISERROR ( @Message, @Severity, 1 )  
 END CATCH

SQL Server 2012:
Just, you can use THROW keyword to throw the complete error information.

 -- EXCEPTION HANDLING using THROW in SQL Server 2012  
 BEGIN TRY  
      DECLARE @VALUE INT  
      SET @VALUE = 1/0  
 END TRY   
 BEGIN CATCH  
      THROW  
 END CATCH

if you execute the above statement, you will receive an error like below.

 Msg 8134, Level 16, State 1, Line 5  
 Divide by zero error encountered.

Custom Paging in SQL Server 2012 - Using OFFSET

2011-11-24T20:12:00.000-08:00

In SQL Server 2005/2008, if you have want to implement the custom paging for ASP.Net GridView or any other controls, you have use either COMMON TABLE EXPRESSIONS with ROW_NUMBER() or just ROW_NUMBER() keyword.

CUSTOM PAGING IN SQL SERVER 2005/2008:

 -- CUSTOM PAGING IN SQL SERVER 2005   
 USE AdventureWorks  
 DECLARE @PageIndex SMALLINT, @PageSize SMALLINT, @StartRowId INT, @EndRowId INT  
 SET @PageIndex = 1  
 SET @PageSize = 10  
 SET @StartRowId = ( (@PageIndex - 1) * @PageSize) + 1  
 SET @EndRowId = (@StartRowId + @PageSize) - 1  
 SELECT      *  
 FROM (  
 SELECT ROW_NUMBER() OVER(ORDER BY EmployeeId) AS RowId, *  
 FROM HumanResources.Employee) AS TT  
 WHERE RowId >= @StartRowId and RowId <= @EndRowId

CUSTOM PAGING IN SQL SERVER 2012 using OFFSET:
In SQL Server 2012, by using the OFFSET feature, we can implement the custom paging easily without having too many SQL statements.
Just you have to specify starting row id and page size like below.

 OFFSET <Offset> ROWS  
 FETCH NEXT <PageSize> ROWS ONLY;

Here is the complete T-SQL statement for custom paging.

 -- CUSTOM PAGING IN SQL SERVER 2012 RC0  
 DECLARE @PageIndex SMALLINT, @PageSize SMALLINT, @Offset INT  
 SET @PageIndex = 1  
 SET @PageSize = 10  
 SET @Offset = ( (@PageIndex - 1) * @PageSize)  
 SELECT      *  
 FROM HumanResources.Employee  
 ORDER BY EmployeeId  
 OFFSET @Offset ROWS  
 FETCH NEXT @PageSize ROWS ONLY;

SQL Server 2012 Features - Sequence Number

2011-11-22T05:31:00.000-08:00

Today,i have installed to explore the features of SQL Server 2012. First feature which i am going to discuss is "Sequence Number". You can generate sequence number like identity column. In Previous release, if you want to generate a sequence id before inserting into the table, you have to create a seperate physical table with identity column. Before inserting record into table, we have to insert a row in custom sequence table and get the latest value.

But in SQL Server 2012, you no need to insert record in any physical custom table to retrieve the number in sequence. Just you have to create sequence object which binds to schema.

Here are the steps to create Sequence object using SQL Server Management Studio.

1. Create a New Sequence:

2. Specify the NAME, MIN, MAX, CYCLE and OTHER PROPERTIES:

3. Once you entered the values for sequence object. You have to use following query to get NEXT sequence number using NEXT VALUE FOR keyword.

 select NEXT VALUE FOR dbo.GlobalSequence;

RESULT:

If you execute above query, you will get the value as 1. Next time, if you executing same query, you will get the value as 2. Same way, you would get the number in sequential for every execution.

4. With CYCLE OPTION:

 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;

if you execute the above query, you would get values like below.

if sequence value reaches maximum value, it will start again with start value if you enabled the CYCLE option. Otherwise, it will throw an error like below;

 The sequence object 'GlobalSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

SQL Server 2012 - Testimonials

2011-10-25T02:59:00.000-07:00

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

SQL SERVER – SHRINK Log File using SHRINKFILE

2011-10-24T22:54:00.000-07:00

if you want to shrink the log file, you would use SHRINKFILE command like.

DBCC SHRINKFILE(TestDBLog, 1)
GO

But in some cases (like production database), if you shrink log file by using above command, you won't get expected result. So, if you want to shrink log file to minimum size, take backup (*.bak) of entire database with SIMPLE recovery mode and then run the shrink file command.

Here is the code to backup the database and shrink the log file

BACKUP DATABASE [TestDb] TO DISK = N'C:\TestDb.bak'
GO
DBCC SHRINKFILE(TestDBLog, 1)
GO

Implementing IF..ELSE.. condition in Selenium IDE using Flow Control Add-On

2010-11-26T08:33:00.000-08:00

Here I am going to discuss about on how to implement if else condition in selenium IDE.We can't implement the IF ELSE condition using core selenium IDE. To implement IF ELSE logic, you have to install Firefox "Flow Control" Add On. So, please install the "Flow Control" Add On using below URL.

https://addons.mozilla.org/en-US/firefox/addon/85794/

After installed, restart the firefox browser. Now, Flow Control commands available in the selenium IDE.

Here are the commands:

COMMAND| TARGET |VALUE
======================================================
gotoif| CONDITION |LABELNAME
label | LABELNAME|
gotolabel | LABELNAME

Here is the example that i have used:

From the above screenshot, first i am checking whether text("Please select") present in the page using "storeTextPresent" and assign the result to variable "POPUP_EXISTS". Then i am checking whether result is "false" or not. if the result is "false", then skip the next statement and move the execution to "target1"(label). Otherwise, continue the execution to next statement.

Clearing ASP.Net FileUpload Control using jQuery

2010-08-30T04:51:00.000-07:00

Here i am going to discuss about on how to clear the file upload control using jQuery. To clear file upload control, you have to place file upload control within the DIV tag like below.

HTML CODE:

jQuery Custom Function:

// Clearing text of file upload control by means of replacing with HTML content.
// you can place this code in your JS file.
$.ClearFileUpload = function (fileUploadControlId) {
try {
document.getElementById(fileUploadControlId).innerHTML = document.getElementById(fileUploadControlId).innerHTML;
$(fileUploadControlId).focus();
} catch (e) {
//alert(e.Description);
}
}

Calling jQuery Custom Function from ASP.Net Design View:

Munnar Trip with company collegues

2010-04-12T04:39:00.000-07:00

You can see more photos here http://picasaweb.google.com/ayyanarj/MunnarTrip

Software Testing Best Practices

2010-01-10T23:18:00.000-08:00

Here is the list of best practice tips:

1. Analyze the requirement and problem thoroughly. If not clear, please ask development team lead or Project Manger.

2. Test the application by breaking application into smaller modules.

3. Write the test cases with all possible test data.

4. Always try to finding the bugs in the application. Don’t assume that it will work.

5. Test cases should be available to developer before development starts on that item. So that time consumption will reduce.

6. Prioritize the test cases for all the modules. So that, whenever testing the application you can have checklist.

7. Programmers won’t do the testing completely because they will think that their code is always right. But its tester responsibility to find the loop holes.

8. Don’t stick to requirement on testing and go beyond that.

9. Regression testing is more important. Please have history of bugs related to those modules. So, you can find the bugs easily.

10. Sometimes we will change the application environment for testing. Tester should have knowledge on where we are doing the changes for testing. So, tester should verify those areas after moved to production.

11. Tester should be kept away from developer environment. So that, tester wont gets distracted.

12. Testers should have regular meeting everyday on sharing the latest knowledge and their experience.

13. Have more communication with development lead or developer to avoid misunderstandings and making the application without bugs.

14. Always try to communicate through email.

15. Bug description should be clear, informative. Always, suggest your solution.

16. Testers should be involved right from the requirement phase of the application.

17. Update your testing knowledge daily by reading articles and discussion with others.

18. Performance testing needs to be done if more users using the web application.

19. Monitor your testing knowledge daily or weekly. So that, you came to know what you learned from today, what you have improved on your testing and what needs to be done to achieve to more.

20. Make sure that you are following above practices and other good practices.

Modal and Modalless Popup Window Using jQuery and Thickbox in ASP.Net - Part II

2009-02-03T21:21:00.000-08:00

In Part I, i have explained how to open image and ASPX page as popup in parent page itself.

In this part, i am going to explain how to open panel or div as popup in same page.

HTML:

<a href="#TB_inline?inlineId=popupdiv&height=120&width=320" class="thickbox" >open child window</a>
</div>

<div id="popupdiv" style="display:none">

<table style="width: 30%">
<tr>
<td >
Comments :</td>
<td>
<asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine"></asp:TextBox></td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Save" /></td>
</tr>
<tr>
<td >
</td>
<td >
</td>
</tr>
</table>
</div>

you have specify href with following important property.

#TB_inline - indicates popup content present in same page.

inlineId - panel or div id that should show when clicked that link.

And also, you have to specify CSS class as "thickbox" .

Screenshot:

In next part, i will explain how to open popup window dynamically without having CSS class as "thickbox".

Modal and Modalless Popup Window Using jQuery and Thickbox in ASP.Net - Part I

2009-02-03T05:18:00.000-08:00

Here I am going to explain on how to open the popwindow using jQuery and Thickbox.

Traditional Method:

In ASP.Net, you will use javascript "window.open" method to open the popup window like.

window.open('popup.aspx','','height=220,width=500');

The above method will open a popup.aspx page as popup with respective width and height.

Cons:
1. No tight linkage between parent and child window(no clarity between parent and child window).
2. We can close the popup window without doing any operation.
3. Open as seperate window instead of in same page.

jQuery & THICKBOX:

we can avoid those cons by using jQuery and Thickbox.
For that, first we have to download the jQuery and THICKBOX js file from respective website

jQuery : http://jQuery.com
THICKBOX : http://jquery.com/demo/thickbox/

After downloaded, you have to link those js file within HTML head tag of your ASPX page like below.

<style type="text/css" media="all">@import "Assets/css/thickbox.css";</style>

<script language="javascript" src="Assets/js/jquery-1.2.6.min.js"></script>
<script language="javascript" src="Assets/js/thickbox.js"></script>

Simple Example:
To open the image as Popup, here is the HTML code.

<a href="images/DSCN0365.JPG" alt="click here" class="thickbox"><img src="images/DSCN0365.JPG" /></a>

you have to specify class as thickbox for anchor tag to open an image in same page.

when you click the link, it will show like below.

Open seperate page as Popup window:

To open seperate page as popup window, here is html code.

<a href="childwindow.aspx?TB_iframe=true&height=250&width=400" class="thickbox" >open child window</a>

you have to specify CSS class as "thickbox" to open childwindow.aspx as popup in same page.

href="childwindow.aspx?TB_iframe=true&height=250&width=400"

TB_iframe=true -- says popup page is seperate page.
width, height -- says size of popup window.

if you click the anchor link, it will show the child window like below.

This is modalless popup window. i.e. you can close the popup window by clicking close link in popup window OR pressing ESC key.

Modal Popup:
Even, you can show the modal popup window by adding another property of "modal=true" in anchor href attribute value like below

<a href="childwindow.aspx?TB_iframe=true&height=250&width=400&modal=true" class="thickbox" >open child window</a>

if you click the above link, it will show without close link in popup window like below.

Now you can't close the popup window without pressing save button.

In next part, i will explain how to open panel in same page as popup.

C# Code Header Designer

2009-02-02T21:25:00.000-08:00

Here i am going to discuss about C# code header designer. while developing the project or product, developer needs to add header to C#/VB code file depending upon the company standards.

To add header, first we will create header for one C# code file and then do the copy and paste of header with small changes for other code files.

it's time consuming process to do the copy and paste for each and every file. Even some of the company not having header so far, but if they want to add header for all projects that they were handled.it would take more time to complete.

To avoid those difficulties and time consumption, msdn developer developed one tool to add headers easily.

Screenshot:

Header Template:

/* ----------------------------------------------------------------
* [COMPANY NAME]
* ----------------------------------------------------------------
* [PRODUCT NAME]
* ----------------------------------------------------------------
* File: [AUTO:FILE]
* Author: [AUTO:AUTHOR]
* ----------------------------------------------------------------
*/

By using, we can created the template for header and configure the values for keys in template.After template is prepared, PRESS GO button to add the header to code files.

C# CODE FILE:

/* ----------------------------------------------------------------
* SPi Technologies
* ----------------------------------------------------------------
* SPiRE
* ----------------------------------------------------------------
* File: Server.cs
* Author: SPIDOM\JAyyanar
* ----------------------------------------------------------------
*/

Click here to download this tool, source code and user guide.

Intellisense for jQuery - Visual Studio 2008

2009-01-27T02:14:00.000-08:00

Here i am going to explain on how to enable intellisense for jQuery in VS 2008.

Currently VS 2008 installation pack does not come with jQuery Intellisense. MS has plan to providing intellisense for VS 2010.

For VS 2008, MS release Hotfix for jQuery Intellisense.

Here are the steps:

Step 1:

Download hotfix and install it.

Step 2:

Download jQuery core js and doc file.

Step 3:

Add those 2 JS files in Web Site Project in same location and link those JS files in ASPX file.

After you have refered those 2 files in ASPX file, it will automatically enable Intellisense for jQuery.

CODE:

jQuery:

$(function()
{
$("#TextBox1").bind("focus", function(event)
{
if ($(this).val() == this.defaultValue)
$(this).val("");
}
);

$("#TextBox1").bind("blur", function(event)
{
if ($(this).val() == "")
$(this).val(this.defaultValue);
}
);
}
);

HTML:

First Name : <asp:TextBox ID="TextBox1" runat="server" Text="Enter First Name"></asp:TextBox>

See the screenshot:

C# 4.0: Optional and Named Parameters - CTP Release

2008-11-01T22:35:00.000-07:00

C# Chief Architect Anders Hejlsberg explained the future of C# in PDC2008.Initial CTP release of C# 4.0 has a feature of Optional and Named Parameters.

I have already explained the another featire of C# 4.0 is Dynamic Programming in my previous post.

Here I am going to explain about Named and Optional Parameters.

C# 1.0, 2.0, 3.0 doest not have a feature of Optional parameter. But in VB, we can define a method with an optional parameters.

Developers facing some difficulties of not having an optional parameter in C# especially when communicating with COM object.

C# 4.0 going to have a feature of Optional Parameter.

In previous version of C#, we have to implement multiple overloaded methods to have a variable parameters in the function like

C# 3.0:

public int Add(int x, int y)
{
return x + y;
}

public int Add(int x, int y, int z)
{
return x + y + z;
}

C# 4.0 (Optional Parameter):

public int Add(int x, int y, int z = 0)
{
return x + y + z;
}

//calling
Add(1,2);
Add(1,2,3

Here we can declare the parameter as an optional by initializing the value to the variable.

if we have multiple optional parameter in a function, we have to initialize the value sequentially in VB like

VB:

Public Function Add(ByVal x As Integer, ByVal y As Integer, Optional ByVal z As Integer = 5, Optional ByVal a As Integer = 10) As Integer

Return x + y + z + a

End Function

From the above code, it has 2 optional parameter. we can't pass value to parameter "a" without passing value for parameter "z".

// Calling
Add(1, 2, 5, 15)

C# 4.0 - Named Parameter
public int Add(int x, int y, int z = 5, int a = 10)
{
return x + y + z + a;
}

//calling
Add(1,2,a:15);

From the above code, it has 2 optional parameter. But here we can pass a value to parameter "a" without passing a value for "z" by using name of parameter like a:15.

C# 4.0 : Dynamic Programming -- CTP Release

2008-11-01T20:49:00.000-07:00

C# Chief Architect Anders Hejlsberg explained the future of C# in PDC2008.Initial CTP release of C# 4.0 has some features like

1. Dynamically Typed Objects.
2. Optional and Named Parameters
3. Improved COM Interoperability.
4. Co and Contra Variance.

Here I am going to explain about Dynamic Programming.

Dynamically Typed Objects:

Here is the code for traditional way of calling typed object methods.

EmployeeInfo eInfo = new EmployeeInfo();
eInfo.EmployeeID = 1;
eInfo.FirstName = "Ayyanar";
eInfo.LastName = "Jayabalan";

Employee emp = GetEmployee();
bool affected = emp.AddEmployee(eInfo);

From the above code, we know the type "Employee" to call the AddEmployee methods.

if we does not know the Type("Employee"), we can use Reflection to call the AddEmployee method.

Here is the code to call the method using Reflection.

object refEmp = GetEmployee();

Type type = refEmp.GetType();

object objAffected = type.InvokeMember("AddEmployee", System.Reflection.BindingFlags.InvokeMethod, null, null, new object[] { eInfo });

bool affected = (bool)objAffected;

C# 4.0:
-------

We can call method directly without using Reflection for UnKnown Typed object.

dynamic emp = GetEmployee();

bool affected = emp.AddEmployee(eInfo);

We have to declared as a "dynamic" for unknown typed objects.

Method calling decision taken at RUNTIME instead of COMPILE TIME.

And also, .Net does not throw error while compiling the code and does not provide INTELLISENSE for dynamic type.

Microsoft Azure Services Platform

2008-11-01T06:13:00.000-07:00

The Azure™ Services Platform (Azure) is an internet-scale cloud services platform hosted in Microsoft data centers, which provides an operating system and a set of developer services that can be used individually or together. Azure’s flexible and interoperable platform can be used to build new applications to run from the cloud or enhance existing applications with cloud-based capabilities. Its open architecture gives developers the choice to build web applications, applications running on connected devices, PCs, servers, or hybrid solutions offering the best of online and on-premises.

Click here to know more about Azure.

What is the Azure Services Platform?

Why use the Azure Services Platform?

Visual Studio 2010 and Framework 4.0 - CTP Release

2008-11-01T05:15:00.000-07:00

Microsoft released the CTP version of Visual Studio 2010 and Framework 4.0.

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

Next Generation Developer Tool : Visual Studio 2010 and .Net Framework 4.0

2008-10-26T09:29:00.000-07:00

Visual Studio 2010 and the .NET Framework 4.0 mark the next generation of developer tools from Microsoft. Designed to address the latest needs of developers, Visual Studio and the .NET Framework deliver key innovations in the following pillars:

Democratizing Application Lifecycle Management
Application Lifecycle Management (ALM) crosses many roles within an organization and traditionally not every one of the roles has been an equal player in the process. Visual Studio Team System 2010 continues to build the platform for functional equality and shared commitment across an organization’s ALM process.
Enabling emerging trends
Every year the industry develops new technologies and new trends. With Visual Studio 2010, Microsoft delivers tooling and framework support for the latest innovations in application architecture, development and deployment.
Inspiring developer delight
Ever since the first release of Visual Studio, Microsoft has set the bar for developer productivity and flexibility. Visual Studio 2010 continues to deliver on the core developer experience by significantly improving upon it for roles involved with the software development process.
Riding the next generation platform wave
Microsoft continues to invest in the market leading operating system, productivity application and server platforms to deliver increased customer value in these offerings. With Visual Studio 2010 customers will have the tooling support needed to create amazing solutions around these technologies.
Breakthrough Departmental Applications
Customers continue to build applications that span from department to the enterprise. Visual Studio 2010 will ensure development is supported across this wide spectrum of applications.

Click here to know more.

Solution: Session Timeout not working on Production Server in ASP.Net

2008-06-25T03:05:00.000-07:00

Problem:

You may faced the session timeout problem on production web server after deployed the ASP.Net Web Application. Even after you have changed the Web.Config setting like

<sessionState mode="InProc" cookieless="false" timeout="300"/>

Session will get lost after default session timeout duration (20 Minutes).

You may also have tried to change the default session timeout value in IIS (Internet Information Server).

In all the above cases, session timeout value is lost after 20 Minutes (default session timeout value).

Solution:

Here is the solution to maintain the session for long period (above 20 minutes) on Production Web Server. To maintain the session, we have to refresh the website atleast before 20 minutes (e.g every 10 minutes). To refresh the website, here is the tricky solution.

ASP.Net 2.0 Web Project:

Instead of refreshing each ASPX page, we can achieve this with single webform.
Add a new webform (RefreshASPXToKeepSessionAlive.aspx) in the web project. Add the metatag information within HEADER of this web page.
E.g.

<head runat="server" >
<meta http-equiv="refresh" content="600" />
<title>Poll this page from master page to keep session alive</title>
<head>

The above META tag information says refresh the page for every 600 seconds (10 Minutes).

Master Page:

You have to call the above created page from MASTER PAGE. you can call this without affecting other web forms master design, use the hidden iframe logic like.

You have to use above iframe HTML tag outside the content placeholder.

The above technique refresh the RefreshASPXToKeepSessionAlive.aspx page from master page for every 20 Minutes. So, website will maintain the session for long period (greater than 20 Minutes) without losing.

if you got any problem on using the IFRAME tricks, add the iframe tag dynamically in HTML view instead of adding that in design time.

E.g.

<%
if (Session["IsUserLogged"] != null && ((bool)Session["IsUserLogged"]) == true)
{
Response.Write("<iframe src ='RefreshASPXToKeepSessionAlive.aspx' style ='display:none'></iframe>");
}
%>

Source Code Analysis for C# - StyleCop

2008-05-31T03:59:00.000-07:00

Microsoft released the tool for source code analysis for C#.Currently MS developed this only for C# not for VB. Microsoft using this for their internal purpose also. They have named this as "StyleCop". Earlier, MS released the tool called "FxCop" that analyse the compiled binaries but "StyleCop" analyse the source code. It provides the set of rules for style, documentation ,readability, etc. Currently they have defined around 200 rules. Those rules are not Customizable.But you switch ON/OFF the rules depending on your needs or company standard.

They have covered the following catagory rules:

1. Documentation
2. Layout
3. Maintainability
4. Naming
5. Ordering
6. Readability
7. Spacing

Screenshot of rules in StyleCop:

Screenshot of using this tool in application:

The ultimate goal of the source code analysis tool is allow you to give clean , consistant code with team members and for others who view your code with more readability.

Click here to download the Source Code Analysis Tool

Official Trip to US

2008-03-11T22:33:00.000-07:00

Here are the pictures that took at Austin, TX

click this link to see more photos.. http://picasaweb.google.com/ayyanarj

Caching Sql Parameters in C#.Net

2007-12-26T11:02:00.000-08:00

Here I am going to discuss about how to cache the sql parameters.

Suppose if you want to add records to the table, either you use Direct-SQL and Stored Procedure. Stored Procedure is compiled one so it’s faster than Direct-SQL.

If you are using stored procedure, you call the stored procedure from the client side either by EXEC Or Parameterized call.

Parameterized call reuse the existing execution plan instead of creating the new execution plan every time when you call using EXEC.

So, Parameterized call is more efficient than EXEC.

If you are using Parameterized call, you have to create the array of Sql Parameters to pass the parameter value. You have to create the array of parameters every time when you call the stored procedure.

If you are calling 100 times, 100 times you have to create the array of parameters.

Cache:

Instead of creating the parameters every time, you can the cache the array of sql parameters in the first call. In next call, you can get the Clone of Sql Parameters from Cache. And then assign the values to the clone sql parameter.

Microsoft released the Enterprise Library having the parameter caching mechanism.

Here I am going to discuss the parameter caching is similar to that in a simple way.

Parameter Cache Code:

public class ParameterCache
{

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

// Create and return a copy of the IDataParameter array.
private static IDataParameter[] CloneParameters(IDataParameter[] originalParameters)
{
IDataParameter[] clonedParameters = new IDataParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (IDataParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

// Empties all items from the cache
public static void Clear()
{
paramCache.Clear();
}

// Add a parameter array to the cache for the command.
public static void AddParameterSetToCache(string connectionString, string storedProcedure, IDataParameter[] parameters)
{
string key = CreateHashKey(connectionString, storedProcedure);
paramCache[key] = parameters;
}

// Gets a parameter array from the cache for the command. Returns null if no parameters are found.
public static IDataParameter[] GetCachedParameterSet(string connectionString, string storedProcedure)
{
string key = CreateHashKey(connectionString, storedProcedure);
IDataParameter[] cachedParameters = (IDataParameter[])(paramCache[key]);
return CloneParameters(cachedParameters);
}

// Gets if a given stored procedure on a specific connection string has a cached parameter set
public static bool IsParameterSetCached(string connectionString, string storedProcedure)
{
string hashKey = CreateHashKey(connectionString, storedProcedure);
return paramCache[hashKey] != null;
}

// create the hash key based on connectionstring and stored procedure name
private static string CreateHashKey(string connectionString, string storedProcedure)
{
return connectionString + ":" + storedProcedure;
}

}

The above shared class is for caching and accessing the cached parameters.

HashTable – Used to store the array of parameters based on hash key. It is used as a cache.

CreateHashKey – Caching the Sql parameters based on hash key formed from connectionstring and stored procedure name.

AddParameterSetToCache – Cache the parameters in HashTable based on connectionstring and stored procedure name.

CloneParameters – Get the clone of cached parameters.

GetCachedParameterSet – Get the parameters from the HashTable(Cache) based on HashKey.

IsParameterSetCached – Used to check whether parameters already cached based on hashkey.

Clear – Clear the HashTable(Cache)

Customer Class:

public class Customer
{

#region C# 3.0 Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

}

How to use ParameterCache shared class:

//Add the Customer Information to Customer Table
public void AddCustomer(Customer cust, string connectionString)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlParameter[] param = null;
string spName = "AddCustomer";

try
{
conn = new SqlConnection(connectionString);
cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;

param = GetParameters(cust, connectionString, spName);

conn.Open();
cmd.ExecuteNonQuery();

}
catch (Exception)
{
throw;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();

if (cmd != null)
cmd.Dispose();

param = null;
}

}

The above method is to add the records to the customer table. For that you have to prepare the parameters for stored procedure. GetParameters() method used to prepare the parameters.

// Prepare the Sql Parameters for Adding Customer
// Get the Clone parameters if already cached based on connectionstring and store procedure name
// Otherwise create the new Sql parameter array and then add to cache for next time use.
private SqlParameter[] GetParameters(Customer cust, string connectionString, string storeProcedure)
{
SqlParameter[] param = null;

if (ParameterCache.IsParameterSetCached(connectionString, storeProcedure))
{
param = (SqlParameter[])ParameterCache.GetCachedParameterSet(connectionString, storeProcedure);

param[0].Value = cust.CustomerID;
param[1].Value = cust.Name;
}
else
{
param = new SqlParameter[2];

param[0] = new SqlParameter("@CustomerID", SqlDbType.BigInt, 8);
param[0].Value = cust.CustomerID;
param[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[1].Value = cust.Name;

ParameterCache.AddParameterSetToCache(connectionString, storeProcedure, param);
}

return param;
}

The above method is for returning array of sql parameters. First it checks the cache, if already exists then get the parameters from the cache. And then assign the values of clone sql parameters.
If not exists, create the new array of sql parameters and add prepared parameters into cache for next time use.

Only first time call, it takes some time to cache the parameters. Next call onwards prepare the sql parameters faster.

Conclusion:
You can get better performance on caching the sql parameters if you are calling the stored procedure frequently.

Parallel Extensions to .Net Framework - PLINQ

2007-12-25T04:48:00.000-08:00

Microsoft released the CTP version of Parallel Extensions to the .Net Framework.
With the use of parallel extensions, we can write the program that using MULTI core (Dual,QUAD,etc) processors.

Usually we write the program for single core processor, so our hardware resources are not fully utilized. But with the use of Parallel Extensions, we can utilize the hardware resources effectively.

You can download the CTP version of Parallel Extension here

Parallel Extensions provide several new ways to express parallelism in your code:

Declarative data parallelism(PLINQ) - Parallel Language Integrated Query (or Parallel LINQ) is an implementation of LINQ-to-Objects that executes queries in parallel, scaling to utilize the available cores and processors of the machine. Because queries are declarative, you are able to express what you want to accomplish, rather than how you want to accomplish it.

Imperative data parallelism - Parallel Extensions also contains mechanisms to express common imperative data-oriented operations such as for and foreach loops, automatically dividing the work in the loop to run on parallel hardware.

Imperative task parallelism - Rather than using data to drive parallelism, Parallel Extensions enables you to express potential parallelism via expressions and statements that take the form of lightweight tasks. Parallel Extensions schedules these tasks to run on parallel hardware and provides capabilities to cancel and wait on tasks.

Code(PLINQ):

1. Create the new Project from VS 2008.
2. Reference the System.Threading.dll from the Parallel Extension installation path.
3. Create the static class like the following.

class ValidNumbers
{
//Sequential Method
public static int SequentialValidNumberCount(IEnumerable<string> list)
{
return list.Where(n => IsNumber(n)).Count();
}

//Parallel Method
public static int ParallelValidNumberCount(IEnumerable<string> list)
{
return list.AsParallel().Where(n => IsNumber(n)).Count();
}

//validate the string whether its number
private static bool IsNumber(string input)
{
return !(Regex.IsMatch(input, "[^0-9]"));
}
}

In the Static class, we have declared the two methods for calculating the number of integers in the array.

SequentialValidNumberCount - This is sequential way of running the method. It uses single core.

ParallelValidNumberCount - This is Parallel way of running the method. AsParallel() is the extension method. It uses all the core in the processor.

Calling code:

string[] arr = { "1", "a", "2" };
int count = 0;

//Sequetial way of accessing
count = ValidNumbers.SequentialValidNumberCount(arr);

//Parallel way of accessing
count = ValidNumbers.ParallelValidNumberCount(arr);

Parallel Method runs faster than sequential method because it uses all the core in the processor. You can see the difference between two in the Task Manager Performance monitor.

Exception Hunter from Red Gate

2007-12-19T01:42:00.000-08:00

Redgate introduced the new tool called Exception Hunter. It hunts the exception in the code.

By using this tool, we can avoid unhandled exception or application crashes at run time. It anaylsis the code that you have written, generates the reports of possible exceptions will occur in the code.

So We can find the unhandle exception easily in the development cycle itself instead in QA cycle.

You can download it here

ASP.NET 3.5 Extensions CTP Preview

2007-12-18T08:44:00.000-08:00

Microsoft released the CTP version of ASP.Net 3.5 Extensions. It provides the added features to framework 3.5. you can download it here .

ASP.Net 3.5 Extensions contains:

1. MVC (Model View Controller):
Its provides clear seperation of presention and logic . And also it provides URL rerouting option.

2. Dynamic Data Support :
It provides faster creation of dynamic data driven websites.

you can create simple data driven websites like
1. Create Dynamic Data Web Application Project
2. Add LINQ To SQL Class Item to the project.
3. Drag the required tables from database into LINQ to SQL Class.
4. Set the EnableTemplates to True in Web.Config like
<dynamicData dataContextType="" enableTemplates="true">
5. Run the Application to view, add, edit and delete the table records.

3. Silverlight Controls:
It provides additional silverlight controls.

4. AJAX Enhancements:
It provides browser history support for the AJAX ASPX pages.

5. ADO.Net Entity Framework:
It prviodes entity framework that enables the deveopers to model the database closely to real world application.

C# 3.0 Extension Methods

2007-12-10T09:02:00.000-08:00

Microsoft introduced the cool and nice features of Extension Methods in C# 3.0.
You can add the methods to the any type like string, int, Collection or even customer class.

you have define and implement the extended methods in static class only and method also static.

Extension Class and Methods:

static class MyExtensionMethods
{

// this methods apply to all string type
public static bool IsNumber(this string input)
{
return !(Regex.IsMatch(input, "[^0-9]"));
}

// this methods apply to all type that derive from object type
public static bool IsExistsIn(this object obj, IEnumerable list)
{
foreach (object o in list)
{
if (o.Equals(obj))
return true;
}

return false;
}
}

Using Extension Methods:

string number = "45";
bool isNum = false;

// returns true
isNum = number.IsNumber();

// returns false
isNum = "45fg".IsNumber();

bool isExists = false;
string[] arr = { "Ayyanar", "Senthil", "Vaithy" };

// returns true
isExists = "Ayyanar".IsExistsIn(arr);

// returns false
isExists = "Ayya".IsExistsIn(arr);

Now we will see how to use this extension methods to custom class object.

Class:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

#region Override Methods

public override bool Equals(object obj)
{
Customer c = (Customer)obj;

if (c.CustomerID == this.CustomerID && c.Name == this.Name)
return true;
else
return false;
}

public override int GetHashCode()
{
return base.GetHashCode();
}

#endregion
}

Using Extension Method for Customer Object:

// C# 3.0 Collection Initializers

List<Customer> custList = new List<Customer>{
new Customer { CustomerID = 1, Name = "Ayyanar" },
new Customer { CustomerID = 2, Name = "Senthil" },
new Customer { CustomerID = 3, Name = "Vaithy" }
};

// C# 3.0 Object Initializers
Customer c = new Customer { CustomerID = 1, Name = "Ayyanar" };

// return true
isExists = c.IsExistsIn(custList);

C# 3.0 Collection Initializers

2007-12-09T23:20:00.000-08:00

C# 3.0 has a nice feature of creating collection of objects in a simple way like C# 3.0 object initializers.

Class Structure:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

}

Conventional Method:

// C# 3.0 object Initialization Method
Customer custmObj1 = new Customer { CustomerID = 1, Name = "Ayyanar" };
Customer custmObj2 = new Customer { CustomerID = 2, Name = "Senthil" };
Customer custmObj3 = new Customer { CustomerID = 3, Name = "Vaithy" };

//List of Customer

List<Customer> listCust = new List<Customer>();

// Adding the object into the Customer List
listCust.Add(custmObj1);
listCust.Add(custmObj2);
listCust.Add(custmObj3);

C# 3.0 :

List<Customer> listStr = new List<Customer> {
new Customer{CustomerID = 1, Name = "Ayyanar"},
new Customer{CustomerID = 2, Name = "Senthil"},
new Customer{CustomerID = 3, Name = "Vaithy"},
};

In single statement, we can populate the multiple object into the list.

C# 3.0: Anonymous Types

2007-12-09T18:26:00.000-08:00

C# 3.0 Anonumous types is a nice feature. you can create the object for the class without declaring the class, private variables or properties. suppose if you want to create a class with two properties,

Conventional Method:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}

Object Creation:

Customer custm = new Customer();
custm.CustomerID = 1;
custm.Name = "Rich";

C# 3.0 Anonymous Types:

you can create a object for the anonymous class(No Class Name) with property initialization.

var cust = new { CustomerID = 1, Name = "Rich" };

The above code will create the object for Anonymous class with two properties of CustomerID and Name.

Visual Studio provides the intellisense for anonymous object without compiling.

Compiler will create the class(Anonymous) for anonymous object like(not exact) the below.

class Anonymous_1
{

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

}

if you create the multiple anonymous object, compiler will use the same class if type of the property is same.

var cust = new { CustomerID = 1, Name = "Rich" };

var cust1 = new { CustomerID = 2, Name = "David" };

From the above code, compiler will use the same anonymous class for creating the object.

var cust = new { CustomerID = 1, Name = "Rich" };

var cust1 = new { CustomerID = "2", Name = "David" };

From the above code, compiler will use the two class for each object because second anonymous object of property CustomerID type differ from first object.

Once you created the anonymous type object, you can't reassign the value for the property of anonymous type object.

cust.CustomerID = 3;

The above code will throw compiler error.

SCRUM Development Process - Agile Project Management

2007-12-09T05:19:00.000-08:00

Here we are going to discuss about Agile Project Management with SCRUM. Nowadays most of companies started to using SCRUM development process. Its practice to follow in project/product development framework. This process is most suited to the project that requirements frequently changing and adding more enhancements.

Definition:
An iterative, incremental process for developing any product or managing any work. It produces a potentially shippable set of functionality at the end of every iteration.

Attributes of SCRUM process:
• An agile process to manage and control development work.
• A wrapper for existing engineering practices.
• A team-based approach to iteratively, incrementally develop systems and products when requirements are rapidly changing
• A process that controls the chaos of conflicting interests and needs.
• A way to improve communications and maximize co-operation.

SCRUM Process Diagram:

Detailed SCRUM Process Diagram:

Terminology:•

Product Owner

o This is the person who makes decisions about features that will be added to the project
o This person may be a customer, but can be a person in the company who acts as the customer’s representative
o This person will be the ultimate judge of a projects successful completion

SCRUM Master

o Equivalent to a project manager for a small project or a team lead on a larger project, with a size of 4 to 8 people.
o The scrum master ensures that the rules of scrum are followed
 By the development team
 By management and customers

Product Backlog

o The product backlog is generally equivalent to the requirements document in traditional projects
o Features are added to the product backlog and include the following information
 ID
 Priority
• Multiple priority scales can be used
o 1-5
o Showstopper, Major, Minor, Trivial
 Description
 Preliminary Hour Estimate
o It can also contain
 Customer/Contract information
 Requirement Owner
 Anticipated Version
 Assigned Development Team
 Assigned QA Team
 Feature Status
o Features are prioritized by the Product Owner

SPRINT

• A Sprint is a single development cycle
• Developers work as a team during a sprint to complete tasks that are defined at the beginning of the sprint
• Typical duration of 4 weeks, but is flexible
• The output of the sprint should be completed software modules that provide demonstrable capability
• The entire team is responsible for all of the tasks on the sprint
o If someone finishes their tasks early, they help others complete the remaining tasks
• The end date of the sprint is fixed. Tasks must get dropped from a sprint rather than move the end date
• Sprint Planning Meeting:
• Occurs at the beginning of sprint
• Features from the product backlog are broken down into smaller tasks and assigned to developers
• Estimates refined by developers based on implementation responsibility
• Attended by development team, product owner and the scrum master
The scrum master runs the meeting

Sprint Backlog

• The Sprint Backlog tracks the progress of an individual sprint
• All of the tasks that were defined during the sprint planning meeting are listed on the sprint backlog
• The estimates for tasks are stored and updated on this document
• Daily SCRUM:
• Daily progress meeting
o Attended by the development team
o Run by the scrum master
• Three questions answered by each team member
o What did you do yesterday?
o What is keeping you from accomplishing your tasks?
o What do you plan on doing today?
• Estimates are updated on the sprint backlog
o Number of hours worked are logged
o Hours remaining are “re-estimated”
• Information gathered at these meetings can be used to track progress of tasks

SCRUM Rules
• The dates are fixed
o If tasks are not going to be finished, they are dropped from this iteration
o Priorities are VERY important
• No Feature Creep
o Features may not be added to a sprint by the product owner after the Sprint Planning Meeting
o Exception – the burn down chart shows that all tasks will be completed early and the team agrees to take on the next task from the product backlog
o Sprints can be cancelled and restarted by the product owner if there is a high priority need
• All tasks must be TESTED to be complete
o Developers must COMPLETE the tasks
o The next sprint will start immediately after this one ends, so there will not be time for revisiting this task

Challenges

o It takes practice by the entire team
o People tend to over-commit during the Sprint Planning Meeting
o Proper estimates must include time for:
 Design
 Development
 Reviews (Design and Code)
 Unit Test Creation
 Testing
 Integration

C# 3.0 : Object Initialization

2007-12-04T20:50:00.000-08:00

Usually object can be initialized by passing value to contructor. if we want to initialize the differnt property value in different situation, we need to create a more constructor for all the situation.

Conventional Method:

class Customer
{
#region Constructors

public Customer() {}

public Customer(long customerID)
{
CustomerID = customerID;
}

public Customer(long customerID, string name)
{
CustomerID = customerID;
Name = name;
}

#endregion

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}

Initialization:

Customer custObj = new Customer(1);
Customer custObj1 = new Customer(1, "Pankaj");
Customer custObj2 = new Customer();
custObj2.CustomerID = 1;
custObj2.Name = "Pankaj";

C# 3.0:

But in C# 3.0, no need to create too much contructor for initializatoin. you can initialize the property without creating the constructor.

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}

Initialization:

Customer custSingle = new Customer { CustomerID = 1 };
Customer cust = new Customer { CustomerID = 1, Name = "Pankaj" };

Constructor and Initialization:

you can also call the constructor and initialize the property at the same time.

Customer custConst = new Customer(1) { CustomerID = 2, Name = "Pankaj" };

First call the constructor and then initialize the property.

So finally CustomerID value will be 2 instead of 1.

C#.Net 3.0 features: Automatic Property

2007-11-23T22:30:00.000-08:00

Microsoft introduced the new features in C#.Net 3.0 called Automatic Property.

No more need to declare the private variable for properties. It automatically creates the private variables for properties. But still you can use the conventional way of declaring the private variables to initialize the specific default value to private variable and also other calculation purposes.

Conventional Method:

class Customer
{
private long m_customerID;
private string m_name;

public string CustomerID
{
get
{
return m_customerID;
}
set
{
m_customerID = value;
}
}

public string Name
{
get
{
return m_name;
}
set
{
m_name = value;
}
}

}

C#.Net 3.0 New Method:

class Customer
{

public string CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

}

From the above declaration, you can find that the no private variable declared.

You may ask the question what would be default value for property. Here is the answer.

1. Value type = 0
2. Reference type = null

Performance difference between EXEC and sp_executesql

2007-11-22T22:12:00.000-08:00

I presumed that you already know about the execution plan. Sometimes we may come to situation to use Dynamic SQL instead of direct T-SQL.

If we are using Direct T-SQL (not dynamic) in stored procedure, SQL Server reused execution plan from the cache. i.e. SQL Server will not compile the Stored Procedure again.

If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.

So, we have to think about the performance while using dynamic sql.

To execute the dynamic SQL in stored procedure, we have to use the following way.

1. EXEC (Non- parameterized)
2. sp_executesql (Parameterized)

There will be performance difference between above two.

Execution plan will not be created until you execute the dynamic sql. If you execute the dynamic sql using EXEC, execution plan will be created for every execution even values only changing. If you use sp_executesql, SQL Server Optimizer will try to use same execution plan. Because dynamic sql string will be the same, values only going to change. So it will be treated as Stored Procedure having input parameters.

Use the following query to test,

CREATE TABLE [dbo].[Item]
(
ID INT
)

GO

INSERT INTO [dbo].[Item](ID) VALUES (1)
INSERT INTO [dbo].[Item](ID) VALUES (2)

GO

DBCC FREEPROCCACHE

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

SET @ItemID = 2
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

To view the execution plan, use the following query.

SELECT usecounts, sql FROM sys.syscacheobjects

Results:

UseCounts SQL

1 SELECT * FROM [dbo].[Item] WHERE ID = 1
2 (@ID INT)SELECT * FROM [dbo].[Item] WHERE ID = @ID
1 SELECT usecounts, sql FROM sys.syscacheobjects
1 SELECT * FROM [dbo].[Item] WHERE ID = 2

From the results, executed the dynamic sql using sp_executesql uses same execution plan. EXEC create the execution plan every time.

Conclusion:

Always try to use sp_executesql to execute the dynamic sql to improve the performance.

Avoiding Global temporary table problems between databases

2007-10-09T03:51:00.000-07:00

Global temp tables are used to store values that can be used across the stored procedures in particular application run. it will disappears when application is stopped.

Global temp tables are created by following query.

CREATE TABLE [dbo].[##Temp] (ID INT)

if global temp tables are created and used
by single instance only, problem(creating 2 temp table with same name) will not occur. The problem will occur when another instance creating same temp table at same time. This problem will occur on same database or another database with same name of temp table creation.

To avoid this confliction, we will use GUID with temp table name. But this method is more constlier and difficult to handle. Because temp table name is so lengthy.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, NEWID()) + ' ] (ID INT)' )

Otherwise we can use identity value of some table with temp table name.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + @ID + ' ] (ID INT)' )

The above method will solve the problem even multiple instances running on same database. Because each instance will create unique identity value and using that id with temp table name.

Again problem will occur between different databases creating the same temp table with same identity value. This problem will occur when the multiple instance running on different databases at the same time. Because each instance trying to create temp table with same name.

To avoid confliction between different databases, use DB_ID() or DB_NAME() with temp table name.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_ID()) + @ID + ' ] (ID INT)' )

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_NAME()) + @ID + ' ] (ID INT)' )

Because DB_ID() or DB_NAME() will be unique to each database.

Adding Documentation for Enum Members in .Net

2007-09-11T02:55:00.000-07:00

To add the documentation for each member of enum, we have to add the

tag for each members like the below.

CODE:
-----

///<summary>
/// Priority
///</summary>
public enum Priority
{
///<summary>
/// Low Priority
///</summary>
Low,
///<summary>
/// Medium Priority
///</summary>
Medium,
///<summary>
/// High Priority
///</summary>
High,

}

Randomly selecting records from the Table - SQL Server

2007-09-03T23:14:00.000-07:00

If you would like to retrieve the 10 records randomly, you have to use either RAND() or NEWID() function.

RAND():
------
SELECT TOP 10 EmployeeID, RAND() AS RNumber FROM dbo.Employee ORDER BY 2

NEWID():
--------
SELECT TOP 10 EmployeeID FROM dbo.Employee ORDER BY NEWID()

The above 2 methods of retrieving the records from the table have own mertis and demerits.

RAND() methods will give same sample of records in every run. so you have to come with own logic to produce different random number for every records.

NEWID() method give easy solution for the above problem. The idea behind this method is having of unique identifier for each rows. SQL Server maintain this for every rows.

There will be performance overhead problem in this approach when you are using this for TABLE having more records. Because its scans through whole table. you can avoid the full scan by limiting the records using WHERE condition.

SQL Server 2005.
---------------

SQL Server 2005 provides new option to get the records randonly. That new option is
TABLESAMPLE. This keyword is used with FROM clause. This approach could not read
through entire table. its just take the sample records instead of scanning entire
table.

TABLESAMPLE:
------------

SELECT EmployeeID FROM dbo.Employee TABLESAMPLE (50 ROWS)

SELECT EmployeeID FROM dbo.Employee TABLESAMPLE (50 PERCENT)

SELECT TOP 10 EmployeeID FROM dbo.Employee TABLESAMPLE (50 ROWS)

The above first query will not return 50 rows exactly.
First it will convert the ROWS into percent. And the select the records randomly.

Selection of random records based on DATA pages(8K) for that table instead of rows identifier. so it will not produce the expected result.

To get the expected result, we have to use the TOP clause in the select query. The TOP #(number) should be less than selection of records specified in the TABLESAMPLE.

Sometimes,Even this appraoch will not produce the expected result.so you have to use this approach carefully with your logic.

Handling Database NULL in .Net while using SqlParameter (.Net 2.0)

2007-07-26T05:16:00.000-07:00

If you want to set the NULL for database column while inserting or updating, normal “null” OR “DBNULL.value” is not suitable.

C# code:

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = null;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = DBNULL.value;

If you are using Sqlparameter for passing the parameter value to stored procedure, the above null types does not support. It will throw an error like “Parameter @MiddleName not specified.”.

C# code:

using System.Data.SqlTypes;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = SqlString.Null;

The above code will execute properly without any error.

The same way you have to use for other data types like
DataTime --> SqlDateTime.Null
int32 --> SqlInt32.Null, etc

Performance Difference between Parameterized and Non-Parameterized call to Stored Procedure

2007-07-24T06:16:00.000-07:00

You can call the sql stored procedures either with parameter ( using SqlParameter) or normal sql string ( using EXEC dbo.spName). There will be significant performance difference between two.

Non – Parameterized Stored Procedure Call:
If you call the stored procedure using EXEC ( like normal sql query) with command type as TEXT, execution plan is not reused. SQL Server caches the execution plan for SP to reuse. If you used EXEC for calling SP, new execution plan will create for every different string of EXEC statement. It will not reuse the execution plan from the SQL Server cache. SQL Server normally doing the parsing, optimizing, compiling process while creating the new execution plan.

Parameterized Stored Procedure Call:

If you call Stored procedure with Parameters ( example: SqlParameter) with Command Type as Stored Procedure, execution plan of the SP is reused again instead of creating the new one with different parameters.

Conclusion:

Use Parameterized method of calling when you call the stored procedure instead of EXEC method. You can’t feel the performance when few call to stored procedure. You can feel this performance difference when more calls to stored procedure (10000 calls frequently).

OUTPUT Command in SQL Server 2005

2007-07-08T21:09:00.000-07:00

The output parameter that can be used in stored procedures. This is about returning effected data on a table with a few feature in SQL Server 2005.
SQL Server 2000

A simple question for you. If you want to retrieve last inserted identity value what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

CREATE TABLE TempTable
(
ID INT IDENTITY(1 , 1)
, Code VARCHAR(25)
, Name VARCHAR(50)
, Salary Numeric(10 , 2)
)
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A002' , 'Ricky' , 200 )

SELECT SCOPE_IDENTITY() AS LastInsertID

However, this will only valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

more...

Features in Microsoft SQL Server 2008 CTP

2007-07-04T16:55:00.000-07:00

Microsoft is making a series of announcements at Tech*Ed related to SQL Server 2008 -- previously codenamed "Katmai". I've got some details on some of the new features including the MERGE statement, Table Valued Parameters, Change Data Capture and the Declarative Management Framework. There should also be a download of the June CTP available inside Connect.
more...

Other Links:
Four Pillar

Outlook with .NET 2.0

2007-06-27T23:19:00.000-07:00

For accessing the outlook and its features you have to add reference of Microsoft Outlook 11.0 object library Version 9.2 (COM component) to your project.This COM component provides various objects through we can access the outlook.

1. Microsoft.Office.Interop.Outlook.Application
2. Microsoft.Office.Interop.Outlook.Explorer
3. Microsoft.Office.Interop.Outlook.Inspector
4. Microsoft.Office.Interop.Outlook.MAPIFolder
5. Microsoft.Office.Interop.Outlook.MailItem
6. Microsoft.Office.Interop.Outlook.AppointmentItem
7. Microsoft.Office.Interop.Outlook.TaskItem
8. Microsoft.Office.Interop.Outlook.ContactItem

more...

Debugging SQL Server 2005 Stored Procedures in Visual Studio

2007-06-18T04:30:00.000-07:00

With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer (see Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer for more information). With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures' statements and have these breakpoints hit when debugging your application more...

New XML Capabilities in SQL Server 2005

2007-06-05T05:39:00.000-07:00

Prior to SQL Server 2005, if developers wanted to convert XML data to relational data, they had to use combinations of the stored procedure sp_xml_preparedocument and the OPENXML function. While still valid, this methodology introduces some overhead. SQL 2005 provides native support for the XML data type, and new methods to directly parse and read the data more

Passing a Table to A Stored Procedure in SQL Server 2005

2007-05-29T22:00:00.000-07:00

In this article, he trying to present a solution to the above scenario by using XML as the format to pass a table to a stored procedure. The CALLER can transform the table (Query result) to an XML variable and pass to the stored procedure. The CALLEE can either convert the XML parameter back to a TABLE variable or directly use XQuery on the XML variable. more

Enable CLR in SQL Server 2005

2007-05-14T02:29:00.000-07:00

if you need to run CLR object in SQL Server 2005. you have to enable the CLR option.
I have used following command to enable the CLR features.

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

Compressing ViewState in ASP.Net 2.0

2007-04-24T05:14:00.000-07:00

Developers often worry about performance of their web sites. Every developer wants that his web site be optimized for good performance. There are several factors affecting performance of your web site and one of them is ViewState. In this article he going to show a way by which you can compress ViewState thus improving the performance. more..

Deployment:Customize User Interfaces and Pass User Input to Installer Classes

2007-04-05T06:50:00.000-07:00

This article going to demonstrate how to customize your MSI install to prompt the user for some information and then pass this information to an installer class. This can be useful when needing to do something during an install based on the user input.There are two key parts to this process the first is the addition of a custom user interface dialog and the second is passing whatever information is entered into the new user interface to the installer class in order to do something with this information during installation. more..

ASP.Net 2.0: Export GridView to Excel

2007-04-05T05:50:00.000-07:00

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.
The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided. In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario. more..

SQL Server 2005 Reporting Services

2007-04-03T22:22:00.000-07:00

Included with SQL Server 2005 is a group of interrelated applications, collectively known as SQL Server Reporting Service (SSRS). SSRS includes all the development and management pieces necessary to publish end user reports in HTML, PDF, Excel, and CSV formats. Originally released as a SQL 2000 separate add on that could be downloaded from the web, all the Reporting Server pieces are now bundled in SQL 2005. With Reporting Services built into SQL, expect to see product adoption rise quickly. Microsoft's accounting package, Solomon, will soon discontinue use of Crystal Reports in favor of SSR. more..

Data partitioning in SQL Server 2005

2007-04-03T21:57:00.000-07:00

Data partitioning, a new feature added to SQL Server 2005, provides a way to divide large tables and indexes into smaller parts. By doing so, it makes the life of a database administrator easier when doing backups, loading data, recovery and query processing.Data partitioning improves the performance, reduces contention and increases availability of data.A Table can be partitioned based on any column in the table. Microsoft defines that column as the partition key. more..

Coding Model and Compilation in ASP.NET 2.0

2006-05-20T04:34:00.000-07:00

In ASP.NET 1.x ,application are compiled on first request or in a batch mode on startup. The disadvantage is we had to deploy uncompiled code into our production server. ASP.NET 2.0 offers new compilation method that precompiles source code into binary assemblies for deployment. Pre-Compiled application consists of assemblies ,resources which of no value for an attacker. PreCompiled application is more secure than an normal ASP.NET application.

Read more

Pass first MCP Exam

2006-05-09T00:41:00.000-07:00

Today is great day for me... I passed the first MCP Exam...(070-315)...

Enjoyable day......

An Introduction to the Service Broker (SQL Server 2005)

2006-04-07T05:21:00.000-07:00

Service Broker is a new feature in SQL Server 2005 that allows you to build reliable, asynchronous, message-based applications using extensions to the T-SQL language to process and manage queues. Since queues are managed in the database, you enjoy all the capabilities of a typical database system, which is transactions, reliability, performance, scalability etc.

Service Broker can easily scale to many thousands of messages per second and it can guarantee message ordering among the various messages and even across different reading threads! All message processing is transacted and thus recoverable. Finally, message processing and posting can be distributed across many participating SQL Servers, thus ensuring true scalability and performance. In the subsequent sections of this article, we will see the building blocks of Service Broker and write our first, simple message based application.

Read more...

Flashback....(MNGP Polytechnic--1st Year Tour)

2006-04-02T21:29:00.000-07:00

The above photo was took in Nilgiris. This is Feb.14. At that time most of guys weared the green shirt especially kalai.

This was really enjoyable moment. we stop the bus...then we dance by using bus audio systems.

This was the hotel we stayed. we booked the two rooms in that hotel. And also I think we did camp fire in that hotel.

This was most memorable moment... one of the girl had a birthday during that period.
I think date was Feb.13.

Flashback....(MNGP Polytechnic--3rd Year Tour)

2006-03-26T20:50:00.000-08:00

We had three wonderful young lecturer's...Vijayaragavan, PremKumar,Justin.

Dance with lecturer..."Justin"

Flashback....(MNGP Polytechnic--1st Year Tour)

2006-03-25T01:07:00.000-08:00

Above photo taken in Ooty Garden... at the time we sang a most popular song.."Al-lello pullelo allea pullelo"....