CSharp/C# >> Dump SQL Server database to XML through C#

by Jenden0 » Tue, 13 Jun 2006 05:20:09 GMT

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>...


CSharp/C# >> Dump SQL Server database to XML through C#

by David Browne » Tue, 13 Jun 2006 06:22:41 GMT



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

CSharp/C# >> Dump SQL Server database to XML through C#

by Jenden0 » Tue, 13 Jun 2006 07:16:28 GMT

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.

Similar Threads

1. Dump SQL CE Database into dataset XML - SQL Server CE

2. Using a stored procedure to dump out xml from sql server

I'm trying to dump po information from our sql server.
It uses a stored procedure that has a select statement in it.
This stored procedure is called from within the an orchestration.
When the orchestration finishes... It produces a good XML file.
(If there is data for it to select)
If... It doesn't find anything.  Basically, returning a NULL,
It produces the start of an XML... with the header, and nothing
else in it.
Unfortunately, I'm using this XML file in another process...
If the XML file doesn't have any data in it, it causes problems
in my other project.

Is there any way to stop the XML file from getting created at all ?
Any ideas?


3. Dump XML into SQL Server 2000

4. ASP.NET 2.0 C# WebService Connect to Database: Sql Server 2005

ASP.NET 2.0 C# WebService Connect to Database: Sql Server 2005
---------------------------------------------------------------------------------------------------

I have successfully created a webservice in ASP.NET 2.0 C# with VS
2005 & VS 2008, however, i can find nothing - books or otherwise to
help with the webservice connection to the database.

Can anyone please offer help on what to return in the method that
makes a select statement to the database and HOW to connect to the
database??   Do i use DataSets, TableAdapters, drivers???

How??