MySql multi-Table looping update

The main ideas for posting this blog post are as follows:

MySql Databases have a large number of table structures, and these tables all have the same field, such as id, name, city, adress, lat, and lng. The id, name, city, and adress fields in the table cannot be empty. Therefore, the data of these fields in the table has been assigned to real values, while the lat and lng fields are empty. Now you need to write a program to obtain data according to the city and adress requests. Update the data obtained in each loop table according to the city and adress requests to the corresponding table, and the corresponding row of data in the corresponding table. In this case, you may be a little dizzy. To put it bluntly, you should first query the values of city and adress in the traversal table cyclically, request data based on the values of city and adress, and update the data to the corresponding rows, after this table is updated cyclically, it will jump to the next table to recycle updates, and so on until all updates are made...

This is a program we made. At that time, I really thought that I had manually entered the city and adress requests to obtain updates. No one would have imagined that there were tens of thousands of data records in the database table. If you were exhausted manually, haha... Therefore, this program idea is also obtained from our colleagues and is very useful. I am constantly learning and posting it for your reference. Only by sharing resources can we make better progress !!!

The connection string of the MySql database is:

String MySqlString = "Host = IP address; Port = Port number; User id = User name; pwd = password; Database = Database name; Character Set = utf8"

Step 1,

Connect to the database and write a query method to obtain data according to the city and adress requests. PASS Parameters (database table name), such as Get_Data (string tablename)

Query statement: string MySqlSelect = "select id, city, address, lat, lng from" + tablename; then the ExecuteReader () method is called to transfer the value out. Note: in the table, we should first create a model class for these fields (this is not detailed here, everyone understands, that is, get {} set {}) to facilitate value transfer.

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

Step 2,

That is, the update method. From the queried data, the table name update is cyclically traversed. The update method has four parameters: the id queried by city and adress, the database table name, and the field lat and lng to be updated. Such as Update_Data (string id, string dataTable, decimal lat, decimal lng)

Update statement: string MySqlUpdata = "update" + dataTable + "set latitude =" + lat + ", longpolling =" + lng + "where id =" + id; run the ExecuteNonQuery () method to return the number of affected rows.

Step 3,

To call the Main () method, first store the table name in an array, such:

            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, write the table name into a for loop, instantiate the Model class written above, and call the query method in step 1, stores the obtained object values in the List <Model> generic set. Then, foreach traverses the List <Model> generic set, obtains the value of variable parameters (lat/lng), calls the update method, and updates cyclically... The sample code is as follows:

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. longpolling); Console. writeLine ("+ h +" record to update "); h ++;} catch (Exception ex) {continue ;}} Console. writeLine (tableName [I] + "this table has been updated !!! ");

At this point, this blog is complete. I don't know if you are reading it. I don't know how to express it. I only know what I can understand, haha ....

This is also resource sharing. You just need to provide some ideas.

For reference only !!!