VS 2012 SSDT使用IEnumerable生成CLR生成语法失败 [英] VS 2012 SSDT build CLR with IEnumerable failing on generated syntax
本文介绍了VS 2012 SSDT使用IEnumerable生成CLR生成语法失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试重复使用此处。使用c＃添加新的代码（如下所示）后似乎还不错。
但是，当我去发布数据库时，代码生成器似乎无法识别IEnumerable类型并最终产生错误。下面生成的代码中的实际错误（AS附近的不正确语法）是显而易见的，所以我的问题是如何让SSDT生成正确的代码并避免错误的原因？（我认为我可以手动添加CLR，但是，我希望从SSDT进行所有操作）
当前生成：
创建函数[dbo]。[RegExMatches]（@sourceString [nvarchar]（4000），@ pattern [nvarchar]（4000））
返回/ *错误：类型不受支持。 * /
作为外部名称[CampaignStrategyStaging]。[SQLRegEx]。[RegExMatches];
应生成以下内容：
创建函数[dbo]。[RegExMatches]（@sourceString [nvarchar]（4000），@pattern [nvarchar]（4000））
返回表（
[rowId] int，-RowId作为其ID的每一行
[matchId] int，特定匹配的--ID（从1开始）
[groupId] int，RegEx匹配中特定组的--ID （GroupID = 0）表示完全匹配
[value] nvarchar（4000）-组
的值），并以调用方
作为外部名称[CampaignStrategyStaging]。[SQLRegEx]。 [RegExMatches]；
CLR的C＃：
使用系统；
使用System.Data;
使用System.Data.SqlClient;
使用System.Data.SqlTypes;
使用Microsoft.SqlServer.Server;
使用System.Text.RegularExpressions;
使用System.Collections;
公共类SQLRegEx
{
私有类RegExRow
{
///< summary>
///用于将RegExMatches的匹配传递给FillRow方法的私有类
///< / summary>
///< param name = rowId>该行的ID< / param>
///< param name = matchId>匹配的ID< / param>
///< param name = groupID>匹配项内的组的ID< / param>
///< param name = value>特定组的值< / param>
public RegExRow（int rowId，int matchId，int groupID，字符串值）
{
RowId = rowId;
MatchId = matchId;
GroupID = groupID;
价值=价值；
}
public int RowId;
public int MatchId;
public int GroupID；
公共字符串值；
}
///< summary>
///在源字符串上应用正则表达式，并从指定的匹配项中返回特定组的值
///< / summary>
///< param name = sourceString>应在其上应用正则表达式的源字符串< / param>
///< param name = pattern>正则表达式pattern< / param>
///< param name = matchId>要返回的匹配项的ID 1基于inex的< / param>
///< param name = groupId>来自匹配项中的组的ID，以返回。 GroupID 0返回完全匹配< / param>。
///< returns>来自匹配项内的组的值< / returns>
[SqlFunction（IsDeterministic = true）]
公共静态SqlChars RegExMatch（字符串sourceString，字符串模式，int matchId，int groupId）
{
Match m = null;
Regex r =新的Regex（pattern，RegexOptions.Compiled）;
if（matchId == 1）
{
m = r.Match（sourceString）;
}
else if（matchId> 1）
{
MatchCollection mc = r.Matches（sourceString）;
if（mc！= null&& mc.Count&matchId-1）
{
m = mc [matchId-1];
}
else
{
m = null;
}
/// m = mc！= null&&计数> matchId – 1？ mc [matchId-1]：null；
}
return m！= null&& m.Groups.Count> groupId？新的SqlChars（m.Groups [groupId] .Value）：SqlChars.Null;
}
///< summary>
///将正则表达式应用于源字符串并返回所有匹配项和组
///< / summary>
///< param name = sourceString>应在其上应用正则表达式的源字符串< / param>
///< param name = pattern>正则表达式pattern< / param>
///< returns>返回代表组值的RegExRows列表< / returns>
[SqlFunction（FillRowMethodName = FillRegExRow）]
公共静态IEnumerable RegExMatches（string sourceString，string pattern）
{
Regex r = new Regex（pattern，RegexOptions.Compiled） ;
int rowId = 0;
int matchId = 0;
foreach（在r.Matches（sourceString）中匹配m）
{
matchId ++;
for（int i = 0; i< m.Groups.Count; i ++）
{
yield return new RegExRow（++ rowId，matchId，i，m.Groups [i] 。值）;
}
}
}
///< summary>
/// FillRow方法填充输出表
///< / summary>
///< param name = obj> RegExRow作为对象传递< / param>
///< param name = rowId> ID或返回的行< / param>
///< param name = matchId>返回的Match的ID< / param>
///< param name = groupID> Match< / param>中的组ID。
///< param name = value>小组的价值< / param>
public static void FillRegExRow（Object obj，out int rowId，out int matchId，out int groupID，out SqlChars value）
{
RegExRow r =（RegExRow）obj;
rowId = r.RowId;
matchId = r.MatchId;
groupID = r.GroupID;
value =新的SqlChars（r.Value）;
}
}
解决方案
在同事的帮助下，我发现CLR需要进行两项更改：
-
SQLFunction []声明需要包括一个TableDefinition
参数，如此处。 （代码如下所示）[SqlFunction（FillRowMethodName = FillRegExRow，
TableDefinition = [rowId] int ，[matchId] int，[groupId] int，[value] nvarchar（4000））]
公共静态IEnumerable RegExMatches（string sourceString，string pattern）
-
int数据类型已更改为SqlInt32。 （解决我最初的问题中的问题可能不是必需的。）
因此，总体代码更改为：
使用系统；
使用System.Data;
使用System.Data.SqlClient;
使用System.Data.SqlTypes;
使用Microsoft.SqlServer.Server;
使用System.Text.RegularExpressions;
使用System.Collections;
公共类SQLRegEx
{
私有类RegExRow
{
///< summary>
///用于将RegExMatches的匹配传递给FillRow方法的私有类
///< / summary>
///< param name = rowId>该行的ID< / param>
///< param name = matchId>匹配的ID< / param>
///< param name = groupID>匹配项内的组的ID< / param>
///< param name = value>特定组的值< / param>
公共RegExRow（SqlInt32 rowId，SqlInt32 matchId，SqlInt32 groupID，字符串值）
{
RowId = rowId;
MatchId = matchId;
GroupID = groupID;
价值=价值；
}
public SqlInt32 RowId;
public SqlInt32 MatchId;
public SqlInt32 GroupID;
公共字符串值；
}
///< summary>
///在源字符串上应用正则表达式，并从指定的匹配项中返回特定组的值
///< / summary>
///< param name = sourceString>应在其上应用正则表达式的源字符串< / param>
///< param name = pattern>正则表达式pattern< / param>
///< param name = matchId>要返回的匹配项的ID 1基于inex的< / param>
///< param name = groupId>来自匹配项中的组的ID，以返回。 GroupID 0返回完全匹配< / param>。
///< returns>来自匹配项内的组的值< / returns>
[SqlFunction（IsDeterministic = true）]
公共静态SqlChars RegExMatch（字符串sourceString，字符串模式，int matchId，int groupId）
{
Match m = null;
Regex r =新的Regex（pattern，RegexOptions.Compiled）;
if（matchId == 1）
{
m = r.Match（sourceString）;
}
else if（matchId> 1）
{
MatchCollection mc = r.Matches（sourceString）;
if（mc！= null&& mc.Count&matchId-1）
{
m = mc [matchId-1];
}
else
{
m = null;
}
/// m = mc！= null&&计数> matchId – 1？ mc [matchId-1]：null；
}
return m！= null&& m.Groups.Count> groupId？新的SqlChars（m.Groups [groupId] .Value）：SqlChars.Null;
}
///< summary>
///将正则表达式应用于源字符串并返回所有匹配项和组
///< / summary>
///< param name = sourceString>应在其上应用正则表达式的源字符串< / param>
///< param name = pattern>正则表达式pattern< / param>
///< returns>返回代表组值的RegExRows列表< / returns>
///
[SqlFunction（FillRowMethodName = FillRegExRow，
TableDefinition = rowId int，[matchId] int，[groupId] int，[value] nvarchar（4000 ））]
公共静态IEnumerable RegExMatches（字符串sourceString，字符串模式）
{
Regex r = new Regex（pattern，RegexOptions.Compiled）;
int rowId = 0;
int matchId = 0;
foreach（在r.Matches（sourceString）中匹配m）
{
matchId ++;
for（int i = 0; i< m.Groups.Count; i ++）
{
++ rowId;
yield return new RegExRow（rowId，matchId，i，m.Groups [i] .Value）;
}
}
}
///< summary>
/// FillRow方法填充输出表
///< / summary>
///< param name = obj> RegExRow作为对象传递< / param>
///< param name = rowId> ID或返回的行< / param>
///< param name = matchId>返回的Match的ID< / param>
///< param name = groupID> Match< / param>中的组ID。
///< param name = value>小组的价值< / param>
public static void FillRegExRow（Object obj，SqlInt32 rowId，SqlInt32 matchId，SqlInt32 groupID，SqlChars value）
{
RegExRow r =（RegExRow）obj;
rowId = r.RowId;
matchId = r.MatchId;
groupID = r.GroupID;
value =新的SqlChars（r.Value）;
}
}
I'm attempting to reuse some code found here. After adding a new using the c# (shown below) seems to be fine.
When I go to publish the database, however, the code generator does not seem to recognize the IEnumerable type and ends up producing an error. The actual error (Incorrect Syntax near AS) from the generated code below is obvious, so my question is how can I get SSDT to generate the correct code and avoid the cause of the error? (I assume that I can add the CLR manually, however, I would prefer to do everything from SSDT)
Currently Generates:
CREATE FUNCTION [dbo].[RegExMatches] (@sourceString [nvarchar](4000), @pattern [nvarchar](4000))
RETURNS /* Error: Unsupported type. */
AS EXTERNAL NAME [CampaignStrategyStaging].[SQLRegEx].[RegExMatches];
Should Generate something like:
CREATE FUNCTION [dbo].[RegExMatches] (@sourceString [nvarchar](4000), @pattern [nvarchar](4000))
RETURNS TABLE (
[rowId] int, --RowId each row as it`s ID
[matchId] int, --ID of particular match (starts from 1)
[groupId] int, --ID of particular group in RegEx match (GroupID = 0) represents a complete match
[value] nvarchar(4000) --value of the group
) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [CampaignStrategyStaging].[SQLRegEx].[RegExMatches];
The C# for the CLR:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
public class SQLRegEx
{
private class RegExRow
{
/// <summary>
/// Private class for passing matches of the RegExMatches to the FillRow method
/// </summary>
/// <param name="rowId">ID of the Row</param>
/// <param name="matchId">ID of the Match</param>
/// <param name="groupID">ID of the Group within the Match</param>
/// <param name="value">Value of the particular group</param>
public RegExRow(int rowId, int matchId, int groupID, string value)
{
RowId = rowId;
MatchId = matchId;
GroupID = groupID;
Value = value;
}
public int RowId;
public int MatchId;
public int GroupID;
public string Value;
}
/// <summary>
/// Applies Regular Expression on the Source string and returns value of particular group from withing a specified match
/// </summary>
/// <param name="sourceString">Source string on which the regular expression should be applied</param>
/// <param name="pattern">Regular Expression pattern</param>
/// <param name="matchId">ID of the Match to be returned 1 inex-based</param>
/// <param name="groupId">ID of the group from within a match to return. GroupID 0 returns complete match</param>
/// <returns>Value of the Group from within a Match</returns>
[SqlFunction(IsDeterministic=true)]
public static SqlChars RegExMatch(string sourceString, string pattern, int matchId, int groupId)
{
Match m = null;
Regex r = new Regex(pattern, RegexOptions.Compiled);
if (matchId == 1)
{
m = r.Match(sourceString);
}
else if (matchId > 1)
{
MatchCollection mc = r.Matches(sourceString);
if (mc!=null && mc.Count > matchId-1)
{
m = mc[matchId-1];
}
else
{
m= null;
}
///m = mc != null && mc.Count > matchId – 1 ? mc[matchId - 1] : null;
}
return m != null && m.Groups.Count > groupId ? new SqlChars(m.Groups[groupId].Value) : SqlChars.Null;
}
/// <summary>
/// Applies Regular Expression o the Source strings and return all matches and groups
/// </summary>
/// <param name="sourceString">Source string on which the regular expression should be applied</param>
/// <param name="pattern">Regular Expression pattern</param>
/// <returns>Returns list of RegExRows representing the group value</returns>
[SqlFunction(FillRowMethodName = "FillRegExRow")]
public static IEnumerable RegExMatches(string sourceString, string pattern)
{
Regex r = new Regex(pattern, RegexOptions.Compiled);
int rowId = 0;
int matchId = 0;
foreach (Match m in r.Matches(sourceString))
{
matchId++;
for (int i = 0; i < m.Groups.Count; i++)
{
yield return new RegExRow(++rowId, matchId, i, m.Groups[i].Value);
}
}
}
/// <summary>
/// FillRow method to populate the output table
/// </summary>
/// <param name="obj">RegExRow passed as object</param>
/// <param name="rowId">ID or the returned row</param>
/// <param name="matchId">ID of returned Match</param>
/// <param name="groupID">ID of group in the Match</param>
/// <param name="value">Value of the Group</param>
public static void FillRegExRow(Object obj, out int rowId, out int matchId, out int groupID, out SqlChars value)
{
RegExRow r = (RegExRow)obj;
rowId = r.RowId;
matchId = r.MatchId;
groupID = r.GroupID;
value = new SqlChars(r.Value);
}
}
解决方案
After some help from a co-worker I discovered that two changes were needed in the CLR:
the SQLFunction[] declaration needed to include a TableDefinition argument as shown in the example on here. (code is shown below)
[SqlFunction(FillRowMethodName = "FillRegExRow", TableDefinition = "[rowId] int,[matchId] int,[groupId] int, [value] nvarchar(4000)")] public static IEnumerable RegExMatches(string sourceString, string pattern)int data types in RegExRow.RegExRow were changed to SqlInt32. (This might not have been necessary to resolve the issue in my original question).
So the overall code changed to:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;
public class SQLRegEx
{
private class RegExRow
{
/// <summary>
/// Private class for passing matches of the RegExMatches to the FillRow method
/// </summary>
/// <param name="rowId">ID of the Row</param>
/// <param name="matchId">ID of the Match</param>
/// <param name="groupID">ID of the Group within the Match</param>
/// <param name="value">Value of the particular group</param>
public RegExRow(SqlInt32 rowId, SqlInt32 matchId, SqlInt32 groupID, string value)
{
RowId = rowId;
MatchId = matchId;
GroupID = groupID;
Value = value;
}
public SqlInt32 RowId;
public SqlInt32 MatchId;
public SqlInt32 GroupID;
public string Value;
}
/// <summary>
/// Applies Regular Expression on the Source string and returns value of particular group from withing a specified match
/// </summary>
/// <param name="sourceString">Source string on which the regular expression should be applied</param>
/// <param name="pattern">Regular Expression pattern</param>
/// <param name="matchId">ID of the Match to be returned 1 inex-based</param>
/// <param name="groupId">ID of the group from within a match to return. GroupID 0 returns complete match</param>
/// <returns>Value of the Group from within a Match</returns>
[SqlFunction(IsDeterministic=true)]
public static SqlChars RegExMatch(string sourceString, string pattern, int matchId, int groupId)
{
Match m = null;
Regex r = new Regex(pattern, RegexOptions.Compiled);
if (matchId == 1)
{
m = r.Match(sourceString);
}
else if (matchId > 1)
{
MatchCollection mc = r.Matches(sourceString);
if (mc!=null && mc.Count > matchId-1)
{
m = mc[matchId-1];
}
else
{
m= null;
}
///m = mc != null && mc.Count > matchId – 1 ? mc[matchId - 1] : null;
}
return m != null && m.Groups.Count > groupId ? new SqlChars(m.Groups[groupId].Value) : SqlChars.Null;
}
/// <summary>
/// Applies Regular Expression o the Source strings and return all matches and groups
/// </summary>
/// <param name="sourceString">Source string on which the regular expression should be applied</param>
/// <param name="pattern">Regular Expression pattern</param>
/// <returns>Returns list of RegExRows representing the group value</returns>
///
[SqlFunction(FillRowMethodName = "FillRegExRow",
TableDefinition = "rowId int,[matchId] int,[groupId] int, [value] nvarchar(4000)")]
public static IEnumerable RegExMatches(string sourceString, string pattern)
{
Regex r = new Regex(pattern, RegexOptions.Compiled);
int rowId = 0;
int matchId = 0;
foreach (Match m in r.Matches(sourceString))
{
matchId++;
for (int i = 0; i < m.Groups.Count; i++)
{
++rowId;
yield return new RegExRow(rowId, matchId, i, m.Groups[i].Value);
}
}
}
/// <summary>
/// FillRow method to populate the output table
/// </summary>
/// <param name="obj">RegExRow passed as object</param>
/// <param name="rowId">ID or the returned row</param>
/// <param name="matchId">ID of returned Match</param>
/// <param name="groupID">ID of group in the Match</param>
/// <param name="value">Value of the Group</param>
public static void FillRegExRow(Object obj, out SqlInt32 rowId, out SqlInt32 matchId, out SqlInt32 groupID, out SqlChars value)
{
RegExRow r = (RegExRow)obj;
rowId = r.RowId;
matchId = r.MatchId;
groupID = r.GroupID;
value = new SqlChars(r.Value);
}
}
这篇关于VS 2012 SSDT使用IEnumerable生成CLR生成语法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
