How to read a SQL Function Value By SQL Data Reader in C#

Question

Hi I have a table tblRegistration which have three fields

Member_ID(int)  Left_Placement(Int) Right_Placement(int)

and one function

CREATE FUNCTION [dbo].[fnGetChildCount](
@ParentId INT
)
RETURNS INT
BEGIN
 DECLARE @ChildCount INT
 DECLARE @Left INT
DECLARE @Right INT


SET @ChildCount = 0
 IF NOT @ParentId IS NULL AND @ParentId <> 0
 BEGIN
 SET @ChildCount = 1
 SELECT @Left = Left_Placement, @Right = Right_Placement FROM tblRegistration WHERE Member_ID = @ParentId
SELECT @ChildCount = @ChildCount + [dbo].[fnGetChildCount](@Left)
 SELECT @ChildCount = @ChildCount + [dbo].[fnGetChildCount](@Right)
 END
 RETURN @ChildCount
END;

Now I use This Code

    SqlCommand cmd = new SqlCommand("SELECT Member_ID, dbo.fnGetChildCount(Left_Placement) AS [Left Count], dbo.fnGetChildCount(Right_Placement) AS [Right Count] FROM tblRegistration", con);
    dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            string member = dr["dbo.fnGetChildCount(Left_Placement)"].ToString();

        }
    }

To read Left Count And Right Count Which Is Not table column then

HOW CAN I READ THE LEFT COUNT(dbo.fnGetChildCount(Left_Placement) AS [Left Count]) AND RIGHT COUNT(dbo.fnGetChildCount(Right_Placement) AS [Right Count])

thank you in Advance

FYI: This kind of stuff usually does better in a stored procedure. — user153923
– user153923, Commented Mar 25, 2013 at 19:30

jrummell · Accepted Answer · 2013-03-25 19:49:37Z

0

Use the column aliases to retrieve the computed column values.

string leftCount = dr["Left Count"].ToString();
string rightCount = dr["Right Count"].ToString();

edited Mar 25, 2013 at 19:49

answered Mar 25, 2013 at 19:28

jrummell

43.2k18 gold badges116 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rs. Over a year ago

you don't have to wrap column names with [] when reading using datareader

Aman Over a year ago

Ya Mr. Rummell It is working string leftCount = dr["Left Count"].ToString(); string rightCount = dr["Right Count"].ToString();

Collectives™ on Stack Overflow

How to read a SQL Function Value By SQL Data Reader in C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related