Switch语句和数据库人口问题

本文介绍了Switch语句和数据库人口问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨 请保持温柔，因为我还在学习。我在C＃中创建了一个方法，每次从MySQL数据库调用不同的搜索时都会自行循环，然后将这些数据写入单独创建的表中。对数据库的调用和返回的数据看起来很好但是我正在使用的switch语句有问题。 首先我创建这些表。

// 保持结果的表格 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（）;

然后我调用以下方法

public static void getAllOwners（） { int _orderByF = 1 ; // 公园号码 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）; // 创建Reader以捕获返回的数据 SqlDataReader readMe = 空; // 定义命令 SqlCommand spCommand = new SqlCommand（ storedProcedure，connection）; // 将命令定义为SP spCommand.CommandType = CommandType.StoredProcedure; // 创建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）; 尝试 { 开关（_ 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 ; 默认： break ; } connection.Close（）; readMe.Close（）; } catch （例外e） { MessageBox.Show（e.ToString（） ）; } 最后 { // 尝试清除表，以防它导致错误。 _tempTB.Clear（）; _tempTB.Dispose（）; _tempTB.Reset（）; if （connection.State == ConnectionState.Open） { connection.Close（ ）; } } } }

第一次通过它似乎工作正常，填充第一个表tblBBResults。但是第二次通过它从数据库返回正确的数据，当我单步执行切换时，它会跳过它应该的第一步，它正确地填充dataTable tblTHResults，但它也用相同的数据集填充第一个表！ ！这是我正在努力的事情，因为当跳过赋值运算符时，它如何将相同的数据分配给第一个表？然后它为所有后续表执行此操作，因此最后我有6个数据表，所有数据都是相同的，并且来自最后一次调用。 谢谢 Harry

解决方案

问题是您只使用一个DataTable，并假设为该表分配静态变量将复制内容 - 它没有。它设置了对表的引用，因此如果您稍后更改表内容（就像每次通过for循环一样），它会更新所有引用同一个表的静态表变量。 它有点像一个办公桌抽屉：如果你把一个订书机放在你的抽屉里，并在屏幕上放一个贴纸说抽屉里的订书机你没问题。但如果您以后再来，将抽屉倒入垃圾箱并将手机放在那里，您可以在屏幕上添加一个新的便条贴，上面写着抽屉里的电话，这是正确的。但是当你去寻找订书机时，它就不存在了！ 每次循环时添加一个新表，而不是依赖于循环外的实例。

尝试添加 tempTB = new DataTable（）; 在 _tempTB.Load（readMe）; 之前的for循环中。添加下面的函数并在case语句中调用它。 public DataTable ImportRows（DataTable dtTemp） { DataTable dt1 = new DataTable（） ; foreach（dtTemp.Rows中的DataRow博士） { dt1.ImportRow（dr）; } 返回dt1; } 所以你的代码看起来像是 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）; //创建Reader来捕获返回数据 SqlDataReader readMe = null; //定义命令 SqlCommand spCommand = new SqlCommand（storedProcedure，connection）; //将命令定义为SP spCommand.CommandType = CommandType.StoredProcedure; //创建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）; 试试 { 开关（_parknums [我]） { 案例2： tblBBResults = ImportRows（_tempTB）; break; 案例3： tblTHResults = ImportRows（_tempTB）; 休息; 案例4： tblBWResults = ImportRows（_tempTB）; break; case 5： tblWHResults = ImportRows（_tempTB）; 休息; 案例6： tblNHResults = ImportRows（_tempTB）; 休息; 案例7： tblWCResults = ImportRows （_tempTB）; 休息; 默认： 休息; } connection.Close（）; readMe.Close（）; } catch（例外e） { MessageBox.Show（e.ToString（））; } 最后 { //尝试清除表格以防万一这会导致错误。 _tempTB.Clear（ ）; _t empTB.Dispose（）; _tempTB.Reset（）; if（connection.State == ConnectionState.Open） { connection.Close（）; } } } } 公共DataTable ImportRows（DataTable dtTemp） { DataTable dt1 = new DataTable（）; foreach（dtTemp.Rows中的DataRow dr） { dt1.ImportRow（dr）; } 返回dt1; }

嗨 我已经设法使用OriginalGriff的建议解决了这个问题并修改了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 ; 默认： break ; }

感谢您的帮助，非常感谢。 Harry

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.

//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

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

解决方案

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.

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; }

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.

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