ADO.NET and Microsoft SQL Server: Introduction to Functions

	Introduction to Functions

Introduction

You are probably not familiar with the concept of functions because there are not functions in C#; but many languages support them, including C/C++, Pascal Visual Basic, etc. A function is like a method in C#, except that a function does not belong to a class. As seen in C#, a function is a relatively small task that should be performed aside but can be accessed any time to give a result. In Transact-SQL, a function is considered an object. Based on this, you must create a function and execute it before using it. The function then becomes part of a database and it can be accessed.

Practical Learning: Introducing Functions

Start Microsoft Visual C# and create a Windows Application named TriStateUtilityCompany1

Design the form as follows:

Control	Text	Name	Other Properties
Label	Customer Name
TextBox		txtCustomerName
Label	Counter: ___________
Label	Last Month:
TextBox		txtLastMonth	TextAlign: Right
Label	This Month:
TextBox		txtThisMonth	TextAlign: Right
Label	Consumption:
TextBox		txtConsumption	TextAlign: Right
Button	Evaluate	btnEvaluate
Label	Invoice: ___________
Label	Amount Due:
TextBox		txtAmountDue	TextAlign: Right
Label	Database: _________
Button	Database	btnDatabase
Button	Close	btnClose

Double-click the Create button and implement its event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TriStateUtilityCompany1
{
    public partial class Form1 : Form
    {
	public Form1()
	{
	    InitializeComponent();
	}

	private void btnDatabase_Click(object sender, EventArgs e)
	{
	    string strConnection =
	        "Data Source=(local);Integrated Security=yes";

	    using (SqlConnection connection = new SqlConnection(strConnection))
	    {
	        SqlCommand command =
		    new SqlCommand("CREATE DATABASE UtilityCompany1;",
				   connection);

		connection.Open();
	    	command.ExecuteNonQuery();

	        MessageBox.Show(
		    "A database named \"UtilityCompany1\" has been created.");
	    }
        }
    }
}

Return to the form and double-click the Close button

Implement the event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Execute the application
Click the Database button
Close the form and return to your programming environment

Function Creation Fundamentals

There are various ways you can create a function:

In the Object Explorer, you can expand the desired database, expand the Programmatically node, and expand the Functions node. Right-click Scalar-Valued Function and click New Scalar-Valued Function... Sample code would be generated for you. You can then modify to customize it
Open an empty query window. Display the Templates Explorer and expand the Function node. Drag Create Scalar-Valued Function and drop it in the query window
You can open a new empty query window and start typing your code in it
Programmatically, you can include of a function creation in the command

In Transact-SQL, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()

The Name of a Function

We mentioned already that, in SQL, a function was created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:

The name of a function will resemble an action. An example is calculate
A name will start with either an underscore or a letter. Examples are _n, act, or Perform
After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24 or act_52_t
A name will not include special characters such as !, @, #, $, %, ^, &, or *
We will avoid using spaces in a name
If the name is a combination of words, each word will start in uppercase. Examples are DoSomething, _CreateStudentsRecords, Get_Age, or _Calculate_Volume_Area

Returning a Value From a Function

For a function to be useful, it must produce a result. This is also said that the function returns a result or a value. When creating a function, you must specify the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)

After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END

Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END

Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
    RETURN Expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
	RETURN 'Doe, John'
END

Practical Learning: Creating a Function

On the form, double-click the Database button and change its code as follows:

private void btnDatabase_Click(object sender, EventArgs e)
{
    string strConnection =
		"Data Source=(local);" +
		"Database='UtilityCompany1';" +
		"Integrated Security=yes;";
    string CreateFunction = "CREATE FUNCTION EvaluateInvoice() " +
			    "RETURNS Decimal(8, 2) " +
			    "AS " +
			    "BEGIN " +
			    "    RETURN 8.50 " +
			    "END;";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
	    new SqlCommand(CreateFunction, connection);

	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show(
	    "A function named \"EvaluateInvoice\" has been created.");
    }
}

Execute the application
To actually create the function, click the Database button
Close the form and return to your programming environment

Function Calling

After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()

Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:

PRINT Exercise.dbo.GetFullName();

As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.

Practical Learning: Calling a Function

On the form, double-click the Evaluate button

Implement the event as follows:

private void btnEvaluate_Click(object sender, EventArgs e)
{
    string strConnection =
	"Data Source=(local);" +
	"Database='UtilityCompany1';" +
	"Integrated Security=yes;";
    string ExecuteFunction = "SELECT dbo.EvaluateInvoice();";

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
	SqlCommand command =
	    new SqlCommand(ExecuteFunction, connection);

	connection.Open();
    	SqlDataReader rdr = command.ExecuteReader();

    	while (rdr.Read())
    	{
	    txtAmountDue.Text = rdr[0].ToString();
        }

        rdr.Close();
    }
}

Execute the application and click the Evaluate button:
Close the form and return to your programming environment

Published on Monday 27 December 2007


Home	Copyright © 2007 FunctionX, Inc.	Next