Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET 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>
Posted 9-Jan-13 1:43am
Edited 9-Jan-13 1:53am
v2

1 solution

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100