Wednesday, November 6, 2013

SQL Server : Context_Info

 

While working one of my project, need for connection specific setting required to enable and disable functionality. Project has very large database in terms of entities and many of entities have triggers that affect other entity and also done task for sending notification.

Sometime there is need to disable certain trigger required during data mining work without affecting application that use data.

While searching for solution to that issue I came across Context_Info functionality available in SQL Server.It is varbinary field and its value available only for particular connection session.

You can use it following way.

Step 1. SET CONTEXT_INFO 0x1

Step 2. If 0x01 = Context_Info()
    Print ‘ContextInfo Set’
else
    print ‘ContextInfo Not Set’

If you have SQL Server Management Studio then open two new query window.

In first window put code specified in Step1 and Step2 and In Second window only put code specified in Step 2.

image

You can also use above functionality during coding as well. You just have to call that function with value. Following is the sample with C#. ( Test application that I created for this)

 class SqlContextInfoSample
{
public void ExecuteSample()
{
using (var conn = GetConnection())
{
SetContextInfomationToConnection(conn);
Console.WriteLine(GetContextInformationFromConnection(conn));
}

using (var conn = GetConnection())
{
Console.WriteLine(GetContextInformationFromConnection(conn));
}
}

SqlConnection GetConnection()
{
var conn = new SqlConnection();
conn.ConnectionString = @"<>";
conn.Open();
return conn;
}

void SetContextInfomationToConnection(SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SET CONTEXT_INFO 0x1";
cmd.ExecuteNonQuery();
}
}

string GetContextInformationFromConnection(SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Select CONTEXT_INFO()";
object result = cmd.ExecuteScalar();
return result.ToString();
}
}
}

Hope this will help.

Tuesday, November 5, 2013

SQL Server 2012 : OFFSET FETCH VS CTE WITH ROW_NUMBER()

 

Since SQL Server 2005 and 2008 ROW_NUMBER() Function available and many of us will us that function with CTE to get data by custom page size.

In SQL Server 2012 there is new functionality to get record by page size and it is known as OFFSET FETCH.

While Learning SQL Server 2012 Syntax for OFFSET and FETCH clause I found that this will not work if you are specify order by clause in Select query. So I decided to check ROW_NUMBER() with CTE to new functionality in terms of performance.

For this example AdventureWorks_2012 database in used.

Old Way Of Query ( Version 2005 , 2008)

WITH CTE
AS
(
Select AccountNumber , P.FirstName , P.LastName , P.MiddleName , ROW_NUMBER() OVER (ORDER BY P.BusinessEntityId) RowNum from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
)
Select AccountNumber , FirstName , LastName , MiddleName from CTE WHERE RowNum > 1000 and RowNum <= 2000

SQL Server 2012

Select AccountNumber , P.FirstName , P.LastName , P.MiddleName   from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
ORDER BY P.BusinessEntityID
OFFSET 1000 ROWS
FETCH NEXT 100 Rows ONLY

At this point I thought there must be a performance improvement in SQL Server 2012. To check that I used execution plan and statics IO.


Execution Plan ( Both Query Took 50%)


image


STATISTICS IO ( Both are same)


image


As per my thinking there is no performance improvement and it is just syntax get change ( Internally In SQL Engine both query are same as they generated same execution plan)


Please give me your opinion regarding this and correct me If I am wrong somewhere to understand functionality.

Monday, May 14, 2012

ASP.net MVC generic client and server side validation

In ASP.net Webform has CustomValidator which can call any javascript function for validation and which make our task is easy in terms of validation. During ASP.net MVC development we have validation via “DataAnnotation Attribute” for validation. All default attribute support most of our requirement but sometime need for custom validation.ASP.net MVC support this via Custom Attribute development but when this lead us to develop to many validation attribute for our need.

Now here i am going to show you one generic validation attribute implement which call any javascript function ( client side validation) or static function ( server side validation). Here i am assuming that ASP.net MVC 3 or later used which Unobtrusive validation true for project.

To develop custom validation attribute create class that inherits from ValidationAttribute and implement IClientValidatable interface.

C# Code 


public class JSFunctionValidateAttribute : ValidationAttribute, IClientValidatable
{
/// <summary>
/// Ctor: Javascript functionname is mandatory and serversideStaticFunction is not mandatory.
/// </summary>
/// <param name="javascriptFunctioname"></param>
/// <param name="serverSideStaticFunction"></param>
public JSFunctionValidateAttribute(string javascriptFunctioname,string serverSideStaticFunction = "")
:base()
{
JavaScriptFunctionName = javascriptFunctioname;
ServerSideFunction = serverSideStaticFunction;
}

/// <summary>
/// Javascript function that need to call
/// </summary>
public string JavaScriptFunctionName { get; set; }

/// <summary>
/// Server side validation.
/// </summary>
public string ServerSideFunction { get; set; }

/// <summary>
/// This function is used to call server side validation function.
/// </summary>
/// <param name="value"></param>
/// <param name="validationContext"></param>
/// <returns></returns>
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
        {
            if (!string.IsNullOrEmpty(ServerSideFunction))
            {
                string className = ServerSideFunction.Substring(0, ServerSideFunction.LastIndexOf('.'));
                string functionName = ServerSideFunction.Substring(ServerSideFunction.LastIndexOf('.')+1);
                Type staticclassMethod = Type.GetType(className);
                MethodInfo info = staticclassMethod.GetMethod(functionName, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
                object result = info.Invoke(null ,new object[]{ value, validationContext , ErrorMessage });
                if (result != null)
                {
                    return (ValidationResult)result;
                }
            }           
            return null;
        }

/// <summary>
/// This generate client side unobtrusive validation.
/// </summary>
/// <param name="metadata"></param>
/// <param name="context"></param>
/// <returns></returns>
public IEnumerable<ModelClientValidationRule> GetClientValidationRules(
ModelMetadata metadata, ControllerContext context)
{
var rule = new ModelClientValidationRule();
rule.ErrorMessage = FormatErrorMessage(metadata.GetDisplayName());
rule.ValidationType = "jsfunctionvalidation";
rule.ValidationParameters.Add("jsfunctionname", JavaScriptFunctionName);
yield return rule;
}
}

In above code two important thing.

1. For client side validation

 public IEnumerable<ModelClientValidationRule> GetClientValidationRules(
ModelMetadata metadata, ControllerContext context)
{
var rule = new ModelClientValidationRule();
rule.ErrorMessage = FormatErrorMessage(metadata.GetDisplayName());
rule.ValidationType = "jsfunctionvalidation";
rule.ValidationParameters.Add("jsfunctionname", JavaScriptFunctionName);
yield return rule;
}

ValidationType is “jsfunctionvalidation” and validation parameter contain “jsfunctionname” which contain name of javascript function to call.


Now register for unobstrusive validation to Jquery validation adaptor.

jQuery.validator.addMethod("jsfunctionvalidation", function (value, element, param) {  
return window[param](value, element, param);
});

jQuery.validator.unobtrusive.adapters.add("jsfunctionvalidation", ["jsfunctionname"], function (options) {
options.rules["jsfunctionvalidation"] = options.params.jsfunctionname;
options.messages["jsfunctionvalidation"] = options.message;
});

2. For Server-side validation

protected override ValidationResult IsValid(object value, ValidationContext validationContext)
        {
            if (!string.IsNullOrEmpty(ServerSideFunction))
            {
                string className = ServerSideFunction.Substring(0, ServerSideFunction.LastIndexOf('.'));
                string functionName = ServerSideFunction.Substring(ServerSideFunction.LastIndexOf('.')+1);
                Type staticclassMethod = Type.GetType(className);
                MethodInfo info = staticclassMethod.GetMethod(functionName, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
                object result = info.Invoke(null ,new object[]{ value, validationContext , ErrorMessage });
                if (result != null)
                {
                    return (ValidationResult)result;
                }
            }           
            return null;
        }

This function call static function for validation via reflection and ServerSideFunction must contain fully qualified name. (Look at example later in this post)


Now look at complete example.


Let say we have to implement that required that Name must start which “C”.

Model class

    /// <summary>
/// Person model.
/// </summary>
public class Person
{
public int Id { get; set; }

[Display(Name = "Full name")]
[Required]
[JSFunctionValidate("NameValidation", "Models.Models.ServerSideValidation.ServerSideNameValidation", ErrorMessage = "Name must start with character 'C'")]
public string Name { get; set; }
}
JSFunctionValidate attribute indicate that need to call “NameValidation” javascript function for client side validation ( you will see sample function later in code) and for server side validation function you need to call “ServerSideNameValidation” static function of static class “Models.Models.ServerSideValidation”.

Server side validation class ( Signature of method must be same and class,method must be static)

    /// <summary>
/// Static server side validation function
/// </summary>
public static class ServerSideValidation
{
public static ValidationResult ServerSideNameValidation(object value,ValidationContext context, string errorMessage)
{
if (value != null)
{
if (value.ToString().StartsWith("C"))
{
return null;
}
}
return new ValidationResult(errorMessage);
}
}

Create View ( Razor View)

@model Models.Models.Person
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/customvalidations.js")" type="text/javascript"></script>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Person</legend>

<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>

<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<script language="javascript" type="text/javascript">
function NameValidation(value, element, param) {
if (value != null) {
if (value[0] == 'C')
return true;
}
return false;
}
</script>

Highlighted JS code is for client side validation.


Test Controller

public class TestController : Controller
{

public ActionResult Index()
{
return View();
}


public ActionResult Create()
{
return View();
}

[HttpPost]
public ActionResult Create(Models.Person person)
{
try
{
// TODO: Add insert logic here
if (ModelState.IsValid)
{
return RedirectToAction("Index");
}
else
{
return View("Create", person);
}
}
catch
{
return View();
}
}
}

Hope this solution give you your solution. Feel free to give your valuable comment.


Sample Code: Download

Create and Read Excel File Using Oledb provider

When working with excel file for data mining or export data to excel many time required to read excel file and also write file. One can use Oledb Provider to connect existing file or create new excel file for report in .net

1. Get Sheets Name from Excel File

      
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
string filePath = @"c:\myExcel2012.xlsx";
conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";",filePath);
conn.Open();
DataTable dtSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dtSheets.Rows)
{
Console.WriteLine(dr["TABLE_NAME"].ToString()); // Print Sheet Name
}
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();
}

2. Read Sheet Data

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
string filePath = @"c:\myExcel2012.xlsx";
conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", filePath);
conn.Open();
DataTable dtSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dtSheets.Rows)
{
Console.WriteLine(dr["TABLE_NAME"].ToString());
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM " + dr["TABLE_NAME"].ToString();
var reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader); // This will load data from excel sheet to datatable.

// your code to work on sheet data.
}
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}

3. Create and Write Data to Excel sheet

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
string pathOfFileToCreate = "c:\newexcel.xlsx";
conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";",pathOfFileToCreate);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE sheet1 (ID INTEGER,NAME NVARCHAR(100))"; // Create Sheet With Name Sheet1
cmd.ExecuteNonQuery();
for (int i = 0; i < 1000; i++) // Sample Data Insert
{
cmd.CommandText = String.Format("INSERT INTO sheet1 (ID,NAME) VALUES({0},'{1}')", i, "Name" + i.ToString());
cmd.ExecuteNonQuery(); // Execute insert query against excel file.
}
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}

 


I feel that above sample code is best way to explain article.

Tuesday, May 1, 2012

DataView ToTable Vs Linq to find distinct

One of my past post i mention that how to find distinct from DataTable (http://dotnetstep.in/find-distinct-value-from-dataset-datatable-in-net). Most of the time need is simple and we need to find out one or two column for distinct column but when need is complex and data is not unique than all columns are used to find distinct value of datatable.

When data in datatable is small is count ( Upto certain thousand) and it contains many duplicate record in that case performance is ok for dataview.ToTable but when no. of row increases performance decrease and in case dataView.ToTable, it is noticeable decrease in performance.

Performance depend on total no of record and total of distinct record that dataset/datatable going to return.

What is the solution ?
If you are using .net framework 3.5 SP1 or later then Linq to DataTable comes to rescue. DataTable.AsEnumerable().Distinct() is much faster than DataView ToTable functionality.

Here is performance matrix on sample data. ( You can see code that i used to measure performance later in this post)

NoOfItem/Distinct Item DataView ToTable Time (Milliseconds) Linq Distinct Time (Milliseconds)
100000/2000 13675 117
200000/2000 27271 231
100000/20000 176101 113
200000/20000 412053 483
100000/80000 777402 179
200000/80000 2249655 207

Seeing above result it is clear that Linq gives much better performance.

C# Code to find distinct using linq.

var items = dt.AsEnumerable().Distinct(System.Data.DataRowComparer.Default).ToList();   

Code used to measure performance.

class Program
{
static System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
static void Main(string[] args)
{
Console.WriteLine(String.Format("{0},{1},{2},{3},{4}", "NoOfItem", "Distinctcountwithdataview", "Distinctcountwithdataviewtime", "WithoutdataviewdistinctCount", "Withoutdataviewdistinctcounttime"));
for (int i = 0; i < 1000; i+=100)
{
DataTable dtSample = GenerateTable(1000 * i, 80000);
var withDataView = NormalDataSetWithDataView(dtSample);
var withoutDataView = NormalDataSetWithOutDataView(dtSample);
Console.WriteLine(String.Format("{0},{1},{2},{3},{4}",1000*i,withDataView.Item1,withDataView.Item2,withoutDataView.Item1,withoutDataView.Item2));
}

Console.ReadLine();
}

private static Tuple<int,long> NormalDataSetWithDataView(DataTable dt)
{
StopWatchEnable(watch, true);
DataView dv1 = new DataView(dt);
DataTable dtNew1 = dv1.ToTable(true, "Id", "Name", "Role", "DataColumn1", "DataColumn2", "DataColumn3", "DataColumn4", "DataColumn5", "DataColumn6", "DataColumn7");
return new Tuple<int,long>(dtNew1.Rows.Count , StopWatchEnable(watch, false));
}
private static Tuple<int, long> NormalDataSetWithOutDataView(DataTable dt)
{
StopWatchEnable(watch, true);
var items = dt.AsEnumerable().Distinct(System.Data.DataRowComparer.Default).ToList();
return new Tuple<int, long>(items.Count, StopWatchEnable(watch, false));
}

private static DataTable GenerateTable(int noOfRows, int modules)
{
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Role", typeof(string));
dt.Columns.Add("DataColumn1", typeof(string));
dt.Columns.Add("DataColumn2", typeof(string));
dt.Columns.Add("DataColumn3", typeof(string));
dt.Columns.Add("DataColumn4", typeof(string));
dt.Columns.Add("DataColumn5", typeof(string));
dt.Columns.Add("DataColumn6", typeof(string));
dt.Columns.Add("DataColumn7", typeof(string));
for (int i = 0; i < noOfRows; i++)
{
int id = i % modules;
dt.Rows.Add(new object[] { id, "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString(), "Test" + id.ToString() });
}
return dt;
}

private static long StopWatchEnable(System.Diagnostics.Stopwatch watch, bool enable)
{
if (enable)
{
watch.Restart();
return 0;
}
else
{
watch.Stop();
return watch.ElapsedMilliseconds;
}
}
}

 


Why DataView.ToTable is slow ?
- I found that ToTable functionality implemented generic functionality to compare each type of row and the way compare data and also it return new result set with distinct field.


Note:
Above code consider that need to find distinct from all column. If you want to find based on certain field then implement custom IEqualityComparer for datarow.I haven’t tested but PLinq might give some performance improvement in Linq operation.

Suggestion are must welcome.

Global assembly cache location change in .net 4.0

When .net Framework 4.0 release it comes with one major change and that change is GAC (Global assembly cache) location. Since first version of .net framework release GAC location is fix to %windir%/assembly. This is default location and you can change that location but default installation is always point to above location . ( If you want to know how to change GAC Location manually then look at my post http://dotnetstep.in/change-the-location-of-gac 

In .net 4.0 this default location is change to C:\Windows\Microsoft.net\Assembly

Why this thing is important to know?
1.  As default location is C:\Windows\Assembly we used to drag and drop assembly directly to GAC but this is not the case when work with 4.0
2. It is better to use GACUtil tool for .net 4.0 to install assembly.

Following link contain many information regarding this.
http://stackoverflow.com/questions/2660355/net-4-0-has-a-new-gac-why

Suggestion:
It would be nice if C:\Windows\Microsoft.net\Assembly as same look and feel as older GAC.
as per msdn shell extension is obsolete (http://msdn.microsoft.com/en-us/library/34149zk3.aspx

Please provide your input if you have any other thing to add.

Friday, March 9, 2012

Move to funnelweb

Hello Readers,

I am moving to funnelwebblog as my personal blog engine. I am not deleting this blog and you continue to read all my older post over here  as well as new blog ( Move old post over there )

Once i will fully satisfied with new blog engine i will disable this blog and if i not satisfied i will come back to blogspot.

New blog location : http://dotnetstep.in/