Wednesday, 30 December 2009

C# load multidimensional array to datatable

[Test]
public void Test()
{
int[,] numbers = new int[3, 2] { { 9, 99 }, { 3, 33 }, { 5, 5 } };
DataTable dt = new DataTable();

Console.WriteLine(numbers.Rank);
Console.WriteLine(numbers.Length);

for (int dimension = 0; dimension < numbers.Rank; dimension++)
{
dt.Columns.Add("Column"+(dimension+1));
}

Console.WriteLine("Array");
for (int element = 0; element < (numbers.Length / numbers.Rank); element++)
{
DataRow row = dt.NewRow();
for (int dimension = 0; dimension < numbers.Rank; dimension++)
{
Console.Write("{0} ", numbers[element,dimension]);
row["Column" + (dimension + 1)] = numbers[element, dimension];
}
dt.Rows.Add(row);
Console.WriteLine();
}

Console.WriteLine("DataTable");
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
Console.Write("{0} ", row[column]);
}
Console.WriteLine();
}
}

Tuesday, 29 December 2009

SQL server concatenate

DECLARE @test TABLE (
FirstName varchar(50)
)
--
INSERT INTO @test
SELECT 'Jon'
UNION SELECT 'Tom'
UNION SELECT 'Mike'
--
SELECT STUFF((
SELECT DISTINCT ', ' + FirstName
FROM @test
FOR XML PATH('')
), 1, 2, '')
FOR XML does the concat, STUFF removes the first 2 characters from position 1 - i.e. the initial unwanted  ', '

List in Excel

excel-list

SQL server indexed temp table

IF (SELECT object_id('TempDB..#TempTable')) IS NOT NULL
DROP TABLE #TempTable
GO
--
CREATE TABLE #TempTable
(
id int,
name varchar(50),
primary key (id, name)
)
--
INSERT INTO #TempTable
SELECT 1,'name'
--
SELECT * FROM #TempTable