Home

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 Practical Learning: Introducing Functions

  1. Start Microsoft Visual C# and create a Windows Application named TriStateUtilityCompany1
  2. Design the form as follows:
     
    Functions
    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  
  3. 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.");
    	    }
            }
        }
    }
  4. Return to the form and double-click the Close button
  5. Implement the event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
     
  6. Execute the application
  7. Click the Database button
  8. 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 Practical Learning: Creating a Function

  1. 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.");
        }
    }
  2. Execute the application
  3. To actually create the function, click the Database button
     
    Creating a Function
  4. 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 Practical Learning: Calling a Function

  1. On the form, double-click the Evaluate button
  2. 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();
        }
    }
  3. Execute the application and click the Evaluate button:
     
    Calling a Function
  4. Close the form and return to your programming environment
 

Published on Monday 27 December 2007

 

Home Copyright © 2007 FunctionX, Inc. Next