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
.Net, MVC, MVP, SQL, JQuery, JSON, Javascript
Tuesday, July 20, 2010
Get All Relationship of Primary Key
Saturday, July 17, 2010
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 :
Get Enum Data :
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;
}
}
Output :
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();
}
<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 :
Output :
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
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
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 :
Import CSV File :
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 ]
Output :
SELECT
'EmployeeInfo.csv' AS FileName
, '.csv' AS FileType
, BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;
FileName FileType BulkColumnExample Of SQL Database :
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv Eugene Malarky,Grove Street Smithtown,New York
Example of MDB :
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 Excel :
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
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 :
CSV File :
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
Eugene Malarky,Grove Street Smithtown,New YorkOutput :
Sam Adams,Main Street,Ashville
Loren Sandler,Main Street,Hometown
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
http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
Create XLS File in ASP.NET application
Class CreateXLS :
ProcessXLS.ASPX :
Sample XLS File
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
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 :
In User Control 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
----------- ------------------------- -------------------------
Subscribe to:
Posts (Atom)