You must Sign In to post a response.
  • Category: ASP.NET

    Is it possible call sql function in ASP.Net C#?


    Are you looking for information on calling stored procedure? Want to know is it possible call sql function in ASP.Net C#? Read this thread to learn more about calling sql user defined function in C#.



    Hi,
    Is it possible call Sql userdefined function inside C# like stored procedure.
    ie if I call stored procedure and store it into DataTable
    like wise is it possible to call sql user defined function in C#
  • #720463
    Hi,

    first of all creat sample function to call into your requirement.

    user defind function:


    CREATE FUNCTION DBO.sumofvalues(@a int,@b int)
    RETURNS INT
    AS
    BEGIN
    RETURN(@a+@b)
    END


    After creating SQL UserDefined function call that function in your Application using the below code


    string constring = //connection string;
    SqlConnection conn = new SqlConnection(constring);
    string cmdstring = "SELECT * FROM DBO.sumofvalues(@a, @b)";
    SqlCommand cmd = new SqlCommand(cmdstring, conn);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@a",value of a);
    cmd.Parameters.AddWithValue("@b",value of b);
    DataSet ds=new DataSet();
    conn.open();
    SqlDataAdapter da=new SqlDataAdapter(cmd);
    da.Fill(ds);
    conn.close();

    GV.DataSource=ds;
    GV.DataBind();


    Use these lines of code in your application..

    Mark this post if it helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #720477
    You can call any function inside your stored procedure so there will not be any issue in calling the same.
    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #720513
    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Net.Mail;
    using System.Net;
    using System.Data.SqlClient;


    public partial class _Default : System.Web.UI.Page
    {

    protected void Page_Load(object sender, EventArgs e)
    {
    try
    {
    SqlConnection con = new SqlConnection("server=pc;uid=sa;password=p@s;database=databasename");

    SqlCommand sqlcmd = new SqlCommand("select dbo.[AddionofTwoNumbers](@Number1, @Number2,@Number3)", con);
    sqlcmd.CommandType = CommandType.Text;
    sqlcmd.Parameters.AddWithValue("@Number1", 10);
    sqlcmd.Parameters.AddWithValue("@Number2", 20);
    sqlcmd.Parameters.AddWithValue("@Number3", 30);
    DataSet dsResutl = new DataSet();
    string stroutput = string.Empty;
    con.Open();
    stroutput =Convert.ToString((Int32)sqlcmd.ExecuteScalar());
    Response.Write(stroutput);
    con.Close();


    }
    catch (Exception ex)
    {
    Response.Write("<script language='javascript'>alert('"+ex.Message.ToString()+"');</script>");
    }
    }
    }





    CREATE FUNCTION [dbo].[AddionofTwoNumbers]

    (

    @Number1 int,
    @Number2 int,
    @Number3 int

    )

    RETURNS int

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Output int

    SELECT @Output = @Number1 + @Number2+ @Number3;

    -- Return the result of the function

    RETURN @Output

    END



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #720540
    Hai Bibillis,
    Yes we can call the system defined as well as user defined functions in the .net code. The calling procedure will be similar to other inline queries. We can pass the parameters similar to the queries and stored procedures etc.
    e.g.

    SELECT * FROM <FUNCTION_NAME>

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #720552
    Hi Bibillis ,Yes you can very well call sql function in Asp.net. It is same like calling Stored Procedure.There are many ways of doing here are the few methods for you
    1. You can either use a stored procedure and in that stored proc call your sql functionfunction.
    2. Directly you can invoke the Sql function

    public void callFunction()
    {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    SqlCommand command = new SqlCommand("SELECT * FROM database.functionname", connection);
    command.Connection.Open();
    command.ExecuteScalar();
    }
    }

    Hope this helps


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.