本文介绍了如何在SQLServer中启用CLR以调用Oracle数据库，包括配置、注册程序集（如Oracle.ManagedDataAccess.dll）、创建存储过程以及处理数据映射。作者提供了详细的过程和示例代码，展示了如何通过存储过程在两者间进行数据操作。
问题
因为sqlserver对于大数据性能问题，所以一部分数据是在Oracle数据库中，为了方便在存储过程中查询或插入Oracle数据，所以使用了在SqlServer中添加外部程序集，在存储过程中调用外部程序集的方法，从而达到目的；
CLR介绍
大家可以看这篇文章SQL Server CLR全功略之一—CLR介绍和配置
下面主要讲一下实现过程
实现
- 使用CLR
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
--test是数据库名
alter database [test] set trustworthy on
- 注册程序集
为了使用Oracle.ManagedDataAccess.dll ，必须先注册它引用的程序集
--Oracle.ManagedDataAccess.dll 引用的库
CREATE ASSEMBLY [system.data.entity]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\system.data.entity.dll'
WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY [system.directoryservices.protocols]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\system.directoryservices.protocols.dll'
WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY [system.enterpriseservices]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\system.enterpriseservices.dll'
WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY [system.enterpriseservices]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\system.enterpriseservices.dll'
WITH PERMISSION_SET = UNSAFE;
--下面这个路径是dll存放的路径
CREATE ASSEMBLY [Oracle.ManagedDataAccess]
authorization [dbo]
from 'T:\oracle dll\Oracle.ManagedDataAccess.dll'
WITH PERMISSION_SET = unSAFE
还有一个是自己写的dll类库，sqlserver数据库可以直接调用的方法（一些注意事项，这里就不说了）
--自己封装的程序集
CREATE ASSEMBLY [OracleClr]
FROM 'T:\oracle dll\OracleClr.dll'
WITH PERMISSION_SET = UNSAFE;
sqlserver需要调用的方法前必须有这个属性
让查询的结果显示在sqlserver管理器中，还需要把数据发送到SqlServer上下文（主要是方便存储过程获取查询的数据，但是如果数据量大这个耗时会比较久，最好使用转换json字符串传递结果）
#region 将数据在sqlserver中显示
/// <summary>
/// 将数据在sqlserver中显示
/// </summary>
/// <param name="dataSet2"></param>
/// <param name="I_ReturnMessage"></param>
/// <returns></returns>
static bool DataSetToSqlDataRecords(DataSet dataSet2, out string I_ReturnMessage)
{
string ColumnsInfo = "";
I_ReturnMessage = "";
try
{
for (int m = 0; m < dataSet2.Tables.Count; m++)
{
SqlMetaData[] array = new SqlMetaData[dataSet2.Tables[m].Columns.Count];
int n = 0;
while (n < array.Length)
{
ColumnsInfo = string.Concat(new string[]
{
dataSet2.Tables[m].Columns[n].DataType.Name,
" ",
dataSet2.Tables[m].Columns[n].DataType.ToString(),
"/",
dataSet2.Tables[m].Columns[n].ColumnName
});
TypeCode typeCode = Type.GetTypeCode(dataSet2.Tables[m].Columns[n].DataType);
switch (typeCode)
{
case TypeCode.Empty:
throw new ArgumentException("Invalid type: TypeCode.Empty");
case TypeCode.Object:
{
array[n] = SqlMetaDataFromObjectColumn(dataSet2.Tables[m].Columns[n].ColumnName, dataSet2.Tables[m].Columns[n], dataSet2.Tables[m].Columns[n].DataType);
if (array[n] == null)
{
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.NVarChar, (long)dataSet2.Tables[m].Columns[n].MaxLength);
}
break;
}
case TypeCode.DBNull:
throw new ArgumentException("Invalid type: TypeCode.DBNull");
case TypeCode.Boolean:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.Bit);
break;
case TypeCode.Char:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.NVarChar, 1L);
break;
case TypeCode.SByte:
throw new ArgumentException("Invalid type: TypeCode.SByte");
case TypeCode.Byte:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.TinyInt);
break;
case TypeCode.Int16:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.SmallInt);
break;
case TypeCode.UInt16:
throw new ArgumentException("Invalid type: TypeCode.UInt16");
case TypeCode.Int32:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.Int);
break;
case TypeCode.UInt32:
throw new ArgumentException("Invalid type: TypeCode.UInt32");
case TypeCode.Int64:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.BigInt);
break;
case TypeCode.UInt64:
throw new ArgumentException("Invalid type: TypeCode.UInt64");
case TypeCode.Single:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.Real);
break;
case TypeCode.Double:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.Float);
break;
case TypeCode.Decimal:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.Decimal, 18, 6);
break;
case TypeCode.DateTime:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.DateTime);
break;
case TypeCode.String:
array[n] = new SqlMetaData(dataSet2.Tables[m].Columns[n].ColumnName, SqlDbType.NVarChar, (long)dataSet2.Tables[m].Columns[n].MaxLength);
break;
default:
throw new ArgumentException("Unknown type: " + ((typeCode != null) ? typeCode.ToString() : null));
break;
}
n++;
continue;
}
SqlDataRecord sqlDataRecord = new SqlDataRecord(array);
SqlContext.Pipe.SendResultsStart(sqlDataRecord);
foreach (object obj in dataSet2.Tables[m].Rows)
{
DataRow dataRow = (DataRow)obj;
for (int num = 0; num < array.Length; num++)
{
sqlDataRecord.SetValue(num, dataRow[num]);
}
SqlContext.Pipe.SendResultsRow(sqlDataRecord);
}
SqlContext.Pipe.SendResultsEnd();
}
ColumnsInfo = "";
return true;
}
catch (Exception ex)
{
if (!string.IsNullOrWhiteSpace(ColumnsInfo))
I_ReturnMessage = $"Invalid type:{ColumnsInfo}!{ex.ToString()}";
return false;
}
}
/// <summary>
/// 转换sqlserver列数据
/// </summary>
/// <param name="name"></param>
/// <param name="column"></param>
/// <param name="clrType"></param>
/// <returns></returns>
private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)
{
SqlMetaData result;
if (clrType == typeof(byte[])
|| clrType == typeof(SqlBinary)
|| clrType == typeof(SqlBytes)
|| clrType == typeof(char[])
|| clrType == typeof(SqlString)
|| clrType == typeof(SqlChars))
{
result = new SqlMetaData(name, SqlDbType.VarBinary, (long)column.MaxLength);
}
else if (clrType == typeof(Guid))
{
result = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
}
else if (clrType == typeof(object))
{
result = new SqlMetaData(name, SqlDbType.Variant);
}
else if (clrType == typeof(SqlBoolean))
{
result = new SqlMetaData(name, SqlDbType.Bit);
}
else if (clrType == typeof(SqlByte))
{
result = new SqlMetaData(name, SqlDbType.TinyInt);
}
else if (clrType == typeof(SqlDateTime))
{
result = new SqlMetaData(name, SqlDbType.DateTime);
}
else if (clrType == typeof(SqlDouble))
{
result = new SqlMetaData(name, SqlDbType.Float);
}
else if (clrType == typeof(SqlGuid))
{
result = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
}
else if (clrType == typeof(SqlInt16))
{
result = new SqlMetaData(name, SqlDbType.SmallInt);
}
else if (clrType == typeof(SqlInt32))
{
result = new SqlMetaData(name, SqlDbType.Int);
}
else if (clrType == typeof(SqlInt64))
{
result = new SqlMetaData(name, SqlDbType.BigInt);
}
else if (clrType == typeof(SqlMoney))
{
result = new SqlMetaData(name, SqlDbType.Money);
}
else if (clrType == typeof(SqlDecimal))
{
result = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);
}
else if (clrType == typeof(SqlSingle))
{
result = new SqlMetaData(name, SqlDbType.Real);
}
else if (clrType == typeof(SqlXml))
{
result = new SqlMetaData(name, SqlDbType.Xml);
}
else
{
result = null;
}
return result;
}
#endregion
- 添加存储过程
--添加存储过程 以调用我们封装的方法
CREATE PROCEDURE CLR_Execute
@SQL nvarchar(max) ,
@I_ReturnMessage nvarchar(4000)='' output
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [OracleClr].[OracleClr.DataAccess].[CLR_Execute]
GO
CREATE PROCEDURE CLR_GetDataSet
@SQL nvarchar(max) ,
@I_ReturnMessage nvarchar(4000)='' output
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [OracleClr].[OracleClr.DataAccess].[CLR_GetDataSet]
GO
CREATE PROCEDURE CLR_GetDataSetWithKey
@SQL nvarchar(max) ,
@I_ReturnMessage nvarchar(4000)='' output
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [OracleClr].[OracleClr.DataAccess].[CLR_GetDataSetWithKey]
GO
- 更新程序集（dll更新）
这里只说一下如果dll有更新，需要先删除之前的dll 包括已经创建的存储过程和函数
--删除dll 必须先删除关联的存储过程和函数
drop PROCEDURE CLR_Execute
drop PROCEDURE CLR_GetDataSet
drop PROCEDURE CLR_GetDataSetWithKey
DROP ASSEMBLY [OracleClr]
- 测试
DECLARE @SQL NVARCHAR(MAX)='select * from [table]',
@ReturnMessage NVARCHAR(4000);
exec dbo.CLR_GetDataSetWithKey @SQL=@SQL,
@ReturnMessage=@ReturnMessage output
select @ReturnMessage
1807
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
