How to read text file containing SQL code and execute it

Question

I'm looking to create a text file, which will contain SQL code to create a database and its tables, and to later on, modify the same database.

The text file will be read via an application the user installs, and when it runs, it should read the text file and create, or modify the database if any changes have been applied.

The SQL text file should of course, be somewhat validated in order to not duplicate tables and such.

I'm not asking for any code, just a specific pathway I should follow in order to make this happen.

Thanks for your input.

Seriously? Why even vote this down? It's a legitimate question — user1676874, Dec 12 '13 at 16:34
A better choice would be an XML file. This way you can read certain elements before proceeding. — OneFineDay, Dec 12 '13 at 16:37
You can what? And if it works I'll try it, why is it better than regular txt file though? — user1676874, Dec 12 '13 at 16:39
How do you find a certain line in a text file - harder than finding an element in an XML file. — OneFineDay, Dec 12 '13 at 16:44
Well yeah of course, the idea of the text file would only be to 'create if exists' and such, thus not reading specific lines but rather the whole thing. — user1676874, Dec 12 '13 at 16:45
I believe the down and close votes are because , quoting from reasons to close: `There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.` Maybe splitting your problem in smaller questions and providing some code will get you some usefull answer. — Yaroslav, Dec 12 '13 at 16:51
@Yaroslav Seems to me he's asking a pretty well constrained question about which design pattern to choose for a common task. It's not a code question, but it's definitely a programming question, and it's a perfectly manageable one. — 15ee8f99-57ff-4f92-890c-b56153, Dec 12 '13 at 16:55
@EdPlunkett, I agree, is a question related to programming but it's to open, it can lead to a wide range of answers and options. Check [What types of questions should I avoid asking?](http://meta.stackoverflow.com/help/dont-ask). — Yaroslav, Dec 12 '13 at 20:15
@Yaroslav Well, I'd rather the moderators don't see it your way, but it's not my site. — 15ee8f99-57ff-4f92-890c-b56153, Dec 12 '13 at 20:42
If you haven't allready chosen a path for this, have a look at roundhouse chuck norris https://github.com/chucknorris/roundhouse it will help you with upgrades, downgrades, scripts and versioning of your database. — Snorre, Dec 17 '13 at 17:46

15ee8f99-57ff-4f92-890c-b56153 · Accepted Answer · 2013-12-12T20:41:26.590

I'd do database creation via a SQL script which checks for the existence of tables/views/SPs/etc. before creating them, then I'd execute it in the VB application via ADO.NET. I'd ship it with the application in a subdirectory. It's not a big deal to read text files, or to execute a SQL string via ADO.NET.

I'd have a VERSION table in the database that identifies what DB schema version is installed, and when I shipped upgrade scripts which modified the DB, I would have them update the VERSION table. The first version you ship is 1.0, increment as appropriate thereafter.

All the SQL object creation/detection/versioning logic would be in SQL. That's by far the simplest way to do it on the client, it's the simplest thing to develop and to test before shipping (MS SQL Management Studio is a godsend), it's the simplest thing to diff against the previous version, store in source control, etc.

Incidentally, I would also have my application interact with the database strictly via stored procedures, and I would absolutely never, ever feed SQL any concatenated strings. All parameters going to SQL should be delivered via ADO.NET's SqlParameter mechanism, which is very cool because it makes for clean, readable code, and sanitizes all of your parameters for you. Ever use a DB application that crashed on apostrophes? They didn't sanitize their parameters.

score 1 · Answer 2 · answered Dec 12 '13 at 17:23

If what you are asking is How do I read a text file and make the results execute in SQL

I would use a StreamReader to read the text file into a string variable.

Once you have read it in, go ahead and open a connection to the database and do a ExecuteNonQuery with the value of the string variable.

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

I would post in the comments but I can't. I think this may be what you are looking for.

Is it possible to execute a text file from SQL query?

edited May 23 '17 at 12:11

Community

1
1

answered Dec 12 '13 at 16:48

SteveB

769
4
18

score 0 · Answer 4 · answered Dec 12 '13 at 16:56

Use MS SQL Server Management Studio to perfect your scripts: http://technet.microsoft.com/en-us/library/ms174173.aspx

SSMS comes with the server installs and is available for the SQL Express versions. (It isn't needed on the client PCs but it may be useful for debugging.

This will most likely be a low security environment and each user will have full control of the DB.

For there it is pretty straight forward to read the text file and run it against the DB. Just get a connection and send the script:

                    Dim cmd As New Data.SqlClient.SqlCommand
                    con.Open()
                    cmd.CommandText = SQL
                    cmd.Connection = con
                    cmd.ExecuteNonQuery()

You might want to use a virtual machine on your development PC as it will allow you to quickly do testing of your scripts and code, and return to baseline state.

How to read text file containing SQL code and execute it

4 Answers4