Click here to Skip to main content
15,884,388 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
C#
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>
Posted
Updated 9-Jan-13 1:53am
v2
Comments
[no name] 9-Jan-13 7:45am    
http://support.microsoft.com/kb/316005

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900