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

    How do i use Sql function in asp.net

    How to i pass parameters to sql functions from code behind in asp.net... whether its possible?
  • #654705
    suppose i have write a function in SQL

    CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
    RETURNS float AS BEGIN RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
    END


    now i will call that function from code behind using DataAdapter

    DataSet ds = new DataSet();
    SqlCommand com = new SqlCommand();
    SqlDataAdapter sqlda = new SqlDataAdapter(com);
    sqlda.SelectCommand.CommandText = "select *, spherical_distance( 12.925432, 57.2958, 77.5940171) as distance from business"
    sqlda.SelectCommand.Connection = con;
    sqlda.Fill(ds);
    con.Close();


    hope it helps !

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #654717
    Hi,
    Yes you can call function as you execute your general SQL statment. There is no difference to the code.

    Please check below sample code,


    Dim strCon As New SqlstrConection(configurationManager.strConectionStrings("SQLstrConectionString").strConectionString)
    Dim objCMD As New SqlCommand
    Dim SQLdr As SqlDataReader = Nothing
    objCMD.strConection = strCon
    objCMD.CommandText = "select functionTest(@param1, @param2) as Result"
    objCMD.CommandType = CommandType.text
    objCMD.Parameters.AddWithValue("param1", "Val1")
    objCMD.Parameters.AddWithValue("param2", "Val2")
    strCon.Open()
    SQLdr = objCMD.ExecuteReader
    //Other code goes here to read the data reader.


    Regards,
    Asheej T K

  • #654729
    Hello,

    You can call the function like as below C# code.

    DataSet ds = new DataSet();
    SqlCommand com = new SqlCommand();
    SqlDataAdapter sqlda = new SqlDataAdapter(com);
    objCMD.CommandText = "select TestFunct(@param1, @param2) as Result"
    com.CommandType = CommandType.text
    com.Parameters.AddWithValue("param1", "Val1")
    com.Parameters.AddWithValue("param2", "Val2")
    con.Open()
    dr = objCMD.ExecuteReader
    //code to ur reade
    con.Close();




    Regards,
    Naveen

  • #654763
    Hai Puneetha,

    yes, you can pass the parameters directly from the code behind if they are dynamic.
    Below is the code snippet Oracle data provider which you can modify as per your requirements:

    dbOracleCommand = OracleUtil.GetCommand("HRIS.PKG_SCP_CTS.SP_INSERT_CTSCONVERSION");
    dbOracleCommand.CommandType = CommandType.StoredProcedure;
    OracleParameter code = new OracleParameter("p_benefitCode", OracleDbType.Varchar2, ParameterDirection.Input);
    code.Value = objCTSConversionBO.Code;
    OracleParameter description = new OracleParameter("p_description", OracleDbType.Varchar2, ParameterDirection.Input);
    description.Value = objCTSConversionBO.Description;
    OracleParameter userid = new OracleParameter("p_userid", OracleDbType.Varchar2, ParameterDirection.Input);
    description.Value = objCTSConversionBO.CreateID;

    dbOracleCommand.Parameters.Add(code);
    dbOracleCommand.Parameters.Add(description);
    dbOracleCommand.Parameters.Add(userid);

    orclCon.Open();
    dbOracleCommand.ExecuteNonQuery();

    Hope it will help.

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

  • #655595
    Tahnk u all.....


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