NCSA SCADA
asked on
VB.net convert a sql stored procedure to code in application
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
ASKER
will this work for both sql 2k and 2k5
Yes, it will. And SQL7 and SQL 2008.
Probably would be best to do a check whether it already exists:
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"
That way you get to keep the performance and transactionality of your stored procedure.
ASKER
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
Are you always using SQL Server? If so, do you mind if I change the code to the SQL data provider instead of OleDB?
ASKER
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
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
ASKER
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
ASKER
I figured that out - I do have one more error - cmCreateMyProcedure = New SqlCommand(MyConnection)
errors with MyConnection not defined
errors with MyConnection not defined
ASKER
I think im getting there ... it hangs on this line ..... cmCreateMyProcedure.Execut eNonQuery( ) 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
change line 19 to
cmCreateMyProcedure = New SqlCommand(con)
cmCreateMyProcedure = New SqlCommand(con)
Sorry I'm not that good writing the code w/o Intellisense to guide me!
ASKER
made the change i get this error ....... Value of type 'system.Data.SqlClient.Sql Connection cannot be converted to string
grr...
cmCreateMyProcedure = New SqlCommand()
cmCreateMyProcedure.Connection = con
ASKER
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
At compile time? Or runtime?
ASKER
when I click the start debugging arrow
ASKER
any thoughts
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.
thanks.
ASKER
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window