Hai,
I am inserting bulk data(serial number,pin number) to DB.Before inserting,the data from datatable is binded into XML tag.Here the pin number is encrypted one...as follows,
strXml = "<?xml version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?><batch>";
strPinXml += "<data cardid="+@"""" +strid+@""""+
" pinnumber=" + @"""" + myRC4Engine.CryptedText + @"""" + "></data>";
Problem is after inserting into db, to verify whether the actual pinnumber(encrypted format in db) is inserted, i decrypted the pinnumber and found that,
Please provide the solution to resolve this issue
->The first digit in all data are displaced by (’)single quote and last digit for some pinnumber is empty (if the pinnumber is-œA_¡/Ì·ÞvËÛ (ie)ending in Û for that pins last digit is empty).
Result as follows
Pins before inserting into db
(Encrypted format) --- (Decrypted format)
šA [¦,ȵØzËÚ <--eg--> 7613051524692
œA _¡/Ì•ÞvËÛ <--eg--> 1687765748683
™@ X¦!Ï´ÝÎÛ <--eg--> 4770086471383
žA Z¡+ɹÝwÏÒ <--eg--> 3642720979218
•O Q¢(˹Þ{ËÛ <--eg--> 8879412945686
ŸO_^¡,ȶÝ}Î× <--eg--> 2846751673342
Pins retrieved from db after insertion
(Encrypted format) ---- (Decrypted format)
A [¦,ȵØzËÚ <--eg--> ’613051524692
A _¡/Ì•ÞvËÛ <--eg--> ’68776574868
@ X¦!Ï´ÝÎÛ <--eg--> ’77008647138
A Z¡+ɹÝwÏÒ <--eg--> ’642720979218
O Q¢(˹Þ{ËÛ <--eg--> ’879412945686
O ^¡,ȶÝ}Î× <--eg--> ’846751673342
XML tag data sending to db from code-behind
<batch>
<data cardid="008900320000" pinnumber="šA[¦,ȵØzËÚ"></data>
<data cardid="008900320001" pinnumber="œA_¡/Ì·ÞvËÛ"></data>
<data cardid="008900320002" pinnumber="™@X¦!Ï´ÝÎÛ"></data>
<data cardid="008900320003" pinnumber="žAZ¡+ɹÝwÏÒ"></data>
<data cardid="008900320004" pinnumber="•OQ¢(˹Þ{ËÛ"></data>
<data cardid="008900320005" pinnumber="ŸO^¡,ȶÝ}Î×"></data>
</batch>
Application coding as follows
try
{
RC4Engine myRC4Engine = new RC4Engine();
myRC4Engine.EncryptionKey = "ab48495fdjk4950dj39405fk";
strXml = "<?xml version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?> <batch>";
foreach (DataRow lobjbaseBatchDetail in dt.Rows)
{
myRC4Engine.InClearText = lobjbaseBatchDetail[3].ToString();
myRC4Engine.Encrypt();
strCardid = lobjbaseBatchDetail[0].ToString();
strBatchid = lobjbaseBatchDetail[1].ToString();
strid = strCardid + strBatchid + lobjbaseBatchDetail[2].ToString();
strPinXml += "<data cardid="+@"""" +strid+@""""+
" pinnumber=" + @"""" + myRC4Engine.CryptedText + @"""" + "></data>";
}
strXml = strXml + strPinXml + "</batch>";
SqlParameter[] arrParam = new SqlParameter[1];
arrParam[0] = new SqlParameter("@BATCHUPLOAD_XML", SqlDbType.Text );
arrParam[0].Direction = ParameterDirection.Input;
arrParam[0].Value = strXml;
iResult = SqlHelper.ExecuteNonQuery(objTrans, CommandType.StoredProcedure, "test_proc", arrParam);
objTrans.Commit();
}
catch(Exception ex)
{
objTrans.Rollback();
throw new Exception("Upload failed :" + ex.Message);
}
stored procedure
create procedure test_proc
(
@BATCHUPLOAD_XML text
)
as
begin
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @BATCHUPLOAD_XML
insert into test_table_new
SELECT cardid,pinnumber
FROM OPENXML (@idoc, '/batch/data')
WITH (cardid varchar(100) '@cardid', pinnumber nvarchar(200) '@pinnumber')
EXEC sp_xml_removedocument @idoc
end
For Encryption and Decryption
public class RC4Engine
{
#region Costructor
public RC4Engine()
{
}
#endregion
#region Public Method
public bool Encrypt()
{
bool toRet = true;
try
{
long i=0;
long j=0;
Encoding enc_default = Encoding.Default;
byte[] input = enc_default.GetBytes(this.m_sInClearText);
byte[] output = new byte[input.Length];
byte[] n_LocBox = new byte[m_nBoxLen];
this.m_nBox.CopyTo(n_LocBox,0);
long ChipherLen = input.Length + 1;
for ( long offset = 0; offset < input.Length ; offset++ )
{
i = ( i + 1 ) % m_nBoxLen;
j = ( j + n_LocBox[i] ) % m_nBoxLen;
byte temp = n_LocBox[i];
n_LocBox[i] = n_LocBox[j];
n_LocBox[j] = temp;
byte a = input[offset];
byte b = n_LocBox[(n_LocBox[i]+n_LocBox[j])% m_nBoxLen];
output[offset] = (byte)((int)a^(int)b);
}
char[] outarrchar = new char[enc_default.GetCharCount(output,0,output.Length)];
enc_default.GetChars(output,0,output.Length,outarrchar,0);
this.m_sCryptedText = new string (outarrchar);
}
catch
{
toRet = false;
}
return ( toRet );
}
public bool Decrypt()
{
bool toRet = true;
try
{
this.m_sInClearText = this.m_sCryptedText;
m_sCryptedText = "";
if (toRet = Encrypt())
{
m_sInClearText = m_sCryptedText;
}
}
catch
{
toRet = false;
}
return toRet;
}