将批量数据作为XML标记发送到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;
		}

推荐答案

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

1403页，肝出来的..