I was hoping for a little code review. This is my first real .NET
application, and would appreciate some advice. For example, in the code
below, notice that I call cn.CreateCommand twice. I tried re-using the
original object and just resetting it's CommandText property, but I got
unreadable output in the results.
The code below creates a "parent" SQL Server record and get's its Identity
value for subsequent "child" inserts.
Although the code works, and is actually quite fast, the code just doesn't
look elegant. Suggestions?
Note, this is just a subset of all the code. I do close the global SQL
connection at the end of processing.
thx
Jeff
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As New SqlClient.SqlConnection
....
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles MyBase.Load
cn.ConnectionString = sConnect
cn.Open()
End Sub
Private Function WriteToDB() As Boolean
Dim myDataAdapter As New SqlClient.SqlDataAdapter
Dim myDataSet As New System.Data.DataSet
Dim myTable As New System.Data.DataTable
Dim dr As System.Data.DataRow
Dim sNewIncidentID As String
Dim sNewExposureID As String
Dim sNewBasicID As String
Dim sNewApparatusID As String
Dim sSQL As String
Dim sAccountID As String = "9"
Dim sState As String = "CA"
Dim cmd As SqlClient.SqlCommand = cn.CreateCommand
sSQL = "exec ers_CreateIncident " & _
sAccountID & _
",'" & sState & "'" & _
",'" & sRECEIVED_DATE & "'" & _
",'" & sRECEIVED_TIME & "'" & _
",'" & sIncidentAddress & "<BR>" & sState & "'" & _
",'" & sIncidentType & "'"
cmd.CommandText = sSQL
myDataAdapter.SelectCommand = cmd
myDataAdapter.Fill(myDataSet)
myTable = myDataSet.Tables(0)
If myTable.Rows.Count > 0 Then
dr = myTable.Rows(0)
sNewIncidentID = dr("NewIncidentID")
cmd = cn.CreateCommand
cmd.CommandText = "exec ers_CreateExposure " & sNewIncidentID
myDataAdapter = New SqlClient.SqlDataAdapter
myDataAdapter.SelectCommand = cmd
myDataSet = New System.Data.DataSet
myDataAdapter.Fill(myDataSet)
myTable = myDataSet.Tables(0)
End If
End Function
End Class
The Stored Procedure:
create procedure ers_CreateIncident
@AID int,
@State varchar(10),
@IncidentDate varchar(8),
@IncidentTime varchar(6),
@Address varchar(500),
@IncidentType varchar(20)
as
declare @IncidentNumber int
declare @FDID varchar(5)
select @IncidentNumber = isnull(max(convert(int, incidentnumber)), 0) + 1
from incidents
where aid = @AID
and DATEPART(yy, IncidentDate) = DATEPART(yy, GetDate())
select @FDID = FDID from Accounts
where AccountID = @AID
INSERT Incidents (AID,
State,
FDID,
IncidentDate,
IncidentTime,
IncidentNumber,
Address,
IncidentType)
values(
@AID,
@State,
@FDID,
@IncidentDate,
@IncidentTime,
@IncidentNumber,
@Address,
@IncidentType)
SELECT SCOPE_IDENTITY() as NewIncidentID