将大量sql数据集写入xml文件的最佳方法? [英] Best approach to write huge sql dataset into xml file?
问题描述
using(var myConnection = new SqlConnection(Data Source = 192.168.5.28; Initial Catalog = dbName; User ID = sa; Password = davood $;))
{
var da = new SqlDataAdapter(select * from tblName,myConnection);
var ds = new DataSet();
da.Fill(ds);
var filestream = File.Create(D:\\\tblName.xml);
var buffered = new BufferedStream(filestream);
ds.WriteXml(buffered);
// ds.WriteXml(D:\\\tblName.xml,System.Data.XmlWriteMode.IgnoreSchema);
//
// using(var fileStream = File.Create(D:\\\tblName.xml))
// {
/ / using(var zipStream = new GZipStream(fileStream,CompressionMode.Compress))
// {
// ds.WriteXml(zipStream,XmlWriteMode.WriteSchema);
//}
//}
}
但之后程序执行(长时间执行),文件无法打开。
Tnx。
在大量的数据大小(批量操作)中,不太建议使用C# ORM和ADO,用于性能,内存,IO,往返和其他...问题。
我更喜欢使用RDBMS核心功能制作文件。
由您可以使用分页
划分查询结果的方式,对于任何页面,您可以创建一个单独的xml文件,在Windows操作系统上,我们有4 gig文件大小限制,因此页面大小必须平衡,因为你的行行大小。
首先:
获取表行数。 (调用数据读取器,否则被忽略)
var dataRowsCount =从tblName中选择count(*)
第二个:
选择有效的页面大小,将结果除以首先调用这个页面的大小,你将会有这样的循环数:
var pageSize = 1000 ;
var pageCount =(dataRowsCount / PageSize)+ 1;
第三个:
循环(基于第二个结果阶段),调用分页查询获取数据并创建多个xml文件。
for(i = 0; i< pageCount,i ++ )
{
//调用分页查询并创建文件
// SQL Server分页查询
SELECT TOP pageSize列
FROM表
WHERE IDColumn NOT IN (SELECT TOP pageSize * i IDColumn
FROM Table
ORDER BY SortColumn)
ORDER BY SortColumn;
}
可以在MSSqlServer,Oracle和MYSql上分页查询示例 here 。
In C#,i have a huge dataset that i want to write it into xml file with write xml,that is my code:
using (var myConnection = new SqlConnection("Data Source=192.168.5.28;Initial Catalog=dbName;User ID=sa;Password=davood$;"))
{
var da = new SqlDataAdapter("select * from tblName", myConnection);
var ds = new DataSet();
da.Fill(ds);
var filestream = File.Create("D:\\tblName.xml");
var buffered = new BufferedStream(filestream);
ds.WriteXml(buffered);
// ds.WriteXml("D:\\tblName.xml", System.Data.XmlWriteMode.IgnoreSchema);
//
//using (var fileStream = File.Create("D:\\tblName.xml"))
//{
// using (var zipStream = new GZipStream(fileStream, CompressionMode.Compress))
// {
// ds.WriteXml(zipStream, XmlWriteMode.WriteSchema);
// }
//}
}
but after program is execute(with long time executing),file is can not be open. Tnx.
In huge amount of data size(bulk operation), its not well advised to use C#, ORM and ADO, for performance,Memory,IO, round trip and else ... issues.
I would prefer using RDBMS core functions to make files.
By the way you may use Paging
to divide the query result and for any page you may create a separate xml file, on windows OS we have 4 gig file size limit ,so page size must be balanced due to you table row size.
First:
fetch the table row count. (calling data Reader and else is ignored)
var dataRowsCount = select count(*) from tblName
Second:
Choose a effective page size, divide the result of first call to this page size, you will have your loop count, something like this:
var pageSize = 1000;
var pageCount = (dataRowsCount / PageSize) + 1;
Third:
On a loop(based on result of second phase), call paged query to get data and create several xml files.
for(i=0;i<pageCount,i++)
{
// Call paged query and create files
// SQL Server paged Query
SELECT TOP pageSize columns
FROM Table
WHERE IDColumn NOT IN ( SELECT TOP pageSize*i IDColumn
FROM Table
ORDER BY SortColumn)
ORDER BY SortColumn;
}
Paging query sample on MSSqlServer, Oracle and MYSql could be found here.
这篇关于将大量sql数据集写入xml文件的最佳方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!