Collapsible Nested GridView with Paging using ASP.Net

http://aspsnippets.com/Articles/Collapsible-Nested-GridView-with-Paging-using-ASPNet.aspx
http://www.aspsnippets.com/Articles/Nested-GridView-Example-in-ASPNet-using-C-and-VBNet.aspx
http://www.aspdotnet-suresh.com/2012/05/gridview-with-in-gridview-or-nested.html
http://www.dotnetfox.com/articles/multilevel-nested-gridview-in-Asp-Net-using-C-Sharp-1012.aspx
http://www.codeproject.com/Articles/189997/Gridview-inside-Gridview-in-asp-net-Csharp

Editable Nested GridView (All-in-One)

http://www.codeproject.com/Articles/20047/Editable-Nested-GridView-All-in-One

Finding numbers from a text in SQL Server.

CREATE FUNCTION [dbo].[GetNumbersFromText](@String VARCHAR(2000))
RETURNS @Number TABLE (Number INT)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
--Find a numeric charactor
IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
--If the next charactor is not a numeric one, the current number ends, so add a separator
IF (SUBSTRING(@String,@Count+1,1) < '0'OR SUBSTRING(@String,@Count+1,1) > '9') AND SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + ','
END
SET @Count = @Count + 1
END
---Split string to give a table with the numbers in the text
INSERT INTO @Number
SELECT DISTINCT items FROM dbo.Split(@IntNumbers, ',')
return
END

how to get only numbers from a string in sql server

declare  @strAlphaNumeric nvarchar(max)
set @strAlphaNumeric='aa12bbc1234c'
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
print @strAlphaNumeric+','
END
END
select ISNULL(@strAlphaNumeric,0)


CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';
GO

SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities

Recently, I read a question on my friend Ritesh Shah‘s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema.routines does give proper answer.

I have quite often seen that information_schema.routines gives proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended.

Let us quickly see where sp_depends fail and other solutions work fine.

Let us first create two scenarios.

Scenario 1: Normal Table Creation Order, where objects are created first and then used afterwords.

USE TempDB
GOCREATE TABLE dbo.TestTable( ID INT,Name VARCHAR(100))GO-- dbo.First is not created yetCREATE PROCEDURE dbo.SecondAS
EXEC dbo.First
GOCREATE PROCEDURE dbo.FirstAS
SELECT ID, NameFROM TestTable
GO

Scenario 2: Objects are created afterwords and they are referenced first.

USE TempDB
GOCREATE TABLE dbo.TestTable( ID INT,Name VARCHAR(100))GOCREATE PROCEDURE dbo.FirstAS
SELECT ID, NameFROM TestTable
GO-- dbo.First is already createdCREATE PROCEDURE dbo.SecondAS
EXEC dbo.First
GO

Now let us run following three queries on both the scenarios.

-- Method 1: Using sp_dependssp_depends 'dbo.First'GO-- Method 2: Using information_schema.routinesSELECT *FROM information_schema.routines ISRWHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
GO-- Method 3: Using DMV sys.dm_sql_referencing_entitiesSELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');GO

Result from Scenario 1

Result from Scenario 2

It is clear that sp_depends does not give proper/correct results when the object creation order is different or following deferred name resolution.

I suggest the use of the third method, in which sys.dm_sql_referencing_entities is used.

Use the following script to get correct dependency:

SELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');GO

Let me know the type of scripts you use for finding Object Dependencies. I will post your script with due credit.

Learn MVC (Model view controller) Step by Step in 7 days – Day 1

http://www.dotnetspider.com/resources/42969-Learn-MVC-Model-view-controller-Step-by-Step.aspx

spilt more than one value

CREATE TABLE #temp(
[Day] [varchar](500) NULL,
[RawKey] [int] NULL,
[TableID] [int] NULL,
[FromTime] [varchar](500) NULL
)
insert into #temp
select 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday', 1, 1, '10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00'


;WITH CTE1 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
Cross Apply
(
SELECT y.value('.', 'VARCHAR(30)') AS SplitString FROM DelimitedStringXML.nodes('//d') as  x(y)

) as b
)
,CTE2 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
CROSS APPLY
(
SELECT h.value('.', 'VARCHAR(30)') AS SplitValue FROM DelimitedValueXML.nodes('//d') as  g(h)

) as c
)


SELECT a.RawKey,a.SplitString,b.SplitValue
FROM CTE1 as a
INNER JOIN CTE2 as b
on  a.TableID= b.TableID
AND a.RN = b.RN

drop table #temp

SQL Server CROSS APPLY and OUTER APPLY

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

T SQL Script - Splitting a delimited column value to multiple columns

Create Table Product(Col1 varchar(10),Col2 Varchar(1000))

iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'AR-5381 BA-8327 BB-7421 BB-8107 BB-9108'
iNSERT INTO pRODUCT(Col1,Col2) sELECT 2,'BC-M005 BC-R205 BE-2349 BE-2908 BK-M18B-40'

   
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM
(SELECT Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N' ', valueTable.Col2 + N' ', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N' ' + valueTable.Col2, n, 1) = N' ') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5])
) AS Table2

Drop table Product

Split one long comma string into multi column new table

CREATE TABLE Table1
([Field1] varchar(38))
;

INSERT INTO Table1
([Field1])
VALUES
('Apples,oranges, pears, berries, melons')
;

WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Field1 AS Field,
    CONVERT(XML,'<Fields><field>'
    + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM Table1
)

 SELECT Field,    
 xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1,  
 xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2,
 xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3,  
 xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4,
 xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 FROM Split_Fields

SQL How to Split One Column WIth 2 Different Delimiters into Multiple Variable Columns

create table #MessyDelim
(DelimList varchar(255));

insert into #MessyDelim
Values ('30;120;100')
, ('50;60')
, ('75/10')
, ('115/50/20/10/5')
, ('80;65;40;23;12;10')
, ('100')
;WITH
MessyDelim AS --As Nenad Zivkovic suggested, standardize on one delimiter for simplicity
(   SELECT DelimList,REPLACE(DelimList,'/',';') AS String
    FROM #MessyDelim),
Split AS --Recursive CTE to produce strings each with one less delimited value attached
(   SELECT DelimList, 1 AS Sort, String + ';' AS String
    FROM MessyDelim
    UNION ALL
    SELECT DelimList, Sort+1, RIGHT(String,LEN(String)-CHARINDEX(';',String))
    FROM Split
    WHERE CHARINDEX(';',String) > 0 ),
Cleanup AS --Reduce strings to single delimited value each
(   SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
    FROM Split
    WHERE String <> '' )  
SELECT DelimList, --Pivot out into columns
    [1] AS DL1,
    [2] AS DL2,
    [3] AS DL3,
    [4] AS DL4,
    [5] AS DL5,
    [6] AS DL6
FROM Cleanup
PIVOT(MAX(String) FOR Sort IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY DelimList
OPTION (MAXRECURSION 10) --Just for safety sake

SQL SERVER – Remove Duplicate Entry from Comma Delimited String – UDF

CREATE FUNCTION dbo.DistinctList(@List VARCHAR(MAX),@Delim CHAR)RETURNS
VARCHAR(MAX)AS
BEGIN
DECLARE @ParsedList TABLE(Item VARCHAR(MAX)
)DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)SET @list = LTRIM(RTRIM(@list)) + @DelimSET @pos = CHARINDEX(@delim, @list, 1)WHILE @pos > 0BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))IF @list1 <> ''INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))SET @list = SUBSTRING(@list, @pos+1, LEN(@list))SET @pos = CHARINDEX(@delim, @list, 1)END
SELECT @rlist = COALESCE(@rlist+',','') + itemFROM (SELECT DISTINCT Item FROM @ParsedList) tRETURN @rlistENDGOSELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',')DistinctList
GO