Подключение к SQL-серверу из 1С:Предприятие 8 (пример кода)

В данной статье приведены экземпляры кода на языке 1С, которые позволяют подключаться к базе данных на SQL-сервере, получать из нее данные, а также записывать новые и обновлять существующие записи в таблицах этой базы данных. Код протестирован на платформе 8.3.5, а также на версиях SQL Server 2008 R2 и SQL Server 2012. Операционная система Windows Server 2008 и Windows Server 2012. Есть предположение, что указанный код будет работать и на других версиях 1С, SQL и Windows.

0. Оглавление

1. Постановка задачи
2. Подключение к SQL-серверу
3. Добавление записей в таблицу
4. Обновление записей в таблице
5. Просмотр выборки из таблицы
6. Удаление записей из таблицы
7. Закрытие соединения
8. Общий текст процедуры

1. Постановка задачи

Итак, имеется SQL-сервер с адресом WIN2012\TEST_SQL. Для подключения используется имя входа SQL-сервера UserDB с паролем Pass123456.

На сервере размещена база данных с именем TestDB. В базе имеется таблица с именем Table_1, в которой 3 столбца:

• C_DateTime, с типом datetime
• C_Char, с типом char(50)
• C_Numeric, с типом numeric(18,0)

Изначально таблица пустая:

У пользователя UserDB есть права на чтение и запись в базу данных TestDB. В данном случае пользователь является владельцем указанной базы данных.

Требуется научиться записывать в базу данных и читать из базы данных из 1С при помощи прямых запросов к SQL-серверу.

2. Подключение к SQL-серверу

Весь приведенный ниже код может работать как на клиенте, так и на сервере. Главное чтобы был доступен COM-объект «ADODB» (подробнее о данной библиотеке можно прочитать, например, здесь). Также с клиента (или с сервера, смотря где выполняется код) должен быть доступен указанный SQL-сервер (настроено правило в Брандмауере для возможности внешнего подключения). Код приводится совсем простой, для понимания с чего вообще начинать разработчику, который раньше с этим не сталкивался.

Итак, для подключения необходимо создать COM-объект «ADODB». Для этого выполняется код 1С:

&НаКлиенте
Процедура ВыполнитьОбработку(Команда)

    //Инициализация переменных
    ИмяСервераSQL = "WIN2012\TEST_SQL";
    ПользовательSQL = "UserDB";
    ПарольSQL = "Pass123456";
    БазаДанныхSQL = "TestDB";
    ТаблицаSQL = "Table_1";


    /////////////////////////////////////////
    //Подключение к SQL-серверу
    Попытка
        Соединение  = Новый COMОбъект("ADODB.Connection");
        Команда     = Новый COMОбъект("ADODB.Command");
        Выборка     = Новый COMОбъект("ADODB.RecordSet");
        Соединение.ConnectionString =
            "driver={SQL Server};" +
            "server="+ИмяСервераSQL+";"+
            "uid="+ПользовательSQL+";"+
            "pwd="+ПарольSQL+";"+
            "database="+БазаДанныхSQL+";";
        Соединение.ConnectionTimeout = 30;
        Соединение.CommandTimeout = 600;
        //Открытие соединение
        Соединение.Open();
        Команда.ActiveConnection   = Соединение;
        Сообщить("Успешное подключение!");
    Исключение
        Сообщить(ОписаниеОшибки());
        Возврат;
    КонецПопытки;

КонецПроцедуры

Результат работы в 1С:Предприятии:

Теперь, в общем то, можно выполнять любой запрос SQL через данный COM-объект. Посмотрим как это делать на самых распространенных примерах.

3. Добавление записей в таблицу

Теперь добавим новую строку в нашу пока еще пустую таблицу. Для этого необходимо выполнить код:

    /////////////////////////////////////////
    //Добавляем запись в таблицу
    ТекДата = ТекущаяДата();
    ТекСтрока = "Запись строки";
    ТекЧисло = 123456;

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДата) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДата = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "INSERT INTO "+ТаблицаSQL+"
        |([C_DateTime], [C_Char], [C_Numeric])
        |VALUES ('"+ТекДата+"', N'"+ТекСтрока+"', '"+ТекЧисло+"')";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись добавлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Смотрим результат и видим, что строка появилась в таблице:

4. Обновление записей в таблице

Теперь попробуем обновить все записи в таблице со значением C_Numeric=123456. Для этого выполняем код:

    /////////////////////////////////////////
    //Обновляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекДатаНовая = Дата(1,1,1);
    ТекСтрокаНовая = "Обновленная запись строки";

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДатаНовая) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДатаНовая = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "UPDATE "+ТаблицаSQL+"
        |SET
        |   [C_Char] = N'"+ТекСтрокаНовая+"',
        |   [C_DateTime] = '"+ТекДатаНовая+"'
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись обновлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Проверяем, что запись обновилась:

5. Просмотр выборки из таблицы

Писать и обновлять записи в таблице мы научились. Теперь посмотрим как просматривать выборку из запроса с SQL-сервера:

    /////////////////////////////////////////
    //Читаем записи
    ТекстИнструкции =
        "SELECT
        |   [C_DateTime], [C_Char], [C_Numeric]
        |FROM "+ТаблицаSQL;
    Попытка
        Команда.CommandText = ТекстИнструкции;
        Выборка = Команда.Execute();
        Если Выборка.BOF = Ложь Тогда
            Выборка.MoveFirst();
            Пока Выборка.EOF = Ложь Цикл
                Сообщить("[C_DateTime]="+Дата(Выборка.Fields("C_DateTime").Value)
                    +", [C_Char]="+СокрЛП(Выборка.Fields("C_Char").Value)
                    +", [C_Numeric]="+Число(Выборка.Fields("C_Numeric").Value));
                Выборка.MoveNext();
            КонецЦикла;
        КонецЕсли;
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Результат в предприятии:

6. Удаление записей из таблицы

Для удаления всех записей в таблице со значением C_Numeric=123456 выполняем код:

    /////////////////////////////////////////
    //Удаляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "DELETE FROM "+ТаблицаSQL+"
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись удалена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Проверяем, что записей в таблице не осталось:

7. Закрытие соединения

Ну и после выполнения всех необходимых действий необходимо закрыть соединение с SQL-сервером. Для этого достаточно вызвать метод Close() созданного COM-объекта:

    /////////////////////////////////////////
    //Закрытия соединения
    Попытка
        Соединение.Close();
        Сообщить("Соединение закрыто!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

8. Общий текст процедуры

Общий текст всей процедуры получился следующий:

&НаКлиенте
Процедура ВыполнитьОбработку(Команда)

    //Инициализация переменных
    ИмяСервераSQL = "WIN2012\TEST_SQL";
    ПользовательSQL = "UserDB";
    ПарольSQL = "Pass123456";
    БазаДанныхSQL = "TestDB";
    ТаблицаSQL = "Table_1";


    /////////////////////////////////////////
    //Подключение к SQL-серверу
    Попытка
        Соединение  = Новый COMОбъект("ADODB.Connection");
        Команда     = Новый COMОбъект("ADODB.Command");
        Выборка     = Новый COMОбъект("ADODB.RecordSet");
        Соединение.ConnectionString =
            "driver={SQL Server};" +
            "server="+ИмяСервераSQL+";"+
            "uid="+ПользовательSQL+";"+
            "pwd="+ПарольSQL+";"+
            "database="+БазаДанныхSQL+";";
        Соединение.ConnectionTimeout = 30;
        Соединение.CommandTimeout = 600;
        //Открытие соединение
        Соединение.Open();
        Команда.ActiveConnection   = Соединение;
        Сообщить("Успешное подключение!");
    Исключение
        Сообщить(ОписаниеОшибки());
        Возврат;
    КонецПопытки;


    /////////////////////////////////////////
    //Добавляем запись в таблицу
    ТекДата = ТекущаяДата();
    ТекСтрока = "Запись строки";
    ТекЧисло = 123456;

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДата) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДата = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "INSERT INTO "+ТаблицаSQL+"
        |([C_DateTime], [C_Char], [C_Numeric])
        |VALUES ('"+ТекДата+"', N'"+ТекСтрока+"', '"+ТекЧисло+"')";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись добавлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;


    /////////////////////////////////////////
    //Обновляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекДатаНовая = Дата(1,1,1);
    ТекСтрокаНовая = "Обновленная запись строки";

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДатаНовая) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДатаНовая = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "UPDATE "+ТаблицаSQL+"
        |SET
        |   [C_Char] = N'"+ТекСтрокаНовая+"',
        |   [C_DateTime] = '"+ТекДатаНовая+"'
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись обновлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

    /////////////////////////////////////////
    //Читаем записи
    ТекстИнструкции =
        "SELECT
        |   [C_DateTime], [C_Char], [C_Numeric]
        |FROM "+ТаблицаSQL;
    Попытка
        Команда.CommandText = ТекстИнструкции;
        Выборка = Команда.Execute();
        Если Выборка.BOF = Ложь Тогда
            Выборка.MoveFirst();
            Пока Выборка.EOF = Ложь Цикл
                Сообщить("[C_DateTime]="+Дата(Выборка.Fields("C_DateTime").Value)
                    +", [C_Char]="+СокрЛП(Выборка.Fields("C_Char").Value)
                    +", [C_Numeric]="+Число(Выборка.Fields("C_Numeric").Value));
                Выборка.MoveNext();
            КонецЦикла;
        КонецЕсли;
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

    /////////////////////////////////////////
    //Удаляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "DELETE FROM "+ТаблицаSQL+"
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись удалена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;


    /////////////////////////////////////////
    //Закрытия соединения
    Попытка
        Соединение.Close();
        Сообщить("Соединение закрыто!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

КонецПроцедуры

Результат работы в 1С:Предприятии: