Обновление БД MySQL и ошибка duplicate entry

Эта проблема также довольно часто обсуждается на различных форумах. Итак, давайте представим себе стандартную рабочую ситуацию. Например, вы пишете программу, обновляющую таблицу или несколько таблиц вашей БД, используя входные данные из Excel-файлов или чего-нибудь подобного. Нередко крупные оптовые поставщики дают розничным интернет-магазинам уже подготовленные прайс-листы со ссылками на изображения, которые скачать или заполучить довольно легко. Перед программистами в основном ставится задача обновить существующие в БД позиции товаров и вписать туда новые при условии их отсутствия.

Работа с БД в данном случае может выстраиваться двумя путями, а именно, удаленное соединение с базой данных сайта на сервере, что на самом деле не очень оправданно, в том числе и с точки зрения безопасности и сохранности данных. И второй вариант - импорт таблиц БД на локальный сервер вашего компьютера, например, самым популярным из которых под Windows является Denwer. Этот вариант очень удобен в силу скорости работы, а для отладки работающей программы так просто необходим. Обновив нужные таблицы БД сайта у себя на локальном компьютере, вы можете их потом легко загрузить в БД сайта через ту же панель phpMyAdmin либо ее аналоги.

Теперь перейдем к самому разрабатываемому приложению. Если говорить о его реализации на C#, то для соединения с базой данных используется MySQLConnector/NET, отображение таблицы в БД производится стандартным методом. А далее мы также можем опять же идти двумя путями. Первый - программное обновление и добавление данных через адаптер MySqlDataAdapter с использованием представления в компоненте DataGridView. Давайте подробнее рассмотрим этот вариант. Допустим, вы подключились к БД, выбрали нужную таблицу и отобразили ее в DataGridView. Обновление полей существующего товара можно произвести следующим образом:

((DataTable)dataGridView1.DataSource).Rows[i][18] = baseId [i];

Именно такая запись является верной, хотя многие начинающие сталкиваются с трудностями из-за непонимания, вводя строки:

dataGridView1.Rows[i].Cells[18].Value = baseId [i];

Этот вариант неверен, потому как программно меняет записи только в элементе отображения, но не самой таблице с данными. А для добавления строк используем схожую конструкцию:

((DataTable) dataGridView1.DataSource).Rows.Add(baseId[i], category[i]);

В данном случае строчки кода набраны просто для примера, в котором показано, что добавление записей производится из неких массивов и ставятся, к примеру, внутри циклов. Произведя необходимые изменения нужно обновить таблицу или таблицы данных, используя конструкцию кода, который выносится либо в отдельную функцию, либо вписывается в нужное место:

DataTable changes = data.GetChanges();
da.Update(changes);
data.AcceptChanges();

... где изначально до этого были определены:

private DataTable data;
private MySqlDataAdapter da;

Наиболее частая ошибка при программном обновлении таблиц БД в данном случае MySQL, и добавления в них новых записей - duplicate entry. Если объяснять ее суть простыми словами, то эта ошибка возникает при обновлении либо добавлении записей в ключевые поля таблицы для случаев, когда идентичные записи в этих полях уже существуют. Например, если у вас в качестве ключевого указано поле с именами артикулов товаров, то в таблице не может быть двух товаров с одинаковыми артикулами. Иначе говоря, каждый артикул должен быть уникальным. При обновлении подобным образом и возникновении ошибки duplicate entry сам процесс обновления тут же прекратится. Ситуацию можно обойти, вписав строку...

da.ContinueUpdateOnError = true;

Указатель ContinueUpdateOnError, установленный в true, позволяет продолжать действие обновления (функции Update()) в случае возникновения ошибок. По умолчанию он установлен как false.

Рассмотренный выше метод не очень удобен в рамках операции добавления (Add()), например, для случаев, когда у вас имеется таблица с большим количеством полей, а вам нужно заполнить выборочно только несколько из них. В результате, нужно составлять строку, учитывающую иногда все имеющиеся поля. Также этот метод будет громоздок, если вы хотите найти необходимый артикул товара и заменить в нем некоторые записи, в результате чего без дополнительных циклов и встроенных в них конструкций if-else не обойтись.

Если не привязываться к представлению в DataGridView и использованию MySqlDataAdapter, то обновление таблиц БД можно производить напрямую с помощью класса MySqlCommand. Он в любом случае используется хотя бы при том же открытии в DataGridView нужной таблицы (применяется метод ExecuteReader()), но мы пойдем дальше.

MySqlCommand позволяет выполнять базовые командные SQL-запросы INSERT, UPDATE и DELETE при использовании его собственного метода ExecuteNonQuery(). Выглядеть все может таким образом. В примере мы читаем данные из таблицы, названной dataGridViewExcel, в которую загружен Excel-файл и обновляем/вносим данные в таблицу 'products' нашей БД 'myDataBase'. Сразу предупрежу, что данный код работать не будет из-за ошибки duplicate entry.

private MySqlConnection conn;
private MySqlCommand incom1;
private MySqlCommand incom2;
....
//делаем коннект к БД
if (conn != null) conn.Close();
string connStr = String.Format("server={0};user id={1}; password={2}; database=mysql; pooling=false", "localhost", "root", "");
try {
 conn = new MySqlConnection(connStr);
 conn.Open();
}
catch (MySqlException ex) {
 MessageBox.Show("Ошибка соедиенения с сервером: " + ex.Message);
}
...
//запускаем цикл
for (int I = 0; i < dataGridExcel.Rows.Count - 1; i++) {
//обновляем данные
 incom1 = new MySqlCommand("UPDATE `myDataBase`.`products` SET name=@k1, picture=@k2 WHERE name=@k0", conn);
 incom1.Parameters.AddWithValue("@k0", dataGridViewExcel.Rows[i].Cells[0].Value.ToString());
 incom1.Parameters.AddWithValue("@k1", dataGridViewExcel.Rows[i].Cells[1].Value.ToString());
 incom1.Parameters.AddWithValue("@k2", dataGridViewExcel.Rows[i].Cells[2].Value.ToString());
 incom1.ExecuteNonQuery();
//добавляем данные
 incom2 = new MySqlCommand("INSERT INTO `myDataBase`.`products` (`name`,`picture`) VALUES (@k1,@k2);", conn);
 incom2.Parameters.AddWithValue("@k1", dataGridViewExcel.Rows[i].Cells[1].Value.ToString());
 incom2.Parameters.AddWithValue("@k2", dataGridViewExcel.Rows[i].Cells[2].Value.ToString());
 incom2.ExecuteNonQuery();
}

Но уже в данном представлении вы можете убедиться, что этот вариант с использованием MySqlCommand более ёмок и позволяет нам напрямую общаться с таблицей БД и ее конкретными полями. Но как быть в данном случае с возникновением ошибки duplicate entry? На самом деле все решается очень просто, если вы поместите блоки кода с обновлением и добавлением в конструкции try-catch. Это работает фактически также как и в случае включения режима обновления в случае ошибок из предыдущего случая.

private MySqlConnection conn;
private MySqlCommand incom1;
private MySqlCommand incom2;
....
//делаем коннект к БД
if (conn != null) conn.Close();
string connStr = String.Format("server={0};user id={1}; password={2}; database=mysql; pooling=false", "localhost", "root", "");
try {
 conn = new MySqlConnection(connStr);
 conn.Open();
}
catch (MySqlException ex) {
 MessageBox.Show("Ошибка соедиенения с сервером: " + ex.Message);
}
...
//запускаем цикл
for (int I = 0; i < dataGridExcel.Rows.Count - 1; i++) {
 try {
  incom1 = new MySqlCommand("UPDATE `myDataBase`.`products` SET name=@k1, picture=@k2 WHERE name=@k0", conn);
  incom1.Parameters.AddWithValue("@k0", dataGridViewExcel.Rows[i].Cells[0].Value.ToString());
  incom1.Parameters.AddWithValue("@k1", dataGridViewExcel.Rows[i].Cells[1].Value.ToString());
  incom1.Parameters.AddWithValue("@k2", dataGridViewExcel.Rows[i].Cells[2].Value.ToString());
  incom1.ExecuteNonQuery();
 } catch {}
 try{
  incom2 = new MySqlCommand("INSERT INTO `myDataBase`.`products` (`name`,`picture`) VALUES (@k1,@k2);", conn);
  incom2.Parameters.AddWithValue("@k1", dataGridViewExcel.Rows[i].Cells[1].Value.ToString());
  incom2.Parameters.AddWithValue("@k2", dataGridViewExcel.Rows[i].Cells[2].Value.ToString());
  incom2.ExecuteNonQuery();
 } catch {}
}

Топорный :), но действенный метод. Что касается сравнения обновления БД по времени с использованием первого или второго метода, которые мы описали... существенной разницы не обнаружено.

Кристофер,
christopher@tut.by,
itcs.3dn.ru