Hi there
As far as I can tell the C
reateAuditPolicy
Function only executes one query. Therefore it is not necessary to use a transaction.I also do not think it necessary for the procedure to return anything. If there is an error the
Using
directive should ensure that the connection is closed properly and the error passed on to the calling procedure. So this is how I would set it up :
Public Shared Sub CreateAuditPolicy(ByVal title As String, ByVal content As String, ByVal policycategory As Int32)
Using con As SqlConnection = New SqlConnection("myConnectionStrings")
Using cmd As SqlCommand = New SqlCommand()
With cmd
.CommandText = "CreatePolicy"
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@PolicyTitle", title)
.Parameters.AddWithValue("@PolicyContent", content)
.Parameters.AddWithValue("@CategoryID", policycategory)
.Parameters.Add("@retvalue", SqlDbType.VarChar, 100)
.Parameters("@retvalue").Direction = ParameterDirection.Output
.Connection = con
End With
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Then in your presentation layer your button Save would use a Try,Catch End Try Block to catch the error and inform the user:
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim msg As String = ""
Try
AdmonAuditClass.CreateAuditPolicy(Me.txtTitle.Text.Trim(), Me.txtContent.Text.Trim(), Me.PolicyCategoryComboBox.SelectedValue)
MsgBox(" Your data was successfully saved")
Me.txtContent.Text = ""
Me.txtTitle.Text = ""
Catch ex As SqlException
AdmonUtilityClass.ShowErrorProvider(btnSave, True, msg, " My.Resources.ErrorIcon", ErrorIconAlignment.MiddleRight)
End Try
End Sub
Anyway there is so many different opinions on error handling between layers. This is just how I would do it
happy Coding