cool, I have a mcv project so I guess I can just stick this code into my home controller to get it to run quickly? hmm.. DataSource is not recognised :(
public ActionResult Index()
{
DataSource ds = new DataSource();
ds.loadXml("xml file path");
Since you've exported these data contents into a DataTable successfully，you should use SqlBulkCopy to deal with the problem：
【Solution】
using System.Data.SqlClient;
class Program
{
static void Main()
{
// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns"; //Make sure that your real table's structure must be the same as what's in the DataSet.Tables[1]
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(ds.Tables[1]);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
I suppose Quantum wanted to auto generate Table structure also , but the above solution would require the user to define the structure himself.
Can you please give insight on this.
Thanks for you tip，and in fact I suggest him creating a database with a specific table with the same structures like what's defined in the DataTable's columns' types（for types in SQL to types in .NET，he can refer：http://msdn.microsoft.com/en-us/library/4e5xt97a(v=vs.71).aspx）
When finishing them and use SqlBulkCopy，it will read line-by-line data contents from DataTable or DataReader so quickly to fill into the same structure of DataTable。
QuantumInfor...
Member
199 Points
228 Posts
import xml files to "auto generated" db tabl
Apr 13, 2012 10:55 AM|LINK
I have a directory with xml files that are named like so:
test20120402
I want to import them all into a database but I don't want to manually create the table to hold tis data.
Is there a way in vs2010 to point to these xmls files and import them to a "auto generated" db table?
nijhawan.sau...
All-Star
16400 Points
3173 Posts
Re: import xml files to "auto generated" db tabl
Apr 13, 2012 11:04 AM|LINK
You need to first load xml into DataSource
DataSource ds= new DataSource(); ds.loadXml("xml file path");and then you can copy data and structure of created Datatable to Database.
http://www.codeproject.com/Articles/17169/Copy-Data-from-a-DataTable-to-a-SQLServer-Database
QuantumInfor...
Member
199 Points
228 Posts
Re: import xml files to "auto generated" db tabl
Apr 13, 2012 11:20 AM|LINK
cool, I have a mcv project so I guess I can just stick this code into my home controller to get it to run quickly? hmm.. DataSource is not recognised :(
public ActionResult Index()
{
DataSource ds = new DataSource();
ds.loadXml("xml file path");
ViewBag.Message = "";
return View();
}
nijhawan.sau...
All-Star
16400 Points
3173 Posts
Re: import xml files to "auto generated" db tabl
Apr 13, 2012 11:25 AM|LINK
Sorry i meant,
DataSet ds = new DataSet(); ds.ReadXml("xml file path");QuantumInfor...
Member
199 Points
228 Posts
Re: import xml files to "auto generated" db tabl
Apr 13, 2012 12:34 PM|LINK
thanks but I'm not really sure what to do after this:
public ActionResult Index()
{
DataSet ds = new DataSet();
ds.ReadXml("\\test20120402.xml");
ViewBag.Message = "";
return View();
}
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: import xml files to "auto generated" db tabl
Apr 15, 2012 02:32 AM|LINK
Hello：）
Since you've exported these data contents into a DataTable successfully，you should use SqlBulkCopy to deal with the problem：
【Solution】
using System.Data.SqlClient; class Program { static void Main() { // Open the destination connection. In the real world you would // not use SqlBulkCopy to move data from one table to the other // in the same database. This is for demonstration purposes only. using (SqlConnection destinationConnection = new SqlConnection(connectionString)) { destinationConnection.Open(); // Set up the bulk copy object. // Note that the column positions in the source // data reader match the column positions in // the destination table so there is no need to // map columns. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"; //Make sure that your real table's structure must be the same as what's in the DataSet.Tables[1] try { // Write from the source to the destination. bulkCopy.WriteToServer(ds.Tables[1]); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Close the SqlDataReader. The SqlBulkCopy // object is automatically closed at the end // of the using block. reader.Close(); } } } } private static string GetConnectionString() // To avoid storing the sourceConnection string in your code, // you can retrieve it from a configuration file. { return "Data Source=(local); " + " Integrated Security=true;" + "Initial Catalog=AdventureWorks;"; } }nijhawan.sau...
All-Star
16400 Points
3173 Posts
Re: import xml files to "auto generated" db tabl
Apr 16, 2012 06:11 AM|LINK
In SQL 2008 there is a very easy way to create a table out of a client defined Datatable: pass the DataTable as a Table value parameter, then issue a , this will effectively transfer the definition of the Datatable and its content data into a real table in SQL.
kavita_khand...
Star
9767 Points
1930 Posts
Re: import xml files to "auto generated" db tabl
Apr 16, 2012 07:54 AM|LINK
DECLARE @FilePath NVARCHAR(100) CREATE TABLE #TempProcessFile ( City NVARCHAR(100), State NVARCHAR(100), Country NVARCHAR(120) ) SET @FilePath = 'D:\test20120402.txt' --I assume you have comma seperated row values. exec('BULK INSERT #TempProcessFile FROM ''' + @FilePath + ''' WITH ( FIRSTROW = 1, FIELDTERMINATOR = '+''',''' + ', ROWTERMINATOR = '+'''\n'')') --print('BULK INSERT #TempProcessFile FROM ''' + @FilePath + ''' WITH ( FIRSTROW = 1, LastRow = 10 , FIELDTERMINATOR = '+''',''' + ', ROWTERMINATOR = '+'''\n'')') select * from #TempProcessFile select * from #TempProcessFile FOR XML PATH DROP table #TempProcessFileI would love to change the world, but they wont give me the source code.
nijhawan.sau...
All-Star
16400 Points
3173 Posts
Re: import xml files to "auto generated" db tabl
Apr 16, 2012 09:18 AM|LINK
Hi Decker,
I suppose Quantum wanted to auto generate Table structure also , but the above solution would require the user to define the structure himself.
Can you please give insight on this.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: import xml files to "auto generated" db tabl
Apr 17, 2012 01:18 AM|LINK
Thanks for you tip，and in fact I suggest him creating a database with a specific table with the same structures like what's defined in the DataTable's columns' types（for types in SQL to types in .NET，he can refer：http://msdn.microsoft.com/en-us/library/4e5xt97a(v=vs.71).aspx）
When finishing them and use SqlBulkCopy，it will read line-by-line data contents from DataTable or DataReader so quickly to fill into the same structure of DataTable。