TSQL: Parsing delimited string into table

CREATE FUNCTION dbo.udf_ItemParse (
@Input VARCHAR(8000), @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE (
Item VARCHAR(50) ,
Pos int
)
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Pos = @Pos + 1
SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
END
ELSE
BEGIN
SET @Pos = @Pos+1
SET @Item = @Input
SET @Input = ''
END
INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
END
RETURN
END

SoapExtension for Logging Soap Exceptions

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.IO;
using System.Xml;
using MIT.BizServices.MCUtil.Misc;

namespace MIT.Common.ExceptionHandlingSoapExtension
{
//

// SOAP extension to transfer full exception information from
// server to client in the event of an exception being thrown
// from a webmethod
//

//
// In the event of an exception being thrown during the execution
// of a webmethod on the server, this class steps in and writes detailed
// exception information to the output stream.
//
// Client side, if an exception is detected on the response from a
// webmethod call, this class retrieves the detailed exception
// information from the input stream and throws an exception
// containing the detailed information
//
public class ExceptionHandlingSoapExtension : SoapExtension
{

region "Initialisation methods - not used because this class has no state to maintain"
public override object GetInitializer(System.Type serviceType) {
return null;
}

public override object GetInitializer(System.Web.Services.Protocols.LogicalMethodInfo methodInfo, System.Web.Services.Protocols.SoapExtensionAttribute attribute) {
return null;
}

public override void Initialize(object initializer)
{
}
endregion

region "Stream chaining code"
private Stream oldStream;
private Stream newStream;

public override Stream ChainStream(Stream stream) {
oldStream = stream;
newStream = new MemoryStream();
return newStream;
}

//

// Copies the contents of one stream to another
//

private void StreamCopy(Stream source, Stream dest) {
StreamReader Reader = new StreamReader(source);
StreamWriter Writer = new StreamWriter(dest);
Writer.WriteLine(Reader.ReadToEnd());
Writer.Flush();
}
endregion

public override void ProcessMessage(System.Web.Services.Protocols.SoapMessage message)
{
switch (message.Stage)
{
case SoapMessageStage.BeforeSerialize:
return;

case SoapMessageStage.AfterSerialize:
//If exception present in message, write details
//to the new stream
if (message.Exception != null)
{
InsertExceptionDetails(message.Exception);
}

//Copy new stream to old stream
newStream.Position = 0;
StreamCopy(newStream, oldStream);
return;

case SoapMessageStage.BeforeDeserialize:
//Copy old stream to new stream
StreamCopy(oldStream, newStream);
newStream.Position = 0;
return;

case SoapMessageStage.AfterDeserialize:
//If exception present in message,
//get details from stream and throw to caller
if (message.Exception != null)
{
CheckExceptionDetails();
}
return;

default:
throw new ArgumentException("Invalid message stage");

}
}

//

// Insert details of the specified exception into the output stream

// <param name="ex">Exception to write details for
private void InsertExceptionDetails(Exception ex) {
//Read output stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Construct string describing exception
string ErrorInfo;
if (ex.InnerException != null)
{
ErrorInfo = ex.InnerException.Message;
}
else
{
ErrorInfo = ex.Message;
}
//log exception to flat file
Helper.LogException(string.Empty, ex);

//Find existing soap:Fault node describing exception
XmlNode ExceptionNode;
ExceptionNode = MessageDoc.SelectSingleNode("//soap:Fault", NsMgr);

//Add extended exception detail node to Fault node
XmlElement ExceptionDetail;
ExceptionDetail = MessageDoc.CreateElement("ExtendedExceptionDetails");

ExceptionDetail.InnerText = ErrorInfo;

ExceptionNode.AppendChild(ExceptionDetail);

//Write XML document back to output stream
newStream = new MemoryStream();
MessageDoc.Save(newStream);
}

//

// Reads extra exception information from stream

// Details of any exception detail found in the input stream
private void CheckExceptionDetails()
{
//Read input stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Find extended exception detail node
XmlNode ExceptionDetailNode;
ExceptionDetailNode = MessageDoc.SelectSingleNode("//soap:Fault/ExtendedExceptionDetails", NsMgr);
XmlNode exception = MessageDoc.SelectSingleNode("//soap:Fault/faultstring", NsMgr);

//Return detail text if found, empty string otherwise
if (ExceptionDetailNode != null)
{
if (exception != null)
{
if (exception.InnerText.Contains("WebServiceException"))
{
throw new WebServiceException(ExceptionDetailNode.InnerText);
}
else
{
throw new Exception(ExceptionDetailNode.InnerText);
}
}
}
}
}
}

Exception and Logging Application Block

Add reference to the following DLL's:
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Logging
If you intend to log information to a database, you also need to reference the Microsoft.Practices.EnterpriseLibrary.Data.dll














For adding tracing in the method call use the following:
public DataSet GetPricingDataSet(string loanNumber)
{
using Tracer("Trace")
{
DoSomething();
//For additional logging use:
Logger.Write("message")
//or use:
Logger.Write(new LogEntry("message"))
}
}

Add a Trace listener in the Logging Application Block via the Enterprise Library Configuration tool and then specify a Category called "Trace" which logs to this newly configured Trace Listener.

For more info:
http://www.devx.com/dotnet/Article/31463/1954?pf=true

http://www.devx.com/dotnet/Article/35736?trk=DXRSS_DOTNET
http://www.devx.com/dotnet/Article/36184?trk=DXRSS_DOTNET - Enterprise Library 3.0
======================================================
For Exception Handling:
======================================================
Add references to the following DLL's:
ExceptionHandling.dll
ExceptionHandling.Logging.dll

public static void LogException(string policyName, Exception ex)
{
if (string.IsNullOrEmpty(policyName))
policyName = ConfigurationManager.AppSettings["ExceptionPolicy"];

ExceptionPolicy.HandleException(ex, policyName);
}

TSQL to concatenate column values into a single string

DECLARE @cols AS varchar(3000)
;
WITH YearsCTE
AS
(SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader)

SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS varchar(4)) + ']'
FROM YearsCTE
ORDER BY [YEAR]

Note the use of ISNULL. This adds a additional "," to separate records. Note the isnull helps to add the additonal "," only to records numbered 2 or greater.

Create and deploy a Sharepoint Feature

Suppose you need to replace the SmallSearchInputBox on the sharepoint site with a new feature. Follow these steps:

step1 : create a copy of existing feature e.g. controlLightUp
Thus copy this folder from 12\Template\Features\CustomLightUp to 12\Template\Features\NewSearchArea

Step2: modify feature.xml to contain the new GUID and add a reference to the element.xml (within a subdirectory or the same directory as feature.xml, whichever you prefer) which describes the feature.
Specify if the feature should be HIDDEN or displayed in the Sharepoint site settings
Specify Title and Description if this feature is NOT Hidden

Step 3: then modify the elements.xml file in controls folder to have the correct settings. Scope can take one of the following values: FARM, Webapplication, Web and Site
Site is SiteCollection
Web is Site
Web Application is parent of Sitecollection
FARM is server wide enabling of the feature

Step4: suppose CustomLightUp feature is installed at FARM level snd it is referenced through a delagate control in the master page and its control id "SmallSearchInputBox", and you want to create a new feature at the Site scope and for the same delegate control
Then there are 2 ways to achioeve this

1: rename the Delegate control id from "SmallSearchInput" to "newSearch" then then make the modification in elements.xml to contain this new control id. then install the feature at Site level using stsadm utility

2. you can keep the control id the same i.e. "SmallSearchInput" but then modify the Sequence value in elements.xml to have a value less than 100.
(for more on this refer to my previous blog on delegate control) Then install the feature at the site level using stsadm
command is stsadm -o installfeature -filename NewSearchArea\feature.xml
stsadm -o activatefeature -filename NewSearch\feature.xml -url http://w.c.com

For automatic deployment of the feature read this:
http://www.codeplex.com/wspbuilder
Or
http://msdn.microsoft.com/msdnmag/issues/07/05/OfficeSpace/
and
http://msdn.microsoft.com/msdnmag/issues/07/08/OfficeSpace/

if you want to list all existing features then you need to extend the stsadm utility:
http://sharepointsolutions.blogspot.com/2006/09/extending-stsadmexe-with-custom.html
and
http://msdn2.microsoft.com/en-us/library/bb417382.aspx
and
http://www.andrewconnell.com/blog/articles/MossStsadmWcmCommands.aspx
and
http://stsadm.blogspot.com/2007/08/enumerate-features.html

Automating Dev, QA, Staging, and Production Web.Config Settings with VS 2005

One of the questions I get asked fairly regularly is: "how can I can easily change different configuration settings in my web.config file based on whether my application is in a dev, qa, staging or production mode?" The most common scenario for this is one where an application uses different database connection-strings for testing and production purposes.

It turns out you can easily automate this configuration process within the Visual Studio build environment (and do so in a way that works both within the IDE, as well as with command-line/automated builds). Below are the high-level steps you take to do this. They work with both VS 2005 and VS 2008.

1. Use ASP.NET Web Application Projects (which have MSBuild based project files)
2. Open the VS Configuration Manager and create new "Dev", "QA", "Staging" build configurations for your project and solution
3. Add new "web.config.dev", "web.config.qa", and "web.config.staging" files in your project and customize them to contain the app's mode specific configuration settings
4. Add a new "pre-build event" command to your project file that can automatically copy over the web.config file in your project with the appropriate mode specific version each time you build the project (for example: if your solution was in the "Dev" configuration, it would copy the web.config.dev settings to the main web.config file).

Once you follow these steps, you can then just pick the mode your solution is in using the configuration drop-down in the VS standard toolbar:

The next time you build/run after changing the configuration mode, VS will automatically modify your application's web.config file to pick up and use the web.config settings specific to that build configuration (so if you select QA it will use the QA settings, if you select Deploy it will use the Deploy settings).

The benefit with this approach is that it works well in a source control environment (everyone can sync and build locally without having to make any manual changes on their local machines). It also works on a build server - including with scenarios where you are doing automated command-line solution builds.

To learn more about the exact steps to set this up, please read the Managing Multiple Configuration File Environments with Pre-Build Events post that Scott Hanselman published earlier tonight. Also check out ScottGu's ASP.NET Tips, Tricks, and Gotchas page for other ASP.NET Tips/Tricks recommendations.

multi column sorting of a typed collection

Use the following One line code for multi-column or single column sorting of a typed collection:

List<Liability> liabList = new List<Liability>();

liabList.Sort(delegate(Liability l1, Liability l2)
{ int r = l1.Type.CompareTo(l2.Type);
if (r == 0 && l1.HolderName != null)
r = l1.HolderName.CompareTo(l2.HolderName);
if (r == 0)
r = l1.Amount.CompareTo(l2.Amount);
return r;
} );

Nant Intellisense with Nant Contrib tasks and VS2005 nant Addin

Nant Intellisense with Nant Contrib tasks and VS2005 nant Addin.

First task is to generate the XSD (schema) file that needs to be added inorder to enable intellisense.
use the following Nant .build script to generate the nant.xsd file:

<project name="nant" default="go">
<property name="NAntContrib" value="c:\rohit\nantcontrib-0.85\bin" />
<target name="go">
<loadtasks assembly="${NAntContrib}\NAnt.Contrib.Tasks.dll" />
<nantschema output="nant.xsd" target-ns="http://nant.sf.net/nightly/2006-03-08-0.85/nant.xsd"/>
</target>
</project>

Build this script using nant:
nant /f:nantxsd.build
This will create the nant.xsd file.

Copy this file nant.xsd to 'C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\Packages\schemas\xml' on your system, or if you are using VS2005 then copy to "C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas".

In your NAnt build file (I make my with the extension .build) include this xmlns attribute:
<project xmlns="http://nant.sf.net/nightly/2006-03-08-0.85/nant.xsd" name="test" default="build">

Now you will get intellisense for all nant tasks as well nant contrib tasks.
==============================================================
To add the Nant Addin for VS2005.
Download Nant addin from:
http://sourceforge.net/projects/nantaddin

Create a directory for Visual Studio Addins ex : "%Program Files%/Microsoft Visual Studio 8/Addins" Copy the NAntAddin directory along with the "NAntAddin.AddIn" file into it.

open VS2005. click Tools\Options.
Click on Envrironment\"Add-in/Macros Security"
Then click "Add" to add the "%Program Files%/Microsoft Visual Studio 8/Addins" directory.

Restart VS 2005.
Then click Tools\Add-in Manager.
Then check the checkbox against "NantAddin".

Now you will be able to run nant scripts or part of it directly from within VS2005.