Добрый день,

Возможно ли создать и вызвать БДшную функцию на сервере,

Например создать функцию(Через CREATE FUNCTION ) и добавить ее в скрипты в пакете

и потом как нибудь вызвать ее и получить то что она возвращает?

 

Заранее спасибо!

Нравится

5 комментариев

так вам нужна функция или хранимка? в любом случае вот тема. Как вызвать из js кода свой сервис можно посмотреть на sdk / как добавить кусок C# кода в БП - элемент "задание-сценарий". Ну и в C# коде вызываете хранимку, в хранимке делаете что угодно, результат читаете с помощью datareader'а и далее по бизнес логике...

Варфоломеев Данила,

Спасибо за ссылку, мне нужна функция, но по идее процедурой тоже можно обойтись

Можно вызвать хранимую процедуру. Правильный вызов процедуры (пример):

DataValueTypeManager dataValueTypeManager =UserConnection.DataValueTypeManager; 

var dateTimeValue = new DateTime(2009, 01, 02, 22, 12, 0);

                    Stream stream = newMemoryStream(Encoding.Unicode.GetBytes("Тест большого бинарного объекта"));

                    var textDataValueType = newTextDataValueType(dataValueTypeManager);

                    var guidDataValueType = newGuidDataValueType(dataValueTypeManager);

                    var integerDataValueType = newIntegerDataValueType(dataValueTypeManager);

                    var floatDataValueType = newFloat2DataValueType(dataValueTypeManager);

                    var booleanDataValueType = newBooleanDataValueType(dataValueTypeManager);

                    var dateTimeDataValueType = newDateTimeDataValueType(dataValueTypeManager);

                    var idValue = new Guid("{BCDB8392-55BC-472A-A49D-22A975E0BEF6}");



                    StoredProcedure storedProcedure =

                           new StoredProcedure(Page.UserConnection,"tsp_TestStoredProcedure")

                           .WithParameter("IdParameter", idValue)

                           .WithVarParameter("VarIdParameter", idValue, guidDataValueType)

                           .WithParameter("TextParameter", "Украина")

                           .WithVarParameter("VarTextParameter", "Украина", textDataValueType)

                           .WithParameter("IntegerParameter", 10)

                           .WithVarParameter("VarIntegerParameter", 10, integerDataValueType)

                           .WithParameter("FloatParameter", 3.14)

                           .WithVarParameter("VarFloatParameter", 3.14, floatDataValueType)

                           .WithParameter("BooleanParameter", true)

                           .WithVarParameter("VarBooleanParameter", false, booleanDataValueType)

                           .WithParameter("DateTimeParameter", dateTimeValue)

                           .WithVarParameter("VarDateTimeParameter", dateTimeValue, dateTimeDataValueType)

                           .WithParameter("BinaryParameter", stream)

                           .WithVarParameter("VarBinaryParameter", stream)

                           .WithOutputParameter("ResultParameter", textDataValueType) as StoredProcedure;

storedProcedure.PackageName = Page.UserConnection.DBEngine.SystemPackageName;

storedProcedure.Execute();



Функция вызывается аналогично.

Add comment

Антон Малий,

Я понял, большое спасибо

Вот для функции:

public virtual string GenerateCardCVC(Guid cardId) {
	var udf = new UserDefinedFunction(UserConnection, "fn_GenerateCardCVC");
	udf.WithParameter("pCardId", cardId);
	return udf.ExecuteScalar<string>();
}

 

Показать все комментарии

Помогите сделать запрос функции SQL из Исходного кода c возможностью записи результата и дальнейшим его использованием.

В sql запрос выглядит так :

SELECT  dbo.fn_dateadd_work ('minute', 20, defValues)

 

Нравится

16 комментариев

Функцию с одним возвращаемым значением можно запустить так:

string number = "12345";
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
	UserDefinedFunction userDefinedFunction = new UserDefinedFunction(userConnection, "fn_GetPhoneNumberSearchForm")
		.WithParameter("SourcePhoneNumber", number) as UserDefinedFunction;
	userDefinedFunction.PackageName = userConnection.DBEngine.SystemPackageName;
	userDefinedFunction.ReturnType = UserDefinedFunctionReturnType.Scalar;
	searchNumber = userDefinedFunction.ExecuteScalar<string>(dbExecutor);
}

Если же функция возвращает много значений:

using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
				UserDefinedFunction userDefinedFunction = new UserDefinedFunction(userConnection, "fn_GetFreeIntervalsForContactToDeadline")
					.WithParameter("ContactId", ContactId)
					.WithParameter("CalendarId", calendarId)
					.WithParameter("FromDate", userConnection.CurrentUser.GetCurrentDateTime())
					.WithParameter("Deadline", deadLineDate.ToString("yyyy-MM-dd HH:mm:ss"))
					.WithParameter("Offset", userConnection.CurrentUser.TimeZone.BaseUtcOffset.TotalMinutes) as UserDefinedFunction;
 
				userDefinedFunction.PackageName = userConnection.DBEngine.SystemPackageName;
				userDefinedFunction.ReturnType = UserDefinedFunctionReturnType.Table;
				using(IDataReader dataReader = userDefinedFunction.ExecuteReader(dbExecutor)) {
					while (dataReader.Read()) {
						DateTime startDate = dataReader.GetColumnValue<DateTime>("StartDate");
						DateTime endDate = dataReader.GetColumnValue<DateTime>("EndDate");
						//...
					}
				}
			}
string datepart = "day";
var defValues3 = "";
var defValues5 = "";
 
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
                    UserDefinedFunction userDefinedFunction = new UserDefinedFunction(userConnection, "fn_dateadd_work ")
                            .WithParameter("datepart", datepart) 
            				.WithParameter("number", 3)
            				.WithParameter("date", defValues.StartDate)as UserDefinedFunction;
                    userDefinedFunction.PackageName = userConnection.DBEngine.SystemPackageName;
                    userDefinedFunction.ReturnType = UserDefinedFunctionReturnType.Scalar;
                    defValues3 = userDefinedFunction.ExecuteScalar<string>(dbExecutor);
            }
 

Выдает ошибку:
Exception Message: Не удалось найти столбец "dbo", определяемую пользователем функцию или агрегатную функцию "dbo.fn_dateadd_work ". Также возможно, имя является неоднозначным.
Exception Type: System.Data.SqlClient.SqlException
Exception Source: .Net SqlClient Data Provider

Функция Скалярная

Это значит, что такой функции у Вас в базе нет. Возможно, дело в пробеле в конце.

"Зверев Александр" написал:

Это значит, что такой функции у Вас в базе нет. Возможно, дело в пробеле в конце.

			string datepart = "day";
			var defValues3 = "";
			var defValues5 = "";
            using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
            UserDefinedFunction userDefinedFunction = new UserDefinedFunction(userConnection, "fn_dateadd_work")
                .WithParameter("datepart", datepart) 
				.WithParameter("number", 3)
				.WithParameter("date", defValues.StartDate)as UserDefinedFunction;
            userDefinedFunction.PackageName = userConnection.DBEngine.SystemPackageName;
            userDefinedFunction.ReturnType = UserDefinedFunctionReturnType.Scalar;
            defValues3 = userDefinedFunction.ExecuteScalar<string>(dbExecutor);
			}

Убарала пробел, ошибка та же. Функция в Бд есть.
В инете пишут, что возможно фн табличная и к ней так и нужно обращаться...
http://www.cyberforum.ru/sql-server/thread735489.html

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

"Зверев Александр" написал:

Может, на функцию нет прав на запуск у пользователя, который прописан в конфиге ConnectionStrings.

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


Спасибо, проблема в правах.

А можно как то в исходном коде реализовать такой вызов функции:

select * from dbo.fn_SomeFunctionName()  where someColumnName>2

Руслан, если через UserDefinedFunction такое нельзя, можно воспользоваться CustomQuery.

Хорошо, а через Select можно? Если да то как?

Пробовал так, не работает.

var select = (Select)new Select(_userConnection)
    .Column("Id")
    .From("fn_GetContactDescendantRoles")
    .Where("level")
    .IsEqual(Column.Parameter(1));
select.Parameters.Add("contact", new Guid("40445C52-FFD1-4DBA-94D8-127A7B29806A"));
select.Parameters.Add("parentRole", new Guid("CFB6F756-B740-44AD-A221-80C5E441EBF4"));

Выходит ошибка:

System.Data.SqlClient.SqlException: "Параметры функции "dbo.fn_GetContactDescendantRoles" не были предоставлены."

Руслан, я имел в виду не Select, а CustomQuery.

В Вашей попытке через Select, вероятно, это получаются параметры не функции, а всего запроса (как при запросе на выборку из обычной таблицы). Проверить можно, сгенерировав перед выполнением текст запроса при помощи GetSqlText().

Зверев Александр,

Я понял что вы имели ввиду, но мне не хочется делать приложение жестко привязанным к базе данных MSSQL. Ожидаю что использование класса Select оставит приложение "кроссплатформенным". Поэтому предположил что можно использовать Select как при обычном обращении к таблице, т.к. функция возвращает таблицу.

Руслан, в справке написано:

Класс Terrasoft.Core.DB.Select предназначен для построения запросов выборки записей из таблиц базы данных. 

Для работы с  функциями используют другой класс, UserDefinedFunction . Для произвольных запросов — CustomQuery.

Зверев Александр,

Это все замечательно. Но в sql существует возможность выполнения такой конструкции:

select * from dbo.fn_SomeFunctionName()  where someColumnName&gt;2

Вы сказали что это не предусмотрено в классе UserDefinedFunction. Конечно моя функция не возвращает +100500 записей и можно было просто пробежаться по нужным в цикле, но я хочу задать условие на стороне сервера, чтобы он вернул только нужные по условию where. Вроде мелочь, но если количество запросов будет большое, то и сетевой трафик вырастет. В общем я за оптимизацию. Есть ли возможность в будущем расширить функционал данного класса?

В SQL можно и drop database выполнить. Возможность есть (но для этого нужно иметь доступ к исходникам ядра системы), больше вопрос, есть ли востребованность. Обходной вариант я предложил. Думаю, написать три строчки вызова для разных БД и получив ConnectionString (например, как тут в комментарии) выбрать нужную проще, чем вносить изменения в классы ядра.

Завёл идею, но не уверен, что до неё скоро доберутся.

Спасибо, Александр, за идею. Согласен что доберутся не скоро, но пусть будет, надеюсь это будет полезно еще кому-нибудь. По поводу получения строки соединения тоже спасибо, интересная возможность. 

Показать все комментарии

Добрый день !
Подскажите пожалуйста как посмотреть стек вызова функции при отладке ?

Нравится

2 комментария

Если используете для отладки Visual Studio, там есть окно «Call Stack». Если оно не открыто, нажмите Ctrl+Alt+C.

спасибо

Показать все комментарии

Как правильно создавать свои функции в БД, так что бы у всех пользователей были на нее права (и у вновь созданных)?

Нравится

2 комментария

Как радикальный вариант GRANT EXECUTE ON fn_yourfunction TO public
ps если функция скалярная

Александр, спасибо, подходит.

Показать все комментарии

Продолжая тему (http://www.community.terrasoft.ru/blogs/7301), поднятую мной же, выкладываю как скрипт, формирующий документ с описанием функций, так и сам документ.

На данный момент описан пока только скрипт scr_DB (в планах описать, хотя бы частично, scr_WindowUtils). По остальным, наиболее используемым скриптам, надеюсь, мне помогут все желающие. :)

Правила формирования описаний функций:
1. Описываемые скрипты прописываются в параметре ScriptsArray (в самом верху скрипта scr_ConfigurationFunctionsDocument). На данный момент, как я писал выше, там прописан только scr_DB.
2. Для формирования комментариев к функциям следует придерживаться следующего формата:

/* Описание назначения функции
Входящие параметры:
Param1 - описание параметра;
Param2 - описание параметра
*/

function MyFunction(Param1, Param2) {
   ...
}

Сам скрипт scr_ConfigurationFunctionsDocument пробегается по всему содержимому и "выдергивает" комментарии перед функциями, обрамленные "/*" и "*/".

Примечание: Прикрепляю scr_DB с комментариями, предназначенный для того, кто хочет сформировать такой документ на своей конфигурации. С этим связано и предупреждение. Если в scr_DB Вами добавлялись какие-то функции, то тогда Вам нужно будет слить Ваш текущий вариант этого сервиса и тот, что я прикрепил. Будьте, пожалуйста, внимательны, поскольку скрипт scr_DB используется везде, во всей системе, и потеря в сервисе одной функции может привести к некорректной работе всей системы. Просьба делать бекап перед такими экспериментами.

Нравится

Поделиться

2 комментария

Стас, спасибо большое!

Интересно, как повлияет на быстродействие системы добавление строчек с описанием функций в общеиспользованные скрипты? Насколько это способно затормозить систему? Не раз сталкивался с тем фактом, что большие скрипты или большое количество подключаемых скриптов замедляют работу системы.

Еще одна интересная мысль для проверки: при загрузке текста скриптов в оперативную память загружаются ли комментарии или они игнорируются? То есть, влияет ли количество комментариев в скрипте на объем используемой памяти при его загрузке?

Показать все комментарии

Коллеги!
Есть небольшой совет (пожелание) - осторожно используйте субд-шные функции в проектах. А именно, старайтесь их не использовать в запросах датасетов деталей, а тем более в датасетах разделов через CustomSQLColumn. Запросы вида

SELECT tbl_Account.ID,
       tbl_Account.Name,
       dbo.tsf_MyFunction(tbl_Account.ID)
FROM tbl_Account

недопустимы, так как очень часто это приводить к огромным "тормозам" при выполнении запроса. БУДЬТЕ ОСТОРОЖНЫ!

Нравится

Поделиться

0 комментариев
Показать все комментарии