Manage Sql Server Database within Visual Studio Team System

Introduction
In the below post I am going show how VSTS use full for the database developers. Note that I am not having full idea about all things but it’s very helpful to DBA and developer who is working on large project with the large no of team member.

Some time when we are releasing our project/product to client problem arise that our dataset version is not matching with the current application version. There is always problem when no of people working on same project and updating database object frequently and we miss updated object at time of release.

But now with the help of the VS team system 2008 we can easily resolve this issue and keep the database changes in VSS. In this article I am going to show how to create Database project and the option you get after creating the database. You get following advantages

• Manage DB change through schema management
"One version of the truth" Offline sandbox for multiple developers

•  Source control for DBs
Ability to store different versions as change sets

• Schema and data compare
Generate scripts/apply updates

Start with Create DataBase Project

   1. Sql Server 2000
   2. Sql Server 2000 Wizard
   3. Sql Server 2005
   4. Sql Server 2005 Wizard

Sql Server 2005 Wizard
Select database instance you installed on your machine or from you network.

Welcome note by wizard

Select you schema or the object type
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Select database collation and some other options
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

• Each SQL Server collation specifies three properties:
• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text).
• The code page used to store non-Unicode character data.

Create new database or import database schema form existing one by selecting from combo box
Choose important options according the needs

Provide information to connect with the database

Build and deploy Configuration

Once you done with the database creation project get created with the folder you see in below screen

• Stored Procedures
• Functions
• Triggers
• Arbitrary SQL

You can find below options which allow you to compare data or schema of the database

• Allows comparisons of:    
Project -> database; database -> database

• Object level script difference between DBs
• Notifies when data loss may occur
• Generate script or apply changes directly
• It’s smart!
understands constraints, creates temp tables to hold data, more

• Compare security settings
Users, roles and permissions

After you done with the adding and changing database object you can build and deploy project as you can see in blow project

Summary
DataBase project and the related utilities to support it by VSTS is very important, time saving and make database maintainable.

Linq Joins with SelectMany

SelectMany projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence. In this post I am going to show how you can use SelectMany extension method to achieve the join between related tables easily without writing long queries.

To understand this consider example below

As shown in above image customer is having relation with the order table.

Scenario 1 
Find all of the users who have an order.

To achieve the above requirement, you need to apply an inner join between the tables. The Linq query to do this is:

var CustomerWithOrders = from c in Customers
  join from o in c.Orders
    select new { 
           c.Name, 
           p.Description, 
           o.Price 
          }

The query above inner joins the customer table with the order table and returns those customer having orders.

The shorter way to achieve the above is to use SelectMany:

Customers.SelectMany (
      c => c.Orders, 
      (c, o) => 
         new  
         {
            Name = c.Name, 
            Description = o.Description, 
            Price = o.Price
         }
   ) 

Scenario 2
Find all of the users who have an order and display N/A for the users who do not.

To achieve the above requirement, you need to apply an Outer join between the tables. The Linq query to do this is:

var CustomerWithOrders = from c in Customers
 join from o in c.Orders.DefaultIfEmpty()
 select new { 
         Name =c.Name,
         Description = ( o.Description ?? "N/A"), 
         Price = (((decimal?) o.Price) ?? 0)  
        }

The query above outer joins the customer table with the order table and returns all of the customers with or without orders.

But with the SelectMany, the query is:

Customers.SelectMany (
      c => c.Orders.DefaultIfEmpty (), 
      (c, o) => 
         new  
         {
            Name = c.Name, 
            Description = (o.Description ?? "N/A"), 
            Price = ((Decimal?)(o.Price) ?? 0)
         }
   ) 

Summary
SelectMany is a way to flatten several results into a single sequence.
CodeProject

Optional parameters (Use and Limitation)

What is Optional Parameter(s) ?
In C#4.0 Optional parameter allows to create method(s) with the parameters which is having default values with it.
Syntax

Accessmodifier ReturnType NameOfMethod(Type param1, Type Pram2

,....,Type Param(n-1)=DefultValue,Type Param(n)=DefultValue )
{
   //code goes here
}

Example

public void MyOptParamTest(int a, int b=10)
{
   //code goes here
}

Points To Remeber
It's very easy to implement the method with the Option Parameter(s). But one should keep in mind following points when using it

• Optional parameter(s) must come after required parameters.

//valid
public void MyOptParamTest(int a, int b=10) 
//not valid
public void MyOptParamTest(int b=10,int a ) 

• But Parameter array always comes last i.e after Optional parameter(s).

//valid
public void MyOptParamTest(int a, int b=10, params int[] myArray) 

• Parameter array cannot be optional parameter

//not valid
public void MyOptParamTest(int a, int b=10, 
                         params int[] myArray = null)

• Optional parameter(s) cannot be specified with ref and out keywords.

//not valid
public void MyOptParamTest(int a,ref int b=10) 

• Optional parameter(s) can have any time but value of Optional parameter(s) can be constants i.e string,int etc. , null, Enum number, const member and the default(T) operator.

• Optional parameter(s) - value types, you can call the parameterless constructor.

• Optional parameter(s) - implicit conversion from the specified value to the parameter type, but this must not be a user-defined conversion.

//valid 
public void MyOptParamTest(int a,decimal b=10) 
//not valid -- user defined conversion
public void MyOptParamTest(int a,
                    XTypeVariable b=10)  

Where to USE ?

• Use full when coding methods where default value play good role i.e By specifying null value to option parameter(s).

        Consider case where I have method to calculate age of the person

public int CalcualteAge(DateTime birthDate,DateTime? deathDate= null)
    {
       DateTime actualEndDate = deathDate ?? DateTime.Now;
       TimeSpan dateDifference = actualEndDate.Subtract(birthDate);
       int days = dateDifference.Days;
    }

        So by the above way its makes code easy.

• Decrease amount of overload method(s) where it's just become overloaded by no. of passing parameter

        Consider same above case where I have method to calculate age of the person

///For person who is dead
    public int CalcualteAge(DateTime birthDate,DateTime deathDate)
    {
       TimeSpan dateDifference = deathDate.Subtract(birthDate);
       int days = dateDifference.Days;
    }

    ///For person who is alive
    public int CalcualteAge(DateTime birthDate)
    {
       DateTime actualEndDate =  DateTime.Now;
       TimeSpan dateDifference = actualEndDate.Subtract(birthDate);
       int days = dateDifference.Days;
    }

        But if we use Optional parameter

public int CalcualteAge(DateTime birthDate,DateTime? deathDate= null)
    {
       DateTime actualEndDate = deathDate ?? DateTime.Now;
       TimeSpan dateDifference = actualEndDate.Subtract(birthDate);
       int days = dateDifference.Days;
    }

         So by the use of the optional parameter(s) we just have one method which is do the task of two method.

Summary
Optional parameter(s) makes code small,simple and easy to understand.

Programming practice for server side state maintenance variable

Http protocol is stateless, so we need to maintain state on Client side or Server side. For this time I am going to discuss about the maintaining state on Server Side with one programming practice.

Problem
To maintain state on Server side most of the developer use Session, Caching, Application variables. But most of the beginner developer does one mistake, which is

session["myData"] = data;

Code pushes data in session variable(s).

Data = (Conversion_To_Data_Type_Of_Data) session["mydata"];

Code gets data back from session variable.
So most of the developers follow above procedure to push and get data from server side variable.

Disadvantage

• Developer requires remembering name of string value i.e name of the variable. If you refer above code its mydata.
• If no. of developer working on project, it’s hard to maintain server side variable if we follow above procedure. i.e you maintenance increase.
• If there are large no developer working in your project you require to inform each and every one about the variable you are declaring.

Solution
The easiest solution that I found after doing so much programming is as following

Step 1
Create one Static class

public class SessionPropeties
{
  ….. code 
}

Step 2
Create property for each session variable of your application as show below

public int UserId
{
        get
        {
           if(HttpContext.Current.Session["UserID"]!=null)
            return Convert.ToInt32(HttpContext.Current.Session["UserID"].ToString());
           else 
          return 0;
        }
        set
        {
            HttpContext.Current.Session["UserID"] = value;
        }
}

After following above steps class will be like as below

public class SessionPropeties
{
 public DataType prop1
 {
        get
        {
           if(HttpContext.Current.Session["prop1"]!=null)
            return Convert.ToDataType (HttpContext.Current.Session["prop1"].ToString());
           else 
            return defaultvalue;
        }
        set
        {
            HttpContext.Current.Session["prop1"] = value;
        }
     
 }
 public DataType prop2
 {
        get
        {
           if(HttpContext.Current.Session["prop2"]!=null)
            return Convert.ToDataType (HttpContext.Current.Session["prop2"].ToString());
           else 
            return defaultvalue;
        }
        set
        {
            HttpContext.Current.Session["prop2"] = value;
        }
     
 }

 ...........

}

Step 3
In coding to get and set data just require to call this properties
To set data

SessionProperties.UserId = 1;

To get data

int userid = SessionProperties.UserId;

Above implementation is somewhat similar to singleton pattern because as you see there is only one instance of class as all properties in it are static. But the real thing is the all are pointing to session variable of the user session.

Advantages

• Once you declare your properties you do not need to remember it. You just need to call your Session property class.
• Maintenance becomes easy when project size is large or no. of developer working in team, because you just need to refer Sessionproperties class having all severside properties. And you can also add property you need and you don’t require informing all the people working in your team.

Summary

You can maintain your Cache and Application server side variable in similar way. So its better you encapsulate your server side variable in on class which makes your task easy when you do code in web application.

Dynamic Types and Use of it

Download Code
What is Dynamic Types ?

Dynamic types are new feature of C# 4.0, which can be defined easily by using the keyword dynamic. Dynamic types are not strongly typed i.e not bound with any specific data type, so they are different from implicit data type defined using var which can bind with any data type when we define. Like any other type Dynamic types also have base class System.Object.

How Dynamic types works ?

Syntax:
dynamic variable = value;

Let’s see the below example to understand dynamic types in more detail.

static void Main(string[] args)
{

Step 1
Declare a dynamic variable named dc_variable and assigned integer value 5, than print its type and value of the variable

dynamic dc_variable = 5;
Console.WriteLine("Type = " + dc_variable.GetType() + " Value=" + dc_variable);

Step 2
Change the value of the declared variable to boolean type and assign false to it, then print its type and value.

dc_variable = false;
Console.WriteLine("Type = " + dc_variable.GetType() + " Value=" + dc_variable);
Console.ReadLine();
}

When you compile the above code, the compiler will not give any error because the variable is of dynamic type. Since you can change it value at runtime to any other type, so, it is not strongly typed like other variable type of C#.
After compiling when you run the code you will get below output

Above example proves that the dynamic type variable gets evaluated at runtime not at compile time.

Point To Remember

There is no intelligence support for the dynamic types in Visual Studio which we find with other type, or, user defined types.

As shown in above image you can see that the variable is dynamic type and its type gets resolved at runtime.
Because Dynamic type values get resolved at runtime not at compile type you can attach any method with the variable as below.

dc_variable.toDisplayType();

But remember if there is no method like this fond with dynamic variable then it would result in RuntimeBinderException at runtime. So to avoid this problem you can use try catch block and catch exception at runtime

try
{
  dc_variable.toDisplayType();
}
catch(RuntimeBinderException ex)
{
  //code to handle exception
}

So beware with the name of the method we are calling upper case or lower case otherwise it throws exception at runtime.

Limitation

1. Dynamic types are not compatible with anonymous method and lambda expression. So the code like below is not possible

var a = ( dc_variable => x= x+1 );

2. Dynamic types are not able to use most of the methods of Linq API.
3. Linq queries cannot use Dynamic types to get data.
    So, following is not possible with the dynamic variable

var list = form a in dc_variable;

Where dynamic variable are more usefull ?

Dynamic types are more useful when we use Reflection i.e. late binding or COM component in application. Basically it minimizes the amount of code and also makes the code under stable.
Here in this post I am just going to discuss how to use dynamic types when developing application using Refection.
To understand how we use dynamic types when coding using reflection feature go through following steps

Step 1
Create one class Helloworld

public class HelloWorld
{
        public void showMessage()
        {
            Console.WriteLine("Hello World");
        }
        public void showMessageWithDetails(string name, string message)
        {
            Console.WriteLine("Hello " + name + "your message is " + message);
        }
}

As you see Class has two methods (showMessage without parameter,showMessageWithDetails with parameter) which simply display message on console.

Stpe 2
I am going to call the method(s) of the Hello World class using reflection.

public void DisplayMessageUsingReflection(Assembly asm)
{
            try
            {
                //get type meta data 
                Type helloWorld = asm.GetType("ShowMessages.HelloWorld");
                //create object 
                object obj = Activator.CreateInstance(helloWorld);
                //get the methodinfo
                MethodInfo mi = helloWorld.GetMethod("showMessage");
                //call method
                mi.Invoke(obj, null);
            }
            catch (Exception ex)
            { }
}

Above method takes assembly as argument. Create object of the Hello World class and then call the showMessage method.

Stpe 3
Now we call same method by using dynamic types

public void DisplayMessageUsingDynamic(Assembly asm)
 {
            try
            {
                //get tye meta data 
                Type helloWorld = asm.GetType("ShowMessages.HelloWorld");
                //create object 
                dynamic obj = Activator.CreateInstance(helloWorld);
                //call method
                obj.showMessage();
            }
            catch (Exception ex)
            { }
 }

By using dynamic types we can easily call the method and it’s also minimizing the amount of the code to be written.
Now I am going to call showMessageWithDetails method of Hello World class, which is having set of parameter.

Step 4
First calling method with the Normal Reflection code

public void DisplayMessageUsingReflectionWithParamete(Assembly asm)
{
            try
            {
                //get type meta data 
                Type helloWorld = asm.GetType("ShowMessages.HelloWorld");
                //create object 
                object obj = Activator.CreateInstance(helloWorld);
                //get the methodinfo
                MethodInfo mi = helloWorld.GetMethod("showMessageWithDetails");
                //call method and passing parameter
                object[] args = { "pranay", "test message for you" };
                mi.Invoke(obj, args);
            }
            catch (Exception ex)
            { }
}

To call method with the parameter we need to create array of the object which get passed to the method when we call the method.

Step 5
Now calling method using dynamic type

public void DisplayMessageUsingDynamicWithParameter(Assembly asm)
 {
            try
            {
                //get type meta data 
                Type helloWorld = asm.GetType("ShowMessages.HelloWorld");
                //create object 
                dynamic obj = Activator.CreateInstance(helloWorld);
                //call method
                obj.showMessageWithDetails("pranay", "test message for you");
            }
            catch (Exception ex)
            { }
 }

When calling with dynamic there is no need to create any extra array of object. Just have to pass parameter as we do in normal method call.

Summary
Dynamic types are more help full we do late binding at runtime. It’s make code easy and understandable.