looping with Pipe

WHILE LEN(@CandidateIds) > 0
BEGIN
IF PATINDEX('%|%',@CandidateIds) > 0
BEGIN
SET @CandidateId = CONVERT(BIGINT,(SUBSTRING(@CandidateIds, 0, PATINDEX('%|%',@CandidateIds))))
-- print @CandidateId
EXEC Proc @CandidateId,@FK_CorporateId,@FK_ProgramId,@Interview_DateTime,@Interview_Venue,@Interview_Remarks,@intInvitationStatus,@ReturnValue OUTPUT
SET @CandidateIds = SUBSTRING(@CandidateIds, LEN(CONVERT(VARCHAR(20),@CandidateId) + '|') + 1, LEN(@CandidateIds))

IF(@ReturnValue != 0 AND @ReturnValue != -1)
BEGIN
SET @DublicateCandidateIds = @DublicateCandidateIds + ',' + CONVERT(VARCHAR(20),@ReturnValue)
--print 'IF:' + @DublicateCandidateIds
END
END
ELSE
BEGIN
SET @CandidateId = CONVERT(BIGINT, @CandidateIds)
SET @CandidateIds = NULL
-- print @CandidateId
EXEC Proc_ @CandidateId,@FK_CorporateId,@FK_ProgramId,@Interview_DateTime,@Interview_Venue,@Interview_Remarks,@intInvitationStatus,@ReturnValue OUTPUT

IF(@ReturnValue != 0 AND @ReturnValue != -1)
BEGIN
SET @DublicateCandidateIds = @DublicateCandidateIds + ',' + CONVERT(VARCHAR(20),@ReturnValue)
--print 'ELSE:' + @DublicateCandidateIds
END
END
END

Procedure Parameter in where condition

WHERE (IsInterviewed = 0 OR IsInterviewed is null) 
AND ISNULL([Corporate-ShortListStatusForInterview],0) IN (2,3) -- Consolidated Candidate AND ((@strDistrictIds IS NOT NULL AND FK_DistrictId   IN (SELECT [ID] FROM  dbo.SplitIDs(@strDistrictIds))) OR (@strDistrictIds IS NULL))

AND(
((LEN(@Keywords) > 0 AND CP.FirstName  LIKE '%' + @Keywords + '%') OR (@Keywords IS NULL OR LEN(@Keywords) = 0))
OR ((LEN(@Keywords) > 0 AND CP.MiddleName LIKE '%' + @Keywords + '%') )
)
AND ((@ProgramId    IS NOT NULL AND P.ProgramId = @ProgramId)  OR (@ProgramId   IS NULL))

Getting error 405 (Method Not Allowed) while calling POST method of WEBAPI from Angular

Issue is due CORS, it should be enabled in WEBAPI project. The CORS enabling not works through WEB.config code, you need to enable CORS through backend.

Note - remove all web.config CORS enabling code if its there in web.config i.e the below code

    
       name="Access-Control-Allow-Origin" value="*" />
       name="Access-Control-Allow-Headers" value="Content-Type" />
       name="Access-Control-Allow-Methods" value="GET, POST, PUT, DELETE, OPTIONS" />
    

Steps 1. Install CORS package Install-Package Microsoft.AspNet.WebApi.Cors for WEBAPI project 2. In WebApiConfig.cs add this code
var corsAttribute = new EnableCorsAttribute("*","Origin, Content-Type, Accept",
                                               "GET, PUT, POST, DELETE, OPTIONS");
            config.EnableCors(corsAttribute);

Done.

JSON Maximum length problem with ASP.NET WebForm/MVC

http://forums.asp.net/t/1962449.aspx?JSON+Maximum+length+problem+with+ASP+NET

ASP.NET State Service for Windows 8

ASP.NET State Service is not installed by default in Windows 8 and should not be installed on a “home” system.
To enabled this navigate to Control Panel -> Programs -> Programs and Features ->  Turn Windows Features on or off. Select “ASP.NET 4.5″

Open Run (Win + R) and write “services.msc”

Here right click on “ASP.NET State Service” and press start.

sqlserver :delete duplicate records

WITH CTE (COl1,Col2, DuplicateCount) AS ( SELECT COl1,Col2, ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount FROM DuplicateRcordTable ) DELETE FROM CTE WHERE DuplicateCount > 1 GO

Foreigh key dependency

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

MVC:Entity Framework Exception “The underlying provider failed on Open”

http://stack247.wordpress.com/2011/03/02/entity-framework-exception-the-underlying-provider-failed-on-open/ When trying to query data from Entity Framework, my code always return the following error: The underlying provider failed on Open. Turn out the problem was on the Connection String for the entity. In the config file, we have to include “Password” parameter in “connectionString” attribute. it works now!

differences between ERP and CRM

http://trak.in/tags/business/2010/12/27/erp-vs-crm/
http://enterprisefeatures.com/2010/08/what%E2%80%99s-the-difference-between-crm-and-erp/

Understanding Request Validation in ASP.NET MVC 3

http://weblogs.asp.net/imranbaloch/archive/2011/02/19/understanding-request-validation-in-asp-net-mvc-3.aspx

difference char/varchar/nvarchar in sqlserver

http://www.sql-server-helper.com/faq/data-types-p01.aspx

XML Output From SELECT Command in SQL Server 2005

http://khanrahim.wordpress.com/2010/03/20/xml-output-from-select-command-in-sql-server-2005/

Delete Duplicate Rows SQL SERVER – 2005 – 2008

http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.

This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO


The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO


This method is a breeze and we can use this for SQL Server version 2005 and the later versions.

CTE recursive

http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/
----------------
SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com .

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result). This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.

This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

WF

http://ceres.napier.ac.uk/staff/bill/wwf.pdf

WCF

1-http://www.c-sharpcorner.com/uploadfile/sunilbabuylv/wcf4beginners08132008040537am/wcf4beginners.aspx
2-http://www.wcftutorial.net/Home.aspx

Header column Text in Datagrid

http://www.codeproject.com/KB/webforms/Datagrid_Col_Example.aspx
private void HideShowColumns(DataGrid dg)
{
if(dg == null)
{
return;
}
// Loop through all of the columns in the grid.

foreach(DataGridColumn col in dg.Columns)
{
// Hide the Salary and SS# Columns.

if(col.HeaderText == "ExampleColumn")
{
col.Visible = false;
}
}
}

interface with private method

http://msdn.microsoft.com/en-us/library/87d83y5b%28VS.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms173157%28v=VS.80%29.aspx

parms

http://msdn.microsoft.com/en-us/library/w5zay9db%28VS.71%29.aspx

call by value and reference

http://msdn.microsoft.com/en-us/library/s6938f28%28VS.80%29.aspx

http://msdn.microsoft.com/en-us/library/0f66670z%28VS.71%29.aspx#vclrfpassingmethodparameters_example1