CODEWITHSUNDEEP
asp.netsqlxml
Philo
Philo

Reputation: 1989

storing data in a xml format file

I m trying to build a function that will retrieve 'some' SQL data from multiple tables, and store it in a file in the XML format.

If I do it with C#, is it as simple as:-

Can any one show me an example?

I was looking at:-

WriteXml () and WriteXmlSchema() functions in C#

string xmlFile = Server.MapPath("Employees.xml");

ds.WriteXml(xmlFile, XmlWriteMode.WriteSchema);

Also, will xmlSerialization be something I need to take a look at?

Sample SQL query.

SqlConnection Connection1 = new SqlConnection(DBConnect.SqlServerConnection);
        String strSQL1 = "SELECT xxx.MEMBERKEY, xxx.MEMBID, xyz.HPCODE, convert(varchar, OPFROMDT, 101) as OPFROMDT"
            + ", convert(varchar, OPTHRUDT, 101) as OPTHRUDT FROM [main].[dbo].[yyy] INNER JOIN [main].[dbo].[xxx] ON xxx.MEMBERKEY = yyy.MEMBERKEY "
            + "and opthrudt >= opfromdt INNER JOIN [main].[dbo].[xyz] ON yyy.HPCODEKEY = xyz.HPCODEKEY where MembID = @memID";


        SqlCommand command1 = new SqlCommand(strSQL1, Connection1);
        command1.Parameters.AddWithValue("@memID", memID);
        SqlDataReader Dr1;
        Connection1.Open();
        Dr1 = command1.ExecuteReader();

        while (Dr1.Read())
        {
            HPCODEs.Add((Dr1["HPCODE"].ToString()).TrimEnd());
            OPFROMDTs.Add((Dr1["OPFROMDT"].ToString()).TrimEnd());
            OPTHRUDTs.Add((Dr1["OPTHRUDT"].ToString()).TrimEnd());
        }
        Dr1.Close();

Upvotes: 0

Views: 503

Answers (2)

Juan Ayala
Juan Ayala

Reputation: 3518

There are so many approaches to this.

For one, you can invest some time and use SQL built in XML capabilities to query and get an XML document directly which then you can serialize straight into a file. A very basic example could be found here. Then you would use the DataReader's GetSqlXml method, something like this

SqlDataReader r = cmd.ExecuteReader();
SqlXml data = r.GetSqlXml(0);
XmlReader xr = data.CreateReader();

Maybe another option is to read from the sql data reader into DTO (data transfer objects). This is probably the tried and true method. And then once you have a list of DTO's you can use .NET's serialization (DataContractSerializer) to serialize to XML.

Upvotes: 1

Jay
Jay

Reputation: 6294

I would highly recommending looking at a tool like this:

http://msdn.microsoft.com/en-us/library/x6c1kb0s(v=vs.80).aspx

This will generate .net classes for you if you have an xsd of the xml output you are trying to create.

Either way, dropping your data into POCO style classes and serializing to XML is a lot better than trying to use the XmlWriter directly.

Upvotes: 0

Related Questions