MySql multi table looping through the update

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Please explain the main ideas in the blog published is:

There is a table structure of MySql database, and these tables have one thing in common, that is in the table have the same field, such as ID,name,city,adress,lat,lng. The fields in the table ID, name, city, adress does not allow nulls, so the field data in the table are already assigned to a real valued, while lat and LNG (longitude and latitude) is empty. Now is to write a program, according to city, the adress request for access to data, the cycle of each table according to the city, request adress data update to the corresponding table, the row in the table corresponds to the corresponding data. This sentence, you may feel dizzy, that is, the first cyclic query city and adress table traversal of the value, according to city, the value of adress request for access to data, update to the corresponding row in the table, the circular updated we jump to the next table update, recycling and so on, until all the update. . .

This is a program that we do, I also naive to think that the manual input city and adress request to get updates, who never expect data in a database table with tens of thousands of, if manual kills you, ha ha... So this program ideas from our colleagues that is acquired, very useful, I also in constant learning, published for reference, resource sharing can better progress!!!

MySql database connection string:

string MySqlString = "Host=ip address; Port= port; User id= username password; pwd=; Database= database name;Character Set=utf8"

The first step,

Connect to the database, write a according to the city, adress request to get the query data, pass a parameter (the database table), such as Get_Data(string tablename)

Query: string MySqlSelect = "select id, city, address, lat, LNG from" + tablename; and then call ExecuteReader () method will be spread out, pay attention to: to establish a model class this field should list (here not elaborate, we all know, it is get{}set{}), convenient.

using (MySqlDataReader dataReader = Command.ExecuteReader()) { while (dataReader.Read()) { DataModel dataModel = new DataModel(); dataModel.Id = dataReader["id"].ToString(); dataModel.City = (string)dataReader["city"]; dataModel.Address = (string)dataReader["address"]; if (dataReader["latitude"] != DBNull.Value) { dataModel.Latitude = (decimal)dataReader["latitude"]; } else dataModel.Latitude = 0.0M; if (dataReader["longitude"] != DBNull.Value) { dataModel.Latitude = (decimal)dataReader["longitude"]; } else dataModel.Latitude = 0.0M; dataSource.Add(dataModel); } dataReader.Close(); dataReader.Dispose(); }

The second step,

That is the update method, from the query to the data, loop through the table name update. There are four parameters, the update method according to the city, the adress query from the ID, the name of the table of database, and to update the field lat, lng. Such as Update_Data(string id, string dataTable, decimal lat, decimal lng)

Update: string MySqlUpdata = "update" + dataTable + "set latitude=" + LAT "," longitude= "+ LNG + where id= + ID"; then executes the ExecuteNonQuery () method, return the number of rows affected.

The third step,

In the Main () method in the call, the first name of the table there is an array, shaped like a:

string[] tableName = new string[10]; tableName[0] = "A"; tableName[1] = "B"; tableName[2] = "C"; tableName[3] = "D"; tableName[4] = "E"; tableName[5] = "F"; tableName[6] = "G"; tableName[7] = "H"; tableName[8] = "I"; tableName[9] = "J";

Then, the name of the table to write a for loop, the Model class instantiation above written, query method calls the first step inside, will obtain the entity value List<Model>Generic collections. Then foreach traversal List<Model>Generic collections, a variable parameter value (lat/lng), calling the update method, update cycle, on the line... The following code example:

for (i = 0; i <tableName.Length; i++) { List<Model> DataSource = new List<Model>(); int h = 0; DataSource = Get_Data(tableName[i]); foreach (DataModel dm in DataSource) { try { Update_Data(dm.Id, tableName[i], dm.Latitude, dm.Longitude); Console.WriteLine("Article" + h + "Record to update"); h++; } catch (Exception ex) { continue; } } Console.WriteLine(tableName[i] + "This table has been updated.!!!");

Here, this blog is finished, I do not know is reading you will see do not understand, I do not know how to express myself, only know that they can understand, ha ha. . . .

Anyway, this is the sharing of resources, to provide you a few ideas.

Statements of a school, for reference only!!!  


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Isaac at December 16, 2013 - 2:37 PM