C# -- Using multiple DataTables of the same name and namespace in a DataSet

Question

suppose I have an MS SQL Server database table named "GROUPS" such as the following:

GROUP_ID    GROUP_NAME    DESCRIPTION
    1        Grp1            Null
    2        Grp2            Null
    3        Grp3            Test

GROUP_ID is an auto increment primary field, GROUP_NAME is a unique non-null varchar field, and Description is a null-allowed (optional) varchar field.

I am trying to produce some XML output as a web service, to be consumed by some other process, of the table information. The XML must be output in a specific manner.

The end goal would look like this:

This procedure almost gets the desired goal:

    string sql = "SELECT * FROM GROUPS";
    Response.ContentType = "application/xml"; // Set the page to output XML
        SqlDataAdapter da = new SqlDataAdapter(sql, conn); // SQL Adapter, conn is an already open SQL connection
        DataSet ds = new DataSet("GROUPS"); // Root Node of XML doc will be "GROUPS"
        da.Fill(ds, "GROUP"); // Each record node is a "GROUP"
        XmlTextWriter xml = new XmlTextWriter(Response.Output) { Formatting = Formatting.None }; // XML writer
        xml.WriteStartDocument(); // XML header
        ds.WriteXml(xml); // Write XML output

The issue with this procedure is that it does not provide the desired empty nodes such as for null values in the table. It would look like this:

After doing some research, an attempt to create the desired output was performed with the following procedure:

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM GROUPS";
        SqlDataReader row = cmd.ExecuteReader();

        DataSet ds = new DataSet("GROUPS");

        while (row.Read())
        {

            DataTable table1 = new DataTable("GROUP" + row["GROUP_ID"]); // This line is the issue
            object[] columns = new object[row.FieldCount];


            for (int i = 0; i < row.FieldCount; i++)
            {

                string name = row.GetName(i);

                    table1.Columns.Add(name);
                    columns[i] = DBstr(row[i]);

            }

            table1.Rows.Add(columns);
            ds.Tables.Add(table1);

        }

        row.Close();

        Response.ContentType = "application/xml"; // Set the page to output XML
        XmlTextWriter xml = new XmlTextWriter(Response.Output) { Formatting = Formatting.None }; // XML writer
        xml.WriteStartDocument(); // XML header
        ds.WriteXml(xml, XmlWriteMode.IgnoreSchema); // Write XML output


    public static string DBstr(Object obj)
    {
        if (obj == null || obj == DBNull.Value)
        {
            return String.Empty; // Default value for null DB value
        }

        else if (obj.GetType() == typeof(DateTime))
        {
            return ((DateTime)obj).ToString("MM/dd/yyyy");
        }
        else
        {
            return obj.ToString();
        }
    }

The issue here is that I had to give either a unique name for each DataTable, or a unique namespace. Prior to writing out xml from the DataSet. So it would look something like this:

Or, each GROUP row would have an xmlns tag thrown in.

Either way, undesirable. So what is the simplest way to get the desired goal using C# here?

Pankaj · Accepted Answer

Try to change the query

string sql = "SELECT * FROM GROUPS";

to below

string sql = "SELECT GROUP_ID, GROUP_NAME, IsNull(DESCRIPTION, '') as DESCRIPTION  FROM GROUPS";

C# -- Using multiple DataTables of the same name and namespace in a DataSet

Answers (2)

Related Questions