raphael232
Programmer
Hi, currently on my site have multiple section areas which are pretty much the same. I'm currently in the process of upgrading from asp to asp.net and feel that i could save time by combining my section areas into a master table. To combat this problem i have come up with the following table structure:
Sections
- SectionID (primary key)
- SectionName
Attributes:
- AttributeID (primary key)
- SectionID (section attribute belongs too)
- AttributeName
Documents
- ID
- SectionID (section document belongs too)
- Title
- Content
... (other shared fields across sections)
DocumentAttributes:
- ID
- DocumentID (document attribute for)
- AttributeID
- AttributeValue
This allows me to have additioanl attributes for the different sections of my site.
I have created a table adapter in my dataset for Attributes, Documents and DocumentAttributes and have added an additional query for Attributes called GetAttributesBySection, an additional query for DocumentAttributes called GetDocumentAttributesByDocumentID and an additional query for documents called GetDocumentByID.
Next i have setup a class i can use to call from page to display the details of a document (using the ObjectDataSource and DetailsView) by passing in the id :
The GetDocumentByID one works fine, this allows me to display my document with all the additional attributes.
The problem i'm having is the InsertDocument and UpdateDocument Functions as the parameters passed in depends on the section. I'm not too sure where to look to overcome this problem.
Appreciate your feedback on this.
Thanks
Sections
- SectionID (primary key)
- SectionName
Attributes:
- AttributeID (primary key)
- SectionID (section attribute belongs too)
- AttributeName
Documents
- ID
- SectionID (section document belongs too)
- Title
- Content
... (other shared fields across sections)
DocumentAttributes:
- ID
- DocumentID (document attribute for)
- AttributeID
- AttributeValue
This allows me to have additioanl attributes for the different sections of my site.
I have created a table adapter in my dataset for Attributes, Documents and DocumentAttributes and have added an additional query for Attributes called GetAttributesBySection, an additional query for DocumentAttributes called GetDocumentAttributesByDocumentID and an additional query for documents called GetDocumentByID.
Next i have setup a class i can use to call from page to display the details of a document (using the ObjectDataSource and DetailsView) by passing in the id :
Code:
Imports KITTableAdapters
Imports System.Data
Public Class DocumentManager
Private _documentsAdapter As tbdDocumentsTableAdapter = Nothing
Private _documentAttributesAdapter As tbdDocumentAttributesTableAdapter = Nothing
Private _attributesAdapter As tbdAttributesTableAdapter = Nothing
Protected ReadOnly Property DocumentsAdapter() As tbdDocumentsTableAdapter
Get
If _documentsAdapter Is Nothing Then
_documentsAdapter = New tbdDocumentsTableAdapter()
End If
Return _documentsAdapter
End Get
End Property
Protected ReadOnly Property DocumentAttributesAdapter() As tbdDocumentAttributesTableAdapter
Get
If _documentAttributesAdapter Is Nothing Then
_documentAttributesAdapter = New tbdDocumentAttributesTableAdapter()
End If
Return _documentAttributesAdapter
End Get
End Property
Protected ReadOnly Property AttributesAdapter() As tbdAttributesTableAdapter
Get
If _attributesAdapter Is Nothing Then
_attributesAdapter = New tbdAttributesTableAdapter()
End If
Return _attributesAdapter
End Get
End Property
Public Function GetDocuments() As KIT.tbdDocumentsDataTable
Return DocumentsAdapter.GetDocuments
End Function
Public Function GetDocumentByID(ByVal documentID As Integer) As DataTable
Dim documents As KIT.tbdDocumentsDataTable = DocumentsAdapter.GetDocumentByID(documentID)
Dim document As KIT.tbdDocumentsRow = documents(0)
Dim documentAttributes As KIT.tbdDocumentAttributesDataTable = DocumentAttributesAdapter.GetDocumentAttributesByDocumentID(documentID)
Dim attributes As KIT.tbdAttributesDataTable = AttributesAdapter.GetAttributesBySection(document.fldSectionID)
Dim dataTable As DataTable = New DataTable
Dim dataRow As DataRow = dataTable.NewRow()
Dim counter As Integer = 2
dataTable.Columns.Add(New DataColumn("ID", GetType(Integer)))
dataTable.Columns.Add(New DataColumn("Title", GetType(String)))
' Add extra column specific to the section the document is in
For Each attribute As KIT.tbdAttributesRow In attributes
dataTable.Columns.Add(New DataColumn(attribute.fldAttributeName, GetType(String)))
Next
' Add column values for a document
dataRow(0) = document.ID
dataRow(1) = document.fldTitle
' Add extra column values for a document
For Each documentAttribute As KIT.tbdDocumentAttributesRow In documentAttributes
dataRow(counter) = documentAttribute.fldAttributeValue
counter += 1
Next
dataTable.Rows.Add(dataRow)
Return dataTable
End Function
Public Function InsertDocument(ByVal title As String) As Boolean
DocumentsAdapter.Insert(8, 1, title, "", "", "", True, True, DateTime.Now, DateTime.Now, Nothing, Nothing, 1, False, Nothing)
Return True
End Function
Public Function UpdateDocument(ByVal title As String, ByVal documentID As Integer) As Boolean
DocumentsAdapter.Update(8, 1, title, "", "", "", True, True, DateTime.Now, DateTime.Now, Nothing, Nothing, 1, False, Nothing, documentID)
Return True
End Function
Public Function DeleteDocument(ByVal documentID As Integer) As Boolean
DocumentsAdapter.Delete(documentID)
Return True
End Function
End Class
The GetDocumentByID one works fine, this allows me to display my document with all the additional attributes.
The problem i'm having is the InsertDocument and UpdateDocument Functions as the parameters passed in depends on the section. I'm not too sure where to look to overcome this problem.
Appreciate your feedback on this.
Thanks