Using 'If Exist' in a an N-Tier Application

Question

4.00/5 (1 vote)

See more:

Hello all,

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

SQL

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:

VB

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:

SQL

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!

Posted 10-Jun-10 4:02am

Adesina Mark

Updated 10-Jun-10 6:52am

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 10-Jun-10 12:53pm

Pre tags are your friend, use it for formatting code next time you post question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Answer 1 · 2010-06-10T22:46:00

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 :

VB.NET

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

Then in your presentation layer your button Save would use a Try,Catch End Try Block to catch the error and inform the user: