Re: Dump SQL Server database to XML through C#


<Jenden0@xxxxxxxxx> wrote in message news:1150147209.213990.190350@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

.