Programatically create a Stored Procedure

[darjonase]

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian

[zacks]

da*******@gmail .com wrote:

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian

I assume you know how to make a SP in, say, the Enterprise Manager?
Just make the proc, and then in EM, run the Generate SQL Scripts
function on the SP, and click on the preview window. It will show you
the transact-sql code you need to create the SP.

Or just review the transact-sql help from the Query Analyzer for the
syntax.

In code, just use a SQLCommand, and set the CommandText to a character
string that contains the entire code to make the SP and execute it as a
non-query.

[Dennis]

Here's excerpts that I copied sometime ago from this newsgroup. I don't know
if they work or not but may give you some ideas:

Access DataBase:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList
alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If
dbCommand.Conne ction = dbConnection
dbConnection.Op en()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

SQL DataBase:
**** Storing Commands in DataAdapter for Future Use ***
Dim da As OleDbDataAdapte r = New OleDbDataAdapte r
Dim cmd As OleDbCommand

' Create the SelectCommand.
cmd = New OleDbCommand("S ELECT * FROM Customers " & "WHERE Country =
@Country AND City = @City", conn)
cmd.Parameters. Add("@Country", OleDbType.VarCh ar, 15)
cmd.Parameters. Add("@City", OleDbType.VarCh ar, 15)
da.SelectComman d = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("I NSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters. Add("@CustomerI D", OleDbType.Char, 5, "CustomerID ")
cmd.Parameters. Add("@CompanyNa me", OleDbType.VarCh ar, 40,
"CompanyNam e")
da.InsertComman d = cmd

--
Dennis in Houston
"da*******@gmai l.com" wrote:

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian

[Cor Ligthert [MVP]]

Dennis,

I saw your code, you use dbConnection.

You know that there is now a new class.

http://msdn2.microsoft.com/en-us/library/c790zwhc.aspx

Just to make you attent on it, it does not harm, but while I was reading
your message I was first confused.

Cor

"Dennis" <De****@discuss ions.microsoft. com> schreef in bericht
news:55******** *************** ***********@mic rosoft.com...

Here's excerpts that I copied sometime ago from this newsgroup. I don't
know
if they work or not but may give you some ideas:

Access DataBase:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList
alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS
SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If
dbCommand.Conne ction = dbConnection
dbConnection.Op en()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

SQL DataBase:
**** Storing Commands in DataAdapter for Future Use ***
Dim da As OleDbDataAdapte r = New OleDbDataAdapte r
Dim cmd As OleDbCommand

' Create the SelectCommand.
cmd = New OleDbCommand("S ELECT * FROM Customers " & "WHERE Country
=
@Country AND City = @City", conn)
cmd.Parameters. Add("@Country", OleDbType.VarCh ar, 15)
cmd.Parameters. Add("@City", OleDbType.VarCh ar, 15)
da.SelectComman d = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("I NSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters. Add("@CustomerI D", OleDbType.Char, 5, "CustomerID ")
cmd.Parameters. Add("@CompanyNa me", OleDbType.VarCh ar, 40,
"CompanyNam e")
da.InsertComman d = cmd

--
Dennis in Houston
"da*******@gmai l.com" wrote:

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian

[guy]

stored procs?
read this excellent article
http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

"da*******@gmai l.com" wrote:

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian

[Dennis]

Thanks Cor. I don't have vb2005 yet as I am waiting for SP1 to come out. I
haven't used the code I posted, just copied if from someone's note here on
this newsgroup...I copy intesesting code that people like yourself post and
index it for future use in my master file.
--
Dennis in Houston
"Cor Ligthert [MVP]" wrote:

Dennis,

I saw your code, you use dbConnection.

You know that there is now a new class.

http://msdn2.microsoft.com/en-us/library/c790zwhc.aspx

Just to make you attent on it, it does not harm, but while I was reading
your message I was first confused.

Cor

"Dennis" <De****@discuss ions.microsoft. com> schreef in bericht
news:55******** *************** ***********@mic rosoft.com...

Here's excerpts that I copied sometime ago from this newsgroup. I don't
know
if they work or not but may give you some ideas:

Access DataBase:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList
alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS
SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If
dbCommand.Conne ction = dbConnection
dbConnection.Op en()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

SQL DataBase:
**** Storing Commands in DataAdapter for Future Use ***
Dim da As OleDbDataAdapte r = New OleDbDataAdapte r
Dim cmd As OleDbCommand

' Create the SelectCommand.
cmd = New OleDbCommand("S ELECT * FROM Customers " & "WHERE Country
=
@Country AND City = @City", conn)
cmd.Parameters. Add("@Country", OleDbType.VarCh ar, 15)
cmd.Parameters. Add("@City", OleDbType.VarCh ar, 15)
da.SelectComman d = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("I NSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters. Add("@CustomerI D", OleDbType.Char, 5, "CustomerID ")
cmd.Parameters. Add("@CompanyNa me", OleDbType.VarCh ar, 40,
"CompanyNam e")
da.InsertComman d = cmd

--
Dennis in Houston
"da*******@gmai l.com" wrote:

Alright, so I am creating an entire database in code since I have no
idea how to bundle a created database in an install of my application.
I know how to create the database and the tables in VB.NET code just
fine, what I can't figure out is how to create stored procedures in
VB.NET that will be saved in this same database.

Can anyone help me out on this one?

Darian