StrataFrame Forum » .NET Forums » General .NET Discussion » Getting a Return Value from a Stored Procedure

Getting a Return Value from a Stored Procedure

Terry Bottorff — Tue, 30 Nov 2010 15:13:45 GMT

I have the following Stored Proc (I am not sure it is correct). I just am trying to see if there are any duplicates in the table.

CREATE PROCEDURE [dbo].[sp_CountSRStockforDups]

-- Add the parameters for the stored procedure here

@myRowCount int = 0 OUTPUT

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select stock, count(stock) as pen from dbo.SRstocks where stock > 0 group by stock having (count(stock)) > 1

select @myRowCount = @@ROWCOUNT

return @myRowCount

END

I know I have 2 records that repeat but with the following code it returns zero(0).?

        Dim nret As Integer = 1000
                 Dim cmd As New SqlCommand("sp_CountSRStockforDups")
                cmd.CommandType = Data.CommandType.StoredProcedure
                ' OutPut Parameter
                cmd.Parameters.Add("@myRowCount", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output
                nret = CType(cmd.Parameters("@myRowCount").Value, Integer)

Please understand I am not sure any of this is correct except the attached picture seems to indicate that my stored proc is working but any help would be appreciated. TIA

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Tue, 30 Nov 2010 15:13:45 GMT

[quote][b]Gerard O Carroll (11/30/2010)[/b][hr]This might sound like a silly question but I 'Asumed' that a primary key field in SQL 'had' to be unique and you could not have duplicates ?. This must'nt be the case if you have written a routine to validate it ?? [/quote]

Hi Gerard,

Nothing silly about it. Even thought you may come across an Integer PK which is not auto incremented by the database, but you are right. That was a code I wrote fast without testing for Terry, but was corrected in the following messages.

RE: Getting a Return Value from a Stored Procedure

Ger Cannoll — Tue, 30 Nov 2010 15:05:15 GMT

Hi Edhy.

This might sound like a silly question but I 'Asumed' that a primary key field in SQL 'had' to be unique and you could not have duplicates ?. This must'nt be the case if you have written a routine to validate it ??

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 21:29:13 GMT

Yeap you are now in the right track, so go for it. Also for your current BO all you need to do is change the Inherits to your new base BO, the same thing applies to forms and other controls.

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Mon, 29 Nov 2010 20:50:07 GMT

So let me get this straight.

I create a BO (say BaseBO) and I don't map it to any table.
I then add MY Functions, Sub Routines and whatever to my BaseBO.
Once I have 1 and 2 done Then when I create a new BO I change the Inherits MicroFour.Strataframe.Business.BusinessLayer
to Inherits Rodeo.Business.BaseBO Where Rodeo is I believe my Namespace.

TIA.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 16:21:09 GMT

Hi Terry,

Yes, there are some sample code in the forums, probably Ivan (the Google expert) may provide some links, but basically this what you have to do:

Create a BO class and do not map it to any of your tables. You could name it whatever you want, I name mine "ApplicationBaseBusinessClass.vb"
Add all the custom, generic code you expect to use in all your BOs like the ones I posted here. Also if you use some custom field properties in all your BO with the same name, here is a good place to have them all.
In your current and new BO, change the "Inherits" statement from:

[quote]

     Inherits MicroFour.StrataFrame.Business.BusinessLayer
to
     Inherits CardTrackingSystem.Business.ApplicationBaseBusinessClass

[/quote]

Of course in the sample above the namespace "CardTrackingSystem" should be replace with your own NameSpace. After that, recompile your project and start having fun with your base BO class.

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Mon, 29 Nov 2010 15:53:52 GMT

Is there some where I can find a basic subclass of the BO that has Everything that is needed to make it work so I can start adding some of these functions and not have to do it on Every BO?

Thanks in Advance.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 14:51:30 GMT

[quote][b]Charles R Hankey (11/29/2010)[/b][hr](... reading that over it probably doesn't make any sense unless you've been playing with TVPs but for anybody who has, the TVP as empty table if no param is passed came as a surprise to me and somebody may find it useful )[/quote]
Hi Charles,

I have not worked with a TVP yet. I have a consultant "Uri Dimant" which is a MS SQL MVP which helps me with those deep needs and even though he is pretty good explaining this type of things with MS-SQL, sometimes I don't have the luxury to pay to much of attention to details. He has created some complex stored procedures to deal with millions of records and re-factored those couple of times to take advantage of new functionality of MS-SQL 2008 R2. In case you or anyone needs serious help with any MS-SQL problem, feel free to contact him.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 14:42:18 GMT

[quote][b]Terry Bottorff (11/29/2010)[/b][hr]...but now with All of the Great Ideas I will be looking to change almost all of my fill routines. [/quote]

Hi Terry,

I've been there, done that! Welcome to the club :hehe:

Glad you find it useful.

RE: Getting a Return Value from a Stored Procedure

Charles R Hankey — Mon, 29 Nov 2010 14:29:42 GMT

I think your idea of an array for multiple fields is very good, especially in keeping the function as generic as possible.

Just as an aside : I've been working a lot with using table value parameters in stored procedures and I'm really interested to find that unlike other type of params, you don't need to account for *not* passing the param - the TVP in the sproc will just be an empty table. Interesting in that you can write where clauses in the sproc that let you receive either a single or TVP param for comparison and it will work depending on what you pass in.

( reading that over it probably doesn't make any sense unless you've been playing with TVPs but for anybody who has, the TVP as empty table if no param is passed came as a surprise to me and somebody may find it useful )

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Mon, 29 Nov 2010 13:42:59 GMT

I did find the Primary Key problem and I was able to change that but now with All of the Great Ideas I will be looking to change almost all of my fill routines.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 11:42:07 GMT

[quote][b]Charles R Hankey (11/29/2010)[/b][hr]This looks good. Also, if you pass a comma separated list of fields to the fieldname param it will work fine for defining a dupe based on multiple fields [/quote]

Charles, I agree, but I would prefer a param array and then check if Column Exist for each one before executing the query. Also a 2nd optional parameter could be use to handle "WHERE conditions like in Terry's case.

So far, I did not have the need for such checking, but since I work on multiple projects at the same time, I like to have my code as generic as possible so it can be reused in all projects, even though I do not have a common assembly for all projects just yet :angry:

I am still learning this stuff which always amaze me with simple things and the .Net/SF approach to handle them. Pure and simple, I just love SF at the same level I loved VFP and my previous framework. It feels good being comfortable with a tool and I am at that level with StrataFrame now. (hmmm, Trent, that does not mean I am totally settle, I still want to see the new StrataListView completed with the automation stuff :rolleyes:)

RE: Getting a Return Value from a Stored Procedure

Charles R Hankey — Mon, 29 Nov 2010 11:28:32 GMT

This looks good. Also, if you pass a comma separated list of fields to the fieldname param it will work fine for defining a dupe based on multiple fields

Dim fieldlist as string = "field1, field2, field3"

then pass fieldlist as the param.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 11:10:26 GMT

Hi Charles,

I had to do the test this time, since it is not productive posting without testing, but sometimes, there is not much time to get everything right the first time :D so here is a generic statement that will check for duplicate for a single field, of course this could be enhanced to go deeper with multiple fields and "WHERE" conditions per needs like in Terry's case in which he needs a WHERE condition.
[quote]

    Public Function DuplicateRecordFound(ByVal FieldName As String) As Boolean
        If Not Me.CheckColumnExists(Me.CurrentDataTable, FieldName, GetType(String)) Then
            Exit Function
        End If
 
        '-- Create the SqlCommand with an Using to automatically dispose it.
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT COUNT(*) FROM {1} GROUP BY {0} HAVING COUNT(*) > 0", FieldName, Me.TableNameAndSchema)
 
            '-- Execute the query and return the value
            Return CType(Me.ExecuteScalar(cmd), Integer) > 1
        End Using
    End Function

[/quote]

Here is a sample on how to call the function in the BO. Of course if you have this function in your base BO it will be available to all of them and just need to pass the field name to check for duplicate:
[quote]

        If Me.BizCompany1.DuplicateRecordFound(bizCompany.bizCompanyFieldNames.CompanyName.ToString) Then
            MessageBox.Show("Duplicate found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("No duplicates found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
 
        Using bo As New bizCompany
            If bo.DuplicateRecordFound(bizCompany.bizCompanyFieldNames.CompanyName.ToString) Then
                MessageBox.Show("Duplicate found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                MessageBox.Show("No duplicates found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Using

[/quote]

Terry, notice that in above sample code I am not passing the field name as literal string "Field Name" or "CompanyName", instead I am taking advantage of the business object field name enumeration which will be named after your bo+FieldNames, this is used, so if you change a field name later on, at least Visual Studio will warn you at compilation giving you the chance to correct the field name before building your application.

Charles, thanks again, for looking into the code and your suggestions, greatly appreciated as always. Now back to work, hope this test pass :hehe:

RE: Getting a Return Value from a Stored Procedure

Charles R Hankey — Mon, 29 Nov 2010 10:52:26 GMT

  >> cmd.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", Me.PrimaryKeyField, Me.TableNameAndSchema)

I'd suggest :
cmd.CommandText = String.Format("SELECT COUNT({0}), {2}  FROM {1} GROUP BY {2} HAVING COUNT({0}) > 1", Me.PrimaryKeyField, Me.TableNameAndSchema, Fieldname)
where fieldnames is a single field or comma separated string of fields that defines a dupe


I was thrown by looking for dupes based on PK as the whole idea of a PK is that they be unique to begin with.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Mon, 29 Nov 2010 10:26:38 GMT

Hi Charles,

SET STUPID OFF (on my side :P)

Yes you are right, this one is looking for duplicates in the primary key field, but the function can be enhanced by passing the field name to be used in the SELECT statement, here is an untested sample code, please let us know if this may work for you?
[quote]

    ''' <summary>
    ''' This a generic function to find duplicate records in tables where the Primary Key is an integer
    ''' </summary>
    ''' <param name="FieldName">The field name used in the SQL to check for duplicate.</param>
    ''' <returns>True if duplicate records are found.</returns>
    ''' <remarks>This code should only work any field type.</remarks>
    Public Function DuplicateRecordFound(ByVal FieldName As String) As Boolean
        If Not Me.CheckColumnExists(Me.CurrentDataTable, FieldName, GetType(String)) Then
            Exit Function
        End If
 
        '-- Create the SqlCommand with an Using to automatically dispose it.
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", FieldName, Me.TableNameAndSchema)
 
            '-- Execute the query and return the value
            Return CType(Me.ExecuteScalar(cmd), Integer) > 0
        End Using
    End Function


    ''' <summary>
    ''' Ensures that the column specified exists within the table.
    ''' </summary>
    ''' <param name="table"></param>
    ''' <param name="columnName"></param>
    ''' <param name="columnType"></param>
    ''' <remarks></remarks>
    Public Function CheckColumnExists(ByVal table As DataTable, ByVal columnName As String, ByVal columnType As System.Type) As Boolean
        '-- Establish Locals
        Dim col As DataColumn
        Dim columnFound As Boolean = False
 
        '-- Cycle through all of the columns so we can test on a case-insensitive basis
        For Each col In table.Columns
            '-- Check to see if the column name matches
            columnFound = col.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase)
 
            '-- If the column was found, exit
            If columnFound Then Exit For
        Next
 
        '-- If the column is not found then throw and error letting the developer knows that the field name passed is wrong.
        CheckColumnExists = columnFound
        If Not columnFound Then
            Dim errMsg As String = String.Format("Make sure the field name [{0}] exist in this table {1}.", columnName, Me.TableNameAndSchema)
            Throw New Exception(errMsg)
        End If
    End Function

[/quote]

RE: Getting a Return Value from a Stored Procedure

Charles R Hankey — Mon, 29 Nov 2010 09:55:58 GMT

Hey Edhy -

It's Monday and I have SET STUPID ON but could you clarify the select statement? It looks like you are looking for records with duplicate primary keys?

I have a function like this checking dupes but I pass in the where that determines what a "match" is.

This looks like you are checking for duplicate primary keys? Isn't that prevented on the backend to start with ?

(or do I completely misunderstand what is going on ?)

Could you show an example of what the string.format would resolve to?

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Mon, 29 Nov 2010 06:26:53 GMT

Thank you so much. That makes sense.

Zowie this stuff is cool.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Sun, 28 Nov 2010 21:07:30 GMT

Hi Terry,

Sorry for the confusion, bizTransationItems is one of my BO. I simply paste one of my code as a sample. :w00t:

You must use your own BO class.

We are in a constant learning and since my switch from VFP a couple of years ago to StrataFrame and VB.NET you will not believe how many times I have refactored by code based on posting in these forums. :)

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Sun, 28 Nov 2010 20:56:44 GMT

Oh if my learning curve was not straight up enough. Now it has a curve backwards. Wow Thanks>

But on the statement: Using bo As New bizTransactionItems

I get the following error:

Type bizTransactionItems is not defined.

I did replace bo with my BO name?

I can not seem to find bizTransactionsItems in the Strataframe Help.

TIA.

RE: Getting a Return Value from a Stored Procedure

Trent L. Taylor — Sun, 28 Nov 2010 10:00:22 GMT

Terry, I agree with Edhy. It is never good Mojo to put your query logic in a button. You are better of encapsulating your query logic in the BO or a Singleton class you use to execute queries in this manner. By embedding your code in the button click and the logic of your application, you break encapsulation which is far more prone to downstream bugs.

RE: Getting a Return Value from a Stored Procedure

Edhy Rijo — Sun, 28 Nov 2010 09:34:21 GMT

Hi Terry,

Your approach is correct but I prefer to have all data validation code at the business level so it can be use from other places if you need to. So what I do is create a sub or function in the BO that will return the desired value, in your case you want to know if a duplicate record exist, so I would do this:

[quote]

    ''' <summary>
    ''' This a generic function to find duplicate records in tables where the Primary Key is an integer
    ''' </summary>
    ''' <returns>True if duplicate records are found.</returns>
    ''' <remarks>This code will only work for Integer PK, if using other type, simply change the CType in the return statement.</remarks>
    Public Function DuplicateRecordFound() As Boolean
        '-- Create the SqlCommand with an Using to automatically dispose it.
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", Me.PrimaryKeyField, Me.TableNameAndSchema)
 
            '-- Execute the query and return the value
            Return CType(Me.ExecuteScalar(cmd), Integer) > 0
        End Using
    End Function

[/quote]

Then if you need to use it from any code where you don't have an instance of the BO, you can do this:
[quote]

        Using bo As New bizTransactionItems
            If bo.DuplicateRecordFound = True Then
                ' Do whatever you want here.
            End If
        End Using

[/quote]

So if you are using a base BO, simply copy above function and it will work with any BO with an Integer Primary Key. Also noticed the use of "Using bo" in the sample calling code to properly dispose the bo instance and by the way, in this case, no need to create a Stored Procedure because the code is pretty simply and would not make any difference in speed or connection, and remember the idea is to have it generic so it can be used in any BO.

Have fun!!!

RE: Getting a Return Value from a Stored Procedure

Ivan George Borges — Sat, 27 Nov 2010 11:20:42 GMT

You're welcome. :cool:

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Sat, 27 Nov 2010 09:57:20 GMT

I think I found it.

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery(cmd)

Thanks for the help.

RE: Getting a Return Value from a Stored Procedure

Terry Bottorff — Sat, 27 Nov 2010 09:52:14 GMT

Actually I am running the code in the click of a button and so when I put

me.ExecuteNonQuery(cmd)

I get an error that says ExecuteNonQuery is not a member of Rodeo.CheckforDupsfrm

I know that it probably just in not the correct syntax because it is in the click of a button but I tried a couple of things and they did not work either.?