rewriting visual basic windows .exe to stored procedure
-
Has anyone heard of rewriting visual basic code (it's compiled into an exe file and run at intervals via Windows Scheduler to update various tables) to a stored procedure? The VB code that I just inherited is fairly complex and touches many database objects and, of course, it's totally undocumented. I don't even know what all it does and no one can tell me.
Originally I thought that I could just move it to the database server, set up an SSIS package to call the exe and then schedule it as a job via the SQL Server agent. It all worked great. Turns out that's not what my new boss wanted. He wants a sproc.
I have been googling my fingers to the bone to find anyone who has done this before just to see how someone might have tackled it. I cannot find anyone yet. One promising link sounded good but turned out to be calling VB from a sproc.
I'm looking at the VB code and I don't know where to start. I don't what it does and it touches so many tables and user defined data types. The tables have many relationships, foreign keys, etc. There are lots of arrays, data readers, etc. All that I recognize are the embedded SQL statements.
PLease reply even if it's to say, "Why in the world are you doing that??!!"
I can post code but I'm not asking for someone to do all the heavy lifting for me - just some opinions on what I'm trying to do, whether it makes sense, and if you know of any examples.
Thanks in advance for reading all this.
farmkitteh
-
1. Ask to hire someone with VB skills
or
2. Ask for complete requirements
I cannot see how you can reliably rewrite someone else code written in a language you can't read.
As for the task itself: it may be possible to rewrite it as a stored proc and schedule execution of the stored proc using SQL Server Agent or some other scheduling software...
-
Thanks for your reply Eugene. I think they expect me to figure it out. I would feel like I had a prayer if I knew what the code was supposed to do but I have been asked, "Can't you figure it out from looking at the code?".
I'm hoping to hear about a case where somebody did a rewrite like this and how they approached it. Or, failing that, if people think this isn't a good idea to begin with.
-
i've done the same as what you are asking many times...moving data logic out of VB and into stored procs...post the code as an attachment, i'm sure we can give you some pointers and maybe a skeleton to get started with.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have done something similar in the past, I would take it in stages.
The first stage would be to get a good idea overall of what the goal is. The second stage would be to look at each area and write out what it does, not how it does it but WHAT it is doing, such as pulls data out of tables A, C, X with the query SELECT... and then changes column Y based on H criteria. The third stage would be to start at the beginning and recode each section. Don't code the section until you understand what is going on and what its goal is.
Also keep in mind if it is being done in an exe that it is almost certainly coded for row-based actions and there is a lot of room for growth using set based operations. The detail from stage 2 would help get your hands around that.
It is entirely likely that this can be simplified and streamlined because what you are describing is what I call the hammer and nail principle. When all you have is a hammer (.Net, C++, PHP, JAVA, SQL) everything looks like a nail to be solved with the hammer.. Basically if all you know is .NET then you will likely try to solve all your problems with .NET, without understanding or sonsidering that this might be better solved with another language such as SQL.. Unfortunately I have seen this MANY times, and have been guilty of it in the past myself..
CEWII
-
Wow, thanks Lowell! [This is in reply to Lowell above, I see there are more comments now that I will read...]
Here's the code for one of them :
Public Class Form1Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.cnJustice.Open()
Dim dteParm As Date = DateValue(Now)
'dteParm = "#9-29-2011#"'
Me.daFutureHearings.SelectCommand.Parameters("@prmDate").Value = dteParm
Me.daFutureHearings.Fill(Me.DsFutureHearings1.CaseStatusHist)
Me.daFutureHearings.Dispose()
Dim aryHearingID(1, 5) As Integer
Dim aryCaseEvent() As Integer
Dim iCaseID As Integer
Dim iCtr As Integer = 0
Dim iHearingIDRows As Integer
Dim iIndex As Integer
Dim iIndexAry As Integer
Dim iReviewCompleteDate As Integer = 0
Dim iSettingOffsetMinutes As Integer
Dim iTotalRecs As Integer = Me.DsFutureHearings1.CaseStatusHist.Count
Dim iUpdated As Integer = 0
Dim rdrCtSession As SqlClient.SqlDataReader
Dim rdrCtSessionBlock As SqlClient.SqlDataReader
Dim rdrCaseEvent As SqlClient.SqlDataReader
Dim rdrEvent As SqlClient.SqlDataReader
Dim rdrHearingEvent As SqlClient.SqlDataReader
Dim dteFromDate As Date = DateAdd(DateInterval.Day, -21, DateValue(Now))
dteFromDate = DateAdd(DateInterval.Day, -21, dteParm)
Dim dteToDate As Date = DateValue(Now)
dteToDate = dteParm
Dim dteSessionDate As Date
Dim dteStartTime As Date
Dim sSQL As String
For iIndex = 0 To iTotalRecs - 1
With Me.DsFutureHearings1.CaseStatusHist.Rows(iIndex)
iCaseID = .Item("CaseID")
If .Item("DtCaseStatus") < dteFromDate Or .Item("DtCaseStatus") > dteToDate Then
Continue For
End If
sSQL = "Select count(*) from xCaseBaseCaseFlag Where CaseID = " & iCaseID
sSQL = sSQL & " and CaseFlagID = 14998"
Me.cmdSelect.CommandText = sSQL
rdrCaseEvent = Me.cmdSelect.ExecuteReader
rdrCaseEvent.Read()
If rdrCaseEvent.Item(0) > 0 Then
rdrCaseEvent.Close()
iUpdated = iUpdated + 1
sSQL = "Delete from xCaseBaseCaseFlag Where CaseID = " & iCaseID
sSQL = sSQL & " and CaseFlagID = 14998"
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
Else
rdrCaseEvent.Close()
End If
sSQL = "Select Count(*) from Justice.dbo.CaseEvent as ce"
sSQL = sSQL & " inner join Event as e on ce.EventID = e.EventID"
sSQL = sSQL & " where (caseid = " & iCaseID & ") and (e.eventtypeid between 15244 and 15248) and (ReviewCompleteDate is null)"
Me.cmdSelect.CommandText = sSQL
rdrCaseEvent = Me.cmdSelect.ExecuteReader
rdrCaseEvent.Read()
If rdrCaseEvent.Item(0) = 0 Then
rdrCaseEvent.Close()
GoTo TAG1
End If
ReDim aryCaseEvent(rdrCaseEvent.Item(0))
rdrCaseEvent.Close()
iCtr = 0
sSQL = "Select ce.eventid from Justice.dbo.CaseEvent as ce"
sSQL = sSQL & " inner join Event as e on ce.EventID = e.EventID"
sSQL = sSQL & " where (caseid = " & iCaseID & ") and (e.eventtypeid between 15244 and 15248) and (ReviewCompleteDate is null)"
Me.cmdSelect.CommandText = sSQL
rdrCaseEvent = Me.cmdSelect.ExecuteReader
While rdrCaseEvent.Read
iCtr = iCtr + 1
aryCaseEvent(iCtr) = rdrCaseEvent.Item(0)
End While
rdrCaseEvent.Close()
For iCtr = 1 To UBound(aryCaseEvent)
sSQL = "Update Justice.dbo.CaseEvent Set ReviewCompleteDate = '" & .Item("DtCaseStatus")
sSQL = sSQL & "' Where EventID = " & aryCaseEvent(iCtr)
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
iReviewCompleteDate = iReviewCompleteDate + 1
Next
TAG1: sSQL = "Select Count(*) from HearingEvent as he"
sSQL = sSQL & " inner join Setting as s on s.HearingID = he.HearingID"
sSQL = sSQL & " inner join Event as e on he.hearingID = e.EventID"
sSQL = sSQL & " where CaseID = " & iCaseID
sSQL = sSQL & " and ResultID IS NULL and RescheduleReasonID IS NULL and e.Deleted = 0"
sSQL = sSQL & " and (s.CancelledDateTime IS NULL)"
Me.cmdSelect.CommandText = sSQL
rdrHearingEvent = Me.cmdSelect.ExecuteReader
rdrHearingEvent.Read()
If rdrHearingEvent.Item(0) = 0 Then
rdrHearingEvent.Close()
Continue For
End If
iHearingIDRows = rdrHearingEvent.Item(0)
ReDim aryHearingID(iHearingIDRows, 5)
rdrHearingEvent.Close()
sSQL = "Select CourtSessionBlockID, s.HearingID, s.SettingID, s.CourtSessionBlockID from HearingEvent as he"
sSQL = sSQL & " inner join Setting as s on s.HearingID = he.HearingID"
sSQL = sSQL & " inner join Event as e on he.hearingID = e.EventID"
sSQL = sSQL & " where CaseID = " & iCaseID
sSQL = sSQL & " and ResultID IS NULL and RescheduleReasonID IS NULL and e.Deleted = 0"
sSQL = sSQL & " and (s.CancelledDateTime IS NULL)"
Me.cmdSelect.CommandText = sSQL
rdrHearingEvent = Me.cmdSelect.ExecuteReader
If Not rdrHearingEvent.HasRows Then
rdrHearingEvent.Close()
Continue For
End If
iIndexAry = 1
While rdrHearingEvent.Read
aryHearingID(iIndexAry, 1) = rdrHearingEvent.Item("HearingID")
aryHearingID(iIndexAry, 2) = rdrHearingEvent.Item("SettingID")
aryHearingID(iIndexAry, 3) = rdrHearingEvent.Item("CourtSessionBlockID")
iIndexAry = iIndexAry + 1
End While
rdrHearingEvent.Close()
For iHearingIDRows = 1 To UBound(aryHearingID)
sSQL = "Select CourtSessionID, OffsetMinutes from CtSessionBlock Where CourtSessionBlockID = " & aryHearingID(iHearingIDRows, 3)
Me.cmdSelect.CommandText = sSQL
rdrCtSessionBlock = Me.cmdSelect.ExecuteReader
If Not rdrCtSessionBlock.HasRows Then
rdrCtSessionBlock.Close()
Continue For
End If
rdrCtSessionBlock.Read()
aryHearingID(iHearingIDRows, 4) = rdrCtSessionBlock.Item("CourtSessionID")
aryHearingID(iHearingIDRows, 5) = rdrCtSessionBlock.Item("OffsetMinutes")
rdrCtSessionBlock.Close()
Next
For iHearingIDRows = 1 To UBound(aryHearingID)
sSQL = "Select SessionDate, TimeStampCreate, StartTime from CtSession Where CourtSessionID = " & aryHearingID(iHearingIDRows, 4)
Me.cmdSelect.CommandText = sSQL
rdrCtSession = Me.cmdSelect.ExecuteReader
If Not rdrCtSession.HasRows Then
rdrCtSession.Close()
Continue For
End If
rdrCtSession.Read()
If rdrCtSession.Item("SessionDate") < dteParm Then
rdrCtSession.Close()
Continue For
End If
dteStartTime = rdrCtSession.Item("StartTime")
dteSessionDate = rdrCtSession.Item("SessionDate")
rdrCtSession.Close()
sSQL = "Select TimeStampCreate, TimeStampChange from Event Where EventID = " & aryHearingID(iHearingIDRows, 1)
Me.cmdSelect.CommandText = sSQL
rdrEvent = Me.cmdSelect.ExecuteReader
If Not rdrEvent.HasRows Then
rdrEvent.Close()
Continue For
End If
rdrEvent.Read()
If IsDBNull(rdrEvent.Item("TimeStampChange")) Then
If DateValue(rdrEvent.Item("TimeStampCreate")) = dteParm Then
rdrEvent.Close()
Continue For
End If
Else
If DateValue(rdrEvent.Item("TimeStampChange")) = dteParm Then
rdrEvent.Close()
Continue For
End If
End If
rdrEvent.Close()
sSQL = "Select OffSetMinutes from Setting Where SettingID = " & aryHearingID(iHearingIDRows, 2)
Me.cmdSelect.CommandText = sSQL
rdrEvent = Me.cmdSelect.ExecuteReader
rdrEvent.Read()
iSettingOffsetMinutes = rdrEvent.Item("OffsetMinutes")
rdrEvent.Close()
Dim dteTime As Date = TimeValue(dteStartTime) ' CTSession StartTime
dteTime = dteTime.AddMinutes(aryHearingID(iHearingIDRows, 5)) 'CTSessionBlock OffsetMinutes
dteTime = dteTime.AddMinutes(iSettingOffsetMinutes) ' Setting OffsetMinutes
Dim iHours As Integer = DatePart(DateInterval.Hour, dteTime)
Dim iMinutes As Integer = DatePart(DateInterval.Minute, dteTime)
dteSessionDate = dteSessionDate.AddHours(iHours)
dteSessionDate = dteSessionDate.AddMinutes(iMinutes)
sSQL = "Update Setting Set CancelledStatus = 1, CancelledReasonID = 108, CancelledDateTime = '" & dteSessionDate
sSQL = sSQL & "', UserIDChange = 442, TimeStampChange = '" & Now
sSQL = sSQL & "' Where SettingID = " & aryHearingID(iHearingIDRows, 2)
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
sSQL = "Update HearingEvent Set CancelledReasonID = 108 Where HearingID = " & aryHearingID(iHearingIDRows, 1)
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
iUpdated = iUpdated + 1
Next
End With
Next
sSQL = "Insert Into Worktables.dbo.tblScriptJobInfo Values('CivilCancelFutureHearings', '" & Now & "', '"
sSQL = sSQL & "Job completed successfully and updated " & iUpdated & " cases')"
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
End Try
sSQL = "Insert Into Worktables.dbo.tblScriptJobInfo Values('CivilCancelFutureHearings', '" & Now & "', '"
sSQL = sSQL & "Updated Review Complete Dates = " & iReviewCompleteDate & "')"
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
Me.cnJustice.Close()
End
End Sub
End Class
-
also guilty of that here!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Elliot. If I had my choice we'd keep it in VB and just run it from SSIS because it is so much time spent rewriting it in a sproc when I don't know what it does. I will definitely use your outline to guide me in tackling this endeavor.
Thanks again!
-
I feel a little dense here. What do you mean about the "whole world looks like a nail" in this context? Do you mean we (or I) would tend to use a sproc, or T-SQL, rather than Visual Basic because I know T-SQL and Sprocs? I actually would prefer to leave it as the VB.exe called through SSIS because "if it ain't broke, don't fix it".
Thanks guys.
-
Lowell (3/12/2012)
i've done the same as what you are asking many times...moving data logic out of VB and into stored procs...post the code as an attachment, i'm sure we can give you some pointers and maybe a skeleton to get started with.It may be quitting time for you depending on your timezone, but if you still want to post a code skeleton when you have a chance in the next few days, I'd be much obliged. Thanks!
-
Luckily I grew out of it.. But that required knowing more than just T-SQL, I already had a base in BASIC, and then Visual Basic, it took a bit then I jumped to VB.NET. The point being I had an understanding of the strengths and weeknesses of the various languages. This allowed me to better chose a toolset for a particular problem. I also had a case where there was no compelling reason to chose between .NEW and T-SQL so it ended up being decided by the developer who was available. It ended up going .NET..
I'll look at that code posted.
CEWII
-
farmkittie (3/12/2012)
I feel a little dense here. What do you mean about the "whole world looks like a nail" in this context? Do you mean we (or I) would tend to use a sproc, or T-SQL, rather than Visual Basic because I know T-SQL and Sprocs? I actually would prefer to leave it as the VB.exe called through SSIS because "if it ain't broke, don't fix it".Thanks guys.
No, what I mean is that when the only tool a particular developer has knowledge he tends to want to solve ALL his problems with it.
I would argue that the initial developer did this. This is something that I would never have done in an app, I would have coded it as a sproc and scheduled it (based on what I see). I have yet to hit any code that changes that opinion.
From the logic you posted the entire process is done row-by-row with a lot of dynamic SQL.
This section:
sSQL = "Select ce.eventid from Justice.dbo.CaseEvent as ce"sSQL = sSQL & " inner join Event as e on ce.EventID = e.EventID"
sSQL = sSQL & " where (caseid = " & iCaseID & ") and (e.eventtypeid between 15244 and 15248) and (ReviewCompleteDate is null)"
Me.cmdSelect.CommandText = sSQL
rdrCaseEvent = Me.cmdSelect.ExecuteReader
While rdrCaseEvent.Read
iCtr = iCtr + 1
aryCaseEvent(iCtr) = rdrCaseEvent.Item(0)
End While
rdrCaseEvent.Close()
For iCtr = 1 To UBound(aryCaseEvent)
sSQL = "Update Justice.dbo.CaseEvent Set ReviewCompleteDate = '" & .Item("DtCaseStatus")
sSQL = sSQL & "' Where EventID = " & aryCaseEvent(iCtr)
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
iReviewCompleteDate = iReviewCompleteDate + 1
Next
Builds a query, fills an array with the eventid, and then executes update statements against each eventid. How "Me.DsFutureHearings1.CaseStatusHist" is filled I didn't see. But if you built a temp table with the case id list (basically the same query used to fill that Ds you could reduce that block of code to about this:
Update Justice.dbo.CaseEventSet ReviewCompleteDate = c.DtCaseStatus
FROM Justice.dbo.CaseEvent AS ce INNER JOIN Event AS e
ON ce.EventID = e.EventID
INNER JOIN #CaseStatusHist AS c
ON c.CaseId = ce.CaseId
WHERE e.eventtypeid BETWEEN 15244 AND 15248
AND ce.ReviewCompleteDate IS NULL
And I can guarantee it would probably run at LEAST 5 times as fast..
CEWII
-
This chunk:
sSQL = "Select count(*) from xCaseBaseCaseFlag Where CaseID = " & iCaseIDsSQL = sSQL & " and CaseFlagID = 14998"
Me.cmdSelect.CommandText = sSQL
rdrCaseEvent = Me.cmdSelect.ExecuteReader
rdrCaseEvent.Read()
If rdrCaseEvent.Item(0) > 0 Then
rdrCaseEvent.Close()
iUpdated = iUpdated + 1
sSQL = "Delete from xCaseBaseCaseFlag Where CaseID = " & iCaseID
sSQL = sSQL & " and CaseFlagID = 14998"
Me.cmdSelect.CommandText = sSQL
Try
Me.cmdSelect.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
End Try
Else
rdrCaseEvent.Close()
End If
Could be reduced to something like:
DELETE dbo.xCaseBaseCaseFlagFROM dbo.xCaseBaseCaseFlag AS x INNER JOIN #CaseStatusHist AS c
ON x.CaseId = c.CaseId
WHERE CaseFlagID = 14998
CEWII
-
Thanks Elliot,
The code I posted is the entire thing except for the data source connection string. Could this: "Me.DsFutureHearings1.CaseStatusHist" be filled from transactions in the table via a user interface? I think that's what's happening. I've only been on this job for a few weeks and I'm more of a DBA. I see what you are saying about sproc/set-based logic better than row by row (I think there is an acronym I've seen that's something like "rbac":row-by-agonizing row
Again, I appreciate all the help!
-
Thanks, I just saw another chunk of code that you've translated and optimized after I posted a minute ago. Wow!
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply