locked
How to Call SQL function from ASP.net application? RRS feed

  • Question

  • User1269502655 posted

    I have SQL functions and I want to be able to call it from my asp.net application.

    How can I call it from my asp.net application?

    Also I am using microsoft enterprise application block to call dataabse. 

    thank you

    maxmax

    Wednesday, October 13, 2010 10:27 AM

Answers

  • User-908318413 posted


    Hi,

             Use like the following...

    Create FUNCTION Test
       (@test1 int )
    RETURNS int
    AS
    BEGIN
       RETURN ( @test1 * @test1 )
    END

     

    SELECT [dbo].[Test](5)

     

     public DataTable DbRecords()
        {
          string sqlScript="SELECT [dbo].[Test](5)";
          SqlConnection sqlConn = new SqlConnection(cnnStr);     
          SqlCommand sqlCmd = new SqlCommand(sqlScript, sqlConn);
          SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);         
          DataSet ds = new DataSet();
               
                  try
                  {
                    sqlDa.Fill(ds, "TempTbl");
                    return ds.Tables["TempTbl"];
                  }
                  catch (SqlException err)
                  {
                    throw;
                  }           
        }


    Thanks
    Harisaran

    If this is your solution, Please mark as Answer.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 14, 2010 7:50 AM

All replies

  • User191633014 posted

    select * from dbo.Myfunction() -- if is table valued function

    or

    select  dbo.Myfunction() -- is is scalar valued function

    Wednesday, October 13, 2010 11:09 AM
  • User-908318413 posted


    Hi,

             Use like the following...

    Create FUNCTION Test
       (@test1 int )
    RETURNS int
    AS
    BEGIN
       RETURN ( @test1 * @test1 )
    END

     

    SELECT [dbo].[Test](5)

     

     public DataTable DbRecords()
        {
          string sqlScript="SELECT [dbo].[Test](5)";
          SqlConnection sqlConn = new SqlConnection(cnnStr);     
          SqlCommand sqlCmd = new SqlCommand(sqlScript, sqlConn);
          SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);         
          DataSet ds = new DataSet();
               
                  try
                  {
                    sqlDa.Fill(ds, "TempTbl");
                    return ds.Tables["TempTbl"];
                  }
                  catch (SqlException err)
                  {
                    throw;
                  }           
        }


    Thanks
    Harisaran

    If this is your solution, Please mark as Answer.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 14, 2010 7:50 AM