Results 1 to 6 of 6

Thread: adSchemaStatistics in .Net ?

  1. #1

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    adSchemaStatistics in .Net ?

    Hi,

    I'm looking to convert some VB6 Code to .Net but can't seem
    to find a solution.

    either somthing like "adSchemaStatistics" is not avalible in .Net or
    I can't find it.

    here the VB6 Code, and perhaps sombody can clarify what the .Net version of this is.


    this will return a Recordcount of each Table with DateCreated/Modified
    Code:
    Private Sub Command2_Click()
      Dim RsL As ADODB.Recordset
      Dim RsT As ADODB.Recordset
      Dim Msg As String
      Dim TblName As String
      Dim i As Long
       
          If cn.State = adStateClosed Then
             Msg = "zuerst Connection herstellen "
             MsgBox Msg, vbCritical, "Statistik Records"
             Exit Sub
          End If
          Set RsL = New ADODB.Recordset
          Set RsL = cn.OpenSchema(adSchemaStatistics)
          
          Set RsT = New ADODB.Recordset
          Set RsT = cn.OpenSchema(adSchemaTables)
          
          With FlexInfo
             .Redraw = False
             .Rows = 1
             .Cols = 4
             .FixedCols = 0
             .ColAlignment(0) = flexAlignLeftCenter
             .ColAlignment(1) = flexAlignRightCenter
             .TextMatrix(0, 0) = "Table"
             .TextMatrix(0, 1) = "Records"
             .TextMatrix(0, 2) = "Modified"
             .TextMatrix(0, 3) = "Created"
             .ColWidth(0) = 1800
             .ColWidth(1) = 840
             .ColWidth(2) = 1590
             .ColWidth(3) = 1590
             .AllowUserResizing = flexResizeColumns
             .FocusRect = flexFocusNone
             Do While Not RsL.EOF
                .AddItem ""
                i = .Rows - 1
                TblName = RsL.Fields("Table_Name").Value
                .TextMatrix(i, 0) = TblName
                .TextMatrix(i, 1) = Format(RsL.Fields("Cardinality").Value, "#,###") 'Recordcount
                
                RsT.Find "Table_Name = '" & TblName & "'"
                If Not RsT.EOF Then
                   .TextMatrix(i, 2) = Format(RsT.Fields("Date_Modified").Value, "dd.mm.yyyy hh:mm:ss")
                   .TextMatrix(i, 3) = Format(RsT.Fields("Date_Created").Value, "dd.mm.yyyy hh:mm:ss")
                End If
                RsL.MoveNext
             Loop
             .Redraw = True
          End With
          RsL.Close
          RsT.Close
          Set RsL = Nothing
          Set RsT = Nothing
    End Sub

    regards
    Chris
    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: adSchemaStatistics in .Net ?

    You can use something like this to get a DataTable with the same kind of information:
    Code:
    Dim MySchemaTable as DataTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                     New Object() {Nothing, Nothing, Nothing, "TABLE"})
    more info (and full examples) are available here:
    https://support.microsoft.com/en-us/...matable-and-vi

  3. #3

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: adSchemaStatistics in .Net ?

    Hi si,

    thanks for your reply, I went threw(your Link) that but it dosn't return the Information like in the VB6 sample.

    I set a ref. to ADO..
    Code:
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
    
            Dim cn As ADODB.Connection
            Dim RsL As ADODB.Recordset
    
            Dim strPathToDB As String
            Dim i As Long
            Dim TblName As String
    
            cn = New ADODB.Connection
            strPathToDB = "D:\Northwind.mdb"
            With cn
                '.CursorLocation = adUseClient
                '.Mode = adModeShareDenyNone
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .ConnectionString = strPathToDB
                .Open()
            End With
    
            RsL = New ADODB.Recordset
            RsL = cn.OpenSchema(ADODB.SchemaEnum.adSchemaStatistics)
            Do While Not RsL.EOF
                TblName = RsL.Fields("Table_Name").Value
                ListBox1.Items.Add(TblName & " - " & Format(RsL.Fields("Cardinality").Value, "#,###"))
                RsL.MoveNext()
            Loop
        End Sub


    regards
    Chris
    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.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: adSchemaStatistics in .Net ?

    I'm not in a position to run it at the moment, but I'm guessing based on your examples that the "number of records" isn't returned by GetOleDbSchemaTable.

    A look thru the options doesn't look promising (and neither did a couple of other potential methods), so using the ADODB version is probably the best way to go.

  5. #5

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: adSchemaStatistics in .Net ?

    Quote Originally Posted by si_the_geek View Post
    I'm not in a position to run it at the moment, but I'm guessing based on your examples that the "number of records" isn't returned by GetOleDbSchemaTable.

    A look thru the options doesn't look promising (and neither did a couple of other potential methods), so using the ADODB version is probably the best way to go.
    yep, sure looks that way to stick to ADODB. Ther Class from VB6 that I want(ed) to convert has got quit a few
    routines.

    only M$ will know why OLEDB missed/forgot the possibilities that ADODB can provide.

    regards
    Chris
    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.

  6. #6

    Thread Starter
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: adSchemaStatistics in .Net ?

    Hi,

    just incase somebody is looking for a solution for the "Cardinality" value of Database table
    I thought it was only possible with ref. to ADOX

    here a another way...
    (isn't cleaned up yet)

    Code:
    Option Strict On
    Imports System.Data.OleDb
    
    
    Public Class Form4
        Private _SchemaNames As New List(Of String)
        Private _TableNames As New List(Of String)
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            con.Open()
            Using generalSchema = con.GetSchema(), tableSchema = con.GetSchema("Tables")
                _SchemaNames.AddRange(From rw In generalSchema Select Name = rw(0).ToString)
                _TableNames.AddRange(From rw In tableSchema Select Name = rw("TABLE_NAME").ToString)
            End Using
            con.Close()
            ListBox1.DataSource = _SchemaNames
            ListBox2.DataSource = _TableNames
            AddHandler ListBox1.SelectedIndexChanged, AddressOf DbTable_SelectedIndexChanged
            AddHandler ListBox2.SelectedIndexChanged, AddressOf DbTable_SelectedIndexChanged
            DbTable_SelectedIndexChanged(ListBox1, EventArgs.Empty)
        End Sub
    
        Private Sub DbTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            If ListBox2.SelectedIndex < 0 Then Return
            Dim tb = TryCast(DataGridView1.DataSource, DataTable)
            If tb IsNot Nothing Then tb.Dispose()
            DataGridView1.DataSource = Nothing
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Cmd As OleDbCommand = con.CreateCommand()
            Dim adp As OleDbDataAdapter = New OleDbDataAdapter(Cmd)
            con.Open()
            Select Case True
                Case sender Is ListBox1
                    Dim schema = _SchemaNames(ListBox1.SelectedIndex)
                    Dim msg = "Connection.GetSchema(""" & schema & """)"
                    Debug.Write(msg & "    ")
                    Try
                        DataGridView1.DataSource = con.GetSchema(schema)
                        Debug.WriteLine("")
                    Catch ex As Exception
                        Debug.WriteLine("Failed: " & ex.Message)
                        MessageBox.Show(msg & " konnte nicht ausgeführt werden")
                    End Try
                Case sender Is ListBox2
                    tb = New DataTable
                    Dim sTable = _TableNames(ListBox2.SelectedIndex)
    
                    Dim SqlCmd As OleDbCommand = con.CreateCommand()
                    Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(SqlCmd)
                    Dim sqldr As OleDbDataReader
                    With SqlCmd
                        .CommandText = "Select * From " & sTable
                        .CommandType = CommandType.Text
                        myDA.Fill(tb)
                        sqldr = .ExecuteReader
                    End With
                    tb = sqldr.GetSchemaTable
                    con.Close()
                    DataGridView1.DataSource = tb
    
            End Select
            con.Close()
        End Sub
    to check for a Recordcount in each Table, click in Listbox 1 on "Indexes", look in the DGV for "Cardinality"
    you get a reliable count on the records of each Table.

    regards
    Chris
    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.

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