vb.net - 发生异常时显示警告消息
问题描述
我有一个网络服务,可以插入、删除、更新数据库中的数据。所以在这种特殊形式中,它有一个外键。所以基本上它应该只接受primarty表中存在的值。当用户输入任何其他值时,会发生异常。
如何确保在捕获此类 SQL 异常时,从我的 Web 服务传递用户友好的自定义错误消息(例如输入的 ID 不存在。数据插入失败!)。
非常感谢。
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()>
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")>
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)>
<ToolboxItem(False)>
Public Class WebService2
Inherits System.Web.Services.WebService
Public Class Details
Public Property CardID As Integer
Public Property BookID As Integer
Public Property IssuedBy As String
End Class
<WebMethod>
Public Sub AddBook(ByVal emp As Details)
Dim cs1 As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con1 As SqlConnection = New SqlConnection(cs1)
Dim thequery As String = "select * from BookIssue where CardID=@CardID"
Dim cmd1 As SqlCommand = New SqlCommand(thequery, con1)
cmd1.Parameters.Add(New SqlParameter() With {
.ParameterName = "@CardID",
.Value = emp.CardID
})
con1.Open()
Dim reader As SqlDataReader = cmd1.ExecuteReader()
If reader.HasRows Then
MsgBox("ID Number is Already in use")
con1.Close()
Else
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("spInsertIntoBookIssue", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@CardID",
.Value = emp.CardID
})
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@BookID",
.Value = emp.BookID
})
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@IssuedBy",
.Value = emp.IssuedBy
})
con.Open()
cmd.ExecuteNonQuery()
MsgBox("Data Inserted")
End Using
End If
End Using
End Sub
<WebMethod>
Public Sub GetAllDetails()
Dim listEmployees As List(Of Details) = New List(Of Details)()
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("Select * from BookIssue", con)
con.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
Dim details As Details = New Details()
details.CardID = Convert.ToInt32(rdr("CardID"))
details.BookID = Convert.ToInt32(rdr("BookID"))
details.IssuedBy = rdr("IssuedBy").ToString()
listEmployees.Add(details)
End While
End Using
Dim js As JavaScriptSerializer = New JavaScriptSerializer()
Context.Response.Write(js.Serialize(listEmployees))
End Sub
<WebMethod>
Public Sub GetAllDetails1()
Dim listEmployees As List(Of Details) = New List(Of Details)()
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("Select * from BookIssue", con)
con.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
Dim details As Details = New Details()
details.CardID = Convert.ToInt32(rdr("CardID"))
details.BookID = Convert.ToInt32(rdr("BookID"))
details.IssuedBy = rdr("IssuedBy").ToString()
listEmployees.Add(details)
End While
End Using
Dim js As JavaScriptSerializer = New JavaScriptSerializer()
Context.Response.Write(js.Serialize(listEmployees))
End Sub
<WebMethod>
Public Function GetdetailsById(ByVal CardID As Integer) As Details
Dim details As Details = New Details()
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("spGetBookIssueDetailsByCardID", con)
cmd.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = New SqlParameter()
parameter.ParameterName = "@CardID"
parameter.Value = CardID
cmd.Parameters.Add(parameter)
con.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
details.CardID = Convert.ToInt32(rdr("CardID"))
details.BookID = Convert.ToInt32(rdr("BookID"))
details.IssuedBy = rdr("IssuedBy").ToString()
End While
End Using
Return details
End Function
<WebMethod>
Public Function GetdetailsById1(ByVal CardID As Integer) As Details
Dim details As Details = New Details()
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("spGetBookIssueDetailsByCardID1", con)
cmd.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = New SqlParameter()
parameter.ParameterName = "@CardID"
parameter.Value = CardID
cmd.Parameters.Add(parameter)
con.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
details.CardID = Convert.ToInt32(rdr("CardID"))
details.BookID = Convert.ToInt32(rdr("BookID"))
details.IssuedBy = rdr("IssuedBy").ToString()
End While
End Using
Return details
End Function
<WebMethod>
Public Sub DeleteRecord1(ByVal emp As Details)
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("spDeleteByID", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@CardID",
.Value = emp.CardID
})
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
<WebMethod>
Public Sub Update(ByVal emp As Details)
Dim cs As String = ConfigurationManager.ConnectionStrings("library management systemConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(cs)
Dim cmd As SqlCommand = New SqlCommand("spUpdateBookIssue", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@CardID",
.Value = emp.CardID
})
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@BookID",
.Value = emp.BookID
})
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@IssuedBy",
.Value = emp.IssuedBy
})
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
End Class
解决方案
我建议根据此示例对您的代码进行建模,我修改了您提供的删除子程序,以包含一个带有自定义 ErrorRoutine 的捕获,用于未处理的情况,否则您可以从 executeNonQuery() 函数获取已删除行的数量。我也改变了,所以我们返回删除的行数,这样如果它小于 1,你可以在调用删除后对消息做任何你想做的事情。
Public Sub DeleteRecord1(ByVal emp As Details) As Integer
Dim cs As String = ConfigurationManager.ConnectionStrings("library management
systemConnectionString").ConnectionString
Dim m_RowsDeleted As Integer
Dim con As SqlConnection = New SqlConnection(cs)
Try
Using con
Dim cmd As SqlCommand = New SqlCommand("spDeleteByID", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter() With {
.ParameterName = "@CardID",
.Value = emp.CardID
})
con.Open()
m_RowsDeleted = cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
ErrorRoutine(ex, "Delete")
Finally
If Not IsNothing(con) Then
If con.State = ConnectionState.Open Then
con.Close()
End If
End If
End Try
Return m_RowsDeleted
End Sub
推荐阅读
- javascript - 在 nodejs 路由中导入模块
- javascript - 给定一个表示层次结构的数组,在JS中输出数据成树形
- node.js - Elasticsearch关键字+范围查询(V-6.2)
- javascript - Three.js - 禁用沿 x、y 和 z 轴的 3d 对象平移
- jquery - 循环内的大文件上的 jQuery load() 会使浏览器无响应。怎么修?
- python - Python - 将上一行和下一行绑定到当前行
- powershell - PowerShell ConvertFrom-Csv
- jquery - 使用 jquery 获取 span 的值
- java - NullPointerException 可能会抛出,因为此处的“值”可以为空声纳警告
- ffmpeg - 如何从具有可靠/可重现结果的视频文件中剥离元数据?