How to find tables used in stored procedure in sql server

In this "How to find tables used in stored procedure in sql server"article i am going to discuss about some SQL queries which are useful in our real time projects. We might have done these things manually but now lets see how useful these queries turns out to be.

First SQL query what i wanted to discuss is -



How to find all stored procedure names which uses the given table

Query -

SELECT DISTINCT 
 obj.Name
 ,com.Text
FROM sysobjects obj 
     INNER JOIN syscomments com ON obj.ID = com.ID
WHERE obj.Type = 'P' AND com.Text LIKE '%' + 'yourtablename' + '%'
ORDER BY obj.Name

Lets create a function use this query in function -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllStoredProcsByTableName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllStoredProcsByTableName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all stored procedure details which is using given table
-- =============================================
CREATE FUNCTION FN_GetAllStoredProcsByTableName 
( 
 @tableName VARCHAR(50)
)
RETURNS 
@SPDetails TABLE 
(
 Name VARCHAR(50)
 ,Text VARCHAR(5000)
) 
AS
BEGIN
 
 INSERT INTO @SPDetails
 (
  Name,
  Text
 )
 SELECT DISTINCT 
  obj.Name
  ,com.Text
 FROM sysobjects obj 
  INNER JOIN syscomments com on obj.ID = com.ID
        WHERE obj.Type = 'P' AND com.Text LIKE '%' + @tableName + '%'
        ORDER BY obj.Name
 
 RETURN;
END

As you can see in the above function, table name is passed as an parameter and this table name is searched in all the stored procedures (Type = 'P'). Now lets execute the above function and lets see how the results will look like.

Before executing this function i have created tables "Student" and "Class"(Here i am not giving the table structures as these are trivial)and created a stored procedures "SP_GetAllStudents" and "SP_GetAllClasses" for demonstration purpose, which returns all the students and class details respectively nothing fancy.
Stored procedure SP_GetAllStudents -

CREATE PROCEDURE [dbo].[SP_GetAllStudents]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    SELECT 
 Classes.ClassID 
 ,Students.StudentID
 ,Students.StudentName
    FROM Classes
     INNER JOIN Students ON Classes.ClassID = Students.ClassID
END

Now lets execute the function -

SELECT * FROM dbo.FN_GetAllStoredProcsByTableName('students')

Above function returns below resultset -

Second stored procedure is not filtered because in that stored procedure only "Class" table is used.
Now lets move onto the next SQL query -

How to find tables used in stored procedure in sql server

Query -

SELECT DISTINCT 
  obj2.Name
FROM sys.sysobjects obj
 INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
 INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
WHERE obj.Type = 'P' and obj.Name = 'SP_GetAllStudents'

Now lets create a new function as we created earlier -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllTableNamesBySpName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllTableNamesBySpName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all tables used in given SP name
-- =============================================
CREATE FUNCTION FN_GetAllTableNamesBySpName 
( 
 @SPName VARCHAR(50)
)
RETURNS 
@TableNames TABLE 
(
 Name VARCHAR(50)
) 
AS
BEGIN
 
 INSERT INTO @TableNames
 (
  Name
 )
 SELECT DISTINCT 
  obj2.Name
 FROM sys.sysobjects obj
  INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
  INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
 WHERE obj.Type = 'P' and obj.Name = @SPName
 
 RETURN;
END

As you can see above stored procedure name is passed as a parameter and table names are searched. This will give below result set once executed with
"SP_GetAllStudents" -

SELECT * FROM dbo.FN_GetAllTableNamesBySpName('SP_GetAllStudents')

Hope this article is useful. Please comment your thoughts.

Skip and take in sql server 2008

As we all know in LINQ we have the functionality of Skip and Take on list of objects , where we can skip some rows of list of objects and take the required list of objects. So here in this - "skip and take in sql server 2008" article i will try to explain the same functionality in SQL Server.
Before that lets see how skip and take functions work in LINQ -
To explain this i will create a class called "Employee.cs" as below -

//Example for Skip and take in sql server 2008
public class Employee
{
 public int ID { get; set; }
 public string Name { get; set; }
}

Now lets create a method which will return the list of employees -

//Example for Skip and take in sql server 2008
public static List<Employee> GetEmployees()
{
 List<Employee> employees = new List<Employee>();

 employees.Add(new Employee() { ID = 1, Name = "Jack" });
 employees.Add(new Employee() { ID = 2, Name = "Aaron" });
 employees.Add(new Employee() { ID = 3, Name = "Peter" });
 employees.Add(new Employee() { ID = 4, Name = "David" });
 employees.Add(new Employee() { ID = 5, Name = "Abel" });
 employees.Add(new Employee() { ID = 6, Name = "Adam" });
 employees.Add(new Employee() { ID = 7, Name = "Brian" });

 return employees;
}

Now my intention would be to get 2 employees from the above list namely "Abel" and "Adam" and skip first 4 employees of the list.

//Example for Skip and take in sql server 2008
List selectedEmps = emps.Skip(4).Take(2).ToList();

Now lets get into SQL and check the same functionality -
For demonstration purpose i will create a table called "Employee" and insert the same list of employees into that table.

CREATE TABLE Employee
(
 ID INT IDENTITY(100,1),
 NAME VARCHAR(50)
)

INSERT INTO Employee
VALUES
   ('Jack')
   ,('Aaron')
   ,('Peter')
   ,('David')
   ,('Abel')
   ,('Adam')
   ,('Brian')

Now i will go ahead and create a table valued function to get the table with required number of records.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION FN_TakeNRows
(
 @startRowNo INT = 1
 ,@endRowNo INT = 1
)
RETURNS 
@filteredRows TABLE 
(
 ID INT
 ,Name VARCHAR(50)
 ,UniqueID INT
)
AS
BEGIN
 
 INSERT INTO @filteredRows
 (
  ID,
  Name,
  UniqueID
 )
 SELECT TOP (@endRowNo) allRows.ID, allRows.Name, allRows.uniqueID
 FROM
 (
  SELECT 
   ID
   ,Name
   ,ROW_NUMBER() over(order by ID) as uniqueID
  FROM Employee
 ) allRows
 WHERE allRows.uniqueID > @startRowNo
 
 RETURN;
END
GO

As in LINQ query we will pass the values 4 and 2 as shown below to get 2 employee records. Execute statement of the function is as below -

SELECT * FROM FN_TakeNRows (4, 2)

So here in the above execute statement similar to LINQ query it skips 4 employee records and takes 2 employee records.
The result is below -

Hope this article is useful. Please comment your feedback.

Open Xml Sdk 2.0 for microsoft office

Introduction
In this article i am going to explain about the common requirement of exporting the data to excel. Most of the product in the market now will be having this functionality for their business needs and there are many SDKs available too to fulfill this requirement.
When i got this requirement for my project i initially thought to use -  Interop Objects  (using Microsoft.Office.Interop.Excel). The drawback of this is - Since it is dependent on Microsoft Office after deploying to the production it expects Microsoft Office to be installed in the deployed environment. Sometimes it is feasible but most of the time it is not. Installing Microsoft Office only for this requirement does not make any sense isn't it ?
Now its time to go to other option - "Open XML SDK", which solves the above problem and its open source too :-)  


Little more on OpenXML
Open XML is an open and standardized format for Office files. The standards used are:

• ECMA-376   
• ISO/IEC 29500-1:2008   

The Open XML file formats use an open standard and are based on well-known technologies: ZIP and XML. Now lets move on to the code part -

OpenXML in Code
Lets create a sample MVC application to demonstrate this - 

• Create New Project -> ASP.Net Web Application -> MVC  ( here MVC is not mandatory as this article focus on OpenXML, since i am comfortable in that i am using it)
• In Home Controller, Index Action call ExportExcel() method like below - 

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

Now lets create a sample dataset with sample data to populate the excel file -

public DataTable GetDatatable()
{
 DataTable dt = new DataTable();

 dt.Columns.Add("FirstName", typeof(String));
 dt.Columns.Add("LastName", typeof(String));
 dt.Columns.Add("Address", typeof(String));

 DataRow dr1 = dt.NewRow();
 dr1["FirstName"] = "John";
 dr1["LastName"] = "Martin";
 dr1["Address"] = "CA";

 DataRow dr2 = dt.NewRow();
 dr2["FirstName"] = "Rick";
 dr2["LastName"] = "Fleming";
 dr2["Address"] = "OH";

 DataRow dr3 = dt.NewRow();
 dr3["FirstName"] = "David";
 dr3["LastName"] = "Langer";
 dr3["Address"] = "SA";

 dt.Rows.Add(dr1);
 dt.Rows.Add(dr2);
 dt.Rows.Add(dr3);

 return dt;
}

As you can see the first snippet of code, we have method called - ExportExcel() which is the parent method to export the excel. The definition of this method - 

public void ExportExcel()
{
 DataTable dt = GetDatatable();
 string strFileFullPath= "D:\\Test.xlsx";

 ExportExcelFile.CreateExcel(dt, strFileFullPath);
}

In the above method path of excel is passed, if excel file not found in that location then the new file will be created in that location or else existing file will be used.
So below is the code snippet of "CreateExcel" method and its child methods -

public static class ExportExcelFile
{

 public static bool CreateExcel(DataTable dt, string xlsxFilePath)
 {
  DataSet ds = new DataSet();
  ds.Tables.Add(dt);

  try
  {
   using (SpreadsheetDocument document = SpreadsheetDocument.Create(xlsxFilePath
                                               , SpreadsheetDocumentType.Workbook))
   {
    WriteToTExcelFile(ds, document);
   }
   return true;
  }
  catch (Exception ex)
  {
   return false;
  }

 }

 private static void WriteToTExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
 {
  spreadsheet.AddWorkbookPart();
  spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

  spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

  WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart("rIdStyles");
  Stylesheet stylesheet = new Stylesheet();
  workbookStylesPart.Stylesheet = stylesheet;

  uint worksheetNumber = 1;
  foreach (DataTable dt in ds.Tables)
  {
   string workSheetID = "Sheet" + worksheetNumber.ToString();
   string worksheetName = dt.TableName;

   WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart();
   newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

   newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

   WriteDataTableToExcel(dt, newWorksheetPart);
   newWorksheetPart.Worksheet.Save();

   if (worksheetNumber == 1)
    spreadsheet.WorkbookPart.Workbook
                                               .AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

   spreadsheet.WorkbookPart.Workbook.GetFirstChild()
                                               .AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
   {
    Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
    SheetId = (uint)worksheetNumber,
    Name = dt.TableName
   });

   worksheetNumber++;
  }

  spreadsheet.WorkbookPart.Workbook.Save();
 }


 private static void WriteDataTableToExcel(DataTable dt, WorksheetPart worksheetPart)
 {
  var worksheet = worksheetPart.Worksheet;
  var sheetData = worksheet.GetFirstChild();

  string cellValue = "";

  int numberOfColumns = dt.Columns.Count;
  bool[] IsNumericColumn = new bool[numberOfColumns];

  string[] excelColumnNames = new string[numberOfColumns];
  for (int n = 0; n < numberOfColumns; n++)
   excelColumnNames[n] = GetExcelColumnName(n);

  uint rowIndex = 1;

  var headerRow = new Row { RowIndex = rowIndex };  
  sheetData.Append(headerRow);

  for (int colInx = 0; colInx < numberOfColumns; colInx++)
  {
   DataColumn col = dt.Columns[colInx];
   AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
   IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || 
                                                         (col.DataType.FullName == "System.Int32");
  }

  double cellNumericValue = 0;
  foreach (DataRow dr in dt.Rows)
  {
   ++rowIndex;
   var newExcelRow = new Row { RowIndex = rowIndex };  
   sheetData.Append(newExcelRow);

   for (int colInx = 0; colInx < numberOfColumns; colInx++)
   {
    cellValue = dr.ItemArray[colInx].ToString();

    if (IsNumericColumn[colInx])
    {
     cellNumericValue = 0;
     if (double.TryParse(cellValue, out cellNumericValue))
     {
      cellValue = cellNumericValue.ToString();
      AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString()
                                                                      , cellValue, newExcelRow);
     }
    }
    else
    {
     AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue
                                                                      , newExcelRow);
    }
   }
  }
 }

 private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
 {
  Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
  CellValue cellValue = new CellValue();
  cellValue.Text = cellStringValue;
  cell.Append(cellValue);
  excelRow.Append(cell);
 }

 private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
 {
  Cell cell = new Cell() { CellReference = cellReference};
  CellValue cellValue = new CellValue();
  cellValue.Text = cellStringValue;
  cell.Append(cellValue);
  excelRow.Append(cell);
 }

 private static string GetExcelColumnName(int columnIndex)
 {
  if (columnIndex < 26)
   return ((char)('A' + columnIndex)).ToString();

  char firstChar = (char)('A' + (columnIndex / 26) - 1);
  char secondChar = (char)('A' + (columnIndex % 26));

  return string.Format("{0}{1}", firstChar, secondChar);
 }
}

Below is the file generated after exporting the data -

Hope this helps. Comment below in case of clarifications or doubts.

jquery validation - Client side validation using jquery in asp.net mvc

Introduction for "client side validation using jquery in asp.net mvc"

In most of the cases we are going to use Data Annotations for validation in MVC. Let's not forget the point of unobtrusive client-side validation which turned out to be a boon for the developers. In unobtrusive client-side validation, validation rules are defined using attributes added to the generated HTML elements. This article will focus on adding the validation rules from client side.

Let's get started

Now let's dive into the code part as how we can add the rules for the controls. Before that i need to discuss when we can use this type of approach – Consider a scenario where you are re-using the page with hide and show the controls of the page, eg : if one client wants textbox and another client wants dropdownlist for the same field in a same page and if the validation rules are different for both these controls then adding the rules from client side would be a better approach.

For enabling client side validation, we required to include following scripts in our view or layout page in the following order.

<script src="~/Scripts/jquery-{version}.min.js" type="text/javascript"></script>
<script src="~/Scripts/jquery.validate.min.js" type="text/javascript"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js" type="text/javascript"></script>

Now lets create a new project for this and lets add the controller, models, views and javascript for our project.

1. Create a new MVC project 
2. Add a new controller called "ProductController"
3. Add a new model called "ProductModel" and add the properties - Name, Description and Amount
4. Add a new view called "Index.html" for our product
5. Add a new javascript file for validation "productvalidation.js"

Now we will just set the validation rules for all the properties in the ProductModel in productvalidation.js file. Script for adding the validation rules for our controls in a page -

$(document).ready(function () {

    var regexAlpabetsOnly = /^[a-zA-Z\- ']*$/;
    var regexNumbersOnly = /^\d+$/;
   
    $('#Name').rules("add", {
       required: true,
       minlength: 2,
       messages: {
         required: "Please enter name",
         minlength: "At least 2 characters are mandatory"
       }
   });

   $('#Description').rules("add", {
      required: true,
      regex: regexAlpabetsOnly,
      maxlength : 20,
      messages: {
        required: "Please enter description",
        regex: "Please enter alphabets in description field",
        maxlength: "Exceeded maximum length of description"
      }
  });

   $('#Amount').rules("add", {
      required: true,
      regex: regexNumbersOnly,
      messages: {
        required: "Please enter Amount",
        regex: "Please enter numbers in amount field"
      }
   });

 });

As you see above nothing fancy all are straight forward and we have following validations -

• For "Name" property we are adding required field validation and minimum length validation.
• For "Description" property we are adding required field validation and regex validation to allow only alphabets.
• For "Amount" property we are adding required field validation and regex validation to allow only numbers.

All are fine but only concern here is error messages are hardcoded. So its always a better idea to get these texts from resource file instead of hardcoding.

Now lets add the resource file and move these texts into resource file. For brevity i have not given the lump steps here -

Once its added the next would be to get the resource file data to javascript and that again is easy. Better option to do this is serialize the resource file and pass the JSON object to javascript.

Lets do that now -

In Controller lets add a method which will return JSON object -

[HttpGet]
public JavaScriptResult GetResourceFileData()
{
 return ResourceSerialiser.GetResourceSerailizedData(Resource.ResourceManager);
} 

For Serializing the resource file data -

 
public static JavaScriptResult GetResourceSerailizedData(ResourceManager resourceManager)
  {
     string cacheName = string.Format
       ("ResourceJavaScripter.{0}", CultureInfo.CurrentCulture.Name);

     JavaScriptResult value = HttpRuntime.Cache.Get(cacheName) as JavaScriptResult;

     if (value == null)
     {
        JavaScriptResult javaScriptResult = CreateResourceJSON(resourceManager);
        HttpContext.Current.Cache.Insert(cacheName, javaScriptResult);
        return javaScriptResult;
     }

      return value;
  }

  static JavaScriptResult CreateResourceJSON(ResourceManager resourceManager)
  {
       ResourceSet defaultSet = resourceManager.GetResourceSet
             (CultureInfo.GetCultureInfo("en"), true, true);
       ResourceSet resourceSet = resourceManager.GetResourceSet
             (CultureInfo.CurrentCulture, true, true);

       var resourceBaseName = resourceManager.BaseName;
       var jsonObjectName = resourceBaseName.Substring(resourceBaseName.LastIndexOf(".") + 1);

       StringBuilder sb = new StringBuilder();
       sb.Append("[");
       sb.Append("{");

       foreach (DictionaryEntry dictionaryEntry in resourceSet)
            if (dictionaryEntry.Value is string)
           {
                string value = resourceSet.GetString
                    ((string)dictionaryEntry.Key) ?? (string)dictionaryEntry.Value;
                sb.AppendFormat("\"{0}\":\"{1}\"", dictionaryEntry.Key, Encode(value));
                sb.Append(",");
           }


        string script = sb.ToString();
        if (!string.IsNullOrEmpty(script)) 
             script = script.Remove(script.Length - 1);


        script += "}]";
   

        JavaScriptResult result = new JavaScriptResult { Script = script };
        return result;
    } 

    static string Encode(string val)
    {
         val = (val).Replace("\"", "\\\"").Replace('{', '[').Replace('}', ']');
         val = val.Trim();
         val = System.Text.RegularExpressions.Regex.Replace(val, @"\s", " ");
         return val;
    }

In javascript lets add a new AJAX call to get the JSON object from controller method -

$.ajax({
  cache: false,
  type: "GET",
  url: "Product/GlobalResourceFileData",
  async: false, 
  dataType: "json",
  success: function (resourceData) {
   resourceFilevalues = resourceData[0];
  }
 });

" resourceFilevalues " is global variable which is used to receive the JSON object in AJAX call given above.

Now the hardcoded values will be replaced by the resource file properties like below -

$('#Name').rules("add", {
  required: true,
  minlength: 2,
  messages: {
   required: resourceFilevalues.NameRequiredErrMessage,
   minlength: resourceFilevalues.NameMinLengthErrMessage
  }
 });

 $('#Description').rules("add", {
  required: true,
  regex: regexAlpabetsOnly,
  maxlength : 20,
  messages: {
   required: resourceFilevalues.DescriptionRequiredErrMessage,
   regex: resourceFilevalues.DescriptionRegexErrMessage,
   maxlength: resourceFilevalues.DescriptionMaxLengthErrMessage
  }
 });

 $('#Amount').rules("add", {
  required: true,
  regex: regexNumbersOnly,
  messages: {
   required: resourceFilevalues.AmountRequiredErrMessage,
   regex: resourceFilevalues.AmountRegexErrMessage
  }
 });

So hope this article is useful and i agree this is the longest post i have written :-)

Invalid range in character set javascript error

I faced this issue when i was working on IE 8 and this issue has driven me crazy.Since this is not a straight forward issue to fix, i tried to narrow down the problem and finally came to know the regular expression what i used for validation is the culprit for this.This issue would not have come if you are running your application in the high end browsers.

The line which was causing the problem -

var SpecialCharRegex = /^[0-9a-zA-Z',-\s]*$/;

After doing lot of googling i found the issue and the issue is "-" in this regular expression.This error is due to the hyphen, which is mistakenly being used to represent a range and is not properly escaped. To fix this lets add backslash before "-" and change the regular expression like this -

var SpecialCharRegex = /^[0-9a-zA-Z',\-\s]*$/;

Simple logic is either put "-" at the beginning or end of the character class or use backslash to do a regex escape.
It works like a charm !!

MVC override controller methods

As you have seen in my post -  Overriding RazorViewEngine in MVC here in this post also i am trying to override the path of the view but this time by overriding controller rather than creating the custom view engine.

Now lets dive in to the code part -

Here i am going to reuse the controllers and folders created in the last post and I will be creating a new controller named "BaseController.cs" and this will be inherited from "Controller" class. This BaseController is used as a base class for all the controllers like Home,Contact etc.

public class BaseController : Controller
{
     protected override ViewResult View(string ViewName, string masterName, object model)
     {
         ViewResult renderview = null;

         if (Session != null && Session["TestFolder"] != null)
         {
            if (System.IO.File.Exists(Server.MapPath(VirtualPathUtility.ToAbsolute("~/Views" 
                                  + Session["TestFolder"].ToString().Trim() + "/" + ViewName + ".cshtml"))))
            {
                renderview = base.View("~/Views/" + Session["TestFolder"].ToString().Trim()+ "/" + ViewName + ".cshtml"
                                  , masterName, model);
            }
            else
            {
                renderview = base.View("~/Views/Shared/" + ViewName + ".cshtml", masterName, model);
            }
   
         }
         else
         {
             renderview = base.View("~/Views/Shared/" + ViewName + ".cshtml", masterName, model);
         }

         if (renderview != null)
              return renderview;


         return base.View(ViewName, masterName, model);
     }
}

As you can see in the above code i am overriding the method  "View" of Controller class , which is been called on "return View()" in action. 

Note :  To get the value for "ViewName" parameter in the above method you have to make sure that you will pass the name of the view - return View("yourviewname") 

The above method searches the view in "AllViews" folder (Session["TestFolder"]) and in case view did not found in that location then it tries to search in "/Shared" folder.
For partial Views we have similar method -

protected override PartialViewResult PartialView(string viewName, object model)
{
 return base.PartialView(viewName, model);
}

So the main drawback of this approach is if in the view uses - "@HTML.Partial("partialviewname")" then both of the above methods would not be called then we need to go with approach of overriding razor view engine as I have shown in the last post. 

Overriding razor view engine

Introduction of overriding razor view engine

View Engines are used to render the HTML from your views to the browser. These View Engines can be customized in our own way. So this article focuses on customizing the default view engine.
There are basically 2 view engines 

• Razor View Engine - This is an popular view engine and it was introduced with MVC3. Namespace for Razor Engine is System.Web.Razor .Razor uses @ symbol as shown in the below code snippet -

@foreach(var item in Model) {
    <tr>
        <td>@item.ID</td>
    </tr>
}

• Web Form (ASPX) View Engine - This is the default view engine for the Asp.net MVC and was  included with Asp.net MVC from the beginning. Namespace for WebFormEngine is System.Web.Mvc.WebFormViewEngine. Below is the code snippet with WebformEngine syntax -

<% foreach(var item in Model) { %>
    <tr>
        <td> <%: item.ID %> </td>
    </tr>
<% } %>

How Razor View Engine Works 

Default View Engine searches the page in the corresponding Controller folder and in Shared folder under Views folder.

For an instance consider you have "Index" action (say it loads Index.cshtml) in the "Home" controller and by routing you have configured that to be loaded first when you run the application. 

In the above scenario "Index.cshtml" searched in "Home" folder and "Shared" folder under Views. 

Customizing Razor View Engine
So for the demonstration purpose lets add new folder called "AllViews" under Views folder. Now lets add "Index.html" inside this newly created folder. Below image shows my solution explorer view -

As you can see i removed the "Index.cshtml" view from "Home" folder. So whenever Index action of the home controller is called it should search the "Index.cshtml" in "AllViews" folder.

Below is the code to customize the path of view -

public class CustomViewEngine : RazorViewEngine
{

        public override ViewEngineResult FindView(ControllerContext controllerContext, string viewName, 
                              string masterName, bool useCache) 
        {
           string viewPath = string.Empty;
           string masterPath = string.Empty; 
           string fullPath = string.Empty;
           string controllerName = controllerContext.RouteData.Values["controller"].ToString();
           string actionName = controllerContext.RouteData.Values["action"].ToString();

           if (HttpContext.Current.Session != null && HttpContext.Current.Session["TestFolder"] != null)
           {
               string testFolder = HttpContext.Current.Session["TestFolder"].ToString();

               if (!string.IsNullOrWhiteSpace(testFolder))
               {
                    // Get the value for your config instead of hardcoding
                   viewPath = "~/Views/" + testFolder + "/" + viewName + ".cshtml"; 
                  //masterPath = "~/Shared/" + masterName;  // Use it if required

                   fullPath = HttpContext.Current.Server.MapPath(viewPath);

                  if (System.IO.File.Exists(fullPath))
                  {
                       return new ViewEngineResult(base.CreateView(controllerContext, viewPath, 
                                                     masterPath), this);
                  }

               }
           }
           
           //Search in default Location "ControllerName/ViewName -- overriding razor view engine
           viewPath = "~/Views/" + controllerName + "/" + viewName + ".cshtml";
           return new ViewEngineResult(base.CreateView(controllerContext, viewPath, masterPath), this);
       }
 }

So as per the code priority is given to "AllViews" folder if view not found in that path then it searches in the default locations as discussed above. And as you can see "Session["TestFolder]" which has value "AllViews". 
Now we have to configure this custom view engine in Global.asax file

protected void Application_Start()
  {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            // Configuration Starts Here
            ViewEngines.Engines.Clear();
            ViewEngines.Engines.Add(new CustomViewEngine());
  }

Similarly for Partial View we use the below method-

 
public override ViewEngineResult FindPartialView(ControllerContext controllerContext, 
                                                       string partialViewName, bool useCache)
  {
         //Do Something Here
  }

Custom Model Binder in MVC

Introduction

Model Binding is one of the fundamental feature of MVC and this is used to convert form values, route data values or any value from a datasource in Model objects. Defaultmodelbinder is internally used to bind the model when you post the data to server, which is derived from “IModelBinder” interface.


How it Works

Lets create a sample to demonstrate how this works. And Obviously in our project we will get the data from database but during this exercise i will hardcode the data in controller itself because this article mainly focuses on Model Binding.

   1.Lets add a new MVC Project as below -

2. Create a Controller called – “EmployeeController.cs” and view corresponding to this controller and call it as “Index.cshtml”.

3. Now lets create a class called “Employee.cs” and i will take arsenal club player names for Employee Names because i just love them :-)

 public class Employee
 {
    public int EmpID { get; set; }
    public string EmpFirstName { get; set; }
    public string EmpLastName { get; set; }
    public string EmpCountry { get; set; }

    public static List GetEmployees()
    {
       return new List
       {
           new Employee { EmpID = 1, EmpFirstName = "Alexis", EmpLastName = "Sanchez", EmpCountry= "Chile"},
           new Employee { EmpID = 2, EmpFirstName = "Jack", EmpLastName = "Wilshere", EmpCountry= "England"},
           new Employee { EmpID = 3, EmpFirstName = "Aaron", EmpLastName = "Ramsey", EmpCountry= "Wales"},
           new Employee { EmpID = 4, EmpFirstName = "Theo", EmpLastName = "Walcott", EmpCountry= "England"}
                           
        };
        
     }
 }

4. Now its time to create a viewmodel for our view. Lets call it as “EmpViewModel.cs”

public class EmpViewModel
{
   public List Employees { get; set; }
}

5. So in my EmployeeController i will add 2 action methods one for “GET” and another for “POST”. 

public class EmployeeController : Controller
 {
    public ActionResult Index()
    {
        EmpViewModel vwModel = new EmpViewModel()
        {
            Employees = Employee.GetEmployees()
        };

        return View(vwModel);
     }

     [HttpPost]
     public ActionResult Index(EmpViewModel vwModel) 
     {
        return Content("Works !!");
     }

  }

6. In my Index.cshtml i will add the fields which are to be displayed or to be edited.

@model PartialViewsBinding.Models.EmpViewModel
           
@{
    ViewBag.Title = "Home Page";
}

@using (Html.BeginForm())
{
    for (int i = 0; i < Model.Employees.Count; i++)
    {
        @Html.HiddenFor(m => m.Employees[i].EmpID)

        @Html.TextBoxFor(m => m.Employees[i].EmpFirstName)
        @Html.TextBoxFor(m => m.Employees[i].EmpLastName)
        @Html.TextBoxFor(m => m.Employees[i].EmpCountry)

    }
    
   <input type="submit" value="Click Here" /> 

}

You can check the below screenshot of Index.cshtml during runtime -

Now i will change the names and i will check in the action method whether i will be getting the same and  my model binding properly or not.

In above figure i made sure the data binding properly to my action method. 

So now coming to the main topic "Custom Model Binder", for demonstrating this lets create a new property in Employee class and lets call it as  "EmpName". Intention behind adding this property would be just to bind this property from "FirstName" and "LastName"  of employee. (Please note : This is just for demonstration. This would not be the scenario in your live projects.)
Now i am going to add the custom binder for this -   

 public class FullNameModelBinder : DefaultModelBinder
  {
        public override object BindModel(ControllerContext controllerContext,
                             ModelBindingContext bindingContext)
        {
            int i = 0;
            List emps = new List();
            HttpRequestBase request = controllerContext.HttpContext.Request;

            while (!string.IsNullOrEmpty(request.Form["Employees[" + i + "].EmpID"]))
            {
                var emp = new Employee();
                emp.EmpID = int.Parse(request.Form["Employees[" + i + "].EmpID"]);
                emp.EmpName = request.Form["Employees[" + i + "].EmpFirstName"] + " " + request.Form["Employees[" + i + "].EmpLastName"];
                emp.EmpCountry = request.Form["Employees[" + i + "].EmpCountry"];

                emps.Add(emp);
                i++;
            }

            return new EmpViewModel()
            {
               Employees = emps
            };
        }
   }

Now add the entry in Global.asax file to intimate the compiler about custom binder.

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

    WebApiConfig.Register(GlobalConfiguration.Configuration);
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
    AuthConfig.RegisterAuth();

    ModelBinders.Binders.Add(typeof(EmpViewModel), new FullNameModelBinder());
  }

Now add the binder in the Post method to get the name based on firstname and lastname.

// ([ModelBinder(typeof(FullNameModelBinder))]EmpViewModel vwModel)

     [HttpPost]
    public ActionResult Index([ModelBinder(typeof(FullNameModelBinder))]EmpViewModel vwModel)
    {
        return RedirectToAction("Index");
    }

Done !!! now the property EmpName has been bound from FirstName and LastName. 

go keyword in sql server 2008

Till now we know that “GO” keyword been used as a batch separator(which is default configured). "GO" is not a statement but a command to the server to commit the current batch to the Database.You can not use "GO" inside a stored procedure. If you would try, the definition of the procedure will end there, and the rest will be a separate batch. 

 Now lets see what you can do with this keyword.

In the above query i am simply creating a temparory table and i am trying to use “GO” keyword which will execute the insert statement 3 times and adds the value “Test” into the column “Name” of @abhiTable.

Same thing if i try for Table Variable (Assuming you know the difference between Temporary Table and Table Variable) the same thing does not work because, SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

Hope this helps.

Dynamic order by clause in sql server

Introduction

The ORDER BY keyword is used to sort the result set by one or multiple columns and it sorts the records in ascending order by default. To sort the records in a descending order we have to use the DESC keyword.

Order By Columns Dynamically

So here i am going to explain how we can use Order By clause to sort the column values dynamically.

Lets create a sample table and populate the values in that -

DECLARE @employee TABLE
( 
 ID INT IDENTITY(1,1)
 ,Name VARCHAR(50)
 ,Gender VARCHAR(20)
 ,DOB DATETIME
 ,TotProjects INT
)

So here the column which is to be sorted and direction of sorting is passed as a variable. So the above table contains columns “Name” which is VARCHAR, “DOB” which DATETIME and “TotProjects” which is INT. So this is intentional to have different datatypes of columns in my table. 

So now lets use the query to sort the column values dynamically –

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'ASC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
      THEN
        CASE 
           WHEN @searchCol = 'NAME' THEN Name 
           WHEN @searchCol = 'GENDER' THEN Gender
           WHEN @searchCol = 'DOB' THEN DOB
           WHEN @searchCol = 'PROJECTS' THEN TotProjects
        END 
  END DESC,
  CASE WHEN @direction = 'ASC'
      THEN
          CASE 
             WHEN @searchCol = 'NAME' THEN Name 
             WHEN @searchCol = 'GENDER' THEN Gender
             WHEN @searchCol = 'DOB' THEN DOB
             WHEN @searchCol = 'PROJECTS' THEN TotProjects
          END
  END ASC

So as in the above figure you can see “CASE” statement is being used in Order By clause. As you observe the columns are sorted based on variable – “@direction“. Column name is also passed from an variable called – “@searchCol“.

So when i tried to execute this with @searchCol setting to “NAME” and @direction setting to “ASC” i will get the below error -

When i googled this, i got multiple links and many of them says – “If you have multiple datatypes in the same CASE statement then it causes this problem“.

So after seeing this i changed my query to have same datatype and it looks like this -

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END 
 END DESC,
 CASE WHEN @direction = 'ASC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END
 END ASC

So in the above screenshot you can see i have used “CAST” to change the datatype of “DOB” and “TotProjects” so now all conditions returns same VARCHAR type. Now i will execute this query with same old paramters  "NAME" and "DESC" - it works.

Now i will execute the same query with @searchCol – “PROJECTS” and @direction - “DESC“. Now i will get results like this -

Oh ho !! it did not work !! Seems like “TotProjects” are not sorting properly.

Now we will go with one more solution to this problem -

DECLARE @searchCol VARCHAR(50) = 'PROJECTS' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @searchCol = 'NAME' AND @direction = 'ASC' THEN Name END ASC,
 CASE WHEN @searchCol = 'NAME' AND @direction = 'DESC' THEN Name END DESC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'ASC' THEN Gender END ASC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'DESC' THEN Gender END DESC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'ASC' THEN DOB END ASC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'DESC' THEN DOB END DESC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'ASC' THEN TotProjects END ASC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'DESC' THEN TotProjects END DESC

and this query works for all datatypes and no where we are changing the datatype. In the above query we are checking both direction and sort column in one condition and it runs good. So performance wise also it works good.

Hope it helps.

Sql server context_info trigger

What is Trigger ?

A trigger is a database object that is attached to a table or we can define trigger as a stored procedure that automatically executes when an event occurs in the database server.

Triggers are classified into two types mainly-

• Instead of Trigger
• After Trigger

How can i access the tables in Triggers

When we update a record on the table where trigger is created, the magic tables “INSERTED” and “DELETED” both will be created, the Old data of the updating record will be available in “DELETED” table and, the new data will be available in “INSERTED” table, while accessing them inside the trigger. These tables are called magic tables of the SQL Server. We can not see these tables in the data base. But we can access these tables from the trigger.

How can i pass the value to Trigger ?

So now we are coming to the point where i need to send the data to trigger. So in case tables “INSERTED” and “DELETED” does not hold the data, and you still need to pass the data from either from parameters of stored procedure or from some variables which are evaluated in stored procedure then you are in right place :-)

Here i am creating the Test tables which are required for the demo. Below is the screenshot of the tables which are added newly in my “Testing” Database.

Now the intension would be to insert a record in table “DataCheck” when some record is inserted in to table “Test“. So that means i am creating a trigger on table “Test” to insert the record in table “DataCheck“.

As you can see table “Test” has columns – ID, NAME
and table “DataCheck” has columns – UserID and Name.

So now lets create a trigger on table “Test“ -

CREATE TRIGGER USP_TR_Test ON dbo.Test
FOR INSERT
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE 
  @commaseparatedStr VARCHAR(200),
  @useridStr VARCHAR(200),
  @nameStr VARCHAR(200)


 SELECT @commaseparatedStr = CAST(CONTEXT_INFO() AS VARCHAR)
 FROM master.dbo.SYSPROCESSES
 WHERE SPID = @@SPID

 SELECT 
  @useridStr = substring(@commaseparatedStr,1,CHARINDEX(',',@commaseparatedStr)- 1),
  @nameStr = substring(@commaseparatedStr,CHARINDEX(',',@commaseparatedStr) + 1, len(@commaseparatedStr) - 1)


 INSERT INTO DataCheck (
  NAME
  ,UserID
 )
 SELECT 
  SUBSTRING(@namestr,CHARINDEX('=',@namestr) + 1, len(@namestr) - 1),
  CAST(SUBSTRING(@useridstr,CHARINDEX('=',@useridstr) + 1, len(@useridstr) - 1) AS INT)
END
GO

So basically it inserts data into table “DataCheck“.

Now lets create a stored procedure which inserts data into table “Test” and calls up trigger on the same table.

CREATE PROCEDURE USP_RP_InsertToTest 
(
 @intUserID INT, 
 @name VARCHAR(50)
)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @CONTEXT_INFO VARBINARY(128)

 SET @CONTEXT_INFO = CAST('UserID=' + CONVERT(VARCHAR(10), @intUserID) + ',' + 'Name =' + @name AS VARBINARY(128))
 SET CONTEXT_INFO @CONTEXT_INFO

 INSERT INTO Test (NAME)
 VALUES (@name)
END
GO

Here we are using keyword called “CONTEXT_INFO” so this is used to retrieve the context information for the current session and session context information is also stored in the context_info columns.

So “SET CONTEXT_INFO variablename” is used to set the context info.

In the trigger if you observe i am using “CONTEXT_INFO()” to fetch the data from context info and insert into “DataCheck“.

Note that the data i am setting to context info is coming as parameters in stored procedures.

Now you just execute the stored procedure created above like this –

and observe that in table “DataCheck” one record is inserted -

Hope it helped.