Solved

VB.net convert a sql stored procedure to code in application

Posted on 2009-05-06
23
492 Views
Last Modified: 2013-11-26
Thanks in advance experts.  I am still learning (im discovering self teaching programming skills is not easy), so any code examples would be great - I currently have written a program that deletes a user from a sql 2000 table.  To do this I have to delete some additional linked data first.  I have a stored procedure I call that does the work.  This works great.  The problem is I have more than 60 sql servers across the country that I will have to create this stored procedure on. I can think of three ways to tackle this.  1) Create the stored procedure (not something I want to do) 2) create it programmatically when my app runs (I just dont know how to do that) 3) convert the stored procedure into code directly in my app. & any help would be great . As our server count will continue to grow, I would like to have the code in my app and not use the stored procedure. I just do not know how to do that.  I have attached the code for the stored procedure and my vb code  thanks again
Stored procedure - - - - 

CREATE PROCEDURE Delete_Users

@UserID varchar(50)

AS

delete from TParkUnit_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID)) 

delete from TNotificationProfile_TNotificationMedia where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID)) 

delete from TEventCode_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID)) 

delete from TNotificationProfile where userid=(select UserId from TUser where UserInitials = @UserID)
 

delete from TMaintenanceProcess where userid=(select UserId from TUser where UserInitials = @UserID)
 

delete from TWebCustomPosition where userid=(select UserId from TUser where UserInitials = @UserID)
 

delete from TUser_TReportProfile where userid=(select UserId from TUser where UserInitials = @UserID)
 

delete from TUser where UserInitials = @UserID

GO

---------------------------------------------------------

My VB code
 

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        Dim con As New OleDb.OleDbConnection

        Dim dbProvider As String

        Dim da As New OleDb.OleDbDataAdapter

        Dim ds As New DataSet

        Dim sql As String

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        Dim Server As String
 

        Dim UserInit As String = txtUserInit.Text
 

        Server = txtIPAddress.Text

        dbProvider = "provider = sqloledb;Data Source=" & Server & ";Initial Catalog=Windman;User ID=user;Password=password"

        sql = "Delete_Users"

        con = New OleDb.OleDbConnection(dbProvider)

        con.Open()

        Dim cmd As New System.Data.OleDb.OleDbCommand("Delete_Users", con)

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue(String.Empty, UserInit)
 
 

        If con.State <> ConnectionState.Open Then

            con.Open()

        End If
 

        If cmd.ExecuteNonQuery() < 1 Then
 

        End If
 

        con.Close()
 

        MsgBox("User Deleted")

    End Sub

Open in new window

0
Comment
Question by:NCSA SCADA
  • 12
  • 11
23 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24316723
Creating the stored procedure through a script or through your application will be better than pulling all those DELETE statements into your VB.




SqlCommand cmCreateMyProcedure = new SqlCommand(MyConnection)

cmCreateMyProcedure.CommandText = "Create Procedure 

 AND the rest of the procedure as you pasted it in

"
 

cmCreateMyProcedure.ExecuteNonQuery();

Open in new window

0
 

Author Comment

by:NCSA SCADA
ID: 24317148
will this work for both sql 2k and 2k5
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24317370
Yes, it will. And SQL7 and SQL 2008.

Probably would be best to do a check whether it already exists:


cmCreateMyProcedure.CommandText = "If not exists (Select * from sysobjects where xtype = 'p' and name = 'Delete_Users') " & _

" Create Procedure  Delete_Users " & _

" @UserID varchar(50) " & _

" AS " & _

" delete from TParkUnit_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TNotificationProfile_TNotificationMedia where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TEventCode_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TNotificationProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TMaintenanceProcess where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TWebCustomPosition where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TUser_TReportProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TUser where UserInitials = @UserID"

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24317377
That way you get to keep the performance and transactionality of your stored procedure.
0
 

Author Comment

by:NCSA SCADA
ID: 24317393
I think I am missing somthing - im not sure how to take my connection and build with the code you posted - sorry for the newbie response, but could use my defined variables and give an example of the connection/sql command ect.  - - - thanks so much for your help
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24317454
Are you always using SQL Server?  If so, do you mind if I change the code to the SQL data provider instead of OleDB?
0
 

Author Comment

by:NCSA SCADA
ID: 24317500
it is always sql, and I do not mind at all - As I was trying to learn this stuff it is likely that i have picked some less than efficient ways of coding - any pointers are most appreciated - thanks
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24317733
using http://connectionstrings.com/sql-server-2005#1 for the connection string ...



 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        Dim con As New SqlClient.SqlConnection

        Dim dbProvider As String

        Dim sql As String

        Dim Server As String

 

        Dim UserInit As String = txtUserInit.Text
 

        Static bProcCreated as Boolean 'so we don't call the CREATE PROCEDURE block quite so often

 

        Server = txtIPAddress.Text

        dbProvider = "Server=" & Server & ";Datbase=Windman;User ID=user;Password=password;Trusted_Connection=False;"

        con = New SqlClient.SqlConnection(dbProvider)

        con.Open()
 

        If not bProcCreated then

dim cmCreateMyProcedure as SqlCommand

cmCreateMyProcedure = new SqlCommand(MyConnection)

cmCreateMyProcedure.CommandText = "If not exists (Select * from sysobjects where xtype = 'p' and name = 'Delete_Users') " & _

" Create Procedure  Delete_Users " & _

" @UserID varchar(50) " & _

" AS " & _

" delete from TParkUnit_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TNotificationProfile_TNotificationMedia where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TEventCode_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

" delete from TNotificationProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TMaintenanceProcess where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TWebCustomPosition where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TUser_TReportProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _ 

" delete from TUser where UserInitials = @UserID"

 

cmCreateMyProcedure.ExecuteNonQuery();

           bProcCreated=true

        end if
 

        sql = "Delete_Users"

        Dim cmd As New System.Data.SqlClient.SqlCommand("Delete_Users", con)

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue(String.Empty, UserInit)

 

 

        If con.State <> ConnectionState.Open Then

            con.Open()

        End If

 

        If cmd.ExecuteNonQuery() < 1 Then

 

        End If

 

        con.Close()

 

        MsgBox("User Deleted")

    End Sub

Open in new window

0
 

Author Comment

by:NCSA SCADA
ID: 24317806
Thanks so much - not sure if its a ver thing, but I am getting "type sqlcommand is not defined" . I am using visual studio 2008 - thanks again
0
 

Author Comment

by:NCSA SCADA
ID: 24317994
I figured that out - I do have one more error - cmCreateMyProcedure = New SqlCommand(MyConnection)
errors with MyConnection not defined
0
 

Author Comment

by:NCSA SCADA
ID: 24318188
I think im getting there ... it hangs on this line ..... cmCreateMyProcedure.ExecuteNonQuery() with this error ....... ExecuteNonQuery: Connection property has not been initialized. - I have attached the code as I am using it .... note the changes that I asked about in the last two messages - thanks
Imports System.Data.SqlClient

Public Class Form1
 
 
 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New SqlClient.SqlConnection

        Dim dbProvider As String

        Dim Server As String
 

        Static bProcCreated As Boolean 'so we don't call the CREATE PROCEDURE block quite so often

        Server = "10.106.3.220"

        dbProvider = "Server=" & Server & ";Database=Windman;User ID=user;Password=password;Trusted_Connection=False;"

        con = New SqlClient.SqlConnection(dbProvider)

        con.Open()
 

        If Not bProcCreated Then

            Dim cmCreateMyProcedure As SqlCommand

            cmCreateMyProcedure = New SqlCommand(dbProvider)

            cmCreateMyProcedure.CommandText = "If not exists (Select * from sysobjects where xtype = 'p' and name = 'Delete_Users') " & _

            " Create Procedure  Delete_Users " & _

            " @UserID varchar(50) " & _

            " AS " & _

            " delete from TParkUnit_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

            " delete from TNotificationProfile_TNotificationMedia where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

            " delete from TEventCode_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

            " delete from TNotificationProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _

            " delete from TMaintenanceProcess where userid=(select UserId from TUser where UserInitials = @UserID) " & _

            " delete from TWebCustomPosition where userid=(select UserId from TUser where UserInitials = @UserID) " & _

            " delete from TUser_TReportProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _

            " delete from TUser where UserInitials = @UserID"
 

            cmCreateMyProcedure.ExecuteNonQuery()

            bProcCreated = True

        End If
 

        
 

    End Sub

End Class

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24318274
change line 19 to
            cmCreateMyProcedure = New SqlCommand(con)

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24318276
Sorry I'm not that good writing the code w/o Intellisense to guide me!
0
 

Author Comment

by:NCSA SCADA
ID: 24318335
made the change i get this error ....... Value of type 'system.Data.SqlClient.SqlConnection cannot be converted to string
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24318488
grr...
cmCreateMyProcedure = New SqlCommand()

cmCreateMyProcedure.Connection = con

Open in new window

0
 

Author Comment

by:NCSA SCADA
ID: 24318673
Well .... you are earning your points today - that worked, but now I am getting an error that says must declare variable @UserID .... the error repeats for each line after the declaration of @UserID .... it looks like some sort of problem with the line above it -  also says incorrect sysntax near the keyword procedure
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24318717
At compile time?  Or runtime?
0
 

Author Comment

by:NCSA SCADA
ID: 24318765
when I click the start debugging arrow
0
 

Author Comment

by:NCSA SCADA
ID: 24319143
any thoughts
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24319301
please post the entire Button1_click as it now stands.  I'll throw it into VB.Net over here & see if I can find the problem.

thanks.
0
 

Author Comment

by:NCSA SCADA
ID: 24319790
heree ya go - thanks
Imports System.Data.SqlClient

Public Class Form1
 
 
 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New SqlClient.SqlConnection

        Dim dbProvider As String

        Dim Server As String
 
 

        Server = "10.106.3.220"

        dbProvider = "Server=" & Server & ";Database=Windman;User ID=user;Password=pass;Trusted_Connection=False;"

        con = New SqlClient.SqlConnection(dbProvider)

        con.Open()
 
 

        Dim cmCreateMyProcedure As SqlCommand

        'cmCreateMyProcedure = New SqlCommand con

        cmCreateMyProcedure = New SqlCommand()

        cmCreateMyProcedure.Connection = con
 

        cmCreateMyProcedure.CommandText = "If not exists (Select * from sysobjects where xtype = 'p' and name = 'Delete_Users') " & _

        " Create Procedure  Delete_Users " & _

        " @UserID varchar(50) " & _

        " AS " & _

        " delete from TParkUnit_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

        " delete from TNotificationProfile_TNotificationMedia where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

        " delete from TEventCode_TNotificationProfile where NotificationProfileId = (select NotificationProfileId from TNotificationProfile where UserId = (select UserId from TUser where UserInitials = @UserID))  " & _

        " delete from TNotificationProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _

        " delete from TMaintenanceProcess where userid=(select UserId from TUser where UserInitials = @UserID) " & _

        " delete from TWebCustomPosition where userid=(select UserId from TUser where UserInitials = @UserID) " & _

        " delete from TUser_TReportProfile where userid=(select UserId from TUser where UserInitials = @UserID) " & _

        " delete from TUser where UserInitials = @UserID"
 

        cmCreateMyProcedure.ExecuteNonQuery()
 
 
 

        
 

    End Sub

End Class

Open in new window

0
 

Author Comment

by:NCSA SCADA
ID: 24320665
ok - I think i figured it out - seems to work, but it all shows up on one line in the stored procedure ... is there a way to break up the lines so it is easier to read -thanks again
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 24320766
Yeah, that's annoying.

Change & _
 to & vbcrlf & _
where the line continuation occurs. That will insert a newline character.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now