How would you store the information?
One of the new products I'm developing for my company is "NQuery", a SQL builder WinForms UserControl. It displays various schema information so the user can drag and drop tables, choose some fields, set some criteria values, etc. in order to have the SQL statement created for them. (btw, props to Scott for coming up with the name "NQuery" - I'll try to round up some swag for you, Scott.)
When the information needs to be saved, I need to store all of the relevant schema information, criteria, and visual display settings to a file.
My first (and only) thought so far has been XML. I could store the information like so:
<nquery version="0.1" release="Alpha">
<sqlstatement type="select">
<select distinct="false" top="0" percent="100">
<selectitems>
<selectitem>
<alias></alias>
<expression></expression>
<field></field>
</selectitem>
</selectitems>
<from>
<tables>
<table>
<owner>dbo</owner>
<name>Orders</name>
</table>
</tables>
</from>
</select>
<update>
<table>
<owner>dbo</owner>
<name>Orders</name>
</table>
<setvaluefields>
<field name="orderdate" value="10/15/2004" />
</setvaluefields>
<criteriafields>
<field name="orderid" value="100" />
</criteriafields>
</update>
<delete>
<table>
<owner>dbo</owner>
<name>Orders</name>
</table>
<criteriafields>
<field name="orderid" value="100" />
</criteriafields>
</delete>
<insert type="into">
<into>
<table>
<owner>dbo</owner>
<name>Orders</name>
</table>
<fields>
<field name="customerid" value="10" />
<field name="employeeid" value="5" />
<field name="orderdate" value="10/15/2004" />
</fields>
</into>
<from>
</from>
</insert>
</sqlstatement>
<options>
<displayownername value="false" />
</options>
<displaysettings>
</displaysettings>
</nquery>
(This XML snippet encompasses all possible SQL types (Sel/Ins/Upd/Del) and is not entirely complete but you get the idea...)
Can anyone think of a better way to store this type of information? My concerns are
- Parsing the whole tree to populate the object model seems like it could be costly performance-wise
- There might be a better format to express this in other than XML
- There might be a simpler way I just haven't thought of
If anyone has any thoughts and/or ideas they'd like to share - please - feel free in the comments or through the contact link or email me at jason dot mauss at gmail dot com.