Database Unit Test Cases

For Database Unit test Cases, please refer to this article which I found out to be very userful and easy to understand.

http://www.mssqltips.com/sqlservertip/2185/sql-server-unit-testing-with-visual-studio-2010/

__doPostBack not firing the Button Click event for IE9, it was working fine till IE8

Buttons are capable of raising submit event so i would recommend not use __dopostback for buttons

Rather code

__doPostBack(buttonID,"OnClick")

can be replaced by

document.all('buttonID').click()

How to avoid the button events on Refresh of page

I have .aspx page that page inserts the data to the database on a button click. But when i press the button it is going right. i m getting the Successfully message as " successfully inserted data". In this situation if i press "F5" or Refresh the page it is firing the button click event. Why it should be ? How to avoid this condition ?

Solution to the above problem is -

Add this in your class:

#region Browser Refresh
private bool refreshState;
private bool isRefresh;
protected override void LoadViewState(object savedState)
{
object[] AllStates = (object[])savedState;
base.LoadViewState(AllStates[0]);
refreshState = bool.Parse(AllStates[1].ToString());
if (Session["ISREFRESH"] != null && Session["ISREFRESH"] != "")
isRefresh = (refreshState == (bool)Session["ISREFRESH"]);
}

protected override object SaveViewState()
{
Session["ISREFRESH"] = refreshState;
object[] AllStates = new object[3];
AllStates[0] = base.SaveViewState();
AllStates[1] = !(refreshState);
return AllStates;
}

#endregion

And in your button click do this:
protected void Button1_Click(object sender, EventArgs e)
{
if (isRefresh == false)
{
Insert Code here

This will do the trick ...

LINQ equivalent of foreach for IEnumerable

There is no ForEach extension for IEnumerable as it is only for List. So you could do

This is not working -
IEnumerable items = GetItems();
items.ForEach(i => i.DoStuff());


You can add tolist before doing foreach -

items.ToList().ForEach(i => i.DoStuff());

XML Serialization of List

Here is the code to convert your list into XML -

public XmlDocument GetEntityXml()
{
StringWriter stringWriter = new StringWriter();
XmlDocument xmlDoc = new XmlDocument();
XmlTextWriter xmlWriter = new XmlTextWriter(stringWriter);
XmlSerializer serializer = new XmlSerializer(typeof(List< T >));
List< T > parameters = ListtobeConverted();
serializer.Serialize(xmlWriter, parameters);
string xmlResult = stringWriter.ToString();
xmlDoc.LoadXml(xmlResult);

return xmlDoc;
}

How to concatenate two Lists objects

Here we are filling the grid values to the List -

var listApp = new List < ApplicationLists > ();
var listFooter = new List< ApplicationLists >();


foreach (GridViewRow row in grdValues.Rows)
{
listApp.Add(new ApplicationLists { Code = ((TextBox)row.Cells[0].FindControl("txtCode")).Text, Value = ((TextBox)row.Cells[0].FindControl("txtValue")).Text });
}
if (((TextBox)grdValues.FooterRow.Cells[0].FindControl("txtCode")).Text != string.Empty)
{
listFooter.Add(new ApplicationLists { Code = ((TextBox)grdValues.FooterRow.Cells[0].FindControl("txtCode")).Text, Value = ((TextBox)grdValues.FooterRow.Cells[0].FindControl("txtValue")).Text });

}

Here is the line that can do the magic -

listApp.AddRange(listFooter);

Xml is well formed or not !!

Code to check whether the xml string is well formed or not -

public bool IsValidXml(string xmlContent)
{
var textStream = new StringReader(xmlContent);

using (var xmlTextReader = new XmlTextReader(textStream))
{
try
{
while (xmlTextReader.Read())
{
}
}
catch
{
return false;
}
}
return true;
}

Mutex could not be created

I was getting the following error when I was browsing the web site which was build and published on to the local machine -

Server Error in '/Ea' Application. ----------------- Mutex could not be created.

The workaround I have got for now is:

- If you have visual studio 2005/2008 open, close it
- Go to the ASP.NET temporary folder for v2.0 of the framework
\Microsoft.Net\Framework\v2.0\Temporary ASpNET pages
- Remove the folder for your application (or all of them)
- Reset IIS (on a command line window, >iisreset) [not always needed, but I had to use it sometimes]
- First Browse your page from IE (http://localhost/your app)
- Then reopen Visual studio

And it should work now

I hope it will work for you as it does for me.

SSAS errors: Errors in the metadata manager. The dimension with ID of 'xxx' referenced by the 'yyy' cube, does not exist.

When deploying Analysis Services solution I am getting errors:

Errors in the metadata manager. The attribute with ID of .., Name of .. referenced by the .. measure group dimension does not exist.

Errors in the metadata manager. An error occurred when loading the .. measure group, from the file, partitionWithFullPath.xml

One of the reasons why you could be getting this error message is because you are trying to deploy solution using "Changes Only" deployment method and as not all changes are deployed, metadata that is already deployed conflicts with your current metadata about cube.

To see if this will fix your problem follow these steps -

• In Business Intelligence Development Studio (BIDS) select your solution in Solution explorer, right mouse click and select Rebuild. You should not get any error during this step.

• In BIDS select menu item "Project" and choose "Properties" menu item

• Go to "Deployment" tab and change "Deployment Mode" to "Deploy All". Change Processing Option to "Full processing", or to "Do not process" if you are planning to process latter. Close this dialog.

• Select your solution in the Solution explorer and choose "Deploy" item.

• If reason for above stated error was metadata inconsistencies, you should see no errors during deployment and/or processing.

• Change your deployment setting back to what you usually use.

Cast Error on SqlDataReader using Enterprise Data Library 5.0

I was trying to fill the dropdown in the conventional way by binding the dropdown with the DataReader. But, somehow couldn't do it and was getting the cast error.

After some googling, got this article which I thought worth sharing it with others -

ExecuteReader in Enterprise Library wraps IDataReader into RefCountingDataReader that as SqlDataReader implements IDataReader interface.
RefCountingDataReader has InnerReader property that you can cast to SqlDataReader.

Here is the code -

SqlDataReader reader;
reader = ((RefCountingDataReader)db.ExecuteReader(command)).InnerReader as SqlDataReader;
if (reader != null)
reader.Read();
return reader;

Error using Microsoft Enterprise Library Data Application Block

I was trying to use Data Access Application Block in a Winforms application. I have added the right references including Microsoft.Practices.EnterpriseLibrary.Data

when I build the application I get this error -

1 The referenced assembly "Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, processorArchitecture=MSIL" could not be resolved because it has a dependency on "System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which is not in the currently targeted framework ".NETFramework,Version=v4.0,Profile=Client". Please remove references to assemblies not in the targeted framework or consider retargeting your project.

Also, where I have referenced with using statement in classes, I was getting errors like -

The type or namespace name 'Data' does not exist in the namespace 'Microsoft.Practices.EnterpriseLibrary' (are you missing an assembly reference?)

After some googling, I got this solution which might be of help to others as well -

This is a known issue in EntLib 5.0. You must target the full .NET Framework instead of the Client Profile (Right click on project, select Properties). The Data Access Application Block has a dependency on System.Data.Oracle that has been removed from the client profile.

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.