Hello,
I have two problems to resolve with .NET 1.1:
1. I search the good way to read hierarchical data with a SqlDataReader.
I don't know if it's possible. Must I use inevitably an
OleDbDataReader with SHAPE ... APPEND ...
2. How read hierarchical data on the same table with SQL server 2000 and
a SqlDataReader ?
Thanks for your help,
Christopher.Hi
You take a look at very good examples about the issue written by Itzik Ben-
Gan. However it does not cover .NET SqlDataReader object to read the data
but I'm sure you will get an idea
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
-- Scalar functions --
--
-- Getting Ancestor using Recursion
CREATE FUNCTION dbo.ufn_GetAncestor
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
IF @.lvl = 0
RETURN @.empid
RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @.empid),
@.lvl -1)
END
GO
-- Getting Ancestor using a Loop
CREATE FUNCTION dbo.ufn_GetAncestor2
(
@.empid AS int,
@.lvl AS int = 1 -- levels above employee
)
RETURNS int
AS
BEGIN
IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
RETURN NULL
DECLARE @.mgrid AS int
SET @.mgrid = @.empid
WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
FROM Employees WHERE empid = @.mgrid
RETURN @.mgrid
END
GO
-- test the dbo.ufn_GetAncestor function
SELECT dbo.ufn_GetAncestor(11, 2)
SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
SELECT E.empname AS employee, A.empname AS ancestor
FROM Employees AS E LEFT OUTER JOIN Employees AS A
ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
GO
-- Calculating an Aggregate of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary
FROM Employees WHERE empid = @.mgrid) +
CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees
WHERE mgrid = @.mgrid)
ELSE 0
END
END
GO
SELECT dbo.ufn_GetSubtreeSalary(3)
GO
-- Calculating the Depth of a Subtree
CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@.mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees
WHERE mgrid = @.mgrid)
WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
ELSE NULL
END
END
GO
SELECT dbo.ufn_GetSubtreeDepth(1)
GO
"Christopher" <Dev@.effect.fr> wrote in message
news:eAngpiOIGHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have two problems to resolve with .NET 1.1:
> 1. I search the good way to read hierarchical data with a
> SqlDataReader.
> I don't know if it's possible. Must I use inevitably an
> OleDbDataReader with SHAPE ... APPEND ...
> 2. How read hierarchical data on the same table with SQL server 2000
> and
> a SqlDataReader ?
> Thanks for your help,
> Christopher.
>
>|||Thanks for these informations.
"Uri Dimant" <urid@.iscar.co.il> a crit dans le message de news:
%23ppmLpOIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi
> You take a look at very good examples about the issue written by Itzik
> Ben- Gan. However it does not cover .NET SqlDataReader object to read
> the data but I'm sure you will get an idea
> IF object_id('dbo.Employees') IS NOT NULL
> DROP TABLE Employees
> GO
> IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
> DROP FUNCTION dbo.ufn_GetSubtree
> GO
> CREATE TABLE Employees
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
> CONSTRAINT FK_Employees_mgrid_empid
> FOREIGN KEY(mgrid)
> REFERENCES Employees(empid)
> )
> CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
> INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
> INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
> INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
> INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
> INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
> INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
> INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
> INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
> INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
> INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
> INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
> INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
> INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
> INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
> GO
> CREATE FUNCTION dbo.ufn_GetSubtree
> (
> @.mgrid AS int
> )
> RETURNS @.tree table
> (
> empid int NOT NULL,
> mgrid int NULL,
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT empid, mgrid, empname, salary,
> @.lvl, '.' + CAST(empid AS varchar(10)) + '.'
> FROM Employees
> WHERE empid = @.mgrid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.empid, E.mgrid, E.empname, E.salary,
> @.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
> FROM Employees AS E JOIN @.tree AS T
> ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> GO
> SELECT empid, mgrid, empname, salary
> FROM ufn_GetSubtree(3)
> GO
> /*
> empid mgrid empname salary
> 2 1 Andrew 5000.0000
> 5 2 Steven 2500.0000
> 6 2 Michael 2500.0000
> */
>
> -- Scalar functions --
> --
> -- Getting Ancestor using Recursion
> CREATE FUNCTION dbo.ufn_GetAncestor
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> IF @.lvl = 0
> RETURN @.empid
> RETURN dbo.ufn_GetAncestor(
> (SELECT mgrid FROM Employees WHERE empid = @.empid),
> @.lvl -1)
> END
> GO
> -- Getting Ancestor using a Loop
> CREATE FUNCTION dbo.ufn_GetAncestor2
> (
> @.empid AS int,
> @.lvl AS int = 1 -- levels above employee
> )
> RETURNS int
> AS
> BEGIN
> IF @.lvl IS NULL or @.empid IS NULL or @.lvl < 0
> RETURN NULL
> DECLARE @.mgrid AS int
> SET @.mgrid = @.empid
> WHILE @.lvl > 0 AND @.mgrid IS NOT NULL
> SELECT @.mgrid = mgrid, @.lvl = @.lvl - 1
> FROM Employees WHERE empid = @.mgrid
> RETURN @.mgrid
> END
> GO
> -- test the dbo.ufn_GetAncestor function
> SELECT dbo.ufn_GetAncestor(11, 2)
> SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)
> SELECT E.empname AS employee, A.empname AS ancestor
> FROM Employees AS E LEFT OUTER JOIN Employees AS A
> ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)
> GO
> -- Calculating an Aggregate of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeSalary
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN (SELECT Salary
> FROM Employees WHERE empid = @.mgrid) +
> CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> (SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> ELSE 0
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeSalary(3)
> GO
> -- Calculating the Depth of a Subtree
> CREATE FUNCTION dbo.ufn_GetSubtreeDepth
> (
> @.mgrid AS int
> )
> RETURNS int
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @.mgrid) THEN
> 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
> FROM Employees
> WHERE mgrid = @.mgrid)
> WHEN EXISTS(SELECT * FROM Employees WHERE empid = @.mgrid) THEN 1
> ELSE NULL
> END
> END
> GO
> SELECT dbo.ufn_GetSubtreeDepth(1)
> GO
>
>
>
>
>
>
> "Christopher" <Dev@.effect.fr> wrote in message
> news:eAngpiOIGHA.2680@.TK2MSFTNGP09.phx.gbl...
>
