在N层应用程序中使用“如果存在" [英] Using 'If Exist' in a an N-Tier Application

问题描述

大家好，

我正在使用以下存储过程编写一个类来创建策略:

Hello all,

I am writing a class to create a policy with the stored procedure below:

CREATE PROCEDURE CreatePolicy   
    @CategoryID int,
    @PolicyTitle nvarchar(100),
    @PolicyContent varchar(1024),
    @retvalue varchar(100) output
   
    AS
   
    IF EXISTS(Select * FROM Policy WHERE CategoryID = @CategoryID)
   
    BEGIN
   
    Set @retvalue = 'Category Policy already exist'
    END
    ELSE
    BEGIN
   
    INSERT INTO Policy   
    (      
        CategoryID,
        PolicyTitle,
        PolicyContent       
    )      
    VALUES   
    (   
        @CategoryID,
        @PolicyTitle,
        @PolicyContent       
    )
    Set @retvalue = 'Record added'
   
    END

和下面的类:

And the class below:

Public Shared Function CreateAuditPolicy(ByVal title As String, ByVal content As String, _
                                                ByVal policycategory As Int32, ByVal retmsg As String) As Boolean

        Dim result As Boolean
        Using con As SqlConnection = New SqlConnection(My.Settings.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
                    .Connection.Open()

                End With
                Dim mytransaction As SqlTransaction = con.BeginTransaction()
                Try

                    cmd.Transaction = mytransaction
                    cmd.ExecuteNonQuery()
                    mytransaction.Commit()
                    result = True
                    retmsg = cmd.Parameters("@retvalue").SqlValue.ToString()

                Catch ex As Exception
                    mytransaction.Rollback()
                    result = False

                    Throw
                End Try
            End Using
        End Using

        Return result
    End Function

这就是我在业务层中调用类的方式:

Then here is how I called the class in business layer:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim msg As String = ""
        If AdmonAuditClass.CreateAuditPolicy(Me.txtTitle.Text.Trim(), Me.txtContent.Text.Trim(), Me.PolicyCategoryComboBox.SelectedValue, msg) Then
            '  Dim obj As AdmonAuditClass = New AdmonAuditClass()
            ' " Your data was successfullt saved"
            Me.txtContent.Text = ""
            Me.txtTitle.Text = ""
            AdmonUtilityClass.ShowErrorProvider(btnSave, True, msg, My.Resources.Information, ErrorIconAlignment.MiddleRight)

        Else
            AdmonUtilityClass.ShowErrorProvider(btnSave, True, msg, My.Resources.ErrorIcon, ErrorIconAlignment.MiddleRight)
        End If
    End Sub

我的问题是，它根本不会返回错误消息，但是当我在后面使用代码时(没有类)会返回错误消息.

有人请帮忙！

My problem is that it doesn''t return the error message at all but it does when I use code behind - without class.

Somebody please help!

推荐答案

您好，
据我所知，C reateAuditPolicy函数仅执行一个查询.因此没有必要使用事务.我也不认为该过程有必要返回任何东西.如果出现错误，Using指令应确保正确关闭连接并将错误传递给调用过程.所以这就是我要设置的方式:

Hi there
As far as I can tell the CreateAuditPolicy 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
                'No need of transaction for single query
                con.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

然后，在您的表示层中，保存按钮将使用Try，Catch End Try Block来捕获错误并通知用户:

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") 'If error occurs it will not reach here
            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

这篇关于在N层应用程序中使用“如果存在"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！