c# - CLR 中的动态 sql 连接字符串

Question

尝试创建一个将 sql 表写入文件的 clr 作为管道分隔。

尚未测试输出，因此不确定它是否正常工作，因为在尝试创建连接字符串时遇到了障碍。我希望它是动态的，以便它根据执行它的 sql 服务器计算出连接字符串。

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

public partial class CLR_uspExportToFiles
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspExportToFiles(string TableOrQuery, string Delimiter, int Debug, string FilePath, string Filename)
    {
        var output = FilePath + Filename;
        using (System.IO.StreamWriter file = new System.IO.StreamWriter(output))
        {

            const string DATASOURCE = "Data Source=";
            const string INITIALCATALOG = ";Initial Catalog=";
            const string INTEGRATEDSECURITY = ";Integrated Security=True;Enlist=false;";

            string ConnString;
            string InstanceName;
            string DbName;

            //Establish a connection in the current context to dynamically get the current
            //Server and Database Name.
            using (SqlConnection sysconn = new SqlConnection("context connection=true"))
            {
                SqlCommand GetSQLSystemProperties = new SqlCommand();
                GetSQLSystemProperties.Connection = sysconn;

                sysconn.Open();

                //Get the current SQL Server instance name
                GetSQLSystemProperties.CommandText = "SELECT @@Servername";
                InstanceName = (string)GetSQLSystemProperties.ExecuteScalar();

                //Get the current Database Name
                GetSQLSystemProperties.CommandText = "SELECT DB_NAME()";
                DbName = (string)GetSQLSystemProperties.ExecuteScalar();
                sysconn.Close();


                //Dynamically construct the connection string to establish a connection outside
                //of the current context, so that any error written to the error table won't be
                //rolled back.
                ConnString = DATASOURCE + InstanceName + INITIALCATALOG + DbName + INTEGRATEDSECURITY;

                using (SqlConnection conn = new SqlConnection(ConnString))
                {

                    using (SqlCommand cmd = new SqlCommand("SELECT * FROM @TableOrQuery", conn))
                    {
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@TableOrQuery", TableOrQuery);
                        //cmd.Parameters.AddWithValue("@Del", Delimiter);
                        //cmd.Parameters.AddWithValue("@Debug", Debug);
                        //cmd.Parameters.AddWithValue("@FilePath", FilePath);
                        //cmd.Parameters.AddWithValue("@Filename", Filename);

                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                //Build the Text file data.
                                string txt = string.Empty;

                                foreach (DataColumn column in dt.Columns)
                                {
                                    //Add the Header row for Text file.
                                    txt += column.ColumnName + "|";
                                }

                                //Add new line.
                                txt += "\r\n";
                                file.WriteLine(txt);
                                foreach (DataRow row in dt.Rows)
                                {
                                    foreach (DataColumn column in dt.Columns)
                                    {
                                        //Add the Data rows.
                                        txt += row[column.ColumnName].ToString() + "|";
                                    }

                                    //Add new line.
                                    txt += "\r\n";
                                    file.WriteLine(txt);
                                }

                            }

                        }
                    };

                }
            }
        }
    }
}

虽然得到一个错误

Msg 6522, Level 16, State 1, Procedure CLR_uspExportToFiles, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_uspExportToFiles": 
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePa...

不知道怎么解决

Answer 1

一些注意事项:

1. 您应该使用 Sql*** 类型而不是 native .NET 类型。意思是，SqlString(或者有时是SqlChars)而不是string，SqlInt32而不是int 等请参阅 Mapping CLR Parameter Data获取完整列表。
2. 我认为您以错误的顺序打开文件和数据库连接。查询可能不返回任何内容:并非所有查询都返回结果集，并且有一些错误。在命令成功完成并指示已返回某些内容之前，我不会打开文件。
3. 我看不出有任何理由打开与服务器的外部连接。我看到了关于不想回滚某些内容的评论，但这没有多大意义。
4. 您不需要这两个查询(SELECT @@SERVERNAME 和 SELECT DB_NAME())，因为您不需要第二个外部连接。<
5. 即使您确实需要(或只是想要)这两条信息，您也只需要查询 @@SERVERNAME。您可以通过 Database 属性从 SqlConnection 获取数据库名称。是的，当使用 Context Connection 时，它将被设置为当前数据库(该查询的当前数据库)。
6. 不要使用 Parameters.AddWithValue。单独创建参数并指定 SqlDbType 和最大长度。
7. 不要使用DataTable，除非您绝对肯定传入的任何表永远不会那么大(就数据/已用空间而言，不是索引)。 Fill 方法将整个结果集吸入内存(即 DataTable)。那是相当危险的。您应该改为使用 SqlDataReader 并将结果集的每一行写到文件中，因为它是从 SqlDataReader 中读取的。
8. 不需要txt 变量。您可以在通过 Write 阅读时写出每个字段方法。
9. 即使您确实想将这些片段连接成一个字符串以每行写一次，您也不需要附加换行符(即 \r\n)，因为这是由WriteLine 方法(同样适用于 Console.Write 与 Console.WriteLine)。通过同时使用 WriteLine 和附加 \r\n，每行数据将由一个空行分隔。

有关一般使用 SQLCLR 的更多信息，请参阅我在 SQL Sever Central 上撰写的系列文章:Stairway to SQLCLR (需要免费注册)。

此外，作为 SQL# 的一部分，我还编写了一个 SQLCLR 存储过程来执行此操作(将查询的结果集转储到文件中)。图书馆。请注意，虽然有免费版的 SQL#，但 DB_BulkExport 存储过程仅在完整版中可用。