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