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>
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)