CREATE FUNCTION dbo.fnc_Split
(
@Data varchar(2000),
@Sep varchar(5)
)
RETURNS @Temp table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@Sep,@RData)>0)
Begin
Insert Into @Temp(data)
Select
Data = ltrim(rtrim(Substring(@Data,1,Charindex(@Sep,@Data)-1)))
Set @Data = Substring(@Data,Charindex(@Sep,@Data)+1,len(@Data))
Set @Cnt = @Cnt + 1
End
Insert Into @Temp (data)
Select Data = ltrim(rtrim(@Data))
Return
END
Friday, November 6, 2009
Thursday, October 29, 2009
Windows Run Commands
Windows Run Commands
Windows Run Commands
Windows has a lot of useful commands accessible from Run menu. To invoke the Run box, click on Start button and choose
Run, or hold down the Windows key and hit R. Run commands allows simply run applications by typing their name instead of click on an icon. Remember
these commands. They can save a lot of your time.
P.S. Some commands may be missed in your system.
|
Thursday, October 22, 2009
Thursday, October 15, 2009
Thursday, September 24, 2009
ranking in sql
RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008
1:12 AM Posted by Suprotim Agarwal
Labels: Transact SQL T-SQL The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.
The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.
Let us understand this difference with an example and then observe the results while using these two functions:
We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.
Using the RANK() function
SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.
Using the DENSE_RANK() function
SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.
Well I hope after seeing these example, you will understand
dense_rank() over(partition by field1 order by field2) as name1,
row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2
1:12 AM Posted by Suprotim Agarwal
Labels: Transact SQL T-SQL The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.
The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.
Let us understand this difference with an example and then observe the results while using these two functions:
We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.
Using the RANK() function
SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.
Using the DENSE_RANK() function
SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust
OUTPUT
As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.
Well I hope after seeing these example, you will understand
dense_rank() over(partition by field1 order by field2) as name1,
row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1 order by fldnm2)) order by fld2) as name2
Monday, September 21, 2009
assembly and reassembly
2 votes for this article.
Popularity: 1.20 Rating: 4.00 out of 5
1
2
3
4
5
Introduction
Do you have a distributed .NET application from the pre-WCF days that you want to migrate to WCF, but you either don't have the source because it's a third party component, or you can't change the source because your boss can't or won't make the jump to .NET 3.0? You just need to decorate your interfaces with some attributes which would be incredibly simple if you could modify the source code, but in those cases where you can't, it's not much harder to modify the MSIL.
Background
I have all these applications built for .NET remoting that are being actively maintained in a .NET 1.1 environment. When I built them, I split out the interface code from the implementation code because the interfaces are distributed in an assembly to clients and the implementation is in a server side assembly. This is exactly the way you would organize your ServiceContract in WCF, but I need one more thing to host the code in a WCF ServiceHost - the attributes. It would be really nice to add the [ServiceContract] and [OperationContract] attributes to my interface, but that means I have to recompile my interface library under .NET 3.0 and also branch my source control projects to maintain the separate versions, just to add some silly attributes. The more I think about it, that' s probably easier than updating the MSIL, but in some cases with legacy code, that isn't an option.
To port that old remoting application assembly over to WCF, the process is pretty simple:
Use ILDASM to disassemble your assembly into MSIL
Use your favorite text editor to add IL for referencing the assembly where the attributes are defined (System.ServiceModel), and then add IL for the attributes themselves to the interface and methods (ServiceContract and OperationContract)
Use ILASM to assemble your MSIL back into an assembly with your new attributes
Tedious? A little, but not as bad as trying to talk your boss into dropping .NET 1.1 support.
Disassembly
The easy part. Just make sure you aren't violating any license agreements here if you're working with third party code. They usually have a clause about how you better not disassemble it. If they do, turn back now and ask them to do this for you. Use the ildasm version (and later on, ilasm) that the existing assembly is built with to ensure you'll keep compatibility in your legacy environment. My assembly currently runs under .NET 1.1, so I'm using ildasm version 1.1.4322.573.
ildasm.dll /out=.il
Now you've probably got two files - the MSIL files (.il) and the resource file (.res). Open the MSIL file in your favorite text editor. Since you're digging into MSIL, you should make your background black and your text bright green Courier font so it's exactly how movie producers envisioned programmers hacking executables back in 1979.
You'll probably also find it helpful to disassemble an assembly from a working WCF project so you can see how the attributes are applied by the compiler.
Modification to IL
Tedious, but still easy. You need to add the referenced assembly to the top of the MSIL, where you'll find any other assembly references, like mscorlib and System:
.assembly extern System.ServiceModel
{
.publickeytoken = (B7 7A 5C 56 19 34 E0 89 ) // .z\V.4..
.ver 3:0:0:0
}
Now you need to find the interface. It will start with .class interface public abstract. Just below the curly brace but before the first .method, you need to add the [ServiceContract] attribute MSIL:
.class interface public abstract auto ansi IMyServiceInterface
{
.custom instance void [System.ServiceModel]
System.ServiceModel.ServiceContractAttribute::.ctor() = ( 01 00 00 00 )
Now you need to add the [OperationContract] MSIL to your methods. Since they are interface methods, they'll be abstract - just a pair of curly braces. You need to add the attribute inside the curly braces.
.method public hidebysig newslot abstract virtual
instance class MyReturnType
MyMethod(valuetype [mscorlib]System.Guid inputId,
string inputData) cil managed
{
.custom instance void [System.ServiceModel]
System.ServiceModel.OperationContractAttribute::.ctor() = ( 01 00 00 00 )
} // end of method IMyServiceInterface::MyMethod
Re-assembly
Once you've updated all the methods, it's time to recompile the assembly. If you haven't already, make a backup of your existing assembly just in case you break something. When you assemble the new file, you will need to remember to include the resource file if ildasm produced one.
Again, make sure you use the ilasm version that corresponds to the .NET version of your legacy assembly. Mine is .NET 1.1, so I'm using ilasm version 1.1.4322.2032. This will ensure that your assembly will still run under .NET 1.1 if necessary. The great thing about attributes is that if the executing code doesn't know about them, it just won't use them; you aren't going to break runtime compatibility.
ilasm /DLL.il /resource=.res
At this point you have the same .NET 1.1 compatible assembly, but your interface and methods are decorated with the attributes WCF will need when running under .NET 3.0. Take the updated interface assembly and the unchanged implementation assembly and rehost that legacy code in the robust and secure infrastructure provided by WCF!
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Popularity: 1.20 Rating: 4.00 out of 5
1
2
3
4
5
Introduction
Do you have a distributed .NET application from the pre-WCF days that you want to migrate to WCF, but you either don't have the source because it's a third party component, or you can't change the source because your boss can't or won't make the jump to .NET 3.0? You just need to decorate your interfaces with some attributes which would be incredibly simple if you could modify the source code, but in those cases where you can't, it's not much harder to modify the MSIL.
Background
I have all these applications built for .NET remoting that are being actively maintained in a .NET 1.1 environment. When I built them, I split out the interface code from the implementation code because the interfaces are distributed in an assembly to clients and the implementation is in a server side assembly. This is exactly the way you would organize your ServiceContract in WCF, but I need one more thing to host the code in a WCF ServiceHost - the attributes. It would be really nice to add the [ServiceContract] and [OperationContract] attributes to my interface, but that means I have to recompile my interface library under .NET 3.0 and also branch my source control projects to maintain the separate versions, just to add some silly attributes. The more I think about it, that' s probably easier than updating the MSIL, but in some cases with legacy code, that isn't an option.
To port that old remoting application assembly over to WCF, the process is pretty simple:
Use ILDASM to disassemble your assembly into MSIL
Use your favorite text editor to add IL for referencing the assembly where the attributes are defined (System.ServiceModel), and then add IL for the attributes themselves to the interface and methods (ServiceContract and OperationContract)
Use ILASM to assemble your MSIL back into an assembly with your new attributes
Tedious? A little, but not as bad as trying to talk your boss into dropping .NET 1.1 support.
Disassembly
The easy part. Just make sure you aren't violating any license agreements here if you're working with third party code. They usually have a clause about how you better not disassemble it. If they do, turn back now and ask them to do this for you. Use the ildasm version (and later on, ilasm) that the existing assembly is built with to ensure you'll keep compatibility in your legacy environment. My assembly currently runs under .NET 1.1, so I'm using ildasm version 1.1.4322.573.
ildasm
Now you've probably got two files - the MSIL files (
You'll probably also find it helpful to disassemble an assembly from a working WCF project so you can see how the attributes are applied by the compiler.
Modification to IL
Tedious, but still easy. You need to add the referenced assembly to the top of the MSIL, where you'll find any other assembly references, like mscorlib and System:
.assembly extern System.ServiceModel
{
.publickeytoken = (B7 7A 5C 56 19 34 E0 89 ) // .z\V.4..
.ver 3:0:0:0
}
Now you need to find the interface. It will start with .class interface public abstract. Just below the curly brace but before the first .method, you need to add the [ServiceContract] attribute MSIL:
.class interface public abstract auto ansi IMyServiceInterface
{
.custom instance void [System.ServiceModel]
System.ServiceModel.ServiceContractAttribute::.ctor() = ( 01 00 00 00 )
Now you need to add the [OperationContract] MSIL to your methods. Since they are interface methods, they'll be abstract - just a pair of curly braces. You need to add the attribute inside the curly braces.
.method public hidebysig newslot abstract virtual
instance class MyReturnType
MyMethod(valuetype [mscorlib]System.Guid inputId,
string inputData) cil managed
{
.custom instance void [System.ServiceModel]
System.ServiceModel.OperationContractAttribute::.ctor() = ( 01 00 00 00 )
} // end of method IMyServiceInterface::MyMethod
Re-assembly
Once you've updated all the methods, it's time to recompile the assembly. If you haven't already, make a backup of your existing assembly just in case you break something. When you assemble the new file, you will need to remember to include the resource file if ildasm produced one.
Again, make sure you use the ilasm version that corresponds to the .NET version of your legacy assembly. Mine is .NET 1.1, so I'm using ilasm version 1.1.4322.2032. This will ensure that your assembly will still run under .NET 1.1 if necessary. The great thing about attributes is that if the executing code doesn't know about them, it just won't use them; you aren't going to break runtime compatibility.
ilasm /DLL
At this point you have the same .NET 1.1 compatible assembly, but your interface and methods are decorated with the attributes WCF will need when running under .NET 3.0. Take the updated interface assembly and the unchanged implementation assembly and rehost that legacy code in the robust and secure infrastructure provided by WCF!
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Friday, July 31, 2009
export data image to excel from gridview
http://www.aspsnippets.com/post/2009/04/23/Export-GridView-with-Images-to-Word-Excel-and-PDF-Formats-in-ASPNet.aspx
Subscribe to:
Posts (Atom)