CRM Difference between Secure & Unsecure Configuration in Plugin Registration tool

Dear all,

Read original text  from the URL

Secure Configuration of Plugin Registration tool in CRM	Unsecure Configuration of Plugin Registration tool in CRM
The Secure Configuration information could be read only by CRM Administrators.(Eg: Restricted data from normal user could be supplied here)	Unsecure configuration information could be read by any user in CRM. Remember its public information (Eg: Parameter strings to be used in plugin could be supplied here)
Imagine that you include a plugin,plugin steps and activate them in asolution. Later solution was exportedas Managed Solution to anotherenvironment. In this scenario, thesupplied Secure configuration  information would NOTbe available in the new environment. The simple  reason behind this is to provide more security to the contents of Secure Configuration.	Imagine that you include a plugin, plugin steps and activate them in a solution. Later solution was exported as Managed Solution to another environment. In this scenario, the supplied Unsecure configuration values would be available in the new environment.

One of the benefits to the plug-in architecture of CRM 4.0 is the ability to store plug-ins in the CRM database so they may be used by multiple CRM servers. This introduces a slight complication regarding the storage of configuration information. Because the plug-in assembly doesn’t reside on the disk the normal method of using a .config file located with the assembly no longer works.

Luckily, the plug-in architecture solves this issue by allowing the developer to supply configuration information for each step executed by the plug-in.

Plug-in Configuration Architecture

As noted in the CRM SDK article, Writing the Plug-in Constructor, when creating your plug-in, you may define a constructor that passes two parameters to your plug-in: unsecure configuration and secure configuration:

   1: public class SamplePlugin : IPlugin

   2: {

   3:   public SamplePlugin(string unsecureConfig, string secureConfig)

   4:   {

   5:   }

   6: }

Both parameters are strings and may contain any configuration data, in any format, that you wish. For the purposes of this discussion, we will only be concerned with the unsecure configuration parameter.

Creating a Configuration Structure

Since most of us are familiar with the XML configuration provided by the standard Properties.Settings structure, I thought it would be a great idea to retain as much of that experience as possible so we can move code from a stand-alone test application to a plug-in with little difficulty.

Using an XML fragment that closely resembles the Settings section found in the .config file of a .Net assembly, we can create a similarly functional system for storing configuration data. Consider the following XML:

   1:

   2:     "RetryCount">

   3:         <value>5</value>

   4:    

   5:     "TaskPrefix">

   6:         <value>This task was created on {0}.</value>

   7:    

   8:     "FirstRun">

   9:         <value>false</value>

  10:    

  11:

As you can see, we have three settings which contain values that we would normally find in our .config file and which are used to configure our assembly. Using the Plug-in Registration Tool, we can add this information to the Unsecure Configuration field when registering a new step, as show by the figure below:

Plug-in Configuration Class

I created a simple class to extract values from an XML document for simple data types such as Guids, strings, Booleans, and integers, given the structure we discussed above:

   1: class PluginConfiguration

   2: {

   3:     private static string GetValueNode(XmlDocument doc, string key)

   4:     {

   5:         XmlNode node = doc.SelectSingleNode(String.Format("Settings/setting[@name='{0}']", key));

   6: 

   7:         if (node != null)

   8:         {

   9:             return node.SelectSingleNode("value").InnerText;

  10:         }

  11:         return string.Empty;

  12:     }

  13: 

  14:     public static Guid GetConfigDataGuid(XmlDocument doc, string label)

  15:     {

  16:         string tempString = GetValueNode(doc, label);

  17: 

  18:         if (tempString != string.Empty)

  19:         {

  20:             return new Guid(tempString);

  21:         }

  22:         return Guid.Empty;

  23:     }

  24: 

  25:     public static bool GetConfigDataBool(XmlDocument doc, string label)

  26:     {

  27:         bool retVar;

  28: 

  29:         if (bool.TryParse(GetValueNode(doc, label), out retVar))

  30:         {

  31:             return retVar;

  32:         }

  33:         else

  34:         {

  35:             return false;

  36:         }

  37:     }

  38: 

  39:     public static int GetConfigDataInt(XmlDocument doc, string label)

  40:     {

  41:         int retVar;

  42: 

  43:         if (int.TryParse(GetValueNode(doc, label), out retVar))

  44:         {

  45:             return retVar;

  46:         }

  47:         else

  48:         {

  49:             return -1;

  50:         }

  51:     }

  52: 

  53:     public static string GetConfigDataString(XmlDocument doc, string label)

  54:     {

  55:         return GetValueNode(doc, label);

  56:     }

  57: }

Putting PluginConfiguration to Work

Once we have our PluginConfiguration class added to our project, we need to modify the plug-in constructor to extract the values from our configuration string:

   1: public SamplePlugin(string unsecureConfig, string secureConfig)

   2: {

   3:     XmlDocument doc = new XmlDocument();

   4:     doc.LoadXml(unsecureConfig);

   5: 

   6:     string TaskPrefix = PluginConfiguration.GetConfigDataString(doc, "TaskPrefix");

   7:     bool FirstRun = PluginConfiguration.GetConfigDataBool(doc, "FirstRun");

   8:     int RetryCount = PluginConfiguration.GetConfigDataInt(doc, "RetryCount");

   9: }

There is no automatic determination of data types so you will need to know which method to use to extract a specific value from the configuration data.

have a glance below for plugin code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using System.Xml;

namespace ReadPluginConfgInPlugins
{
public class PluginConfg : IPlugin
{
private readonly string _unsecureString;
private readonly string _secureString;

public PluginConfg(string unsecureString, string secureString)
{
if (String.IsNullOrWhiteSpace(unsecureString) || String.IsNullOrWhiteSpace(secureString))
{
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(unsecureString);
Guid DefaultQueueGuid = PluginConfiguration.GetConfigDataGuid(doc, “Contact Guid”);
string ContactFullName = PluginConfiguration.GetConfigDataString(doc, “Full Name”);
int MobileNumber = PluginConfiguration.GetConfigDataInt(doc, “Mobile Number”);
bool Gender = PluginConfiguration.GetConfigDataBool(doc, “Gender 0 refer to Male”);
}
catch (Exception ex)
{
throw new Exception(“SoapException” + ex.Message + “########” + ex.StackTrace + “$$$$Inner Exception” + ex.InnerException);
}
}
}

public void Execute(IServiceProvider serviceProvider)
{
//Extract the tracing service for use in debugging sandboxed plug-ins.
ITracingService tracingService =
(ITracingService)serviceProvider.GetService(typeof(ITracingService));

// Obtain the execution context from the service provider.
IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

// For this sample, execute the plug-in code only while the client is online.
tracingService.Trace(“AdvancedPlugin: Verifying the client is not offline.”);
if (context.IsExecutingOffline || context.IsOfflinePlayback)
return;

// The InputParameters collection contains all the data passed
// in the message request.
if (context.InputParameters.Contains(“Target”) &&
context.InputParameters["Target"] is Entity)
{
Entity entity = (Entity)context.InputParameters["Target"];

}

}
}

}

For this approach also have a Pros and Cons.

PROS:

• The step configuration is solution-aware so it will be automatically transported with the plugin step.

CONS:

• You need to use the plugin registration tool or another application to update the step configuration.
• The configuration is step-specific so you have to provide it and/or update it for every step even if the value is the same for all the steps (the configuration is on each step instead of the assembly or plugin type).
• the configuration is just an attribute of the plugin step so you cannot control privileges on the configuration independently from privileges on plugin step entity.

*. Use the plugin step “Secure Configuration”
This is similar to the step Configuration except that the configuration data is stored in a separate entity which can be secured.
PROS:

• The configuration data can be secured as any other entity using the CRM security model. This is useful when the configuration contains sensitive information such as passwords.

CONS:

• Secure configuration is not solution aware so you will need to configure it for each environment.
• You need to use the plugin registration tool or another application to update the step configuration.
• The configuration is step-specific so you have to provide it and/or update it for every step even if the value is the same for all the steps (the configuration is on each step instead of the assembly or plugin type).

*. Use a Web Resource
You can store configuration information in web resources, for example you might have some XML configuration stored in a web resource and have your plugin read the web resource each time it executes.

PROS:

• Web resources are solution aware and the GUID is preserved across environments so you can hardcode the web resource GUID in your plugin code. You can transport the web resource and the plugin in the same solution.
• Can be easily updated using the CRM UI.

CONS:

• You cannot secure the configuration since it depends on the web resource access privileges and most users will need at least read access to web resources.

Create a plugin in MS CRM, How to start a plugin in MS CRM?

Please find the following line of code to start a plugin either in MS CRM 4.0 or 2011.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;

namespace CRM2011Plugin
{
    public class vpscrmActivateQuote:IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                // obtain target entity from context
                Entity entity = context.InputParameters["Target"] as Entity;
                //check if the entity name is lead
                if (entity.LogicalName != "quote")
                { return; }
                else
                {
                    IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                    IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
                    if (entity.Attributes.Contains("statuscode"))
                    {
                        if (entity.GetAttributeValue<OptionSetValue>("statuscode").Value == 2)
                        {
                            //Retrieve data.
                            var trackingOptionObj = entity.GetAttributeValue<OptionSetValue>("new_trackingoption");
                            var accountId = entity.GetAttributeValue<Guid>("accountid");
                            var newCustomerStatus = entity.GetAttributeValue<OptionSetValue>("new_customerstatus").Value;
                            var current_syn_customerid = entity.GetAttributeValue<string>("new_customerid");
                            var current_syn_debtorclassid = entity.GetAttributeValue<EntityReference>("new_customerclass").Id.ToString();

                            //Add attribute
                            Guid contactId = Guid.Empty; //sepcify value
                            entity.Attributes.Add("primarycontactid", new EntityReference("contact", contactId));

                            updateDetailsinNet4DB(context, service, entity, "Yes", entity.GetAttributeValue<string>("name"), entity.GetAttributeValue<EntityReference>("customerid").Id.ToString());
                        }
                    }
                }
            }
        }
    }
}

Could not load file or assembly 'Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencie

Problem : Could not load file or assembly 'Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencie 

Reason:

"Windows identity foundation pack" may be missed from your machine.

Solution:

Download the pack from http://www.microsoft.com/downloads/details.aspx?FamilyID=eb9c345f-e830-40b8-a5fe-ae7a864c4d76&displaylang=en and install it in your Windows 7/ Windows server 2008 R2 machine.

Using INSTEAD OF triggers in SQL Server for DML operations

For more information please Click Here (This is the copy of the given link for my refrence)

ProblemI have created some views to provide limited data access for an application. Several of these views are composed of more than one table and there are problems while performing insert, delete, or update operations on multi-base table views. I am required to seamlessly manage such problems, so that developers do not get direct access to the base tables for DML operations. In SQL Server 2000 and onwards there are INSTEAD OF triggers that can be used to carry out such tasks. Although these types of triggers can be used in a number of scenarios their primary function is to perform DML operations through such views. It looks like a powerful capability and I am interested to know about the implementation of INSTEAD OF triggers for various DML operations.
Solution
In SQL Server 2000 and later versions there are two types of DML triggers

• AFTER triggers
• INSTEAD OF triggers

Both of these work for insert, delete and update operations. Triggers created with FOR or AFTER keywords are both AFTER triggers. AFTER triggers do not work for views, so we will discuss the properties and functionality of INSTEAD OF triggers.
INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. Inserted and deleted tables are widely used in operations inside triggers and I will show you some examples below . We will discuss further aspects of INSTEAD OF triggers by going through some examples for DML operations.

SETUP
In the following script we will create and populate two base tables and a create a view over both tables.

USE AdventureWorks
GO

-- Create table for employees
CREATE TABLE Employees 
(EmpCode VARCHAR(8) PRIMARY KEY, Name VARCHAR(50) NOT NULL, 
Designation VARCHAR(50) NOT NULL, QualificationCode TINYINT, 
Deleted BIT NOT NULL DEFAULT 0)
GO

-- Create look up table for employees qualification
CREATE TABLE Lib_Qualification 
(QualificationCode TINYINT PRIMARY KEY, Qualification VARCHAR(20) NOT NULL)
GO

-- Add constraint to lib_qualification
ALTER TABLE dbo.Lib_Qualification ADD CONSTRAINT
FK_Lib_Qualification_Lib_Qualification FOREIGN KEY
( QualificationCode ) REFERENCES dbo.Lib_Qualification
( QualificationCode ) ON UPDATE NO ACTION 
ON DELETE NO ACTION 
GO 

-- Add constraint to employees 
ALTER TABLE dbo.EMPLOYEES ADD CONSTRAINT
FK_EMPLOYEES_Lib_Qualification FOREIGN KEY
( QualificationCode ) REFERENCES dbo.Lib_Qualification
( QualificationCode ) ON UPDATE NO ACTION 
ON DELETE NO ACTION 
GO

-- Insert data into lib_qualification table
Insert into lib_qualification VALUES (1, 'MS')
Insert into lib_qualification VALUES (2, 'MCS')
Insert into lib_qualification VALUES (3, 'BCS')
Insert into lib_qualification VALUES (4, 'MBA')
GO

-- Insert data into employees table
Insert into Employees VALUES ('405-21-1' ,'Emp1' ,'Designation1' ,1 ,0)
Insert into Employees VALUES ('527-54-7' ,'Emp2' ,'Designation2' ,2 ,0)
Insert into Employees VALUES ('685-44-2' ,'Emp3' ,'Designation3' ,1 ,0)
Insert into Employees VALUES ('044-21-3' ,'Emp4' ,'Designation4' ,3 ,0)
Insert into Employees VALUES ('142-21-9' ,'Emp5' ,'Designation5' ,2 ,0)
GO

-- Create view by two base tables
CREATE VIEW vw_EmpQualification
AS
SELECT EmpCode, Name, Designation, Qualification
FROM employees E inner join lib_qualification Q
ON E.qualificationCOde = Q.QualificationCode
WHERE deleted = 0
GO 

Select * from vw_EmpQualification
GO

Now we are ready to create the INSTEAD OF trigger for insert, delete and update operations on view vw_empQualification.
In the examples I have chosen simple and common scenarios, but there is much that can be done using INSTEAD OF triggers in SQL Server.

INSTEAD OF INSERT Trigger for Insert operation
Our view is comprised of two base tables. If someone tries to insert values using the view the following error will be generated and the insert will fail.

Msg 4405, Level 16, State 1, Line 1 View or function 'vw_EmpQualification' is not updatable because the modification affects multiple base tables.

At this point, the INSTEAD OF INSERT trigger provides us several options to handle insert operations on this view. For our example we will allow the users to insert data through this view, by having the trigger handle some logic for data integrity.
The INSTEAD OF INSERT trigger will be created using the following script for Insert operations

USE AdventureWorks
GO

CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN

DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification

IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

We have just used the key word INSTEAD OF versus using the FOR or AFTER keyword in the trigger header and our required INSTEAD OF INSERT trigger has been created. Now we will verify the insert statement on this view which has two base tables. The INSTEAD OF trigger will seamlessly handle the insert operation in the trigger without an error.

USE AdventureWorks
GO 

-- Insert data in view
INSERT INTO vw_EmpQualification VALUES ('425-27-1', 'Emp8','Manager','MBA')
GO

-- To confirm the data insertion
SELECT * FROM vw_EmpQualification
GO

Inserted data is handled in the trigger and our inserted row is shown in the selected data below.

INSTEAD OF UPDATE Trigger for update operation
There may be several scenarios where using INSTEAD of triggers can solve this problem. In the case of views with multiple base tables, you may only issue update statements that affect a single base table at a time. If any update statement on our view affects multiple base tables at a time then the following error would be generated

Msg 4405, Level 16, State 1, Line 1 View or function 'vw_EmpQualification' is not updatable because the modification affects multiple base tables.

The following script is for an INSTEAD OF UPDATE trigger is used to provided seemless update capability for multiple base tables.

USE AdventureWorks
GO

CREATE TRIGGER INSTEADOF_TR_U_EmpQualification 
ON vw_EmpQualification
INSTEAD OF UPDATE AS
BEGIN

IF (UPDATE(qualification)) -- If qualification is updated
BEGIN
DECLARE @code TINYINT
UPDATE employees
SET @code = L.qualificationcode 
FROM lib_qualification L INNER JOIN inserted I 
ON L.qualification = I.qualification
IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

UPDATE employees
SET qualificationCode = @code
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode
END

IF (UPDATE(EmpCode)) -- If employee code is updated
BEGIN
RAISERROR (N'You can not edit employee code, Transaction has been failed', 16, 1)
RETURN
END
IF (UPDATE(name)) -- If name is updated 
BEGIN
UPDATE employees
SET name = I.name 
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode 
WHERE E.empcode = I.empcode
END 

IF (UPDATE(designation)) -- If designation is updated
BEGIN
UPDATE employees
SET designation = I.designation 
FROM inserted I INNER JOIN employees E ON I.empcode = E.empcode 
WHERE E.empcode = I.empcode
END
END
GO

Now we can verify the proper functioning of our trigger for an update statement.

USE AdventureWorks
GO 

-- Update data in view
UPDATE vw_EmpQualification
SET designation = 'Designation4 Updated', Qualification = 'MCS'
WHERE empcode = '044-21-3'
GO

-- To confirm the data update
SELECT * FROM vw_EmpQualification
GO

The INSTEAD OF UPDATE trigger handled the update and the update is reflected in the selected data below.

INSTEAD OF trigger for delete operation
INSTEAD OF trigger may be attached for delete operations. In our case we are required that when rows are deleted through the view, a deleted flag in the table should be marked "1" for those rows, but rows should not actually be deleted. Such rows may be deleted in bulk later at specified time if needed. For this we may create the following INSTEAD OF DELETE trigger.

USE AdventureWorks
GO 

CREATE TRIGGER INSTEADOF_TR_D_EmpQualification 
ON vw_EmpQualification
INSTEAD OF DELETE AS
BEGIN
update employees
set deleted = 1
where empcode in (select empcode from deleted)
END
GO

To verify the implementation for deletes the following script can be used

USE AdventureWorks
GO 

-- Delete data in view
DELETE FROM vw_EmpQualification
WHERE Designation = 'Manager'
GO

-- To confirm the data update
SELECT * FROM vw_EmpQualification
SELECT * FROM Employees
GO

The deleted row still exist in the base table, but it is not shown in the view because the deleted flag is set to "1" in the base table

Summary
After going through these examples for implementing INSTEAD OF triggers for insert, update and delete operations on a view, there are some considerations that are required to keep in mind while planning to implement INSTEAD OF triggers.

• If you do not specify the INSTEAD OF or AFTER keyword in the trigger header and just use the FOR keyword, then by default an AFTER trigger will be created.
• You can have only one trigger for each of insert, update or delete option on a single table or view.
• If you have created an INSTEAD OF trigger on a table then you can not use the cascade option as an UPDATE and DELETE rule. Cascade for delete rule will be prohibited if INSTEAD OF trigger is defined for delete operation and same is the case for update operations.
• If you have already used cascade for delete or update options for a table then you can not create an INSTEAD OF trigger for that specific DML operation on that table.
• INSTEAD OF triggers do not work in a recursive manner. Even if you update the same table inside the INSTEAD OF trigger, the trigger will not be invoked a second time. So INSTEAD OF triggers can be used to avoid recursion.
• You can define both AFTER and INSTEAD OF triggers for the same DML operation on the same table.
• If defined on an object, an AFTER trigger can be invoked as a result of DML operations from inside the INSTEAD OF trigger.
• The deleted table for an INSTEAD OF INSERT trigger is always empty and the inserted table for INSTEAD OF DELETE trigger is always empty.

SQL Server CROSS APPLY and OUTER APPLY

For more detailed Information please Click Hare

ProblemSQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.

SolutionThe APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only. Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

Script #1 creates a Department table to hold information about departments. Then it creates an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table.

USE [tempdb]
GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) BEGIN
DROP TABLE [Employee] END GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U')) BEGIN
DROP TABLE [Department] END
CREATE TABLE [Department](
[DepartmentID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY] INSERT [Department] ([DepartmentID], [Name]) VALUES (1, N'Engineering') INSERT [Department] ([DepartmentID], [Name]) VALUES (2, N'Administration') INSERT [Department] ([DepartmentID], [Name]) VALUES (3, N'Sales') INSERT [Department] ([DepartmentID], [Name]) VALUES (4, N'Marketing') INSERT [Department] ([DepartmentID], [Name]) VALUES (5, N'Finance') GO CREATE TABLE [Employee](
[EmployeeID] [int] NOT NULL PRIMARY KEY,
[FirstName] VARCHAR(250) NOT NULL,
[LastName] VARCHAR(250) NOT NULL,
[DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
) ON [PRIMARY]
GO INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (1, N'Orlando', N'Gee', 1 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (2, N'Keith', N'Harris', 2 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (3, N'Donna', N'Carreras', 3 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (4, N'Janet', N'Gates', 3 )

Department and employee table information

First query in Script #2 selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced.

SELECT * FROM Department D CROSS APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

If you look at the results they produced, it is the exact same result-set; not only that even the execution plan for these queries are similar to each other and has equal query cost, as you can see in the image below. So what is the use of APPLY operator? How does it differ from a JOIN and how does it help in writing more efficient queries. I will discuss this later, but first let me show you an example of OUTER APPLY also.

The first query in Script #3 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table. As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table.

SELECT * FROM Department D OUTER APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO SELECT * FROM Department D LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (which has an estimated operator cost of 0.0000103 or almost 0% of total query cost) before Nested Loops operator to evaluate and produce the columns of Employee table.

Now comes the time to see where the APPLY operator is really required. In Script #4, I am creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function we created. It passes the DepartmentID for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns. 

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF')) BEGIN
DROP FUNCTION dbo.fn_GetAllEmployeeOfADepartment END GO CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT) RETURNS TABLE
AS
RETURN
(
SELECT * FROM Employee E
WHERE E.DepartmentID = @DeptID
) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO

Now the output is excately same as above figure.

So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound." error. This is because with JOINs the execution context of outer query is different from the execution context of the function (or a derived table), and you can not bind a value/variable from the outer query to the function as a parameter. Hence the APPLY operator is required for such queries.

How to use XML DataType of SQL Server with C#.NET

Some time we required to insert XML data in sql server through the use of C#.NET, You can do it after execution the some line of code.

1) create an XML formated data in C# either using loop or from any other way, for eg.


2) create an stored procedure in sql server

CREATE PROCEDURE [dbo].[ProcedureName]

(

@XMLData XML

)

AS

BEGIN

UPDATE table SET PRICE_1YEAR=TempData.Item.value('@PRICE_1YEAR'

FROM @XMLData.nodes('/root/row') AS TempData(Item)

      WHERE ID=TempData.Item.value('@ID', 'INT') and PRODUCT_ID =TempData.Item.value('@PRODUCT_ID', 'INT')END

Case Sensitive Search on a Case Insensitive SQL Server

Question
Most of the SQL Server installations are installed with the default collation which is case insensitive. This means that SQL Server ignores the case of the characters and treats the string 'Harish Kumar' equal to the string 'harish kumar'. If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?
Answer
1) As you can see, this SQL Server has a case insensitive collation i.e. CI.
    SELECT SERVERPROPERTY ('Collation')
      Ans: SQL_Latin1_General_CP1_CI_AS
2) How can I just capture the rows with the mixed case
     SELECT *      FROM dbo.TableName WHERE Column1 LIKE '%Test%' Collate SQL_Latin1_General_CP1_CS_AS