3

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.

Improve this question
12
  • 1
    Seriously? Why even vote this down? It's a legitimate question Commented Dec 12, 2013 at 16:34
  • A better choice would be an XML file. This way you can read certain elements before proceeding. Commented Dec 12, 2013 at 16:37
  • You can what? And if it works I'll try it, why is it better than regular txt file though? Commented Dec 12, 2013 at 16:39
  • How do you find a certain line in a text file - harder than finding an element in an XML file. Commented Dec 12, 2013 at 16:44
  • 1
    @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. Commented Dec 12, 2013 at 16:55

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

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?

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.