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