将批量数据作为XML标记发送到SQL Server的问题 [英] Issue of Sending Bulk Data as XML tag to SQL server
问题描述
海，
我正在将批量数据(序列号，引脚号)插入DB.在插入之前，数据表中的数据已绑定到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 = "<?xml version=" + @"""1.0"" encoding=" + @"""iso-8859-1""?><batch>";
strPinXml += "<data cardid="+@"""" +strid+@""""+
" pinnumber=" + @"""" + myRC4Engine.CryptedText + @"""" + "></data>";
问题是插入到db中之后，要验证是否插入了实际的pinnumber(db中的加密格式)，我解密了pinnumber并发现，
请提供解决此问题的解决方案
->所有数据中的第一个数字都用(')单引号引起来，某些针脚编号的最后一位为空(如果针脚编号为-–A?_¡/Ì·ÞvËÛ(即)，则该针脚最后一位用ending结尾为空).
结果如下
插入db之前的引脚
(加密格式)---(解密格式)
šA[¦，ȵØzËÚ<-eg-> 7613051524692
œA_¡/Ì•ÞvËÛ<-例如-> 1687765748683
™@ X¦！Ï´Ý?<-eg-> 4770086471383
žAZ¡+ɹÝwÏÒ<-例如-> 3642720979218
•O Q¢(˹Þ{ËÛ<-eg-> 8879412945686
ŸO_^¡，ȶÝ}×<-eg-> 2846751673342
插入后从db中检索到的针
(加密格式)----(解密格式)
A [¦，ȵØzËÚ<-eg-> ’613051524692
A_¡/Ì•ÞvËÛ<-eg-> ’68776574868
@ X¦！Ï´Ý?<-eg-> ’77008647138
AZ¡+ɹÝwÏÒ<-例如-> ’642720979218
O Q¢(˹Þ{ËÛ<-eg->’879412945686
O ^¡，ȶÝ}α<-eg-> ’846751673342
XML标签数据从后台代码发送到db
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;
}
推荐答案
该死的.奇怪的是，您和 http://www.codeproject.com/script/Membership/View.aspx? mid = 8735212 [ ^ ]这个家伙正在从地球的相反两面编写完全相同的代码.
由于您的代码无法编译，因此我不得不用困难的方式做事.我真的怀疑您的代码是问题.如果查看数据库的输入内容以及从数据库中获取的内容，您会发现字符串是不同的.由于ISO-8859-1是用于单字节字符集的，所以我的猜测是，如果您从XML中删除该属性，它可能会开始工作.
请参见 http://social.msdn.microsoft. com/Forums/zh-CN/sqlxml/thread/e0b6cc60-4b1c-4124-bdf3-099e2897976b [
What the hell. Oddly enough you and http://www.codeproject.com/script/Membership/View.aspx?mid=8735212[^] this guy are working on the exact same code from opposite sides of the planet.
Since your code does not compile I had to do things the hard way. I really doubt that your code is the problem. If you look at your input to the database and what you are getting out of it you will observe that the strings are not the same. Since ISO-8859-1 is for a single byte character set, my guess would be that if you remove that attribute from your XML it might start working.
See here http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/e0b6cc60-4b1c-4124-bdf3-099e2897976b[^]
这篇关于将批量数据作为XML标记发送到SQL Server的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
