Caching Sql Parameters in C#.Net

Here I am going to discuss about how to cache the sql parameters.

Suppose if you want to add records to the table, either you use Direct-SQL and Stored Procedure. Stored Procedure is compiled one so it’s faster than Direct-SQL.

If you are using stored procedure, you call the stored procedure from the client side either by EXEC Or Parameterized call.

Parameterized call reuse the existing execution plan instead of creating the new execution plan every time when you call using EXEC.

So, Parameterized call is more efficient than EXEC.

If you are using Parameterized call, you have to create the array of Sql Parameters to pass the parameter value. You have to create the array of parameters every time when you call the stored procedure.

If you are calling 100 times, 100 times you have to create the array of parameters.

Cache:

Instead of creating the parameters every time, you can the cache the array of sql parameters in the first call. In next call, you can get the Clone of Sql Parameters from Cache. And then assign the values to the clone sql parameter.


Microsoft released the Enterprise Library having the parameter caching mechanism.

Here I am going to discuss the parameter caching is similar to that in a simple way.

Parameter Cache Code:

public class ParameterCache
{

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());


// Create and return a copy of the IDataParameter array.
private static IDataParameter[] CloneParameters(IDataParameter[] originalParameters)
{
IDataParameter[] clonedParameters = new IDataParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (IDataParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

// Empties all items from the cache
public static void Clear()
{
paramCache.Clear();
}

// Add a parameter array to the cache for the command.
public static void AddParameterSetToCache(string connectionString, string storedProcedure, IDataParameter[] parameters)
{
string key = CreateHashKey(connectionString, storedProcedure);
paramCache[key] = parameters;
}

// Gets a parameter array from the cache for the command. Returns null if no parameters are found.
public static IDataParameter[] GetCachedParameterSet(string connectionString, string storedProcedure)
{
string key = CreateHashKey(connectionString, storedProcedure);
IDataParameter[] cachedParameters = (IDataParameter[])(paramCache[key]);
return CloneParameters(cachedParameters);
}

// Gets if a given stored procedure on a specific connection string has a cached parameter set
public static bool IsParameterSetCached(string connectionString, string storedProcedure)
{
string hashKey = CreateHashKey(connectionString, storedProcedure);
return paramCache[hashKey] != null;
}

// create the hash key based on connectionstring and stored procedure name
private static string CreateHashKey(string connectionString, string storedProcedure)
{
return connectionString + ":" + storedProcedure;
}

}

The above shared class is for caching and accessing the cached parameters.

HashTable – Used to store the array of parameters based on hash key. It is used as a cache.

CreateHashKey – Caching the Sql parameters based on hash key formed from connectionstring and stored procedure name.

AddParameterSetToCache – Cache the parameters in HashTable based on connectionstring and stored procedure name.

CloneParameters – Get the clone of cached parameters.

GetCachedParameterSet – Get the parameters from the HashTable(Cache) based on HashKey.

IsParameterSetCached – Used to check whether parameters already cached based on hashkey.


Clear – Clear the HashTable(Cache)


Customer Class:

public class Customer
{

#region C# 3.0 Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

}

How to use ParameterCache shared class:

//Add the Customer Information to Customer Table
public void AddCustomer(Customer cust, string connectionString)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlParameter[] param = null;
string spName = "AddCustomer";

try
{
conn = new SqlConnection(connectionString);
cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;

param = GetParameters(cust, connectionString, spName);

conn.Open();
cmd.ExecuteNonQuery();

}
catch (Exception)
{
throw;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();

if (cmd != null)
cmd.Dispose();

param = null;
}




}

The above method is to add the records to the customer table. For that you have to prepare the parameters for stored procedure. GetParameters() method used to prepare the parameters.

// Prepare the Sql Parameters for Adding Customer
// Get the Clone parameters if already cached based on connectionstring and store procedure name
// Otherwise create the new Sql parameter array and then add to cache for next time use.
private SqlParameter[] GetParameters(Customer cust, string connectionString, string storeProcedure)
{
SqlParameter[] param = null;

if (ParameterCache.IsParameterSetCached(connectionString, storeProcedure))
{
param = (SqlParameter[])ParameterCache.GetCachedParameterSet(connectionString, storeProcedure);

param[0].Value = cust.CustomerID;
param[1].Value = cust.Name;
}
else
{
param = new SqlParameter[2];

param[0] = new SqlParameter("@CustomerID", SqlDbType.BigInt, 8);
param[0].Value = cust.CustomerID;
param[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[1].Value = cust.Name;

ParameterCache.AddParameterSetToCache(connectionString, storeProcedure, param);
}

return param;
}

The above method is for returning array of sql parameters. First it checks the cache, if already exists then get the parameters from the cache. And then assign the values of clone sql parameters.
If not exists, create the new array of sql parameters and add prepared parameters into cache for next time use.

Only first time call, it takes some time to cache the parameters. Next call onwards prepare the sql parameters faster.

Conclusion:
You can get better performance on caching the sql parameters if you are calling the stored procedure frequently.

Parallel Extensions to .Net Framework - PLINQ

Microsoft released the CTP version of Parallel Extensions to the .Net Framework.
With the use of parallel extensions, we can write the program that using MULTI core (Dual,QUAD,etc) processors.

Usually we write the program for single core processor, so our hardware resources are not fully utilized. But with the use of Parallel Extensions, we can utilize the hardware resources effectively.

You can download the CTP version of Parallel Extension here


Parallel Extensions provide several new ways to express parallelism in your code:


Declarative data parallelism(PLINQ) - Parallel Language Integrated Query (or Parallel LINQ) is an implementation of LINQ-to-Objects that executes queries in parallel, scaling to utilize the available cores and processors of the machine. Because queries are declarative, you are able to express what you want to accomplish, rather than how you want to accomplish it.


Imperative data parallelism - Parallel Extensions also contains mechanisms to express common imperative data-oriented operations such as for and foreach loops, automatically dividing the work in the loop to run on parallel hardware.


Imperative task parallelism - Rather than using data to drive parallelism, Parallel Extensions enables you to express potential parallelism via expressions and statements that take the form of lightweight tasks. Parallel Extensions schedules these tasks to run on parallel hardware and provides capabilities to cancel and wait on tasks.

Code(PLINQ):

1. Create the new Project from VS 2008.
2. Reference the System.Threading.dll from the Parallel Extension installation path.
3. Create the static class like the following.

class ValidNumbers
{
//Sequential Method
public static int SequentialValidNumberCount(IEnumerable<string> list)
{
return list.Where(n => IsNumber(n)).Count();
}

//Parallel Method
public static int ParallelValidNumberCount(IEnumerable<string> list)
{
return list.AsParallel().Where(n => IsNumber(n)).Count();
}

//validate the string whether its number
private static bool IsNumber(string input)
{
return !(Regex.IsMatch(input, "[^0-9]"));
}
}

In the Static class, we have declared the two methods for calculating the number of integers in the array.

SequentialValidNumberCount - This is sequential way of running the method. It uses single core.

ParallelValidNumberCount - This is Parallel way of running the method. AsParallel() is the extension method. It uses all the core in the processor.


Calling code:

string[] arr = { "1", "a", "2" };
int count = 0;

//Sequetial way of accessing
count = ValidNumbers.SequentialValidNumberCount(arr);

//Parallel way of accessing
count = ValidNumbers.ParallelValidNumberCount(arr);

Parallel Method runs faster than sequential method because it uses all the core in the processor. You can see the difference between two in the Task Manager Performance monitor.

Exception Hunter from Red Gate

Redgate introduced the new tool called Exception Hunter. It hunts the exception in the code.

By using this tool, we can avoid unhandled exception or application crashes at run time. It anaylsis the code that you have written, generates the reports of possible exceptions will occur in the code.

So We can find the unhandle exception easily in the development cycle itself instead in QA cycle.

You can download it here

ASP.NET 3.5 Extensions CTP Preview

Microsoft released the CTP version of ASP.Net 3.5 Extensions. It provides the added features to framework 3.5. you can download it here .

ASP.Net 3.5 Extensions contains:

1. MVC (Model View Controller):
Its provides clear seperation of presention and logic . And also it provides URL rerouting option.

2. Dynamic Data Support :
It provides faster creation of dynamic data driven websites.

you can create simple data driven websites like
1. Create Dynamic Data Web Application Project
2. Add LINQ To SQL Class Item to the project.
3. Drag the required tables from database into LINQ to SQL Class.
4. Set the EnableTemplates to True in Web.Config like
<dynamicData dataContextType="" enableTemplates="true">
5. Run the Application to view, add, edit and delete the table records.

3. Silverlight Controls:
It provides additional silverlight controls.

4. AJAX Enhancements:
It provides browser history support for the AJAX ASPX pages.

5. ADO.Net Entity Framework:
It prviodes entity framework that enables the deveopers to model the database closely to real world application.

C# 3.0 Extension Methods

Microsoft introduced the cool and nice features of Extension Methods in C# 3.0.
You can add the methods to the any type like string, int, Collection or even customer class.

you have define and implement the extended methods in static class only and method also static.

Extension Class and Methods:

static class MyExtensionMethods
{

// this methods apply to all string type
public static bool IsNumber(this string input)
{
return !(Regex.IsMatch(input, "[^0-9]"));
}

// this methods apply to all type that derive from object type
public static bool IsExistsIn(this object obj, IEnumerable list)
{
foreach (object o in list)
{
if (o.Equals(obj))
return true;
}

return false;
}
}

Using Extension Methods:

string number = "45";
bool isNum = false;

// returns true
isNum = number.IsNumber();

// returns false
isNum = "45fg".IsNumber();


bool isExists = false;
string[] arr = { "Ayyanar", "Senthil", "Vaithy" };

// returns true
isExists = "Ayyanar".IsExistsIn(arr);

// returns false
isExists = "Ayya".IsExistsIn(arr);

Now we will see how to use this extension methods to custom class object.

Class:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

#region Override Methods

public override bool Equals(object obj)
{
Customer c = (Customer)obj;

if (c.CustomerID == this.CustomerID && c.Name == this.Name)
return true;
else
return false;
}

public override int GetHashCode()
{
return base.GetHashCode();
}

#endregion
}

Using Extension Method for Customer Object:

// C# 3.0 Collection Initializers


List<Customer> custList = new List<Customer>{
new Customer { CustomerID = 1, Name = "Ayyanar" },
new Customer { CustomerID = 2, Name = "Senthil" },
new Customer { CustomerID = 3, Name = "Vaithy" }
};

// C# 3.0 Object Initializers
Customer c = new Customer { CustomerID = 1, Name = "Ayyanar" };

// return true
isExists = c.IsExistsIn(custList);

C# 3.0 Collection Initializers

C# 3.0 has a nice feature of creating collection of objects in a simple way like C# 3.0 object initializers.

Class Structure:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

}


Conventional Method:

// C# 3.0 object Initialization Method
Customer custmObj1 = new Customer { CustomerID = 1, Name = "Ayyanar" };
Customer custmObj2 = new Customer { CustomerID = 2, Name = "Senthil" };
Customer custmObj3 = new Customer { CustomerID = 3, Name = "Vaithy" };

//List of Customer

List<Customer> listCust = new List<Customer>();

// Adding the object into the Customer List
listCust.Add(custmObj1);
listCust.Add(custmObj2);
listCust.Add(custmObj3);



C# 3.0 :


List<Customer> listStr = new List<Customer> {
new Customer{CustomerID = 1, Name = "Ayyanar"},
new Customer{CustomerID = 2, Name = "Senthil"},
new Customer{CustomerID = 3, Name = "Vaithy"},
};





In single statement, we can populate the multiple object into the list.

C# 3.0: Anonymous Types

C# 3.0 Anonumous types is a nice feature. you can create the object for the class without declaring the class, private variables or properties. suppose if you want to create a class with two properties,

Conventional Method:

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}


Object Creation:

Customer custm = new Customer();
custm.CustomerID = 1;
custm.Name = "Rich";


C# 3.0 Anonymous Types:

you can create a object for the anonymous class(No Class Name) with property initialization.


var cust = new { CustomerID = 1, Name = "Rich" };

The above code will create the object for Anonymous class with two properties of CustomerID and Name.

Visual Studio provides the intellisense for anonymous object without compiling.

Compiler will create the class(Anonymous) for anonymous object like(not exact) the below.


class Anonymous_1
{

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

}

if you create the multiple anonymous object, compiler will use the same class if type of the property is same.


var cust = new { CustomerID = 1, Name = "Rich" };

var cust1 = new { CustomerID = 2, Name = "David" };

From the above code, compiler will use the same anonymous class for creating the object.


var cust = new { CustomerID = 1, Name = "Rich" };

var cust1 = new { CustomerID = "2", Name = "David" };


From the above code, compiler will use the two class for each object because second anonymous object of property CustomerID type differ from first object.


Once you created the anonymous type object, you can't reassign the value for the property of anonymous type object.

cust.CustomerID = 3;

The above code will throw compiler error.

SCRUM Development Process - Agile Project Management

Here we are going to discuss about Agile Project Management with SCRUM. Nowadays most of companies started to using SCRUM development process. Its practice to follow in project/product development framework. This process is most suited to the project that requirements frequently changing and adding more enhancements.

Definition:
An iterative, incremental process for developing any product or managing any work. It produces a potentially shippable set of functionality at the end of every iteration.

Attributes of SCRUM process:
• An agile process to manage and control development work.
• A wrapper for existing engineering practices.
• A team-based approach to iteratively, incrementally develop systems and products when requirements are rapidly changing
• A process that controls the chaos of conflicting interests and needs.
• A way to improve communications and maximize co-operation.


SCRUM Process Diagram:



Detailed SCRUM Process Diagram:




Terminology:•

Product Owner

o This is the person who makes decisions about features that will be added to the project
o This person may be a customer, but can be a person in the company who acts as the customer’s representative
o This person will be the ultimate judge of a projects successful completion

SCRUM Master

o Equivalent to a project manager for a small project or a team lead on a larger project, with a size of 4 to 8 people.
o The scrum master ensures that the rules of scrum are followed
 By the development team
 By management and customers

Product Backlog

o The product backlog is generally equivalent to the requirements document in traditional projects
o Features are added to the product backlog and include the following information
 ID
 Priority
• Multiple priority scales can be used
o 1-5
o Showstopper, Major, Minor, Trivial
 Description
 Preliminary Hour Estimate
o It can also contain
 Customer/Contract information
 Requirement Owner
 Anticipated Version
 Assigned Development Team
 Assigned QA Team
 Feature Status
o Features are prioritized by the Product Owner

SPRINT

• A Sprint is a single development cycle
• Developers work as a team during a sprint to complete tasks that are defined at the beginning of the sprint
• Typical duration of 4 weeks, but is flexible
• The output of the sprint should be completed software modules that provide demonstrable capability
• The entire team is responsible for all of the tasks on the sprint
o If someone finishes their tasks early, they help others complete the remaining tasks
• The end date of the sprint is fixed. Tasks must get dropped from a sprint rather than move the end date
• Sprint Planning Meeting:
• Occurs at the beginning of sprint
• Features from the product backlog are broken down into smaller tasks and assigned to developers
• Estimates refined by developers based on implementation responsibility
• Attended by development team, product owner and the scrum master
The scrum master runs the meeting

Sprint Backlog

• The Sprint Backlog tracks the progress of an individual sprint
• All of the tasks that were defined during the sprint planning meeting are listed on the sprint backlog
• The estimates for tasks are stored and updated on this document
• Daily SCRUM:
• Daily progress meeting
o Attended by the development team
o Run by the scrum master
• Three questions answered by each team member
o What did you do yesterday?
o What is keeping you from accomplishing your tasks?
o What do you plan on doing today?
• Estimates are updated on the sprint backlog
o Number of hours worked are logged
o Hours remaining are “re-estimated”
• Information gathered at these meetings can be used to track progress of tasks


SCRUM Rules
• The dates are fixed
o If tasks are not going to be finished, they are dropped from this iteration
o Priorities are VERY important
• No Feature Creep
o Features may not be added to a sprint by the product owner after the Sprint Planning Meeting
o Exception – the burn down chart shows that all tasks will be completed early and the team agrees to take on the next task from the product backlog
o Sprints can be cancelled and restarted by the product owner if there is a high priority need
• All tasks must be TESTED to be complete
o Developers must COMPLETE the tasks
o The next sprint will start immediately after this one ends, so there will not be time for revisiting this task


Challenges

o It takes practice by the entire team
o People tend to over-commit during the Sprint Planning Meeting
o Proper estimates must include time for:
 Design
 Development
 Reviews (Design and Code)
 Unit Test Creation
 Testing
 Integration

C# 3.0 : Object Initialization

Usually object can be initialized by passing value to contructor. if we want to initialize the differnt property value in different situation, we need to create a more constructor for all the situation.

Conventional Method:

class Customer
{
#region Constructors

public Customer() {}

public Customer(long customerID)
{
CustomerID = customerID;
}

public Customer(long customerID, string name)
{
CustomerID = customerID;
Name = name;
}

#endregion


#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}

Initialization:

Customer custObj = new Customer(1);
Customer custObj1 = new Customer(1, "Pankaj");
Customer custObj2 = new Customer();
custObj2.CustomerID = 1;
custObj2.Name = "Pankaj";

C# 3.0:

But in C# 3.0, no need to create too much contructor for initializatoin. you can initialize the property without creating the constructor.

class Customer
{

#region Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion
}

Initialization:

Customer custSingle = new Customer { CustomerID = 1 };
Customer cust = new Customer { CustomerID = 1, Name = "Pankaj" };


Constructor and Initialization:

you can also call the constructor and initialize the property at the same time.

Customer custConst = new Customer(1) { CustomerID = 2, Name = "Pankaj" };


First call the constructor and then initialize the property.

So finally CustomerID value will be 2 instead of 1.