Problems with Switch Statement and Datatable Population

Question

3.40/5 (2 votes)

See more:

Hi

Please be gentle as I am still learning. I have created a method in C# that loops through itself each time calling a different search from an MySQL database then should write this data to individually created tables. The call to the database and the returned data seem fine however I am having a problem with the switch statement I am using.

First I create these tables.

C#

//Table to hold results
        public static DataTable tblBBResults = new DataTable();
        public static DataTable tblBWResults = new DataTable();
        public static DataTable tblNHResults = new DataTable();
        public static DataTable tblTHResults = new DataTable();
        public static DataTable tblWCResults = new DataTable();
        public static DataTable tblWHResults = new DataTable();

Then I call the following method

C#

public static void getAllOwners()
        {

            int _orderByF = 1;
            //Park Numbers
            int[] _parknums = new int[] {2,3,4,5,6,7};
            DataTable _tempTB = new DataTable();


            for (int i = 0; i < _parknums.Length; i++)
            {
                SqlConnection connection = new SqlConnection(connString);
                //Create Reader to catch returned data
                SqlDataReader readMe = null;
                //Define command 
                SqlCommand spCommand = new SqlCommand("storedProcedure", connection);
                //define command as SP
                spCommand.CommandType = CommandType.StoredProcedure;
                //Create the parameters that are required by the SP
                spCommand.Parameters.Add(new SqlParameter("@Park", _parknums[i]));
                spCommand.Parameters.Add(new SqlParameter("@OrderByF", _orderByF));

                connection.Open();

                readMe = spCommand.ExecuteReader();
                //MessageBox.Show(Convert.ToString(readMe.Read()));    

                _tempTB.Load(readMe);
                
                try
                {


                    switch (_parknums[i])
                    {
                        case 2:
                            tblBBResults = _tempTB;
                            break;
                        case 3:
                            tblTHResults = _tempTB;
                            break;
                        case 4:
                            tblBWResults = _tempTB;
                            break;
                        case 5:
                            tblWHResults = _tempTB;
                            break;
                        case 6:
                            tblNHResults = _tempTB;
                            break;
                        case 7:
                            tblWCResults = _tempTB;
                            break;
                        default:
                            break;
                    }

                    connection.Close();
                    readMe.Close();

                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    //Attempts to clear Table just in case it was this causing the error.
                    _tempTB.Clear();
                    _tempTB.Dispose();
                    _tempTB.Reset();

                    if (connection.State == ConnectionState.Open)
                    {
                        
                        connection.Close();
                    }
                } 
            }

        }

The first time through it seems to work fine and the first table tblBBResults is populated. However on the second time through it returns the correct data from the database and when I step through the switch it skips the first step as it should, it fills the dataTable tblTHResults correctly but it also fills the first table with the same set of data!! This is what I am struggling with as how can it assign the same data to first table when the assignment operator is skipped? It then does this for all subsequent tables so in the end I have 6 tables of data all identical and from the last call.

Thanks

Harry

Posted 6-Nov-13 5:54am

hcompston

Add a Solution

3 solutions

Solution 2

try adding the tempTB = new DataTable(); in the for loop just before _tempTB.Load(readMe);. Add the function below and call it in case statement.
public DataTable ImportRows(DataTable dtTemp)
{
DataTable dt1 = new DataTable();
foreach (DataRow dr in dtTemp.Rows)
{
dt1.ImportRow(dr);
}
return dt1;
}
so your code shd look like

public static void getAllOwners()
{

int _orderByF = 1;
//Park Numbers
int[] _parknums = new int[] {2,3,4,5,6,7};
DataTable _tempTB = new DataTable();

for (int i = 0; i < _parknums.Length; i++)
{
SqlConnection connection = new SqlConnection(connString);
//Create Reader to catch returned data
SqlDataReader readMe = null;
//Define command
SqlCommand spCommand = new SqlCommand("storedProcedure", connection);
//define command as SP
spCommand.CommandType = CommandType.StoredProcedure;
//Create the parameters that are required by the SP
spCommand.Parameters.Add(new SqlParameter("@Park", _parknums[i]));
spCommand.Parameters.Add(new SqlParameter("@OrderByF", _orderByF));

connection.Open();

readMe = spCommand.ExecuteReader();
//MessageBox.Show(Convert.ToString(readMe.Read()));

_tempTB = new DataTable();

_tempTB.Load(readMe);

try
{

switch (_parknums[i])
{
case 2:
tblBBResults = ImportRows(_tempTB);
break;
case 3:
tblTHResults = ImportRows(_tempTB);
break;
case 4:
tblBWResults = ImportRows(_tempTB);
break;
case 5:
tblWHResults = ImportRows(_tempTB);
break;
case 6:
tblNHResults = ImportRows(_tempTB);
break;
case 7:
tblWCResults = ImportRows(_tempTB);
break;
default:
break;
}

connection.Close();
readMe.Close();

}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
//Attempts to clear Table just in case it was this causing the error.
_tempTB.Clear();
_tempTB.Dispose();
_tempTB.Reset();

if (connection.State == ConnectionState.Open)
{

connection.Close();
}
}
}

}

public DataTable ImportRows(DataTable dtTemp)
{
DataTable dt1 = new DataTable();
foreach (DataRow dr in dtTemp.Rows)
{
dt1.ImportRow(dr);
}
return dt1;
}

Posted 6-Nov-13 6:33am

santosh_131

Solution 3

Hi
I have managed to solve this using OriginalGriff's advice and modified the switch statement to completely remove the temp table. Seems to be working as intended...for the minute.

C#

switch (_parknums[i])
                    {
                        case 2:
                            tblBBResults.Load(readMe);
                            break;
                        case 3:
                            tblTHResults.Load(readMe);
                            break;
                        case 4:
                            tblBWResults.Load(readMe);
                            break;
                        case 5:
                            tblWHResults.Load(readMe);
                            break;
                        case 6:
                            tblNHResults.Load(readMe);
                            break;
                        case 7:
                            tblWCResults.Load(readMe);
                            break;
                        default:
                            break;
                    }

Thank you both for your help it was very much appreciated.

Harry

Posted 6-Nov-13 6:50am

hcompston

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-11-06T06:22:00

The problem is that you are using just one DataTable, and assuming that assigning a static variable to that table will copy the contents - it doesn't. It sets the reference to the table, so if you later change the table content (as you do each time through the for loop) it "updates" all the static table variables that refer to the same table.

It's a bit like a desk drawer: if you put a stapler in your drawer, and the put a postit on your screen to say "Stapler in drawer" you are ok. But if you later come along, empty the drawer into the bin and put your phone in there instead, you can add a new postit note to your screen saying "Phone in drawer" and it is correct. But then when you go looking for the stapler, it isn't there!

Add a new table each time you go round the loop, instead of relying on the one instance outside the loop.