Creating Stored Procedure

[Terrance]

I have a question and I'm not quite sure if this can be done or not. Does any
have any idea(s) on how to create a SQL stored procedure from a text file
using C#. You see a developer has given me a text file that creates some
stored procedures for a database. I am in the process of creating a app that
will create the database along with the stored procedures.

I don't want to rewrite what the developer wrote I just want to be able to
read the file content and run it to create the stored procedures. Will using
SQLDMO help?

 

[William Vaughn]

It's easier than that. I would use SQLCMD to execute the SQL (assuming it's
properly formatted). Next, I might try SQLDMO but I've written my own SQLCMD
class a couple of times so I would just use that. The code is included on
the DVD in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cowboy \(Gregory A. Beamer\)]

Open the file in SQL Server Management studio and hit F5. No need to run it
in C#, unless you are going to build the sql bits as part of an install.
Even then, you can tag the command line and run the text file as a batch.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Chris Anderson [MVP-VB]]

Instead of SQLDMO (which is COM based and won't work with SQL2005 (you
didn't specify the SQL version), give SMO (SQL Management Object) a try.
In a nutshell, it's the .NET replacement for SQLDMO, and talks to
SQL2000 and SQL2005 (I don't know about SQL2008).

Once connected, there's a ExecuteWithResults (and an ExecuteNoResults)
that you can feed the script to. But there's a catch, you can't have any
GO statements in it. You'll generate an error if you do. What I do is
split the script up using the GO's, putting each segment into a
StringCollection (Specialized.StringCollection), which can then be
passed to the ExecuteWithResults and it'll run them.

Hope this helps.

-ca

 

[Shawn Wildermuth]

Since you specified using C#, can't you just open a connection and execute
the CREATE PROC call? SMO and such will work too, but it would seem that
something like this (NOTE: Pseudo code):

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = conn.CreateCommand())
{
string storedProc = File.ReadAllText("yourstoredprocfile.txt");
if (storedProc.ToLower().StartsWith("create proc"))
{
cmd.CommandText = storedProc;

try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
if (conn.State != ConnectionState.Closed) conn.Close();
}
}
}


Thanks,

Shawn Wildermuth
Microsoft MVP (C#)
http://wildermuthconsulting.com
mailto:swildermuth@REMOVE_ALLCAPS_adoguy.com

 

[Cor Ligthert[MVP]]

Terrance,

I see a lot of magic here, you get many answers based on a created " Create"
*.sql file, while my thought was that it was about a document text file with
some SQL scripts described in it.

As it is the first, I would not do it, just execute it in SQL management
studio and you are ready.



Cor