更有效地管理异常 [英] Managing exception more efficiently

问题描述

我正在使用存储过程将数据插入sql 2008服务器中的两个表中.现在在处理异常时，我正在使用异常代码来检查异常，并因此向用户显示错误消息.你能告诉我一个更好的选择来做同样的事情吗?

I am using a stored procedure to insert data into two tables in sql 2008 server. Now while handling exception I am using exception code to check the exception and accordingly i am showing the error message to the user.Can u tell me a better option to do the same.

Public Sub rtrnQry(ByVal usrNm As String, ByVal psWd As String, ByVal fNm As String, _
ByVal lNm As String, ByVal empId As Integer, ByVal proNm As String, ByVal aDm As String, ByVal rowno As Integer)
Try

Using cn As New SqlConnection(sqlConnStr)
cn.Open()
Using cmd As New SqlCommand("InsrtLogin", cn)
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@empid", empId)
.Parameters.AddWithValue("@username", usrNm)
.Parameters.AddWithValue("@password", psWd)
.Parameters.AddWithValue("@firstname", fNm)
.Parameters.AddWithValue("@lastname", lNm)
.Parameters.AddWithValue("@adminpri", aDm)
.Parameters.AddWithValue("@proName", proNm)
.ExecuteNonQuery()
End With
End Using
End Using

Catch ex As SqlException
If chkBox1_Crtusr.Checked Then
If ex.Number.Equals(2627) Then 'Exception code for Primary Key Violation
MsgBox("Duplicate Employee ID cannot be inserted. Violation of Primary Key " & _
"Constraint Occured. Enter an unique value.Please check row number : 1", MsgBoxStyle.Critical, "Error")
Else
MsgBox(ex.ToString)
End If
If ex.Number.Equals(208) Then
MsgBox("Invalid Table Name. Check the Sql String.", MsgBoxStyle.Critical, "Error")
End If

End If
If chkBox2_Crtusr.Checked Then
If ex.Number.Equals(2627) Then 'Exception code for Primary Key Violation
MsgBox("Duplicate Employee ID cannot be inserted. Violation of Primary Key " & _
"Constraint Occured. Enter an unique value.Please check row number : 2", MsgBoxStyle.Critical, "Error")
Else
MsgBox(ex.ToString)
End If
If ex.Number.Equals(208) Then
MsgBox("Invalid Table Name. Check the Sql String.", MsgBoxStyle.Critical, "Error")
End If

现在，我正在调用上述程序

Now I am calling the above procedure

Private Sub btnSubmit_CrtMul_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit_CrtMul.Click
Dim cnt As Integer = 0

Try

If chkBox1_Crtusr.Checked Then
rtrnQry(txtUsrNm_1.Text, txtPswd_1.Text, txtFName_1.Text, txtLName_1.Text, txtEmpID_1.Text, cmbProNm1.SelectedItem, cmbAdmin1.SelectedItem, 1)
Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_1.Text & "'"
Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
If usrVerified.Length > 0 Then
Call ClearData(1)
cnt = cnt + 1
End If
End If

If chkBox2_Crtusr.Checked Then
rtrnQry(txtUsrNm_2.Text, txtPswd_2.Text, txtFName_2.Text, txtLName_2.Text, txtEmpID_2.Text, cmbProNm2.SelectedItem, cmbAdmin2.SelectedItem, 2)
Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_2.Text & "'"
Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
If usrVerified.Length > 0 Then
Call ClearData(2)
cnt = cnt + 1
End If
End If

If chkBox3_Crtusr.Checked Then
rtrnQry(txtUsrNm_3.Text, txtPswd_3.Text, txtFName_3.Text, txtLName_3.Text, txtEmpID_3.Text, cmbProNm3.SelectedItem, cmbAdmin3.SelectedItem, 3)
Dim sqlLogin As String = "Select * from login where user_name ='" & txtUsrNm_3.Text & "'"
Dim usrVerified As String = DataStore.ExecuteScalar(sqlLogin, sqlConnStr)
If usrVerified.Length > 0 Then
Call ClearData(3)
cnt = cnt + 1
End If
End If

现在，当我执行此操作时，对于每种异常，错误消息都会出现两次，我也需要注意这一点.请帮忙，在此先感谢

Now when I am doing this, for each exception the error message is appearing twice, I need to take care of that as well. Please Help, Thanks in advance

推荐答案

很明显，为什么两次出现此错误.代码行在异常处理程序中添加了两次.

这部分:

It is pretty clear why you get the error twice. The code lines are added twice in the exception handler.

This part:

If chkBox2_Crtusr.Checked Then
If ex.Number.Equals(2627) Then 'Exception code for Primary Key Violation
MsgBox("Duplicate Employee ID cannot be inserted. Violation of Primary Key " & _
"Constraint Occured. Enter an unique value.Please check row number : 2", MsgBoxStyle.Critical, "Error")
Else
MsgBox(ex.ToString)
End If
If ex.Number.Equals(208) Then
MsgBox("Invalid Table Name. Check the Sql String.", MsgBoxStyle.Critical, "Error")
End If

鉴于您希望会发生很多异常，请不要使用if语句.
除了使代码难以处理之外，它还很丑陋且效率低下.

为了获得最佳结果，请将异常放入case块中，然后根据异常所需的条件调用proc或方法或其他内容.

Given that you wish to do so many exceptions, do not use the if statements.
Apart from making the code unfeasably longwinded, it is ugly and inefficient.

For best results put the exceptions in a case block, then call procs or methods or whatever, as per needed by the exception.

您好，感谢您的回复，实际上我正在尝试插入15种不同的用我的表单向数据库发送用户详细信息，现在是否可以使用上面的代码-它可以正常工作，但是它包含很多编码，还有其他方法，例如创建一些proc或其他方法来解决此问题，这是vb.net的新功能因此在这些小事情上苦苦挣扎.请帮忙，谢谢.

Hi thanks for replying, actually i am trying to insert 15 different user details to the DB with my form,now if i use the above code - it works fine, but it includes lots of coding, is there any other way like creating some proc or something else to solve this, i m new to vb.net hence struggling a lot on these small things. Please help, thanks is advanced.

这篇关于更有效地管理异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！