[RESOLVED] SHared Data Access Method

[RunsWithScissors]

Hi all -

Does anyone have an opinion (or even better, FACTS) as to whether the following use of a Shared Function is a good or bad practice for retrieving data into an app?

To be clear, my question is with regard to the use of the shared function, which returns a reference to a datatable to the calling code. While there a probably many debates as to the preferred use of ADO.NET objects, my question is about the Shared Method aspect. Note that the dataTable is declared within the Shared Function, so it seems to me you get a new one every time, yes?

Is there a downside to this I am missing?

    Public Shared Function MyData() As DataTable

        Dim ReturnData As New DataTable

        'A silly example of retreiving some data into a datatable:
        Using cn As New SqlClient.SqlConnection("SomeCOnnectionString")
            Using cmd As New SqlClient.SqlCommand("SomeSQLString", cn)
                'May or may not need params. This is just an example . . .
                cn.Open()
                Dim dr As SqlClient.SqlDataReader
                dr = cmd.ExecuteReader
                ReturnData.Load(dr)
            End Using
        End Using

        'Now return THIS as the Function output:
        Return ReturnData

    End Function

[chris128]

The only possible issue I can see with that is that if you have other methods that are going to be getting data from the database as well then you might want them to all share the same SqlConnection object rather than adding the performance hit of creating a new one every time every method is called. Of course that doesnt mean you can't have a Shared method, it just means you would declare a Shared SqlConnection object at class level.

Note that the dataTable is declared within the Shared Function, so it seems to me you get a new one every time, yes?

Yep, but that has nothing to do with the method being Shared, that would be the same if the method was an instance method.

I am quite keen on Shared methods myself - I dont see the point in having to declare an instance of a class just to use methods from it when they are just 'one off' methods like grabbing some data from a database. I use non-shared methods when the class that they are in actually represents something and the method does something based on (or needs to modify) other members of that instance of the class.

[RunsWithScissors]

I am quite keen on Shared methods myself - I dont see the point in having to declare an instance of a class just to use methods from it when they are just 'one off' methods like grabbing some data from a database. I use non-shared methods when the class that they are in actually represents something and the method does something based on (or needs to modify) other members of that instance of the class.

I quite agree - that was my objective! Good to know I am not totally off-base here.

The only possible issue I can see with that is that if you have other methods that are going to be getting data from the database as well then you might want them to all share the same SqlConnection object rather than adding the performance hit of creating a new one every time every method is called. Of course that doesnt mean you can't have a Shared method, it just means you would declare a Shared SqlConnection object at class level.

Yeah, I think about that when I do this. In cases where the amount of connection overhead might be significant, I tend to implement things diferently.

Thanks for the feedback!

[Shaggy Hiker]

Your connections are being pooled anyways, so the overhead may not be quite as high as you might think. .NET appears to manage connections pretty well.

[RunsWithScissors]

Thanks to all for the feedback. Rep to both, and it is good to know I may have actually learned a few things over this past year!