将XML标记中的批量数据发送到SQL Server问题

问题描述

海，
我正在将批量数据(序列号，引脚号)插入DB.在插入之前，数据表中的数据已绑定到XML标记中.此处的引脚号被加密了一个，如下所示.
strXml ="version =" + @""1.0""encoding =" + @""iso-8859-1"?>< batch>";
strPinXml = strPinXml +"";
strXml = strXml + strPinXml +";

问题是插入到db中之后，要验证是否插入了实际的pinnumber(db中的加密格式)，我解密了pinnumber并发现，

->所有数据中的第一个数字都用(')单引号引起来，某些针脚编号的最后一位为空(如果针脚编号为-–A?_¡/Ì·ÞvËÛ(即)，则该针脚最后一位用ending结尾为空).

请提供解决此问题的解决方案

结果如下-

插入db之前的引脚
引脚号(插入时)
(加密格式)---(解密格式)

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


插入后从db中检索到的引脚
引脚号(从db检索)检索引脚号
(加密格式)----(解密格式)

A [¦，ȵØzËÚ ----’613051524692
A_¡/Ì•ÞvËÛ----’68776574868
?? @ X¦！Ï´ÝÎÛ ----’77008647138
AZ¡+ɹÝwÏÒ----’642720979218
O Q¢(˹Þ{ËÛ----’879412945686
O ^¡，ȶÝ}Î×----’846751673342


XML标记数据发送到数据库

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

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

推荐答案

strPinXml =strPinXml + "<data cardid="+@" hold=" />                         " pinnumber=" + @"></data>"

放入

strPinXml += string.Format("<data cardid="\"{0}\"" pinnumber="\"{1}\"" />"), strid, myRC4Engine.CryptedText);

最好阅读

it is better to read

这篇关于将XML标记中的批量数据发送到SQL Server问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！