Dot Net

ALTER User Defined Table Type without removing dependancy

2014-03-07T19:10:00.002+05:30

Use LotusBOS_Development
Go
If Object_Id('dbo.SPA_SystemUDTT_Update') Is Not Null
Begin
Drop Proc dbo.SPA_SystemUDTT_Update
End
Go
/*
Created By : Dhaval Chauhan, Created On : 24 Feb 2014
Exec SPA_SystemUDTT_Update
@ObjectName = 'TBLB_SupplierPLOFType',
@TableTypeScript = '
/****** Object: UserDefinedTableType [dbo].[TBLB_SupplierPLOFType] Script Date: 3/7/2014 5:35:23 PM ******/
CREATE TYPE [dbo].[TBLB_SupplierPLOFType] AS TABLE(
[SuPLItemType] [varchar](2) NULL,
[SuPLSalesType] [varchar](2) NULL,
[SuPLWeightType] [varchar](2) NULL,
[SuPLVATID] [tinyint] NULL,
[SuPLID] [bigint] NOT NULL,
[SuGrID] [smallint] NULL,
[SuppID] [bigint] NULL,
[SuPLProductCode] [varchar](25) NULL,
[SuPLEffectiveDate] [datetime] NULL,
[SuPLEndDate] [datetime] NULL,
[SuPLReason] [varchar](50) NULL,
[SuPLNSP] [decimal](20, 4) NULL,
[SuPLPromotionNSP] [decimal](20, 4) NULL,
[SuPLRSP] [decimal](20, 4) NULL,
[SuPLPromotionRSP] [decimal](20, 4) NULL,
[SuPLPalletNSP] [decimal](20, 4) NULL,
[SuPLPalletPromotionNSP] [decimal](20, 4) NULL,
[SuPLPromotionID] [int] NULL,
[SuPLPromotionRetail] [decimal](20, 4) NULL,
[SuPLPromotionSellStartDate_Original] [datetime] NULL,
[SuPLPromotionSellEndDate_Original] [datetime] NULL,
[SuPLPromotionSellStartDate] [datetime] NULL,
[SuPLPromotionSellEndDate] [datetime] NULL,
[SuPLPromotionOrderStartDate] [datetime] NULL,
[SuPLPromotionOrderEndDate] [datetime] NULL,
[SuPLVATCode] [varchar](12) NULL,
[SuPLVATRate] [decimal](20, 10) NULL,
[SuPLPOR] [decimal](20, 2) NULL,
[SuPLCurrency] [varchar](6) NULL,
[SuPLDivisionRMS] [int] NULL,
[SuPLCommodityGroup] [int] NULL,
[SuPLSubCommodityGroup] [int] NULL,
[SuPLsubSubCommodityGroup] [int] NULL,
[SuPLcatchWeight] [bit] NULL,
[SuPLPlof] [int] NULL,
[SuPLOwnLabel] [varchar](1) NULL,
[SuPLInnerEAN] [varchar](18) NULL,
[SuPLOuterEAN] [varchar](18) NULL,
[SuPLshortDescription] [varchar](50) NULL,
[SuPLlongDescription] [varchar](100) NULL,
[SuPLnumberUnitsPack] [decimal](20, 4) NULL,
[SuPLunitWeight] [decimal](20, 2) NULL,
[SuPLisPresell] [varchar](1) NULL,
[SuPLPalletSize] [decimal](12, 4) NULL,
[SuPLpackageUOM] [varchar](4) NULL,
[SuPLWeight] [decimal](12, 4) NULL,
[SuPLunitPriceMeasure] [varchar](20) NULL,
[SuPLWidth] [decimal](20, 4) NULL,
[SuPLdepth] [decimal](20, 4) NULL,
[SuPLheight] [decimal](20, 4) NULL,
[SuPLlwhUnitOfMeasure] [varchar](20) NULL,
[SuPLnetWeight] [decimal](20, 4) NULL,
[SuPLmaxOrderQuantity] [decimal](20, 4) NULL,
[SuPLblackListed] [varchar](1) NULL,
[SuPLprePricedFlag] [varchar](1) NULL,
[SuPLlastSubstitute] [varchar](25) NULL,
[SuPLlastRatio] [decimal](20, 2) NULL,
[SuPLrangeStatus] [varchar](1) NULL,
[SuPLpackDesc] [varchar](20) NULL,
[SuPLforecast] [varchar](1) NULL,
[SuPLlabel] [varchar](8) NULL,
[SuPLpricingUnitOfMeasure] [varchar](40) NULL,
[SuPLplofShelfLife] [smallint] NULL,
[SuPLPriceCheckIndPlus] [varchar](1) NULL,
[SuPLPriceCheckInd] [varchar](1) NULL,
[SuPLcoreRange] [varchar](1) NULL,
[SuPLcoreRangePlus] [bit] NULL,
[SuPLsurchargeIncludeFlag] [bit] NULL,
[SuPLsurchargeApplicableFlag] [bit] NULL,
[SuPLrspFactor] [int] NULL,
[SuPLcountryOfOriginID] [varchar](10) NULL,
[SuPLcountryOfOriginName] [varchar](100) NULL,
[SuPLrspOther] [varchar](1000) NULL,
[SuPLproductAttribute] [varchar](100) NULL,
[SuGDID] [smallint] NULL,
[ProdID] [int] NULL,
[ItGrID] [int] NULL,
[SuPLPricingStrategyType] [char](1) NULL,
[SuPLPricingStrategyValue] [decimal](7, 2) NULL,
[SuPLRoundingType] [char](1) NULL,
[SuPLRoundingPoint1] [tinyint] NULL,
[SuPLRoundingPoint2] [tinyint] NULL,
[SuPLRRP] [decimal](20, 4) NULL,
[SyReMessage] [varchar](1000) NULL,
[MyColumn] Varchar (10) NULL
)'
###
*/
Create PROCEDURE [dbo].[SPA_SystemUDTT_Update]
@ObjectName Varchar(1000),
@TableTypeScript Varchar(max)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Declare @TransName Varchar(100) = NewId()

Begin Try
Begin Transaction @TransName;

Declare @DependantSource As Table (RowId Bigint,[Object_Name] Varchar(1000),[Script] Varchar(max),[Type] Varchar(50))

If Object_Id('dbo.[SequenceSystemDependancy]') Is Not NUll
Begin
Drop SEQUENCE [DBO].[SequenceSystemDependancy]
End
CREATE SEQUENCE [DBO].[SequenceSystemDependancy] AS INT START WITH 1 INCREMENT BY 1

Insert Into @DependantSource
SELECT NEXT VALUE FOR DBO.SequenceSystemDependancy, OBJECT_NAME(m.object_id) [Object_Name],m.Definition As Script,o.Type
FROM sys.sql_expression_dependencies d
JOIN sys.sql_modules m ON m.object_id = d.referencing_id
JOIN sys.objects o ON o.object_id = m.object_id
WHERE referenced_id = TYPE_ID(@ObjectName)

----///----------Alter Sequence to decending order -----------
---- =====================================================================================================================================
Declare @sqlSequence As Varchar(max) = ''
Set @sqlSequence = 'Alter SEQUENCE [DBO].[SequenceSystemDependancy] RESTART WITH '
+ Cast(
(Select Current_Value FROM SYS.Sequences WHERE name='SequenceSystemDependancy')
As Varchar(10))+ ' INCREMENT BY -1 MINVALUE 0 MAXVALUE '
+ Cast(
(Select Current_Value FROM SYS.Sequences WHERE name='SequenceSystemDependancy')
As Varchar(10))
execute (@sqlSequence);
Declare @ObjectNameR Varchar(1000)= Null,@ObjectTypeR Varchar(50) = NULL
While (NEXT VALUE FOR DBO.SequenceSystemDependancy>0)
Begin
Select @ObjectNameR = NULL,
@ObjectTypeR = NULL

Select @ObjectTypeR = p.[Type],@ObjectNameR = p.[Object_Name]
From @DependantSource p
Where p.RowId = cast((SELECT Current_Value FROM SYS.Sequences With(NoLock) WHERE name='SequenceSystemDependancy') As BigInt)

If Object_Id(@ObjectNameR) Is Not Null And @ObjectTypeR = 'P'
Begin
Execute ('Drop Proc '+@ObjectNameR);
End
End

execute ('Drop Type '+@ObjectName);
execute(@TableTypeScript);

Set @sqlSequence = 'Alter SEQUENCE [DBO].[SequenceSystemDependancy] RESTART WITH '
+ Cast(
(Select Count(1) From @DependantSource)
As Varchar(10))+ ' INCREMENT BY -1 MINVALUE 0'

execute (@sqlSequence);

Declare @ObjectNameC As Varchar(1000) ,@ObjectTypeC Varchar(50) ,@ObjectScriptC Varchar(max)
While (NEXT VALUE FOR DBO.SequenceSystemDependancy>0)
Begin
Select @ObjectNameC = NULL,
@ObjectTypeC = NULL,
@ObjectScriptC = NULL
Select @ObjectTypeC = p.[Type],@ObjectNameC = p.[Object_Name],@ObjectScriptC = p.Script
From @DependantSource p
Where p.RowId = cast((SELECT Current_Value FROM SYS.Sequences With(NoLock) WHERE name='SequenceSystemDependancy') As BigInt)

execute(@ObjectScriptC);
End
-- =====================================================================================================================================
--///----------Remove Sequence If Any-----------
-- =====================================================================================================================================
If Object_Id('DBO.SequenceSystemDependancy') Is Not Null
Begin
Drop SEQUENCE [DBO].[SequenceSystemDependancy]
End

Commit Transaction @TransName
End Try
Begin Catch
IF (@@TRANCOUNT > 0 )
BEGIN
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION @TransName
END
END
EXEC [SPBE_LogAndThrowException] 'SPA_System_UpdateUDTT';
End Catch

Online XML Parsing in SQL

2012-04-19T17:52:00.006+05:30

sp_configure 'Show Advanced Option',1
Reconfigure
go
sp_configure 'Ole Automation Procedures',1
Reconfigure
  go  
 Declare @Object as Int;      
Declare @ResponseText1 as Varchar(8000);      
Declare @Url as Varchar(MAX);     
 select @Url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'     
  Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;     
 Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'      
Exec sp_OAMethod @Object, 'send'      
Exec sp_OAMethod @Object, 'responseText', @ResponseText1 OUTPUT          
 Exec sp_OADestroy @Object       --load into Xml     
 Declare @XmlResponse as xml;         
  select @XmlResponse = CAST(@ResponseText1 as xml)         
  ;with xmlnamespaces (   'http://www.ecb.int/vocabulary/2002-08-01/eurofxref' as Envelope, 
 'http://www.gesmes.org/xml/2002-08-01' as gesmes )
 select x.i.value('@rate','money') [Money],x.i.value('@currency','char(3)') As [Currency]  
from @XmlResponse.nodes('/gesmes:Envelope/Envelope:Cube/Envelope:Cube/Envelope:Cube')  as x(i)

XML Deserialization and Seriallization in C#

2012-04-18T16:22:00.004+05:30

public class XMLData

{

public string Data1 { get; set; }

public string Data2 { get; set; }

}

private void Deserialize()

{

try

{

string strXML = string.Empty;

strXML = "aabc";

string strTemp = "" + strXML;

XmlRootAttribute rt = new XmlRootAttribute("Roots");

XmlSerializer serializer = new XmlSerializer(typeof(XMLData[]), rt);

XMLData[] xmlDatas = null;

byte[] bt = Encoding.UTF8.GetBytes(strTemp);

MemoryStream ms = new MemoryStream(bt);

xmlDatas = (XMLData[])serializer.Deserialize(ms);

}

catch (Exception ex)

{

}

private void Serialize(XMLData[] xmlDatas)

{

try

{

XmlSerializer serializer = new XmlSerializer(typeof(XMLData[]));

StringBuilder stringBuilder = new StringBuilder();

System.Xml.XmlWriterSettings xWriSetting = new System.Xml.XmlWriterSettings();

xWriSetting.Encoding = Encoding.UTF8;

xWriSetting.ConformanceLevel = System.Xml.ConformanceLevel.Document;

xWriSetting.CheckCharacters = false;

xWriSetting.Indent = false;

xWriSetting.OmitXmlDeclaration = true;

xWriSetting.CloseOutput = false;

System.Xml.XmlWriter xmlWriter = System.Xml.XmlWriter.Create(stringBuilder, xWriSetting);

serializer.Serialize(xmlWriter, xmlDatas);

string strOp = stringBuilder.ToString();

}

catch (Exception ex)

{

}

Recursive Common Table Expression

2012-03-27T14:34:00.001+05:30

DECLARE @Date DATE = '1900-01-01',
@inc INT = 0

;with cte as
(
select @inc AS Inc,DATENAME(mm,@Date) AS [MonthName],DATEPART(mm,@Date) AS [MonthNumber]
UNION ALL
select inc+1,DATENAME(mm,DATEADD(mm,inc+1,@Date)),DATEPART(mm,DATEADD(mm,inc+1,@Date))
FROM cte
where inc < 11
)

select [MonthName],[MonthNumber] from cte

XML File to SQL Table

2012-03-14T19:35:00.002+05:30

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(max)

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'D:\MyShopData\MobileOperatorIdentify.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML

SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
= @x
END

SELECT @FileContents as FileContents
DROP TABLE #tempXML
print @FileContents
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc OUTPUT, @FileContents

SELECT PrefixId, OperatorId, PrefixNumber
FROM OPENXML(@iDoc,'/DocumentElement/dtMobileOperatorPrefix',2)
With(
PrefixId varchar(10) 'PrefixId',
OperatorId varchar(10) 'OperatorId',
PrefixNumber varchar(10) 'PrefixNumber'
)

xml in a tabular format in sqlserver 2008

2012-03-05T19:31:00.003+05:30

declare @xml xml
set @xml ='
--
-- abc@gmail.com
--
--
--
--
--
--
--def@gmail.com
--
--
--
--
--
--'
/*-----------------------------------------------------
abc@gmail.com abc 123
abc@gmail.com def 345
def@gmail.com abc 123
def@gmail.com def 345
*/

SELECT
x.v.value('../../EA[1]','varchar(100)') as EMail,
x.v.value('@Name[1]','varchar(1000)') as CAS,
x.v.value('@value[1]','varchar(1000)') as Value

FROM @xml.nodes('CampaignItems/Record/CAS/Attr') x(v)

SQL-Server Query from your SQL-Server

2012-02-22T12:23:00.001+05:30

Note :
To execute other SQL-Server SQL Query from your SQL-Server

To Enable This Option Follow Step 1 and 2

#####Step 1

sp_configure 'show advanced options', 1;
RECONFIGURE;

#####Step 2

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

#####Step 3 - Execute Your Query
SELECT top 10 *
FROM OPENROWSET('SQLNCLI','Server=DbServerName;Uid=username;Pwd=password;Database=databasename;',
'SELECT * FROM tabelname') AS FS

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

How to use c# class in VB.net Web application

2012-01-06T12:02:00.005+05:30

Step 1 : Add App_Code Folder in ROOT directory

Step 2 : Add Two folder n App_Code Folder name as "cscode", "vbcode"

Step 3 : Now open web config file.
Add Follwing section in compilation section

How to use c# class in VB.net Web application

Step 1 : Add App_Code Folder in ROOT directory

Step 2 : Add Two folder n App_Code Folder name as "cscode", "vbcode"

Step 3 : Now open web config file.
Add Follwing section in compilation section

codeSubDirectories>
add directoryName="vbcode"/>
add directoryName="cscode"/>
codeSubDirectories>

Datatable XML to SQL Table Conversion

2011-12-12T19:32:00.002+05:30

Declare @VarXML As XML
Set @VarXML = '

Planned
6
0

Planned
7
0

Planned
8
0

Planned
10
0

Planned
11
0

Planned
13
0

Planned
15
0

Planned
16
0

Planned
17
0

Planned
18
0

Planned
19
0

Planned
20
0

Planned
22
0

Planned
666
0

Planned
999
0

Planned
Comment

Actual
6
0

Actual
7
0

Actual
8
0

Actual
10
0

Actual
11
0

Actual
13
0

Actual
15
0

Actual
16
0

Actual
17
0

Actual
18
0

Actual
19
0

Actual
20
0

Actual
22
0

Actual
666
0

Actual
999
0

Actual
Comment

'

SELECT
x.v.value('Type[1]','varchar(10)') as type,
x.v.value('ProjectNo[1]','varchar(10)') as ProjectNo,
x.v.value('Value[1]','varchar(max)') As Value
FROM @VarXML.nodes('DocumentElement/DailyWorkReport') x(v)

Get Unique No In SQL - Excel Column Format

2011-09-28T15:14:00.002+05:30

-->>>>>>>>>>>Variable Declaration>>>>>>>>>>>

Declare @LastNo Varchar(max)='ZZ99999',
@NewNo Varchar(max) = '',
@NoOfDigit Int = 5,
@SeriesNo varchar(max) = '',
@SeriesChar varchar(max) = '',
@SeriesCharOld Varchar(max) = '',
@IncrementSeries Bit = 0,
/* Char Variable >>>>>*/
@CharA char(1)='A',@CharB char(1)='B',@CharC char(1)='C',@CharD char(1)='D',@CharE char(1)='E',
@CharF char(1)='F',@CharG char(1)='G',@CharH char(1)='H',@CharI char(1)='I',@CharJ char(1)='J',
@CharK char(1)='K',@CharL char(1)='L',@CharM char(1)='M',@CharN char(1)='N',@CharO char(1)='O',
@CharP char(1)='P',@CharQ char(1)='Q',@CharR char(1)='R',@CharS char(1)='S',@CharT char(1)='T',
@CharU char(1)='U',@CharV char(1)='V',@CharW char(1)='W',@CharX char(1)='X',@CharY char(1)='Y',
@CharZ char(1)='Z'
/* End Of char Variable<<<<<*/
Begin Try
-->>>Get Current Number>>>>>>>>>
Select @LastNo = Case When Len(RTrim(LTrim(@LastNo))) = 0
Then replace(STR(' ',@NoOfDigit),' ','0')
Else @LastNo End
Select @SeriesNo = right(@LastNo,@NoOfDigit)

If (@SeriesNo <> Replace(STR('9',@NoOfDigit),' ','9'))
Begin
Select @SeriesNo = Replace(replace(str((@SeriesNo + 1),@NoOfDigit),'0','0'),' ','0')
Set @SeriesChar = LEFT(@LastNo,LEN(@LastNo) - @NoOfDigit)
If Len(@SeriesChar) = 0
Begin
Set @SeriesChar = @CharA
End
End
Else
Begin
If Len(LTrim(RTrim(@LastNo))) <> 0
Begin
Set @SeriesCharOld = LEFT(@LastNo,LEN(@LastNo) - @NoOfDigit)
Set @IncrementSeries = 1
End
Else
Begin
Set @SeriesChar = @CharA
End
Set @SeriesNo = Replace(STR(1,@NoOfDigit),' ','0')
End
--<< -->>>>Get Series Character>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If (@IncrementSeries = 1)
Begin
--Set @SeriesChar = @SeriesCharOld
Declare @CharLen Int = 0, @LastChar Char(1)=''
Set @CharLen = Len(LTrim(RTrim(@SeriesCharOld)))
Set @LastChar = Substring(LTrim(RTrim(@SeriesCharOld)),@CharLen , 1)

If @LastChar = @CharA
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharB
End
Else If @LastChar = @CharB
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharC
End
Else If @LastChar = @CharC
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharD
End
Else If @LastChar = @CharD
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharE
End
Else If @LastChar = @CharE
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharF
End
Else If @LastChar = @CharF
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharG
End
Else If @LastChar = @CharG
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharH
End
Else If @LastChar = @CharH
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharI
End
Else If @LastChar = @CharI
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharJ
End
Else If @LastChar = @CharJ
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharK
End
Else If @LastChar = @CharK
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharL
End
Else If @LastChar = @CharL
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharM
End
Else If @LastChar = @CharM
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharN
End
Else If @LastChar = @CharN
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharO
End
Else If @LastChar = @CharO
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharP
End
Else If @LastChar = @CharP
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharQ
End
Else If @LastChar = @CharQ
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharR
End
Else If @LastChar = @CharR
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharS
End
Else If @LastChar = @CharS
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharT
End
Else If @LastChar = @CharT
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharU
End
Else If @LastChar = @CharU
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharV
End
Else If @LastChar = @CharV
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharW
End
Else If @LastChar = @CharW
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharX
End
Else If @LastChar = @CharX
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharY
End
Else If @LastChar = @CharY
Begin
Set @SeriesChar = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @CharLen - 1)
+ @CharZ
End
Else If @LastChar = @CharZ
Begin
If @SeriesCharOld = Replace(Replace(STR('0', Len(@LastNo)-@NoOfDigit) ,' ',@CharZ),'0',@CharZ)
Begin
Set @SeriesChar = @CharA + Replace(@SeriesCharOld,@CharZ,@CharA)
End
Else
Begin
Declare @LoopValue Int = 0, @LoopChar char(1) = ''
Set @LoopValue = @CharLen
While (@LoopValue>0)
Begin
Set @LoopChar = Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue, 1)
If @LoopChar = @CharZ
Begin
print Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharA+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)

Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharA+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = @LoopValue - 1
End
Else If @LoopChar = @CharA
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharB+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharB
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharC+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharC
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharD+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharD
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharE+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharE
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharF+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharF
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharG+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharG
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharH +Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharH
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharI+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharI
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharJ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharJ
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharK+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharK
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharL+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharL
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharM+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharM
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharN+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharN
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharO+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharO
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharP+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharP
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharQ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharQ
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharR+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharR
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharS+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharS
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharT+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharT
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharU+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharU
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharV+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharV
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharW+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharW
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharX+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharX
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharY+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else If @LoopChar = @CharY
Begin
Set @SeriesCharOld = Substring(LTrim(RTrim(@SeriesCharOld)), 1, @LoopValue - 1)
+@CharZ+Substring(LTrim(RTrim(@SeriesCharOld)), @LoopValue + 1, @CharLen)
Set @LoopValue = 0
End
Else
Begin
Set @LoopValue = @LoopValue - 1
End

End
Set @SeriesChar = @SeriesCharOld
End
End
End

--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-->>>>>>>>Get Your Unique Number >>>>>>>>>>>>>>>>>>>>>
Select @SeriesChar + @SeriesNo
--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

End Try
Begin Catch
Select ERROR_MESSAGE() As Error
End Catch

Get Unique No In SQL - Excel Column Format

2011-09-28T15:12:00.003+05:30

Nth Highest In SQL

2011-09-27T16:13:00.002+05:30

If Object_Id('tempdb.dbo.#EmployeeMaster') Is Not Null
Begin
Drop Table dbo.#EmployeeMaster
End
Go
Create Table dbo.#EmployeeMaster
(
EmpNo Int,
DeptId Int,
Salary Numeric(18,2)
)
Insert Into dbo.#EmployeeMaster values(1,1,4000)
Insert Into dbo.#EmployeeMaster values(2,2,1000)
Insert Into dbo.#EmployeeMaster values(3,3,400)
Insert Into dbo.#EmployeeMaster values(4,1,100)
Insert Into dbo.#EmployeeMaster values(5,2,5000)
Insert Into dbo.#EmployeeMaster values(6,3,25000)
Insert Into dbo.#EmployeeMaster values(7,1,56222)
Insert Into dbo.#EmployeeMaster values(8,2,5652)
Insert Into dbo.#EmployeeMaster values(9,3,1000)

Select *From dbo.#EmployeeMaster

Select e.EmpNo, e.DeptId, e.Salary
From dbo.#EmployeeMaster e
Where 1>=
(Select COUNT(1) From dbo.#EmployeeMaster e1
Where e.Salary <= e1.Salary And e.DeptId = e1.DeptId )

=============================================

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
EmpNo DeptId Salary
----------- ----------- ---------------------------------------
1 1 4000.00
2 2 1000.00
3 3 400.00
4 1 100.00
5 2 5000.00
6 3 25000.00
7 1 56222.00
8 2 5652.00
9 3 1000.00

(9 row(s) affected)

EmpNo DeptId Salary
----------- ----------- ---------------------------------------
6 3 25000.00
7 1 56222.00
8 2 5652.00

(3 row(s) affected)

Split String to Table

2011-09-16T12:39:00.002+05:30

If OBJECT_ID('dbo.UdfSplitStringToTable') Is Not Null
Begin
Drop Function dbo.UdfSplitStringToTable
End
/*
Function That return Table using split with specified char from string

e.g:

Select * From dbo.UdfSplitStringToTable('1¶2¶3¶4','¶')
*/
Go
Create Function dbo.UdfSplitStringToTable
(
@String varchar(max),
@char char(1)
)
Returns @Result Table(Item Varchar(max))
As
Begin

Declare @xDoc XML
Set @xDoc = ''
+ Replace(@String,@char,'')
+''

Insert Into @Result
Select T2.Loc.value('.','Varchar(max)')
From
@xDoc.nodes('/Ids/Id') As T2(Loc)

Return
End

Storeprocedure as Simple Query

2009-07-03T14:05:00.001+05:30

begin try"+
" begin transaction"+
" IF(1="+intMode.ToString()+")"+
" begin"+
//" --Check Exists or Not"+
" IF (SELECT count(*)FROM Department_Master_Desktop WHERE Department_Name = '" + strDeptName + "')=0" +
" begin"+
//-------Insert Query------------
" insert into Department_Master_Desktop ([Department_Id],[Department_Name],[Company_Id],[IsDeleted])"+
" (select"+
" (select isnull(max(Department_Id),0)+1 from Department_Master_Desktop)"+
" ,'"+strDeptName+"',1,0)" +
" select 'Inserted' as 'Result'"+
" commit transaction"+
" end"+
//Department Already Exists
" else"+
" begin"+
//--Exists
" RAISERROR('Department already Exists!',16,1)"+
" end"+
" end"+
" else if(2="+intMode.ToString()+")"+//-------Update Process----
" begin "+
" IF (SELECT count(*)FROM Department_Master_Desktop WHERE Department_Name = '" + strDeptName + "' and Department_Id<>"+DeptId .ToString()+")=0" +
" begin"+
" update Department_Master_Desktop set Department_Name='"+strDeptName+"' where Department_Id="+DeptId.ToString()+
" commit transaction"+
" select 'Updated' as 'Result'"+
" end"+
" else"+//--Department Already Exists
" begin"+
//--Exists
" RAISERROR('Department already Exists!',16,1)"+

" end"+
" end "+
" else if(3="+intMode.ToString()+")"+//--Delete Operation
" begin"+
" delete Department_Master_Desktop where Department_Id="+DeptId.ToString()+
" commit transaction "+
" select 'Deleted' as 'Result'"+
" end "+
" else if(4="+intMode.ToString()+")"+//--Select Opration
" begin"+
" select Department_Id,Department_Name from Department_Master_Desktop" +
" commit transaction"+
" end"+
" else"+
" begin"+
" RAISERROR('No Operation Found!',16,1)"+
" end"+
" end try"+//--end try
" begin catch"+ //--start catch
" rollback transaction"+
" select ERROR_MESSAGE() as 'Error'"+
" end catch

Encryption - Decryption Class

2009-06-11T10:31:00.002+05:30

using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.IO;

namespace Project_NameSpace.Classes
{
public class EncDec
{
public EncDec()
{ }
public void Dispose()
{
GC.Collect();
GC.SuppressFinalize(this);
GC.WaitForPendingFinalizers();
//GC.GetTotalMemory(true);
}
///# byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV) - encrypts a byte array with a key and an IV and returns a byte array;
///# string Encrypt(string clearText, string Password) - encrypts a string with a password and returns a string;
///# byte[] Encrypt(byte[] clearData, string Password) - encrypts a byte array with a password and returns a byte array;
///# void Encrypt(string fileIn, string fileOut, string Password) - encrypts a file with a password and writes the encrypted bytes into another file.
///
// Encrypt a byte array into a byte array using a key and an IV

public static byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
{
// Create a MemoryStream to accept the encrypted bytes

MemoryStream ms = new MemoryStream();

// Create a symmetric algorithm. We are going to use Rijndael because it is strong and
// available on all platforms.
// You can use other algorithms, to do so substitute the
// next line with something like
// TripleDES alg = TripleDES.Create();

Rijndael alg = Rijndael.Create();

// Now set the key and the IV.
// We need the IV (Initialization Vector) because
// the algorithm is operating in its default
// mode called CBC (Cipher Block Chaining).
// The IV is XORed with the first block (8 byte)
// of the data before it is encrypted, and then each
// encrypted block is XORed with the
// following block of plaintext.
// This is done to make encryption more secure.
// There is also a mode called ECB which does not need an IV,
// but it is much less secure.
alg.Key = Key;
alg.IV = IV;
// Create a CryptoStream through which we are going to be
// pumping our data.
// CryptoStreamMode.Write means that we are going to be
// writing data to the stream and the output will be written
// in the MemoryStream we have provided.
CryptoStream cs = new CryptoStream(ms,
alg.CreateEncryptor(), CryptoStreamMode.Write);

// Write the data and make it do the encryption

cs.Write(clearData, 0, clearData.Length);

// Close the crypto stream (or do FlushFinalBlock).
// This will tell it that we have done our encryption and
// there is no more data coming in,
// and it is now a good time to apply the padding and
// finalize the encryption process.

cs.Close();

// Now get the encrypted data from the MemoryStream.
// Some people make a mistake of using GetBuffer() here,
// which is not the right way.

byte[] encryptedData = ms.ToArray();

return encryptedData;
}

// Encrypt a string into a string using a password
// Uses Encrypt(byte[], byte[], byte[])

public static string Encrypt(string clearText, string Password)
{
// First we need to turn the input string into a byte array.
byte[] clearBytes =
System.Text.Encoding.Unicode.GetBytes(clearText);

// Then, we need to turn the password into Key and IV

// We are using salt to make it harder to guess our key

// using a dictionary attack -

// trying to guess a password by enumerating all possible words.

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

// Now get the key/IV and do the encryption using the
// function that accepts byte arrays.
// Using PasswordDeriveBytes object we are first getting
// 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by default
// 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is
// 8 bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off
// the algorithm to find out the sizes.

byte[] encryptedData = Encrypt(clearBytes,
pdb.GetBytes(32), pdb.GetBytes(16));

// Now we need to turn the resulting byte array into a string.
// A common mistake would be to use an Encoding class for that.
//It does not work because not all byte values can be
// represented by characters.
// We are going to be using Base64 encoding that is designed
//exactly for what we are trying to do.

return Convert.ToBase64String(encryptedData);
}

/// Encrypt bytes into bytes using a password
/// Uses Encrypt(byte[], byte[], byte[])
///

public static byte[] Encrypt(byte[] clearData, string Password)
{
// We need to turn the password into Key and IV.
// We are using salt to make it harder to guess our key
// using a dictionary attack -

// trying to guess a password by enumerating all possible words.

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

// Now get the key/IV and do the encryption using the function
// that accepts byte arrays.
// Using PasswordDeriveBytes object we are first getting
// 32 bytes for the Key
// (the default Rijndael key length is 256bit = 32bytes)
// and then 16 bytes for the IV.
// IV should always be the block size, which is by default
// 16 bytes (128 bit) for Rijndael.
// If you are using DES/TripleDES/RC2 the block size is 8
// bytes and so should be the IV size.
// You can also read KeySize/BlockSize properties off the
// algorithm to find out the sizes.
return Encrypt(clearData, pdb.GetBytes(32), pdb.GetBytes(16));
}

// Encrypt a file into another file using a password

public static void Encrypt(string fileIn,
string fileOut, string Password)
{

// First we are going to open the file streams

FileStream fsIn = new FileStream(fileIn,
FileMode.Open, FileAccess.Read);
FileStream fsOut = new FileStream(fileOut,
FileMode.OpenOrCreate, FileAccess.Write);

// Then we are going to derive a Key and an IV from the
// Password and create an algorithm

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

Rijndael alg = Rijndael.Create();
alg.Key = pdb.GetBytes(32);
alg.IV = pdb.GetBytes(16);

// Now create a crypto stream through which we are going
// to be pumping data.
// Our fileOut is going to be receiving the encrypted bytes.

CryptoStream cs = new CryptoStream(fsOut,
alg.CreateEncryptor(), CryptoStreamMode.Write);

// Now will will initialize a buffer and will be processing
// the input file in chunks.
// This is done to avoid reading the whole file (which can
// be huge) into memory.

int bufferLen = 4096;
byte[] buffer = new byte[0];
int bytesRead;

do
{
// read a chunk of data from the input file

bytesRead = fsIn.Read(buffer, 0, bufferLen);

// encrypt it

cs.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);

// close everything

// this will also close the unrelying fsOut stream

cs.Close();
fsIn.Close();
}

// Decrypt a byte array into a byte array using a key and an IV

public static byte[] Decrypt(byte[] cipherData,
byte[] Key, byte[] IV)
{
// Create a MemoryStream that is going to accept the

// decrypted bytes

MemoryStream ms = new MemoryStream();

// Create a symmetric algorithm.

// We are going to use Rijndael because it is strong and

// available on all platforms.

// You can use other algorithms, to do so substitute the next

// line with something like

// TripleDES alg = TripleDES.Create();

Rijndael alg = Rijndael.Create();

// Now set the key and the IV.

// We need the IV (Initialization Vector) because the algorithm

// is operating in its default

// mode called CBC (Cipher Block Chaining). The IV is XORed with

// the first block (8 byte)

// of the data after it is decrypted, and then each decrypted

// block is XORed with the previous

// cipher block. This is done to make encryption more secure.

// There is also a mode called ECB which does not need an IV,

// but it is much less secure.

alg.Key = Key;
alg.IV = IV;

// Create a CryptoStream through which we are going to be

// pumping our data.

// CryptoStreamMode.Write means that we are going to be

// writing data to the stream

// and the output will be written in the MemoryStream

// we have provided.

CryptoStream cs = new CryptoStream(ms,
alg.CreateDecryptor(), CryptoStreamMode.Write);

// Write the data and make it do the decryption

cs.Write(cipherData, 0, cipherData.Length);

// Close the crypto stream (or do FlushFinalBlock).

// This will tell it that we have done our decryption

// and there is no more data coming in,

// and it is now a good time to remove the padding

// and finalize the decryption process.

cs.Close();

// Now get the decrypted data from the MemoryStream.

// Some people make a mistake of using GetBuffer() here,

// which is not the right way.

byte[] decryptedData = ms.ToArray();

return decryptedData;
}

// Decrypt a string into a string using a password

// Uses Decrypt(byte[], byte[], byte[])

public static string Decrypt(string cipherText, string Password)
{
// First we need to turn the input string into a byte array.

// We presume that Base64 encoding was used

byte[] cipherBytes = Convert.FromBase64String(cipherText);

// Then, we need to turn the password into Key and IV

// We are using salt to make it harder to guess our key

// using a dictionary attack -

// trying to guess a password by enumerating all possible words.

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65,
0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

// Now get the key/IV and do the decryption using

// the function that accepts byte arrays.

// Using PasswordDeriveBytes object we are first

// getting 32 bytes for the Key

// (the default Rijndael key length is 256bit = 32bytes)

// and then 16 bytes for the IV.

// IV should always be the block size, which is by

// default 16 bytes (128 bit) for Rijndael.

// If you are using DES/TripleDES/RC2 the block size is

// 8 bytes and so should be the IV size.

// You can also read KeySize/BlockSize properties off

// the algorithm to find out the sizes.

byte[] decryptedData = Decrypt(cipherBytes,
pdb.GetBytes(32), pdb.GetBytes(16));

// Now we need to turn the resulting byte array into a string.

// A common mistake would be to use an Encoding class for that.

// It does not work

// because not all byte values can be represented by characters.

// We are going to be using Base64 encoding that is

// designed exactly for what we are trying to do.

return System.Text.Encoding.Unicode.GetString(decryptedData);
}

// Decrypt bytes into bytes using a password

// Uses Decrypt(byte[], byte[], byte[])

public static byte[] Decrypt(byte[] cipherData, string Password)
{
// We need to turn the password into Key and IV.

// We are using salt to make it harder to guess our key

// using a dictionary attack -

// trying to guess a password by enumerating all possible words.

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});

// Now get the key/IV and do the Decryption using the

//function that accepts byte arrays.

// Using PasswordDeriveBytes object we are first getting

// 32 bytes for the Key

// (the default Rijndael key length is 256bit = 32bytes)

// and then 16 bytes for the IV.

// IV should always be the block size, which is by default

// 16 bytes (128 bit) for Rijndael.

// If you are using DES/TripleDES/RC2 the block size is

// 8 bytes and so should be the IV size.

// You can also read KeySize/BlockSize properties off the

// algorithm to find out the sizes.

return Decrypt(cipherData, pdb.GetBytes(32), pdb.GetBytes(16));
}

// Decrypt a file into another file using a password

public static void Decrypt(string fileIn,
string fileOut, string Password)
{

// First we are going to open the file streams

FileStream fsIn = new FileStream(fileIn,
FileMode.Open, FileAccess.Read);
FileStream fsOut = new FileStream(fileOut,
FileMode.OpenOrCreate, FileAccess.Write);

// Then we are going to derive a Key and an IV from

// the Password and create an algorithm

PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
new byte[] {0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d,
0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76});
Rijndael alg = Rijndael.Create();

alg.Key = pdb.GetBytes(32);
alg.IV = pdb.GetBytes(16);

// Now create a crypto stream through which we are going

// to be pumping data.

// Our fileOut is going to be receiving the Decrypted bytes.

CryptoStream cs = new CryptoStream(fsOut,
alg.CreateDecryptor(), CryptoStreamMode.Write);

// Now will will initialize a buffer and will be

// processing the input file in chunks.

// This is done to avoid reading the whole file (which can be

// huge) into memory.

int bufferLen = 4096;
byte[] buffer = new byte[0];
int bytesRead;

do
{
// read a chunk of data from the input file

bytesRead = fsIn.Read(buffer, 0, bufferLen);

// Decrypt it

cs.Write(buffer, 0, bytesRead);

} while (bytesRead != 0);

// close everything

cs.Close(); // this will also close the unrelying fsOut stream

fsIn.Close();
}
}
}

Database connection Class

2009-06-11T10:29:00.004+05:30

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections ;
using System.IO;
using System.ComponentModel;
using System.Net;
using System.Windows.Forms;
using System.Data;
using System.Configuration;
using System.Xml;
//Below namespace is for execute sql-file by programatically
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace Project_NameSpace.Classes
{
class clsDataOperation
{
public string ErrorMessage = "",strDatabase = "", strUsername = "", strPassword = "", strAppName = "", strServer = "", strDbAutMod = "";
public string strToCreateDbName="";
private SqlConnection conn;
private SqlCommand comm=new SqlCommand();
public void SetAppConfigData()
{
strDatabase = ConfigurationSettings.AppSettings["database"].ToString();
strUsername = ConfigurationSettings.AppSettings["username"].ToString();
strPassword = ConfigurationSettings.AppSettings["password"].ToString();
strAppName = ConfigurationSettings.AppSettings["Application"].ToString();
strServer = ConfigurationSettings.AppSettings["server"].ToString();
strDbAutMod = ConfigurationSettings.AppSettings["Mode"].ToString();
}
public bool CheckDataBase()
{
bool bRet = false;
if (OpenMasterDbConn())//Check Master Database connection open or not
{
SetAppConfigData();
strToCreateDbName = strDatabase;//Set string database name to create
string cmdText = "select * from dbo.sysdatabases where name='" + strDatabase + "'";
DataSet ds = ExecMasterDbQry(cmdText, "Database");
if (ds.Tables[0].Rows.Count > 0)
{
bRet = true;
}

CloseConnection();
return bRet;
}
return bRet;
}
public bool CreateDatabase(string strDbScript)
{
bool bRet = false;
try
{
OpenMasterDbConn();

string cmdText1 = "create database " + strToCreateDbName;
ExecNonQuery(cmdText1, "Database");
string cmdText = strDbScript;
string qry = "use " + strToCreateDbName;
conn.Open();
comm.CommandText = qry;
comm.ExecuteNonQuery();
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(cmdText);
CloseConnection();
}
catch
{
if (CheckDataBase())
{
OpenMasterDbConn();
conn.Open();
comm.CommandText = "drop "+strToCreateDbName;
comm.ExecuteNonQuery();
conn.Close();
CloseConnection();
//Server server = new Server();
//string abc=server.ActiveDirectory.UserData.ToString();

}
bRet = false;
}
return bRet;
}
public void ExecNonQuery(string qry, string tablename)
{
try
{
OpenMasterDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
//this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
//string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
//StringReader oSr = new StringReader(strXml);
//ds.ReadXml(oSr);
//return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();

}
public void OpenDbConn()
{
try
{
//SetAppConfigData();
//Create_Database_IfNotExists();
//---Check Database exists or Not----------------
//---If Not exits than created automatically-----
//-----------------------------------------------
//conn = new SqlConnection(connstr);

// Changes Made By: Preeti B. Mathur
// Date: 07-02-2009
// START

string strConnectionString = EncDec.Decrypt(ConfigurationSettings.AppSettings["ConnString"].ToString(), "DatabaseConnectionString");
//conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"].ToString());
conn = new SqlConnection(strConnectionString);
// Changes Made By: Dhaval Chauhan
// Date: 20-02-2009
// START
//conn = new SqlConnection(connstr);
// END
conn.Open();
comm.Connection = conn;
}
catch
{
MessageBox.Show("Cannot connect database", "Connection failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool OpenMasterDbConn()
{
bool blnRes = false;
try
{
SetAppConfigData();
strDatabase = "master";
//Create_Database_IfNotExists();
//---Check Database exists or Not----------------
//---If Not exits than created automatically-----
//-----------------------------------------------
string connstr;
if (strDbAutMod == "sql")
{
connstr = "Persist Security Info=False;" + "User ID=" + strUsername + ";PWD=" + strPassword + ";Initial Catalog=" + strDatabase + ";Data Source=" + strServer + ";";
}
else
{
connstr = "Data Source=" + strServer + ";Initial Catalog=" + strDatabase + ";Integrated Security=True";
}
// Changes Made By: Preeti B. Mathur
// Date: 07-02-2009
// START
// conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"].ToString());
conn = new SqlConnection(connstr);
//"Persist Security Info=False;User ID=sa;PWD=sa123;Initial Catalog=master;Data Source=it-dhaval\\sqlexpress;"
// Changes Made By: dhaval chauhan
// Date: 07-02-2009
// START
//conn = new SqlConnection(connstr);
// END

conn.Open();
comm.Connection = conn;
blnRes = true;
}
catch
{
MessageBox.Show("Cannot connect database", "Connection failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
blnRes = false;
}
return blnRes;
}
public void AddParameter(string ParamName, SqlDbType Datatype, string Value, int ParamSize)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype, ParamSize);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public void AddParameter(string ParamName,SqlDbType Datatype,byte[] Value,int ParamSize)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype, ParamSize);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public void AddParameter(string ParamName, SqlDbType Datatype, int Value)
{
SqlParameter Param = new SqlParameter(ParamName, Datatype);
Param.Value = Value;
Param.Direction = ParameterDirection.Input;
comm.Parameters.Add(Param);
}
public DataSet ExecProc(string strProcedurename,string strTablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
OpenDbConn();
try
{
comm.CommandText = strProcedurename;
comm.CommandType = CommandType.StoredProcedure;
adp.SelectCommand = comm;
adp.Fill(ds, strTablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + strTablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
CloseConnection();
return ds;
}
public void CloseConnection()
{
conn.Close();
}
public DataSet ExecQry(string qry,string tablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
OpenDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
adp.SelectCommand = comm;
adp.Fill(ds, tablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}
public DataSet ExecMasterDbQry(string qry, string tablename)
{
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
OpenMasterDbConn();
comm.CommandText = qry;
comm.CommandType = CommandType.Text;
adp.SelectCommand = comm;
adp.Fill(ds, tablename);
}
catch (SqlException ex)
{
this.ErrorMessage = "SqlException: " + ex.Message + " // SqlErrorNumber:- " + ex.Number;
string strXml = "<" + tablename + ">100" + this.ErrorMessage + "";
StringReader oSr = new StringReader(strXml);
ds.ReadXml(oSr);
return ds;
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
CloseConnection();
return ds;
}

}
}

Save And Retrive Any format file to database

2008-11-26T10:09:00.003+05:30

//--Data base table required sql server-2005
column 1.Id(Pk)
2.FileName(nvarchar(max))
3.FileData(varbinary(max))

private void SaveFile()
{
try
{
if (txtFile .Text != "")
{
FileStream fs;
fs = new FileStream(txtFile.Text, FileMode.OpenOrCreate, FileAccess.ReadWrite);
BinaryReader br;
br=new BinaryReader(fs);
////a byte array to read the image
byte[] picbyte = br.ReadBytes ((int)fs.Length);
//byte[] picbyte = new byte[fs.Length];
// br.Close();
fs.Close();
//open the database using odp.net and insert the data
string connstr = "Data Source=IT-DHAVAL\\SQLEXPRESS;Initial Catalog=treeview;User ID=sa;PWD=sa123";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
string query;
SqlParameter Fileparameter = new SqlParameter();
query = "insert into files(FileName,FileData) values('" +txtFile.Text + "',"+"@pic)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("pic",SqlDbType.VarBinary,picbyte.Length).Value=picbyte;
cmd.ExecuteNonQuery();
MessageBox.Show("FileSave");
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnRetrive_Click(object sender, EventArgs e)
{
string connstr = "Data Source=IT-DHAVAL\\SQLEXPRESS;Initial Catalog=treeview;User ID=sa;PWD=sa123";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlDataAdapter empadap1 = new SqlDataAdapter();
empadap1.SelectCommand = new SqlCommand("SELECT * FROM files", conn);
DataSet dset = new DataSet("dset");
empadap1.Fill(dset);
DataTable dtable;
dtable = dset.Tables[0];
DataTable dataTable = dset.Tables[0];
foreach (DataRow dataRow in dataTable.Rows)
{
FileStream FS1 = new FileStream(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\" + (string)dataRow[1], FileMode.Create);
byte[] blob = (byte[])dataRow[2];
FS1.Write(blob, 0, blob.Length);
FS1.Close();
FS1 = null;
}
}

Tab based MDI from

2008-10-23T11:23:00.001+05:30

When the MDI Child Form is activated; if child form has a tab page, activate the corresponding tab page else create a tab page for child form and select tab page.

private void Form1_MdiChildActivate(object sender,
EventArgs e)
{
if (this.ActiveMdiChild == null)
tabForms.Visible = false;
// If no any child form, hide tabControl

else
{
this.ActiveMdiChild.WindowState =
FormWindowState.Maximized;
// Child form always maximized

// If child form is new and no has tabPage,

// create new tabPage

if (this.ActiveMdiChild.Tag == null)
{
// Add a tabPage to tabControl with child

// form caption

TabPage tp = new TabPage(this.ActiveMdiChild
.Text);
tp.Tag = this.ActiveMdiChild;
tp.Parent = tabForms;
tabForms.SelectedTab = tp;

this.ActiveMdiChild.Tag = tp;
this.ActiveMdiChild.FormClosed +=
new FormClosedEventHandler(
ActiveMdiChild_FormClosed);
}

if (!tabForms.Visible) tabForms.Visible = true;

}
}

When the MDI Child Form is closing, destroy the corresponding tab page. Add the following code to main form.

private void ActiveMdiChild_FormClosed(object sender,
FormClosedEventArgs e)
{
((sender as Form).Tag as TabPage).Dispose();
}

When a tab page selected, activate its child form

private void tabForms_SelectedIndexChanged(object sender,
EventArgs e)
{
if ((tabForms.SelectedTab != null) &&
(tabForms.SelectedTab.Tag != null))
(tabForms.SelectedTab.Tag as Form).Select();
}

Ajax Class

2008-09-02T14:51:00.000+05:30

//var //xmlHttp = //createXmlHttpRequestObject();
// function //createXmlHttpRequestObject()
// {
// try
// {
// // try to create XMLHttpRequest object
// //xmlHttp = new //XMLHttpRequest();
// }
//catch(e)
//{
// var //XmlHttpVersions = new //Array("MSXML2.XMLHTTP.6.0","MSXML2.XMLHTTP.5.0", //"MSXML2.XMLHTTP.4.0","MSXML2.XMLHTTP.3.0","MSXML2.XMLHTTP","Microsoft.XMLHTTP");
// for (var i=0; i // {
// try
// {
// // xmlHttp = new //ActiveXObject(//XmlHttpVersions[i]);
// }
//catch (e)
// {}
//}
//}
//if (!//xmlHttp)
// alert("Error creating the //XMLHttpRequest object.");
//else
//return //xmlHttp;
//}
//Pass Your Data in below function
//Url is Url of your Ajax Pages
//TimerFunction function name for if serverbusy then which function called e.g:test()
//TimerDelay is delay calll timer
//Wait Function in server busy mode which image call or text display
//here pass only function name like test not test().and in this funtion put status argument value off and value on
//RequestFor= type charcter which u want which user in AjaxResponse() funtion as per your value call ServerResponse Funtion that will return xmlRootnode
//Now put One Funtion name as exactly=AjaxResponse(RequestFor)
//function AjaxRequest(Url,TimerFunction,TimeDelay,WaitFunction,RequestFor)
//{
// if(//xmlHttp)
//{
// try
//{
// if((//xmlHttp.readyState==0)||(//xmlHttp.readyState==4))
// {
// try
// {
// //xmlHttp.open("GET", Url, true);
//}
// catch(e)
// {
// alert("File not found"+e);
// }
// // state Change Function
// //xmlHttp.onreadystatechange=
// function AjaxStateChange()
// {
// if(//xmlHttp.readyState==1 ||//xmlHttp.readyState==3 || //xmlHttp.readyState==2)
// {
// //If server busy then wait message comes in picture with //body disabled
// setTimeout(""+WaitFunction+"('on')"+"",0);
// }
// else if (//xmlHttp.readyState == 4)
// {
// setTimeout(""+WaitFunction+"('off')"+"",0);
//Now Server not busy and ready with output
// if (//xmlHttp.status == 200)
// {
// try
// {
//Server Response Funtion
// //AjaxResponse(RequestFor);
// }
// catch(e)
// {
// alert("Error reading the response: " + //e.toString());
// }
// }
// else
// {
// alert("There was a problem retrieving the data:\n" //+//xmlHttp.statusText);
// }
// }
// }
// //----------------
// //xmlHttp.send(null);
// }
// else
// {
// setTimeout(""+TimerFunction+"",TimeDelay);
// }
// }
// catch (e)
// {
// alert("Can't connect to server:\n" + e.toString());
// }
//}
//return false;
// }
// function AjaxResponse(RequestFor)
//{
// var //xmlResponse = //xmlHttp.responseXML;

// if (//!xmlResponse || //!xmlResponse.documentElement)
// throw("Invalid XML structure:\n" + //xmlHttp.responseText);
//var rootNodeName = ///xmlResponse.documentElement.nodeName;
//if (rootNodeName == "parsererror")
// throw("Invalid XML structure");
// //xmlRoot = xmlResponse.documentElement;
//GetResponse(//xmlRoot,RequestFor);
//}
//function GetResponse(Root,RequestFor)
//{
// if(RequestFor=='ValueToMatch')
// {
// ServerResponseFunction1(Root);
//}
//else if(RequestFor=='ValueToMatch')
//{
// ServerResponseFunction2(Root);
//}

//}

PHP_IMAGE_FROM_DATABASE

2008-08-14T08:54:00.000+05:30

To Upload DataBase:
$filename=$_FILES['imgfile']['name'];
$img="C:\\PHP\\uploadtemp\\".$_FILES['imgfile']['name'];

#
$fh = fopen($_FILES['imgfile']['tmp_name'], "rb");
#
$imgdata = addslashes(fread($fh, filesize($_FILES['imgfile']['tmp_name'])));
#
fclose($fh);
$imgdata store to database as variable
Retrive Image Form Database:
$UserId=$_REQUEST['get'];
//$UserId=1;
$dbconn = mysql_connect('localhost','root','admin') or exit("SERVER Unavailable");
$db=mysql_select_db('test',$dbconn) or exit("DB Unavailable");
$query = "SELECT Image FROM UserLogin where UserId=$UserId";
$result = mysql_query($query, $dbconn) or die('Check Sql Statement');
$result_data = mysql_fetch_array($result, MYSQL_ASSOC);
$img=$result_data['Image'];

if (!empty($img))
{
header("Content-Type: image/jpeg");
$img=$result_data['Image'];
echo $img;
}
else
{
$imgfile="../Image/NotFound.jpg";
$img=imagecreatefromjpeg($imgfile);
header("Content-Type: image/jpeg");
imagejpeg($img);
}
exit();
?>

Javascript for Work WIth Ajax

2008-08-02T08:34:00.000+05:30

// JavaScript Document
//var xmlHttp = //createXmlHttpRequestObject();

//IF Page Submit/Postback
TimerId=0;
TimerId=setTimeout('GetPastDtl(document.getElementById("CompList").value)',1000);
function createXmlHttpRequestObject()
{
try
{
// try to create XMLHttpRequest object
//xmlHttp = new //XMLHttpRequest();
}
catch(e)
{
var //XmlHttpVersions = new Array("MSXML2.XMLHTTP.6.0","MSXML2.XMLHTTP.5.0", "MSXML2.XMLHTTP.4.0","MSXML2.XMLHTTP.3.0","MSXML2.XMLHTTP","Microsoft.XMLHTTP");
//for (var i=0; i";
document.getElementById('DeptList').options.add(k);
for (var i=0; i
document.getElementById('EmpList').options.add(l);
for (var m=0; m0)
{
i=document.getElementById('EditedRowNo').value;
GetEditMode('divEd'+i,'divRd'+i,'divNameEd'+i,'divNameRd'+i,'divMailEd'+i,'divMailRd'+i,ENameDtlArray.length);
}

}
function GetEmpDetail()
{
CompId=document.getElementById('CompList').value;
DeptId=document.getElementById('DeptList').value;
EmpId=document.getElementById('EmpList').value;
PageSize=document.getElementById('hdnPageSize').value;
Offset=document.getElementById('hdnoffset').value;
CurrnentPg=document.getElementById('hdnCurrPg').value;
TotalPg=document.getElementById('hdnTotalRow').value;
if (xmlHttp)
{
try
{
if((xmlHttp.readyState==0)||(xmlHttp.readyState==4))
{
var params = "CompId=" + CompId+"&DeptId="+DeptId+"&EmpId="+EmpId+"&PageSize="+PageSize+"&Offset="+Offset;
//xmlHttp.open("GET", "Xml/EmpToDetail.php?" + params, true);
//xmlHttp.onreadystatechange = EmpDtlRequestStateChange;
//xmlHttp.send(null);
}
else
{
setTimeout('GetEmpDetail()',1000);
}
}
catch (e)
{
alert("Can't connect to server:\n" + e.toString());
}
}
}
function CreateTd(id,style,className,innerHTML)
{
Td=document.createElement("td","");
Td.setAttribute("id",id);
Td.setAttribute("style",style);
Td.className=className;
Td.innerHTML=innerHTML;
return Td;
}
function CreateTextBox(AttributeArray,ValueArray,Value)
{
inputName=document.createElement("input","");
for(i=0;i<=AttributeArray.length-1;i++) { inputName.setAttribute(AttributeArray[i],ValueArray[i]); } inputName.value=Value; return inputName; } function GridTable() { document.getElementById("EmpDetail").innerHTML=""; div=document.getElementById("EmpDetail"); //Generate Table Table=document.createElement("table",""); Table.setAttribute("style","width:100%"); Table.setAttribute("cellpadding","0"); Table.setAttribute("cellspacing","0"); Table.setAttribute("border","1"); //Generate Row Tr=document.createElement("tr",""); //Generate Column1 Td1=CreateTd('','width:40%','TdDataHeading','Name'); Tr.appendChild(Td1); //Generate Column2 Td2=CreateTd('','width:40%','TdDataHeading','Email'); Tr.appendChild(Td2); //Generate Coloumn3 Td3=CreateTd('','width:20%','TdDataHeading','Manage'); Tr.appendChild(Td3); Table.appendChild(Tr); ENameDtlArray = xmlRoot.getElementsByTagName("Ename"); EmailDtlArray = xmlRoot.getElementsByTagName("Email"); EContactIdArray=xmlRoot.getElementsByTagName("EContactId"); for (var i=0; i0)&&(i==document.getElementById('EditedRowNo').value))
{
UpdateErrMsg=UpdateArray.item(0).firstChild.data;
divErr.innerHTML=UpdateErrMsg;
}
Td1.appendChild(divErr);
Tr.appendChild(Td1);
//Generate Column2
Td2=CreateTd('','width:40%','TdData','');
divMailRd=document.createElement("div","");
divMailRd.setAttribute('id','divMailRd'+i);
divMailRd.innerHTML=mail;
Td2.appendChild(divMailRd);
divMailEd=document.createElement("div","");
divMailEd.setAttribute('id','divMailEd'+i);
divMailEd.setAttribute('style','display:none');
//Edit Text
inputMail=CreateTextBox(['type','name','id'],['text','textmail'+i,'textmail'+i],mail);
divMailEd.appendChild(inputMail)
//--
Td2.appendChild(divMailEd);
Tr.appendChild(Td2);
//Generate Column3
Td3=CreateTd('','width:20%','TdData','');
//Div for Readmode Link
DivRd=document.createElement("div","");
DivRd.setAttribute("id","divRd"+i);
EditLink=document.createElement("a","");
EditLink.setAttribute("href","Javascript");
EditLink.setAttribute("onClick",
"return GetEditMode('divEd"+i+"','divRd"+i+"','divNameEd"+i+"','divNameRd"+i+"','divMailEd"+i+"','divMailRd"+i+"',ENameDtlArray.length);");
EditLink.innerHTML="Edit";
DivRd.appendChild(EditLink);
Td3.appendChild(DivRd);
//-------
//Div for Editmode Link
DivEd=document.createElement("div","");
DivEd.setAttribute("id","divEd"+i);
DivEd.setAttribute("style","display:none");
UpdateLink=document.createElement("a","");
UpdateLink.setAttribute("href","Javascript");
UpdateLink.setAttribute("onClick","return GetDataUpdate('hdnConId"+i+"','text"+i+"','divNameRd"+i+"','textmail"+i+"','divMailRd"+i+"','divErr"+i+"');");
UpdateLink.innerHTML="Update |";
DivEd.appendChild(UpdateLink);
CancelLink=document.createElement("a","");
CancelLink.setAttribute("href","Javascript");
CancelLink.setAttribute("onClick",
"return GetReadMode('divEd"+i+"','divRd"+i+"','divNameEd"+i+"','divNameRd"+i+"','divMailEd"+i+"','divMailRd"+i+"','divErr"+i+"');");
CancelLink.innerHTML="Cancel";
DivEd.appendChild(CancelLink);
Td3.appendChild(DivEd);
//-------
Tr.appendChild(Td3);
Table.appendChild(Tr);
}

return div.appendChild(Table);
}
function CheckName(Name)
{
alert(Name);
return false;
}
function GetEditMode(divEdit,divRead,divNameEd,divNameRd,divMailEd,divMailRd,Trow)
{
document.getElementById('EditedRowNo').value=divEdit.split('divEd')[1];
var i;
i=0;
for(i=0;i1))
{
document.getElementById('hdnCurrPg').value=parseInt(currPg)-1;
document.getElementById('hdnoffset').value=parseInt(document.getElementById('hdnoffset').value)-parseInt(PageSize);
GetEmpDetail();
}
return false;
}
function DropChange()
{
document.getElementById('hdnCurrPg').value=1;
document.getElementById('hdnoffset').value=0;
document.getElementById('hdnTotalRow').value='';
document.getElementById('hdnTotalPage').value='';
GetEmpDetail();
}

Gridview- Pagging

2008-05-29T15:57:00.000+05:30

--------------Step-1----------
Add Folowing code in .aspx page in asp:gridview paging templete
------------------------------

EnableViewState="false" Text="Previous" CommandName="page" CommandArgument="prev">

|
EnableViewState="false" Text="Next" CommandName="page" CommandArgument="next">

Text=' SSLib.GetPosition(gvOrderList)'>

-----------------------------------------------
---------------------step-2-------------------
in back-side-aspx.vb page add following
----------------------------------------------
Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete
Dim Paging As New GridViewPaging
Paging.GridViewPaging(gvClientList)
End Sub
-------------------------------------------------
---------------------step-3------------------
-------------------------------------------
add following 2-class in project------------
-------------------------------------------
Class1=
-------
Public Class GridViewPaging
Function GridViewPaging(ByVal GridViewId As GridView) As Boolean
Dim pagetoprow As GridViewRow = GridViewId.TopPagerRow
Dim pagerbotomrow As GridViewRow = GridViewId.BottomPagerRow
If Not (IsNothing(pagerbotomrow)) Then
If (GridViewId.PageIndex) = 0 Then
CType(GridViewId.BottomPagerRow.FindControl("lbPrevious"), LinkButton).Enabled = False
CType(GridViewId.BottomPagerRow.FindControl("lbPrevious"), LinkButton).CssClass = "DisableLink"
End If
If (GridViewId.PageIndex + 1) = GridViewId.PageCount Then
CType(GridViewId.BottomPagerRow.FindControl("lbNext"), LinkButton).Enabled = False
CType(GridViewId.BottomPagerRow.FindControl("lbNext"), LinkButton).CssClass = "DisableLink"
End If
End If
If Not (IsNothing(pagetoprow)) Then
If (GridViewId.PageIndex) = 0 Then
CType(GridViewId.TopPagerRow.FindControl("lbPrevious"), LinkButton).Enabled = False
CType(GridViewId.TopPagerRow.FindControl("lbPrevious"), LinkButton).CssClass = "DisableLink"
End If
If (GridViewId.PageIndex + 1) = GridViewId.PageCount Then
CType(GridViewId.TopPagerRow.FindControl("lbNext"), LinkButton).Enabled = False
CType(GridViewId.TopPagerRow.FindControl("lbNext"), LinkButton).CssClass = "DisableLink"
End If
End If
End Function
---------------------------------------
------class2-----------
--------------------------------------
Public Shared Function GetPosition(ByVal gv As GridView) As String
Return "( " & (gv.PageIndex + 1).ToString & " of " & gv.PageCount & " )"
End Function

Public Shared Function GetPosition(ByVal fv As FormView) As String
Return "( " & (fv.PageIndex + 1).ToString & " of " & fv.PageCount & " )"
End Function

Public Shared Function GetPageIndex(ByVal idx As Integer) As Integer
Return idx + (CInt(HttpContext.Current.Request("Pg")) * 12)
End Function

Javascript-Auto call function

2008-05-29T14:17:00.000+05:30

onLoad=window.setTimeout("StartText(25)",100);
var y
var timerID = 0;
function StartText(x)
{
document.getElementById ('AdOrCon').style .fontSize =x+"px";
if (x==40)
{y=1;}
else{
y=x+1;}
clearTimeout(timerID);
timerID=0;
timerID=setTimeout("StartText(y)",100);
}
function showTab(x)
{
document.getElementById('Div1').style.display="none";
document.getElementById('Div2').style.display="none";
document.getElementById('Div3').style.display="none";
document.getElementById('Div4').style.display="none";
document.getElementById('Div5').style.display="none";
document.getElementById('Div'+x).style.display="";
if (x==5)
{ y=1 ;}
else
{ y=x+1; }
clearTimeout(timerID);
timerID=0;
timerID = setTimeout("showTab(y)", 10000);

}

Javascript-window open

2008-05-29T11:42:00.000+05:30

window.open(('ZoomImage.aspx?Categoryid=' + catid + '&pageIndex=' + curpg + '&RowPerPage=100&RowCnt=0&PgCnt=0&PgInfo=0'),'zoomimage','status=no,width=550,height=500, resizable= no, scrollbars=Yes, toolbar=no,location=no,menubar=no,left=200,top=20','');

Shopping Cart With Session

2008-05-21T14:32:00.000+05:30

Imports System.Data
Partial Class ViewCart
Inherits System.Web.UI.Page
Dim objDT As System.Data.DataTable
Dim objDR As System.Data.DataRow
Public Total As Decimal

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
If Session("StartCart") = Nothing Then
makeCart()
AddToCart(Nothing, Nothing)
GetItemTotal()
Else
objDT = Session("Cart")
Dim itemCount As String = objDT.Rows.Count
Dim sessICount() As String = Session.Item("Product").Split(",")
If itemCount <> sessICount.Length Then
'--------
Dim i As Integer = 0
For i = (itemCount) To sessICount.Length - 1
'Dim Product = a(i)
Dim adp_CartPrd As New ViewCartDsTableAdapters.Product_MasterTableAdapter
Dim tb_CartPrd As New ViewCartDs.Product_MasterDataTable
adp_CartPrd.FillProductByPId(tb_CartPrd, sessICount(i))
objDR = objDT.NewRow
objDR("Quantity") = 1
objDR("Image") = tb_CartPrd.Rows(0).Item("ProdImage")
objDR("Product") = tb_CartPrd.Rows(0).Item("ProdName")
objDR("Cost") = Decimal.Parse(123.5)
objDR("ProdId") = tb_CartPrd.Rows(0).Item("ProdId")
objDT.Rows.Add(objDR)
Session("Cart") = objDT
gvCart.DataSource = objDT
gvCart.DataBind()
GetItemTotal()
Next
'---------
Else
gvCart.DataSource = objDT
gvCart.DataBind()
GetItemTotal()
End If

End If
Else
Dim i As Integer
i = 0
makeCart()
For i = 0 To gvCart.Rows.Count - 1
Dim sess As String = Session("Product")
Dim a() As String = sess.Split(",")
Dim Product = a(i)
Dim adp_CartPrd As New ViewCartDsTableAdapters.Product_MasterTableAdapter
Dim tb_CartPrd As New ViewCartDs.Product_MasterDataTable
adp_CartPrd.FillProductByPId(tb_CartPrd, a(i))
objDR = objDT.NewRow
objDR("Quantity") = CType(gvCart.Rows(i).FindControl("textQuantity"), TextBox).Text
objDR("Image") = tb_CartPrd.Rows(0).Item("ProdImage")
objDR("Product") = tb_CartPrd.Rows(0).Item("ProdName")
objDR("Cost") = Decimal.Parse(123.5)
objDR("ProdId") = tb_CartPrd.Rows(0).Item("ProdId")
objDT.Rows.Add(objDR)
Next
Session("Cart") = objDT
gvCart.DataSource = objDT
gvCart.DataBind()
GetItemTotal()
End If
End Sub
Sub AddToCart(ByVal s As Object, ByVal e As EventArgs)
Dim sess As String = Session("Product")
Dim a() As String = sess.Split(",")
Dim i As Integer
i = 0
objDT = Session("Cart")
Session("StartCart") = "Start"
For i = 0 To a.Length - 1
Dim Product = a(i)
Dim adp_CartPrd As New ViewCartDsTableAdapters.Product_MasterTableAdapter
Dim tb_CartPrd As New ViewCartDs.Product_MasterDataTable
adp_CartPrd.FillProductByPId(tb_CartPrd, a(i))
objDR = objDT.NewRow
objDR("Quantity") = 1
objDR("Image") = tb_CartPrd.Rows(0).Item("ProdImage")
objDR("Product") = tb_CartPrd.Rows(0).Item("ProdName")
objDR("Cost") = Decimal.Parse(123.5)
objDR("ProdId") = tb_CartPrd.Rows(0).Item("ProdId")
objDT.Rows.Add(objDR)
Session("Cart") = objDT
gvCart.DataSource = objDT
gvCart.DataBind()
Next
End Sub
Function makeCart()
objDT = New System.Data.DataTable("Cart")
'objDT.Columns.Add("ID", GetType(Integer))
'objDT.Columns("ID").AutoIncrement = True
'objDT.Columns("ID").AutoIncrementSeed = 1
objDT.Columns.Add("ProdId", GetType(String))
objDT.Columns.Add("Image", GetType(String))
objDT.Columns.Add("Product", GetType(String))
objDT.Columns.Add("Quantity", GetType(Integer))
objDT.Columns.Add("Cost", GetType(Decimal))
Session("Cart") = objDT
Return ""
End Function
Function GetItemTotal() As Decimal
Dim intCounter As Integer
Dim decRunningTotal As Decimal
For intCounter = 0 To objDT.Rows.Count - 1
objDR = objDT.Rows(intCounter)
decRunningTotal += (objDR("Cost") * objDR("Quantity"))
Next
Total = decRunningTotal
Return decRunningTotal
End Function