Create a sql create function statement in C# with the DacFx Framework

[MrChiggles]

Hello community,

i posted this question in the wrong forum. So i try it here again

I have a working sql statement to create a scalar function:

CREATE FUNCTION [schema].[getValue] (@xmlParameter XML)
RETURNS nvarchar(10)
AS
BEGIN
return cast(@xml.query('declare namespace ns="http://GetValue"; local-name((/ns:value/*)[1])') as nvarchar(10));
end
GO


Creation of tables, columns and constraints with the DacFx framework works without problem.

Now i try to implement a create function statement in a c# application (with the DacFx-Framework). But i fail. I'll show you my attempt, but it doesn't seem correct:



var getValueFunctionStatement = new CreateFunctionStatement();

getValueFunctionStatement.Name = TsqlHelper.SchemaObjectName("getValue", "schema");

getValueFunctionStatement.ReturnType = new ScalarFunctionReturnType { DataType = new SqlDataTypeReference { Parameters = { NVarCharLength10.AsLiteral() }, SqlDataTypeOption = SqlDataTypeOption.NVarChar } };



Then i create the function parameter:

var parameter = new ProcedureParameter { DataType = new XmlDataTypeReference(), VariableName = new Identifier { Value = "xmlParameter", QuoteType = QuoteType.NotQuoted}, Nullable = new NullableConstraintDefinition { Nullable = false } };

fktStatement.Parameters.Add(parameter);


Then i create the return statement and the cast call:


var returnStatement = new ReturnStatement();

var castCall = new CastCall();

castCall.DataType = new SqlDataTypeReference { Parameters = { NVarCharLength10.AsLiteral() }, SqlDataTypeOption = SqlDataTypeOption.NVarChar };

// fill the cast call with the expression

returnStatement.Expression = castCall;

fktStatement.StatementList = new StatementList();

fktStatement.StatementList.Statements.Add(returnStatement);




I have several questions:

1. Is the basic procedure correct?

2. Is the definition of a procedure parameter correct?

3. How can i implement the call ("@xmlParamter.query(....)" for the castCall with dacfx in c#?

I hope someone can help me, because there is almost no information to this topic in the internet.

Thank you.

Continue reading...