Creazione, modifica e rimozione delle funzioni definite dall'utente
L'oggetto UserDefinedFunction fornisce funzionalità che consentono agli utenti di gestire a livello di codice le funzioni definite dall'utente in MicrosoftSQL Server. Le funzioni definite dall'utente supportano parametri di input e di output e riferimenti diretti alle colonne delle tabelle.
In SQL Server è necessario registrare gli assembly all'interno di un database prima di poterli utilizzare in stored procedure, funzioni definite dall'utente, trigger e tipi di dati definiti dall'utente. In SMO questa funzionalità è supportata con l'oggetto SqlAssembly.
L'oggetto UserDefinedFunction fa riferimento all'assembly .NET con le proprietà AssemblyName, ClassName e MethodName.
Quando l'oggetto UserDefinedFunction fa riferimento a un assembly .NET, è necessario registrare l'assembly creando un oggetto SqlAssembly e aggiungendolo all'oggetto SqlAssemblyCollection che appartiene all'oggetto Database.
Esempio
Per utilizzare qualsiasi esempio di codice fornito, è necessario scegliere l'ambiente, il modello e il linguaggio di programmazione per la creazione dell'applicazione. Per ulteriori informazioni, vedere Procedura: Creazione di un progetto Visual Basic SMO in Visual Studio .NET o Procedura: Creazione di un progetto Visual C# SMO in Visual Studio .NET.
Creazione di una funzione scalare definita dall'utente in Visual Basic
In questo esempio di codice viene illustrato come creare e rimuovere una funzione scalare definita dall'utente con un parametro dell'oggetto DateTime di input e di un tipo restituito integer in Visual Basic. La funzione definita dall'utente viene creata nel database AdventureWorks. Nell'esempio viene creata una funzione definita dall'utente, ISOweek, che accetta un argomento data per calcolare il numero di settimana ISO. Affinché il calcolo venga eseguito correttamente, è necessario impostare l'opzione DATEFIRST del database su 1 prima di chiamare la funzione.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.
Dim udf As UserDefinedFunction
udf = New UserDefinedFunction(db, "IsOWeek")
'Set the TextMode property to false and then set the other properties.
udf.TextMode = False
udf.DataType = DataType.Int
udf.ExecutionContext = ExecutionContext.Caller
udf.FunctionType = UserDefinedFunctionType.Scalar
udf.ImplementationType = ImplementationType.TransactSql
'Add a parameter.
Dim par As UserDefinedFunctionParameter
par = New UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime)
udf.Parameters.Add(par)
'Set the TextBody property to define the user defined function.
udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;"
'Create the user defined function on the instance of SQL Server.
udf.Create()
'Remove the user defined function.
udf.Drop()
Creazione di una funzione scalare definita dall'utente in Visual C#
In questo esempio di codice viene illustrato come creare e rimuovere una funzione scalare definita dall'utente con un parametro dell'oggetto DateTime di input e di un tipo restituito integer in Visual C#. La funzione definita dall'utente viene creata nel database AdventureWorks. Nell'esempio viene creata la funzione definita dall'utente, ISOweek. Questa funzione calcola il numero di settimana ISO in base a un argomento di data specificato. Affinché il calcolo venga eseguito correttamente, è necessario impostare l'opzione DATEFIRST del database su 1 prima di chiamare la funzione.
//Connect to the local, default instance of SQL Server.
{
Server srv = default(Server);
srv = new Server();
//Reference the AdventureWorks database.
Database db = default(Database);
db = srv.Databases("AdventureWorks");
//Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.
UserDefinedFunction udf = default(UserDefinedFunction);
udf = new UserDefinedFunction(db, "IsOWeek");
//Set the TextMode property to false and then set the other properties.
udf.TextMode = false;
udf.DataType = DataType.Int;
udf.ExecutionContext = ExecutionContext.Caller;
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.ImplementationType = ImplementationType.TransactSql;
//Add a parameter.
UserDefinedFunctionParameter par = default(UserDefinedFunctionParameter);
par = new UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime);
udf.Parameters.Add(par);
//Set the TextBody property to define the user-defined function.
udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;";
//Create the user-defined function on the instance of SQL Server.
udf.Create();
//Remove the user-defined function.
udf.Drop();
}