Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
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 = " version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?><batch>";
strPinXml =strPinXml + "";
strXml = strXml + strPinXml + "";

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,

->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).

Please provide the solution to resolve this issue

Result as follows-

Pins before inserting into db
Pinnumber(While inserting)
(Encrypted format) --- (Decrypted format)

šA [¦,ȵØzËÚ ---- 7613051524692
œA _¡/Ì•ÞvËÛ ---- 1687765748683
™@ X¦!Ï´ÝÎÛ ---- 4770086471383
žA Z¡+ɹÝwÏÒ ---- 3642720979218
•O Q¢(˹Þ{ËÛ ---- 8879412945686
ŸO_^¡,ȶÝ}Î× ---- 2846751673342


Pins retrieved from db after insertion
Pinnumber (Retrieved from db) Retrieved pinnumber
(Encrypted format) ---- (Decrypted format)

šA [¦,ȵØzËÚ ---- ’613051524692
œA _¡/Ì•ÞvËÛ ---- ’68776574868
™@ X¦!Ï´ÝÎÛ ---- ’77008647138
žA Z¡+ɹÝwÏÒ ---- ’642720979218
•O Q¢(˹Þ{ËÛ ---- ’879412945686
ŸO ^¡,ȶÝ}Î× ---- ’846751673342


XML tag data sending to db

XML
<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

C#
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 =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
SQL
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
Posted
Updated 16-Mar-12 5:08am
v3
Comments
Herman<T>.Instance 16-Mar-12 11:11am
   
pinnumber is now set to nvarchar in your sp. what happens if you set it to varchar ?

1 solution

..since the comment couldn't take some of the signs..
I have one tip for you:
change
C#
strPinXml =strPinXml + "<data cardid="+@" hold=" />                         " pinnumber=" + @"></data>"
into
C#
strPinXml += string.Format("<data cardid="\"{0}\"" pinnumber="\"{1}\"" />"), strid, myRC4Engine.CryptedText);

it is better to read
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900