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

Question

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 ?

ahmed salah · Answer

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 Mohapatra · Answer

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 salah · Answer

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 Mohapatra · Answer

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#?