Deploy a SQL stored procedure in C#

Question

I have a stored proc .sql file on my system. I want to be able to move this file into database as a sp from C# code.

i could open the file, read it in as a string, and execute it but I feel like there should be a better way.

Answer 1

why not ? it's a simple way and it works, you read the sql script from the file and execute it
I think this way it's just fine

Answer 2

The Sql parser has to compile the "create procedure ....." statement to convert it to the server's internal representation of an sp. Even if you could find a way around this, why? It's the accepted way to get data into and out of the server - even when that data is code.

Answer 3

Are you talking about having the stored procedure reside on the database instead of a string query in your C# code?

I may be way off base but your question isn't very clear...

Have you look into the TSQL command CREATE PROCEDURE?

Answer 4

I dont think there is a way to do what you are looking for in C# or .net. You can use SMO to take stored procs on sql server and save them to a file, but not the other way around. You might have to use powershell if you want to directly execute .sql files on your computer.

Answer 5

It is possible to use the command line tools for whatever database server you are using to create the stored procedures. Executing the exe from C# with the correct parameters.

They will often accept the file name as a parameter, so you don't need to load the sql in as a string.

Don't know if that's what you are after, but it should work.

Answer 6

I believe what you're looking for is "SQL Server Management Objects (SMO)". It's installed under the "Client Tools" section with the SQL Server installation, and you can program it with C# and Visual Studio 2008.