Solved

Best practice for creating multiple columns in dataset?

Posted on 2008-06-25
4
290 Views
Last Modified: 2013-12-16
Hi
I have a table in a dataset that must contain a lot of columns. The data is answers from a questionnaire - and every question has 10 answers. Like so ...

Column name 1: Question1_00
Column name 2: Question1_03
Column name 3: Question1_05
Column name 4: Question1_06
Column name 5: Question1_07
Column name 6: Question1_08
Column name 7: Question1_09
Column name 8: Question1_10
Column name 9: Question1_11
Column name 10: Question1_13

Same  goes for question 2, question 3 and so on ... I tried doing this using enums - like the code below. It works, but doesn't seem like a greate idea. Any help?

enum Grade13
{
    grade00,
    grade03,
    grade05,
    grade06,
    grade07,
    grade08,
    grade09,
    grade10,
    grade11,
    grade13
}
 
 
// ----------
 
dsSummery = new DataSet();
DataTable dtSummery = new DataTable("Answers");
dtSummery.Columns.Add(new DataColumn("ID"));
 
foreach (int grade in Enum.GetValues(typeof(Grade13)))
{
    dtSummery.Columns.Add(new DataColumn("Question1_" + grade.ToString()));
}
 
dsSummery.Tables.Add(dtSummery);

Open in new window

0
Comment
Question by:Balltown
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Joni Kettunen
ID: 21863874
Why can't your DataSet contain multiple tables instead of multiple columns?

For example with two tables: Question and Answer you could make it lot easier.

Table Question with fields QuestionId and QuestionText
Table Answer with fields QuestionId, OrderNbr, AnswerText

You can link QuestionId fields if you want to, but in simple scenario you'd just create counter starting from one. First question would have QuestionId 1 and QuestionText.

According to QuestionId you would add rows to Answer table.
1st answer would have QuestionId 1, OrderNbr 1, and AnswerText.
2nd answer would have QuestionId 1, OrderNbr 2, and AnswerText.
...

Does this sound like a feasible solution to your problem?
0
 

Author Comment

by:Balltown
ID: 21864004
Hmm maybe theres is a misunderstanding. I'm only working on the answers - the questions doesn't matter right now. Maybe it's my naming of the columns that confuses... "Question1_XX" is the answer to question 1. A snip of the "Answers"-table would look like:

Subject ; Question1_00 ; Question1_03 ; Question1_05 ;
Math ; 5 ; 1 ;  6 ;
English ; 1 ; 0 ; 2 ;
Geo ; 0 ; 4 ; 3 ;

... As if: Five people voted "Question1_00" in Math and zero people voted "Question1_03" in English.

I could do a for-loop:
for (int i=0; i <=10; i++)
{
    dtSummery.Columns.Add(new DataColumn("Question1_" + i));
}
But then I wouldn't get the correct naming of the column as shown in my first post.
0
 
LVL 4

Accepted Solution

by:
Joni Kettunen earned 125 total points
ID: 21864086
If you are looking for just performance there are things to avoid. One thing is to avoid string catenation.
For example

string badString = "foo" + "bar"; // Creates three string "foo", "bar" and "foobar".

// Worst
string s = "";
for (int i=0; i<10; i++)
{
  s = s + i.ToString(); // creates new string in memory for each loop. Very slow when i < 1000.
}

Answer is to use StringBuilder when there are lots of catenations or in simple case String.Format.

One example to your case could be:

But to your question.

// Just numbers in string to avoid unnecessary cast
string[] answerNbrArray = { "00", "03", "05", "06", "07", "08", "09", "10", "11", "13" };
 
// Iterating through questions
for (int qNbr = 0; qNbr < 5; qNbr++)
{
    // Iterating through answers
    for (int aNbr = 0; aNbr < answerNbrArray.Length; aNbr++)
    {
        string colName = String.Format("Question{0}_{1}", qNbr, answerNbrArray[aNbr]);
        // Assing the colName    
        dtSummery.Columns.Add(new DataColumn(colName));
    }
}

Open in new window

0
 

Author Comment

by:Balltown
ID: 21864258
Thanks Valkoturkki. Of course that's it.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now