Hola!
In the following code snippet, I am creating User Defined Function (T-SQL) programmaticall y into database from C# Code:
BTW, this code originally I wrote for creating StoredProcedure programmaticall y, which worked fine. Now, I am trying to create User Defined Function programmaticall y (by just replacing "StoredProcedur e" class with "UserDefinedFun ction" 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:
In the following code snippet, I am creating User Defined Function (T-SQL) programmaticall y into database from C# Code:
BTW, this code originally I wrote for creating StoredProcedure programmaticall y, which worked fine. Now, I am trying to create User Defined Function programmaticall y (by just replacing "StoredProcedur e" class with "UserDefinedFun ction" 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:
Code:
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());
}
}
Comment