Programatically create a Stored Procedure

22 Jun 2006 6:51 PM

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

22 Jun 2006 7:03 PM

zacks

darjon***@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.

22 Jun 2006 11:12 PM

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

Show quoteHide quote

"darjon***@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
>
>

23 Jun 2006 5:49 AM

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

Show quoteHide quote

"Dennis" <Den***@discussions.microsoft.com> schreef in bericht
news:55B32B65-80C5-4F50-8CBA-FD78D7F9B946@microsoft.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
>
>
> "darjon***@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
>>
>>

24 Jun 2006 12:23 AM

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

Show quoteHide quote

"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" <Den***@discussions.microsoft.com> schreef in bericht
> news:55B32B65-80C5-4F50-8CBA-FD78D7F9B946@microsoft.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
> >
> >
> > "darjon***@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
> >>
> >>
>
>
>

23 Jun 2006 8:13 AM

guy

stored procs?
read this excellent article http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspxhttp://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Show quoteHide quote

"darjon***@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
>
>