Results 1 to 37 of 37

Thread: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multiple places

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Resolved [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Ok - I'm using a simple SqlDataReader like this:

    Code:
    Using rsPrint As SqlDataReader = cmd.ExecuteReader
    
        ReDim strRS(0 To rsPrint.FieldCount - 1, 0 To 2)
    .
    .
    .
        If rsPrint.HasRows Then
            If rsPrint.GetName(0) = "%%errormessage%%" Then
                strErrorMessage = rsPrint(0)
    .
    .
    .
            End If
        End If
    Simple stuff - using for example: .FieldCount, .HasRows, .GetName and more important, the default method which just returns a field (rsPrint(0) near the bottom of that code snippet).

    What I'm looking to do is allow for this existing SQL access - calling that ExecuteReader at the top to work but also allow the "source" of the data to be a local array.

    I'm thinking I can "override" each method and property to either "call the underlying" SqlDataReader code, or "use" my own code that is managing the local array instead.

    I'm looking to not change any of the code in this Using Block - basically transparently source rsPrint from two different places.

    I know how to do this in JavaScript - and believe I've seen it done here before.

    I'm using VS 2012 unfortunately.

    TIA! Steve
    Last edited by szlamany; Feb 25th, 2021 at 05:05 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: What to hijack a SqlDataReader class so it can be sourced from multiple places

    Would you be willing to change it just a LITTLE bit? If you used the IDataReader interface rather than a DataReader directly, then you could make any class you want that implements the IDataReader interface, and pass it in.

    The IDataReader interface has some 34 items for you to implement, but since you are only using a few of them, you can leave the rest to their default implementation...maybe. I'm not sure what the default implementation is in VS2012, but it might be just an empty method. In 2019, the default implementation is this:
    Code:
    Throw New NotImplementedException()
    Which at least tells you when you try to use something you didn't implement.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: What to hijack a SqlDataReader class so it can be sourced from multiple places

    Seems IDataReader is missing .HasRows - oddly enough.

    Why can't I just hide the SqlDataReader in a class that I make has the methods and other stubs that I need?

    Way outside my wheelhouse here!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: What to hijack a SqlDataReader class so it can be sourced from multiple places

    Code:
        If Not rsPrint.HasRows Then
    .
    .
    .
            If rsPrint.HasRows Then
    I'm confused about this. If you reach "If rsPrint.HasRows" how could it ever be True?
    Last edited by wes4dbt; Feb 25th, 2021 at 04:31 PM.

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: What to hijack a SqlDataReader class so it can be sourced from multiple places

    Quote Originally Posted by wes4dbt View Post
    Code:
        If Not rsPrint.HasRows Then
    .
    .
    .
            If rsPrint.HasRows Then
    I'm confused about this. If you reach "If rsPrint.HasRows" how could it ever be True?
    The "..." indicates lots of code - I picked a bad spot to cut code out, lol!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    I've always thought HasRows was utterly useless, though I admit to having used it once or twice. Whenever I used it, I always asked myself, "self, why are you using this?" to which I answered, "I have no idea, but I'm doing it anyways."

    If you call .Read, you get a True or False. If you get False, then there are no rows left, whereas if you call True, then you have moved to that next row. The thing is, you can't do much of anything with a datareader until you DO move to that first row, so call .Read. If there are any rows, you move to the first row, which you'd have to do anyways, and if there are no rows, then you know that HasRows would also be false. So, it seems to me that HasRows is the same as .Read, except that it doesn't ALSO advance you to the next row.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Quote Originally Posted by Shaggy Hiker View Post
    I've always thought HasRows was utterly useless, though I admit to having used it once or twice. Whenever I used it, I always asked myself, "self, why are you using this?" to which I answered, "I have no idea, but I'm doing it anyways."
    I read a row, then save the row, then advance to the next. Then see if we still have rows... That's a standard "report writer" engine trick to pre-evaluate break logic conditions.

    Like I said in the OP, I'm looking to not TOUCH the inner code. It is old VB6 code ported to .Net - and it's huge. My client base would be really unhappy if I broke the report writer they rely on because I'm off on some crazy challenge to read data from an array instead of a SQL SPROC call.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Yeah since it was indented it looked like it was inside the first If statement. Good luck

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: What to hijack a SqlDataReader class so it can be sourced from multiple places

    Quote Originally Posted by Shaggy Hiker View Post
    Would you be willing to change it just a LITTLE bit? If you used the IDataReader interface rather than a DataReader directly, then you could make any class you want that implements the IDataReader interface, and pass it in.

    The IDataReader interface has some 34 items for you to implement, but since you are only using a few of them, you can leave the rest to their default implementation...maybe. I'm not sure what the default implementation is in VS2012, but it might be just an empty method. In 2019, the default implementation is this:
    Code:
    Throw New NotImplementedException()
    Which at least tells you when you try to use something you didn't implement.
    You don't need to implement IDataReader yourself. It's already implemented in the DbDataReader class, which is the base class for SqlDataReader and pretty much every other ADO.NET data reader. You can just inherit that class and then override as required. You can then use either DbDataReader or IDataReader as your type. Note that DbDataReader does have the HasRows property.

    Alternatively, use a DataTable as the alternative source instead of an array and then use a DataTableReader, which also inherits DbDataReader.

  10. #10

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    @jmc - thank you so much - I'll dig into DbDataReader tomorrow AM!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Quote Originally Posted by szlamany View Post
    @jmc - thank you so much - I'll dig into DbDataReader tomorrow AM!
    I just took a closer look at the documentation and it seems that many (most? all?) of the members are declared abstract. That means that you would still have to provide your own implementation when inheriting that class.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    But you'd only need to implement those pieces you are using, which sounds like a distinct minority of the methods.
    My usual boring signature: Nothing

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    It could be worthwhile checking out the source for the DataTableReader class to see how it uses a local data source. Reading from an array would be quite similar.

    https://referencesource.microsoft.co...TableReader.cs

  14. #14

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Ok - off to a slow start. Added a PUBLIC CLASS acsDataReader and much to my surprise when I typed INHERITS DbDataReader all of those METHODS and PROPERTIES appeared automatically. Nice job VS!

    But I do not see any NEW methods to start off the data reader - where are those?

    Code:
    Public Class acsDataReader
        Inherits DbDataReader
    
        Public Overrides Sub Close()
    
        End Sub
    
        Public Overrides ReadOnly Property Depth As Integer
            Get
    
            End Get
        End Property
    
        Public Overrides ReadOnly Property FieldCount As Integer
            Get
    
            End Get
        End Property
    
        Public Overrides Function GetBoolean(ordinal As Integer) As Boolean
    
        End Function
    
        Public Overrides Function GetByte(ordinal As Integer) As Byte
    
        End Function
    
        Public Overrides Function GetBytes(ordinal As Integer, dataOffset As Long, buffer() As Byte, bufferOffset As Integer, length As Integer) As Long
    
        End Function
    
        Public Overrides Function GetChar(ordinal As Integer) As Char
    
        End Function
    
        Public Overrides Function GetChars(ordinal As Integer, dataOffset As Long, buffer() As Char, bufferOffset As Integer, length As Integer) As Long
    
        End Function
    
        Public Overrides Function GetDataTypeName(ordinal As Integer) As String
    
        End Function
    
        Public Overrides Function GetDateTime(ordinal As Integer) As Date
    
        End Function
    
        Public Overrides Function GetDecimal(ordinal As Integer) As Decimal
    
        End Function
    
        Public Overrides Function GetDouble(ordinal As Integer) As Double
    
        End Function
    
        Public Overrides Function GetEnumerator() As IEnumerator
    
        End Function
    
        Public Overrides Function GetFieldType(ordinal As Integer) As Type
    
        End Function
    
        Public Overrides Function GetFloat(ordinal As Integer) As Single
    
        End Function
    
        Public Overrides Function GetGuid(ordinal As Integer) As Guid
    
        End Function
    
        Public Overrides Function GetInt16(ordinal As Integer) As Short
    
        End Function
    
        Public Overrides Function GetInt32(ordinal As Integer) As Integer
    
        End Function
    
        Public Overrides Function GetInt64(ordinal As Integer) As Long
    
        End Function
    
        Public Overrides Function GetName(ordinal As Integer) As String
    
        End Function
    
        Public Overrides Function GetOrdinal(name As String) As Integer
    
        End Function
    
        Public Overrides Function GetSchemaTable() As DataTable
    
        End Function
    
        Public Overrides Function GetString(ordinal As Integer) As String
    
        End Function
    
        Public Overrides Function GetValue(ordinal As Integer) As Object
    
        End Function
    
        Public Overrides Function GetValues(values() As Object) As Integer
    
        End Function
    
        Public Overrides ReadOnly Property HasRows As Boolean
            Get
    
            End Get
        End Property
    
        Public Overrides ReadOnly Property IsClosed As Boolean
            Get
    
            End Get
        End Property
    
        Public Overrides Function IsDBNull(ordinal As Integer) As Boolean
    
        End Function
    
        Default Public Overloads Overrides ReadOnly Property Item(ordinal As Integer) As Object
            Get
    
            End Get
        End Property
    
        Default Public Overloads Overrides ReadOnly Property Item(name As String) As Object
            Get
    
            End Get
        End Property
    
        Public Overrides Function NextResult() As Boolean
    
        End Function
    
        Public Overrides Function Read() As Boolean
    
        End Function
    
        Public Overrides ReadOnly Property RecordsAffected As Integer
            Get
    
            End Get
        End Property
    End Class

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    nvm - just needed to make a SUB NEW() of my own - moving along now!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Want to hijack a SqlDataReader class so it can be sourced from multiple places

    Shaggy and JMC - thank you both! This is coming along very nicely!

    Is just calling the SQLDataReader .CLOSE method enough to clean up or are there any other disposal requirements?

    My implementation has two SUB NEW() methods.

    One has just the SqlDataReader and the other is my local source - an IList(Of Dictionary(Of String, String)) - and some supporting info to consume this JSON array of info (shown at the bottom of this post).

    Code:
    Public Class acsDataReader
        Inherits DbDataReader
    
        Private _sdr As SqlDataReader = Nothing
    
        Private _doSdr As Boolean = False
    
        Private _style As String = ""
        Private _names As String = ""
        Private _columns As Integer = 0
        Private _start As Integer = 0
        Private _end As Integer = 0
        Private _source As IList(Of Dictionary(Of String, String))
    
        Sub New(sdr As SqlDataReader)
            _sdr = sdr
            _doSdr = True
        End Sub
    
        Sub New(strStyle As String, strNames As String, intColumns As Integer, intStart As Integer, intEnd As Integer _
                                 , ilSource As IList(Of Dictionary(Of String, String)))
            _style = strStyle
            _names = strNames
            _columns = intColumns
            _start = intStart
            _end = intEnd
            _source = ilSource
            _doSdr = False
        End Sub
    And my USING block has to start off a bit differently - allowing for both entry methods into the class.

    Code:
    Dim adr As acsDataReader = Nothing
    If _SetData Then
        adr = New acsDataReader(_DataStyle, _DataNames, _ColumnCount, _DataStart, _DataEnd, _DataSource)
    Else
        adr = New acsDataReader(cmd.ExecuteReader)
    End If
    
    Using rsPrint As acsDataReader = adr ' SqlDataReader = cmd.ExecuteReader
    
        ReDim strRS(0 To rsPrint.FieldCount - 1, 0 To 2)
        ReDim strLastRS(0 To rsPrint.FieldCount - 1, 0 To 2)
        ReDim curRSTotal(0 To lngRSTotal, 0 To rsPrint.FieldCount - 1, 0 To 2)
        ReDim curRSGTotal(0 To rsPrint.FieldCount - 1, 0 To 2)
    
        If Not rsPrint.HasRows Then
    And the methods I'm writing down in the acsDataReader class just have to handle two methods for processing.

    Code:
        Public Overrides ReadOnly Property FieldCount As Integer
            Get
                Dim intFC As Integer = 0
                If _doSdr Then
                    intFC = _sdr.FieldCount
                Else
                    intFC = _columns
                End If
                Return intFC
            End Get
        End Property
    Bit of syntax sugar needed to make this work - but that was the only obstacle!

    Below is the JavaScript code that is passing a JSON array of objects in an AJAX call to my web server!

    I copy/pasted this data from a stored procedure call in SQL Server Management Studio and put it into that JSON string. I'm expecting identical results - PDF outputs from the web server in this case - to as if I had called the stored procedure on the webserver and read that SqlDataReader as the code had in place originally!

    Happy day!
    Code:
    objExtra.offerdata = true
    objExtra.datastyle = "listkvps";
    objExtra.datanames = "ordinal0";
    objExtra.datacolumns = "10"
    arrFeed = [{'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-02', '5': 'E & G : CASH IN BANK : xxx', '6': '208851.91', '7': '185275.84', '8': '93681.67', '9': '117257.74'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-03', '5': 'E & G : CASH IN BANK : xxx', '6': '44983.94', '7': '23138.30', '8': '9706.77', '9': '31552.41'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-04', '5': 'E & G : CASH IN BANK : xxx', '6': '100473.61', '7': '78896.16', '8': '37011.58', '9': '58589.03'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1106-01', '5': 'E & G : CASH IN BANK - FIRST STATE : xxx', '6': '30925181.58', '7': '12997835.43', '8': '12774926.52', '9': '30702272.67'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1113-01', '5': 'E & G : AP CLEARING : xxx', '6': '269722.29', '7': '287535.82', '8': '165586.31', '9': '147772.78'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '10-0-00000-1106-01', '5': 'I & S : CASH IN BANK - FIRST STATE : xxx', '6': '298453.11', '7': '10838.92', '8': '183.24', '9': '287797.43'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '20-0-00000-1106-01', '5': 'PLANT : CASH IN BANK - FIRST STATE : xxx', '6': '1123694.45', '7': '208568.57', '8': '294643.13', '9': '1209769.01'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1106-01', '5': 'AUX : CASH IN BANK - FIRST STATE : xxx', '6': '-64466.23', '7': '1601387.89', '8': '2177416.50', '9': '511562.38'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-01', '5': 'AUX : SUSPENSE : xxx', '6': '-0.01', '7': '36.53', '8': '11.90', '9': '-24.64'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-04', '5': 'AUX : SUSPENSE : xxx', '6': '0.00', '7': '0.09', '8': '0.00', '9': '-0.09'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1106-01', '5': 'AGENCY  : CASH IN BANK - FIRST STATE : xxx', '6': '1312292.46', '7': '45065.76', '8': '132332.53', '9': '1399559.23'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1107-01', '5': 'AGENCY  : CASH IN BANK-BANK ONE (OTHER) : xxx', '6': '8041.37', '7': '539.09', '8': '0.00', '9': '7502.28'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '50-0-00000-1106-01', '5': 'SCHOL : CASH IN BANK - FIRST STATE : xxx', '6': '1653258.79', '7': '810356.57', '8': '533130.20', '9': '1376032.42'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '60-0-00000-1112-01', '5': 'LOAN : DIRECT LOANS : xxx', '6': '3079590.41', '7': '3079241.37', '8': '1903928.46', '9': '1904277.50'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1106-01', '5': 'OPP GRANT  : CASH IN BANK - FIRST STATE : xxx', '6': '5392619.21', '7': '5393119.21', '8': '740332.47', '9': '739832.47'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1109-01', '5': 'OPP GRANT  : STALE DATED CHECKS : xxx', '6': '-840.76', '7': '0.00', '8': '0.00', '9': '-840.76'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '80-0-00000-1106-01', '5': 'FEDERAL WORKSTUDY : CASH IN BANK - FIRST STATE : xxx', '6': '0.00', '7': '4522.00', '8': '19895.00', '9': '15373.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '90-0-00000-1106-01', '5': 'PAYROLL  : CASH IN BANK - FIRST STATE : xxx', '6': '777.77', '7': '5068751.04', '8': '5068383.59', '9': '410.32'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-01', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '2800.00', '7': '0.00', '8': '0.00', '9': '2800.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-02', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-04', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '100.00', '7': '0.00', '8': '0.00', '9': '100.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-01', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '6290.00', '7': '0.00', '8': '0.00', '9': '6290.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-02', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '600.00', '7': '0.00', '8': '0.00', '9': '600.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-04', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '400.00', '7': '0.00', '8': '0.00', '9': '400.00'}
        , {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-05', '5': 'AUX : PETTY CASH - CHANGE FUND : DO NOT USE', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}
    ]
    Last edited by szlamany; Feb 27th, 2021 at 08:48 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    don't know if you can use this, it will pass your JSON string to a Datatable

    Code:
    Imports System.Text.RegularExpressions
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim arrFeed As String = "[{'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-02', '5': 'E & G : CASH IN BANK : xxx', '6': '208851.91', '7': '185275.84', '8': '93681.67', '9': '117257.74'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-03', '5': 'E & G : CASH IN BANK : xxx', '6': '44983.94', '7': '23138.30', '8': '9706.77', '9': '31552.41'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-04', '5': 'E & G : CASH IN BANK : xxx', '6': '100473.61', '7': '78896.16', '8': '37011.58', '9': '58589.03'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1106-01', '5': 'E & G : CASH IN BANK - FIRST STATE : xxx', '6': '30925181.58', '7': '12997835.43', '8': '12774926.52', '9': '30702272.67'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1113-01', '5': 'E & G : AP CLEARING : xxx', '6': '269722.29', '7': '287535.82', '8': '165586.31', '9': '147772.78'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '10-0-00000-1106-01', '5': 'I & S : CASH IN BANK - FIRST STATE : xxx', '6': '298453.11', '7': '10838.92', '8': '183.24', '9': '287797.43'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '20-0-00000-1106-01', '5': 'PLANT : CASH IN BANK - FIRST STATE : xxx', '6': '1123694.45', '7': '208568.57', '8': '294643.13', '9': '1209769.01'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1106-01', '5': 'AUX : CASH IN BANK - FIRST STATE : xxx', '6': '-64466.23', '7': '1601387.89', '8': '2177416.50', '9': '511562.38'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-01', '5': 'AUX : SUSPENSE : xxx', '6': '-0.01', '7': '36.53', '8': '11.90', '9': '-24.64'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-04', '5': 'AUX : SUSPENSE : xxx', '6': '0.00', '7': '0.09', '8': '0.00', '9': '-0.09'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1106-01', '5': 'AGENCY  : CASH IN BANK - FIRST STATE : xxx', '6': '1312292.46', '7': '45065.76', '8': '132332.53', '9': '1399559.23'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1107-01', '5': 'AGENCY  : CASH IN BANK-BANK ONE (OTHER) : xxx', '6': '8041.37', '7': '539.09', '8': '0.00', '9': '7502.28'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '50-0-00000-1106-01', '5': 'SCHOL : CASH IN BANK - FIRST STATE : xxx', '6': '1653258.79', '7': '810356.57', '8': '533130.20', '9': '1376032.42'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '60-0-00000-1112-01', '5': 'LOAN : DIRECT LOANS : xxx', '6': '3079590.41', '7': '3079241.37', '8': '1903928.46', '9': '1904277.50'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1106-01', '5': 'OPP GRANT  : CASH IN BANK - FIRST STATE : xxx', '6': '5392619.21', '7': '5393119.21', '8': '740332.47', '9': '739832.47'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1109-01', '5': 'OPP GRANT  : STALE DATED CHECKS : xxx', '6': '-840.76', '7': '0.00', '8': '0.00', '9': '-840.76'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '80-0-00000-1106-01', '5': 'FEDERAL WORKSTUDY : CASH IN BANK - FIRST STATE : xxx', '6': '0.00', '7': '4522.00', '8': '19895.00', '9': '15373.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '90-0-00000-1106-01', '5': 'PAYROLL  : CASH IN BANK - FIRST STATE : xxx', '6': '777.77', '7': '5068751.04', '8': '5068383.59', '9': '410.32'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-01', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '2800.00', '7': '0.00', '8': '0.00', '9': '2800.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-02', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-04', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '100.00', '7': '0.00', '8': '0.00', '9': '100.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-01', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '6290.00', '7': '0.00', '8': '0.00', '9': '6290.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-02', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '600.00', '7': '0.00', '8': '0.00', '9': '600.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-04', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '400.00', '7': '0.00', '8': '0.00', '9': '400.00'}"
            arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-05', '5': 'AUX : PETTY CASH - CHANGE FUND : DO NOT USE', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}"
            arrFeed &= "]"
    
            DataGridView1.DataSource = JsonToDataTable(arrFeed)
        End Sub
    
        Public Function JsonToDataTable(ByVal strJson As String) As DataTable
            Try
                strJson = strJson.Substring(strJson.IndexOf("[") + 1)
                strJson = strJson.Substring(0, strJson.IndexOf("]"))
                Dim rg As Regex = New Regex("(?<={)[^}]+(?=})")
                Dim mc As MatchCollection = rg.Matches(strJson)
                Dim tb As DataTable = Nothing
    
                For i As Integer = 0 To mc.Count - 1
                    Dim strRow As String = mc(i).Value
                    Dim strRows As String() = strRow.Split(","c)
                    If tb Is Nothing Then
                        tb = New DataTable With {.TableName = ""}
                        For Each str As String In strRows
                            Dim dc As DataColumn = New DataColumn()
                            Dim strCell As String() = str.Split(":"c)
                            dc.ColumnName = strCell(0).ToString().Replace("""", "").Replace("'", "").Trim()
                            tb.Columns.Add(dc)
                        Next
                        tb.AcceptChanges()
                    End If
                    Dim dr As DataRow = tb.NewRow()
                    For r As Integer = 0 To strRows.Length - 1
                        dr(r) = strRows(r).Split(":"c)(1).Trim().Replace(",", ",").Replace(":", ":").Replace("/", "").Replace("""", "").Replace("'", "").Trim()
                    Next
                    tb.Rows.Add(dr)
                    tb.AcceptChanges()
                Next
                Return tb
            Catch ex As Exception
                MsgBox(ex.Message & vbNewLine, 48, "JsonToDataTable")
            Finally
            End Try
        End Function
    End Class
    but why do you have to use a Json when you extract the Data like you say...
    I copy/pasted this data from a stored procedure call in SQL Server Management Studio and put it into that JSON string.
    can't you use the Data from the Database direct with the DataReader?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #18
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    I haven't tried that, but I did try the IDataReader, and I believe it automatically includes IDisposable. If your class doesn't implement IDisposable, it probably should, if only to call the base Dispose method. Whether or not that does something significant, I can't say.
    My usual boring signature: Nothing

  19. #19

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by ChrisE View Post
    but why do you have to use a Json when you extract the Data like you say...

    can't you use the Data from the Database direct with the DataReader?
    About to market my report writer as an API call. I'll support lots of different data transmission methods. I've got a "mark up" language that goes along with it, for Crystal Reports-like formatting.

    I've used it for my own business for decades - started in VB6, now ported to .Net running in IIS. I've been reading the "report layout specs" and "data" from SQL all this time. Now I'm exposing the "layout" and "data" to external submission.

    Returns a PDF when done...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,041

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by szlamany View Post
    About to market my report writer as an API call. I'll support lots of different data transmission methods. I've got a "mark up" language that goes along with it, for Crystal Reports-like formatting.

    I've used it for my own business for decades - started in VB6, now ported to .Net running in IIS. I've been reading the "report layout specs" and "data" from SQL all this time. Now I'm exposing the "layout" and "data" to external submission.

    Returns a PDF when done...
    ah, got it
    sounds good, and "light weight"
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  21. #21

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by Shaggy Hiker View Post
    I haven't tried that, but I did try the IDataReader, and I believe it automatically includes IDisposable. If your class doesn't implement IDisposable, it probably should, if only to call the base Dispose method. Whether or not that does something significant, I can't say.
    Shaggy - I know I marked this resolved - but quick question...

    It made me use OVERLOADS because the base class had a Dispose() method. What would the base method know about what to dispose anyway? Am I supposed to call the base class dispose as well as the stuff I put in the Dispose() I wrote here?

    Code:
        Public Overloads Sub Dispose() ' Implements IDisposable.Dispose
            If _sdr IsNot Nothing Then
                Try
                    _sdr.Close()
                Catch ex As Exception
    
                End Try
                Try
                    _sdr.Dispose()
                Catch ex As Exception
    
                End Try
            End If
            If _source IsNot Nothing Then
                _source = Nothing
            End If
        End Sub

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    I've gone back and forth on this one. In general, calling MyBase.Dispose seems like it would be safest, but I'm not sure what it would do. For example, had you just implemented the IDataReader and IDisposable interfaces on some class that you had, then there would BE no base class, and there would be nothing to dispose aside from anything that you wanted to dispose. In this case, since you inherited from the base class, then there is SOMETHING there, but it seems quite unlikely that what is there does anything meaningful such that it has something to dispose. After all, the derived class you have created is where all the work is happening, and there's really nothing in the base class that matters to you aside from its structure itself.

    I don't have a means to see what is in the base class. JMC said that pretty nearly everything was abstract, so the base class may have no body at all, as it may serve as nothing more than a set of properties and methods with no implementation of their own. So, it seems like you would be safe not disposing the base class, but I can't be totally certain.
    My usual boring signature: Nothing

  23. #23
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    You don't overload the Dispose method. You override the Dispose(Boolean) method. Here's a basic disposable class:
    vb.net Code:
    1. Public Class Class1
    2.     Implements IDisposable
    3.  
    4.     Private disposedValue As Boolean
    5.  
    6.     Protected Overridable Sub Dispose(disposing As Boolean)
    7.         If Not disposedValue Then
    8.             If disposing Then
    9.                 ' TODO: dispose managed state (managed objects)
    10.             End If
    11.  
    12.             ' TODO: free unmanaged resources (unmanaged objects) and override finalizer
    13.             ' TODO: set large fields to null
    14.             disposedValue = True
    15.         End If
    16.     End Sub
    17.  
    18.     ' ' TODO: override finalizer only if 'Dispose(disposing As Boolean)' has code to free unmanaged resources
    19.     ' Protected Overrides Sub Finalize()
    20.     '     ' Do not change this code. Put cleanup code in 'Dispose(disposing As Boolean)' method
    21.     '     Dispose(disposing:=False)
    22.     '     MyBase.Finalize()
    23.     ' End Sub
    24.  
    25.     Public Sub Dispose() Implements IDisposable.Dispose
    26.         ' Do not change this code. Put cleanup code in 'Dispose(disposing As Boolean)' method
    27.         Dispose(disposing:=True)
    28.         GC.SuppressFinalize(Me)
    29.     End Sub
    30.  
    31. End Class
    That code was all added by VS, once the Implements line was added. Note the comment in the Dispose method:
    Do not change this code. Put cleanup code in 'Dispose(disposing As Boolean)' method
    You should be overriding the Dispose(Boolean) method, putting your cleanup code there and then calling the base implementation. When the consuming code calls Dispose, it's actually calling the method inherited from the base class. That method then calls the overriding Dispose(Boolean) method in your class, which will do your cleanup and then pass off to the base class to do its cleanup, if any.

    I would suggest ALWAYS calling the base method. If nothing else, you will be safe if you ever change the base class to something else that does have code in its Dispose(Boolean) method.

  24. #24
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Actually, my previous post is true in general but not quite in this case. Here's the IDisposable implementation from the DbDataReader class, taken from the source code I linked to earlier:
    Code:
            public void Dispose() {
                Dispose(true);
            }
        
            protected virtual void Dispose(bool disposing) {
                if (disposing) {
                    Close();
                }
            }
    So, Dispose calls Dispose(Boolean) and Dispose(Boolean) calls Close. Here's that Close method:
    Code:
            virtual public void Close()
            {
            }
    So, Close is an Overridable method that does nothing by default. That means that you should be overriding the Close method and putting your cleanup code there. While it won't actually make a difference whether you call the base method or not, I recommend that you do, for consistency and for the very small chance of avoiding an issue if you change the base class later. When all that is done, everything will work when using your class whether the consuming code calls Close, calls Dispose explicitly or implicitly (via Using) or the object is finalised by the system.

  25. #25

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    @jmc - thanks - that makes a whole lot more sense. I was fighting to OVERLOAD the DISPOSE method, when the base DISPOSE method was setup to call my OVERRIDES CLOSE method.

    But to clarify - in this case I would NOT want to then call MyBase.Dispose() from my OVERRIDES CLOSE method, because that would be calling the caller that called me - right?

    Isn't my OVERRIDES CLOSE method being called by the Base DISPOSE method?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  26. #26
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by szlamany View Post
    But to clarify - in this case I would NOT want to then call MyBase.Dispose() from my OVERRIDES CLOSE method, because that would be calling the caller that called me - right?
    That's correct. The work gets done in the Close method. If the code calls Close then that's it. If the code calls Dispose, as would be done with a Using block, then Dispose will call Dispose(Boolean) and that will call Close.
    Quote Originally Posted by szlamany View Post
    Isn't my OVERRIDES CLOSE method being called by the Base DISPOSE method?
    Yes, the overriding method in your derived class is what will be executed, which is why that would need to call the base method if it contained and code.

  27. #27

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    This article is very clear

    https://docs.microsoft.com/en-us/dot...enting-dispose

    and I am following the section titled "Implement the dispose pattern for a derived class"

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  28. #28
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by szlamany View Post
    I am following the section titled "Implement the dispose pattern for a derived class"
    That would be the wrong thing to do. If you do that then calling Close will not actually do anything useful. As we have already established, calling Dispose invokes the Close method but not vice versa, so it is the Close method you need to override and put your code in. The actual code you put in the Close method would do the same thing but it should be in Close, not in Dispose(Boolean).

  29. #29

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by jmcilhinney View Post
    That would be the wrong thing to do...
    And testing here has proven that out to be quite true!

    All I need is just to OVERRIDE the CLOSE() - as shown below. MyBase.Dispose() or MyBase.Dispose(true) were both tried (at the bottom - commented out) - they both called my Close() in a loop - not good.
    Code:
        Public Overrides Sub Close()
            If _doSdr Then
                Try
                    _sdr.Close()
                Catch ex As Exception
    
                End Try
                Try
                    _sdr.Dispose()
                Catch ex As Exception
    
                End Try
            Else
                If _source IsNot Nothing Then
                    _source = Nothing
                End If
            End If
            ' MyBase.Dispose()        *** This does not work - it calls the CLOSE in a loop!
            ' MyBase.Dispose(true)   *** This does not work - it calls the CLOSE in a loop!
        End Sub
    I also tried this - from the that article I linked to - and it's not good either. So I guess the pattern I'm using is not referred to in that link at all.

    Code:
        Protected Overrides Sub Dispose(disposing As Boolean)
            If _disposed Then Return
    
            If disposing Then
                Close()
            End If
    
            _disposed = True
    
            MyBase.Dispose(disposing)
        End Sub
    Bottom line - just handle the CLOSE() and all is well.
    Last edited by szlamany; Mar 2nd, 2021 at 09:28 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  30. #30
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Your Close method should not be calling the base Dispose method. It should be calling the base Close method. Generally speaking, you can override a method to replace it or extend it. In the former case you would not call the base method and in the latter case you would. It's academic in this case because, even though you're extending the base Close method, it's actually empty. Let's say that, in future, you change your class to inherit some other class that inherits DbDataReader instead of inheriting it directly yourself. What if that class has some code in its Close method? Your Close method needs to call the base Close method in order for that code to be executed.

  31. #31

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    There is no BASE CLOSE method - I tried that in testing as well - MyBase.Close() does not exist to call...

    And I did not call the DISPOSE from CLOSE - I was just testing test to see what it would do - and it did what I would expect (which was call CLOSE multiple times).

    I think I'm all set here.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  32. #32
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by szlamany View Post
    There is no BASE CLOSE method
    That's not true. The code in post #24 was copied directly from the source code for the DbDataReader class. How could you override a method in the first place if it didn't exist in the base class? Here's a class that I just created that compiles just fine. The relevant part is at the very bottom. All I did was start typing "Overrides" and then selected the Close method from the Intellisense list and the rest was added automatically. Not sure whether that was courtesy of VS or ReSharper but the point is that it's there and it compiles.
    vb.net Code:
    1. Imports System.Data.Common
    2.  
    3. Public Class CustomDataReader
    4.     Inherits DbDataReader
    5.  
    6.     Public Overrides ReadOnly Property Depth As Integer
    7.     Public Overrides ReadOnly Property FieldCount As Integer
    8.     Public Overrides ReadOnly Property HasRows As Boolean
    9.     Public Overrides ReadOnly Property IsClosed As Boolean
    10.     Public Overrides ReadOnly Property RecordsAffected As Integer
    11.  
    12.     Default Public Overrides ReadOnly Property Item(ordinal As Integer) As Object
    13.         Get
    14.         End Get
    15.     End Property
    16.  
    17.     Default Public Overrides ReadOnly Property Item(name As String) As Object
    18.         Get
    19.         End Get
    20.     End Property
    21.  
    22.     Public Overrides Function GetDataTypeName(ordinal As Integer) As String
    23.         Throw New NotImplementedException()
    24.     End Function
    25.  
    26.     Public Overrides Function GetEnumerator() As IEnumerator
    27.         Throw New NotImplementedException()
    28.     End Function
    29.  
    30.     Public Overrides Function GetFieldType(ordinal As Integer) As Type
    31.         Throw New NotImplementedException()
    32.     End Function
    33.  
    34.     Public Overrides Function GetName(ordinal As Integer) As String
    35.         Throw New NotImplementedException()
    36.     End Function
    37.  
    38.     Public Overrides Function GetOrdinal(name As String) As Integer
    39.         Throw New NotImplementedException()
    40.     End Function
    41.  
    42.     Public Overrides Function GetBoolean(ordinal As Integer) As Boolean
    43.         Throw New NotImplementedException()
    44.     End Function
    45.  
    46.     Public Overrides Function GetByte(ordinal As Integer) As Byte
    47.         Throw New NotImplementedException()
    48.     End Function
    49.  
    50.     Public Overrides Function GetBytes(ordinal As Integer, dataOffset As Long, buffer() As Byte, bufferOffset As Integer, length As Integer) As Long
    51.         Throw New NotImplementedException()
    52.     End Function
    53.  
    54.     Public Overrides Function GetChar(ordinal As Integer) As Char
    55.         Throw New NotImplementedException()
    56.     End Function
    57.  
    58.     Public Overrides Function GetChars(ordinal As Integer, dataOffset As Long, buffer() As Char, bufferOffset As Integer, length As Integer) As Long
    59.         Throw New NotImplementedException()
    60.     End Function
    61.  
    62.     Public Overrides Function GetDateTime(ordinal As Integer) As Date
    63.         Throw New NotImplementedException()
    64.     End Function
    65.  
    66.     Public Overrides Function GetDecimal(ordinal As Integer) As Decimal
    67.         Throw New NotImplementedException()
    68.     End Function
    69.  
    70.     Public Overrides Function GetDouble(ordinal As Integer) As Double
    71.         Throw New NotImplementedException()
    72.     End Function
    73.  
    74.     Public Overrides Function GetFloat(ordinal As Integer) As Single
    75.         Throw New NotImplementedException()
    76.     End Function
    77.  
    78.     Public Overrides Function GetGuid(ordinal As Integer) As Guid
    79.         Throw New NotImplementedException()
    80.     End Function
    81.  
    82.     Public Overrides Function GetInt16(ordinal As Integer) As Short
    83.         Throw New NotImplementedException()
    84.     End Function
    85.  
    86.     Public Overrides Function GetInt32(ordinal As Integer) As Integer
    87.         Throw New NotImplementedException()
    88.     End Function
    89.  
    90.     Public Overrides Function GetInt64(ordinal As Integer) As Long
    91.         Throw New NotImplementedException()
    92.     End Function
    93.  
    94.     Public Overrides Function GetString(ordinal As Integer) As String
    95.         Throw New NotImplementedException()
    96.     End Function
    97.  
    98.     Public Overrides Function GetValue(ordinal As Integer) As Object
    99.         Throw New NotImplementedException()
    100.     End Function
    101.  
    102.     Public Overrides Function GetValues(values() As Object) As Integer
    103.         Throw New NotImplementedException()
    104.     End Function
    105.  
    106.     Public Overrides Function IsDBNull(ordinal As Integer) As Boolean
    107.         Throw New NotImplementedException()
    108.     End Function
    109.  
    110.     Public Overrides Function NextResult() As Boolean
    111.         Throw New NotImplementedException()
    112.     End Function
    113.  
    114.     Public Overrides Function Read() As Boolean
    115.         Throw New NotImplementedException()
    116.     End Function
    117.  
    118.     ''' <inheritdoc />
    119.     Public Overrides Sub Close()
    120.         MyBase.Close()
    121.     End Sub
    122.  
    123. End Class

  33. #33

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    With MyBase.,Close() compile gets this error - did I screw up earlier and hurt this code

    Code:
    Error	149	'MyBase' cannot be used with method 'Public MustOverride Sub Close()' because it is declared 'MustOverride'.	C:\Users\szlam_000\OneDrive\ACS\AWC\ACSReport\acsReportWriter.vb	3643	9	C:\...\AWC\
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  34. #34
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by szlamany View Post
    With MyBase.,Close() compile gets this error - did I screw up earlier and hurt this code

    Code:
    Error	149	'MyBase' cannot be used with method 'Public MustOverride Sub Close()' because it is declared 'MustOverride'.	C:\Users\szlam_000\OneDrive\ACS\AWC\ACSReport\acsReportWriter.vb	3643	9	C:\...\AWC\
    Hmmm... something funny going on there. That error message suggests that the DbDataReader.Close method is declared abstract (the C# equivalent of MustInherit) but, as you can see from the code in post #24, that method is actually declared virtual (the C# equivalent of Overridable). I'm not sure whether that declaration has changed at some point. What version of .NET are you targeting?

  35. #35
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by jmcilhinney View Post
    Hmmm... something funny going on there. That error message suggests that the DbDataReader.Close method is declared abstract (the C# equivalent of MustInherit) but, as you can see from the code in post #24, that method is actually declared virtual (the C# equivalent of Overridable). I'm not sure whether that declaration has changed at some point. What version of .NET are you targeting?
    I just checked the documentation and, in fact, the declaration was changed in .NET Framework 4.6. It was introduced to .NET Core in version 2.0 and it was virtual there, so maybe they decided to fix what they considered a mistake from the get-go in .NET Core and made the change in .NET Framework too. So, in .NET Framework 4.6 or later you could call the base method but it contains no code anyway, but you can't call it at all in earlier versions. Mystery solved.

  36. #36

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Quote Originally Posted by jmcilhinney View Post
    ...What version of .NET are you targeting?
    Quote Originally Posted by szlamany View Post
    I'm using VS 2012 unfortunately.
    Yes - using older VS and sticking with older versions of .Net. I keep all my clients running the exact same backend code base and some of the web servers they are using can be quite old and sometimes quite difficult for me to get access to.

    I really appreciate all your assistance - thank you very much!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  37. #37
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Want to hijack a SqlDataReader class so it can be sourced from multipl

    Pondering the change in declaration of the Close method, my guess would be that they made the change so that you aren't forced to implement the Close method if you don't actually need to close anything. I can imagine a data reader that cached the data internally and had no need to perform any action when closing, so overriding the Close method would serve no purpose. I can't think of any other reason that they would have changed it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width