Dump SQL Server database to XML through C#

[Jenden0]

Ok, I'm trying to dump an SQL Server 2005 database to XML via C#, and
I'm having some troubles. I'm relatively new to the whole .net thing,
so there may be some stupid/basic questions/assumptions involved.

The first way I thought about doing it was dumping the entire dataset
to XML. However, as far as I can tell there's no way to populate the
entire dataset at once (I'm using an auto-generated dataset from Visual
Studio 2005). I think if I could do that it would be simplest, but I
haven't even found something hinting that its possible.

Right now I've got a partial solution where I loop through each of the
tables and print that out as XML. However, that is printing them out
as individual entities, instead of printing out the document as one
entire XML document. While that might work, I'd much rather have it
all nice and together.
<database><table1>...</table1><table2>...</table2>... is what I'd like.
What I'm getting is
<document><table1>...</table1></document><document><table2>...</table2></document>...

 

[David Browne]

Ok, I'm trying to dump an SQL Server 2005 database to XML via C#, and
I'm having some troubles. I'm relatively new to the whole .net thing,
so there may be some stupid/basic questions/assumptions involved.

The first way I thought about doing it was dumping the entire dataset
to XML. However, as far as I can tell there's no way to populate the
entire dataset at once (I'm using an auto-generated dataset from Visual
Studio 2005). I think if I could do that it would be simplest, but I
haven't even found something hinting that its possible.

That's easiest. You can just create a DataSet and keep adding tables to it.

EG:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace ConsoleApplication5
{
class Program
{
static void Main(string[] args)
{
string constr="Data Source=(local); Initial Catalog=test;Integrated
Security=true";

using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
DataTable tables = new DataTable();
string sqlTables = "select name from sys.Tables where schema_id =
1";
using (SqlDataReader rdr = new SqlCommand(sqlTables,
con).ExecuteReader())
{
tables.Load(rdr);
}

DataSet db = new DataSet();
foreach (DataRow table in tables.Rows)
{
string tableName = (string)table["name"];
string sql = string.Format("select * from [{0}]", tableName);
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.TableMappings.Add("Table", tableName);
da.Fill(db);


}

db.WriteXml(Console.Out,XmlWriteMode.WriteSchema);

}

}
}
}


David

 

[Jenden0]

Yea, I thought about that. I'm trying to use visual studio's
auto-generated stuff as much as I can (as I find its easier for other
people to update later on)... I may have to just go with the SQL
though.