一、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'