-
Feb 13th, 2018, 11:35 AM
#1
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.
-
Feb 13th, 2018, 12:41 PM
#2
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
-
Feb 13th, 2018, 12:52 PM
#3
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.
-
Feb 13th, 2018, 06:36 PM
#4
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.
-
Feb 14th, 2018, 01:01 AM
#5
Re: adSchemaStatistics in .Net ?
Originally Posted by si_the_geek
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.
-
Mar 24th, 2018, 03:23 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|