How to build Table-Valued Parameter that is used in SQL Stored Procedures
Table-Valued Parameters
Table-valued parameters have two primary components: a SQL Server type and a parameter that references that type.
Benefits:
1. Do not acquire locks for the initial population of data from a client. Facilitate bulk inserts very efficiently
2. Are Strongly Typed
3. Provide a simple programming model.
Restrictions:
1. Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
2. SQL Server does not maintain statistics on columns of table-valued parameters.
3. Once you’ve created a user-defined table types you can’t modify it. You need to drop and recreate.
4. You can’t drop a user-defined table types if it’s being referenced by another object
5. The order of columns in a Datatable need to be in the exact same order as the UDDT as no column mapping is applied.
Example:
1. Create UDT type in SQL
IF EXISTS (SELECT * FROM SYS.TYPES WHERE NAME = 'Name_UDT') DROP TYPE Name_UDT GO CREATE TYPE dbo.Name_UDT AS TABLE( LastName NVARCHAR(40) NOT NULL, FirstName NVARCHAR(40) NOT NULL ) GO
2. Create Stored Procedure that references the UDT type
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spVerifyEmployeesExist') DROP PROCEDURE spVerifyEmployeeExists GO CREATE PROCEDURE dbo.spVerifyEmployeeExists ( @EmployeeNameList dbo.Name_UDT READONLY ) AS BEGIN SELECT ENL.FirstName + ' ' + ENL.LastName AS EmployeeFullName, CASE WHEN E.empid IS NULL THEN 0 ELSE 1 END AS IsInCompany FROM @EmployeeNameList ENL LEFT JOIN HR.Employees E ON ENL.LastName = E.lastname AND ENL.FirstName = E.firstname END GO
—-Test Data—
Declare @TestData dbo.Name_UDT
INSERT INTO @TestData (FirstName, LastName)
Values(‘Sara’,'Davis’),(‘Judy’,'Liu’)
EXEC dbo.spVerifyEmployeeExists @TestData
OutPut:
Sara Davis 1
Judy Liu 0
3. Write code to build parameters
UDTType
public class EmployeeName :UDTType
{
public string FirstName { get; set; }
public string LastName { get; set; }
public override List<string> MappedColumnOrder()
{
return new List<string>{"LastName","FirstName"};
}
public override void PopulateTo(DataRow row)
{
row["FirstName"] = FirstName;
row["LastName"] = LastName;
}
}
public abstract class UDTType
{
public abstract List<string> MappedColumnOrder();
public abstract void PopulateTo(DataRow row);
};
UDTHelper
public static class UDTHelper<T> where T : UDTType
{
public static DataTable DataTable = CreateDataTable();
private static DataTable CreateDataTable()
{
var classType = typeof(T);
var defaultInstance = Activator.CreateInstance(classType);
var udtTypeInstance = (T)defaultInstance;
var mappedColumnOrder = udtTypeInstance.MappedColumnOrder();
var dataTable = new DataTable(classType.Name);
foreach (var columnName in mappedColumnOrder)
{
var propInfo = classType.GetProperty(columnName);
var column = new DataColumn(propInfo.Name, propInfo.PropertyType);
dataTable.Columns.Add(column);
}
return dataTable;
}
public static void AddToTable(DataTable dataTableToAdd, List<T> udtDataList)
{
foreach (var udtData in udtDataList)
{
var row = dataTableToAdd.NewRow();
udtData.PopulateTo(row);
dataTableToAdd.Rows.Add(row);
}
}
}
Reference:
1. Table-Valued Parameters, http://msdn.microsoft.com/en-us/library/bb510489.aspx
2. A very good overview article on TVP http://beyondrelational.com/blogs/jeffwharton/archive/2011/07/02/part-4-table-valued-parameters-tvp-s-table-what.aspx
3. A simple but clear example shows how to use .Net code to construct TVP http://blogs.staykov.net/2011/04/table-valued-parameter-procedures-with.html
Reading Note of Professional ASP.Net Design Patterns (1) – Adapter Pattern
Example:
public class ProductService
{
private ProductRepository _productRepository;
public ProductService()
{
_productRepository = new ProductRepository();
/*1.This shows that the ProductService depends on the ProductRepository class.
It is also responsible for creating the concrete implementation, which violate Single Responsibility Principle.
Currently it is impossible to test the code without a real ProductRepository class connecting to a real database. */
}
public IList<Product> GetAllProductsIn(int categoryId)
{
IList<Product> products;
string storageKey = string.Format(
“products_in_category_id_{0}”, categoryId);
products = (List<Product>)HttpContext.Current.Cache.Get(storageKey);
/*2. Another problem related to testing is the dependency
on the HTTP context for use in the caching of the products.
It is hard to test code that is so tightly coupled to HTTP context.*/
/*3. In its current state, using a different cache storage provider such as Velocity or
Memcached would require altering of the ProductService class and any other class that uses caching.
Velocity and Memcached are both distributed memory object caching systems that can be used in place of
ASP.NET’s default caching mechanism.*/
if (products == null)
{
products = _productRepository.GetAllProductsIn(categoryId);
HttpContext.Current.Cache.Insert(storageKey, products);
}
return products;
}
How to solve the problem?
1. Use dependency injection to solve problem 1.
public class ProductService
{
private IProductRepository _productRepository;
<strong>public ProductService(IProductRepository productRepository)</strong>
<strong>{</strong>
<strong>_productRepository = productRepository;</strong>
<strong>}</strong>
public IList<Product> GetAllProductsIn(int categoryId)
{
…
}
}
Dependency Injection enables a substitute to be passed to the ProductService class during testing, which enables you to test the ProductService class in isolation. By removing the responsibility of creating ProductRepository class , you are ensuring that the ProductService class adheres to the Single Responsibility principle: it is now only concerned with the coordinating of retrieving data from the cache or repository and not for creating the concrete IProductRepository implementation.
The last thing you need to do is sort out the dependency on the HTTP Context for your caching
requirements. For this we need employ the Adapter design pattern.
The Adapter pattern Converts the interface of a class into another interface clients expect; its sole purpose is to let classes with incompatible interfaces work together.
Final:
public class ProductService
{
private IProductRepository _productRepository;
<strong>private ICacheStorage _cacheStorage;</strong>
<strong>public ProductService(IProductRepository productRepository,</strong>
<strong>ICacheStorage cacheStorage)</strong>
<strong>{</strong>
_productRepository = productRepository;
<strong>_cacheStorage = cacheStorage;</strong>
<strong>}</strong>
public IList<Product> GetAllProductsIn(int categoryId)
{
IList<Product> products;
string storageKey = string.Format(
“products_in_category_id_{0}”, categoryId);
<strong>products = _cacheStorage.Retrieve<List<Product>>(storageKey);</strong>
if (products == null)
{
products = _productRepository.GetAllProductsIn(categoryId);
<strong>_cacheStorage.Store(storageKey, products);</strong>
}
return products;
}
}
Design Pattern 6: The decorator pattern
One of the OO principles is open for extension closed for modification.
The goal is to allow classes to be easily extended to incorporate new behavior without modifying existing code, which means that the designs are resilient to change and flexible enough to take on new functionality to meet changing requirements. If not changing existing code, the chances of introducing bugs or causing unintended side-affects in pre-existing code are much reduced.
However applying the open-closed principle everywhere is wasteful, unnecessary, and can lead to complex, hard to understand code. Therefore concentrate on the areas that are most likely to change in your design and apply the principles there.
One pattern allows this open-closed principle possible is the decorator pattern.
The Decorator Pattern: attaches additional responsibility to an object dynamically. Decorators provide a flexible alternative to subclassing for extending functionality. Subclassing adds behavior at compile time, and the change affects all instances of the original class; decorating can provide new behavior at run-time for individual objects. Thus, decorator solves the problem of “Class Explosion”.
Guidelines:
You have:
• An existing component class that may be unavailable for subclassing
You want to:
• Attach additional state or behavior to an object dynamically.
• Make changes to some objects of a class without affecting others.
• Avoid subclassing because too many classes could result.
Decorators are typically created by using other patterns like Factory or Builder to prevent errors described in the following: For example I have a darkroast coffee with Mocha, soy, whip decorators, it is easy to make coding errors that end up with a reference to soy decorator other than whip, which means it does not include whip.
Motivation from real world (from Wiki)
As an example, consider a window in a windowing system. To allow scrolling of the window’s contents, we may wish to add horizontal or vertical scrollbars to it, as appropriate. Assume windows are represented by instances of the Window class, and assume this class has no functionality for adding scrollbars. We could create a subclass ScrollingWindow that provides them, or we could create a ScrollingWindowDecorator that adds this functionality to existing Window objects. At this point, either solution would be fine.
Now let’s assume we also desire the ability to add borders to our windows. Again, our original Window class has no support. The ScrollingWindow subclass now poses a problem, because it has effectively created a new kind of window. If we wish to add border support to all windows, we must create subclasses WindowWithBorder and ScrollingWindowWithBorder. Obviously, this problem gets worse with every new feature to be added. For the decorator solution, we simply create a new BorderedWindowDecorator—at runtime, we can decorate existing windows with the ScrollingWindowDecorator or the BorderedWindowDecorator or both, as we see fit.
Real world decorator examples
1. I/O of .Net
These feed off another stream, transforming the data in some way, such as DeflatedStream or CryptoStream. They liberate backing store stream, such as FileStream or Network Stream(Base) from needing to implement such features as compression and encryption themselves.
2. Graphics world
System,Windows.Controls (Base)
Border(decorator)
References:
1.Slides, http://www.slideshare.net/eprafulla/01-introduction-and-decorator-pattern
2.Head First Design Patterns
Design Patter 5: Strategy Pattern
The first chapter of the book “Head First design patterns” talks about the strategy pattern, which is my favorite pattern.
Why? Because in my opinion, this is the most fundamental pattern that allows me to see the power of polymorphism, see how we could achieve dependency injection using this pattern, see why there is a principle saying “Favor Composition over Inheritance”.
Strategy pattern defines a family of algorithms, encapsulates each one, and makes them interchangeable. Strategy lets the algorithm vary independently from clients that use it.
The example used in the book is to implement many different kinds of ducks; they all quack and fly but behave differently from each other.
I think of inheritance at the first glance, thinking that we should have a base class implements how a duck quack and fly, thus way we might able to reuse code and save some time during development.
This is totally wrong. Actually, think about this question, “Is more time spent on code before or after development is complete?” The answer is “AFTER”. The effort put in Maintainability and Extensibility should be above than reusability.
If we have 50 different kinds of ducks, override its fly behavior in each derived duck class would be a nightmare! Think about abstracting out these fly behavior and quack behavior instead!
Duck
{
IFlyBehavior _flybehavior;
IQuackBehavior _quackbehavior;
Public class Duck(IFlyBehavior fly, IQuackBehavior quack){ initialize field}
}
Now we could swap in and out different fly and quack behaviors for different ducks and isn’t this a way to inject the different behavior dependencies in via constructor?
C # Generics VS C++ Templates
I have developed in C# for a while now, but I don’t really understand what C# generic is. I used to program in C++ and know basics about Templates. I think if I compare these two, it might help my understanding for generics in C#.
C# Generics and C++ templates are both language features that provide support for parameterized types. There are two major differences between C# generics and C++ Templates.
1. C# generic type substitutions are performed at runtime and generic type information is thereby preserved for instantiated objects. C++ does it at compile time, or perhaps at link time. But regardless the instantiation happens before the program runs.
2. Error checking. Because the type that is used with the generic is not known at compile time, the compiler needs additional information about the type that will be used with a generic class. This is done through constraints, which allow the author to constrain the types that can be used with a generic type to provide strong type checking when you compile the generic type.
For an unconstrained type parameter, like List, the only methods available on values of type T are those that are found on type object, because those are the only methods that we can generally guarantee will exist.
And also C# does constraint by type not by signature. The type must either extend a class or implement interfaces.
In C++, templates are not constrained or constraints are implied. So it is easy for the template designer because they don’t have to care about stating the valid operations in their template interface. They put the burden on the user of their template – so the user has to make sure he fulfills all those requirements. Often it happens that the user tries seemingly valid operations but fails, with the compiler giving the user hundreds of lines of error messages about some invalid syntax or not found names. Because the compiler can’t know what constraint in particular was violated in the first place, it lists all parts of code paths ever involved around the faulty place and all not even important details, and the user will have to crawl through the horrible error message text.
So in a sense, C++ templates are sort of weak typing mechanism. C# adds constraints on top of it, making it from weak typing to strong typing.
References:
1. MSDN, Differences between C++ Templates and C# Generics, http://msdn.microsoft.com/en-us/library/c6cyy67b.aspx
2. StackOverflow, What are differences between generics in C# and Java..and Templates in C++, http://stackoverflow.com/questions/31693/what-are-the-differences-between-generics-in-c-and-java-and-templates-in-c
3. Eric Gunnerson, How do C# generics compare to C++ templates, http://blogs.msdn.com/b/csharpfaq/archive/2004/03/12/88913.aspx
4. Generics in C#, Java, and C++, http://www.artima.com/intv/generics2.html
5. C# FAQ, http://www.andymcm.com/dotnetfaq.htm
Design Patterns 4: The Adapter Pattern
Adapter: Often referred to as the wrapper pattern or simply a wrapper. It wrap a legacy object that provides an incompatible interface with an object that supports the desired interface
It provides a new interface for the client to use.
Reference:
Install latest skype 2.1 beta failed on Ubuntu 8.04
A lib dependency is missing: libasound2 when installing latest skype version 2.1 Beta on my Ubuntu 8.04. even after I reinstalled libasound2 and libasound-plugins, it gives me the same error.
Did quite a bit search, seems that in 2.1 beta release, skype added some new sound related lib which is not supported in Ubuntu 8.04.
The problem is skype doesn’t provided any older version on their official download site, it forces you to download the latest version (which doesn’t work on certain distro/version of Linux btw).
Did another round of search, finally, in this post I found the link for skype-debian_2.0.0.68-1_i386.deb
http://skype-old-version.blogspot.com/2010/03/skype-old-version.html
This package works fine on my Ubuntu 8.04 (hardy).
Thanks for the author of the post, and if anyone can’t find the link, leave your email in the comment, I will send you a copy.
Json Serializer and Deserializer
Json stands for JavaScript Object Notation. Json is a lightweight syntax for representing data. JASON’s elegance comes from the fact that it’s a subset of the JavaScript language itself.
The typical json format {object} [array]
{
“class”: “Person”,
“name”: “William Shakespeare”,
“birthday”: -12802392000000,
“nickname”: “Bill”
“phoneNumbers”: [
{
"class": "Phone",
"name": "cell",
"number": "555-123-4567"
},
{
"class": "Phone",
"name": "home",
"number": "555-987-6543"
},
{
"class": "Phone",
"name": "work",
"number": "555-678-3542"
}
]
}
You can follow the JSON format and easily convert objects to JSON string objects.
// Create a new instance of Person object
Person p = new Person(5, “Bilal Haidar”);
// Construct the JSON string object
StringBuilder sb = new StringBuilder();
sb.Append(“{“);
sb.AppendFormat(“\”{0}\”: \”{1}\”", p.ID, p.Name);
sb.Append(“}”);
Response.Write(sb.ToString());
Then on the client side, you just need to parse the JSON string object into a real JSON object to be accessed as a normal object.
Reference:
- Introducing JSON, describes the syntax of JSON, http://json.org/
- JSON serialization and Deserialization in ASP.Net, http://www.codedigest.com/CodeDigest/112-JSON-Serialization-and-Deserialization-in-ASP-Net.aspx
- An ActionResult to return JSON from ASP.NET MVC to the browser using Json.NET, http://code.google.com/p/sharp-architecture/source/browse/trunk/src/SharpArch/SharpArch.Web/JsonNet/JsonNetResult.cs?spec=svn370&r=266
- API Documentation, http://james.newtonking.com/projects/json/help/html/N_Newtonsoft_Json.htm
Design Pattern 3: the Command Pattern
The command pattern encapsulates a request as an object, thereby letting you parameterize other objects with different requests, queue or log requests, and support undoable operations.
When you need to decouple an object making requests[Invoker of a request] from the objects that know how to perform the requests[Receiver of the request], use the command pattern.
The command object contains both receiver and actions. The command object provides one method, execute(), which encapsulates the actions and can be called to invoke the actions on the Receiver.
The client is responsible for creating a ConcreateCommand and setting its Receiver.
The Invoker holds a command and at some point asks the command to carry out a request by calling its execute() method.
The Receiver knows how to perform the work needed to carry out the request. Any class can act as a Receiver.
Reference:
- Definition, participants, and sample code in C#, http://www.dofactory.com/Patterns/PatternCommand.aspx#_self1
How to use the Selenium-RC to test your first web application in Windows
Tools needed:
Java run time environment: Jre6 http://www.java.com/en/download/manual.jsp
Selenium-RC, http://seleniumhq.org/download/
Nunit
- Install Jre6 and add java path to the environment, echo %path% to verify
- Install the selenium-rc server.
- Go to the directory where selenium-rc’s server is located and run the following, “java –jar selenium-server.jar”
- If an “Unrecognized windows socket error exception” is thrown, using “netstats -a” to verify if port 4444(default selenium socket port) is already in use. More details, please refer to http://serverfault.com/questions/124022/selenium-server-wont-start
- Creating a batch (.bat) file to execute step 2.
- Open visual studio(or other preferred IDE) to create a new test project, select new a class library.
- Add references to the following DLLs: nmock.dll, nunit.core.dll, nunit. framework.dll, ThoughtWorks.Selenium.Core.dll, ThoughtWorks.Selenium. IntegrationTests.dll and ThoughtWorks.Selenium.UnitTests.dll. See http://seleniumhq.org/docs/appendix_installing_dotnet_driver_client.html#configuring-selenium-rc-net-reference
- Write the selenium test in a .net language, or export a script from selenium IDE to a C# and copy the code into the class file just created.
- Open Nunit. Open the test project.
- Run the test from the visual studio Nunit GUI or from the command line.
Reference:
- Selenium-RC document, http://seleniumhq.org/docs/05_selenium_rc.html
- Selenium RC in C# using Nunit, an end to end example, http://thetestingblog.com/2009/09/10/selenium-rc-in-c-using-nunit-an-end-to-end-example/
