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 CreateStoredProcedures()
Dim alSql As New ArrayList
alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection(connectionString)
dbCommand = New OleDbCommand
End If
dbCommand.Connection = dbConnection
dbConnection.Open()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.CommandText = DirectCast(alSql(i), String)
dbCommand.ExecuteNonQuery()
Next
dbConnection.Close()
End Sub

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

' Create the SelectCommand.
cmd = New OleDbCommand("SELECT * FROM Customers " & "WHERE Country =
@Country AND City = @City", conn)
cmd.Parameters.Add("@Country", OleDbType.VarChar, 15)
cmd.Parameters.Add("@City", OleDbType.VarChar, 15)
da.SelectCommand = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("INSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID")
cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40,
"CompanyName")
da.InsertCommand = cmd

--
Dennis in Houston
"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

[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****@discussions.microsoft.com> schreef in bericht
news:55**********************************@microsof t.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 CreateStoredProcedures()
Dim alSql As New ArrayList
alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS
SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection(connectionString)
dbCommand = New OleDbCommand
End If
dbCommand.Connection = dbConnection
dbConnection.Open()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.CommandText = DirectCast(alSql(i), String)
dbCommand.ExecuteNonQuery()
Next
dbConnection.Close()
End Sub

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

' Create the SelectCommand.
cmd = New OleDbCommand("SELECT * FROM Customers " & "WHERE Country
=
@Country AND City = @City", conn)
cmd.Parameters.Add("@Country", OleDbType.VarChar, 15)
cmd.Parameters.Add("@City", OleDbType.VarChar, 15)
da.SelectCommand = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("INSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID")
cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40,
"CompanyName")
da.InsertCommand = cmd

--
Dennis in Houston
"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

[guy]

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

"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

[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****@discussions.microsoft.com> schreef in bericht
news:55**********************************@microsof t.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 CreateStoredProcedures()
Dim alSql As New ArrayList
alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS
SELECT
* FROM _ qryResults WHERE ID = inID")
If dbConnection Is Nothing Then
dbConnection = New OleDbConnection(connectionString)
dbCommand = New OleDbCommand
End If
dbCommand.Connection = dbConnection
dbConnection.Open()
Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.CommandText = DirectCast(alSql(i), String)
dbCommand.ExecuteNonQuery()
Next
dbConnection.Close()
End Sub

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

' Create the SelectCommand.
cmd = New OleDbCommand("SELECT * FROM Customers " & "WHERE Country
=
@Country AND City = @City", conn)
cmd.Parameters.Add("@Country", OleDbType.VarChar, 15)
cmd.Parameters.Add("@City", OleDbType.VarChar, 15)
da.SelectCommand = cmd

' Create the InsertCommand.
cmd = New OleDbCommand("INSERT INTO Customers (CustomerID,
CompanyName) " & "VALUES (@CustomerID, @CompanyName)", conn)
cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID")
cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40,
"CompanyName")
da.InsertCommand = cmd

--
Dennis in Houston
"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