SqlClr —— 一次查询多个DB

一、C#代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;
using System.Collections.Generic;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcReturnResultSet()
    {
        // Create the record and specify the metadata for the columns.
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("col1", SqlDbType.NVarChar, 100),
            new SqlMetaData("col2", SqlDbType.Int));

        // Mark the begining of the result-set.
        SqlContext.Pipe.SendResultsStart(record);

        // Send 10 rows back to the client.
        for (int i = 0; i < 10; i++)
        {
            // Set values for each column in the row.
            record.SetString(0, "row " + i.ToString());
            record.SetInt32(1, i);

            // Send the row back to the client.
            SqlContext.Pipe.SendResultsRow(record);
        }

        // Mark the end of the result-set.
        SqlContext.Pipe.SendResultsEnd();
    }

    /// <summary>
    /// 一次查询多个DB
    /// </summary>
    /// <param name="connStrings">连接串，多个以|分割</param>
    /// <param name="flags">DB的标识，多个以|分割</param>
    /// <param name="sql">要查询的SQL</param>
    [SqlProcedure]
    public static void Proc_DBA_QueryMultiDB(string connStrings, string flags, string sql)
    {
        List<string> forbiddenWords = new List<string>() { "update", "delete", "drop", "truncate" };
        if (forbiddenWords.Find(p => sql.ToLower().IndexOf(p) != -1) != null)
        {
            throw new Exception("SQL包含违禁词，已中止！");
        }

        string[] connStringArr = (connStrings ?? string.Empty).Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries);  
        string[] flagArr =(flags ?? string.Empty).Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries);  

        int i = 0;
        SqlDataRecord record = null;
        List<SqlMetaData> metaList = new List<SqlMetaData>();
        bool builtStruct = false;
        foreach (string connString in connStringArr)
        {
            string flag = flagArr.Length > i ? flagArr[i] : string.Empty;
            i++;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd1 = new SqlCommand("select db_name()",conn);
                string dbName = cmd1.ExecuteScalar().ToString();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    //构建输出的结构，并标识开始输出
                    if (!builtStruct)
                    {
                        metaList.Add(new SqlMetaData("flag",SqlDbType.NVarChar,100));
                        metaList.Add(new SqlMetaData("currDbName", SqlDbType.NVarChar, 100));
                        for (int colIdx = 0; colIdx < sdr.FieldCount; colIdx++)
                        {
                            metaList.Add(new SqlMetaData(sdr.GetName(colIdx), SqlDbType.NVarChar,-1 ));
                        }

                        record = new SqlDataRecord(metaList.ToArray());
                        builtStruct = true;
                        SqlContext.Pipe.SendResultsStart(record);
                    }

                    record.SetString(0, flag);
                    record.SetString(1, dbName);
                    for (int colIdx = 0; colIdx < sdr.FieldCount; colIdx++)
                    {
                        string v = string.Empty;
                        try
                        {
                            v = Convert.ToString(sdr[colIdx]);
                        }
                        catch (Exception ex)
                        {
                            v = ex.Message;
                        }

                        record.SetString(colIdx+2, v);
                    }
                    SqlContext.Pipe.SendResultsRow(record);
                }
            }
        }
        //结束发送
        if (builtStruct)
        {
            SqlContext.Pipe.SendResultsEnd();
        }
    }
};

二、部署SQL：

USE MASTER
GO
--1. 在SQL Server中启用CLR
exec sp_configure 'clr enabled', 1 
GO
RECONFIGURE WITH OVERRIDE;  
go  
--2. 在目标库 [test] 设置TRUSTWORTHY为ON
ALTER DATABASE MASTER SET TRUSTWORTHY ON
GO
--Sp_changedbowner 'sa',true
GO
--1. 删除主程序集已有对象db
IF OBJECT_ID('[dbo].Proc_DBA_QueryMultiDB') IS NOT NULL
	DROP PROC [dbo].Proc_DBA_QueryMultiDB
GO
--2. 删除主程序集已有对象db
IF OBJECT_ID('[dbo].StoredProcReturnResultSet') IS NOT NULL
	DROP PROC [dbo].StoredProcReturnResultSet
GO

--4. 删除主程序集 StudySqlClr 
IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='StudySqlClr') 
	DROP  ASSEMBLY StudySqlClr
GO
--3. 创建主程序集: StudySqlClr
CREATE ASSEMBLY StudySqlClr FROM 'D:\StudySqlClr.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE PROC [dbo].StoredProcReturnResultSet
AS
	EXTERNAL NAME [StudySqlClr].[StoredProcedures].StoredProcReturnResultSet ; 
GO
EXEC StoredProcReturnResultSet
GO
CREATE PROC [dbo].Proc_DBA_QueryMultiDB
(
@connStrings nvarchar(4000),
@flags nvarchar(4000),
@sql nvarchar(4000)
)
AS
	EXTERNAL NAME [StudySqlClr].[StoredProcedures].Proc_DBA_QueryMultiDB ; 
GO

EXEC [dbo].Proc_DBA_QueryMultiDBV1
@connStrings=N'data source=192.168.xx.xx,2014;initial catalog=master;user id=?;password=??;
|data source=192.168.xx.xx,2014;initial catalog=tempdb;user id=?;password=??;'
,@flags=N'广东|广西'
,@sql='select top 10 * from sys.tables'