Let it snow...

Its Christmas and New Year holiday time. Both Google & Microsoft seems to be playing same "Let it snow" competition.

1. Go to google.com and search for "Let it snow". Press enter.
2. Go to URL - http://ie.microsoft.com/testdrive/performance/letitsnow/ . Also see other test drive by clicking on “Return to test drive” on right top corner.

Updating configuration parameters programmatically using ASP.Net

Download sample code

Introduction
In this article, we would see the method to update the key parameters in configuration file without causing the application instance.
This article assumes that the reader is already familiar with ASP.NET programming.

Keywords
web.config: Application configuration files contain settings specific to an application. This file contains the configuration settings that the common language runtime reads (such as the assembly binding policy, remoting objects, and so on), and settings that the application can read [MSDN].

xml: Extensible Markup Language (XML) is the universal format for data on the Web. XML allows developers to easily describe and deliver rich, structured data from any application in a standard, consistent way. XML does not replace HTML; rather, it is a complementary format [MSDN].

Requirement
According to the requirement we need to modify the values of the keys found in web.config programmatically or by providing configuration screen and also without re-instantiating Application instance (i.e. Application instance is not lost). So the user can modify the values of the keys for which he is allowed to do so.

Description
We all are familiar with ASP.Net programming and also the production environment of the website under Internet Information Services (IIS).
During the application life cycle, the application raises events that you can handle and calls particular methods like Application_Start and Application_End placed in Global.asax in the root directory of your application.
The Application_Start and Application_End methods are special methods that are called once for the lifetime of the application domain.
Here are some of the conditions in which the Applicaton_Start event is invoked.

• On restarting of IIS.
• Application pool of the website is restarted.
• Updating the web.config file.

Solution

Add the new xml file in your solution by selecting Add New Item > Web Configuration file from dialog box.
Name this file whatever you like. Here I named it as “DataKey.config”.

 Web.config

In the above code snippet we have used configSource attribute of the appSettings tag. ConfigSource take the path of the another config file in the solution. And during the runtime the keys from config file are replaced in the appSettings section in web.config file.

 DataKey.config (custom configuration file)

Here there is no need for XML declaration, but the document element should be same as that we provide in web.config.

C# Code

System.Configuration.ConfigurationManager.AppSettings["merchantid"] 

System.Configuration.ConfigurationManager.AppSettings["merchantkey"]

Accessing the value of the key is same way we access from web.config.

C# Code

System.Xml.XmlDocument xml = new System.Xml.XmlDocument();
xml.Load(Server.MapPath(@"~\DataKey.config"));  
System.Xml.XmlNode node = xml.SelectSingleNode("//appSettings/add[@key='" + txtKeyName.Text + "']");
node.Attributes["value"].Value = txtKeyValue.Text.Trim();
xml.Save(Server.MapPath(@"~\DataKey.config"));

Updating DataKey.config can be done using methods of System.Xml.XmlDocument.

So here the keys can be saved in XML file and can be modified without loosing the Application instance.

Hence we are done!!!

SSRS - Report Viewer - Hide PDF Export option

SQL Server Reporting Services eases designing of reports, whereas Microsoft Report Viewer control is used to display .rdl/.rdlc report into ASP.Net website.

Moreover report viewer control provides the various options in its toolbar. Of the different options, one option provides functionality of Exporting the report in different formats (like XML, CSV, Word, Excel, PDF ...)

Now what if one wants to exclude any/all of the above options?

Say if I want to hide PDF export option from Report Viewer toolbar. Below are the steps to do so.

Step - 1
Locate the file name "RSReportDesigner.config" at
"Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config".

Note: Location of the file varies in different version of SQL Server.

Step - 2
Disable PDF export option - add the visible attribute with value false

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false" />

Enable PDF export option - remove the visible attribute

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering" />

Step - 3
After this you need RESTART your Report Server.

This can be applied to other exporting options too.

SQL Query – How to get data in Hierarchical Structure?

Download sample data with Schema

Prepare an SQL query to display the given data in “Hierarchical Structure” based on the level of the parent node. In a way that tree structure can be prepared.

I have one table:

• Location – It contains the data with Parent – Child relationship.

The structure of the table and result set is as follows:

Location

Resultant Data

Please provide your SQL query in comment of this article.

Click here to download the sample data including schema.

Following are the solutions provided by:
Me (Chirag Vidani)

WITH LocationList (LocationId,LocationName,ParentLocationId,LocationLevel)
AS
(
    SELECT loc.Id, loc.Name,loc.ParentId, 1 AS LocationLevel
    FROM Location AS loc
    WHERE loc.ParentId IS NULL
    UNION ALL
    SELECT l.Id,l.Name,l.ParentId,list.LocationLevel + 1
    FROM Location AS l
    INNER JOIN LocationList AS list
        ON l.ParentId = list.LocationId
)
SELECT LocationId,LocationName,isnull(ParentLocationId,0) 'ParentLocationId',LocationLevel FROM LocationList

Mr. Kaushik Patel

WITH LocationLevelReports (LocationID, Locationname , ParentLocationId, LocationLevel)
AS
(
-- Anchor member definition
SELECT L.ID, L.name, ISNULL(L.ParentId,0) ParentId, 
1 AS LocationLevel
FROM Location AS L
WHERE ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT L.ID, L.name, L.ParentId, 
LocationLevel + 1 as LocationLevel
FROM Location AS L
INNER JOIN LocationLevelReports AS R
ON L.ParentId = R.LocationID

)
select * from LocationLevelReports

SQL Query - How to Select top 2 highest salaried employees from each department?

Download sample data with Schema

Let us share some knowledge of SQL Query.

I have three tables mentioned below:

• Employee – Master table
• SalaryDetails – Reference table with salary information
• Department – Master table

The sample data into these tables is show below:

Employee	Department
Salary Details

Now the SQL Query to be prepared should contain resultant data as ‘top 2 highest salaried employees from each department’ as below :

Resultant Data

Please provide your queries in comment of this article.

Schema with sample data is provided on download link, which will save your time for preparing sample tables and data. Click here to download.

Following are the solutions provided by:
Me (Chirag Vidani)

select 
 data.DepartmentName 'Department Name', data.EmployeeName 'Employee Name', data.Salary 'Salary/Day' 
from
 (select 
  d.Name 'DepartmentName',e.Name 'EmployeeName',sd.Salary 'Salary',dense_rank() over(partition by sd.DepartmentId order by sd.Salary desc)'SalaryRank' 
 from 
  Salarydetails sd
 join Department d 
  on sd.DepartmentId = d.Id
 join Employee e 
   on sd.EmployeeID =e.Id) as data
where 
 data.SalaryRank <= 2
order by 
 data.DepartmentName,data.Salary desc

Mr. Kaushik Patel

SELECT 
 d.* 
FROM
 (select 
  S.*,D.NAME DepartmentName,E.NAME EmployeeName ,RANK() OVER (PARTITION BY DeptID ORDER BY SALARY DESC) AS Raw_Rank 
 from 
  Salary s 
 inner join Employee e 
  on s.EmployeeId = e.id 
 inner join Department d 
  on s.DepartmentId = d.id ) d 
WHERE 
 Raw_Rank <=2

How to upload/download file to/from server using WebClient method…?

Today’s business applications are working on client server architecture and on timely basis files are being uploaded to server from client or vice versa.

In ASP.Net we can upload a file to server using File-Upload control. But what-if we want to upload a file automatically or programmatically to the server using Windows application, Windows Service or Web application?

The possible ways to upload a file to server are follows:

• FTP Upload 
• HTTP Upload
• ...

Here I will show how to upload/download a file using HTTP method (i.e. commonly known as Web Client method).

In Web Client method we require a virtual directory to which file is to be upload under domain name (i.e. http://www.YourDomainName.com/ClientFiles/). So we are going to upload files to “ClientFiles” directory.

Note: You need to install and configure WebDAV (Web Distributed Authoring and Versioning) in IIS. Click here for an article on Installing and Configuring WebDAV on IIS 7.

Code to upload a file on Server using WebClient method:

System.Net.WebClient webClient = new System.Net.WebClient();
string sourceFilePath = @"D:\MyDocuments\DataFile.xml" ;
string webAddress = "http://www.YourDomainName.com/ClientFiles/";
string destinationFilePath= webAddress + "DataFile.xml";
webClient.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
webClient.UploadFile(destinationFilePath, "PUT", sourceFilePath);
webClient.Dispose();

Code to download a file from Server using WebClient method:

System.Net.WebClient webClient = new System.Net.WebClient();
string webAddress = "http://www.YourDomainName.com/ClientFiles/";
string sourceFilePath = webAddress + "DataFile.xml";
string destinationFilePath = @"D:\MyDocuments\DataFile.xml";
webClient.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
webClient.DownloadFile(sourceFilePath, destinationFilePath);
webClient.Dispose();

How to RESET identity column in SQL Server

During the development of any application we input dummy data and which is stored in database. But frequently we come to the point where we want all records of the table to be deleted and also want to start the identity columns values from 0.

For this we delete the data using truncate command.
It will delete the data from table and also reset the identity column’s value to 0.

truncate table <table_name>

truncate table product

But the truncate command fails to delete the data if there is relationship given to the table and the identity column is not reset.

For this after firing the delete command execute below command.
It will reset the identity column of product table to 0.

DBCC CHECKIDENT('<table_name>', RESEED, <reset from number>)

DBCC CHECKIDENT('product', RESEED, 0)

Preparing “Data Dictionary” using SQL Query

Data dictionary is the required while documenting any project. So here is the query for preparing the data dictionary of any database.

SELECT [Table Name] = OBJECT_NAME(c.object_id)
,[Column Name] = c.name
,[Data Type] = t.name
,[Size] = c.max_length
,[Description] = isnull(ex.value,'') 
FROM sys.columns c 
LEFT OUTER JOIN sys.extended_properties ex ON (ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description') 
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id 
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
AND OBJECT_NAME(c.object_id) in (select name from sys.tables where type= 'U') 
ORDER BY OBJECT_NAME(c.object_id), c.column_id

Just include the column of the table definition (if any other required - i.e. if you want to display whether the column is null/not null, just include the field from sys.objects table and so on).

Taking backup of Skype contacts

Something important thing to share with you.

If ever you are using Skype messenger, please make a habit to take backup of contacts on timely basis, because your contacts gets deleted you can easily restore/recover them back from backup file.

As today only I lost all my contacts from skype messenger, because I used old version of skype messenger and Skype suggest us not to use old version when new version is out.

So then after contacting to skype customer care, they restored my contacts.

So to overcome this situation keep a habit of taking backup of skype contacts if you are using skype for communication purpose

Serializing into JSON using .Net 4.0 classes

Lets discuss something new and interesting points related to latest technology JSON.

While returning data in ajax call of jQuery we generally convert into string and then IIS serialize the data.

But .Net 4.0 provides the DataContractJSONSerializer class which automatically converts any datatype to string datatype (i.e. generic list/any type of data can be serialized using this class and returned back in form JSON).

Parsing JSON using jQuery

Lets discuss something new and interesting points related to latest technology jQuery & JSON.

Many of us might have worked in it and others don't need to worry as in future they will surely work in it.

Recently I came across one thing that "While using JSON we require parse_json.js file (plugin), but according to me this file is not at all required. As parse json function is already included in default jQuery pre-requisite file (.js) only. "

To refer the complete syntax visit: http://api.jquery.com/jQuery.parseJSON/