Returning string from scalar function via C#

Question

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

 

<pre>Alter function fngetFullName

(

@FirstName varchar(50),

@LastName varchar(50)

)

returns varchar(101)

as

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.