Reading SQL to DataSet to XmlDocument

Question

The code below works dreamy. But can it be made more compact and C#'ish? Especially i have suspicions regarding two issues.

1. Isn't it ugly (old C-style) filling fill a variable by using it as a in-parameter?
2. Can the code be made more compact instead of going through a String?

C#

String
  connectionString = "...",
  sqlStatement = "select * from Test",
  output = "";

SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString);
DataSet dataSet = new DataSet("Contents");
adapter.Fill(dataSet, "Test");
StringWriter stringWriter=new StringWriter();
dataSet.WriteXml(new XmlTextWriter(stringWriter));
XmlDocument document = new XmlDocument();
output = stringWriter.ToString();
document.LoadXml(output);

Answer 1

Although Holger's answer is really good, I was actually working with Linq to XML. What I leveraged from his code into Linq is even more elegant, I think, if your output is an XElement instead of an XmlDocument or an XmlNode. You also don't need to cast to XmlTextWriter.

String
   strConnection = "...",
   strSQL = "select * from Test",

XElement objOutput = null;

DataSet objDataSet = new DataSet("output");

using (SqlDataAdapter objAdapter = new SqlDataAdapter(strSQL, strConnection)){
    objAdapter.Fill(objDataSet, "row");}
using (StringWriter objWriter = new StringWriter()) { 
  objDataSet.WriteXml(objWriter);
  XDocument objDoc = XDocument.Parse(objDataSet.GetXml());
  objOutput = objDoc.Root;
};
return objOutput;

Answer 2

I don't know about making it more C#ish or compact, but you do need to close your Adapter and StringWriter. I would use the using block.

   String
       connectionString = "...",
       sqlStatement = "select * from Test",
       output = "";

    DataSet dataSet = new DataSet("Contents");
    using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, connectionString)){
        adapter.Fill(dataSet, "Test");}
    using (StringWriter stringWriter = new StringWriter()) { 
      dataSet.WriteXml(new XmlTextWriter(stringWriter));
      output = stringWriter.ToString();
    };
    XmlDocument document = new XmlDocument();
    document.LoadXml(output);