eXtreme Programming

Fixing SSDT unresolved reference to object error

2013-03-21T09:34:00.001Z

The SQL Server Data Tools (SSDT) for Visual Studio 2012 are a great set of tools for database development, but as any tool they have some undesirable "features".
One of those "features" is the "unresolved reference to object" error.
The "unresolved reference to object" in a stored procedure is a warning, but for the function it is an error. The error fails the build and consequently the the schema compare and update of the database.
The error normally is caused by a query that references an object on other database.

One example of the error is:
Error 190 SQL71561: Function: [dbo].[myFunction] has an unresolved reference to object [myDatabase].[dbo].[Product]
In the example the function [myFunction] is using the table [Product] from the database [myDatabase], that isn't the same database being managed by the database project of [myFunction].

A possible solution is to add a database reference to the database that has the missing object.
The reference needs a Data-tier Application (dacpac file) that can be easily generated on the solution with the database project that has the missing object. Press the right mouse button over the database project and selected Snapshot Project. The dacpac file is created on the Snapshots folder.
The file should then be copied to a common folder for re-usability.

In the project with the error press the right mouse button over the References and selected Add Database Reference.
The Add Database Reference dialog appears:
1) Select the dacpac file
2) Select the database location. The most common option is "Different database, same server"
3) Confirm that the Database name field is as expected
4) Clear the "Database variable" field in the dialog. If this field has a value the queries must use this variable and not the database name.

Look at the "Example usage" text and verify that it looks as expected. Click "OK" to add the reference and that should take care of the 'unresolved reference' errors.

The database reference resolves the schema comparison issue, but trying to build the project produced the following error:
Error 408 SQL00208: Invalid object name 'db.schema.table'.

To fix this error, go to the project properties and uncheck "Enable extended Transact-SQL verification for common objects".

Dropdown list autopostback not working when validators fire

2013-02-15T14:37:00.001Z

The Dropdown server control of the webforms can automatically post back by setting the autopostback property to true and setting the change event.
It works as expected, except when you have validators on the form with client-side validation active. The post back causes the validators to fire and prevent the autopostback if the form is invalid.

If there is the need to post back even if the form is invalid, the Microsoft documentation states that the CausesValidation property must be set to false.
However the issue still persists and the client side onchange event must execute the code Page_BlockSubmit=false
Page_BlockSubmit controls whether the form should be submitted or not.
Here is a full example:

<asp:dropdownlist autopostback="true" 
                 causesvalidation="false"
                 id="myDropDown" 
                 onchange="Page_BlockSubmit = false;"
                 onselectedindexchanged="myDropDown_SelectedIndexChanged"
                 runat="server">  

</asp:dropdownlist>

Note that to apply the best practices the client side event onchange should be set on the code behind.

myDropDown.Attributes.Add("onchange", "myDropDownOnchange();");

function myDropDownOnchange() {
    Page_BlockSubmit=false;
}

Query Optimization and the SQL Server Cache

2013-02-07T19:02:00.003Z

Query optimization is a very important task to assure that the server resources are not heavily consumed by a bad performing query.
One of the important tasks when optimizing a query is to clean the SQL Server Cache so that the results are not influenced by the caching mechanism.

The T-SQL commands, that clean the Sql Server cache, will cause severe performance problems in a production environment, so they are to be used for testing purposes on a development or staging environment only.
They can be executed in a production server in special and controlled conditions. An example is to remove an individual plan of a query that has a bad performance because of a bad plan cache.

This article explains the main concepts involved and then the possible options to Clean the Sql Server Cache.

Execution Plan

The execution plan is the result of the query optimizer attempt to calculate the most efficient way to process the request represented by the SQL query statement.
Sql Server has to build an execution plan for each Transact-SQL (T-SQL) statement it has to execute.
The execution plan defines how the T-SQL statement can be executed by Sql Server to produce the desired results.

The execution plan is built based on several considerations:
1) The tables it needes to join
2) The Indexes to use
3) The sub-queries it has to execute
4) How aggregations of Group By are calculated
5) The estimated cost and load the operations place on the system
6) other even more complex considerations

The execution plan is also known as query plan.

As can be easily understandable, SQL has to put a lot of work to build a Execution Plan, so it caches the execution plan in memory to avoid having to do the same work over and over again.
Sql Server uses the Plan Cache to reuse plans. In this way, SQL Server can avoid the overhead of calculating the execution plan for each T-SQL statement and in this way speed up the execution of the queries.

Plan Cache

The plan cache is used by Sql Server to store the Execution Plans of the queries it has run.
The plan cache allows Sql Server to reuse Execution Plans for subsequent requests. It stores plans and it's associated information. There are metrics about the number of times a query was executed and the resources it used for example.

The Plan cache can be flush totally in some situations, the principal are:
1) Sql Server Service Restarts
2) Statistics of an object changing
3) Restores of a database
4) Executing T-SQL commands to clean it
5) Insufficient Memory on the server, causing memory pressure
6) Detaching a database
7) Some T-SQL Commands like for example RECONFIGURE, ALTER DATABASE ... MODIFY FILEGROUP or modifying a collation using ALTER DATABASE … COLLATE command

The Plan cache of a database can also be flushed totally in some situations, where the principal are:
1) Some operations like for example DROP DATABASE or ALTER DATABASE … MODIFY NAME
2) If the database auto-closes
3) The database is set online or offline

Recompilations

Sql Server checks for correctness and for the optimality of a query plan before it executes it.
If one of the checks fails, the statement is compiled again and new query plan is produced.
These compilations are named as recompilations.

The recompilations are necessary to:
1) Ensure statement correctness
2) Obtain potentially better query execution plans as data changes

The recompilations can also have the side effect that they can slow down executions considerably.
In this case it is necessary to reduce the number of recompilations.

Parameter sniffing

Parameter sniffing is a complex topic, but according to Microsoft:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

The query optimizer uses the parameters passed to the statements for performing estimates when evaluating possible execution plan options. The final effect is that the plan is optimized for those specific parameter values.
This feature allows more efficient stored procedure execution plans in most cases. There is however an assumption that the parameter values are "typical".
The main problem with this approach is when a parameter is atypical. The asymmetry usually is in the distribution of the data values or how the value is distributed across where the data is a non-uniform distribution, this is called skewed data.
One example of skewed data is when there is a table with two million records and a column of type BIT. That column has the value 0 for only 1000 records and all the others have the value 0.
The LIKE clause is also very prune to this issue.

Parameter sniffing affects the performance of a query since the execution plan that is generated by the query optimizer depends on parameter sniffing.

Parameter values are sniffed for:
1) Stored Procedures
2) Queries executed using sp_executesql
3) Prepared queries

The following statements help to control the parameter sniffing performance problems:
1) WITH RECOMPILE - the stored procedure
2) Dummy local variables that are set equal to a parameter
3) OPTION(RECOMPILE) - This query hint is used to extended the behavior to queries (SELECT, INSERT, UPDATE, or DELETE). In this case, both the parameter values and the current values of local variables are sniffed.
4) OPTION(OPTIMIZE FOR ())

The explanation of these options are out of the scope of this article, but you can search for a detailed explanation if you wish to learn more.

Clean the buffers

Sql Sever buffers the data before it is written to disk, this can cause dirty pages.
To guarantee that all these dirty pages, for the current database, are be written to disk and buffers are clean use the CHECKPOINT statement.
CHECKPOINT forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

The CHECKPOINT is to guarantee that if you performed an operation or if you are in a collaborative environment that you are also testing you don't end up with dirty pages in the buffers.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. It serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

Example:
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO

Clean the Plan Cache

There are several options to clean the plan cache. The next sections are going to explain the principal options, but don't forget to always Clean the Buffers first.

Clean the entire Plan Cache

The more drastic method is to clean the entire Sql Server plan cache, using the statement FREEPROCCACHE.
This will free the entire plan cache and causes the recompilation of subsequent ad-hoc SQL statements or Stored Procedures.

Usage:
DBCC FREEPROCCACHE

Clean a Database Plan Cache

A less drastic option, is to clear only the entire plan cache for a specific database and not the full Sql Server Plan Cache.

Usage:
DECLARE @dbId INTEGER
SELECT @dbId = dbid FROM master.dbo.sysdatabases WHERE name = ‘myDatabase’
DBCC FLUSHPROCINDB (@dbId)

Where myDatabase is database to clear the entire plan cache.
The @dbId parameter is the database the number (database ID) to be affected by the FLUSHPROCINDB command.

Clean a Compiled Plan

In some situations it is useful to clean only a specific compiled execution plan for an Stored Procedure or a Ad-Hoc query.
One example is when some queries are executed in production for testing or to Extract, Transform, Load (ETL) data and should be removed form the Plan Cache so that they don't occupy cache space.

Usage:
SELECT [text], CachedPlans.size_in_bytes, CachedPlans.plan_handle, CachedPlans.objtype, CachedPlans.usecounts
FROM sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE CachedPlans.cacheobjtype = N'Compiled Plan'

This query returns all the compiled plans.
The column Text identifies the T-SQL statement executed (Stored Procedure: proc or Ad-Hoc Query: Adhoc for example) and the plan_handle can be passed to FREEPROCCACHE to remove it:

DBCC FREEPROCCACHE (plan_handle)

Clean Stored procedure or Trigger Plan cache

The sp_recompile statement causes the recompilation of stored procedures and triggers the next time that they are run.It drops the existing plan from the procedure cache.

Usage:
EXEC sp_recompile N'myObject'';

Where myObject can be a stored procedure, trigger, table, or view in the current database.
If is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time that it is executed.
If object is the name of a table or view, all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are executed.

Undoing a checkout that is from another user

2012-09-12T12:19:00.000+01:00

A common task in TFS isto to undo someone else pending change for several reasons.
The TFS client doesn't have this option in the GUI, so we have to go to the command line as an administrator of TFS and enter the command:

tf undo /workspace:UserWorkspace;Username $/Project/file.cs /s:http://yourtfsserver:8080/tfs

Where:
- UserWorkspace is the workspace of the user to undo pending changes
- Username is the user name of the user we wanto to undo the checkout
- $/Project/file.cs is the TFS path to the file to undo the checkout
- /s:http://yourtfsserver:8080/tfs is the connection to the TFS Server

Visual Studio 2012: Change default SQL Server Instace

2012-08-30T13:51:00.002+01:00

Visual Studio 2012 Database Project / Sql Server data tools (SSD ) installs and uses a SQL Server Express version named LocalDB for it's operations by default.

LocalDB is created specifically for developers and is a fast, lightweight SQL Server instance that requires minimal management or configuration.

This scenario is great is you don't have a SQL Server instance to develop.

When a object is open, like for example a store procedure, and you execute the code Visual Studio 2012 will connect to LocalDB and there is no option to connect to other database. The solution is to change the default SQL Server Instance used by Visual Studio 2012:

1) On the menu, choose Tools-> Options -> Database tools
2) On the data connections enter the SQL server Instance name that you want to use
3) Press OK

Close all the database project files you have open on Visual Studio and the configuration is done.

List of an anonymous type

2012-08-29T12:14:00.001+01:00

Anonymous types allows us to easily encapsulate a set of read-only properties of single object without the need to define a type first.
The type name is generated by the compiler and each property type is inferred by the compiler.

Anonymous types are useful when using LINQ for example.

A List of an anonymous type is something that at the first thought seems rather impossible, but for everything there is always a solution:

1) First define the anonymous type:

 var person = new { Id = 1, Name = "John Doe" };

2) Then create the list of the anonymous type

var people = (new[] { person }).ToList();

4) Add more items to the list easly

people.add(new { Id = 2, Name = "Peter Doe" });

Visual Studio 2012 does not support SSAS, SSIS or SSRS

2012-08-21T08:40:00.001+01:00

Visual Studio 2012 and SQL Server 2012 have been launched by Microsoft recently.

Having that into account I was expecting to have full integration between the two products, but I discovered the hard way that Visual Studio 2012 does not have any support for the Integration Services (SSIS), Analisys Services (SSAS) or even the Reporting Services (SSRS) projects.

The SQL Server 2012 data tools (SSDT) offer an integrated environment within Visual Studio 2010, but there is no support for Business Intelligence on Visual Studio 2012!

The SQL Server 2008 had the same issue with Visual Studio 2010.

So get ready to have Visual Studio 2012 and Visual Studio 2010 side by side on the same development machine.

Some solutions to repair the Visual Studio 2012 UI

2012-08-20T15:14:00.001+01:00

The new visual stuidio 2012 UI is not consensual ... so here are some tips to repair the Visual Studio 2012 UI:
1) Prevent Visual Studio 2012 ALL CAPS Menus

Open your registry editor and create the following registry key and value: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\General\SuppressUppercaseConversion

REG_DWORD value: 1

Source

2) Create and share Visual Studio color schemes

The site http://studiostyl.es/ has some Visual Studio color schemes that you can use, if the default are not as you like.

Migration from SQL server 2008 to 2012

2012-08-06T18:53:00.000+01:00

The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.
An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.
There are two approaches when upgrading:

In-Place: The SQL Server is upgraded where it is currently installed
Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.

The approach I am going to take is the second, since I have a new hardware and I am going to do a fresh SQL Server 2012 installation.

The migration from SQL server 2008 to 2012 must be well prepared and tested before going to production.

I have to migrate several databases, the integration services (SSIS) packages, analisys services (SSAS) packages and reporting services (SSRS) reports.

Requirements

This article assumes you already know the SQL Server database engine, integration services, analisys services, reporting services and it's tools.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.

Databases

The database engine isn't a complete rewrite. This means that we can expect a deep compability level.

There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read it here to ensure that you ate not using a feature that breaks the SQL 2012 compatibility.
After reading the article and fixing any issues, the upgrade of the databases can be implemented in the following steps:

Create the databases and configure them on the SQL 2012 Server. If you don't have any special requirements skip this step, since when you perform the restore the databases are created automatically.
Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
Backup the SQL Server 2008 databases and restored them in SQL 2012.
Change each database compatibility level from 2008 to 2012. This action is important since it allows the usage of the new SQL Server 2012 features.
The following script can be useful you you have several databases to migrate:

USE [master]
GO
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110

where [mydatabase] is the database to change the compatibility level

or goto the database properties and on the options select the Compatibility Level 110.
Check the logical and physical integrity of all the objects in the upgraded databases:

DBCC CHECKDB([myDatabase]) WITH NO_INFOMSGS
where [mydatabase] is the database to run the integrity checks
NO_INFOMSGS option suppresses all informational messages.

If If DBCC printed any error messages you must fix them so that your database will work correctly.

Don't forget to create the databases maintenance plans.

Integration Services (SSIS)

In SQL Server 2012 the SSIS Package format changed and the specifications are now Open Source.
The Business Intelligence Development Studio (BIDs) is replaced by the SQL Server Data Tools (SSDT).
SQL Server 2012 SSIS offers a wizard for upgrading most of the solution components, but a few settings may be needed to be changed manually.
The wizard appears when you open a SQL Server 2008 package on the SQL Server data tools.
Microsoft has a white Paper that gives you 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012. You can read it here.

SSIS 2012 supports two deployment models:

Package deployment model: In this model the the unit of deployment is the package. This is the model used in previous versions of SSIS and is the default deployment model for upgraded packages.
Project deployment model: . The unit of deployment is the project for this model. This model is new in SQL Server 2012 and provides additional package deployment and management features such as parameters and the Integration Services catalog

I have decided to use the Package deployment model for now, since it is the one the gives more compatibility with the SSIS 2008 model. When I have more time for testing and development I am going to convert to the Project deployment model. There is an wizard the performs this task and that is explained in the white paper I mentioned previously.

The migration of the integration services (SSIS) packages

Open the solution (sln) file with the packages to migrate
The Visual Studio Conversion wizard appears. It is very simple and after a few next's pressed, the Package Management options appear.
In the Package Management options select validate upgraded packages, so that the packages are validated and only the ones that pass validation are saved.
Disable Ignore configurations, so that the configurations are validated during the upgrade process.
The wizard ends the conversion and you can close it.
Test each package and verify that it is working as expected.

If there is a conversion error by the wizard, when you open the package in Visual Studio it is immediately converted. This methodology allows that you can easly control the errors and correct them.

Analisys Services (SSAS)

The SSAS 2012 has a great deal of changes. The main new features are:

Business Intelligence Semantic Model
Tabular model
PowerPivot for Excel and Sharepoint
SQL Server Data Tools (SSDT)
Programmability with support for the new features

The options for migrating are:

Use only the multidimensional model
Convert to the tabular model
Use the multidimensional model for existing cubes and use the tabular model for new developments
Use the tabular model or the multidimensional model depending on the project requirements

The conversion from the multidimensional model to the other models isn't supported by Microsoft at the date this article was written.
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:

The existing cubes can be migrated to the SSAS 2012 multidimensional model, without any modifications.
The existing reports and client tools will work without any problems
The model is more mature and supports much higher data volumes
The team has knowledge of this model and can continue the development without any significant changes

In the future I pretend to explore the new models, but for now the mature multidimensional model is the best option.

The analysis services migration, with the selected approach, can be performed in the following simple steps :

Open the solution (sln) file with the SSAS databases to migrate
The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish.
Terminated the wizard and let it execute
Deploy and process the SSAS database
Test the SSAS database and confirm that everything is working as expected

The only issued I faced was that after processing I got the error:

- Errors in the back-end database access module. The provider 'SQLNCLI10.1' is not registered.
- The following system error occurred: Class not registered

This error hints that there is a problem with the Data Sources connection string.
When I tried to open a data source the in project and pressed the edit button to edit the connection string, I got the error:

"The specified provider is not supported. Please choose different provider in connection manager."

The SQL Server 2008 Native Client is not installed in the Sql Server 2012 server, so I changed the connection string provider to the native client 11.0 and the issue was fixed.

Another option, if strictly necessary, is to Download and install the SQL Server 2008 SQL Native Client or the SQL Server 2005 SQL Native Client, depending on the connection string provider you want to use.

After his issue was fixed the processing occurred correctly and smoothly.

Reporting services (SSRS)

The SQL Server 2012 Reporting Services (SSRS) has two processing modes:
1) SSRS 2012 report processor. A report that is successfully converted to SSRS 2012 format is executed in this mode and can use the new SSRS features.
2) Backward-compatibility mode processor. A report that cannot be converted to SSRS 2012 is processed in backward-compatibility mode and the new features are not available, but the report is still rendered.
You can find more information here.

This approach by Microsoft gives a high degree of compatibility and I don't expect to have any issues in the migration.
The reporting services migration steps are:

Open the solution (sln) file with the reports to migrate
The Visual Studio Conversion wizard appears. The wizard doesn't have any options, so press Next and then Finish.
An information message may appear asking if you want to upgrade the report server project to the latest version. Press Yes
Let the wizard execute.
Open each data source and test the connection string. If there is an error fix it.
Deploy the reports
Test the reports and confirm that everything is working as expected

MVC jqGrid advanced scenario

2012-07-24T14:51:00.000+01:00

The MvcJqGrid is an Html Helper that eases greatly the implementation of the jqGrid in MVC 3 with the Razor view engine or MVC WebForms.
In my previous article (see here) I have explained a simple scenario to use the MvcJqGrid.

In a more advanced scenario, the requested type preferred should be POST, since the default is GET. The SetRequestType method allows the defintion of the request type.
The previous example uses an array that must return the values in the same order as the column definitions. A more generic solution is to return a json object with named properties. The MvcJqGrid supports this scenario by the SetJsonReader method.
The SetJsonReader method is used to configure the grid jsonReader so that the json data doesn't have to match the column model (ColModel) order of jqGrid.
Another feature that is advanced, is the capability to search in each desired column, using different search types.
The SetSearchType sets the seach type for the column. The seach type can be:

Input (Default)
Select. The SetSearchTerms method receives the collection of strings that define the select options.
Datepicker. The SetSearchDateFormat method allow the definition of the date format.

To enable the toolbar searching SetSearchToolbar must be set to true.
The SetSearchOnEnter is used to define when the search is executed:

true : the search is executed when the user presses enter
false: the search is executed after the user stops typing

The SetRowList creates a dropdownlist in the pager of the grid with the specified number of rows per page.

Now we can create an example using the customer class.
The view:


@(Html.Grid("customerGrid")
    .SetRequestType(RequestType.Post)
    .SetJsonReader(new MvcJqGrid.DataReaders.JsonReader { Id="id", RepeatItems = false})
    .SetCaption("Customers")
    .AddColumn(new Column("FirstName").SetWidth(100).SetLabel("First Name"))
    .AddColumn(new Column("LastName").SetWidth(100).SetLabel("Last Name"))
    .AddColumn(new Column("CountryName").SetLabel("Country")
                          .SetSearchType(Searchtype.Select)
                          .SetSearchTerms((string[])ViewBag.Countries))
    .AddColumn(new Column("Phone").SetWidth(100))
    .AddColumn(new Column("BirthDate").SetWidth(80).SetSearchType(Searchtype.Datepicker)
                          .SetSearchDateFormat("yy-mm-dd"))
    .AddColumn(new Column(" ").SetSearch(false).SetCustomFormatter("buttonize")
                                    .SetWidth(25)
                                    .SetAlign(Align.Right))
    .SetUrl(Url.Action("Search", "Customer"))
    .SetAutoWidth(true)
    .SetRowNum(10)
    .SetRowList(new[] { 10, 15, 20, 50 })
    .SetViewRecords(true)
    .SetPager("pager")
    .SetSearchToolbar(true).SetSearchOnEnter(false)     
    )

The SetCustomFormatter was explained previously and it allows to format the content of the column using a javascript function.
The controller that returns json with named properties:

public class CustomerController : Controller
{
    public ActionResult Index()
    {
        var countries = new CountryRepository().Search();
        ViewBag.Countries = (from c in countries select c.Name).ToArray();
        return View();
    }

    public JsonResult Search(GridSettings gridSettings)
    {
        List<CustomerSearchResult> customers = null;
        int totalRecords;
        CustomerRepository customerRepository = new CustomerRepository();
            
        CustomerSeachFilter filter = new CustomerSeachFilter();
        if (gridSettings.IsSearch)
        {
            filter.FirstName = gridSettings.Where.rules.Any(r => r.field == "FirstName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "FirstName").data : string.Empty;
            filter.LastName = gridSettings.Where.rules.Any(r => r.field == "LastName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "LastName").data : string.Empty;
            filter.CountryName = gridSettings.Where.rules.Any(r => r.field == "CountryName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "CountryName").data : string.Empty;
            filter.Phone = gridSettings.Where.rules.Any(r => r.field == "Phone") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "Phone").data : string.Empty;
            filter.BirthDate = gridSettings.Where.rules.Any(r => r.field == "BirthDate") ? 
            filter.BirthDate = gridSettings.Where.rules.Any(r => r.field == "BirthDate") ? 
                    DateTime.ParseExact(gridSettings.Where.rules.FirstOrDefault(r => r.field == "BirthDate").data, 
                                        "yyyy-MM-dd", null) : DateTime.MinValue;
        }

        customers = customerRepository.Search(filter, 
                                              gridSettings.SortColumn, 
                                              gridSettings.SortOrder, 
                                              gridSettings.PageSize, 
                                              gridSettings.PageIndex, 
                                              out totalRecords);

        var jsonData = new
        {
            total = totalRecords / gridSettings.PageSize + 1,
            page = gridSettings.PageIndex,
            records = totalRecords,
            rows = (
                from c in customers
                select new
                {
                    id = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName,
                    BirthDate = c.BirthDate.ToString("yyyy-MM-dd"),
                    CountryName = c.CountryName,
                    EmailAddress = c.EmailAddress,
                    Phone = c.Phone,
                })
        };
        return Json(jsonData);
    }
}

The MvcJqGrid search feature also allows the configuration of the seach:

SetSortName - Defines the default seach field for the grid.
SetSortOrder - Defines the default sort order, using the SortOrder enumerator for the grid.
SetDefaultSearchValue - Sets the default search value for a specific column.

MVC Data Annotation Validators - The Solution

2012-07-20T14:15:00.000+01:00

This "Problem - Solution" is a two series article . So this is the second part: The Solution.
The first part explains the problems facing the implementation of the MVC Data Annotation Validators in a Service Oriented Architecture (SOA) with clear separation of the layers. You can read it here

The solution I found is to use Fluent Validation framework with an Inversion of Control container (IoC) to instantiate my validators.

The Fluent Validation framework is a validation engine that can be used in several scenarios. In this specific solution, I will be focusing in the integration with MVC without using any attributes.
To use Fluent Validation with ASP.Net MVC I am going to use an Inversion of Control container to instantiate the validators.

The difference between an Inversion of Control (IoC) and any other kind of frameworks is that the control gets inverted. The objects in a application are controlled by the Inversion of Control Containers and the application is completely unaware of what the IoC does. A IoC container manages it's life-cycle, invoks methods and is fully autonomous form the application.

The solution implemented by Fluent Validation is to use a custom Validator Factory.
The process to implement can be described in the following steps:
1) Create a Validator Factory for FluentValidation that inherits from ValidatorFactoryBase. Override the CreateInstance method to call the IoC container that is responsible for instantiating the validators.

public class StructureMapValidatorFactory : ValidatorFactoryBase
{
    public override IValidator CreateInstance(Type validatorType)
    {
        return ObjectFactory.TryGetInstance(validatorType) as IValidator;
    }
}

I am going to use StructureMap as the IoC Container.
2) Create a StructureMap Controller Factory that inherits from the MVC DefaultControllerFactory.

public class StructureMapValidatorFactory : ValidatorFactoryBase
{
    public override IValidator CreateInstance(Type validatorType)
    {
        return ObjectFactory.TryGetInstance(validatorType) as IValidator;
    }
}

3) Register your validator types with StructureMap, using the FluentValidation AssemblyScanner. The AssemblyScanner automatically registers all of the validator classes in a particular assembly with a IoC container.

public class MyRegistry : StructureMap.Configuration.DSL.Registry
{
    public MyRegistry()
    {
        FluentValidation.AssemblyScanner.FindValidatorsInAssemblyContaining<CustomerValidator>()
            .ForEach(result =>
            {
                For(result.InterfaceType)
                    .Singleton()
                    .Use(result.ValidatorType);
            });

    }
}

4) Configure MVC to use FluentValidation MVC integration in Global.asax Application_Start

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();

    RegisterGlobalFilters(GlobalFilters.Filters);
    RegisterRoutes(RouteTable.Routes);

    // Configure structuremap
    ObjectFactory.Configure(cfg => cfg.AddRegistry(new MyRegistry()));
    ControllerBuilder.Current.SetControllerFactory(new StructureMapControllerFactory());

    // Configure FluentValidation to use StructureMap
    var factory = new StructureMapValidatorFactory();

    // Tell MVC to use FluentValidation for validation
    ModelValidatorProviders.Providers.Add(new FluentValidationModelValidatorProvider(factory));
    DataAnnotationsModelValidatorProvider.AddImplicitRequiredAttributeForValueTypes = false;
}

5) Now the real fun can begin. The FluentValidation library can be used in MVC.
Let's create a customer validator class, where the First Name and Last Name are mandatory and we also want to set a custom error message.
Also perform the same validation on the email and guarantee that the email follows the basic rules.
The validation class must inherit from AbstractValidator.
The rules are defined using lambda expressions.

public class CustomerValidator : AbstractValidator<Customer>
{
    public CustomerValidator()
    {
        RuleFor(customer => customer.FirstName).NotEmpty().WithMessage("Please fill the first name.");
        RuleFor(customer => customer.LastName).NotEmpty().WithMessage("Please fill the last name.");
        RuleFor(customer => customer.EmailAddress).EmailAddress().WithMessage("Please fill a valid email address.")
                                                    .NotEmpty().WithMessage("Please fill the first email.");
    }

}

The FluentValidation has Built in Validators or you can define your custom validators.
You can read more about the fluent validation here
Structuremap download and documentation can be found here

MVC Data Annotation Validators - The Problem

2012-07-19T13:34:00.001+01:00

This "Problem - Solution" is a two series article . So this is the first: The Problem.

Validating objects through metadata is the approach of Validation with the MVC Data Annotation concept.
Metadata allows us to bind specific configurations to the business objects without changing or extending any of the objects implicit responsibilities.
In ASP.NET MVC, the concept is to use a specialized view model for each view and to pass the data needed. In this approach, the validation attributes must be placed in view model class.
If you have a separation between each layer of the application, and that separation implies that you have different projects for each layer. The model can be defined by the classes generated by the entity framework in you data access layer or by your business layer.

I have a database first approach using the Entity Framework and to use the MVC validation I could create a partial class with a MetadataType attribute to define the validator class. This is called a "buddy class".
A simple example is:

[MetadataType(typeof(CustomerMetadata))]
public partial class Customer
{
    // Class generated by the Entity Framework designer
}

public class CustomerMetadata
{
   [Required]
   public object Id;

   [Required]
   [StringLength(250)]
   public object FirstName;
}

The above approach works great, but if you have service oriented architecture with WCF Services the validation attributes will be ignored by the WCF Serializer.
The reason is obvious, the service and operation contracts are described by the Web Service Definition Language (WSDL), but the W3C specification doesn't support these attributes, so they are simple are ignored.

Possible solutions to this problem are:
1) Using buddy classes for partial classes of the WCF proxy
2) Share an assembly with your entities between your WCF client and WCF service and reuse those types

None of the above solutions are what I consider a good option and both will create tight coupling between your service and ASP.NET MVC application.

Another problem I face is that I have a project with all my service Proxies that is referenced by the MVC application or any client.
Since you cannot have two partial classes referring to the same class in two different assemblies (projects) and I don't want to have MVC specific client side validation on my reusable proxy library, so another solution is in order.

Above all i think that if you want to have client side validation you should to it on the client side.
The validation must be as simple as defining some rules that don't need to be associated directly with your model class.

I also wanted to take advantage of all the MVC validation infrastructure with client and server side validation. I don't want to reinvent the wheel and develop all that code manually.

The solution I found is to use Fluent Validation framework with an Inversion of Control container (IoC) to instantiate my validators.
The Inversion of Control (IoC) pattern is all about removing dependencies from your code.
Fluent Validation is a small validation library for .NET that uses a fluent interface and lambda expressions for building validation rules for your business objects.
This post explains the problem, on my next post I will explain the implementation of Fluent Validation with MVC as the solution.

Meanwhile if you can't wait, you can read about the fluent validation here

ASP.Net MVC Html Helper for the jqGrid

2012-07-13T10:40:00.000+01:00

The jqGrid is a great grid with a lot of features, but the free version must be developed using JavaScript.
If you are developping in MVC, having a free Html Helper would be great.
This is where the MvcJqGrid enters.

The MvcJqGrid is an Html Helper that eases greatly the implementation of the jqGrid in MVC 3 with the Razor view engine or MVC WebForms .

To use the MvcJqGrid you can NuGet the package and then include the namespace in the view

@using MvcJqGrid
@using MvcJqGrid.Enums

or the web.config in the namespaces of the system.web.webPages.razor section.
A simple example:

@(Html.Grid("CustomerGrid")
    .SetCaption("Customers")
    .AddColumn(new Column("CustomerId").SetLabel("Id"))
    .AddColumn(new Column("Name"))
    .AddColumn(new Column("Company"))
    .SetUrl(Url.Action("List","Customer"))
    .SetAutoWidth(true)
    .SetRowNum(10)
    .SetViewRecords(true)
    .SetPager("pager"))

The Html is self explanatory:

Html.Grid("CustomerGrid") - Creates the grid with id CustomerGrid
SetCaption - Sets the grid caption
AddColumn - Adds the columns to the grid
SetUrl - The Action and Controller that returns the json formatted for the jqGrid SetLable - Sets the Label of the column that appears in the grid

A simple MVC controller for the grid is:

public ActionResult List(GridSettings gridSettings)
{
   CustomerRepository repository = new CustomerRepository();
   string name = string.Empty;
   string company = string.Empty;
            
   if (gridSettings.IsSearch)
   {
       name = gridSettings.Where.rules.Any(r => r.field == "Name") ? gridSettings.Where.rules.FirstOrDefault(r => r.field == "Name").data : string.Empty;
        company = gridSettings.Where.rules.Any(r => r.field == "Company") ? gridSettings.Where.rules.FirstOrDefault(r => r.field == "Company").data : string.Empty;
    }

    var customers = repository.List(name, company, gridSettings.SortColumn, gridSettings.SortOrder);
    int totalCustomers = customers.Count;
    var jsonData = new
    {
        total = totalCustomers / gridSettings.PageSize + 1,
        page = gridSettings.PageIndex,
        records = totalCustomers,
        rows = (
                from c in customers
                select new
                {
                    id = c.CustomerID,
                    cell = new[]
                    {
                        c.CustomerID.ToString(),
                        string.Format("{0} {1}", c.FirstName, c.LastName),
                        c.CompanyName,
                        c.EmailAddress
                    }
        }).ToArray()
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

A more complex example, with a column for operations like edit and delete is:

@(Html.Grid("CustomerGrid")
    .SetCaption("Customers")
    .AddColumn(new Column("CustomerId").SetLabel("Id").SetSearch(false))
    .AddColumn(new Column("Name"))
    .AddColumn(new Column("Company"))
    .AddColumn(new Column("EmailAddress").SetLabel("Email Address").SetSearch(false))
    .AddColumn(new Column("Last Modified").SetSearch(false))
    .AddColumn(new Column("Telephone").SetSearch(false))
    .AddColumn(new Column(" ").SetSearch(false).SetCustomFormatter("buttonize").SetWidth(60).SetAlign(Align.Right))
    .SetUrl(Url.Action("List","Customer"))
    .SetAutoWidth(true)
    .SetRowNum(10)
    .SetRowList(new[] { 10, 15, 20, 50 })
    .SetViewRecords(true)
    .SetPager("pager")
    .SetSearchToolbar(true)
    .SetSearchOnEnter(false))

Where buttonize is a javascript function where the operation column Html is returned.

   function buttonize(cellvalue, options, rowobject) {
        return 'eXtreme Programming';
    }

For more information and live examples go to the MvcJqGrid site by pressing here
The about tab has the links to the license, the source code and documentation.

How to parse a ASP.Net JSON formatted date in C#

2012-06-12T16:31:00.001+01:00

ASP.Net serializes a date to JSON in a Microsoft specific format:

/Date(1338505200000)/

The value 1338505200000 is the number of milliseconds since January 1st 1970 UTC.

The data can be parsed in C# removing the placeholders "/Date(" and ")/" and parsing it as:

string jsonDate = "/Date(1338505200000)/"; jsonDate = jsonDate .Replace("/Date(", string.Empty); jsonDate = jsonDate.Replace(")/", string.Empty);

var date = new DateTime(1970, 1, 1, 0, 0, 0, 0).AddMilliseconds(long.Parse(jsonDate));

jQuery Deferred for Asynchronous callbacks

2012-06-12T11:42:00.000+01:00

jQuery Deferred is a convenient way to perform Asynchronous callbacks.
For example, during the load of a page if you are performing a javaScript operation that takes some time to execute, an asynchronous callback can be used. The page will load without blocking.

jQuery has the $.Deferred() feature that can register multiple callbacks and invoke them.
The deferred.done() adds an handler to be called when the Deferred object is resolved (doneCallbacks).
The deferred.resolve() resolves a Deferred object and calls any doneCallbacks with the given args.

The following code is an example usage:

var deferred = new $.Deferred();

deferred.done(function(message) { 
    // Put the code to execute asynchronously here
    alert(message);
});

deferred.resolve('"hello world!!!"');

There is a lot more about the Deferred Object in the jQuery documentation.

KnockoutJS force viewModel update

2012-06-01T11:38:00.001+01:00

The KnockoutJS (KO) has a binding parameter named valueUpdate that defines which browser event KO should use to detect changes.

The default is on change: As the documentation says: "change" (default)
- updates your view model when the user moves the focus to a different control, or in the case of select elements, immediately after any change

Now let us say that you are using the jquery datepicker on a textbox.
After the date is selected the texbox has the value, but KO doesn't update the value on it's model, because there isn't any change event on the control.

To force that KO updates the model for the date textbox, the change event must be fired:

$("#textboxID").trigger("change");

Another possible solution is to create a custom binding for the jQuery datepicker.
The skeleton to specifiy a custom binding is:

ko.bindingHandlers.myCustomBinding = {
    init: function(element, valueAccessor, allBindingsAccessor, viewModel) {

    },
    update: function(element, valueAccessor, allBindingsAccessor, viewModel) {

    }
};

You can create a datepicker binding:

ko.bindingHandlers.datepicker= {
    init: function(element, valueAccessor, allBindingsAccessor, viewModel) {
      var parameters = allBindingsAccessor().datepickerParameters || {};
      $(element).datepicker(parameters);

       //handle the field changing
    },
    update: function(element, valueAccessor, allBindingsAccessor, viewModel) {
       //handle the field changing
    }
};

and use it as:

Customer KnockoutJS Validation

2012-05-29T13:56:00.000+01:00

This article is the second on KnockoutJS series I am writing.
The first article was introductory: Customer KnockoutJS and MVC demo using JSON

Now I am going to focus on KnockoutJS and validation. I am going to use a KnockoutJS Plugin for model and property validation that is named Knockout Validation. You can download it from here.

The Asp.Net MVC Controller have the actions to Get and Add a customer. This example has a new customer property: the country.
This property allows to add an input type select to KnockoutJS and validation.

namespace KnockoutDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "";

            return View();
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public JsonResult Get(int customerID)
        {
            // Get the customer ...
            Customer customer = new Customer {CustomerID = customerID, 
                                              FirstName = "John", 
                                              LastName = "Doe", 
                                              IsMale = true, 
                                              CountryID = 1 };
            return Json(customer);
        }

        [HttpPost]
        public JsonResult Add(Customer customer)
        {
            // Save the customer ...

            // return status message 
            var message = string.Format("Customer: {0} {1} Added. IsMale: {2} Age:{3}  CountryID: {4} ",
                                        customer.FirstName, customer.LastName, customer.IsMale.ToString(), 
                                        customer.Age.ToString(), customer.CountryID.ToString());
            return Json(message);
        }

    }
}

The Asp.Net MVC model is the customer with the new property:

 namespace KnockoutDemo.Models
{
    public class Customer
    {

        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public bool IsMale { get; set; }
        public int Age { get; set; }
        public int CountryID { get; set; }
    }
}

The Asp.Net MVC Layout includes the new knockout validation plugin:

       
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>@ViewBag.Title</title>
    <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
    <script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/modernizr-1.7.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/knockout.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/json2.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/knockout.validation.js")" type="text/javascript"></script>
</head>
<body>
    <div class="page">
        <header>
            <div id="title">
                <h1>Knockout Demo</h1>
            </div>
            <div>&nbsp;</div>
        </header>
        <div>&nbsp;</div>
        <div>&nbsp;</div>
        <section id="main">
            @RenderBody()
        </section>
        <footer>
        </footer>
    </div>
</body>
</html>

And the Asp.Net MVC view has the KnockoutJS and validation specifics:

@{
    ViewBag.Title = "Add Customer";
}



@ViewBag.Message

Customer Number:  



First Name: 
Last Name: 
Age: 
Male
Country:

The KnockoutJS now has a observableArray of countries in the VewModel, that is bind to the country select.
Note the data-bind oprtions of the select:
- options: controls what options should appear in a drop-down lis
- optionsValue: The name of the ViewModel property to bind to the option value
- optionsText: The name of the ViewModel property to bind to the option text
- value: The name of the ViewModel property to bind to the selected value
- optionsCaption: The option that is used to make the user select a value on the select list

There is also the validation plugin specifics in the VewModel declaration:
- The extend is used to extend the observables with the validation rules. (you can read about all of them here)
In the example I am using the required (required: true) and number (number: true) validation rules.
- validatedObservable in the view model declaration
- isValid() - Before saving the data validate that the model is valid
- validation.init - Used to configure the validation engine

In the example the invalid inputs are painted in red, that is where the css class is used by the validation.

Customer KnockoutJS and MVC demo using JSON

2012-05-07T14:41:00.001+01:00

After reading about KnockoutJS I have decided to create a simple demo using JSON to comunicate with the web server.
The application retrieves a Customer from an ASP.Net MVC Action and sends it to be saved.

The KnockoutJS is a JavaScript Model View ViewModel (MVVM) framework.
The View Model object contains properties which values are specified as ko.observable(). Knockout will automatically updates the UI when the view model changes.
KnockoutJS has a declarative binding syntax where the HTML view elements are bind with our view model object. Knockout uses the "data-bind" attribute in the HTML elements for the data binding.

To learn the basics goto the KnockoutJS site by pressing here

Pr-requisites:
1) KnockoutJS
2) json2.js - for json parsing
3) ASP.NET MVC
4) jQuery

The Asp.Net MVC Controller have the actions to Get and Add a customer.

namespace KnockoutDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "";

            return View();
        }


        [AcceptVerbs(HttpVerbs.Post)]
        public JsonResult Get(int customerID)
        {
            // Get the customer ...
            Customer customer = new Customer {CustomerID = customerID, FirstName = "John", LastName = "Doe", IsMale = true };

            return Json(customer);
        }


        [HttpPost]
        public JsonResult Add(Customer customer)
        {
            // Save the customer ...

            // return status message 
            var message = "Customer: " + customer.FirstName + " " + customer.LastName + " Added.";
            message += " IsMale: " + customer.IsMale.ToString();
            return Json(message);
        }

    }
}

The Asp.Net MVC model is the customer:

    public class Customer
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public bool IsMale { get; set; }
    }

The Asp.Net MVC Layout:

    
 <!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>@ViewBag.Title</title>
    <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
    <script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/modernizr-1.7.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/knockout.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/json2.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/knockout.validation.js")" type="text/javascript"></script>
</head>
<body>
    <div class="page">
        <header>
            <div id="title">
                <h1>Knockout Demo</h1>
            </div>
            <div>&nbsp;</div>
        </header>
        <div>&nbsp;</div>
        <div>&nbsp;</div>
        <section id="main">
            @RenderBody()
        </section>
        <footer>
        </footer>
    </div>
</body>
</html>

And finally the Asp.Net MVC view has the KnockoutJS specifics:

@{
    ViewBag.Title = "Add Customer";
}

<h2>
@ViewBag.Message</h2>
<form action="" method="post">
<b>Customer Number: </b> <span data-bind="text: CustomerID"></span><br />
<br />
<b>First Name: </b><input data-bind="value: FirstName" style="width: 200px;" type="text" /> <br />
<br />
<b>Last Name: </b><input data-bind="value: LastName" style="width: 200px;" type="text" /> <br />
<br />
<input data-bind="checked: IsMale" type="checkbox" /><b>Male</b><br />
<br />
<input data-bind="click: KnockoutDemoNamespace.addCustomer" type="button" value="Add Customer" /><br />
<div id="message">
</div>
</form>

The KnockoutJS has the following specifics:
1) View Model object: customerViewModel
2) HTML View: The HTML from the page with the KnockoutJS specific attributes for data binding
The demo performs a data bind with a span, and the input types text, checkbox and button.
Note that the "data-bind" has the html element attribute to be affected and the View Model property associated.
3) View Model activation: ko.applyBindings(viewModel);

Error deleting a file: Could not find this item. This is no longer located in

2012-05-03T15:17:00.000+01:00

When I was trying to delete a file I got a strange error:
      "Could not find this item. This is no longer located in..."

After some investigation I found the following solution:
1) Open a command prompt
2) Go to the folder where is the located the problematic file:
   cd c:\temp
3) Type the command:
                   dir /x
4) Find the 8.3 filename
5) Delete the file using the command:
    del filenam~1.txt

And bingo...the file is deleted !

T-SQL And Linq To Sql Reference: IN

2012-04-30T16:23:00.001+01:00

1. T-SQL

 select *   
 from Orders o  
 where ProductID IN (3, 4, 5)

2. Linq

Code:

List<int> filter = new List<int>();  
filter.add(3);  
filter.add(4);  
filter.add(5);  
var orders = from o in dc.Orders    
          where filter.Contains(o.ProductID)  
          select o;

How to Convert rows to CSV in T-SQL

2011-12-13T16:52:00.000Z

To convert several rows to unique row in CSV using T-SQL, use the following code:

DECLARE @myList varchar(MAX)
SELECT @myList = coalesce(@myList + ',', '') + UserName 
FROM [User]

SELECT @myList

WCF: The maximum string content length quota (8192) has been exceeded

2011-12-13T16:39:00.003Z

A very annoying error I received when passing a very large string to a WCF Service was:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter . The InnerException message was 'There was an error deserializing the object of type System.String. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 8, position 9944.'. Please see InnerException for more details.

This is a cryptic error and only after some investigation I found the solution:

The readerQuotas element must be configured on the binding configuration:

<binding name="basicHttpBinding_IMyService" maxReceivedMessageSize="63400320">
      <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647"
       maxBytesPerRead="2147483647"   maxNameTableCharCount="2147483647"/>
</binding><br />

This is a simple example, you must configure it for your own needs.
I have added the additional configuration elements to show the other options.

Bulk insert efficiently

2011-12-13T15:20:00.000Z

Using an ORM like Entity Framework or performing single inserts is not efficient to perform bulk operations.
To bulk insert efficiently, the SqlBulkCopy class ADO.Net must be used.

The SqlBulkCopy class is used to efficiently bulk load a Sql Server table with data from another sources.
SQL Server has a command-prompt utility named bcp to also perform a bulk insert. The SqlBulkCopy is the equivalent for writing managed code.

The data source, like for example a flat file, can be converted to a generic list, that is the source of the SqlBulkCopy or a DataTable can be used directly.

To bulk insert a generic list, it must be converted to a DataTable so that it can be used by the SqlBulkCopy:

public static DataTable ConvertToDataTable<T>(IList<T> list)
{
    PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for (int i = 0; i < propertyDescriptorCollection.Count; i++)
    {
        PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
        Type propType = propertyDescriptor.PropertyType;
        if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propType));
        }
        else
        {
            table.Columns.Add(propertyDescriptor.Name, propType);
        }
    }
    object[] values = new object[propertyDescriptorCollection.Count];
    foreach (T listItem in list)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = propertyDescriptorCollection[i].GetValue(listItem);
        }
        table.Rows.Add(values);
    }
    return table;
}

Then the SqlBulkCopy can be used.
The steps to use the SqlBulkCopy are simple:
1) A SqlConnection open must be passed to it's constructor.
2) A DataTable must have the data to bulk insert
3) The mappings between the database columns and the DataTable must be defined, using the ColumnMappings propery. The Column mappings define the relationships between columns in the data source and columns in the destination.
4) The DestinationTableName property must be set with the name of the destination table on the server.
5) The WriteToServer method copies all the rows of the DataTable to the specified destination table.

In the following example the user table is bulk inserted, where dt is the DataTable with the data to bulk insert.

DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
    {
        sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
        sqlBulkCopy.ColumnMappings.Add("Password", "Password");
        sqlBulkCopy.DestinationTableName = "User";
        sqlBulkCopy.WriteToServer(dt);
    }
}

The WriteToServer method also supports a DataRow array or a IDataReader.

Recover the mirror database

2011-12-05T16:34:00.001Z

Database mirroring is a software solution for increasing database availability.
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
When the mirror is removed from the Primary database the mirrored one sometimes doesn't recover and it says "Recovering".
If the database doesn't leave this state, the following TSQL command can help:

RESTORE DATABASE myDatabase WITH RECOVERY

This will allow to recover the former mirror database.

ValidateRequest=“false” doesn't work in Asp.Net 4

2011-11-14T15:16:00.001Z

Before Asp.Net 4.0 is some scenarios it was useful to disable the automatic request validation performed by the .Net Framework to prevent Script Attacks.

This could be done by going to web.config or the page and setting

ValidateRequest="false"

In Asp.Net 4.0 this feature only works if we set requestValidationMode="2.0" :