Website - Great Site for Learning Serialization to XML - XmlSerializer

http://www.switchonthecode.com/tutorials/csharp-tutorial-xml-serialization


236 unique visitors

Website - Sorting of String by Array / LINQ / Lists

http://dotnetperls.com/Content/Sorting-String-Array.aspx

236 unique visitors

A different way to insert Data into a Gridview from array

strProjListFinal = strAERProjList.Trim() + ";" + strIAProjList.Trim();
string[] strs = strProjListFinal.Split(';');
ddlProjectName.Items.Clear();
foreach (string s in strs)
{
ddlProjectName.Items.Add(s.Trim());
}
ddlProjectName.Items.Insert(0, new ListItem("All Projects", "-1"));


236 unique visitors

Website to get conversion techniques of DATETIME in sqlserver

To understand difference between

CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),101) is MMDDYYYY

and

CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),103) is DDMMYYYY

The website is: -

http://www.sql-server-helper.com/tips/date-formats.aspx

234 unique visitors

SQL Server - Create Temporary Tables & Insert into Temporary Table

DECLARE @tbl table
(
ID int identity,
ProjName varchar(100)
)
INSERT into @tbl(ProjName)
SELECT * from dbo.SemisvToString(@IAAudioEyeProjectNames)

also

CREATE TABLE #AERTTV2TempReportTable
(
ProjectName varchar(200),
TaskName varchar(400), --'CHANGED THIS 200 to 400 '
HoursSpent decimal(18,2),
ExecutionDate varchar(20),
UserName varchar(200)
)

TRUNCATE TABLE #AERTTV2TempReportTable

INSERT INTO #AERTTV2TempReportTable(ProjectName, TaskName, HoursSpent, ExecutionDate, UserName)
SELECT ProjectName, TaskName, HoursSpent, CONVERT(VARCHAR(20),ExecutionDate,101) AS ExecutionDate, UserName
FROM AudioEyeReporting.dbo.TempReportTable
WHERE IsDeleted = 0 AND ExecutionDate BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT ProjectName, TaskName, HoursSpent, CONVERT(VARCHAR(20),CONVERT(DATETIME,ExecutionDate),101) AS ExecutionDate, UserName
FROM TimeTrackerV2.dbo.TempReportTable
WHERE ProjectName IN (SELECT ProjName FROM @tbl) AND ExecutionDate BETWEEN @StartDate AND @EndDate

SELECT * FROM #AERTTV2TempReportTable

SELECT SUM(HoursSpent)
FROM #AERTTV2TempReportTable
DROP TABLE #AERTTV2TempReportTable

234 unique visitors

SQL Server - Function to Get individual values from Comma Separated Values (CSVs)

1. CSV - String

CREATE FUNCTION[dbo].[CsvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ','
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ','
WHILE patindex('%,%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%,%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

2. CSV - Int

CREATE Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end

3. SSV(Semicolon Separated Values) - Int

ALTER FUNCTION[dbo].[SemisvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ';'
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ';'
WHILE patindex('%;%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%;%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

234 unique visitors