How to make function by c# to call stored proc from sql

ahmed salah

9y865Learn iOS Programming

I work in visual studio 2015 c# and i need to call stored procedure from sql 2012

and make function by c# to call stored procedure and retrieve one value string

in case name = @name

ALTER PROCEDURE [dbo].[ATC_Code_ByGeneric_H]
@Lvl_Name nvarchar(500)
AS
SELECT Tbl_Lvl1.Code + Tbl_Lvl2.Code + Tbl_Lvl3.Code + Tbl_Lvl4.Code + Tbl_Lvl5.Code AS ATC_Code
FROM Tbl_Lvl1
INNER JOIN
Tbl_Lvl2
ON Tbl_Lvl1.S_ID = Tbl_Lvl2.UpLvl_ID
INNER JOIN
Tbl_Lvl3
ON Tbl_Lvl2.S_ID = Tbl_Lvl3.UpLvl_ID
INNER JOIN
Tbl_Lvl4
ON Tbl_Lvl3.S_ID = Tbl_Lvl4.UpLvl_ID
INNER JOIN
Tbl_Lvl5
ON Tbl_Lvl4.S_ID = Tbl_Lvl5.UpLvl_ID
WHERE (Tbl_Lvl5.Lvl_Name = @Lvl_Name) and Tbl_Lvl5.human=1
RETURN

Actually i need to retrieve ATC_Code in case of Lv1_Name = @Lv1_Name

How to create function return string by c# to call stored procedure ?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed salah9y ago
I have three questions
1- why not close connection meaning
conn.close()
2- why use return value in sql stored procedure

I mean without using return value it will work

3-in this case of return value using as you do

How many parameters i will use
meaning
public string getATCcode(string name)
{
//actually what i write here coding c#
}
i will use parameter name only or another parameter
4- why use var datatype and not use string
if possible help me answer these questions
Manas Mohapatra9y ago
Let's try below code:
using (SqlConnection conn = new SqlConnection(getConnectionString()))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "ATC_Code_ByGeneric_H";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Lvl_Name", "Name");

        var returnParameter = cmd.Parameters.Add("@Return_Val", SqlDbType.String);
        returnParameter.Direction = ParameterDirection.ReturnValue;

        conn.Open();
        cmd.ExecuteNonQuery();
        var result = returnParameter.Value;
    }
}

//In SP

ALTER PROCEDURE [dbo].[ATC_Code_ByGeneric_H]
    @Lvl_Name nvarchar(500)
    @Return_Val nvarchar(500)
AS
    SELECT     @Return_Val = Tbl_Lvl1.Code + Tbl_Lvl2.Code + Tbl_Lvl3.Code + Tbl_Lvl4.Code + Tbl_Lvl5.Code
    FROM         Tbl_Lvl1
                 INNER JOIN
                 Tbl_Lvl2
                 ON Tbl_Lvl1.S_ID = Tbl_Lvl2.UpLvl_ID
                 INNER JOIN
                 Tbl_Lvl3
                 ON Tbl_Lvl2.S_ID = Tbl_Lvl3.UpLvl_ID
                 INNER JOIN
                 Tbl_Lvl4
                 ON Tbl_Lvl3.S_ID = Tbl_Lvl4.UpLvl_ID
                 INNER JOIN
                 Tbl_Lvl5
                 ON Tbl_Lvl4.S_ID = Tbl_Lvl5.UpLvl_ID
    WHERE     (Tbl_Lvl5.Lvl_Name = @Lvl_Name) and Tbl_Lvl5.human=1
    RETURN @Return_Val;
ahmed salah9y ago
Thank you for reply
i need create c# function to call stored procedure
and i dont need any changes in sql stored procedure
i need to write c# function and return string to call stored procedure by c# ?
as following
public string getATCcode(string name)
{
//actually what i write here coding c#
}
Manas Mohapatra9y ago
Couple of doubts:

As per your code, you are fetching ATC_Code(combination of Lv1, Lv2, Lv3, Lv4, Lv5) based on Lv5Name = param value. What changes do you need here.

Secondly are looking code for SQL or C#?