Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
Не подскажите как писать CLR функции. Проблем c C# нет нужен пример. Есть конкретная задача - на входе функции следующие параметры:
- путь к двоичному файлу
- имя таблицы
- поле таблицы куда вставляются данные

Нужно прочитать содержимое файла и вставить в эту таблицу и это поле.
20 фев 14, 09:13    [15595443]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
http://blogs.msdn.com/b/alexejs/archive/2009/06/09/p20090609_5f00_1.aspx
20 фев 14, 09:29    [15595525]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Новопашин Владимир
Нужно прочитать содержимое файла и вставить в эту таблицу и это поле.
Во-первых, в функции, даже CLR, этого сделать не получится. Во-вторых, желаемое легко осуществить средствами T-SQL.
20 фев 14, 10:02    [15595669]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
invm
Новопашин Владимир
Нужно прочитать содержимое файла и вставить в эту таблицу и это поле.
Во-первых, в функции, даже CLR, этого сделать не получится. Во-вторых, желаемое легко осуществить средствами T-SQL.


А как?
20 фев 14, 10:06    [15595696]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Glory
Member

Откуда:
Сообщений: 104760
Новопашин Владимир
А как?

Изучить такие инструкции как
OPENROWSET
BULK INSERT
20 фев 14, 10:10    [15595714]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
Сделал сборку:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data;
using Microsoft.SqlServer.Server;

namespace PURPEImage
{
    public partial class PURPEData
    {
        /// <summary>
        /// Процедура читает файл в блобовское поле таблицы.
        /// Пример вызова: exec ReadFileToBlobField 'Media', 'blob', 'B6DFBCA5-665D-4E8B-8BDB-8CA9CB0CC1A0', 'c:\Temp\Book1.csv'
        /// </summary>
        /// <param name="tblName">Имя таблицы</param>
        /// <param name="colName">Название блобовской колонки</param>        
        /// <param name="fileName">Полное имя файла</param>
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ReadFileToBlobField(SqlString tblName, SqlString colName, SqlInt32 id,  SqlString fileName)
        {
            if (!CheckObjectsValidity(tblName, colName)) return;
            SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
            SqlCommand cmd = new SqlCommand("update " + tblName.ToString() + " set " + colName.ToString() 
                + " = @colValue where doc_id = @id", cnn);

            cmd.Parameters.Add("@colValue", SqlDbType.VarBinary);
            cmd.Parameters.Add("@id", SqlDbType.Int   );
            System.IO.FileStream fs = new System.IO.FileStream(fileName.ToString(), System.IO.FileMode.Open);
            cmd.Parameters["@colValue"].Value = new SqlBytes(fs);
            cmd.Parameters["@id"].Value = id;
            cmd.ExecuteNonQuery();
            fs.Close(); 
            cnn.Close();
        }
        /// <summary>
        /// Проверяет, что в текущей базе существует таблица с таким именем и в ней колонка с таким именем.
        /// Какая-никакая защита от injection.
        /// </summary>
        /// <param name="tblName">Имя таблицы</param>
        /// <param name="fldName">Имя колонки</param>
        /// <returns></returns>
        private static bool CheckObjectsValidity(SqlString tblName, SqlString colName)
        {
            SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
            string dbName = cnn.Database;
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "select count(1) from sys.tables where name = @tblName";
            cmd.Parameters.Add(new SqlParameter("@tblName", tblName));
            if ((int)cmd.ExecuteScalar() == 0)
            {
                cnn.Close(); //Поскольку Pipe.ExecuteAndSend(cmd) открывает свое контекстное соединение, предыдущее к этому моменту нужно закрыть.
                cmd = new SqlCommand("raiserror('Таблица %s не найдена в текущей базе %s!', 16, 1, '" 
                    + tblName.ToString() + "', '" + dbName + "')");
                try { SqlContext.Pipe.ExecuteAndSend(cmd); } //raiserror поднимает еще .NETовскую ошибку Msg 6522, Level 16, State 1 ...
                catch (SqlException) { }; //которая со всем стеком вызовов будет добавлена к пользовательской, если ее вовремя не перехватить.
                return false;
            }
            cmd = cnn.CreateCommand();
            cmd.CommandText = "select count(1) from sys.columns where name = @colName and object_name(object_id) = @tblName and type_name(system_type_id) = 'varbinary' and max_length = -1";
            cmd.Parameters.Add(new SqlParameter("@colName", colName)); 
            cmd.Parameters.Add(new SqlParameter("@tblName", tblName));
            if ((int)cmd.ExecuteScalar() == 0)
            {
                cnn.Close(); //Поскольку Pipe.ExecuteAndSend(cmd) открывает свое контекстное соединение, предыдущее к этому моменту нужно закрыть.
                cmd = new SqlCommand(String.Format("raiserror('Колонка {0} типа varbinary(max) не найдена в таблице {1}!', 16, 1)", 
                    colName, tblName));
                try { SqlContext.Pipe.ExecuteAndSend(cmd); } //raiserror поднимает еще .NETовскую ошибку Msg 6522, Level 16, State 1 ...
                catch (SqlException) { }; //которая со всем стеком вызовов будет добавлена к пользовательской, если ее вовремя не перехватить.
                return false;
            }
            cnn.Close(); return true;
        }
    }
}


Пытаюсь скриптом
CREATE ASSEMBLY PURASM from 'd:\PURPEImage.dll' WITH PERMISSION_SET = SAFE
зарегить , выдается ошибка:

Сообщение 6218, уровень 16, состояние 3, строка 1
CREATE ASSEMBLY не удалось выполнить для сборки "PURPEImage" из-за ошибки проверки сборки "PURPEImage". Убедитесь, что соответствующие сборки обновлены и являются доверенными (для external_access или unsafe) для запуска в базе данных. Сообщения средства проверки среды CLR об ошибках, если таковые имеются, последуют за этим сообщением
20 фев 14, 10:40    [15595934]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
Что-то помню насчет параметра trustworthy или как-то так.
20 фев 14, 10:44    [15595967]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
Ну и еще навскидочку
Лучше использовать конструкцию типа

using(SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open())
{
   ... тут код, использующий SqlConnection cnn
}

Более корректно обрабатывает работу с Disposable-объектами.
Правда это больше по C# информация.
Но думаю утечки памяти на сервере вам не нужны.
20 фев 14, 10:50    [15596014]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Glory
Member

Откуда:
Сообщений: 104760
Новопашин Владимир
Сделал сборку:

Вместо того, чтобы написать одну команду ?

  INSERT INTO myTable(FileName, FileType, Document) 
     SELECT 'Text1.txt' AS FileName, 
        '.txt' AS FileType, 
        * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
20 фев 14, 10:50    [15596017]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
И еще один подводный камень, о который можно больно ушибиться.
Вот этот код будет выполняться не под вашим логином, а под логином службы SQL Server.

System.IO.FileStream fs = new System.IO.FileStream(fileName.ToString(), System.IO.FileMode.Open);


Вы должны будете обеспечить этой службе доступ к файлу, который собираетесь загружать.
Если служба LOCALSERVICE и файл лежит на том же сервере, где запущен SQL Server, то работать будет в большинстве случаев.
Но часто используется доменный аккаунт или файл на сетевом ресурсе...

P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.
20 фев 14, 11:04    [15596139]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
Разобрался в чем была ошибка - у меня по умолчанию стоял фреймворк 4.5 - изменил на 2.0 и все зарегилось.
20 фев 14, 11:35    [15596421]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
SQL2008
Что-то помню насчет параметра trustworthy или как-то так.

Trustworthy никак не влияет на случай, когда PERMISSION_SET=SAFE.
20 фев 14, 11:40    [15596477]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Новопашин Владимир
Сделал сборку:
Мда... Ответы не читаем, предлагаемые ссылки тоже.
Вместо того, чтобы сделать нормально, пишем кривые сборки...

Достаточно сформировать инструкцию update и выполнить ее:
use tempdb;
go

create table #t (id int not null primary key, b varbinary(max) null);
insert into #t (id) values (1);
go

select * from #t;
go

declare @t sysname = '#t', @c sysname = 'b', @pk sysname = 'id', @key int = 1, @f varchar(255) = 'C:\Windows\notepad.exe', @cmd varchar(max);

select
 @cmd = 'update t set ' + quotename(@c) + ' = s.b from ' + quotename(@t) + ' t cross join openrowset(bulk ''' + @f + ''', single_blob) s(b) where ' + quotename(@pk) + ' = ' + cast(@key as varchar(10))

select @cmd;
exec(@cmd);

select * from #t;
go

drop table #t;
go
20 фев 14, 11:42    [15596507]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
SQL2008
P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.
Тут же цель не решить бизнес-задачу, а чтоб "зарегилось" :-)
20 фев 14, 11:42    [15596514]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
alexeyvg
SQL2008
P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.
Тут же цель не решить бизнес-задачу, а чтоб "зарегилось" :-)


Когда что-то до конца не доделано - это не правильно.
20 фев 14, 11:54    [15596656]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
alexeyvg
SQL2008
P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.
Тут же цель не решить бизнес-задачу, а чтоб "зарегилось" :-)


зарегилось в скуле под виндой
20 фев 14, 11:54    [15596661]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
Новопашин Владимир
Member

Откуда:
Сообщений: 129
Winnipuh
alexeyvg
пропущено...
Тут же цель не решить бизнес-задачу, а чтоб "зарегилось" :-)


зарегилось в скуле под виндой


Аха
20 фев 14, 11:56    [15596677]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
CLR
Guest
SQL2008
P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.

ой, а я думал, для операций которые выгоднее выполнять в CLR а не в tsql.
Например парсинг строк, математику и т.п.
Да банальный spiltstring
20 фев 14, 12:19    [15596931]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
Сон Веры Павловны
SQL2008
Что-то помню насчет параметра trustworthy или как-то так.

Trustworthy никак не влияет на случай, когда PERMISSION_SET=SAFE.

Думаю, что вы правы. Это было так давно, что остались только смутные воспоминания
20 фев 14, 12:55    [15597325]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
CLR
SQL2008
P.S. CLR пишутся только для операций, которые ну никак нельзя сделать силами T-SQL.

ой, а я думал, для операций которые выгоднее выполнять в CLR а не в tsql.
Например парсинг строк, математику и т.п.
Да банальный spiltstring

Понимаю, что втягиваюсь в холивар, но...
В чем заключается выгода? В плане выполнения запроса?
20 фев 14, 12:57    [15597359]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
CLR
Guest
SQL2008
CLR
пропущено...

ой, а я думал, для операций которые выгоднее выполнять в CLR а не в tsql.
Например парсинг строк, математику и т.п.
Да банальный spiltstring

Понимаю, что втягиваюсь в холивар, но...
В чем заключается выгода? В плане выполнения запроса?

в скорости выполнения.
в потребляемых ресурсах.
20 фев 14, 13:04    [15597447]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
Знал одного "программиста", который делал в коде запрос типа "SELECT * FROM TABLE",
а затем в цикле в коде перебирал все записи и, если нужно поле совпадало, с искомым отбирал
эту запись.
Он тоже экономил на перечислении полей и критериях "WHERE", мотивировав это тем, что чем короче запрос,
тем быстрее он выполняется.

Возвращаясь к теме нашего разговора - допустим, что одиночный вызов CLR процедуры быстрее, чем выполнение аналогичного запроса. Но вы уверены, что миллион вызовов той же процедуры будет быстрее запроса, возвращающего миллион записей?
Кроме того для вызова процедуры в стек кроме текущего состояния процессора и регистров будут сохраняться параметры (имя таблицы и поля). Как сохраняются в стеке строковые переменные?
Это к вопросу о ресурсах.

И последний вопрос - вызов CLR процедуры может быть многопоточным (распарралелен)?
Или вам необходимо дождаться её окончания, прежде чем продолжить выполнения основного запроса?
Это к вопросу о скорости.
20 фев 14, 13:30    [15597729]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4279
Я говорю не о том, что CLR это плохо!
Тоже часто использовал их ранее.
Просто их применение должно быть взвешенным.
И, как и в случае с курсорами, - только когда нет иного способа.
Впрочем это чистое IMHO.
20 фев 14, 13:33    [15597782]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
SQL2008,

Индусы пошли дальше, они делают запрос вида select * from [список всех таблиц базы через запятую], а потом уже на клиенте разгребают что куда.
20 фев 14, 13:36    [15597827]     Ответить | Цитировать Сообщить модератору
 Re: CLR функция  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
WarAnt
Индусы пошли дальше, они делают запрос вида select * from [список всех таблиц базы через запятую], а потом уже на клиенте разгребают что куда.
Надо им предложить все хранить в одной таблице. Это эффективнее - серверу не придется кроссджойнить.
20 фев 14, 13:43    [15597919]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить