SQL statements in separate file

Question

Hello,

I'm working on .net project and writing an application that interacts with database. I currently have some SQL statements in the code and I want to put these statements in separate file with solution. So, later when the application gets deployed, and if I want to update SQL statements, I could just update the statements in the file and just replace the file instead of redeploying the whole solution.

Thank you for your help.

Answer 1

What kind of sql statements are these? Perhaps, you could use stored procedures and call them from your .net code.

Answer 2

You can put the SQL statements in an XML file, then use System.Xml.Linq to read the file.

However, you should use stored procedures instead.

Answer 3

If you like your db functionality easy to modify you should consider using stored procedures. This has also some security benefits.

Answer 4

why not implement them as stored procedures?

Answer 5

We do this very often where I work by using the App.config file. Create an app.config file for your project and add key-value pairs for each of the sql statements. For example:

<configuration>
 <appSettings>
  <add key="PRODUCTS_QUERY" value="SELECT * FROM Products"></add>
  <add key="EMPLOYEE_QUERY" value="SELECT * FROM Employee"></add>
 </appSettings>
</configuration>

Then, in your code, you can pull the queries from the file using something like this (for VB.NET):

Dim myReader As New System.Configuration.AppSettingsReader
Dim productsQuery As String = myReader.GetValue("PRODUCTS_QUERY", GetType(System.String))
Dim employeeQuery As String = myReader.GetValue("EMPLOYEE_QUERY", GetType(System.String))

But note - the App.config file gets compiled into a ProjectName.exe.config file in the bin folder - this is the file that you should change if you want to change the SQL without rebuilding.

Edit: You can also try using an xml file and then parsing with XPath. It's very powerful and very easy. Check out one of these tutorials:

http://www.w3schools.com/xpath/

http://www.zvon.org/xxl/XPathTutorial/General/examples.html

Answer 6

We have done this in the past using XML much like a config file, with a key="SomeProcName" and a value="the SQL".

When doing this we have found two complications:

1. You have to be very careful about escaping special characters - quotes get confusing sometimes.

2. You will want to check performance, as sometimes this technique can have a negative impact on query caching.

Answer 7

you may use SqlCommand (OleDbCommand if RDBMS is not Sql Server) to execute sql statements. running .sql file using C#

Answer 8

Just don't loose sight of the fact that if you deploy this application with the SQL statements publicly accessible you open up your database to any user with a text editor. Anybody could open up that file and change a "SELECT * FROM Products" to "DELETE FROM Products" and your products are gone. Of course, you may be able to protect your database if you only grant SELECT permissions to the application in the database but still.. it seems like a bad idea to leave your queries out in the open like that.