Reading SQL queries from .txt file for C#

Question

Before, I ask my question, I want you to know that I'm quite new to winforms in C#. :) So, I'm trying to create a winform using C# and in order to get the data from the back end, I'm storing all the SQL queries in a .txt file. The format in which my SQL queries are entered in the .txt file is as follow:

// Caption

Query

end

So, in my code, I start reading the file in a loop and read until I get my required caption. Once, I get my caption, I start reading the query line by line until I reach "end". After I get my query, I EXPLICITILY replace the variables in the queries(because I know whether a query has a variable or not). For example,

// Caption 1

Select * from table_1 where col1 = var1;

end

In this case, I read the query and use string.Replace() to replace "var1" with var1.ToString(). Then, execute the query.

My question is, is there a better way to do this?Like, is there a better format to store the queries in the .txt file? Any suggestions will be appreciated. Thank you.

Answer 1

The fact is that there are situnations where you do not want a stored procedure because that means one one hand there needs to be an installation before you can use the functionality and on the other you do not want another layer just to query for straight forward server or database statistics. The query is actualy is a resource and not part of the code. The only question actually is where to store it so that it would be part of the application or component. Therefore the resource file actually is not a bad idea. The only thing here to decide is whether to store it in a predefined format and use existing support code to manage it or write your own.

@Chase Florell: maybe he is writting a Repository Layer?

Answer 2

I'm not actually against this pattern - though it is unusual, it does have some benefits (and I've done similar myself on occasion - not that that should sway your opinion). To be fair - just about everyone else here on SO (and others) will advise you to use stored procs and/or an ORM. That's certainly the mainstream and no-surprise approach, and you should strongly consider it before forging your own path.

Either way. you should definitely take heed of the advice to use parameters rather than string.Replace though. SQL Injection is a huge vulnerability that's ridiculously easy to prevent - there's really no excuse for it anymore.

If you continue to store your queries in files, you also may want to watch out for performance and consider caching the queries instead of hitting disk each time. Depending on your use case, you may need to deal with invalidating the cache if the file changes - something a stored proc will handle for you.

I also disagree with the comments of using a markup language for this. If you're going to store them in text files, I'd suggest a single query per file. It versions much better, is much clearer, and should offer better performance in most cases as well.

If you don't care about runtime edits or production debugging of queries, you could also compile them down to a resource file (which is normally used for things like localization). That plays well with Visual Studio, is relatively easy to implement and is a somewhat common approach.

Answer 3

Warning: I would not actually do this, but here is an attempt to answer the question. My official stance is at the bottom.

If I'm reading your question properly, your struggle is actually "parsing" the text file to get the required query. If this is the case, I would highly recommend not using straight text to accomplish this task. You need some sort of structure so that the program knows "how" to acquire the appropriate information. Markup languages are basically structured text that allows a program to navigate the document.

There are many markups that will work, here are a few that could fit your needs.

1. If you're trying to keep it in a .txt file, and keep it really simple, you could try using something like toml, and parse it with toml.net or something similar
2. A more preferred method would be to use JSON(which is also text), and then use JSON.NET to parse the data.
3. Lastly you could use XML and parse it with the XmlReader

Now, because you need to parse the data, you should create a model that stores this information.

// note this is a JSON example, but you can replace it with whatever Markup parser you like.
public class SqlQueriesFromJsonModel {
    public class Query{
        public string Caption { get; set; }
        public string Query { get; set; }
    }

    public List<Query> Queries{ get; set; }
}

From here, you want to read your data from the file, and parse it into your model object.

SqlQueriesFromJsonModel getSqlQueries(){
    //  grab the file and parse it using json.net
    return queriesFromJson;
}

Call the method, and use the needed Query

// using System.Linq;
var queries = getSqlQueries();
var neededSqlQuery = queries.Where(Query => Query.Caption == "Caption 1");

note: have wrote this in the text editor, so it might not be exact. I'm open to edits if I got something wrong.

Now for the actual document format, here's a few options (you don't actually have to use any of these... this is just to get the creative juices flowing).

These are all "text" formats.

json

{
    "queries": [
        {
            "caption": "Caption 1",
            "query": "select * from someTable"
        },
        {
            "caption": "Caption 2",
            "query": "select * from someOtherTable"
        }
    ]
}

xml

<?xml version="1.0"?>
<queries>
    <query caption="Caption 1">select * from someTable</query>
    <query caption="Caption 2">select * from someOtherTable</query>
</queries>

toml

title = "SQL Queries"

[Caption 1]
query = "select * from someTable"

[Caption 2]
query = "select * from someOtherTable"

---

Now that that's out of the way, I'd like to write a disclaimer that reading your queries from a file is not a wise plan. Instead, write a Repository Layer that handles all of your needed queries, and consider using an ORM to make wiring it up easier and more secure.

If you're not interested in Repositories and ORM's, at LEAST write parameterized queries inside Stored Procedures within your database.

Either way, you should leave the text files out of it completely.. imo

Answer 4

Is there a reason that you cannot save the queries on the database using stored procedures? That is usually the optimal way to store the queries as it keeps the database logic in the database.

If not, rather than using replace() you can have your varibles listed as @varname and use addwithvalue to populate the parameters with that value.