Deploy a SQL stored procedure in C#

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.

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

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?

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.

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.

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.

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.