.Net .Config, really long text.

Question

Hello all.

I'm building a very simple application .net Console Application that will execute a SQL statement and post it to a URL. My issue is that the SQL Statement Might be very long and will certainly go over many lines. How can you specify a lot of text inside of a .config file?

Example:

<add key="SelectStatement" value="select * from test1table"/>

The above is pretty easy and works well. However, when that SQL is 1000+ characters long and includes many case statements, joins, group by it becomes more troublesome. A basic copy and paste from Query Analyzer doesnt work very well.

Ideal would be below, but doesn't work. :

<add key="SelectStatement"> select * from test1table </add>

Thanks for the help.

Edit: Based on the feedback, going to be more specific.

The plan is for a friend of mine, that's not too technical, to be able to deploy the .exe at various client locations. Each location will have a similar query, but each may be different. He'll have read only access to the tables, ie;, can't save anything into the database or create different objects. My hope was to leave all config settings to just 1 file. Is there a clean/easy way to store it in the .config file?

Answer 1

You could store the name of a file in your config and put the SQL into that file. Then read the file contents using File.ReadAllText().

Answer 2

Or you could also store your actual SQL statements in a database table, e.g. "SQLStatements", and then just simply define the "key" name here and grab the long text from the database.

Or you could include a text file with SQL statement in your project and mark it as "embededded resource" and store just its file name in the config.

To make things short: don't store more than a few hundred characters in config - it's just really not made for that. Store a pointer (file name, database key name etc.) in the config and the actual text (SQL statement or whatever) somewhere else.

Marc

Answer 3

Replace spaces by their XML equivalent, or better, change the setting programmatically with your long text.

Answer 4

If you are using sql server or mySql version 5, you should turn the long sql statement into a stored procedure. You'll have a performance gain from doing this as well.

Answer 5

Save your overly-long query as a View in your database, and then just save SELECT * FROM yourView in your config file.

Answer 6

I have used a similar system to store SQL queries within an XML document. I added an all the queries to one file under the following or similar structure:

<queries>
  <query id="someQueryName" orderBy="created desc">
     select * from some table where id &lt; 500
  </query>
.....  all the other queries
</queries>

And then i could just pull out the query using xmlPaths or something, the &lt and &gt are automatically translated to < and >. I used it extensively within a few big projects with no problems and it was a great way to make slight changes to how the application worked without recompiling any code.