Queryable Collections

[cmhampton]

Does anyone know how to create queryable collections in VB without using LINQ? I am working on creating a dynamic questionnaire system and need some help. Here's some background:

The system needs to support parent/child questionnaires. I have the database model created, as well as a query that returns all the questions for a group of questionnaires, sorted and grouped, first by the questionnaire sort order, and then by the question sort order.

Example:

[B]questionid, questionName, questionType, questionOrder, questionnaireOrder, questionnaireid[/B]
1, Favorite Color, Dropdown, 100, 100, 3
2, Zip Code, TextBox, 200, 100, 3
3, Email, TextBox, 100, 200, 4

I have a question class, with a collection of answers. The questionnaire class has a collection of the questions.

On the output control, I want to be able to loop through the child questionnaires, and then query the collection to get only the questions associated with the current child questionnaire.

I know this can be done with LINQ, but my company is still on VB8, so I don't have that as an option.

Here's what I am doing now:

'Get the questions from the questionnaire
Dim dsQuestions As New AppName.BLL.Questionnaires.QuestionCollection

'If this questionnaire is a parent of other questionnaires, get the ParentChildQuestions Collection
If Me.ParentChildQuestions.Count > 0 Then
  dsQuestions = Me.ParentChildQuestions
Else
  dsQuestions = Me.Questions
End If

'Loop through the questions
For Each q As AppName.BLL.Questionnaires.Question In dsQuestions

'If this is a parent/child questionnaire, and this is a new section, create the section header
If Me.ParentChildQuestions.Count > 0 And (Not q.Questionnaireid = questionnaireid) Then
  CreateChildQuestionnaireElement(...)
End If

'Update the questionnaireid variable
questionnaireid = q.Questionnaireid

'Do a whole bunch of crap to output the questions

Next

I don't really like this method because it's too many steps and just seems inefficient.

What I would like to do is something like this:

Dim dsChildren As New AppName.BLL.Questionnaire.ChildCollection
dsChildren = questionnaireDataService.GetChildren(_Questionnaireid)
  
For Each child As AppName.BLL.Questionnaire.Child In dsChildren
  CreateChildQuestionnaireElement(...)

  'Query questions collection to get the questions for this specific child

Next

[SheSaidImaPregy]

The problem with this is that a querystring can only hold so many characters. Want to see how many? Type as many as you can inside the address bar and response.write your querystring. You'll see how many. (Not sure if it's 100 or 255).

So most likely... no. However, you can pass them through a cookie if you wish, or a session variable, or even put it in a text file then read it off.

[cmhampton]

The problem with this is that a querystring can only hold so many characters. Want to see how many? Type as many as you can inside the address bar and response.write your querystring. You'll see how many. (Not sure if it's 100 or 255).

So most likely... no. However, you can pass them through a cookie if you wish, or a session variable, or even put it in a text file then read it off.

Was this supposed to be in another thread? My question has nothing to do with query strings.

[SheSaidImaPregy]

Question real quick.. then I can help you (it will be in RED).

An no, it was meant for this thread, however I didn't read your thread, just the header. My apologies.

'Get the questions from the questionnaire
Dim dsQuestions As New AppName.BLL.Questionnaires.QuestionCollection

'Is this entire thing in another loop?
'If this questionnaire is a parent of other questionnaires, get the ParentChildQuestions Collection
If Me.ParentChildQuestions.Count > 0 Then
  dsQuestions = Me.ParentChildQuestions
Else
'Does this part matter? It seems like it doesn't, and you only
'choose the above line later on anyway.
  dsQuestions = Me.Questions
End If

'Loop through the questions
For Each q As AppName.BLL.Questionnaires.Question In dsQuestions

'If this is a parent/child questionnaire, and this is a new section, create the section header
If Me.ParentChildQuestions.Count > 0 And (Not q.Questionnaireid = questionnaireid) Then
  CreateChildQuestionnaireElement(...)
End If

'Update the questionnaireid variable
questionnaireid = q.Questionnaireid

'Do a whole bunch of crap to output the questions

Next

This is what I would do. Let me know if I am missing something that I should know:

Dim dsQuestions As New AppName.BLL.Questionnaires.QuestionCollection

If Me.ParentChildQuestions.Count > 0 Then
    dsQuestions = Me.ParentChildQuestions

    For Each q As AppName.BLL.Questionnaires.Question In dsQuestions
        If Not q.Questionnaireid = questionnaireid Then CreateChildQuestionnaireElement(...)

        questionnaireid = q.Questionnaireid

        '........... more code
    Next
End If

However, like I said, you might have more here that depends on that previous code. Which then, this example would not work.

Why not do it like this also?

'create a parent collections
Dim dsQuestions As New AppName.BLL.Questionnaires.QuestionCollection

If Me.ParentChildQuestions.Count > 0 Then
    dsQuestions = Me.ParentChildQuestions

    For Each q As AppName.BLL.Questionnaires.Question In dsQuestions
        If Not q.Questionnaireid = questionnaireid Then

            'create a child collections for each parent.
            For Each subq As .... (array or collections)

                'Either create an array or directly create your child elements
                'Dim arr = From rec In subq CreateChildQuestionnaireElement(...)
                'For Each elemnt In arr
                    'write your ChildQUestionnaireElement
                'Next

                CreateChildQuestionnaireElement(...)
            Next

            questionnaireid = q.Questionnaireid

            '........... more code
    Next
End If

Unless you wish to post the whole code, I think this is about as much as I can help.

Also, quickie question, how do you specify the language you're writing in the code blocks? Been searching for it here. I only saw it when I first joined.

[cmhampton]

Here's the whole function:

'Create default xml objects
Dim xmlQuestionnaireData As New XmlDocument()
Dim elForm As XmlElement
Dim elPage As XmlElement
Dim elPages As XmlElement
Dim elQuestionnaire As XmlElement
Dim elFields As XmlElement

Dim questionnaireid As Integer = 0

'Create the xml header.
xmlQuestionnaireData.AppendChild(xmlQuestionnaireData.CreateXmlDeclaration("1.0", "utf-8", Nothing))

'Add the base elements.
elForm = xmlQuestionnaireData.CreateElement("FORM")
elPages = xmlQuestionnaireData.CreateElement("PAGES")
elPage = xmlQuestionnaireData.CreateElement("PAGE")

'Add the questionnaire name and page info...
elPage.Attributes.Append(xmlQuestionnaireData.CreateAttribute("title")).Value = _QuestionnaireName
elPage.Attributes.Append(xmlQuestionnaireData.CreateAttribute("id")).Value = "Page_1"

'Create the element for the intro text.
elQuestionnaire = xmlQuestionnaireData.CreateElement("INFO")
elQuestionnaire.InnerText = _introText
elPage.AppendChild(elQuestionnaire)

'Create the container for the questions
elFields = xmlQuestionnaireData.CreateElement("FIELDS")

'Get the questions from the questionnaire
Dim dsQuestions As New App.BLL.Questionnaires.QuestionCollection

'If this questionnaire is a parent of other questionnaires, get the ParentChildQuestions Collection
If Me.ParentChildQuestions.Count > 0 Then
  dsQuestions = Me.ParentChildQuestions
Else
  dsQuestions = Me.Questions
End If

'Create objects for the question
Dim elFieldElement As XmlNode
Dim elPropertiesElement As XmlNode
Dim elPropertyElement As XmlNode

Dim questionnaireDataService As New App.DAL.Questionnaire.QuestionnaireDataService()

Dim dsChildren As New DataSet
dsChildren = questionnaireDataService.GetChildren(_Questionnaireid)

'Loop through the questions
For Each q As App.BLL.Questionnaires.Question In dsQuestions

'If this is a parent/child questionnaire, and this is a new section, create the section header
If Me.ParentChildQuestions.Count > 0 And (Not q.Questionnaireid = questionnaireid) Then
  CreateChildQuestionnaireElement(xmlQuestionnaireData, elFieldElement, elFields, elPropertiesElement, elPropertyElement, q.Questionnaireid)
End If

'Update the questionnaireid variable
questionnaireid = q.Questionnaireid

'Create the base element for this question
elFieldElement = xmlQuestionnaireData.CreateElement("FIELD")

'Create the attributes for the field element
                elFieldElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("id")).Value = q.Questionid
                elFieldElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("label")).Value = q.Question
                elFieldElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("required")).Value = q.Required
                elFieldElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("type")).Value = q.ControlType

'Create the validation types and pass the regular expressions
If q.ValidationType = "Date" Then
  CreateValidationDate(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Currency" Then
  CreateValidationCurrency(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Numeric" Then
  CreateValidationNumeric(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Email" Then
  CreateValidationEmail(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Password" Then
  CreateValidationPassword(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "URL" Then
  CreateValidationURL(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Phone" Then
  CreateValidationPhone(xmlQuestionnaireData, elFieldElement, q)
ElseIf q.ValidationType = "Zip" Then
  CreateValidationZip(xmlQuestionnaireData, elFieldElement, q)
End If

'Add the element to the document
elFields.AppendChild(elFieldElement)

'Create elements for the question properties
elPropertiesElement = xmlQuestionnaireData.CreateElement("PROPERTIES")
elPropertyElement = xmlQuestionnaireData.CreateElement("PROPERTY")

'Question name in HTML
                elPropertyElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("name")).Value = "ID"
elPropertyElement.InnerText = "questionid_" & q.Questionid.ToString()

'Add the element
elPropertiesElement.AppendChild(elPropertyElement)

'Create special properties for different question types.  Each type can have a seperate CSS class.
If (q.QuestionType = "Text Box") Then

  CreatePropertiesTextBox(...)

ElseIf q.QuestionType = "Password" Then

  CreatePropertiesPassword(...)

ElseIf q.QuestionType = "Text Area" Then

  CreatePropertiesTextArea(...)

ElseIf q.QuestionType = "Radio Button" Or q.QuestionType = "Check Box List" Or q.QuestionType = "Scale" Or q.QuestionType = "Yes/No" Then

  CreatePropertiesListControl(...)

ElseIf q.QuestionType = "Multi-Select List Box" Then

  CreatePropertiesListBox(...)

End If

'Add the properties to the document
elFieldElement.AppendChild(elPropertiesElement)

'Create the base objects for the answers
Dim elListItemsElement As XmlNode
Dim elListItemElement As XmlNode

'Create the listitems element
elListItemsElement = xmlQuestionnaireData.CreateElement("LISTITEMS")

'Some questions have specific listitem structures
If q.QuestionType = "Scale" Then

  CreateAnswersScale(...)

ElseIf q.QuestionType = "Yes/No" Then

  CreateAnswersYesNo(...)

ElseIf q.QuestionType = "Check Box List" Then

  CreateAnswersCheckBoxList(...)

Else

'Otherwise, write out the answers normally
  CreateAnswers(...)

End If


elFieldElement.AppendChild(elListItemsElement)

Next

'Add everything to the document
elPage.AppendChild(elFields)
elPages.AppendChild(elPage)
elForm.AppendChild(elPages)

xmlQuestionnaireData.AppendChild(elForm)

Return xmlQuestionnaireData

Thanks for looking at this.

To answer your questions in red:

'Is this entire thing in another loop?
No.

'Does this part matter? It seems like it doesn't, and you only
'choose the above line later on anyway.

There are two properties of this class that contain questions. If this is a parent/child questionnaire, the Me.ParentChildQuestions property will contain a collection of questions for all the questions for the entire questionnaire set. So it could have questions from three different questionnaires.

If it is a standalone questionnaire, the Me.Questions propery will contain a collection of questions just for that questionnaire.

The whole reason I'm looking at this is because the sql query I use gets all the questions for the questionnaire set, but I need to separate them into a new section for each child questionnaire.

BTW, [ code=VB.NET ] is how you specify the language.

[SheSaidImaPregy]

Thanks. I'll look at it and give you back the results.

One quick question as well, this function works properly, you just wish to make it more efficient and condensed, correct?

I will make it more efficient and condense it.

Add trace to your page, and open the page, and refresh it. Do this 10 times and keep track of each time the value "From Last(s)" for this certain function. To see how long this function is taking, add a trace to the page manually as well:

...
...

Trace.Write("tracename","Tracedescription")
call the function
Trace.Write("tracename","tracedescription")

ex:

Trace.Write("Before","Before Function")
QuestionnaireFunction()
Trace.Write("After","Function")

This will tell you how long it takes to execute.

I like this part, making things more efficient :)

I'll get back to you momentarily.

[SheSaidImaPregy]

Unfortunately I don't think you can skip any steps here. It seems all quite logical. If you do skip steps, that would require a different route to take and can be a waste of time, as performance gains would be very minimal. It's done alright here.

Here is my edited version:
(Before Edit: Lines: 170)
(After Edit: Lines: 135)

'Create default xml objects
Dim xmlQuestionnaireData As New XmlDocument()
''Combined all Declared Variables as they have same datatype
Dim elForm, elPage, elPages, elQuestionnaire, elFields As XmlElement

Dim questionnaireid As Integer = 0

'Create the xml header.
xmlQuestionnaireData.AppendChild(xmlQuestionnaireData.CreateXmlDeclaration("1.0", "utf-8", Nothing))

'Add the base elements.
elForm = xmlQuestionnaireData.CreateElement("FORM")
elPages = xmlQuestionnaireData.CreateElement("PAGES")
elPage = xmlQuestionnaireData.CreateElement("PAGE")

'Add the questionnaire name and page info...
elPage.Attributes.Append(xmlQuestionnaireData.CreateAttribute("title")).Value = _QuestionnaireName
elPage.Attributes.Append(xmlQuestionnaireData.CreateAttribute("id")).Value = "Page_1"

'Create the element for the intro text.
elQuestionnaire = xmlQuestionnaireData.CreateElement("INFO")
elQuestionnaire.InnerText = _introText
elPage.AppendChild(elQuestionnaire)

'Create the container for the questions
elFields = xmlQuestionnaireData.CreateElement("FIELDS")

'Get the questions from the questionnaire
Dim dsQuestions As New App.BLL.Questionnaires.QuestionCollection

'If this questionnaire is a parent of other questionnaires, get the ParentChildQuestions Collection
If Me.ParentChildQuestions.Count > 0 Then dsQuestions = Me.ParentChildQuestions Else dsQuestions = Me.Questions

'Create objects for the question
''Combined variables with same datatype
Dim elFieldElement, elPropertiesElement, elPropertyElement As XmlNode

Dim questionnaireDataService As New App.DAL.Questionnaire.QuestionnaireDataService()

Dim dsChildren As New DataSet = questionnaireDataService.GetChildren(_Questionnaireid)

'Loop through the questions
For Each q As App.BLL.Questionnaires.Question In dsQuestions

'If this is a parent/child questionnaire, and this is a new section, create the section header
If Me.ParentChildQuestions.Count > 0 And (Not q.Questionnaireid = questionnaireid) Then CreateChildQuestionnaireElement(xmlQuestionnaireData, elFieldElement, elFields, elPropertiesElement, elPropertyElement, q.Questionnaireid)

'Update the questionnaireid variable
questionnaireid = q.Questionnaireid

'Create the base element for this question
elFieldElement = xmlQuestionnaireData.CreateElement("FIELD")

'Create the attributes for the field element
''Using "with" command to help.
''You can try (I never have), using the next line and removing "Attributes" from
''next following lines:
''With elFieldElement.Attributes
With elFieldElement
                .Attributes.Append(xmlQuestionnaireData.CreateAttribute("id")).Value = q.Questionid
                .Attributes.Append(xmlQuestionnaireData.CreateAttribute("label")).Value = q.Question
                .Attributes.Append(xmlQuestionnaireData.CreateAttribute("required")).Value = q.Required
                .Attributes.Append(xmlQuestionnaireData.CreateAttribute("type")).Value = q.ControlType
End With

'Create the validation types and pass the regular expressions
''You should always use select statements if you have more than a simple if...else...end if
''Performance gains a nice.
Select Case q.ValidationType
  Case "Date" : CreateValidationDate(xmlQuestionnaireData, elFieldElement, q)
  Case "Currency" : CreateValidationCurrency(xmlQuestionnaireData, elFieldElement, q)
  Case "Numeric" : CreateValidationNumeric(xmlQuestionnaireData, elFieldElement, q)
  Case "Email" : CreateValidationEmail(xmlQuestionnaireData, elFieldElement, q)
  Case "Password" : CreateValidationPassword(xmlQuestionnaireData, elFieldElement, q)
  Case "URL" : CreateValidationURL(xmlQuestionnaireData, elFieldElement, q)
  Case "Phone" : CreateValidationPhone(xmlQuestionnaireData, elFieldElement, q)
  Case "Zip" : CreateValidationZip(xmlQuestionnaireData, elFieldElement, q)
''You should always have a Case Else, a default incase of an error.
End Select

'Add the element to the document
elFields.AppendChild(elFieldElement)

'Create elements for the question properties
elPropertiesElement = xmlQuestionnaireData.CreateElement("PROPERTIES")
elPropertyElement = xmlQuestionnaireData.CreateElement("PROPERTY")

'Question name in HTML
                elPropertyElement.Attributes.Append(xmlQuestionnaireData.CreateAttribute("name")).Value = "ID"
elPropertyElement.InnerText = "questionid_" & q.Questionid.ToString()

'Add the element
elPropertiesElement.AppendChild(elPropertyElement)

'Create special properties for different question types.  Each type can have a seperate CSS class.
Select Case q.QuestionType
  Case "Text Box" : CreatePropertiesTextBox(...)
  Case "Password" : CreatePropertiesPassword(...)
  Case "Text Area" : CreatePropertiesTextArea(...)
  Case "Radio Button", "Check Box List", "Scale", "Yes/No" : CreatePropertiesListControl(...)
  Case "Multi-Select List Box" : CreatePropertiesListBox(...)
''Should have a Case Else for default.
End Select

'Add the properties to the document
elFieldElement.AppendChild(elPropertiesElement)

'Create the base objects for the answers
''Combined, same datatype
Dim elListItemsElement, elListItemElement As XmlNode

'Create the listitems element
elListItemsElement = xmlQuestionnaireData.CreateElement("LISTITEMS")

'Some questions have specific listitem structures
Select Case q.QuestionType
  Case "Scale" : CreateAnswersScale(...)
  Case "Yes/No" : CreateAnswersYesNo(...)
  Case "Check Box List" : CreateAnswersCheckBoxList(...)
'Otherwise, write out the answers normally
  Case Else : CreateAnswers(...)
End Select

elFieldElement.AppendChild(elListItemsElement)

Next

'Add everything to the document
elPage.AppendChild(elFields)
elPages.AppendChild(elPage)
elForm.AppendChild(elPages)

xmlQuestionnaireData.AppendChild(elForm)

Return xmlQuestionnaireData

[SheSaidImaPregy]

Without Commenting lines, it's a lot smaller than it was:
Lines Before Edit: 143
Lines After Edit: 98

A nice 45 line savings ^^

It should be a lot quicker in performance as well.

[cmhampton]

Thanks for the help. The method is only taking 14 hundredths of a second anyway, so I wasn't too worried about time savings. The way I'm doing it just seemed counter-intuitive. I kept telling myself there had to be a better way, but I guess not.

BTW, thanks for pointing out the select statement. I don't know how I missed that.

[SheSaidImaPregy]

No problem.

I'm a performance geek I guess. I sit here at work almost all day long just finding what is faster than what, and tweaking a 1500 line file I use whenever I build an application, just to make my life easier.

But no, I don't believe it will be easier another way. Who knows, someone else might find a better one!