03-08-13, 10:37 PM
السلام عليكم ورحمه الله وبركاته
احتاج مساعدة فى كيفية قراءة العلاقات (relations) من قاعدة بيانات access و sql
مثل الفيجوال ستوديو عند اضافة data source للمشروع تظهر العلاقات فى الداتاسيت
وجدت كود لتحميل الجداول والعلاقات من قاعدة البيانات لكن يظهر الخطأ التالى
Record(s) cannot be read; no read permission on 'MSysRelationships'.
PHP كود :
Function getDataSetAndFill(ByRef connection As OleDb.OleDbConnection, Optional ByVal isExportSchema As Boolean = True) As DataSet
Dim myDataSet As New DataSet
Dim myCommand As New OleDb.OleDbCommand
Dim myAdapter As New OleDb.OleDbDataAdapter
myCommand.Connection = connection
myCommand.Connection.Open()
'Get Database Tables
Dim tables As DataTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
'iterate through all tables
Dim table As DataRow
For Each table In tables.Rows
'get current table's name
Dim tableName As String = table("TABLE_NAME")
Dim strSQL = "SELECT * FROM " & "[" & tableName & "]"
Dim adapter1 As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand(strSQL, connection))
adapter1.FillSchema(myDataSet, SchemaType.Source, tableName)
'Fill the table in the dataset
myCommand.CommandText = strSQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myDataSet, tableName)
Next
''''''''''''''''''''''''''''''''''''''
'''' Add relationships to dataset ''''
''''''''''''''''''''''''''''''''''''''
'First, get relationships names from database (as well as parent table and child table names)
Dim namesQuery As String = "SELECT szRelationship, szReferencedObject, szObject " & _
"FROM MSysRelationships"
Dim namesCommand As New System.Data.OleDb.OleDbCommand(namesQuery, connection)
Dim namesAdapter As New System.Data.OleDb.OleDbDataAdapter(namesCommand)
Dim namesDataTable As New DataTable
namesAdapter.Fill(namesDataTable)
'Now, get MSysRelationship from database
Dim relationsQuery As String = "SELECT * FROM MSysRelationships"
Dim command As New System.Data.OleDb.OleDbCommand(relationsQuery, connection)
Dim adapter As New System.Data.OleDb.OleDbDataAdapter(command)
Dim relationsDataTable As New DataTable
adapter.Fill(relationsDataTable)
Dim relationsView As DataView = relationsDataTable.DefaultView
Dim relationName As String
Dim parentTableName As String
Dim childTablename As String
For Each relation As DataRow In namesDataTable.Rows
relationName = relation("szRelationship")
parentTableName = relation("szReferencedObject")
childTablename = relation("szObject")
'Keep only the record of the current relationship
relationsView.RowFilter = "szRelationship = '" & relationName & "'"
'Declare two arrays for parent and child columns arguments
Dim parentColumns(relationsView.Count - 1) As DataColumn
Dim childColumns(relationsView.Count - 1) As DataColumn
For i As Integer = 0 To relationsView.Count - 1
parentColumns(i) = myDataSet.Tables(parentTableName). _
Columns(relationsView.Item(i)("szReferencedColumn"))
childColumns(i) = myDataSet.Tables(childTablename). _
Columns(relationsView.Item(i)("szColumn"))
Next
Dim newRelation As New DataRelation(relationName, parentColumns, childColumns, False)
myDataSet.Relations.Add(newRelation)
Next
'If isExportSchema Then
'Dim schemaName = GetXmlSchemaFileName()
'If File.Exists(schemaName) Then File.SetAttributes(schemaName, FileAttributes.Normal)
'myDataSet.WriteXmlSchema(schemaName)
'End If
myCommand.Connection.Close()
Return myDataSet
End Function
