Thursday, November 18, 2010

MVC Application Configration in IIS

I have referred this site to get the MVC applicaiton working using Virtual directory. There is no direct way to get the MCV working on the IIS. It can run through port by default but making it work with IIS Virtual Directory follow this link.

1) http://haacked.com/archive/2008/11/26/asp.net-mvc-on-iis-6-walkthrough.aspx
2) http://www.asp.net/mvc/tutorials/using-asp-net-mvc-with-different-versions-of-iis-vb

Tuesday, July 20, 2010

Get All Relationship of Primary Key


SELECT
PKTableInfo.TABLE_NAME AS PK_Table_Name
, ConstarintReference.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT_NAME
, STUFF( (
SELECT
',' + kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
WHERE kcu.TABLE_CATALOG = PKTableInfo.TABLE_CATALOG
AND kcu.TABLE_SCHEMA = PKTableInfo.TABLE_SCHEMA
AND kcu.TABLE_NAME = PKTableInfo.TABLE_NAME
AND kcu.CONSTRAINT_CATALOG = PKTableInfo.CONSTRAINT_CATALOG
AND kcu.CONSTRAINT_SCHEMA = PKTableInfo.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = PKTableInfo.CONSTRAINT_NAME
ORDER BY kcu.ORDINAL_POSITION
FOR XML PATH('')
), 1, 1, '') AS [PK_CONSTRAINT_COLUMNS]
, FKTableInfo.TABLE_NAME AS FK_Table_Name
, ConstarintReference.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
, STUFF( (
SELECT
',' + kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
WHERE kcu.TABLE_CATALOG = FKTableInfo.TABLE_CATALOG
AND kcu.TABLE_SCHEMA = FKTableInfo.TABLE_SCHEMA
AND kcu.TABLE_NAME = FKTableInfo.TABLE_NAME
AND kcu.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
AND kcu.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
ORDER BY kcu.ORDINAL_POSITION
FOR XML PATH('')
), 1, 1, '') AS [FK_CONSTRAINT_COLUMNS]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS ConstarintReference
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS PKTableInfo ON PKTableInfo.CONSTRAINT_CATALOG = ConstarintReference.UNIQUE_CONSTRAINT_CATALOG
AND PKTableInfo.CONSTRAINT_SCHEMA = ConstarintReference.UNIQUE_CONSTRAINT_SCHEMA
AND PKTableInfo.CONSTRAINT_NAME = ConstarintReference.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS FKTableInfo ON ConstarintReference.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
AND ConstarintReference.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
AND ConstarintReference.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
ORDER BY PK_Table_Name, FK_Table_Name
GO

Wednesday, July 14, 2010

Searching and Multipal Selection Criteria in JQuery



<div>
<div style="float: left; width: 150px">
Your Gender :
</div>
<div style="float: left; width: 250px">
<asp:RadioButtonList ID="rdbGender" runat="server">
<asp:ListItem Text="Male" Value="1"></asp:ListItem>
<asp:ListItem Text="Female" Value="0"></asp:ListItem>
</asp:RadioButtonList>
</div>
</div>
<div style="clear: both">
<div style="float: left; width: 150px">
Searching :
</div>
<div style="float: left; width: 250px">
<asp:RadioButtonList ID="rdbSearching" runat="server">
<asp:ListItem Text="Groom" Value="1"></asp:ListItem>
<asp:ListItem Text="Bride" Value="0"></asp:ListItem>
</asp:RadioButtonList>
</div>
</div>

<script type="text/javascript" language="javascript">
$(document).ready(function() {
$("input[id*='rdbGender'], input[id*='rdbSearching']").click(function() {

var foundIn = $(this).attr('id').toString().search(new RegExp(/rdbGender/i));

var strOpositeRadioButton = "rdbGender";

if (foundIn > -1) {
strOpositeRadioButton = "rdbSearching";
}

if ($(this).val() == 1) {
$("input[id*=" + strOpositeRadioButton + "][value=0]").attr("checked", "checked");
}
else {
$("input[id*=" + strOpositeRadioButton + "][value=1]").attr("checked", "checked");
}
});
});
</script>

Monday, July 12, 2010

JQuery


Ready
-----------------------------------
$(document).read(function(){
alert('hi');
})

$(document).read(FormSetting)
Function FormSetting()
{
alert('hi');
}
-----------------------------------

Selection Criteria
-----------------------------------
=> ID
$("#txtUserName")

=> Control
$("input")
$("div")

=> Inner Control
$("div div input")

=> Class
$(".button")

=> Selection on standard attributes
$("input[type='button']")

=> Selection on custome attributes
<input type="text" IsGridText="True" />
$("input[IsGridText ='True']")
-----------------------------------

Functions
-----------------------------------
=> VAL()
Var strUserName = $("#txtUserName).val();
$("
#txtUserName).val("jsd24");

=> HTML()
Var strInnerHtml = $("#divUserInfo).html();
$("
#divUserInfo).html("<a href='#' OnClick='Alert(1)'>Click Me</a>");

=> Attr ( attribute)
var IsGridText = $("#txtUserName").attr("IsGridText");
$("#txtUserName").attr("IsGridText", "False");
$("#txtUserName").attr({IsGridText:'True', IsSelected:'True'});

=> Css (style sheet)
var color = $("#txtUserName").css("color");
$("#txtUserName").css("color", "red");
$("#txtUserName").css({'width': '100px', 'height':'25px'});

=> Each
$("input").each(function() { $(this).val("jd"); })

=> addClass()
$("input[type='button']").addClass("button");

=> removeClass()
$("input[type='button']").removeClass("button");

=> haseClass()
$("#txtUserName").haseClass("UserName");
$("#txtUserName").haseClass("EmailID");

=> toggleClass()
$("#divUserInfo").toggleClass("Content");

=> height()
$("#divUserInfo").height();

=> innerHeight()
$("#divUserInfo").innerHeight();

=> outerHeight()
$("#divUserInfo").outerHeight();

=> width()
$("#divUserInfo").width();

=> innerWidth ()
$("#divUserInfo").innerWidth ();

=> outerWidth ()
$("#divUserInfo").outerWidth ();

=> fadeIn()
$("#divUserInfo").fadeIn();

=> fadeOut()
$("#divUserInfo").fadeOut ();

=> hide()
$("#divUserInfo").hide();

=> show()
$("#divUserInfo").show();

=> appendTo
$("Hello").appendTo("#divUserInfo");

=> clone
$("#divUserInfo").clone().appendTo("#divUserInfo1");

=> not
$("#ddlProductList option").not("[value*='Computer']").clone().appendTo("#ddlFilterList");
-----------------------------------

Multiple Action in single line
-----------------------------------
$("#txtUserName").val("JSD24").css("color", "green").attr("IsSelected", "true")
-----------------------------------

Events
-----------------------------------
=> change()
$("#txtUserName").change(function(){});

=> click()
$("a").click(function(){});

=> dblclick()
$("a").dblclick(function(){});

=> focus()
$("#txtUserName").focus();

=> focusIn()
$("#txtUserName").focusIn(function());

=> focusOut()
$("#txtUserName").focusOut(function());

=> hover()
$("#txtUserName").hover(function());

=> keydown()
$("#txtUserName").keydown(function());

=> keyup()
$("#txtUserName").keyup(function());

=> keypress ()
$("#txtUserName").keypress(function());

=> mousedown()
$("#txtUserName").mousedown(function());

=> mouseup()
$("#txtUserName").mouseup(function());

=> mousemove()
$("#txtUserName").mousemove(function());
-----------------------------------

AJAX / JSON
-----------------------------------
$.ajax({
url : "test.html",
type:"GET" , //Post
data : { id : "2", pid : "1" },
context: document.body,
datatype : "html" //JSON, XML
success : handleResponse,
error : handleError
});

Function handleResponse(data)
{
alert(data);
}

Function handleError(msg)
{
alert(msg);
}
-----------------------------------

Sunday, July 11, 2010

Using Enum in Javascript


<script type="text/javascript" language="javascript">
var enumTabInfo = {
BasicInformation: 1,
SkillDetail: 2,
EducationDetail: 3,
ProjectDetail: 4,
CompanyDetail: 5
}

$(document).ready(function(){
$("div.UserTab").Hide();

var intTabNo = Number($("#<%= hdnCurrentTab.ClientID %>").val());

switch (intTabNo) {
case enumPageStep.BasicInformation:
$("#divBasicInformation").show();
break;

case enumPageStep.SkillDetail:
$("#divSkillDetail").show();
break;

case enumPageStep.EducationDetail:
$("#divEducationDetail").show();
break;

case enumPageStep.ProjectDetail:
$("#divProjectDetail").show();
break;

case enumPageStep.CompanyDetail:
$("#divCompanyDetail").show();
break;
}
});
</script>

Saturday, July 10, 2010

Enum Extention

Enum Extention :

public static class EnumExtention
{
public static Dictionary<int, string> GetEnumData(this Type EnumType)
{
Dictionary<int, string> objEnumList = new Dictionary<int, string>();

if (EnumType != null && EnumType.IsEnum)
{
foreach (int intCount in Enum.GetValues(EnumType))
{
objEnumList.Add(intCount, Enum.GetName(EnumType, intCount).ToString().Replace("_", " "));
}
}
return objEnumList;
}
}
Get Enum Data :

private enum enumUserType
{
Admin,
Sales_Department,
Purchase_Department,
Guest
}

protected void Page_Load(object sender, EventArgs e)
{
ddlUserType.DataSource = typeof(enumUserType).GetEnumData();

ddlUserType.DataTextField = "Value";
ddlUserType.DataValueField = "Key";

ddlUserType.DataBind();
}
Output :

<select name="ddlUserType" id="ddlUserType">
<option value="1">Admin</option>
<option value="2">Sales Department</option>
<option value="3">Purchase Department</option>
<option value="4">Guest</option>
</select>

Friday, July 9, 2010

OUTPUT Clause

SQL QUERY :

CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblOutPut ( OutputAction VARCHAR(20), ID INT, GroupID INT, NewValueName VARCHAR(10), OldValueName VARCHAR(10) )

INSERT INTO #tblSource VALUES (1,1,'a11')
INSERT INTO #tblSource VALUES (2,1,'a21')
INSERT INTO #tblSource VALUES (3,1,'a31')

--- Inserted
BEGIN
INSERT INTO #tblTarget
OUTPUT
'Inserted' AS OutputAction
, INSERTED.ID
, INSERTED.GroupID
, INSERTED.Name
, NULL
INTO #tblOutPut
SELECT * FROM #tblSource
UNION ALL
SELECT ID + 10, GroupID, Name FROM #tblSource;
END

--- Updated
BEGIN
UPDATE #tblTarget
SET Name = CAST(ID AS VARCHAR) + ' : ' + Name
OUTPUT
'Updated' AS OutputAction
, INSERTED.ID
, INSERTED.GroupID
, INSERTED.Name
, deleted.Name
INTO #tblOutPut;
END

--- Deleted
BEGIN
DELETE FROM #tblTarget
OUTPUT
'Deleted' AS OutputAction
, deleted.ID
, deleted.GroupID
, null
, deleted.Name
INTO #tblOutPut
WHERE ID >= 10;
END

SELECT * FROM #tblOutPut

DROP TABLE #tblSource
DROP TABLE #tblTarget
DROP TABLE #tblOutPut
Output :
OutputAction         ID          GroupID     NewValueName OldValueName
-------------------- ----------- ----------- ------------ ------------
Inserted 1 1 a11 NULL
Inserted 2 1 a21 NULL
Inserted 3 1 a31 NULL
Inserted 11 1 a11 NULL
Inserted 12 1 a21 NULL
Inserted 13 1 a31 NULL
Updated 1 1 1 : a11 a11
Updated 2 1 2 : a21 a21
Updated 3 1 3 : a31 a31
Updated 11 1 11 : a11 a11
Updated 12 1 12 : a21 a21
Updated 13 1 13 : a31 a31
Deleted 11 1 NULL 11 : a11
Deleted 12 1 NULL 12 : a21
Deleted 13 1 NULL 13 : a31

Merge Statement with insert into output table


DECLARE @tblMergeOutput TABLE
(
ID BIGINT IDENTITY(1,1)
, MergeAction NVARCHAR(100)
, InsertedId INT
, DeletedId INT
, GroupID INT
)

CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO #tblSource VALUES (1,1,'a11')
INSERT INTO #tblSource VALUES (2,1,'a21')
INSERT INTO #tblSource VALUES (3,1,'a31')
INSERT INTO #tblSource VALUES (4,1,'a41')

INSERT INTO #tblTarget VALUES (1,1,'a1')
INSERT INTO #tblTarget VALUES (3,1,'a3')
INSERT INTO #tblTarget VALUES (5,1,'a5')
INSERT INTO #tblTarget VALUES (6,2,'b2')
INSERT INTO #tblTarget VALUES (7,2,'b1')


---- Method 2 : Using Output insert into table
BEGIN
INSERT INTO @tblMergeOutput
SELECT MergeAction, InsertedId, DeletedID, GroupID
FROM
(
MERGE #tblTarget
USING (
SELECT *
FROM #tblSource
) AS tblSource
ON tblSource.GroupID = #tblTarget.GroupID
AND tblSource.ID = #tblTarget.ID
WHEN MATCHED THEN
UPDATE
SET Name = tblSource.Name
WHEN NOT MATCHED THEN
INSERT (
ID
, GroupID
, Name
)
VALUES (
tblSource.ID
, tblSource.GroupID
, tblSource.Name
)
WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
THEN DELETE
OUTPUT
$action AS MergeAction
, inserted.Id AS InsertedId
, deleted.Id AS DeletedID
, tblSource.GroupID AS GroupID
) AS MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);
END

SELECT * FROM #tblTarget order by GroupID, ID
SELECT * FROM @tblMergeOutput

DROP TABLE #tblSource
DROP TABLE #tblTarget

Thursday, July 8, 2010

Enable AD Hoc Distributed Queries OPENROWSET

By default Ad Hoc Distributed Queries are disabled on SQL server

If you try to run OPENROWSET you will get this error message

Server: Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


To enable it do this

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Openrowset Command in SQL

Syntax :

OPENROWSET
( { 'provider_name', { 'datasource';'user_id';'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file',
{ FORMATFILE ='format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE ='file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH =rows_per_batch ]
Import CSV File :

SELECT
'EmployeeInfo.csv' AS FileName
, '.csv' AS FileType
, BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;
Output :
FileName         FileType BulkColumn
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv Eugene Malarky,Grove Street Smithtown,New York
Example Of SQL Database :

SELECT JSDTest.*
FROM OPENROWSET('SQLOLEDB','PC\SQLSERVER2008';'sa';'softweb',
'SELECT CategoryName, ProductName
FROM JSDTest.dbo.Product AS Product
INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID
ORDER BY CategoryName, ProductName'
) AS JSDTest
Example of MDB :

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
Example of Excel :

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
'SELECT * FROM [sheet1$]')

Import CSV File using SQL Query

SQL Query :

DECLARE @FileName VARCHAR(100) = 'C:\EmployeeInfo.csv'

CREATE TABLE #UserInfo
(
UserName VARCHAR(50)
, Address VARCHAR(100)
, CITY VARCHAR(10)
)

/*** Get Data From Import File ***/
DECLARE @SqlQuery VARCHAR(2000)
SELECT @SqlQuery = 'BULK INSERT'
+ ' #UserInfo'
+ ' FROM'
+ ' ''' + @FileName + ''' '
+ ' WITH (FIELDTERMINATOR='','''
+ ',ROWTERMINATOR = ''' + CHAR(10) + ''')'

EXECUTE ( @SqlQuery )
/*** Get Data From Import File ***/

SELECT * FROM #UserInfo

DROP TABLE #UserInfo
CSV File :
Eugene Malarky,Grove Street Smithtown,New York
Sam Adams,Main Street,Ashville
Loren Sandler,Main Street,Hometown
Output :
UserName                       Address                        CITY
------------------------------ ------------------------------ ----------
Eugene Malarky Grove Street Smithtown New York

Sam Adams Main Street Ashville

Loren Sandler Main Street Hometown

Excel Permission Configration for ASP.NET Application

Refer this link

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

Create XLS File in ASP.NET application

Class CreateXLS :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;
using System.Reflection;

public class CreateXLS
{
#region Excel Variables
private Microsoft.Office.Interop.Excel.Application oXL;
private Microsoft.Office.Interop.Excel._Workbook oWB;
private Microsoft.Office.Interop.Excel._Worksheet oSheet;

private long iExcelRow = 2;
private long iExcelCol = 2;
private long iStartRow = 0;

private string sFromCellNo = "";
private string sToCellNo = "";

private enum enumXLSFields
{
EmployeeName,
Address,
AccountNo,
Earnings,
Deduction,
NetSalary
}

private float LogoLeft = 50;
private float LogoTop = 15;
private float LogoWidth = 52;
private float LogoHeight = 52;
#endregion

#region Properties
public string XLSPath { get; set; }
public string XLSFileName { get; set; }
public string XLSSheetName { get; set; }

public List<EmployeeInfo> EmployeeList { get; set; }

/// <summary>
/// Logo Properties
/// </summary>
public string LogoFile { get; set; }
#endregion

/// <summary>
/// Cunstructor
/// </summary>
public CreateXLS()
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.UserControl = false;

this.EmployeeList = new List<EmployeeInfo>();
}

#region Public Methods
public void GenerateXLSFile()
{
try
{
#region GenrateXLSFile
GC.Collect();
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = this.XLSSheetName;

/*----------------Main Coding----------------*/
InsertLogoFileToSheet();

SetXLSTitle((int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary, "Employee Report", 10, true);

DisplayEmployeeInfo();

SheetAutoFit();
/*-------------------------------------------*/

#region XLS File Save into Server Path

#region Check XLS File if Exists
string sFileName = this.XLSPath + this.XLSFileName + ".XLS";
if (File.Exists(sFileName))
{
File.Delete(sFileName);
}
#endregion

//Create BillingXLS Directory
if (!System.IO.Directory.Exists(XLSPath))
{
System.IO.Directory.CreateDirectory(XLSPath);
}

string strXLSFile = string.Format("{0}\\{1}.xls", this.XLSPath, this.XLSFileName);
if (File.Exists(strXLSFile))
{
File.Delete(strXLSFile);
}
oWB.SaveAs(string.Format("{0}\\{1}", this.XLSPath, this.XLSFileName), XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
oWB.Close(null, null, null);
#endregion

#endregion
}
catch
{
System.GC.Collect();
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

oSheet = null;
oWB = null;
}
}

private void DisplayEmployeeInfo()
{
#region Header
iExcelRow++;

oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Employee Name";
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = "Address";
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = "Account No";
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = "Earnings";
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = "Deduction";
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = "Net Salary";

SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
iExcelRow++;
#endregion

#region Detail
iStartRow = iExcelRow;
foreach (EmployeeInfo Employee in EmployeeList)
{
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = Employee.EmployeeName;
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = Employee.Address;
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = Employee.AccountNo;
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = Employee.Earnings;
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = Employee.Deduction;
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = Employee.NetSalary;
iExcelRow++;
}
#endregion

#region Total

#region Print Total
oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Total";

SetRangeTotal(iExcelRow, (int)enumXLSFields.Earnings, iStartRow, iExcelRow - 1, (int)enumXLSFields.Earnings);
SetRangeTotal(iExcelRow, (int)enumXLSFields.Deduction, iStartRow, iExcelRow - 1, (int)enumXLSFields.Deduction);
SetRangeTotal(iExcelRow, (int)enumXLSFields.NetSalary, iStartRow, iExcelRow - 1, (int)enumXLSFields.NetSalary);
#endregion

#region Range Format
SetRangeNumberFormat(iStartRow, iExcelRow, (int)enumXLSFields.Earnings, (int)enumXLSFields.NetSalary);
SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
SetRangeBorder(iStartRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
#endregion

#endregion
}
#endregion

#region Private Methods
private void InsertLogoFileToSheet()
{
if (File.Exists(this.LogoFile))
{
oSheet.Shapes.AddPicture(this.LogoFile, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, LogoLeft, LogoTop, LogoWidth, LogoHeight);
}
SetXLSTitle((int)enumXLSFields.Address, (int)enumXLSFields.NetSalary, "ABC Inc.", 12, true);

iExcelRow = 6;
}

private void SheetAutoFit()
{
Range oRng = oSheet.get_Range("A1", "Z1");
oRng.EntireColumn.AutoFit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}

private void SetXLSTitle(int StartColumn, int EndColumn, string ColumnHeading, int FontSize, bool MergeColumn)
{
sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + iExcelRow.ToString();
sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + iExcelRow.ToString();

oSheet.Cells[iExcelRow, iExcelCol + StartColumn] = ColumnHeading;

Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
oRng.Font.Name = "Arial";
oRng.Font.Size = FontSize;
oRng.Font.Bold = true;
if (MergeColumn)
{
oRng.Merge(1);
oRng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oRng.Font.Color = Color.White.ToArgb();
oRng.Interior.Color = Color.FromArgb(128, 128, 128).ToArgb();
oRng.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
oRng.Borders.Value = 1;
oRng.Borders.Color = Color.Black.ToArgb();
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}

private void SetRangeTotal(long DisplayRow, long DisplayColumn, long StartRow, long EndRow, long RangeColumn)
{
sFromCellNo = ((char)(iExcelCol + RangeColumn + 64)) + StartRow.ToString();
sToCellNo = ((char)(iExcelCol + RangeColumn + 64)) + EndRow.ToString();

oSheet.Cells[DisplayRow, iExcelCol + DisplayColumn] = "=SUM(" + sFromCellNo + ":" + sToCellNo + ")";
}

private void SetRangeNumberFormat(long StartRow, long EndRow, long StartColumn, long EndColumn)
{
sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
oRng.NumberFormat = "###,###,##0.00";
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}

private void SetRangeBorder(long StartRow, long EndRow, long StartColumn, long EndColumn)
{
sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
oRng.Borders.Value = 1;
oRng.Borders.Color = Color.Black.ToArgb();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}

private void SetRangeBackColor(long StartRow, long EndRow, int StartColumn, int EndColumn)
{
sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
oRng.Font.Name = "Arial";
oRng.Font.Bold = true;
oRng.Font.Color = Color.Black.ToArgb();
oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}

private void SetRangeBold(long StartRow, long EndRow, long StartColumn, long EndColumn)
{
sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
oRng.Font.Name = "Arial";
oRng.Font.Bold = true;
oRng.Font.Color = Color.Black.ToArgb();
oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
}
#endregion
}

public class EmployeeInfo
{
public string EmployeeName { get; set; }
public string Address { get; set; }
public string AccountNo { get; set; }
public float Earnings { get; set; }
public float Deduction { get; set; }
public float NetSalary { get; set; }
}

ProcessXLS.ASPX :

public partial class ProcessXLS : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GenrateXLS();
}

private void GenrateXLS()
{
CreateXLS objXLS = new CreateXLS();

objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Eugene Malarky", Address = "Grove Street, Smithtown", AccountNo = "A001", Earnings = 1500, Deduction = 450, NetSalary = 1050 });
objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Sam Adams", Address = "Main Street, Suite 1000, Ashville", AccountNo = "A002", Earnings = 1250, Deduction = 320, NetSalary = 930 });
objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Loren Sandler", Address = "Main Street, Hometown", AccountNo = "A003", Earnings = 1800, Deduction = 550, NetSalary = 1250 });

objXLS.XLSPath = Server.MapPath("XLSFiles");
objXLS.XLSFileName = "EmployeeSalary";
objXLS.XLSSheetName = "EmployeeList";

objXLS.LogoFile = Server.MapPath("XLSFiles/JDLogo.jpeg");

objXLS.GenerateXLSFile();
}
}

Sample XLS File

Wednesday, July 7, 2010

MERGE Statement and compatibility level

In SQL 2008 Merge Statement when execute and give the error like this

Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Solution
SQL Command
ALTER DATABASE <DBName>
SET COMPATIBILITY_LEVEL = 100

Monday, July 5, 2010

Delegate using in ASPX Page and user control

In ASPX Page :

public partial class Activity_ContactActivity : Page
{
protected void Page_Load(object sender, EventArgs e)
{
MonthActivity1.LoadWeekTabInformation += new UserControls_Activity_MonthActivity.LoadTabInformation(MonthActivity1_LoadWeekTabInformation);
}

void MonthActivity1_LoadWeekTabInformation()
{
this.ActivityDate = MonthActivity1.ActivityDate;
this.CurrentActivityTab = enumContactActivityTab.Week;
LoadActivityInformation();
}
}


In User Control Page :

public partial class UserControls_Activity_MonthActivity : UserControl
{
public delegate void LoadTabInformation();
public event LoadTabInformation LoadWeekTabInformation;

private void DisplayWeekTabInformation()
{
LoadWeekTabInformation();
}
}

Execute Process in Threading Pool


using System.Threading;

public class ImportData()
{
public void Import()
{
ThreadPool.QueueUserWorkItem(new WaitCallback(SendImportDataRequest), new ImportParameter()
{
PageURL = "ImportData.aspx",
CurrentUpload = enumUploadData.CustodianData,
XMLCustodianIds = sbCustodianId.ToString().Substring(1),
ImportDate = txtImportDate.Text.ToDateTimeValue(),
LoginContactId = this.LoginContactId,
LoginUserId = this.LoginUserId
});
}

public static void SendImportDataRequest(object Parameter)
{
ImportParameterDTO objParameter = (ImportParameterDTO)Parameter;
string urlEncodedUserInput = string.Format("StartImport={0}&CurrentUpload={1}&CustodianIds={2}&ImportDate={3}&LoginContactId={4}&LoginUserId={5}"
, (int)enumYesNo.Yes // 0
, objParameter.CurrentUpload // 1
, objParameter.XMLCustodianIds // 2
, objParameter.ImportDate.ToShortDateString() // 3
, objParameter.LoginContactId // 4
, objParameter.LoginUserId // 5
);

System.Net.WebRequest httpRequest = System.Net.WebRequest.Create(objParameter.PageURL);

httpRequest.Method = "POST";
httpRequest.ContentType = "application/x-www-form-urlencoded;charset=utf-8";
byte[] bytedata = System.Text.Encoding.UTF8.GetBytes(urlEncodedUserInput);
httpRequest.ContentLength = bytedata.Length;
System.IO.Stream requestStream = httpRequest.GetRequestStream();
requestStream.Write(bytedata, 0, bytedata.Length);
requestStream.Close();

try
{
System.Net.WebResponse res = httpRequest.GetResponse();
}
catch (Exception ex)
{
}
}
}

public class ImportParameter()
{
public string PageURL { get; set; }
public enumUploadData CurrentUpload { get; set; }
public string XMLCustodianIds { get; set; }
public DateTime ImportDate { get; set; }
public long LoginContactId { get; set; }
public long LoginUserId { get; set; }
}

Saturday, July 3, 2010

Checking current user Menu Rights

SQL QUERY :

SET NOCOUNT ON

DECLARE @CurrentUserTypeID INT = 1 ------ <---- Pass Current User Type

-----------------------------------
DECLARE @tblUserType TABLE
(
UserTypeID INT
, TypeName VARCHAR(50)
, MenuRights BIGINT
)

INSERT INTO @tblUserType
VALUES (1, 'Admin', 1)
, (2, 'GenralManager', 2)
, (3, 'SalesManager', 4)
, (4, 'SalesMan', 8)
, (5, 'Client', 16)

DECLARE @tblMenu TABLE
(
MenuID INT
, MenuName VARCHAR(50)
, MenuRights BIGINT
)

INSERT INTO @tblMenu
VALUES (1, 'Admin Setting', 1)
, (2, 'Genral Setting', 3)
, (3, 'Sales Report', 7)
, (4, 'Sales Order Folloup', 15)
, (5, 'Client FeedBack', 31)

DECLARE @UserRights BIGINT = 0

SELECT @UserRights = MenuRights
FROM @tblUserType
WHERE UserTypeID = @CurrentUserTypeID

SELECT *
FROM @tblMenu
WHERE MenuRights & @UserRights = @UserRights


Admin Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
1 Admin Setting 1
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31


GenralManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
2 Genral Setting 3
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31


SalesManager Menu List :
MenuID MenuName MenuRights
----------- -------------------------------------------------- --------------------
3 Sales Report 7
4 Sales Order Folloup 15
5 Client FeedBack 31

Cross Apply in XML

Query :


DECLARE @XML XML =
'<Customers>;
<Customer ID="1">;
<Address1>;Address - 11</Address1>;
<Address2>;Address - 12</Address2>;
</Customer>;
<Customer ID="2">;
<Address1>;Address - 21</Address1>;
<Address2>;Address - 22</Address2>;
</Customer>;
</Customers>;'


SELECT
Customer.value('@ID', 'INT') AS CustomerID
, CustAdd1.value('.', 'VARCHAR(25)') Address1
, CustAdd2.value('.', 'VARCHAR(25)') Address2
FROM @XML.nodes('/Customers/Customer')a(Customer)
CROSS APPLY Customer.nodes('Address1')aa(CustAdd1)
CROSS APPLY Customer.nodes('Address2')bb(CustAdd2)


Output :
----------- ------------------------- -------------------------
CustomerID Address1 Address2
----------- ------------------------- -------------------------
1 Address - 11 Address - 12
2 Address - 21 Address - 22
----------- ------------------------- -------------------------

Friday, June 11, 2010

Using ConvertAll and Aggregate in LINQ


string strImportResult = this.ImportResult.ToList()
.ConvertAll<string>(Import => string.Format("<tr><td>
{0}</td><td>{1}</td><td>{2}</td><td>
{3}</td></tr>"

, Import.CustodianName
, Import.ImportFile
, Import.ImportStatus
, Import.Particular))
.Aggregate((ImportA, ImportB) => ImportA + ImportB);

Multipal Field Group By IN LINQ



var objOrderList = from OrderList in
(from OrderDetail in objOrderDetail
join Item in objItem on OrderDetail.ItemId equals Item.ItemId
join ItemGroup in objItemGroup on Item.GroupId equals ItemGroup.GroupId
select new
{
GroupName = ItemGroup.GroupName,
ItemName = Item.ItemName,
Rate = Item.Rate,
Qty = OrderDetail.Qty
}
)
group OrderList by new
{
OrderList.GroupName,
OrderList.ItemName
} into OrderList
select new
{
GroupName = OrderList.Key.GroupName,
ItemName = OrderList.Key.ItemName,
OrderTotal = OrderList.Sum(OL => OL.Qty * OL.Rate)
};

Left Outer Join and Group By In LINQ


List Table1 = new List();
List Table2 = new List();

var objList = from ListData in
(from a in Table1
join b in Table2 on a.Id equals b.Id into t2
from t2Data in t2.DefaultIfEmpty()
select new
{
Product = a.Product,
Title = t2Data.Title,
Amount = a.Amount
}
)
group ListData by new
{
ListData.Product,
ListData.Title
} into GroupData
select new
{
Product = GroupData.Key.Product,
Title = GroupData.Key.Title,
Amount = GroupData.Sum(OL => OL.Amount)
};

EditTemplates for FieldType in MVC

View->Shared->EditorTemplates->Decimal.ascx

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.TextBox("", String.Format("{0:F}", Model)) %>

DisplayFor in MVC

Views->Shared->DisplayTemplates->EmailAddress.ascx

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<a href="mailto:<%= Html.Encode(Model) %>"><%= Html.Encode(Model) %></a>
<img src="/Content/images/sendemail.gif" />

EditorFor in MVC

Models->Supplier:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;

namespace FeaturesOfMVC2.Models
{
[MetadataType(typeof(Supplier_Validation))]
public partial class Supplier
{
class Supplier_Validation
{
[ScaffoldColumn(false)]
[DisplayName("Supplier Id :")]
public int SupplierId { get; set; }

[Required(ErrorMessage = "Name Required!")]
[DisplayName("Supplier Name :")]
public string SupplierName { get; set; }

[UIHint("StateDDL")]
[DisplayName("State :")]
public int StateId { get; set; }

[UIHint("CityDDL")]
[DisplayName("City :")]
public int CityId { get; set; }

[UIHint("EmailAddress")]
[DisplayName("Email Address :")]
public string EmailId { get; set; }
}
}
}


Views->Shared->EditorTemplates->StateDDL.ascx

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>


Views->Shared->EditorTemplates->CityDDL.ascx

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>


Views->Supplier->Edit.aspx

<%= Html.EditorFor(model => model.StateID) %>
<%= Html.EditorFor(model => model.CityID) %>

JSON Calling from MVC Page

HTML:

<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>


Javascript:

<script type="text/javascript" language="javascript">
$(document).ready(function() {
$("select[id='StateId']").change(function() {
$.ajax({
type: "POST",
url: "/Supplier/StateCityInfo/",
data: { StateId: $(this).val() },
dataType: "json",
error: function(xhr, status, error) {
// you may need to handle me if the json is invalid
// this is the ajax object
alert(status);
},
success: function(data) {
$("#CityId").empty();
$.each(data, function(key, City) {
$("#CityId").append($("<option></option>").val(City.Id).html(City.Name));
});
}
});
})
})
</script>


Supplier Controller:

public class SupplierController : Controller
{
[AcceptVerbs("POST")]
public ActionResult StateCityInfo(int StateId)
{
return Json(this.AllCity(StateId));
}
}

JSON Calling from ASP.Net

HTML:

<body>
<form id="form1" runat="server">
No Of Doors :
<input id="txtNoOfDoor" type="text" /><br />
<input type="button" id="Button1" value="Get All Cars" />
<input type="button" id="Button2" value="Get Car By No Of Door" />
<div id="output">
</div>
</form>
</body>


Javascript:

<script type="text/javascript" language="javascript">
$(function() {
$('#Button1').click(getCars);
$('#Button2').click(getCarsByDoors);
});

function getCars() {
$.ajax({
type: "POST",
url: "CarService.asmx/GetAllCars",
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function(response) {
var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
$('#output').empty();
for (var i = 0; i < cars.length; i++) {
$('#output').append('<p><strong>' + cars[i].Make + ' ' +
cars[i].Model + '</strong><br /> Year: ' +
cars[i].Year + '<br />Doors: ' +
cars[i].Doors + '<br />Colour: ' +
cars[i].Colour + '<br />Price: £' +
cars[i].Price + '</p>');
}
},

failure: function(msg) {

$('#output').text(msg);

}
});
}

function getCarsByDoors() {
var data = '{doors: ' + $('#txtNoOfDoor').val() + ', Name : "Jayesh" }';
$.ajax({
type: "POST",
url: "CarService.asmx/GetCarsByDoors",
data: data,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function(response) {
var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
$('#output').empty();
for (var i = 0; i < cars.length; i++) {
$('#output').append('<p><strong>' + cars[i].Make + ' ' +
cars[i].Model + '</strong><br /> Year: ' +
cars[i].Year + '<br />Doors: ' +
cars[i].Doors + '<br />Colour: ' +
cars[i].Colour + '<br />Price: £' +
cars[i].Price + '</p>');
}
},

failure: function(msg) {

$('#output').text(msg);

}
});
}

</script>


CarService.asmx:

/// <summary>
/// Summary description for CarService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
[ScriptService]
public class CarService : System.Web.Services.WebService
{
List<Car> objCarList = new List<Car>{
new Car{Make="Audi",Model="A4",Year=1995,Doors=5,Colour="Red",Price=2995f},
new Car{Make="Ford",Model="Focus",Year=2002,Doors=5,Colour="Black",Price=3250f},
new Car{Make="BMW",Model="5 Series",Year=2006,Doors=4,Colour="Grey",Price=24950f},
new Car{Make="Renault",Model="Laguna",Year=2000,Doors=5,Colour="Red",Price=3995f},
new Car{Make="Toyota",Model="Previa",Year=1998,Doors=5,Colour="Green",Price=2695f},
new Car{Make="Mini",Model="Cooper",Year=2005,Doors=2,Colour="Grey",Price=9850f},
new Car{Make="Mazda",Model="MX 5",Year=2003,Doors=2,Colour="Silver",Price=6995f},
new Car{Make="Ford",Model="Fiesta",Year=2004,Doors=3,Colour="Red",Price=3759f},
new Car{Make="Honda",Model="Accord",Year=1997,Doors=4,Colour="Silver",Price=1995f}
};

[WebMethod]
public List<Car> GetAllCars()
{
return objCarList;
}

[WebMethod]
public List<Car> GetCarsByDoors(int doors, string Name)
{
var query = from c in objCarList
where c.Doors == doors
select c;

return query.ToList();
}

[WebMethod]
public string GetAllCarsInString()
{
JavaScriptSerializer objJS = new JavaScriptSerializer();
return objJS.Serialize(objCarList);
}
}


Car Class:

public class Car
{
public string Make { get; set; }
public string Model { get; set; }
public int Year { get; set; }
public int Doors { get; set; }
public string Colour { get; set; }
public float Price { get; set; }
}

Merge Statement

SQL Statement:

DECLARE @tblMergeOutput TABLE
(
ID BIGINT IDENTITY(1,1)
, MergeAction NVARCHAR(100)
, InsertedId INT
, DeletedId INT
, GroupID INT
)

CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO #tblSource VALUES (1,1,'a11')
INSERT INTO #tblSource VALUES (2,1,'a21')
INSERT INTO #tblSource VALUES (3,1,'a31')
INSERT INTO #tblSource VALUES (4,1,'a41')

INSERT INTO #tblTarget VALUES (1,1,'a1')
INSERT INTO #tblTarget VALUES (3,1,'a3')
INSERT INTO #tblTarget VALUES (5,1,'a5')
INSERT INTO #tblTarget VALUES (6,2,'b2')
INSERT INTO #tblTarget VALUES (7,2,'b1')

MERGE #tblTarget
USING (
SELECT *
FROM #tblSource
) AS tblSource
ON tblSource.GroupID = #tblTarget.GroupID
AND tblSource.ID = #tblTarget.ID
WHEN MATCHED THEN
UPDATE
SET Name = tblSource.Name
WHEN NOT MATCHED THEN
INSERT (
ID
, GroupID
, Name
)
VALUES (
tblSource.ID
, tblSource.GroupID
, tblSource.Name
)
WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
THEN DELETE
OUTPUT
$action
, inserted.Id
, deleted.Id
, tblSource.GroupID
INTO @tblMergeOutput;

SELECT * FROM #tblSource
SELECT * FROM #tblTarget order by GroupID, ID
SELECT * FROM @tblMergeOutput

DROP TABLE #tblSource
DROP TABLE #tblTarget


Target Table Output:

------------------
ID GroupID Name
------------------
1 1 a11
2 1 a21
3 1 a31
4 1 a41
6 2 b2
7 2 b1
------------------


Merge Table Output:

----------------------------------------------------
ID Merge Action InsertedID DeletedID GroupID
----------------------------------------------------
1 INSERT 2 NULL 1
2 INSERT 4 NULL 1
3 UPDATE 1 1 1
4 UPDATE 3 3 1
5 DELETE NULL 5 NULL
----------------------------------------------------

Wednesday, June 9, 2010

User Tree


DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )

INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')

;WITH cteNodes AS
(
--initialization
SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
FROM @tblUInfo
--WHERE ParentUserID IS NULL

UNION ALL

----recursive execution
SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
FROM cteNodes AS P
INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)

, cteEmployeePath AS
(
SELECT tblUInfo.UserID
, tblUInfo.UserName
,(
SELECT CAST(UserTree AS VARCHAR ) + ':'
FROM cteNodes AS UserParth
WHERE UserParth.UserID = tblUInfo.UserID
ORDER BY UserLevel DESC
FOR XML PATH('')
) AS UserTree
,(
SELECT MAX(UserLevel)
FROM cteNodes AS UserParth
WHERE UserParth.UserID = tblUInfo.UserID
) AS UserLevel
FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName
FROM cteEmployeePath
ORDER BY UserTree
OPTION (MaxRecursion 32767)

Tuesday, April 20, 2010

Pivot / UnPivot Example

SQL Query :


DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

INSERT INTO @Tbl
SELECT 1, 'a', 'b', 'c'
UNION
SELECT 2, 'd', 'e', 'f'
UNION
SELECT 3, 'g', 'h', 'i'

SELECT
*
FROM
(
SELECT *
FROM
(
SELECT
Col1 AS Cols
, A AS [A(N1)]
, B AS [B(N2)]
, C AS [C(N3)]
FROM @Tbl
) A
UNPIVOT
( [Value] For [Col1] IN (
[A(N1)]
, [B(N2)]
, [C(N3)]
) ) AS upvt
) B
PIVOT
(
MIN ( [Value])
FOR [Cols]
IN ([1], [2], [3])
) AS pvt;

Input :

----------------
Col1 A B C
----------------
1 a b c
2 d e f
3 g h i
----------------

OutPut :
    
-------------------
Col1 1 2 3
-------------------
A(N1) a d g
B(N2) b e h
C(N3) c f i
-------------------