SqlClr —— 一次查询多个DB

来源：互联网发布：mac珊瑚红口红效果图编辑：程序博客网时间：2024/05/21 05:43

一、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 MASTERGO--1. 在SQL Server中启用CLRexec sp_configure 'clr enabled', 1 GORECONFIGURE WITH OVERRIDE;  go  --2. 在目标库 [test] 设置TRUSTWORTHY为ONALTER DATABASE MASTER SET TRUSTWORTHY ONGO--Sp_changedbowner 'sa',trueGO--1. 删除主程序集已有对象dbIF OBJECT_ID('[dbo].Proc_DBA_QueryMultiDB') IS NOT NULLDROP PROC [dbo].Proc_DBA_QueryMultiDBGO--2. 删除主程序集已有对象dbIF OBJECT_ID('[dbo].StoredProcReturnResultSet') IS NOT NULLDROP PROC [dbo].StoredProcReturnResultSetGO--4. 删除主程序集 StudySqlClr IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='StudySqlClr') DROP  ASSEMBLY StudySqlClrGO--3. 创建主程序集: StudySqlClrCREATE ASSEMBLY StudySqlClr FROM 'D:\StudySqlClr.dll' WITH PERMISSION_SET = UNSAFEGOCREATE PROC [dbo].StoredProcReturnResultSetASEXTERNAL NAME [StudySqlClr].[StoredProcedures].StoredProcReturnResultSet ; GOEXEC StoredProcReturnResultSetGOCREATE PROC [dbo].Proc_DBA_QueryMultiDB(@connStrings nvarchar(4000),@flags nvarchar(4000),@sql nvarchar(4000))ASEXTERNAL NAME [StudySqlClr].[StoredProcedures].Proc_DBA_QueryMultiDB ; GOEXEC [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'

阅读全文

0 0