Запрос к внешней базе данных MS SQL Server

На многих предприятиях возникает необходимость "подружить" между собой различные системы учета. В моем случае это были 1С и Navision. Суть взаимодействия систем была проста — какие-то данные мигрировали из одной системы в другую и наоборот. А когда есть две системы с (в теории) идентичными данными эту самую идентичность нужно еще достичь. Потому как на практике по различным причинам что-то может не попасть в обмен или уже на стороне приемника отсечься как некорректное и т.п. Заниматься поиском расхождений задача относительно несложная, но кропотливая. Чтоб облегчить себе жизнь и не скакать между различными окнами, пришла в голову идея находясь в 1С получать данные из Navision. А так как данные он хранит в базе MS SQL сервера, нужно было всего лишь придумать инструмент для выполнения произвольного запроса на классическом SQL.

Конечно можно ничего не выдумывать и пользоваться Server Management Studio. Но во-первых, пускать туда какого-нибудь продвинутого аналитика небезопасно. Во-вторых, далеко не всегда сисадмины позволяют ставить подобный софт. В-третьих, если инструмент внедрен в 1С, то задачу по поиску можно делегировать, банально сохранив запрос, и пусть бухгалтер сам ищет что у него не ушло/пришло.

Для начала нужно установить соединение с SQL Server. Далее описать команду и результат выполнения вернуть в набор записей. Нам понадобятся три COM объекта: ADODB.Connection, ADODB.Command и ADODB.Recordset. Код выглядит следующим образом:

 Если АутентификацияWindows Тогда
СтрокаПодключения = "DRIVER={SQL Server};SERVER="+Сервер+";DATABASE="+База+";LANGUAGE=русский;Trusted_connection=yes";
Иначе
СтрокаПодключения = "DRIVER={SQL Server};SERVER="+Сервер+";UID="+Пользователь+";
|PWD="+Пароль+";DATABASE="+База+";LANGUAGE=русский";
КонецЕсли;
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = СтрокаПодключения;

cmd = Новый COMОбъект("ADODB.Command");
cmd.CommandTimeout = 360;
cmd.ActiveConnection = Соединение;
RS = Новый COMОбъект("ADODB.Recordset");
cmd.CommandText = ТекстЗапроса;

Try
RS=cmd.execute();
Except
#Если Клиент Тогда
Сообщить(ОписаниеОшибки(), СтатусСообщения.Важное);
#КонецЕсли
RS = Неопределено;
EndTry;

Если запрос выполнен успешно, то нам останется перебрать набор записей. Тут тоже ничего сложного нет. Сначала подготовим таблицу значений, добавим колонки в соответствии с полями набора записей. Далее переберем все записи и сохраним результат в таблицу значений.

 Нав = Новый ТаблицаЗначений;
Для каждого П из RS.Fields Цикл
Попытка
Нав.Колонки.Добавить(ПривестиИмяКДопустимому(П.Name), , П.Name);
Исключение
Предупреждение("Имя поля <" + П.Name + "> в запросе встречается более двух раз, задайте ему псевдоним.");
КонецПопытки;
КонецЦикла;

Пока RS.EOF() = 0 Цикл
строка = Нав.Добавить();
й = 0;
Для каждого П из RS.Fields Цикл
ТекущееПоле = ИменаПолей[й].Поле;
строка[ТекущееПоле] = RS.fields(й).Value;
й = й + 1;
КонецЦикла;
RS.MoveNext();
КонецЦикла;
RS.Close();

В коде встречается функция ПривестиИмяКДопустимому. Из названия не трудно догадаться, что ее предназначение убрать из имени всякие символы (%./ и т.п), недопустимые в наименовании колонок таблицы. И попутно сохранение соответствия имен полей в ТЧ ИменаПолей.

Функция ПривестиИмяКДопустимому(Стр)
Рез = СтрЗаменить(Стр," ","_");    // удалим пробелы
Рез = СтрЗаменить(Рез,Символы.Таб,"");   // удалим знак табуляции
Рез = СтрЗаменить(Рез,"(","_");    // удалим знаки скобок
Рез = СтрЗаменить(Рез,")","_");    // удалим знаки скобок
Рез = СтрЗаменить(Рез,"-","_");    // удалим дефис
Рез = СтрЗаменить(Рез,"%","_");    // удалим знак %
Рез = СтрЗаменить(Рез,"#","_");    // удалим знак #
Рез = СтрЗаменить(Рез,"№","_");    // удалим знак №
Рез = СтрЗаменить(Рез,"/","_");    // удалим знак /
Рез = СтрЗаменить(Рез,"","_");    // удалим знак \r

НайденнаяСтрока = ИменаПолей.Найти(Рез,"Поле");
Если НайденнаяСтрока = Неопределено Тогда
НП = ИменаПолей.Добавить();
НП.Поле = Рез;
НП.Описание = Стр;
Иначе
Рез = Рез + "_";
НП = ИменаПолей.Добавить();
НП.Поле = Рез;
НП.Описание = Стр;
КонецЕсли;

Возврат Рез;
КонецФункции

На этом этапе у нас есть результат в виде таблицы значений. В обычной форме вывести его особых трудов не составляет.

 Результат = Нав.Скопировать();
ЭлементыФормы.Результат.СоздатьКолонки();

В управляемой форме основная логика обработки та же самая. Некоторые трудности вызвало динамическое отображение результата. Дело в том, что я не нашел аналога методу СоздатьКолонки() для управляемых форм. Пришлось добавлять/удалять элементы формы вручную. Код получился слегка громоздким. Перед выполнением запроса очищаем то, что осталось от предыдущего. Сначала удаляем элементы, а затем связанные с ними реквизиты.

  //Очистим все результаты предыдущего запроса
Если Объект.ИменаПолей.Количество() > 0 Тогда
РезультатЗапроса.Очистить();
//Удалим элементы формы
Для каждого П из Объект.ИменаПолей Цикл
Эл = Элементы.Найти(П.Поле);
Если НЕ Эл = Неопределено Тогда
Элементы.Удалить(Эл);
КонецЕсли;
КонецЦикла;
//Удалим реквизиты ТЧ РезультатЗапроса
УдаляемыеРеквизиты = Новый Массив;
Для каждого П из Объект.ИменаПолей Цикл
УдаляемыеРеквизиты.Добавить("РезультатЗапроса."+П.Поле);
КонецЦикла;
ИзменитьРеквизиты(,УдаляемыеРеквизиты);
КонецЕсли;
//Удалим список полей из результата предыдущего запроса
Объект.ИменаПолей.Очистить();

После того как мы получили результат в виде таблицы значений выводим его аналогично тому как мы очищали перед выполнением запроса. Только на этот раз сначала заводим реквизиты, а затем добавляем элементы.

  //Выводим результат выполнения запроса в ТЧ РезультатЗапроса
ДобавляемыеРеквизиты = Новый Массив;
Для каждого К из ТР.Колонки Цикл
ДобавляемыеРеквизиты.Добавить(СоздатьРеквизитТЧФормы(К.Имя,К.ТипЗначения,К.Заголовок));
КонецЦикла;
ИзменитьРеквизиты(ДобавляемыеРеквизиты);
Для каждого К из ТР.Колонки Цикл
НовыйЭлемент = Элементы.Добавить(К.Имя, Тип("ПолеФормы"), Элементы.РезультатЗапроса);
НовыйЭлемент.ПутьКДанным = "РезультатЗапроса."+К.Имя;
НовыйЭлемент.Вид = ВидПоляФормы.ПолеВвода;
НовыйЭлемент.КнопкаОчистки = Ложь;
КонецЦикла;
Для каждого Стр из ТР Цикл
НС = РезультатЗапроса.Добавить();
ЗаполнитьЗначенияСвойств(НС,Стр);
КонецЦикла;
Для каждого П из Элементы.РезультатЗапроса.ПодчиненныеЭлементы Цикл
ТекущийИтог = ТР.Итог(П.Имя);
Если ТекущийИтог <> 0 И ТекущийИтог <> Неопределено Тогда
П.ТекстПодвала = ТР.Итог(П.Имя);
П.ГоризонтальноеПоложениеВПодвале = ГоризонтальноеПоложениеЭлемента.Право;
КонецЕсли;
КонецЦикла;

На этом все.