Using sqlbulkcopy in C# asp.net to write xml data to a SQL server

c# sqlbulkcopy sql-server xml

Question

On my system, there are two XML files named customers.xml and customerdetails.xml. I'm trying to write this data straight to an asp.net-made sql database. My whole code is done in C#. CustomerID, CustomerN, CustomerLN, CustomerAdd, CustomerTelNo, and Comments are the columns found in the CustomerDetails table in the sql database.

My update page in Asp.net features an upload control that I'm trying to use to download the xml file and publish the data to the database. The control's code is as follows:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication9
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
if (fileExt == ".xml")
{
try
{
DataSet reportData = new DataSet();
SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;  
AttachDbFilename=|DataDirectory|\CustomerDetails.mdf;Integrated  
Security=True;User Instance=True");
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "CustomerInfo";
connection.Open();
sbc.WriteToServer(reportData.Tables[0]);
}
catch (Exception ex)
{
 Label1.Text = "ERROR: " + ex.Message.ToString();
 }
 }
 else
 {
                Label1.Text = "Only .xml files allowed!";
            }
        }
        else
        {
            Label1.Text = "You have not specified a file.";
        }
    }
}

}

However, when I try to download the file, I get a message that states, "Cannot locate table [0];"

Can somebody please assist me in figuring out what the issue is?

1
2
9/23/2011 4:25:13 PM

Popular Answer

Before making a call, you must find a mechanism to enter your XML data into the DataSet. Tables[0]. Utilize the.ReadXml() data into your dataset using the DataSet class's load() method:

FROM: 12-12-12

DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("table1");
dataTable.Columns.Add("col1", typeof(string));
dataSet.Tables.Add(dataTable);

string xmlData = "<XmlDS><table1><col1>Value1</col1></table1><table1><col1>Value2</col1></table1></XmlDS>";

System.IO.StringReader xmlSR = new System.IO.StringReader(xmlData);

dataSet.ReadXml(xmlSR, XmlReadMode.IgnoreSchema);

After that, you should be able to dial.sbc.WriteToServer(reportData.Tables[0]); no issues.

1
9/23/2011 4:31:40 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow