Count of Nodes in XML through Sql-Server

Hi,


Here is the example by which we can count that how many times a particular node is coming.



DECLARE @strXml XML = (
'<Parent>
<Child>1</Child>
<Child>2</Child>
<Child>3</Child>
<Child>4</Child>
<Child>5</Child>
<Child>6</Child>
<Child>7</Child>
</Parent>'
) 
DECLARE @cnt INT 
SET @cnt = 
(
SELECT
COUNT(*) AS NodeCount
FROM
(SELECT @strXml) AS XmlTable(XmlColumn)
CROSS APPLY 
XmlColumn.nodes('/Parent/Child') XmlTableFunction(XmlColumn2)
)

Chaning extension of a file

This is very usefull code for me. This code I was needed when my solution was migrated from VS2008 to VS2010. In VS2008 we are using the naming convention for our function and SP's like this : dbo.<function_name>.sql and dbo.<SP_name>.sql but in VS2010 we are using <function_name>.function.sql  and <SP_name>.proc.sql. I want to commit som 100+ files from VS2008 t0 VS2010 so I uses below code which changes the file names.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
       static void Main()
        {
            //Path where all the files are stored.
            string[] filePaths = Directory.GetFiles(@"E:\test\");
            for(int i = 0;i<filePaths.Length;i++)
            {
                string oldFileName=filePaths[i].ToString();
                //This will remove the prefix and suffix name of the file
                string fileName = oldFileName.Substring(0,oldFileName.LastIndexOf("."));
                //This will add the suffix name of the file
                string NewFileName=@"E:\test\"+(fileName.Substring(fileName.LastIndexOf(".")+1))+".function.sql";
                //It creates the copy of the file
                File.Copy(oldFileName, NewFileName);
                //It deletes the old file
                File.Delete(oldFileName);
            }
        
        }
    }
}

Allow only numeric character in JavaScript

Hi,


Here is the example code of Javascript which allows you to type only numeric value in a textbox.


Here is the features of this code :

• It allows only numeric value with decimal point.
• It allows only one decimal point in a textbox.
• It allows only two character after decimal point.

$("#txtExample").keydown(function (event) {
var a = $("#txtWeightChangePercent").val();
var len = '';
if ((a.indexOf('.') - 1) == 0)
len = a.length - a.indexOf('.') - 1;
else
len = a.length + (a.indexOf('.') - 1);

if (len > 1 && event.keyCode != 8) {
event.preventDefault();
}

if (event.keyCode == 46 || event.keyCode == 8 || event.keyCode == 190 || event.keyCode == 110) {
if ((a.split(".").length - 1) > 0 && event.keyCode != 8) {
event.preventDefault();
}
// Some thing goes here
}
else {
// Ensure that it is a number and stop the keypress
if ((event.keyCode < 48 || event.keyCode > 57) && (event.keyCode < 96 || event.keyCode > 105) && (event.keyCode <= 42 || event.keyCode >= 40) && (event.keyCode <= 33 || event.keyCode >= 45)) {
event.preventDefault();
}
}
});


If you don't want to allow decimal also then remove this "|| event.keyCode == 190 || event.keyCode == 110" 
Thx,
RS

Ranking Function

We have four types of ranking function in sql server


1.) row_number
2.) rank
3.) dense_rank


Row_Number() Function


The ROW_NUMBER() function generates the auto incrementing integer number to the sort order of the OVER() clause


it is used like this :


SELECT
       ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNumber
       ,SalesOrderID
       ,Sales
FROM
       SalesOrder




Rank() and Dense_rank() Function


RANK() and DENSE_RANK() function return values as if the rows were computing according to the sortorder.
For example if two record have the same sortorder value then both have same rank value. but next value in rank function will be +2 and in denseRank function it will be +1.


it is used like this :


SELECT
      RANK() OVER(ORDER BY SalesOrderID) AS RANK
      DENSE_RANK() OVER(ORDER BY SalesOrderID) AS DENSE_RANK
     ,SalesOrderID
     ,Sales
FROM
     SalesOrder

Connecting BIDS or Visual Studio 2008 with TFS 2010

In my project I am working on Visual Studio 2010 and I am using TFS 2010. For SSRS we have to use VS 2008 Or BIDS. But TFS 2010 does not allow us to directly chekin VS 2008 or BIDS Files in our solution. For check in those files we have to perform following step.

1. Install Visual Studio 2008. 
2. Install Visual Studio Team explorer. Download Here
3. Install Visual Studio 2008 SP1. Download Here
4. Install Visual Studio 2008 SP1 Forward compatibility Update For TFS 2010. Download Here

Make Sure all these things should install in the mention order.


After these installation we have to give the server ip address for tfs like this .
http://[ip address]:[portnumber]/[tfs]/[]folddername]

Getting Hard Disk Serial No. in C#

Every hard disk has a unique serial number which we can get by our code here is a example which gives you the you computer's c drive serial number.

using System.Management;

public string GetHDDSerialNumber(string drive)

        {

            //check to see if the user provided a drive letter

            //if not default it to "C"

            if (drive == "" || drive == null)

            {

                drive = "C";

            }

            //create our ManagementObject, passing it the drive letter to the

            //DevideID using WQL

            ManagementObject disk = new ManagementObject("win32_logicaldisk.deviceid=\"" + drive + ":\"");

            //bind our management object

            disk.Get();

            //return the serial number

            return disk["VolumeSerialNumber"].ToString();

        }

Replacing Tab key functionality with Enter Key C#

Sometime we need to replace the Enter key functionality with the tab key here is a sample which can help you :

public static void EnterTab(System.Windows.Forms.KeyPressEventArgs e)

{

   if (e.KeyChar == 13)

   {

     System.Windows.Forms.SendKeys.Send("{tab}");

   }

}

Manipulate Header and Footer values at each page

Generally in SSRS Header and Footer value is same for a report. Sometime we require to show different value in the footer or header on the bases of database of each page of SSRS.
For acheiving this we have to refer textbox name which is in report area or if it is not there we have to create a text box and give that value which we want to show in header or footer then we can hide it. Refer Text Box Name AS : ReportItems!Textbox208.value
Now in Header or footer for text box’s expression we can use = ReportItems!Textbox208.value.


With the help of this we can get different value in header and footer at each page of report.

Creating Connection String Dynamically

For creating the connection string dynamically first you create a notepad file and save it at desktop with the extension .udl

For Example:

Right Click on desk top and New -> Text Document

Go to File-> Save As. Save this file with extension .udl

Now Just Double Click on your udl file. And select provider

Select Microsoft OLEDB Provider for SQLServer and click on Next Button;

Now in Give the server name for your machine you can give '.' or your computer name.

Submit user name & password of your database, Select the Database Name Check the connection with click on test connection and then click on ok. Now open that udl file in notepad you an find connection string in that. 

Retrieve the Image From the Database

If we want to retrive the image from the database then first we pick the image from the database and then convert it into the image :

Here I am store the image in to database in an byte array now I am converting that byte array in to an image :

Image MyImage = ConvertImage(imageData);

public static Image ConvertImage(object value)

{

     byte[] img;

     Image newImage;

     img = (byte[])value;

     //Read image data into a memory stream

     using (MemoryStream ms = new MemoryStream(img, 0, img.Length))

     {

        ms.Write(img, 0, img.Length);

        //Set image variable value using memory stream.

        newImage = Image.FromStream(ms, true);

     }

     return newImage;

}

picturebox1.Image = MyImage;

Save the Image in to database

Here is a code which we can use for storing the image into database for this we can convert the image into a byte array and then store that array in to data base. The method is as follows:

byte[] imageData = CreateImage.convertPicBoxImageToByte(pb1);

public static byte[] convertPicBoxImageToByte(System.Windows.Forms.PictureBox pbImage)

{

    System.IO.MemoryStream ms = new System.IO.MemoryStream();

    pbImage.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

    return ms.ToArray();

}

Now save that imageData variable in database as a normal way.

Sql directives order

When we are optimizing the SQL statements to improve the performance I thought this tip would help the figure out where to tweak their code.

This is how the order in which sql directives (clauses) get executed when you write a select statement.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Insert Multiple records into a SQL-Server Database

To insert multiple records into a SQL Server database, we need to call the INSERT INTO  query every time.
Suppose if we want to insert 10 records, we need to call INSERT INTO statement 10 times.
As every time we invoke the INSERT INTO statement the INSERT INTO statements are repeated multiple times.
The work around is to use one Single INSERT INTO statement in SQL Server.
There are two methods Available to Insert Multiple Records in Single INSERT INTO Statement


1. Using UNION ALL Keyword
2. SQL Server 2008 Row Construction Method


Assume the database TEST_DB contains a table called Student with the following fields:


1. StdID
2. First Name
3. Last Name
4. Country
5. State
6. ZipCode


To Insert 5 records we have to call INSERT INTO 5 times as follows:


MULTIPLE INSERT STATEMENTS


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1002, 'Student2','User2', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1003, 'Student3','User3', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1004, 'Student4','User4', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1005, 'Student5','User5', 'INDIA','Banglore','560068')


GO


1). USING UNION ALL


The same result can be accomplished using the following Code.


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
SELECT 1001, 'Student1','User1', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1002, 'Student2','User2', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1003, 'Student3','User3', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1004, 'Student4','User4', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1005, 'Student5','User5', 'INDIA','Banglore','560068'


GO


2). USING SQL Server 2008 Row Construction Method


USE TEST_DB
GO


INSERT INTO Employee  (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001'),
(1002, 'Student2','User2', 'INDIA','Bhopal','462001'),
(1003, 'Student3','User3', 'INDIA','Banglore','560068'),
(1004, 'Student4','User4', 'INDIA','Banglore','560068'),
(1005, 'Student5','User5', 'INDIA','Banglore','560068')

Find Duplicate Entry

In this way we can find duplicate entry in the database


-- create table for test data
create table test_data (
id int,
product char(10)
)


-- insert test data


insert into test_data values(1,'item 1')
insert into test_data values(2,'item 2')
insert into test_data values(3,'item 3')
insert into test_data values(3,'item 4')
insert into test_data values(5,'item 5')
insert into test_data values(5,'item 6')
insert into test_data values(5,'item 7')
insert into test_data values(8,'item 8')


-- find duplicate id
select id, count(*) #_of_dups
from test_data
group by id
having count(*) > 1


-- drop table test_data
drop table test_data

Detail members can only contain static inner members

Hi,
'The tablix 'Table1' has a detail member with inner members. Detail members can only contain static inner members. 

This is the one of the generic Error in SSRS 2008. This error will come when we are putting a table inside another table and forget to gives the group property of a parent table. It is always coming when we are having a table inside another table and if parent table does not have any group. So whenever we are using a table in side another table that time it is necessary to give the group property to the parent table.