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.
4
-
If there is, then I would like to know it, because this is how I do it.Jrud– Jrud2009-11-06 20:47:38 +00:00Commented Nov 6, 2009 at 20:47
-
you want to be able to execute the sproc directly in c#?Daniel– Daniel2009-11-06 20:48:40 +00:00Commented Nov 6, 2009 at 20:48
-
1I think he wants to run the code to create the SP in C#, not just run it. (that would be too easy)Jrud– Jrud2009-11-06 20:50:45 +00:00Commented Nov 6, 2009 at 20:50
-
1I guess people think this is how you do it. I'm primarily a Python programmer, so I was looking for the elegant solution. Apparently, this is as elegant as it gets.Xylarax– Xylarax2009-11-06 21:56:24 +00:00Commented Nov 6, 2009 at 21:56
Add a comment
|
6 Answers
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
Comments
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?
Comments
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.
Comments
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.
Comments
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.
Comments
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.
