Sql-Server

SQL 函式，獲取要發送到 CLR 的值

  • December 14, 2018

我有一個 CLR 可以使用 powershell 從 .net 訪問 RichTextBox 對象。

在我這樣創建的 SQL 函式上：

CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @path [nvarchar](500))
RETURNS 
  nvarchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [abcSQLHelper].[abcSQLHelper.Chinese].TextFromRTF

現在需要一些幫助是取出@path 作為 SQL 函式上的參數（但在 CLR 上仍然需要它）並在函式內部從表中選擇一個值並將其發送到 CLR。

我怎樣才能做到這一點？找不到任何有關此的資訊。

編輯：為了幫助理解這裡發生了什麼，我在 C# 中有一個 CLR，所以我可以將 RTF 轉換為字元串（下面的 RTF 範例）：

{\rtf1\ansi\ansicpg1252\def0\deflang2070{\fonttbl{\f0\fmodern\fprq6\fcharset134 SimSun;}{\f1\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\lang2052\f0\ fs17'b7'f2'c8'cb'b7'eb'b7'f2'c8'cb6346'b8'f6'ba'ec'b5'c4\ ‘b9'f0'bb'a8\lang2070\f1\par}

為了能夠實現這一點，因為它被要求在 SQL 查詢中完成，所以由於中文字元，我必須將它放在 C# 中的 CLR 中。

這是正在發送的內容@rtf。

現在@path，也需要發送到用C#完成的CLR，所以他知道在哪裡寫臨時txt文件（再次需要在powershell上寫腳本的輸出以使用.Net的RichTextBox轉換 RTF）。

現在該函式在查詢中被呼叫為 ex:select dbo.RTF2Text(RTFText, (select filePath from tablexpto)) as teste from RTFTest但已被要求只有 hasselect dbo.RTF2Text(RTFText) as teste from RTFTest

所以在第一個程式碼中，我需要從表中選擇路徑，並且需要在 CLR 中發送參數

希望這有助於清除我需要做的事情，如果沒有，我會嘗試更多地改進它

編輯2：

我需要這個，因為它要求我在 SQL 上執行一個可以接收 RTF 並返回字元串的函式，問題是漢字，確實嘗試了一些我可以在網上找到的東西，但沒有成功。

所以我告訴自己，如果我可以從 .Net 訪問 RichTextBox，我可以更輕鬆地做到這一點，所以我確實使用了 powershell（因為我無法添加 WinForms 程序集來執行此操作……我可以但需要更多的東西）訪問.Net的類RichTextBox並在那裡插入RTF並要求文本並將其放入一個文件並從那裡讀取它（由於中文字元必須​​再次成為一個文件，如果我只是閱讀輸出為?????)

這是我擁有的 CLR，很抱歉一開始沒有提供它。

public class Chinese
{
   /// <summary>
   /// It will receive the source of an RTF and transform into readable text
   /// </summary>
   /// <param name="rtf">Source of the RTF</param>
   /// <param name="path">Directory where the file must be created</param>
   /// <returns>Readable text from RTF</returns>
   [Microsoft.SqlServer.Server.SqlFunction]
   public static string TextFromRTF(string rtf, string path)
   {
       var output = String.Empty;
       try
       {
           var proc = new Process();
           proc.StartInfo.FileName = "cmd.exe";
           proc.StartInfo.Arguments = "/C powershell.exe -ExecutionPolicy ByPass -Command Add-Type \"-AssemblyName System.Windows.Forms; $Rtb = New-Object -TypeName System.Windows.Forms.RichTextBox; $stringRTF = \\\"" + rtf + "\\\"; $Rtb.Rtf = $stringRTF; $Retorno = $Rtb.Text; Write-Output $Retorno > " + path + "\\chines.txt;\"";
           proc.StartInfo.UseShellExecute = true;
           proc.StartInfo.CreateNoWindow = true;
           proc.Start();
           proc.WaitForExit();
           output = File.ReadAllText(path + "chines.txt");
       }
       catch (Exception ex)
       {
           return ex.Message;
       }
       return output;
   }
}

因此，如果我提供我提到的 RTF，它將返回給我

夫人馮夫人6346個紅的桂花

編輯3：

實施寫答案給出的解決方案後，我得到這個“錯誤”

塊引用

在這種情況下不允許數據訪問。上下文要麼是未使用 DataAccessKind.Read 或 SystemDataAccessKind.Read 標記的函式或方法，要麼是從表值函式的 FillRow 方法獲取數據的回調，要麼是 UDT 驗證方法。

是否將其添加到 CLR：

using (var con = new SqlConnection("Context Connection = true;"))
{
   using (var cmd = con.CreateCommand())
   {
       cmd.CommandText = "select @FilePath = filePath from dbo.tablexpto;";
       var sqlParam = new SqlParameter("@FilePath", SqlDbType.NVarChar, 500)
       {
           Direction = ParameterDirection.Output
       };
       con.Open();
       cmd.ExecuteNonQuery();
       if (sqlParam.Value != DBNull.Value)
       {
           path = sqlParam.Value.ToString();
       }
   }
}

@ScottHodgin 在對該問題的評論中是正確的，您不能簡單地隱藏輸入參數。你需要：

  1. path從 SQLCLR 程式碼中刪除參數（問題中未顯示）
  2. @path從 T-SQL 包裝程式碼中刪除參數（如問題所示）
  3. 從 .NET 方法中的查詢中獲取值。類似的東西（以下程式碼是近似的；我沒有測試過它，但它非常接近，最多應該只有輕微的語法問題）：
string _FilePath = string.Empty;
using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
 using (SqlCommand _Command = _Connection.CreateCommand())
 {
   _Command.CommandText = "SELECT @FilePath = filePath FROM dbo.tablexpto;";

   SqlParameter _ParamFilePath =
          new SqlParameter("@FilePath", SqlDbType.NVarChar, 500);
   // adjust the 500 to the actual size of the filepath column
   _ParamFilePath.Direction = ParameterDirection.Output;

   _Connection.Open();

   _Command.ExecuteNonQuery();
   if (_ParamFilePath.Value != DBNull.Value)
   {
     _FilePath = _ParamFilePath.Value.ToString();
   }

 }
}
  1. 更新方法裝飾器如下：
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

但是，不清楚您是否首先需要此 .NET 程式碼。輸出應該是什麼樣子？您只提供了輸入。您可能能夠擺脫使用純 T-SQL，或者如果不是，那麼很可能是一些不需要外部庫或寫入文件系統的簡單 .NET 程式碼。

其他注意事項：

  1. 使用SqlString而不是string輸入參數/返回類型。使用屬性從中獲取 .NET 字元串Value（例如
method(SqlString path)
{
  path.Value // to get the string
  path.IsNull // returns bool to test if a NULL was passed in
}
  1. 您不應該通過呼叫外部程序來執行此操作。命令行的最大大小只有幾千個字元，因此任何超過該大小的 RTF 文件都會導致錯誤，因為送出的命令行不完整。

這在標準 .NET 方法中應該很容易解析簡單的 RTF 格式。Microsoft.com 上提供了 RTF 規範，您只需過濾掉元數據並返回其餘部分。您甚至可以使用純 T-SQL 僥倖逃脫。

看\fonttbl{\f0。這是字型表，字型#0，在您要轉換的文本之前使用—— \lang2052\f0\fs17\“lang2052”表示“中文”，“f0”指的f0是\fonttbl組中定義的字型，“fs17”是字型大小，所以可以忽略。在這個字型（“f0”）定義中，它指定\fcharset134了哪個是GB2312，意思是“ANSI/OEM簡體中文（PRC，新加坡）；簡體中文（GB2312）”，對應程式碼頁936（見這裡——搜尋“gb2312 ”）。使用以下查詢：

SELECT *, COLLATIONPROPERTY([name], 'Version')
FROM   sys.fn_helpcollations()
WHERE  COLLATIONPROPERTY([name], 'CodePage') = 936;

Chinese_PRC_*您可以看到和 有幾個選項Chinese_Simplified_*。排序規則較新，因此Chinese_Simplified_*請選擇Chinese_Simplified_Pinyin_100_BIN2. 然後我們可以轉換

'b7'f2'c8'cb'b7'eb'b7'f2'c8'cb6346'b8'f6'ba'ec'b5'c4' b9'f0'bb'a8

通過使用：

DECLARE @RTF VARCHAR(MAX) = 
'\''b7\''f2\''c8\''cb\''b7\''eb\''b7\''f2\''c8\''cb6346\''b8\''f6\''ba\''ec\''b5\''c4\''b9\''f0\''bb\''a8'; -- ' (CSS bug work-around)

SELECT CONVERT(VARBINARY(MAX), REPLACE(@RTF, '\''', ''), 2); -- ' (CSS bug work-around)

DECLARE @ConvertRTF TABLE
(
  [ToChinese] VARCHAR(MAX) COLLATE Chinese_Simplified_Pinyin_100_BIN2
);
INSERT INTO @ConvertRTF ([ToChinese])
VALUES (CONVERT(VARBINARY(MAX), REPLACE(@RTF, '\''', ''), 2));

SELECT * FROM @ConvertRTF;

返回：

夫人冯夫人cF个红的桂花

接近，但並不完美。非轉義序列也會被轉換。還在做那部分…

更新

更詳細地看一下，使用 T-SQL 使這個完全工作需要相當多的工作。這是可能的，但可能不值得花時間。不過，我認為您不需要使用命令外殼（它需要UNSAFE）呼叫 PowerShell。我強烈建議用 C# 編寫一個簡單的解析器。在 RTF 規範文件的末尾有一個程式碼範例（用於 C++，但很接近）。這應該提供足夠的邏輯來查找編碼、翻譯轉義序列，甚至獲得 Unicode 轉義序列和預定義的標籤，例如\ldblquote和\rdblquote。

據我所知，這是最新的 RTF 規範文件（帶您到 Microsoft 下載頁面）：Word 2007：富文本格式 (RTF) 規範，版本 1.9.1（發佈於 2008-03-20）。查看“附錄 A”（從第 208 頁開始），它是範例 RTF 閱讀器程式碼。或者，只需搜尋一些開源 RTF 閱讀器程式碼並將其集成到您的程序集中。

UPDATE 2

OP 最初確實試圖找到一個開源解析器，並找到了一個，但它不適用於雙字節字元集。我剛剛創建了一個拉取請求（使用 DBCS 編碼 #2）來解決這個問題。該修復程序在我的筆記型電腦上本地執行，產生所需的輸出。最終結果：不需要UNSAFE生成命令 shell 來執行 PowerShell 的程式碼（哎呀🙀），不需要寫入/讀取文件系統，這可以在程序集中完成SAFE。 3. 有關一般使用 SQLCLR 的更多資訊，請訪問SQLCLR 資訊

引用自：https://dba.stackexchange.com/questions/224997

相關問答

Sql-Server

SQL Server 2016 並發限制？調整數據庫並發性

  • February 9, 2018
檢視問答
Sql-Server

為什麼 SQL Server 可以準確地跟踪某些多語句表值函式查詢計劃而不是其他的時間？

  • July 18, 2022
檢視問答
Sql-Server

UDF 和內置函式行為

  • April 29, 2022
檢視問答
Sql-Server

修剪尾隨空格、製表符、換行符、輸入符等

  • March 31, 2022
檢視問答
Sql-Server

為什麼 DATABASEPROPERTYEX 的結果與 AdventureWorks 不一致？

  • January 18, 2022
檢視問答
Sql-Server

MS SQL Server 是否支持“功能索引”？

  • December 21, 2021
檢視問答