SQL Server's solution for storing massive data into the database

1: In order to reduce the number of database connections for storing large volumes of data, you can use XML to store data at one time. ReferenceCodeAs follows:

SQL XML format
Create   Procedure B2B _cpersoncard_xml
-- Author: Sey
-- Description: insert into B2B _cpersoncard
-- Datetime: 2009-08-13
@ XML   Nvarchar ( Max )
As
Begin
Declare   @ Idhandle   Int
Exec Sp_xml_preparedocument @ Idhandle Output, @ XML -- Create XML handle
Print   @ Idhandle

Insert   Into   [ B2B _cpersoncard ] (Cempid, ctype, cname, cCode, cairid)
Select Cempid, ctype, cname, cCode, cairid From Openxml ( @ Idhandle , N ' /Root/personcard ' ) With   [ B2B _cpersoncard ]

If   @ Error = 0
Begin
Select   1
End
Else
Begin
Select   0
End

Exec sp_xml_removedocument @ idhandle -- the XML file is stored in the cache. To avoid insufficient memory, execute this statement to release the memory.

End

 

2: C # organize the XML format as follows:

 

 

C # xml Organization Method
Public   Bool Addpersoncard (list < Personcard > List, String Ptype, Int Pempid)
{
// Throw new exception ("the method or operation is not implemented .");

Xmldocument document =   New Xmldocument ();
Xmlelement Root = Document. createelement ( " Root " );
Document. appendchild (Root );
Foreach (Personcard personentity In List)
{
Xmlelement xmlperson = Document. createelement ( " Personcard " );
Xmlperson. setattribute ( " Cempid " , Pempid. tostring ());
Xmlperson. setattribute ( " Ctype " , Ptype );
Xmlperson. setattribute ( " Cname " , Personentity. cradname );
Xmlperson. setattribute ( " CCode " , Personentity. cardcode );
Xmlperson. setattribute ( " Cairid " , Personentity. airid );
Root. appendchild (xmlperson );
}
Sqlparameter [] Parameters =   Null ;
Parameters =   New Sqlparameter [] { New Sqlparameter ( " @ XML " , Document. innerxml )};
Try
{
Int Result =   0 ;
Result =   Int . Parse (dblib. getdatatablebysp ( " B2B _cpersoncard_xml " , Parameters, dbconnenm. b2bdb). Rows [ 0 ] [ 0 ]. Tostring ());
If (Result >   0 )
{
Return   True ;
}
Else
{
Return   False ;
}
}
Catch (Exception E)
{
Throw E;
}
}

 

Organize the data into a list!