Creating User Defined Function (T-SQL) programmatically into database

qwedster

Hola!

In the following code snippet, I am creating User Defined Function (T-SQL) programmatically into database from C# Code:

BTW, this code originally I wrote for creating StoredProcedure programmatically, which worked fine. Now, I am trying to create User Defined Function programmatically (by just replacing "StoredProcedure" class with "UserDefinedFunction" class).

The User Defined Function checks if "FirstName" exists in "Customer" table in the database and returns 0 else -1.

However, I get an exception on my query string (string SqlQuery). Please help.

I am using Visual Studio 2008 (.NET 3.5 Framework) and SQL Server Express 2008.

Code snippet:

Expand|Select|Wrap|Line Numbers

 using System;

using System.Data;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

// Add Reference: 

// System.configuration

// Microsoft.SqlServer.ConnectionInfo

// Microsoft.SqlServer.Management.Sdk.Sfc 

// Microsoft.SqlServer.Smo

// Microsoft.SqlServer.SqlEnum
 
using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;
 
namespace CS2008ProgrammaticStoredProcedure

{

    public partial class Form1 : Form

    {

        public Form1()

         {

             InitializeComponent();

         }
 
        private string ConnectionString

        {

            get

            {

                ConnectionStringSettingsCollection ConnectionStrings = ConfigurationManager.ConnectionStrings;

                return ConnectionStrings["ConnectionString"].ConnectionString;

            }

        }
 
        private void ClearFUNCTION()

        {

            try

            {

                string SqlQuery = "IF OBJECT_ID (N'CheckFirstName', N'FN') IS NOT NULL ";

                      SqlQuery += "DROP FUNCTION dbo.CheckFirstName; ";
 
                //string SqlQuery = "IF EXISTS (SELECT name FROM sys.objects WHERE NAME = N'CheckFirstName') ";

                      //SqlQuery += "DROP FUNCTION dbo.CheckFirstName; ";
 
                using(SqlConnection ConnectionSql = new SqlConnection(ConnectionString))

                {

                    SqlCommand CommandSql = new SqlCommand(SqlQuery, ConnectionSql);
 
                    CommandSql.Connection.Open();

                    CommandSql.ExecuteNonQuery();

                    CommandSql.Connection.Close();

                }
 
                MessageBox.Show("Checked for FUNCTION CheckFirstName.");

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }
 
        private void CreateFUNCTION()

        {

            try

            {

                using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))

                {

                    //Microsoft.SqlServer.Management.Smo.Server

                    //Microsoft.SqlServer.Management.Common.ServerConnection

                    Server Srvr = new Server(new ServerConnection(ConnectionSql));
 
                    //Microsoft.SqlServer.Management.Smo.Database

                    Database Datbase = Srvr.Databases["master"];
 
                    //Microsoft.SqlServer.Management.Smo.UserDefinedFunction

                    UserDefinedFunction UDF = new UserDefinedFunction(Datbase, "CheckFirstName");
 
                    UDF.TextMode = false;

                    UDF.AnsiNullsStatus = false;

                    UDF.QuotedIdentifierStatus = false;
 
                    UDF.FunctionType = UserDefinedFunctionType.Unknown;
 
                    //Microsoft.SqlServer.Management.Smo.StoredProcedureParameter //Microsoft.SqlServer.Management.Smo.DataType

                    UserDefinedFunctionParameter Parameter = new UserDefinedFunctionParameter(UDF, "@FirstName", DataType.NVarCharMax);

                    UDF.Parameters.Add(Parameter);
 
                    string SqlQuery = "BEGIN ";

                          SqlQuery += "DECLARE @Result [INT] ";

                          SqlQuery += "IF EXISTS ";

                          SqlQuery += "(";

                          SqlQuery += "SELECT ";

                          SqlQuery += "NULL ";

                          SqlQuery += "FROM ";

                          SqlQuery += "dbo.Customer WITH (UPDLOCK) ";

                          SqlQuery += "WHERE ";

                          //SqlQuery += "ISNULL(FirstName, 'NULL') = ISNULL(@FirstName, 'NULL') ";

                          SqlQuery += "("; //

                          SqlQuery += "FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) "; //

                          SqlQuery += ") ";

                          SqlQuery += "BEGIN ";

                          SqlQuery += "SELECT @Result = 0 ";

                          SqlQuery += "END ";

                          SqlQuery += "ELSE ";

                          SqlQuery += "BEGIN ";

                          SqlQuery += "SELECT @Result = -1 ";

                          SqlQuery += "END ";

                          SqlQuery += "RETURN @Result ";

                          SqlQuery += "END; ";
 
                    UDF.TextBody = SqlQuery;
 
                    UDF.Create(); // Exception
 
                    MessageBox.Show("Function CheckFirstName Created.");

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }
 
        private void Form1_Load(object sender, EventArgs e)

        {

            ClearFUNCTION();

            CreateFUNCTION();

        }

    }

}
 
SQL Query:
 
USE [master];
 
--IF OBJECT_ID ('Customer', N'U') IS NOT NULL

--DROP TABLE dbo.Customer;

--GO
 
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'Customer')

DROP TABLE dbo.Customer;

GO
 
CREATE TABLE dbo.Customer

(

 CustomerID INT IDENTITY(1,1) NOT NULL,

 FirstName NVARCHAR(MAX),

 LastName NVARCHAR(MAX),

    Country NVARCHAR(MAX)

    --CONSTRAINT PK_CustomerID PRIMARY KEY(CustomerID)

) 

GO
 
INSERT INTO dbo.Customer(FirstName, LastName, Country)

VALUES('Bill', 'Gates', 'USA');

GO
 
INSERT INTO dbo.Customer(FirstName, LastName, Country)

VALUES('Larry', 'Page', 'USA');

GO
 
INSERT INTO dbo.Customer(FirstName, LastName, Country)

VALUES('Barrack', 'Obama', 'USA');

GO
 
INSERT INTO dbo.Customer(FirstName, LastName, Country)

VALUES('Stephen', 'Harper', 'Canada');

GO
 
SELECT * FROM dbo.Customer
 
--IF OBJECT_ID (N'CheckFirstName', N'FN') IS NOT NULL --

--DROP FUNCTION dbo.CheckFirstName;

--GO
 
IF EXISTS(SELECT name FROM sys.objects WHERE name = N'CheckFirstName')

DROP FUNCTION dbo.CheckFirstName;

GO
 
CREATE FUNCTION dbo.CheckFirstName

(

@FirstName NVARCHAR(MAX)

)

RETURNS [INT] --

WITH EXECUTE AS CALLER --

AS

BEGIN 

DECLARE @Result [INT] 

IF EXISTS 

(

SELECT NULL FROM dbo.Customer WITH (UPDLOCK) 

WHERE (FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) 

) 

BEGIN 

SELECT @Result = 0 

END 

ELSE 

BEGIN 

SELECT @Result = -1 

END 

RETURN @Result 

END 

GO
 
DECLARE @ResultValue [INT]

EXECUTE  @ResultValue = dbo.CheckFirstName @FirstName = 'Bill'

SELECT @ResultValue AS ResultValue;

GO
 
App.config:
 
<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="ConnectionString" connectionString="Persist Security Info=false;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;" />

  </connectionStrings>

</configuration>
 
Here's the exception:
 
Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for UserDefinedFunction 'dbo.CheckFirstName'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'AS'.

A RETURN statement with a return value cannot be used in this context.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

   --- End of inner exception stack trace ---

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingOptions so)

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

   --- End of inner exception stack trace ---

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

   at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.Create()

   at CS2008ProgrammaticStoredProcedure.Form1.CreateFUNCTION() in C:\Documents and Settings\User\Desktop\CS2008ProgrammaticStoredProcedure\CS2008ProgrammaticStoredProcedure\Form1.cs:line 120
 
I don't want this:
 
       private void CreateFUNCTION()

        {

            try

            {

                using (SqlConnection ConnectionSql = new SqlConnection(Config.ConnectionString))

                {

                    string SqlQuery = "CREATE FUNCTION dbo.CheckFirstName ( ";

                    SqlQuery += "@FirstName NVARCHAR(MAX) ) ";

                    SqlQuery += "RETURNS [INT] WITH EXECUTE AS CALLER ";

                    SqlQuery += "BEGIN ";

                    SqlQuery += "DECLARE @Result [INT] ";

                    SqlQuery += "IF EXISTS ";

                    SqlQuery += "(";

                    SqlQuery += "SELECT ";

                    SqlQuery += "NULL ";

                    SqlQuery += "FROM ";

                    SqlQuery += "dbo.Customer WITH (UPDLOCK) ";

                    SqlQuery += "WHERE ";

                    //SqlQuery += "ISNULL(FirstName, 'NULL') = ISNULL(@FirstName, 'NULL') ";

                    SqlQuery += "("; //

                    SqlQuery += "FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) "; //

                    SqlQuery += ") ";

                    SqlQuery += "BEGIN ";

                    SqlQuery += "SELECT @Result = 0 ";

                    SqlQuery += "END ";

                    SqlQuery += "ELSE ";

                    SqlQuery += "BEGIN ";

                    SqlQuery += "SELECT @Result = -1 ";

                    SqlQuery += "END ";

                    SqlQuery += "RETURN @Result ";

                    SqlQuery += "END; ";
 
                    ConnectionSql.Open();

                    SqlCommand ComandSql = new SqlCommand(SqlQuery, ConnectionSql);
 
                    ComandSql.ExecuteNonQuery();

                    MessageBox.Show("Function CheckFirstName Created.");

                }

            }

            catch (Exception ex)

            {

               MessageBox.Show(ex.ToString());

            }

        }

tlhintoq

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

Creating User Defined Function (T-SQL) programmatically into database

re: Creating User Defined Function (T-SQL) programmatically into database

More