How do you create a stored procedure using ADO.NET

[Former Member]

I found a post that included code for executing a stored procedure using ADO.NET from within a SAP addon, but how do you use the ADO.NET commands to create a stored procedure when it does not already exist.

The following code was suggested for executing an existing stored procedure:

conn.Provider = "sqloledb"

conn.open "Server=" & sboCompany.Server, sboCompany.DbUserName, ""

conn.DefaultDatabase = sboCompany.CompanyDB

cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "MyStoredProcedure"

cmd.Parameters("@MyFirstParameter").Value = "My Parameter Value"

cmd.Execute

But, if the procdure does not exist, do you still set the CommandType property to adCmdStoredProc? Also, I have the SQL script to create my stored procedure in a separate, .txt file (SBO_Usp_MyStoredProcedure.txt) within my .NET project. Do I have to set the CommandText property to that .txt file name including the file extension?

Thanks for any advice,

Nancy Walk

PS - I wanted to use the isqlw.exe utility to do all of this, but the person in charge of our addon project wants our stored procedures to be database independent, so I cannot use the SQL utility, so now I am trying to use ADO.NET instead.

[Former Member · Accepted solution]

Hi Nancy,

Before you create the stored procedure you should check whether it exists, and if it does you should drop it.

Below is an example of how to create a stored procedure:

string Sql = "CREATE PROCEDURE MYPROC AS....." //define your stored proc sql here

Command.Connection = GetSBOConnection(); //define a method for retrieving the current SBO company database

Command.CommandType = CommandType.Text;

Command.CommandText = Sql;

Command.Parameters.Clear();

int Result = Command.ExecuteNonQuery();

Regards, Lita

[Former Member · Accepted solution]

Hi Lita,

how can u check if stored procedure still exists?? With another stored procedure? Can u give an example please?

Thanks a lot

Christ

[barend_morkel2 · Accepted solution]

Hi Christian,

You can run this SQL String in the same stored procedure creation string that Lita gave you (just paste it before the "Create procedure part") or

as a straight sql command (in Query analyzer)

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'YourStoredProcedureName' AND type = 'P')

DROP PROCEDURE YourStoredProcedureName

GO

-> Now write your create procedure part...

[Former Member · Accepted solution]

Thanks a lot !!

[former_member201110]

Hi Nancy,

The following link should take you to an MSDN article that shows how to create a database using ADO.NET. The article goes through how you can read the script from your text file and execute it using ADO.NET. You should be able to play around with the code examples to get what you want.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/html/vxwlkWalkthroughUsing...

Hope this helps,

Owen