How To Call A SQL Function In C#

Functions in SQL Server are code modules that can implement business logic and execute code.

Introduction

We can directly execute a SQL function from the C# code in our application. In this blog, let's learn how to create and execute a function in SQL Server and execute it from C# code.

Step 1. Create a function in SQL Server

Here is an example of a function in our SQL Server database. 

CREATE function [dbo].[function_xyz](@username varchar(200))  
returns table  
as  
return (select [password] from [User] where username =@username)  

Step 2. Execute SQL Function in C# code

We can execute a function in C# using a SqlCommand object and pass a SQL-defined function in a SELECT SQL query.  

SqlConnection conn=new SqlConnection(@"SQLCONNECTION STRING");  
SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.function_xyz(@username)", conn);  
// cmd.CommandType=CommandType.StoredProcedure;  
cmd.Parameters.AddWithValue("@username","username");  
SqlDataAdapter da = new SqlDataAdapter(cmd);  
DataTable dt = new DataTable();  
da.Fill(dt);  
string str = dt.Rows[0][0].ToString();  
Response.Write(str.ToString()); 

Summary

This simple tip taught us how to execute a SQL function from a C# application.

Here is a detailed tutorial on Functions in SQL Server.