Hi everyone!
I am running into an issue that I need some advice on. I am trying to use LinQ to create an XML document.
I am using the information_schema.columns table to create my datatable before using LinQ. One column consists of the table names in the database and the 2nd column consist of the columns that go with that particular table.
Here is what I have so far:
XDocument document = new XDocument(
new XDeclaration("1.0", "utf-8", "yes"),
new XElement("testmenu",
new XElement("showwebhelp", "True"),
new XElement("menuitem",
new XElement("caption", yearComboBox.Text + " " + modelComboBox.Text + " Waveforms"),
from distinctItem in linqCollection
select new XElement("menuitem",
new XElement("caption", distinctItem["table_name"])
)
)
)
);
document.Save("C:\\test.xml");
My output that I'm getting is:
<testmenu>
<showwebhelp>True</showwebhelp>
<menuitem>
<caption>Vehicle Info</caption>
<menuitem>
<caption>Table 1</caption>
</menuitem>
<menuitem>
<caption>Table 1</caption>
</menuitem>
<menuitem>
<caption>Table 1</caption>
</menuitem>
<menuitem>
<caption>Table 2</caption>
</menuitem>
<menuitem>
<caption>Table 3</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 4</caption>
</menuitem>
<menuitem>
<caption>Table 5</caption>
</menuitem>
<menuitem>
<caption>Table 5</caption>
</menuitem>
<menuitem>
<caption>Table 5</caption>
</menuitem>
<menuitem>
<caption>Table 5</caption>
</menuitem>
<menuitem>
<caption>Table 6</caption>
</menuitem>
<menuitem>
<caption>Table 7</caption>
</menuitem>
<menuitem>
<caption>Table 8</caption>
</menuitem>
</menuitem>
</testmenu>
What I should be seeing is 1 of each table name. How can I eliminate the duplicates? Do I need to create a separate list that has a distinct list of table names?
This is how I'm obtaining the table_names.
DataTable linqDT = new DataTable();
SqlCeCommand tableColumnCommand = new SqlCeCommand("SELECT table_name, column_name FROM information_schema.columns " + "WHERE (table_name <> 'accountmanagement') AND (table_name <> 'docfiles') AND (table_name <> 'engineandtransmissioncodes') " + "AND (table_name <> 'enginecodemaster') AND (table_name <> 'transmissioncodemaster') AND (table_name <> 'vehicles') " + "AND (table_name <> 'waveform_files') AND (column_name <> 'vehicle_key')", conn);
tableColumnCommand.CommandType = CommandType.Text;
tableColumnCommand.ExecuteNonQuery();
SqlCeDataAdapter tableColumnCommand_da = new SqlCeDataAdapter(tableColumnCommand);
tableColumnCommand_da.Fill(linqDT);
var linqCollection = (from item in linqDT.AsEnumerable()
select item["table_name"]);