Wednesday, April 06, 2011

What’s new in SQL 2008 

Perquisites: You should have SQL 2008 and .NET Framework 4

Table-Valued Parameters
 
1) Create your data type first,

The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:
CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

2) Create a stored procedure, which will cosume this newly created table 'type'

After you create a table type, you can declare table-valued parameters based on that type. The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY) 
/*
 -- You can use this @tvpNewCategories table for insert/update/select joins, anything as a normal table
*/
UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;


3) Passing table from code to stored procedure
 // Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);
 
// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
 
// Execute the command.
insertCommand.ExecuteNonQuery();
}

Limitations
1)      You cannot pass table-valued parameters to user-defined functions.

2)     Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.

3)     Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

4)      You cannot use ALTER TABLE statements to modify the design of table-valued parameters.






Monday, April 04, 2011

New features in C# 3.0

Prerequisites :You should have Visual Studio 2008 Professional Edition

Implicitly Typed Local Variables

Let us start by discussing the implicitly typed local variables feature.
C# 3.0 introduces a new keyword called "var". This new keyword enables you to declare a variable whose type is implicitly inferred from the expression used to initialize the variable. For example, consider the following line of code:
var age = 30;
The preceding line initializes the variable age to value 30 and automatically gives it the type of integer. Note that the variable age is a strongly typed variable (integer in this case) and doesn't carry the overhead of a generic object type such as System.Object class.
Note that the var keyword is not a completely new type and it just signals the compiler to take a look at the right-hand side of the expression to decipher the type. If the right-hand side is an int, the compiler will replace the var keyword with int. Here are some of the key characteristics of implicitly typed local variables.
  • They need to be declared and initialized in the same statement.
  • They can't be initialized to null.
  • They can't be used as class members.
  • They are primarily used to declare anonymous types (which you will see later in this article) as part of a LINQ expression.

Implicitly Typed Arrays 

In the previous section, you have seen how to use the var keyword for implicitly typing variables. In addition to implicitly declaring variables, you can also use the var keyword for declaring arrays as well. For example, consider the following lines of code:
int[] numbers = new int[] { 1, 2, 3, 4, 5};
string[] names = new string[] { "Dave", "Doug", "Jim" };

By using the var keyword, you can rewrite the preceding lines of code as follows:
var numbers = new[] { 1, 2, 3, 4, 5};
var names = new[] { "Dave", Doug, "Jim" };

The compiler infers the type of the array elements at compile-time by examining the values from the initialization expression.


Auto Implemented Properties 

Whenever you declare a class, most of the times the class is used only as a placeholder with getters and setters for holding property values without any additional logic. For example, consider a simple class like the following:
public class Person
{
    int _id;
    string _firstName;
    string _lastName;

    public int ID
    {
        get{return _id;}
        set{_id = value;}
    }
    public string FirstName
    {
        get{return _firstName;}
        set{_firstName = value;}
    }
    public string LastName
    {
        get{return _lastName;}
        set{_lastName = value;}
    }
    public string FullName
    {
        get{return FirstName + " " + LastName;}
    }
}

As you can see from the above class declaration, it doesn't contain any additional logic. The get and set properties are repetitive and they simply set or get the values of the properties without adding any value. In C#, you can simplify that by leveraging the new feature named Auto-Implemented properties. By taking advantage of this new feature, you can rewrite the above code as follows:
public class Person
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName
    {
        get
        {
            return FirstName + " " + LastName;
        }
        private set {;}
    }
}

In the above code, when you declare a property, the compiler automatically creates a private, anonymous field that is available only to the property's get and set accessors. Note that the auto implemented properties must declare both a get and set accessor. However if you need to create a read-only property, modify the scope of the set accessor to be private

Object Initializers 

In previous versions of C#, you would create the object in one step and then populate its properties in a separate next step. However with C# 3.0, you can create the object as well as set its properties in a single line of code. For example, consider the following lines of code:
Person obj = new Person();
obj.ID = 1;
obj.FirstName = "Thiru";
obj.LastName = "Thangarathinam";
MessageBox.Show(obj.FullName);

In the above code, you create the Person object and set its properties appropriate values. By leveraging the object initializers feature, you can simplify the above code as follows:
Person obj = new Person { ID = 1, FirstName = "Thiru", LastName = "Thangarathinam" };
MessageBox.Show(obj.FullName);

In the above code lines of code, you directly assign the values to the properties of the Person object. Although you can simulate this behavior using constructor for the object, object initializers reduce the need to have a specific constructor for every variation of argument variation we need over time.
This becomes all the more important in cases where you need to write LINQ based select projection queries which transform and create a new object for the results of the query.

Collection Initializers 

In C# 2.0, when you want to populate a collection, you need to write the following line of code:
List<string> names = new List<string>();
names.Add("David");
names.Add("Tim");
names.Add("Doug");

Now with the introduction of the new collection initializers feature in C# 3.0, you can shorten the above lines of code to a single line of code:
List<string> names = new List<string> {"David", "Tim", "Doug"};

Anonymous Types 

As the name suggests, anonymous types allow you to create a type on-the-fly at compile time. The newly created type has public properties and backing fields defined for the members you initialize during construction. For example, consider the following line of code:
var obj=new{ID=1,FirstName="Thiru",LastName="Thangarathinam"};
In the above line, you just specify the various attributes you want to have in the anonymous class and assign the instantiated object to a variable of type "var". The actual type assigned to obj is determined by the compiler. Since the compiler assigns the name of the type only at compile time, you can't pass an anonymous type to another method and it can only be used within the method they were declared.
When the compiler sees the above code, it automatically declares a class as follows:
class __Anonymous1
{
    private int _id = 1;
    private string _firstName = "Thiru";
    private string _lastName = "Thangarathinam";

    public int ID
    {
        get{return _id;}
        set{_id = value;}
    }
    public string FirstName
    {
        get{return _firstName;}
        set{_firstName = value;}
    }
    public string LastName
    {
        get{return _lastName;}
        set{_lastName = value;}
    }
}

Anonymous Types use the Object Initializer to specify what properties the new type will be declare. This allows us to reduce code looking similar to this:
Note that the anonymous types are just meant to be placeholders for quickly defining entity types and you can't add methods or customize the behavior of an anonymous type

Extension Methods 

Another important feature introduced with C# is the ability to add new static methods to existing classes, known as extension methods. Using this new feature, you can extend the built-in classes (such as the String class) to support your custom requirements. For example, you can add a new method named "IsValidZipCode" to the string class that validates the zip code format. Let us discuss the code required to accomplish this:
namespace StringExtensions
{
    public static class CustomStringExtension
    {
        public static bool IsValidZipCode(this string input)
        {
            Regex regEx = new Regex(@"^\d{5}$");
            return regEx.IsMatch(input);
        }
    }
}


As part of the declaring the arguments for the IsValidZipCode, you specify the name of the type to which the extension method should be added as the first parameter. In this case, since we want the IsValidZipCode method to be added to the string class, you specify string as the first parameter. Once you are inside the IsValidZipCode() method, you can access all of the public properties/methods/events of the actual string instance that the method is being called on. In this example, you return true or false depending on whether it is a valid zip code or not.
Now that you have implemented the extension method, the next step is to invoke it from the client application. To be able to do that, you first need to import the namespace in which the CustomStringExtension is located.
using StringExtensions;
Once you have imported the namespace, the next step is to declare a variable of type string and invoke the IsValidZipCode() method.
private void btnTestExtensionMethod_Click(object sender, EventArgs e)
{
    string zip = "85226";
    if (zip.IsValidZipCode())
        MessageBox.Show("Valid Zipcode format");
    else
        MessageBox.Show("Invalid Zipcode format");
}

As you can see from the preceding lines of code, the extension methods allow you to write cleaner and easy-to-maintain code.
Here are some of the key characteristics of extension methods:
  • The extension method as well as the class that contains the extension method should be static.
  • Although extension methods are static methods, they are invoked as if they are instance methods.
  • The first parameter passed to the extension method specifies the type on which they operate and it is preceded by the "this" keyword.
  • From within the extension method, you can't access the private variables of the type you are extending.
  • Instance methods take precedence over extension methods in situations where they have same signature.
Lambda Expressions 

Anonymous methods is a new feature introduced with C# 2.0 that enables you to declare your method code inline instead of with a delegate function. Let us take a look at a simple anonymous method:
public Forms()
{
    check = new CheckBox(...);
    text  = new TextBox(...);
    checkBox.CheckedChanged += delegate
    {
        text.Text = "...";
    };
}

As you can see in the above code, you don't have to explicitly declare a new method to link it with an event. C# 3.0 introduces an even simpler syntax, lambda expressions, which you write as a parameter list followed by the "=>" token, followed by an expression or a statement block.
Lambda expressions are simply functions and they are declared in the context of expressions than as a member of a class. It is an inline expression or a statement block which can be used to pass arguments to a method or assign value to delegate. All lambda expressions use the lambda operator => and the left side of the operator denotes the results and the right side contains the expression to be evaluated. For instance, consider the following lambda expression:
age => age + 1
The above function takes one argument named age, and returns age + 1 as the result. As you can see, Lambda expressions follow the below syntax:
(parameter-list) => expression;
where expression can be any C# expression or a block of code. Just like anonymous methods you can use a lambda expression in place of a delegate. Here are some sample lambda expressions and their corresponding delegates.
//Explicitly typed parameter
(Person obj) => MessageBox.Show(obj.FirstName.ToUpper());

//Implicitly typed parameter
(obj) => obj.FirstName == "Thiru";

//Explicitly typed parameter
(int a, int b) => a + b

//Implicitly typed parameter
(x, y) => { return x + y; }

As you see from the preceding lines of code, lambda expressions can be written in such a way that it can infer the parameter type from the signature of the delegate it is assigned to.
Conclusion

In this article, you have understood the object oriented features introduced with C# 3.0. Specifically,
  • How to use auto-implemented properties for creating placeholder classes
  • How to initialize object and collections using a simple, intuitive syntax
  • How to extend built-in classes using extension methods
  • How to use anonymous types for creating classes on-the-fly
  • How to use Lambda expressions to declare method inline
As you can see, the new features of C# make the development of .NET applications a breezy experience by reducing the number of lines of code to perform common operations such as initializing an object.