using System; using System.Data; using System.Data.SqlClient; using System.IO; // End of code to move // private void InsertXmlCustomersUsingSqlBulkCopy() { // (YOU MUST CHANGE THE CONNECTION STRING TO MATCH YOUR SYSTEM) String sDatabaseConnectionString = @"Data Source=SERVERNAME;Initial Catalog=XMLTest;Integrated Security=True"; // // (YOU MUST CHANGE THE PATH TO YOUR SYSTEMS DRIVE:\PATH) String sXMLFile = @"D:\XMLData\Customers.XML"; try { // Instanciate a new Sql Connection. using (SqlConnection oConn = new SqlConnection(sDatabaseConnectionString)) { // Open the Connection to the database oConn.Open(); // Instanciate a new DataSet using (DataSet dsTemp = new DataSet()) { //Read the XML file into the DataSet dsTemp.ReadXml(sXMLFile); // Instante a datatable from the DataSet using (DataTable dt = dsTemp.Tables[0]) { // Instanciate a new SqlBulkCopy object using the connection using (SqlBulkCopy sb = new SqlBulkCopy(oConn)) { // Assign BatchSize sb.BatchSize = 50; // Assign Destination Table Name sb.DestinationTableName = "Customers"; // Map fields from DB Field Names to the XML Field Names sb.ColumnMappings.Add("ID", "ID"); sb.ColumnMappings.Add("FirstName", "FirstName"); sb.ColumnMappings.Add("LastName", "LastName"); sb.ColumnMappings.Add("DOB", "DOB"); sb.ColumnMappings.Add("Address", "Address"); sb.ColumnMappings.Add("City", "City"); sb.ColumnMappings.Add("State", "State"); sb.ColumnMappings.Add("Zip", "Zip"); sb.WriteToServer(dt); } } } } } // Catch any SQL errors first catch (SqlException ex) { MessageBox.Show("Sql Error: " + ex.Message); } // Was not a SQL error, so handle the Exception catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } }
XML SqlBulkCopy in C#
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment