Search content within the blog

Monday, January 30, 2012

Named and Optional Parameters in C#

Visual C# 2010 introduces named and optional arguments.

Named arguments enable you to specify an argument for a particular parameter by associating the argument with the parameter's name rather than with the parameter's position in the parameter list.  

Optional arguments enable you to omit arguments for some parameters. Both techniques can be used with methods, indexers, constructors, and delegates.

When you use named and optional arguments, the arguments are evaluated in the order in which they appear in the argument list, not the parameter list.

Named and optional parameters, when used together, enable you to supply arguments for only a few parameters from a list of optional parameters. This capability greatly facilitates calls to COM interfaces such as the Microsoft Office Automation APIs.

Named Arguments

Named arguments free you from the need to remember or to look up the order of parameters in the parameter lists of called methods. The parameter for each argument can be specified by parameter name. For example, a function that calculates body mass index (BMI) can be called in the standard way by sending arguments for weight and height by position, in the order defined by the function.
CalculateBMI(123, 64); 
 
If you do not remember the order of the parameters but you do know their names, you can send the arguments in either order, weight first or height first.

CalculateBMI(weight: 123, height: 64);
CalculateBMI(height: 64, weight: 123);
Named arguments also improve the readability of your code by identifying what each argument represents.

A named argument can follow positional arguments, as shown here.
CalculateBMI(123, height: 64);

However, a positional argument cannot follow a named argument. The following statement causes a compiler error.
//CalculateBMI(weight: 123, 64);

 Example
class NamedExample
{
    static void Main(string[] args)
    {
        // The method can be called in the normal way, by using positional arguments.
        Console.WriteLine(CalculateBMI(123, 64));

        // Named arguments can be supplied for the parameters in either order.
        Console.WriteLine(CalculateBMI(weight: 123, height: 64));
        Console.WriteLine(CalculateBMI(height: 64, weight: 123));

        // Positional arguments cannot follow named arguments.
        // The following statement causes a compiler error.
        //Console.WriteLine(CalculateBMI(weight: 123, 64));

        // Named arguments can follow positional arguments.
        Console.WriteLine(CalculateBMI(123, height: 64));
    }

    static int CalculateBMI(int weight, int height)
    {
        return (weight * 703) / (height * height);
    }
} 
 
Optional Arguments
 
The definition of a method, constructor, indexer, or delegate can specify that its parameters are required or that they are optional. Any call must provide arguments for all required parameters, but can omit arguments for optional parameters.

Each optional parameter has a default value as part of its definition. If no argument is sent for that parameter, the default value is used. A default value must be one of the following types of expressions:
  • a constant expression;
  • an expression of the form new ValType(), where ValType is a value type, such as an enum or a struct;
  • an expression of the form default(ValType), where ValType is a value type.
Optional parameters are defined at the end of the parameter list, after any required parameters. If the caller provides an argument for any one of a succession of optional parameters, it must provide arguments for all preceding optional parameters. Comma-separated gaps in the argument list are not supported. For example, in the following code, instance method ExampleMethod is defined with one required and two optional parameters.

  Example 

In the following example, the constructor for ExampleClass has one parameter, which is optional. Instance method ExampleMethod has one required parameter, required, and two optional parameters, optionalstr and optionalint. The code in Main shows the different ways in which the constructor and method can be invoked.


namespace OptionalNamespace
{
    class OptionalExample
    {
        static void Main(string[] args)
        {
            // Instance anExample does not send an argument for the constructor's
            // optional parameter.
            ExampleClass anExample = new ExampleClass();
            anExample.ExampleMethod(1, "One", 1);
            anExample.ExampleMethod(2, "Two");
            anExample.ExampleMethod(3);

            // Instance anotherExample sends an argument for the constructor's
            // optional parameter.
            ExampleClass anotherExample = new ExampleClass("Provided name");
            anotherExample.ExampleMethod(1, "One", 1);
            anotherExample.ExampleMethod(2, "Two");
            anotherExample.ExampleMethod(3);

            // The following statements produce compiler errors.

            // An argument must be supplied for the first parameter, and it
            // must be an integer.
            //anExample.ExampleMethod("One", 1);
            //anExample.ExampleMethod();

            // You cannot leave a gap in the provided arguments. 
            //anExample.ExampleMethod(3, ,4);
            //anExample.ExampleMethod(3, 4);

            // You can use a named parameter to make the previous 
            // statement work.
            anExample.ExampleMethod(3, optionalint: 4);
        }
    }

    class ExampleClass
    {
        private string _name;

        // Because the parameter for the constructor, name, has a default
        // value assigned to it, it is optional.
        public ExampleClass(string name = "Default name")
        {
            _name = name;
        }

        // The first parameter, required, has no default value assigned
        // to it. Therefore, it is not optional. Both optionalstr and 
        // optionalint have default values assigned to them. They are optional.
        public void ExampleMethod(int required, string optionalstr = "default string",
            int optionalint = 10)
        {
            Console.WriteLine("{0}: {1}, {2}, and {3}.", _name, required, optionalstr,
                optionalint);
        }
    }

    // The output from this example is the following:
    // Default name: 1, One, and 1.
    // Default name: 2, Two, and 10.
    // Default name: 3, default string, and 10.
    // Provided name: 1, One, and 1.
    // Provided name: 2, Two, and 10.
    // Provided name: 3, default string, and 10.
    // Default name: 3, default string, and 4.

}

 
 

Wednesday, January 4, 2012

Dynamic SQL statements

A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements. It can be useful to construct these statements dynamically when you need to decide at run time what fields to bring back from SELECT statements; the different criteria for your queries; and perhaps different tables to query based on different conditions.

These SQL strings are not parsed for errors because they are generated at execution time, and they may introduce security vulnerabilities into your database. Also, SQL strings can be a nightmare to debug, which is why I have never been a big fan of dynamically built SQL statements; however, sometimes they are perfect for certain scenarios.

A dynamic example

The question I answer most often is, “How can I pass my WHERE statement into a stored procedure?” I usually see scenarios similar to the following, which is not valid TSQL syntax:

DECLARE @WhereClause NVARCHAR(2000)
SET @WhereClause = ' Prouct = ''Computer'''

SELECT * FROM SalesHistory WHERE @WhereClause

In a perfect world, it would make much more sense to do the following:

DECLARE @Product VARCHAR(20)
SET @Product = 'Computer'

SELECT * FROM SalesHistory WHERE Product = @Product

It isn’t always this easy. In some scenarios, additional criteria is needed, and as tables grow wider, more and more criteria is often needed. This can typically be solved by writing different stored procedures for the different criteria, but sometimes the criteria is so different for each execution that covering all of the possibilities in a stored procedure is burdensome. While these stored procedures can be made to take into account every WHERE statement possible depending on different parameters, this often leads to a degradation in query performance because of so many conditions in the WHERE clause.

Let’s take a look at how to build a simple dynamic query. First, I need a table and some data to query. The script below creates my SalesHistory table and loads data into it:

CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1),
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO

SET NOCOUNT ON

DECLARE @i INT
SET @i = 1
WHILE (@i <=5000)
BEGIN

INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(ww, @i, '3/11/1919'),
DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(ww, @i, '3/11/1927'),
DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(ww, @i, '3/11/1908'),
DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1

END

Now I will build my stored procedure that accepts a WHERE clause. For the purpose of this example, I will assume that the WHERE clause was built dynamically from the calling client application.

CREATE PROCEDURE usp_GetSalesHistory
(
@WhereClause NVARCHAR(2000) = NULL
)
AS
BEGIN
DECLARE @SelectStatement NVARCHAR(2000)
DECLARE @FullStatement NVARCHAR(4000)

SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '
SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')

PRINT @FullStatement
EXECUTE sp_executesql @FullStatement

/*
--can also execute the same statement using EXECUTE()
EXECUTE (@FullStatement)
*/
END

I set the @WhereClause parameter to allow NULL values because we may not always want to pass a value in for the @WhereClause.

For every execution of this stored procedure, every field is returned for the TOP 5 rows from SalesHistory. If there is a value passed in for the @WhereClause parameter, the executing statement will append that string to the @SelectStatement string. Then I use the stored procedure sp_executesql to execute the dynamically built SQL string.
sp_executesql or EXECUTE()

There are two ways to execute dynamic SQL in SQL Server: use the sp_executesql system stored procedure or the EXECUTE() operator. Sometimes the two methods can produce the same result, but there are differences in how they behave.

The system stored procedure sp_executesql allows for parameters to be passed into and out of the dynamic SQL statement, whereas EXECUTE() does not. Because the SQL statement is passed into the sp_executesql stored procedure as a parameter, it is less suseptible to SQL injection attacks than EXECUTE(). Since sp_executesql is a stored procedure, passing SQL strings to it results in a higher chance that the SQL string will remain cached, which should lead to better performance when the same SQL statement is executed again. In my opinion, sp_executesql results in code that is a lot cleaner and easier to read and maintain. These reasons are why sp_executesql is the preferred way to execute dynamic SQL statements.

In my previous example, I looked at how you can build a simple SQL statement by passing a WHERE clause into a stored procedure. But what if I want to get a list of parameter values from my dynamically built SQL statement? I would have to use sp_executesql because it is the only one of my two options that allows for input and output parameters.

I am going to slightly modify my original stored procedure so that it will assign the total number of records returned from the SQL statement to an output parameter.

DROP PROCEDURE usp_GetSalesHistory
GO
CREATE PROCEDURE usp_GetSalesHistory
(
@WhereClause NVARCHAR(2000) = NULL,
@TotalRowsReturned INT OUTPUT
)
AS
BEGIN
DECLARE @SelectStatement NVARCHAR(2000)
DECLARE @FullStatement NVARCHAR(4000)
DECLARE @ParameterList NVARCHAR(500)

SET @ParameterList = '@TotalRowsReturned INT OUTPUT'

SET @SelectStatement = 'SELECT @TotalRowsReturned = COUNT(*) FROM SalesHistory '
SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')

PRINT @FullStatement
EXECUTE sp_executesql @FullStatement, @ParameterList, @TotalRowsReturned = @TotalRowsReturned OUTPUT
END
GO

In the above procedure, I need to declare a parameter list to pass into the sp_executesql stored procedure because a value is being assigned to the variable at run time. The only other change to the sp_executesql call is that I am assigning the output parameter from the call to the local @TotalRowsReturned parameter in my usp_GetSalesHistory stored procedure.

I can even call my usp_GetSalesHistory stored procedure similar to the way I did before, but with the addition of an output parameter to indicate the rows that were returned.

DECLARE @WhereClause NVARCHAR(2000), @TotalRowsReturned INT
SET @WhereClause = 'WHERE Product = ''Computer'''

EXECUTE usp_GetSalesHistory
@WhereClause = @WhereClause,
@TotalRowsReturned = @TotalRowsReturned OUTPUT

SELECT @TotalRowsReturned

Caution

Although I am not a huge fan of using dynamic SQL statements, I believe it is a great option to have in your tool belt.

If you decide to incorporate dynamic SQL into your production level code, be careful. The code is not parsed until it is executed, and it can potentially introduce security vulnerabilities that you do not want.

If you are careful with your dynamic SQL statement, it can help you create solutions to some pretty tricky problems.
Passing table valued parameters in SQL Server 2008

How to pass table parameters

I need to set up my SalesHistory table, which holds my product sales. The following script will create the table in the database of your choice:


IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO

The first step in setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine. This allows you to define the type of table and reuse it as needed in your procedure code. This code creates the SalesHistoryTableType table type definition:

CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO

If you want to view other types of table type definitions in your system, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

I want to define the stored procedure that I plan on using to handle my table valued parameter. The following procedure accepts a table valued parameter, which is of the specific SalesHistoryTableType, and loads the SalesHistory with the records in the table parameter with a value of ‘BigScreen’ in the Product column.

CREATE PROCEDURE usp_InsertBigScreenProducts
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product, SaleDate, SalePrice
)
SELECT
Product, SaleDate, SalePrice
FROM
@TableVariable
WHERE
Product = 'BigScreen'

END
GO

You can use the table variable being passed in as any other table for querying data.
Limitations to passing table parameters

You must use the READONLY clause when passing in the table valued variable into the procedure. Data in the table variable cannot be modified — you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters — you can only use table variables as input parameters.
Putting my new table variable type to use

First, I must declare a variable of type SalesHistoryTableType. I don’t need to define the structure of the table again because it was defined when I created the type.

DECLARE @DataTable AS SalesHistoryTableType

The following script adds 1,000 records into my @DataTable table variable:

DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=1000)
BEGIN

INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))

SET @i = @i + 1
END

Once I have data loaded into my table variable, I can pass the structure to my stored procedure. (Make sure you add the data to the table variable and pass the table to the procedure all in the same batch. Table variables go out of scope as soon as the procedure or batch returns.)

Note: When table variables are passed as parameters, the table is materialized in the tempdb system database rather than passing the entire data set in memory; this makes handling large amounts of data rather efficient. All server side passing of table variable parameters are passed by reference, using the reference as a pointer to the table in the tempdb.

EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable

To see if my procedure performed the way I expect, I’ll run this query to see if the records were inserted into the SalesHistory table:

SELECT * FROM SalesHistory

Considerations

SQL Server 2008’s table parameter feature is a huge step forward in terms of development and potentially performance. The benefits to this feature are that it can: reduce server round trips, use table constraints, and extend the functionality of programming on the database engine.

There are some limitations to keep in mind, such as not being able to alter the data in the parameter and not being able to use the variable as output.