如何从xml批量插入到SQL Server [英] How to bulk insert from xml to sql server

问题描述

public void BulkCopyXMLToTable(string ConnectionString)
    {
        try
        {
            string strUplodedfilePath = string.Empty;
           
            XmlDocument xmlDoc = new XmlDocument();
            //xmlDoc = LoadXMLDoc(Server.MapPath("DataBackup/filename.xml"));

            if (fuXmlImport.HasFile)
            {
                string strFilePath = Server.MapPath(string.Format("DataBackup"));
                if (!Directory.Exists(strFilePath))
                    Directory.CreateDirectory(strFilePath);

                strUplodedfilePath = string.Format("{0}/{1}", strFilePath, fuXmlImport.FileName);
                fuXmlImport.PostedFile.SaveAs(strUplodedfilePath);
                

                if (File.Exists(strUplodedfilePath))
                {
                    xmlDoc = LoadXMLDoc(strUplodedfilePath);

                    List<string> tablenode = new List<string>(); /*Table list*/
                    XmlNodeList xnList = xmlDoc.DocumentElement.SelectNodes("Tables/row");
                    

                    foreach (XmlNode xn in xnList)
                    {
                        tablenode.Add(xn.InnerText);

                    }
                    CreateTruncateTableScript(tablenode, ConnectionString); /*Truncate Tables*/

                    //XmlNode node;
                    for (int index = 0; index < tablenode.Count; index++)
                    {
                        XmlNode node = xmlDoc.DocumentElement.SelectSingleNode(tablenode[index]);
                        string str = node.OuterXml.ToString();
                        DataSet ds = new DataSet();
                        byte[] buffer = Encoding.UTF8.GetBytes(node.OuterXml);
                        using (MemoryStream stream = new MemoryStream(buffer))
                        {
                            XmlReader reader = XmlReader.Create(stream);
                            ds.ReadXml(reader);

                            if (ds != null && ds.Tables[0].Rows.Count > 0)
                            {

                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                                {
                                    bulkCopy.DestinationTableName = string.Format("{0}", tablenode[index]);
                                    try
                                    {
                                        // Write from the source to the destination.
                                        bulkCopy.WriteToServer(ds.Tables[0]);
                                    }
                                    catch (Exception ex)
                                    {
                                        throw ex;
                                    }
                                }
                            }
                        }
                    }
                    lblMessageImport.Text = "Data imported successfully.";
                    File.Delete(strUplodedfilePath);
                }
            }
        }
        catch (Exception)
        {

            throw;
        }
    }

 public XmlDocument LoadXMLDoc(string fileName)
    {
        XmlDocument xdoc;
        long lnum;

        try
        {
            xdoc = new XmlDocument();
            //  xdoc.Load(fileName);
            using (StreamReader sr = new StreamReader(fileName, true))
            {
                xdoc.Load(sr);
            }
        }
        catch (XmlException ex)
        {
            lnum = ex.LineNumber;
            xdoc = LoadXMLDoc(fileName);
        }
        return (xdoc);
    }

 protected void CreateTruncateTableScript(List<string> tablenodeList, string ConnectionString)
    {
        StringBuilder strData = new StringBuilder();

        foreach (string tablename in tablenodeList)
        {
           strData.AppendLine(string.Format("Truncate table {0}",tablename));
        }
        //Execute Script
        ExecuteScriptQuery(strData.ToString(), ConnectionString);
    }

 protected void ExecuteScriptQuery(string strScript, string ConnectionString)
    {
        try
        {
            SqlConnection Connection = new SqlConnection(ConnectionString);
            // split script on GO command
            IEnumerable<string> commandStrings = Regex.Split(strScript, "^\\s*GO\\s*$",
                                     RegexOptions.Multiline | RegexOptions.IgnoreCase);

            if (Connection.State != ConnectionState.Closed) Connection.Close();
            Connection.Open();
            foreach (string commandString in commandStrings)
            {
                if (commandString.Trim() != "")
                {
                    new SqlCommand(commandString, Connection).ExecuteNonQuery();
                }
            }
            Connection.Close();
        }
        catch { }
    }</string></string></string></string>

推荐答案

，
RegexOptions.Multiline | RegexOptions.IgnoreCase）;

if （Connection.State！= ConnectionState.Closed）Connection .Close（）;
Connection.Open（）;
foreach （ string 合作mmandString in commandStrings）
{
if （commandString.Trim（）！= ）
{
新的 SqlCommand（commandString，Connection）.ExecuteNonQuery（）;
}
}
Connection.Close（）;
}
catch {}
} < / string > < / 字符串 > < / string > < / 字符串 >

", RegexOptions.Multiline | RegexOptions.IgnoreCase); if (Connection.State != ConnectionState.Closed) Connection.Close(); Connection.Open(); foreach (string commandString in commandStrings) { if (commandString.Trim() != "") { new SqlCommand(commandString, Connection).ExecuteNonQuery(); } } Connection.Close(); } catch { } }</string></string></string></string>

http://msdn.microsoft.com/en-us/library/ms188365.aspx [ ^ ]





处理BULK数据输入从CSV到SQL Server [ ^ ]

这篇关于如何从xml批量插入到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！