By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

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

P: 24
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
  1. using System;
  2. using System.Data;
  3. using System.Windows.Forms;
  4. using System.Data.SqlClient;
  5. using System.Configuration;
  6. // Add Reference: 
  7. // System.configuration
  8. // Microsoft.SqlServer.ConnectionInfo
  9. // Microsoft.SqlServer.Management.Sdk.Sfc 
  10. // Microsoft.SqlServer.Smo
  11. // Microsoft.SqlServer.SqlEnum
  12.  
  13. using Microsoft.SqlServer.Management.Smo;
  14. using Microsoft.SqlServer.Management.Common;
  15.  
  16. namespace CS2008ProgrammaticStoredProcedure
  17. {
  18.     public partial class Form1 : Form
  19.     {
  20.         public Form1()
  21.          {
  22.              InitializeComponent();
  23.          }
  24.  
  25.         private string ConnectionString
  26.         {
  27.             get
  28.             {
  29.                 ConnectionStringSettingsCollection ConnectionStrings = ConfigurationManager.ConnectionStrings;
  30.                 return ConnectionStrings["ConnectionString"].ConnectionString;
  31.             }
  32.         }
  33.  
  34.         private void ClearFUNCTION()
  35.         {
  36.             try
  37.             {
  38.                 string SqlQuery = "IF OBJECT_ID (N'CheckFirstName', N'FN') IS NOT NULL ";
  39.                       SqlQuery += "DROP FUNCTION dbo.CheckFirstName; ";
  40.  
  41.                 //string SqlQuery = "IF EXISTS (SELECT name FROM sys.objects WHERE NAME = N'CheckFirstName') ";
  42.                       //SqlQuery += "DROP FUNCTION dbo.CheckFirstName; ";
  43.  
  44.                 using(SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
  45.                 {
  46.                     SqlCommand CommandSql = new SqlCommand(SqlQuery, ConnectionSql);
  47.  
  48.                     CommandSql.Connection.Open();
  49.                     CommandSql.ExecuteNonQuery();
  50.                     CommandSql.Connection.Close();
  51.                 }
  52.  
  53.                 MessageBox.Show("Checked for FUNCTION CheckFirstName.");
  54.             }
  55.             catch (Exception ex)
  56.             {
  57.                 MessageBox.Show(ex.ToString());
  58.             }
  59.         }
  60.  
  61.         private void CreateFUNCTION()
  62.         {
  63.             try
  64.             {
  65.                 using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
  66.                 {
  67.                     //Microsoft.SqlServer.Management.Smo.Server
  68.                     //Microsoft.SqlServer.Management.Common.ServerConnection
  69.                     Server Srvr = new Server(new ServerConnection(ConnectionSql));
  70.  
  71.                     //Microsoft.SqlServer.Management.Smo.Database
  72.                     Database Datbase = Srvr.Databases["master"];
  73.  
  74.                     //Microsoft.SqlServer.Management.Smo.UserDefinedFunction
  75.                     UserDefinedFunction UDF = new UserDefinedFunction(Datbase, "CheckFirstName");
  76.  
  77.                     UDF.TextMode = false;
  78.                     UDF.AnsiNullsStatus = false;
  79.                     UDF.QuotedIdentifierStatus = false;
  80.  
  81.                     UDF.FunctionType = UserDefinedFunctionType.Unknown;
  82.  
  83.                     //Microsoft.SqlServer.Management.Smo.StoredProcedureParameter //Microsoft.SqlServer.Management.Smo.DataType
  84.                     UserDefinedFunctionParameter Parameter = new UserDefinedFunctionParameter(UDF, "@FirstName", DataType.NVarCharMax);
  85.                     UDF.Parameters.Add(Parameter);
  86.  
  87.  
  88.                     string SqlQuery = "BEGIN ";
  89.                           SqlQuery += "DECLARE @Result [INT] ";
  90.                           SqlQuery += "IF EXISTS ";
  91.                           SqlQuery += "(";
  92.                           SqlQuery += "SELECT ";
  93.                           SqlQuery += "NULL ";
  94.                           SqlQuery += "FROM ";
  95.                           SqlQuery += "dbo.Customer WITH (UPDLOCK) ";
  96.                           SqlQuery += "WHERE ";
  97.                           //SqlQuery += "ISNULL(FirstName, 'NULL') = ISNULL(@FirstName, 'NULL') ";
  98.                           SqlQuery += "("; //
  99.                           SqlQuery += "FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) "; //
  100.                           SqlQuery += ") ";
  101.                           SqlQuery += "BEGIN ";
  102.                           SqlQuery += "SELECT @Result = 0 ";
  103.                           SqlQuery += "END ";
  104.                           SqlQuery += "ELSE ";
  105.                           SqlQuery += "BEGIN ";
  106.                           SqlQuery += "SELECT @Result = -1 ";
  107.                           SqlQuery += "END ";
  108.                           SqlQuery += "RETURN @Result ";
  109.                           SqlQuery += "END; ";
  110.  
  111.                     UDF.TextBody = SqlQuery;
  112.  
  113.                     UDF.Create(); // Exception
  114.  
  115.                     MessageBox.Show("Function CheckFirstName Created.");
  116.                 }
  117.             }
  118.             catch (Exception ex)
  119.             {
  120.                 MessageBox.Show(ex.ToString());
  121.             }
  122.         }
  123.  
  124.         private void Form1_Load(object sender, EventArgs e)
  125.         {
  126.             ClearFUNCTION();
  127.             CreateFUNCTION();
  128.         }
  129.     }
  130. }
  131.  
  132.  
  133. SQL Query:
  134.  
  135. USE [master];
  136.  
  137. --IF OBJECT_ID ('Customer', N'U') IS NOT NULL
  138. --DROP TABLE dbo.Customer;
  139. --GO
  140.  
  141. IF EXISTS (SELECT name FROM sys.tables WHERE name = N'Customer')
  142. DROP TABLE dbo.Customer;
  143. GO
  144.  
  145. CREATE TABLE dbo.Customer
  146. (
  147.  CustomerID INT IDENTITY(1,1) NOT NULL,
  148.  FirstName NVARCHAR(MAX),
  149.  LastName NVARCHAR(MAX),
  150.     Country NVARCHAR(MAX)
  151.     --CONSTRAINT PK_CustomerID PRIMARY KEY(CustomerID)
  152. GO
  153.  
  154. INSERT INTO dbo.Customer(FirstName, LastName, Country)
  155. VALUES('Bill', 'Gates', 'USA');
  156. GO
  157.  
  158. INSERT INTO dbo.Customer(FirstName, LastName, Country)
  159. VALUES('Larry', 'Page', 'USA');
  160. GO
  161.  
  162. INSERT INTO dbo.Customer(FirstName, LastName, Country)
  163. VALUES('Barrack', 'Obama', 'USA');
  164. GO
  165.  
  166. INSERT INTO dbo.Customer(FirstName, LastName, Country)
  167. VALUES('Stephen', 'Harper', 'Canada');
  168. GO
  169.  
  170.  
  171. SELECT * FROM dbo.Customer
  172.  
  173. --IF OBJECT_ID (N'CheckFirstName', N'FN') IS NOT NULL --
  174. --DROP FUNCTION dbo.CheckFirstName;
  175. --GO
  176.  
  177. IF EXISTS(SELECT name FROM sys.objects WHERE name = N'CheckFirstName')
  178. DROP FUNCTION dbo.CheckFirstName;
  179. GO
  180.  
  181. CREATE FUNCTION dbo.CheckFirstName
  182. (
  183. @FirstName NVARCHAR(MAX)
  184. )
  185. RETURNS [INT] --
  186. WITH EXECUTE AS CALLER --
  187. AS
  188. BEGIN 
  189. DECLARE @Result [INT] 
  190. IF EXISTS 
  191. (
  192. SELECT NULL FROM dbo.Customer WITH (UPDLOCK) 
  193. WHERE (FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) 
  194. BEGIN 
  195. SELECT @Result = 0 
  196. END 
  197. ELSE 
  198. BEGIN 
  199. SELECT @Result = -1 
  200. END 
  201. RETURN @Result 
  202. END 
  203. GO
  204.  
  205. DECLARE @ResultValue [INT]
  206. EXECUTE  @ResultValue = dbo.CheckFirstName @FirstName = 'Bill'
  207. SELECT @ResultValue AS ResultValue;
  208. GO
  209.  
  210. App.config:
  211.  
  212. <?xml version="1.0" encoding="utf-8" ?>
  213. <configuration>
  214.   <connectionStrings>
  215.     <add name="ConnectionString" connectionString="Persist Security Info=false;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;" />
  216.   </connectionStrings>
  217. </configuration>
  218.  
  219. Here's the exception:
  220.  
  221. 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'.
  222. A RETURN statement with a return value cannot be used in this context.
  223.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  224.    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  225.    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  226.    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  227.    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
  228.    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
  229.    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
  230.    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
  231.    --- End of inner exception stack trace ---
  232.    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
  233.    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
  234.    at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
  235.    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
  236.    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingOptions so)
  237.    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
  238.    --- End of inner exception stack trace ---
  239.    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
  240.    at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.Create()
  241.    at CS2008ProgrammaticStoredProcedure.Form1.CreateFUNCTION() in C:\Documents and Settings\User\Desktop\CS2008ProgrammaticStoredProcedure\CS2008ProgrammaticStoredProcedure\Form1.cs:line 120
  242.  
  243.  
  244. I don't want this:
  245.  
  246.        private void CreateFUNCTION()
  247.         {
  248.             try
  249.             {
  250.                 using (SqlConnection ConnectionSql = new SqlConnection(Config.ConnectionString))
  251.                 {
  252.                     string SqlQuery = "CREATE FUNCTION dbo.CheckFirstName ( ";
  253.                     SqlQuery += "@FirstName NVARCHAR(MAX) ) ";
  254.                     SqlQuery += "RETURNS [INT] WITH EXECUTE AS CALLER ";
  255.                     SqlQuery += "BEGIN ";
  256.                     SqlQuery += "DECLARE @Result [INT] ";
  257.                     SqlQuery += "IF EXISTS ";
  258.                     SqlQuery += "(";
  259.                     SqlQuery += "SELECT ";
  260.                     SqlQuery += "NULL ";
  261.                     SqlQuery += "FROM ";
  262.                     SqlQuery += "dbo.Customer WITH (UPDLOCK) ";
  263.                     SqlQuery += "WHERE ";
  264.                     //SqlQuery += "ISNULL(FirstName, 'NULL') = ISNULL(@FirstName, 'NULL') ";
  265.                     SqlQuery += "("; //
  266.                     SqlQuery += "FirstName IS NULL AND @FirstName IS NULL) OR (@FirstName = FirstName) "; //
  267.                     SqlQuery += ") ";
  268.                     SqlQuery += "BEGIN ";
  269.                     SqlQuery += "SELECT @Result = 0 ";
  270.                     SqlQuery += "END ";
  271.                     SqlQuery += "ELSE ";
  272.                     SqlQuery += "BEGIN ";
  273.                     SqlQuery += "SELECT @Result = -1 ";
  274.                     SqlQuery += "END ";
  275.                     SqlQuery += "RETURN @Result ";
  276.                     SqlQuery += "END; ";
  277.  
  278.                     ConnectionSql.Open();
  279.                     SqlCommand ComandSql = new SqlCommand(SqlQuery, ConnectionSql);
  280.  
  281.                     ComandSql.ExecuteNonQuery();
  282.                     MessageBox.Show("Function CheckFirstName Created.");
  283.                 }
  284.             }
  285.             catch (Exception ex)
  286.             {
  287.                MessageBox.Show(ex.ToString());
  288.             }
  289.         }
Jan 25 '10 #1
Share this Question
Share on Google+
1 Reply


tlhintoq
Expert 2.5K+
P: 3,525
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.
Jan 25 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.