Table Valued Split Function

During the initial phase of development of our project one of my colleague stumbled upon a table-valued Split function (from the net ofcource J )
I thoroughly enjoyed using this Split function throughout my SPs and functions.
Dynamic Query cannot be used in Functions and I’d a requirement to write a table valued function which has to parse a variable number of Items.
I got around the problem by using the Split function in it.
This is what I did:
Send the list of ItemIds as a comma separated string
Parse it using the Split function inside the Table Valued function.

The Split Function code goes like this:

CREATE FUNCTION [dbo].[Split](@List nvarchar(2000), @SplitOn
nvarchar(5))
RETURNS @RtnValue TABLE (Id INT
IDENTITY(1,1), Value NVARCHAR(100))
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue
(value)
SELECT Value =
LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)));

SET @List =
SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List));
END
INSERT INTO @RtnValue (Value) SELECT Value =
LTRIM(RTRIM(@List));

RETURN;
END

Courtesy: to the unknown Internet site that hosted it and to the unknown developer who created it.

Agreed that this function has limitation like you cannot parse a very big string since the Value column’s limit is 100.
But still this Split function is very useful!!

Grouping in Infragistics Excel

The below code shows how to group using Infragistics Excel

using System;
using System.Collections.Generic;
using System.Text;
using Infragistics.Excel;
namespace ConsoleApplication1{
class Program
{
static void Main(string[] args)
{
Workbook theWorkBook = new
Workbook();
Worksheet
theWorkSheet;
theWorkSheet =
theWorkBook.Worksheets.Add("Customers");
theWorkSheet.Rows[0].OutlineLevel =
0;
//the outline property is the key to Grouping
theWorkSheet.Rows[0].Cells[0].Value =
"hello";
theWorkSheet.Rows[1].OutlineLevel = 1;

theWorkSheet.Rows[1].Cells[0].Value =
"hello1";
theWorkSheet.Rows[2].OutlineLevel =
2;
theWorkSheet.Rows[2].Cells[0].Value =
"hello2";
theWorkSheet.Rows[2].Expanded =
false;
theWorkSheet.Rows[1].Expanded =
false;
BIFF8Writer.WriteWorkbookToFile(theWorkBook,
"C://file.xls");
}
}
}

Excel Export for Multi-Header UltraGrid

This is in continuation of the Blog on Multi-Header implementation of UltraWebGrid.

If the UltraWebGrid has multi-header then UltraWebGridExcelExporter doesnot export the Grid properly. The only way to get around the problem is to create a custom method to write the Grid data in xls format.
One way of doing this is by using StringBuilder to create html table and then passing it as a response. The Column and Rowspacing can be achieved using RowSpan and ColumnSpan of Table Header

Multi header implementation in UltraWebGrid

Multi-Header Implementation is possible in Infragistic's UltraWeGrid.



So how to do it:

1. Mostly if we are going for Multi column header then there should be some static Columns
2. So first write the code to Bind the DataSet
3. Use Initialize Layout Event to write the Static Columns and also to work on the Spanning of the columns.
   

Sample Code

public void UltraWebGrid1_InitializeLayout(object sender,
Infragistics.WebUI.UltraWebGrid.LayoutEventArgs e)
{
// Create Static UltraGrid Header
Infragistics.WebUI.UltraWebGrid.ColumnHeader ch = new
ColumnHeader(true);
// other Code
// .......
// Initilaize the Spans here
ch.RowLayoutColumnInfo.OriginY = 0;
ch.RowLayoutColumnInfo.OriginX = 0;
ch.RowLayoutColumnInfo.SpanX = 2;
ch.RowLayoutColumnInfo.SpanY = 1;
// other Codes
}

The Methods Span X, OriginX are self explanatory, but what is important is to visualize how the axis is getting created in UltraWebGrid. This is shown in the dig above.
One more thing to note is correct calculation of the Spans and Origins of each header is critical.
If you get it wrong then

1. The Headers may be scrambled
2. Some Headers may be lost

Note that no error Will be thrown on either of the case so write the Origin and Spanning of the columns carefully

The Story Behind
I was new to .net and Infragistics and one requirement came up to construct a Grid using Multi-Header. I know for sure that this will be possible using Infragistics but don't know how exactly to do it. I tried a lot of googling but in vain. Finally I got a Chinese web site which talked about this :)

That was a wake up call for me, I thought how many ppl would've searched for this implementation and would've suffered?

I decided then that I should write a Blog about this and other things which I know and assume many ppl do not know. May be a guy reading this implementation might think its silly to do it in the way i wrote and can come back with a better approach. Or may be I'm duplicating a thing which would've been already known to many ppl. If my consciousness says to share something then I will definitely share it.

Table Dependency Issue

SQL Server's inbuilt feature of finding the dependency for a Table seems to have an issue. If a SP has just an "Insert into Table1" then this SP (say SP1) will not be shown as a dependent to Table1!! If I just add "Select * from Table1" in the above SP then SP1 is getting showed as dependent to Table1!!!!!!!!!!So if we are going to use the "View Dependency" feature then we are gonna have some trouble. The best way to get around this problem is to use the following "Text" search query

SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%Table1%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

Preamble

I’m a software engineer with 3 years experience. I’ve been coding in .NET 2.0 and SQL Server 2005 for almost a year now. I use Infragistic’s NetAdvantage 6.2 to do most of the UI stuff in ASP.NET. Whenever I encounter some problem or don’t know how to do a piece of code I search the net for sample codes. There are times when I don’t get the answers and I have to toil a lot to fix the issues. I did some introspect and found out that I always consume things but never shared the info I know. So I decided to write this blog to share something I know on .NET, SQL Server and Infragistics. All the tips and code snippets I’m going to share are not really hi-fi stuff, but are a bit elusive.