Returning string from scalar function via C#

Hi Guys,

I have a table called Employees containing Two Columns called FirstName and LastName.

I have made a function that takes FirstName and LastName and returns FullName.

It works good when executed from Backend.

Now I was making a console application in C# that does the same but its not working.

Please help me to fix this issue

Any article or link would be appreciated

I am adding the details below.

----------------Table Script to Populate Data ------------

CREATE TABLE Employee

(

EmpID int PRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar(50) NULL,

Salary int NULL,

Address varchar(100) NULL,

)

--Insert Data

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');

---------------Function --------------

Alter function fngetFullName

(

@FirstName varchar(50),

@LastName varchar(50)

)

returns varchar(101)

begin

DECLARE @Result VARCHAR(50);

SELECT @Result= (Select @FirstName + ' '+@LastName)

Return @Result

end

----------------Calling the same from backend---------

Select dbo.fngetFullName(FirstName,LastName) as Name,Salary from Employee

-------------------C# Code -----------------------

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApp4

{

class Program

{

private static string FullName;

public object ConfigurationManager { get; private set; }

static void Main(string[] args)

{

string FullName = GetLookupCodeFromShortCode("FirstName", "LastName");

Console.WriteLine("Full Name is", FullName);

Console.ReadKey();

}

public static string GetLookupCodeFromShortCode(string Fname, string LName)

{

using (var conn = new SqlConnection(@"Data Source=\VAIBHAVLOCAL;Initial Catalog=Dummy ; Integrated Security=SSPI"))

{

using (var cmd = new SqlCommand("dbo.fngetFullName(@FirstName,@LastName)", conn))

{

cmd.CommandTimeout = 30;

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar);

cmd.Parameters.Add("@LastName", SqlDbType.VarChar);

cmd.Parameters["@FirstName"].Value = Fname;

cmd.Parameters["@LastName"].Value = LName;

conn.Open();

string getValue = cmd.ExecuteScalar().ToString();

if (getValue != null)

{

FullName = getValue.ToString();

}

conn.Close();

return FullName;

}

// So this the code which i have made. Please have a look at it and let me know if you got nay solution for this.

4 Replies

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

Jignesh Kumar7y ago
Accepted answer
Please find below link where it explains clearly,

https://www.c-sharpcorner.com/blogs/calluse-a-sql-function-in-c-sharp1
Amit Gupta7y ago
@Jenith is right, in addition you have to make a check the value is null before converting to string. cmd.ExecuteScalar().ToString();, you can alternatively use cmd.ExecuteScalar()+"";
Jenith Thakkar7y ago
You need to change sql command line like this

(var cmd = new SqlCommand("SELECT dbo.fngetFullName(@FirstName,@LastName)", conn))

Because you forgot write SELECT
Salman Beg7y ago
Hi. The code looks good to me. Are you facing any kind of error? What is your expected output can you please tell me. Thanks.