Windows Installer CleanUp Utility

When you are working on your computer and installing a new program, the installation suddenly fails. Now you are left with a partly installed program. You try to install the program again, but you are unsuccessful. Or, maybe you have problems trying to remove an old program because the installation files are corrupted.

By running the Windows Installer CleanUp Utility, you will be able to remove that program and can proceed further. I thought of sharing it as many people still don't know about it.

Encrypting & Decrypting XML

I have been given a task to encrypt an xml file before sending it to a different server and use it there after decrypting it just to make the data transfer more secured.

Here is what the code does to encrypt and decrypt an xml file -

This is to be entered in the web.config file -

< system.web>
< machineKey validationKey="1B8B081B3ED61D4663AE0F85406E69A5C8D8D2B8" decryptionKey="77C3813937C1C210054267AC940D0E75A4BD988F095EDE2D"
decryption="3DES"
validation="SHA1"/>

OR

< machineKey
validationKey="AutoGenerate,IsolateApps"
decryptionKey="AutoGenerate,IsolateApps"
validation="SHA1"
decryption="Auto"
/>

Now is the code -

public XmlDocument Config = new XmlDocument();

public string sEnvironment = "";

string sLoc = string.Empty;

protected void Page_Load(object sender, EventArgs e)
{
sLoc = Server.MapPath("XMLFile1.xml");
Config.Load(sLoc);

sEnvironment = "Prod"; // ConfigurationSettings.AppSettings["Environment"].ToString();
if (IsXmlEncrypted(Config))
//Config = DecryptXml(Config);
DecryptXml(sLoc);
else
{
if (sEnvironment != "Local")
{
EncryptXml(sLoc, "root");
}
Config = DecryptXml(Config);
}
}

#region Encrypt and Decrypt Methods

///

/// EncryptXml is a method that will encrypt and entire Xml file, or just a particular node in an Xml. This method accepts 2 parameters: the path where the Xml file is located
/// and the node that must be encrypted. If the entire Xml document needs to be encrypted, the root node of the Xml document must be passed as the node parameter.
/// NOTE: This method SAVES the file! If you do not need to overwrite your Xml file, then DO NOT USE this method. Use the other version in its place.
///

/// The actual path where the Xml file is stored.
/// The node from which encryption starts. If the root node is passed as a parameter, the entire document will be encrypted.
/// If you do not need the entire document encrypted, but instead, just one node, pass the node in in xpath format: "root/parent/child"
///

public void EncryptXml(string file, string node)
{

sLoc = Server.MapPath("web.config");
Config.Load(sLoc);

XmlNode xNode = Config.SelectSingleNode("configuration/system.web/machineKey");

CspParameters cspParams = new CspParameters();

cspParams.KeyContainerName = xNode.Attributes["validationKey"].InnerText;
cspParams.Flags = CspProviderFlags.UseMachineKeyStore;

RSACryptoServiceProvider rsaKey = new RSACryptoServiceProvider(cspParams);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.PreserveWhitespace = true;
xmlDoc.Load(file);

XmlElement elementToEncrypt = xmlDoc.GetElementsByTagName(node)[0] as XmlElement;

if (elementToEncrypt != null)
{
RijndaelManaged sessionKey = new RijndaelManaged();
sessionKey.KeySize = 256;

EncryptedXml eXml = new EncryptedXml();

byte[] encryptedElement = eXml.EncryptData(elementToEncrypt, sessionKey, false);
EncryptedData eData = new EncryptedData();
eData.Type = EncryptedXml.XmlEncElementUrl;

eData.EncryptionMethod = new EncryptionMethod(EncryptedXml.XmlEncAES256Url);

EncryptedKey eKey = new EncryptedKey();
byte[] encryptedKey = EncryptedXml.EncryptKey(sessionKey.Key, rsaKey, false);
eKey.CipherData = new CipherData(encryptedKey);
eKey.EncryptionMethod = new EncryptionMethod(EncryptedXml.XmlEncRSA15Url);

eData.KeyInfo = new KeyInfo();

KeyInfoName kin = new KeyInfoName();

kin.Value = xNode.Attributes["decryptionKey"].InnerText;
eKey.KeyInfo.AddClause(kin);

eData.KeyInfo.AddClause(new KeyInfoEncryptedKey(eKey));

eData.CipherData.CipherValue = encryptedElement;
EncryptedXml.ReplaceElement(elementToEncrypt, eData, false);

xmlDoc.Save(file);
}
else
throw new ArgumentNullException("The specified node does not exist in the provided Xml document.");
}

///

/// EncryptXml is a method that will encrypt an Xml file and return an XmlDocument object for use IN MEMORY. If the XmlDocument needs to be saved, then it is up to the calling
/// program to save the encrypted Xml file.
///

/// A previously loaded Xml file that needs to be encrypted.
/// he node from which encryption starts. If the root node is passed as a parameter, the entire document will be encrypted.
/// If you do not need the entire document encrypted, but instead, just one node, pass the node in in xpath format: "root/parent/child"
///
/// An encrypted XmlDocument. How it is used is up to the calling program.

public static XmlDocument EncryptXml(XmlDocument xmlDoc, string node)
{
XmlDocument xmlDocToEncrypt = xmlDoc;
XmlDocument config = DotNetNuke.Common.Utilities.Config.Load();
XmlNode xNode = config.SelectSingleNode("configuration/system.web/machineKey");

CspParameters cspParams = new CspParameters();

cspParams.KeyContainerName = xNode.Attributes["validationKey"].InnerText;
cspParams.Flags = CspProviderFlags.UseMachineKeyStore;

RSACryptoServiceProvider rsaKey = new RSACryptoServiceProvider(cspParams);

XmlElement elementToEncrypt = xmlDocToEncrypt.GetElementsByTagName(node)[0] as XmlElement;

if (elementToEncrypt != null)
{
RijndaelManaged sessionKey = new RijndaelManaged();
sessionKey.KeySize = 256;

EncryptedXml eXml = new EncryptedXml();

byte[] encryptedElement = eXml.EncryptData(elementToEncrypt, sessionKey, false);
EncryptedData eData = new EncryptedData();
eData.Type = EncryptedXml.XmlEncElementUrl;

eData.EncryptionMethod = new EncryptionMethod(EncryptedXml.XmlEncAES256Url);

EncryptedKey eKey = new EncryptedKey();
byte[] encryptedKey = EncryptedXml.EncryptKey(sessionKey.Key, rsaKey, false);
eKey.CipherData = new CipherData(encryptedKey);
eKey.EncryptionMethod = new EncryptionMethod(EncryptedXml.XmlEncRSA15Url);

eData.KeyInfo = new KeyInfo();

KeyInfoName kin = new KeyInfoName();

kin.Value = xNode.Attributes["decryptionKey"].InnerText;
eKey.KeyInfo.AddClause(kin);

eData.KeyInfo.AddClause(new KeyInfoEncryptedKey(eKey));

eData.CipherData.CipherValue = encryptedElement;
EncryptedXml.ReplaceElement(elementToEncrypt, eData, false);

return xmlDocToEncrypt;
}
else
throw new ArgumentNullException("The specified Xml node does not exist in the provided Xml document.");
}

///

/// This method encrypts a string!
///

/// The string that needs to be encrypted.
/// The encrypted string.

public static string EncryptData(string data)
{
DotNetNuke.Security.PortalSecurity ps = new PortalSecurity();

XmlDocument config = DotNetNuke.Common.Utilities.Config.Load();
XmlNode node = config.SelectSingleNode("configuration/system.web/machineKey");

return ps.Encrypt(node.Attributes["decryptionKey"].InnerText, data);
}

///

/// This method decrypts a previously encrypted string!
///

/// The string that needs to be decrypted.
/// The decrypted string.

public static string DecryptData(string data)
{
DotNetNuke.Security.PortalSecurity ps = new PortalSecurity();

XmlDocument config = DotNetNuke.Common.Utilities.Config.Load();
XmlNode node = config.SelectSingleNode("configuration/system.web/machineKey");

return ps.Decrypt(node.Attributes["decryptionKey"].InnerText, data);
}

///

/// DecryptXml decrypts an Xml file. This method will decrypt the file and then save it to the specified location. If you
/// do not want your original file overwritten, then do not use this method.
///

/// The path of the encrypted Xml file.

public void DecryptXml(string file)
{
sLoc = Server.MapPath("web.config");
Config.Load(sLoc);

XmlNode xNode = Config.SelectSingleNode("configuration/system.web/machineKey");

CspParameters cspParams = new CspParameters();

cspParams.KeyContainerName = xNode.Attributes["validationKey"].InnerText;
cspParams.Flags = CspProviderFlags.UseMachineKeyStore;

RSACryptoServiceProvider rsaKey = new RSACryptoServiceProvider(cspParams);

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.PreserveWhitespace = true;
xmlDoc.Load(file);

EncryptedXml eXml = new EncryptedXml(xmlDoc);
eXml.AddKeyNameMapping(xNode.Attributes["decryptionKey"].InnerText, rsaKey);

eXml.DecryptDocument();

try
{
xmlDoc.Save(file);
}
catch (XmlException ex)
{
DotNetNuke.Services.Exceptions.Exceptions.LogException(ex);
}
}

///

/// This version of DecryptXml will decrypt a given Xml file for use in memory. If the file needs to be saved, it is up to the calling program to handle that.
///

/// The encrypted Xml document that must be decrypted
/// The node within the document that is to be decrypted

public XmlDocument DecryptXml(XmlDocument xDoc)
{
sLoc = Server.MapPath("web.config");
Config.Load(sLoc);

XmlNode xNode = Config.SelectSingleNode("configuration/system.web/machineKey");

XmlDocument xmlDoc = xDoc;

CspParameters cspParams = new CspParameters();

cspParams.KeyContainerName = xNode.Attributes["validationKey"].InnerText;
cspParams.Flags = CspProviderFlags.UseMachineKeyStore;

RSACryptoServiceProvider rsaKey = new RSACryptoServiceProvider(cspParams);

EncryptedXml eXml = new EncryptedXml(xmlDoc);
eXml.AddKeyNameMapping(xNode.Attributes["decryptionKey"].InnerText, rsaKey);

eXml.DecryptDocument();

return xmlDoc;
}

///

/// This method will determine if the "EncryptedData" node exists. If so, then this file has been encrypted.
///

/// The xml file to test for encryption.
/// TRUE OR FALSE

public static bool IsXmlEncrypted(XmlDocument xDoc)
{
XmlElement eData = xDoc.GetElementsByTagName("EncryptedData")[0] as XmlElement;

if (eData != null)
return true;
else
return false;
}

Reseed the Auto increment field

You can reseed the Auto increment field to the desired number by using the following SQL statement -
[I think it is specific for SQL Server only] other Database have their own methods.

Syntax :-

DBCC CHECKIDENT ("TableName", RESEED, 0)

jQuery !!

jQuery is a library that makes it quicker and easier to build JavaScript webpages and web apps. I often find myself short of pleased with the built in functionality of AJAX and having to do some client side “touch ups” using jQuery. The essential part of a jQuery statement, the selector, is what makes jQuery a good choice for client side scripting, and an even better reason to avoid traditional java script. The selector allows you to query HTML elements to perform some logic, hence the name jQuery. Below are some of the common use of selectors.

#id

To select an element by the ID attribute. If the ID contains special character then you can escape them with backslashes. For example to find a text box with ID txtName you can write the following :

$(“#txtName”)

Then you can do any operation on the text box like getting the value or changing the css class.

element

To find all the elements in the page with the given element name. For example to get all the DIV elements following is the syntax

$(“div”)

This will find all the DIVs present in the page. You can do any operation after that.

.class

To find all elements with the given class name. The following code finds all the elements which have class name ‘RedButton’.

$(“.RedButton”)

*
This finds all the elements in the document. The syntax is :-

$(“*”)

selector 1, selector 2, selector N

This matches the combined result of all the specified selectors. We can specify N number of selectors to get a single result. Suppose you want to find all the DIVs, a text box with ID ‘txtName’ and a Button with class name ‘RedButton’. Then following is the syntax:

$(“div, #txtName, .RedButton”)

Suppose you want to change the border color of all these elements then write :

$(“div, #txtName, .RedButton”).css(“border”,”3px solid blue”);

It will change all the specified elements’ border color to blue.

There are some good tools available to test your jQuery selectors on your Apps while debugging in .Net, firebug being the most common (http://getfirebug.com/).

For a more comprehensive overview of jQuery and its syntax use the link provided for W3 schools (http://www.w3schools.com/jquery/default.asp).

jQuery cheat sheet (http://www.javascripttoolbox.com/jquery/cheatsheet/JQueryCheatSheet-1.3.2.pdf).

jQuery video (http://www.youtube.com/watch?v=7eQI90xYez0).

Install missing templates in the installed Visual Studio templates

One day, I tried to create a new WCF Service Application and when I opened the Visual studio and clicked on Add new project, I found that this project option is missing from my Visual studio templates. To add the missing templates to your current installed templates, you need to run the following command on Visual studio command prompt after closing all the instances of the Visual studio.

devenv/installvstemplates
and then Press Enter.

Let the processing to be completed and then open visual studio. Now all the missing templates would be visible under Visual Studio installed templates.

Dictionary in c#

A Dictionary class represents a dictionary in C# that is used to represent a collection of keys and values pair of data. This article demonstrates how to use a dictionary in C#.

Creating a Dictionary

The Dictionary class is a generic class and can store any data types. This class is defined in the System.Collections.Generic namespace. Before you use a Dictionary class in your code, you must import the System.Collections.Generic namespace using the following line.

using System.Collections.Generic;

As other collection classes, the Dictionary class has Add, Remove, RemoveAt and other collection methods. The Key and Value property are used to extract a key and a value from an item in a Dictionary.

Adding data in the dictionary -

Dictionary AuthorList = new Dictionary();

AuthorList.Add("Mahesh Chand", 35);
AuthorList.Add("Mike Gold", 25);
AuthorList.Add("Praveen Kumar", 29);
AuthorList.Add("Raj Beniwal", 21);
AuthorList.Add("Dinesh Beniwal", 84);

Reading data from dictionary -

foreach( KeyValuePair author in AuthorList )
{
Response,Write(author.Key, author.Value);
}

Hashtable in c#

The Hashtable class in C# represents a hashtable. The following code creates a Hashtable:

private Hashtable hshTable = new Hashtable();

Adding Items to a Hashtable

Add method of Hashtable is used to add items to the hashtable. The method has index and value parameters. The following code adds three items to hashtable.

hshTable .Add("Author1", "Mahesh Chand");
hshTable .Add("Author2", "James Brand");
hshTable .Add("Author3", "Mike Gold");


Retrieving an Item Value from Hashtable

The following code returns the value of "Author1" key:

string name = hshTable["Author1"].ToString();

Removing Items from a Hashtable

The Remove method removes an item from a Hashtable. The following code removes item with index "Author1" from the hashtable:

hshTable.Remove("Author1");

Looking through all Items of a Hashtable

The following code loops through all items of a hashtable and reads the values.

// Loop through all items of a Hashtable
IDictionaryEnumerator en = hshTable.GetEnumerator();
while (en.MoveNext())
{
string str = en.Value.ToString();
}

Using Foreach loop -

foreach (DictionaryEntry entry in hshTable)
{
Response.Write(entry.Key);
Response.Write(entry.Value);
}

Generic List in C#

Using a Generic List in C# is an efficient method of storing a collection of variables. And probably the best part is that the List is strongly typed and casting (which degrades performance) will no longer be necessary. Your collection of variables should be of the same data type. Generic Lists were first introduced into the .NET Framework 2.0. Before we begin our Generic List example, first remember to include the correct namespace below:

using System.Collections.Generic;

In our example, we will store the integers 5, 8, 435, 76, 3256, and -85. Our next step is to initialize a List using Generics. Because we are storing integers, our it with be an int Generics List. If you wish to create the List as another variable type, simply replace int with double, decimal, string, or another type of element. We will give our List the name myGenericIntegerList.

ListmyGenericIntegerList = new List();

Now that the List has been created and initialized, we easily add integer elements. Since we stated this list as an int, the input will now be strongly typed to only accept integer values. We will now add the integeres5, 8, 435, 76, 3256, and -85 iin the example below:

myGenericIntegerList.Add(5);
myGenericIntegerList.Add(8);
myGenericIntegerList.Add(435);
myGenericIntegerList.Add(76);
myGenericIntegerList.Add(3256);
myGenericIntegerList.Add(-85);

Your generic int List is now complete! Another advantage to using the generics List is there are helper functions such as average, contains, and counts, and toArray. As brought up by Khalid, the average function is an extension method.

double average = myGenericIntegerList.Average();
bool contains = myGenericIntegerList.Contains(4);
int count = myGenericIntegerList.Count;
int[] intArray = myGenericIntegerList.ToArray();

You can print it out like -
Response.Write(myGenericIntegerList[1]);

For more functions, you can either visit the MSDN and search for “List(T) Methods” or better just give the function a try in Visual Studio and let Intellisense show you other functions.

Pivot Magic in SQL

PIVOT is a relational operator used to provide a more readable or simpler view of a table’s data. This is achieved by rotating a rows data into columns using the query response to simply the query results.

UNPIVOT is the opposite. It rotates columns into rows.

PIVOTING can make the sales data below -













Using PIVOT

Specify the desired values that you want to see in SELECT statement and FROM clause contains the PIVOT statement. Let us take an example

The following query uses ADVENTUREWORKS database which uses the order years as columns.


SELECT
CustomerID,
[2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
FROM
(
SELECT CustomerID, DATEPART(yyyy, OrderDate) AS OrderYear, TotalDue
FROM Sales.SalesOrderHeader
) AS piv
PIVOT
(
SUM(TotalDue) FOR OrderYear IN([2001], [2002], [2003], [2004])
) AS child
ORDER BY CustomerID


The FROM clause create the values that you want to see in rows of the newly created columns. We used the FOR operator to list the values that we want to pivot in the OrderYear Column.

The Result for the above query will look like the following -

How do I find an object/objects which are referencing a particular table

Recently I have been assigned a task to find out all the tables, stored procs, views where are making reference to a particular table.


Query to find out all the tables which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT OBJECT_NAME(referenced_id)
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(@objectname)


Query to find out stored procs/views which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT OBJECT_NAME(referencing_id)
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID(@objectname)


Query to find out views which are referencing a particular table -

DECLARE @objectname varchar(256)
SET @objectname = 'TB_CON_LogicalOperator'

SELECT name
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@objectname+'%'

Difference between SCOPE_IDENTITY vs @@IDENTITY vs IDENT_CURRENT

SCOPE_IDENTITY, IDENT_CURRENT,@@IDENTITY functions return last-generated identity values.

In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like:

# @@IDENTITY
# SCOPE_IDENTITY
# IDENT_CURRENT

All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

Compare

@@IDENTITY

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table
which inserts a row in another table with generate an identity
column, then @@IDENTITY returns that identity record which is
created by trigger.


SCOPE_IDENTITY

It returns the last identity value generated for any table in the
current session and the current scope.

Let me explain this... suppose we create an insert trigger on table
which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.


IDENT_CURRENT

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

How do I find a stored procedure containing a particular 'text'?

Recently I have been assigned a task to find out all the stored
procs where we have put hardcoded values like 'AL', 'GL', 'TM' etc.

Checking all the stored procs one by one is a tedious task when
you have a few hundred of them in your application and there is a
possiblity of missing some stored procs where we have hardcoded
these values.

Following query I have used to find out all the stored procs where
we have hard coded all these kinda values.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' 'AL' '%'
AND ROUTINE_TYPE='PROCEDURE'

Similarly, we can check in the functions and views -

SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%AL%'

SQL Paging - ROW_NUMBER function

With the introduction of ROW_NUMBER function in SQL 2005 life is a lot easier.

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH tblOrders AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)

SELECT *
FROM tblOrders
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID;

TIPS for writing good optimized SQL Query

Here are some of the TIPS for writing good optimized
SQL Query (apart from analyzing through EXPLAIN PLAN):

## If you specify 2 tables in the FROM clause of a
SELECT statement, the parser will process the tables
from right to left, so the table name you specify last
will be processed first. In this case you have to
choose one table as driving table. Always choose the
table with less number of records as the driving
table.

## If three tables are being joined, select the
intersection tables as the driving table. The
intersection table is the table that has many tables
dependent on it.

## Never compare NULL to anything else. All
expressions return NULL if one of the operands is
NULL. This is applicable for all operators except
Concatenation operator (||).

## Use DECODE when you want to scan same rows
repetitively or join the same table repetitively.

## Always use table alias and prefix all column
names with the aliases when you are using more than
one table.

## Use NOT EXISTS in place of NOT IN.

## Join Tables in place of using EXISTS through
Sub-Query.

## Use EXISTS in place of DISTINCT.

## Never use NOT on an indexed column. Whenever
Oracle encounters a NOT on an index column, it will
perform full-table scan.

## Never use a function / calculation on an
indexed column. If there is any function is used on
an index column, optimizer will not use index. Use
some other alternative.

## There are three good reasons why it is better
to name the columns in a query rather than to use
"select * from ...".

· Network Traffic is reduced

· The Code is easier to understand

· It could save the need for changes in
future.

## It is also better to use bind variables in
queries. That way the query becomes generic and
therefore re-usable.

## It is a good practice to use a standard syntax
for wiring SQL queries. Incase, the same query has
different standard then Oracle will re-parse the
statement again.
## Combine Multiples Scans with CASE Statements.

SQL Server Convert Datetime function

MS SQL Server 2005 provides CONVERT function to change or convert the DateTime formats.

CONVERT Function syntax:

Convert (datatype, datetime string, date style)

DateType parameter accepts the following types:
nchar, nvarchar, char, varchar, binary or varbinary
DataType length is optional.

DateTime String parameter accepts the datatime variable, datetime type column name or getdate() function to convert the datetime format.

Date Style parameter accepts the style number in which you want to convert the datetime.


Following are the SQL query Syntax to convert DateTime format:

SQL query to convert datetime into mon dd yyyy hh:miAM (or PM) format:

Select convert(varchar, getdate(), 100)
SQL query to convert datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format:

Select convert(varchar, getdate(), 101)
SQL query to convert datetime into Default mon dd yyyy format:

Select convert(varchar, getdate(), 109)
SQL query to convert datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format:

Select convert(varchar, getdate(), 113)
SQL query to convert datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format:

Select convert(varchar, getdate(), 120)
SQL query to convert datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format:

Select convert(varchar, getdate(), 121)
SQL query to convert datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format:

Examples -

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113)
-- dd mon yyyy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11', 120)
-- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121)
-- yyyy-mm-dd hh:mm:ss.mmm

Style 126 is ISO 8601 format: international standard - works
with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126)
-- yyyy-mm-ddThh:mm:ss(.mmm)

SSRS - Unwanted repeatation of text in other columns if the text is too long

I don't know if anyone else has run into this problem. I have a report that I designed in a table format with several groups. The detail row has a field that has no size limits. The field could basically contain pages of text. It only seems to be a problem when the size of of the field is larger than the size of a page when it is
exported to a PDF.

For some reason when the text is too large and field cannot fit on one page nicely it causes the other fields to repeat its text on the next page.

After hours of googling, I figured out a solution which I thought I should share it. I have used rectangles. I have put in a rectangle where I have placed my field and in that rectange I further placed a textbox and in that I have further pasted my field. This is how it stopped repeatation of text of other columns in case the text is too long to fit in one page.

Encryption & Decryption using MD5 Algorithm

If sumbody wants to try his hands on encryption & decryption using MD5 algorithm, then below are the two functions :


using System.Text;
using System.Security.Cryptography;

public static string EncryptMd5(string sOriginal)
{
string sPassKey = "16";
byte[] sPassKeyArray;
byte[] sOriginalArray = UTF8Encoding.UTF8.GetBytes(sOriginal);
MD5CryptoServiceProvider MD5Hash = new MD5CryptoServiceProvider();
sPassKeyArray = MD5Hash.ComputeHash(UTF8Encoding.UTF8.GetBytes(sPassKey));
MD5Hash.Clear();
TripleDESCryptoServiceProvider tripleDesCsp = new TripleDESCryptoServiceProvider();
tripleDesCsp.Key = sPassKeyArray;
tripleDesCsp.Mode = CipherMode.ECB;
tripleDesCsp.Padding = PaddingMode.PKCS7;
ICryptoTransform cTransform = tripleDesCsp.CreateEncryptor();
byte[] resultArray = cTransform.TransformFinalBlock(sOriginalArray, 0, sOriginalArray.Length);
tripleDesCsp.Clear();
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}

public static string DecryptMd5(string sToOriginal)
{
string sPassKey = "16";
byte[] sPassKeyArray;
byte[] sOriginalArray = Convert.FromBase64String(sToOriginal);
MD5CryptoServiceProvider MD5Hash = new MD5CryptoServiceProvider();
sPassKeyArray = MD5Hash.ComputeHash(UTF8Encoding.UTF8.GetBytes(sPassKey));
MD5Hash.Clear();
TripleDESCryptoServiceProvider tripleDesCsp = new TripleDESCryptoServiceProvider();
tripleDesCsp.Key = sPassKeyArray;
tripleDesCsp.Mode = CipherMode.ECB;
tripleDesCsp.Padding = PaddingMode.PKCS7;
ICryptoTransform cTransform = tripleDesCsp.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(sOriginalArray, 0, sOriginalArray.Length);
tripleDesCsp.Clear();
return UTF8Encoding.UTF8.GetString(resultArray);
}

GridView bug that fire RowCommand twice

I was trying my hands on .NET 3.5, and discovered that Gridview was firing Rowcommand twice for my ImageButton that is drawn using ButtonField Column. Later I found that this is a bug/unexpected behaviour from out of the box Gridview. I found some solution on internet that can be seen in detail here:
http://forums.asp.net/p/1002747/1324414.aspx#1324414
It mainly suggest to use TemplateField column with imagebutton, as Imagebutton is not quite compatible with ButtonField. Rest you can read above for details on it.

Sorting XML With System.Xml.XPath.XPathExpression

If you want to retrieve a sorted list of nodes from an XmlDocument instance, there are a couple ways to do this. You can use XSLT, or you can write your own sorting algorithm. The .NET Framework provides capability to provide sort orders to the result of an xpath query through the System.Xml.XPath.XPathExpression::AddSort method. Here is a simplistic demo:

static void Main(string[] args)

{

XmlDocument doc = new XmlDocument();

doc.LoadXml("5361");

XPathNavigator nav = doc.CreateNavigator();

string path = "/a/b";

XPathExpression expression = nav.Compile(path);

expression.AddSort("text()", XmlSortOrder.Ascending, XmlCaseOrder.None, string.Empty, XmlDataType.Number);

XPathNodeIterator iterator = nav.Select(expression);

while (iterator.MoveNext())

{

Console.WriteLine(iterator.Current.Value);

}

}

Appending data from different text files to single file !!

These dayz I m doing nothing, except reading the code and tryin to understand it.
My senior came to me and asked me to write a small application to read all the text files from the directory and append their data into a file. Its a very small and easy thing to do, but still I m posting it thinking that this small piece of code will help someone.

using System.IO;
private void Form1_Load(object sender, EventArgs e)
{
DirectoryInfo dir1 = new DirectoryInfo(@"C:\abc");

FileInfo[] txtfiles = dir1.GetFiles("*.txt");

FileInfo fxists = new FileInfo(@"c:\abc\c.txt");
if (fxists.Exists)
{
fxists.Delete();
}

foreach (FileInfo f in txtfiles)
{
StreamReader sr = File.OpenText(f.Name);
string read = null;
while ((read = sr.ReadLine()) != null)
{

FileInfo fi = new FileInfo(@"c:\abc\c.txt");
StreamWriter sw = fi.AppendText();

sw.Write(read.ToString());
sw.Close();

}

}

}

Lately, I have been given the task of stealin data from the job sites like Yahoo, CarrerBuilder and Monster and others like Bigwheels & StylineConcepts. I m really pissed off by doing this job since the last 1 month or so. As soon as I finish off with the parsing of 1 site. Another site comes for the parsing. I m waiting for a new project which doesnt involve html parsing.

This is the function which i used to extract the html content of a page into the string and used it further as i required.

public string Find_Source_Code(string myURL)
{
if(myURL.Trim().Length<=0) return "";

string return_result = "";
System.Net.WebClient Http = new System.Net.WebClient();
WebRequest objRequest = System.Net.HttpWebRequest.Create(myURL);

try
{
using(StreamReader sr = new StreamReader(objRequest.GetResponse().GetResponseStream()))
{
return_result = sr.ReadToEnd();
sr.Close();
}
}
catch
{
return_result = "";
}
return return_result;
}


private void ParsenCheck()
{
string line=Find_Source_Code(url);
string[] Description;
Boolean flag;
flag=false;

Description=line.Split('\n');

//Some more string commands to parse the data
}