Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to call SQL Server User Defined Function in ASP.NET

Author: Alanna Kremer
Download Source Code : 1385_UserDefinedFunction.zip

User Defined Functions in SQL Server accept parameters and return single value or table. These can be called in ASP.NET code in a SQL Query.

 

There are three types of User Defined functions. In T-SQL or in SQL Query, Scalar-valued Functions are called slightly differently from In-Line and Table-value functions because Scalar-valued functions return a single value. In ASP.NET, we can write SQL query to call a User Defined function. Parameters can be passed and added to same way as we pass and add in stored procedure. Only difference between both is, User Defined functions don’t use output parameters but stored procedure can use output parameters. Moreover, we can also use In-Line and Table-valued functions in join queries. 

 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <table>
        <tr>
            <td>
                CategoryID:
            </td>
            <td>
                <asp:TextBox ID="txtCatID" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Unit Price:
            </td>
        <td>
                <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <br />
    <asp:Button ID="btnGetProducts" runat="server" Text="Get Products using UDF"
        onclick="btnGetProducts_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server"></asp:Label>
    <br />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
     
  6. Write below User Defined function in SQL Server NORTHWIND database

    CREATE FUNCTION dbo.GetProductsByCategoryID
           (
           @CategoryID int,
           @UnitPrice money
           )
    RETURNS TABLE
    AS
           RETURN
           SELECT *
           FROM Products
           WHERE CategoryID = @CategoryID
           AND UnitPrice <= @UnitPrice
     
    This User Defined Function takes two parameters and returns a Table. This is an In-Line function because In-Line functions allows a single T-SQL statement and returns Table.

     
  7. Open code behind file and include following namespace.

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
     
  8. Write code below in button click event

    C#

    protected void btnGetProducts_Click(object sender, EventArgs e)
    {
        int catID = Convert.ToInt32(txtCatID.Text);
        string unitPrice = txtPrice.Text;
     
        string constring = "Data Source=Local;Initial Catalog=NORTHWND;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        string cmdstring = "SELECT * FROM dbo.GetProductsByCategoryID(@CategoryID, @UnitPrice)";
        SqlCommand cmd = new SqlCommand(cmdstring, conn);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = catID;
        cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice;
     
        DataTable dt = new DataTable();
     
        try
        {
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
     
    VB.NET
     
    Protected Sub btnGetProducts_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGetProducts.Click
        Dim catID As Integer = Convert.ToInt32(txtCatID.Text)
        Dim unitPrice As String = txtPrice.Text
     
        Dim constring As String = "Data Source=Local;Initial Catalog=NORTHWND;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmdstring As String = "SELECT * FROM dbo.GetProductsByCategoryID(@CategoryID, @UnitPrice)"
        Dim cmd As New SqlCommand(cmdstring, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = catID
        cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice
     
        Dim dt As New DataTable()
     
        Try
            conn.Open()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            conn.Close()
        End Try
     
        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub
     
    Get Values form TextBox controls to set values for parameters. Connect to your server by passing connection string to SqlConnection class constructor. Write a SQL query to call User Defined Function. You can see I have written User Defined Function name with parameters in FROM clause because it an In-Line function and it returns Table. If we have to call a Scalar-valued function then we can write its name with parameters straight after SELECT keyword. Set Command Type as Text and add parameters for User Defined Functions. Values for User Defined Function parameters can be provided in SQL query as well. Get data in a DataTable object and display it in a GridView.
     
  9. Press F5, provide values and click on button to call User Defined Function. 
 
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to insert multiple selected items of ListBox into SQL Server database in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net