CODEWITHSUNDEEP
sql-serverdatabasevb.netsql-server-2008text-files
user1676874
user1676874

Reputation: 905

How to read text file containing SQL code and execute it

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.

Upvotes: 3

Views: 4460

Answers (4)

15ee8f99-57ff-4f92-890c-b56153
15ee8f99-57ff-4f92-890c-b56153

Reputation: 37059

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.

Upvotes: 2

logixologist
logixologist

Reputation: 3834

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.

Upvotes: 1

rheitzman
rheitzman

Reputation: 2297

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.

Upvotes: 0

SteveB
SteveB

Reputation: 799

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?

Upvotes: 0

Related Questions