Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
El AssemblyCleanup ejemplo contiene un procedimiento almacenado de .NET que limpia ensamblados sin usar en la base de datos actual consultando los catálogos de metadatos. Su único parámetro, visible_assemblies, se usa para especificar si los ensamblados visibles sin usar se deben quitar o no. Un valor de "false" significa que de forma predeterminada solo se quitarán los ensamblados invisibles sin usar; de lo contrario, se quitarán todos los ensamblados sin usar. El conjunto de ensamblados no usados son aquellos ensamblados que no tienen ningún punto de entrada definido (rutinas/ tipos y agregados) y no hay ensamblados usados que hagan referencia a ellos directa o indirectamente.
Prerrequisitos
Para crear y ejecutar este proyecto, debe instalarse el siguiente software:
SQL Server o SQL Server Express. Puede obtener SQL Server Express de forma gratuita desde el sitio web de documentación y ejemplos de SQL Server Express.
La base de datos AdventureWorks que está disponible en el sitio web para desarrolladores de SQL Server
SDK de .NET Framework 2.0 o posterior o Microsoft Visual Studio 2005 o posterior. Puede obtener el SDK de .NET Framework de forma gratuita.
Además, se deben cumplir las siguientes condiciones:
La instancia de SQL Server que usa debe tener habilitada la integración clR.
Para habilitar la integración clR, realice los pasos siguientes:
Habilitar la integración con CLR
- Ejecute los siguientes comandos Transact-SQL:
sp_configure 'clr enabled', 1GORECONFIGUREGONota:
Para habilitar CLR, debe tener
ALTER SETTINGSpermiso de nivel de servidor, que se mantiene implícitamente en los miembros de lossysadminroles fijos de servidor yserveradmin.La base de datos AdventureWorks debe instalarse en la instancia de SQL Server que está usando.
Si no es administrador para la instancia de SQL Server que usa, debe tener un permiso de administrador para crearAssembly para completar la instalación.
Compilar el ejemplo
Cree y ejecute el ejemplo mediante las instrucciones siguientes:
Abra un símbolo del sistema de Visual Studio o .NET Framework.
Si es necesario, cree un directorio para el ejemplo. En este ejemplo, usaremos C:\MySample.
En c:\MySample, cree
AssemblyCleanup.vb(para el ejemplo de Visual Basic) oAssemblyCleanup.cs(para el ejemplo de C#) y copie el código de ejemplo de Visual Basic o C# adecuado (a continuación) en el archivo.Compile el código de ejemplo desde el símbolo de la línea de comandos ejecutando uno de los siguientes, según su elección de idioma.
Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Transactions.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library AssemblyCleanup.vbCsc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library AssemblyCleanup.cs
Copie el código de instalación Transact-SQL en un archivo y guárdelo como
Install.sqlen el directorio de ejemplo.Si el ejemplo está instalado en un directorio distinto,
C:\MySample\edite el archivoInstall.sqlcomo se indica para que apunte a esa ubicación.Implementación del ensamblado y el procedimiento almacenado mediante la ejecución
sqlcmd -E -I -i install.sql
Copie Transact-SQL script de comando de prueba en un archivo y guárdelo como
test.sqlen el directorio de ejemplo.Ejecute el script de prueba con el comando siguiente.
sqlcmd -E -I -i test.sql
Copie el script de limpieza Transact-SQL en un archivo y guárdelo como
cleanup.sqlen el directorio de ejemplo.Ejecute el script con el comando siguiente.
sqlcmd -E -I -i cleanup.sql
Código de ejemplo
A continuación se muestran las listas de código de este ejemplo.
C#
using System;
using System.Text;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Globalization;
using Microsoft.SqlServer.Server;
/// <summary>
/// Defines a CLR user defined function CleanupUnusedAssemblies that drops
/// all the invisible assemblies with no references.
/// </summary>
public sealed class AssemblyCleanup
{
private SqlTransaction transaction;
internal class AssemblySet
{
private Dictionary<int, object> m_dictionary;
/// <summary>
/// Initialize internal structures
/// </summary>
/// <returns></returns>
public AssemblySet()
{
m_dictionary = new Dictionary<int, object>();
}
/// <summary>
/// Adds an assembly id into the current AssemblySet if it is not
/// already part of it.
/// </summary>
/// <returns></returns>
public void Add(int assemblyId)
{
if (!m_dictionary.ContainsKey(assemblyId))
{
m_dictionary.Add(assemblyId, null);
}
}
/// <summary>
/// Number of assembly ids stored in this instance
/// </summary>
/// <returns></returns>
public int Count
{
get
{
return m_dictionary.Count;
}
}
/// <summary>
/// Returns the comma-separated list of assembly ids contained in this instance
/// </summary>
/// <returns>string value that represents a comma-separated list
/// of assembly ids</returns>
public string ToCommaSeparatedList()
{
StringBuilder sb = new StringBuilder();
if (m_dictionary.Count > 0)
{
foreach (KeyValuePair<int, object> kv in m_dictionary)
{
sb.Append(kv.Key);
sb.Append(",");
}
sb.Length--; // remove the trailing comma
}
return sb.ToString();
}
}
/// <summary>
/// Initializes an instance of AssemblyCleanup with a SqlTransaction
/// </summary>
/// <returns></returns>
private AssemblyCleanup(SqlTransaction transaction)
{
this.transaction = transaction;
}
/// <summary>
/// Helper function that creates a SqlCommand object as part of the current
/// transaction
/// </summary>
/// <returns></returns>
private SqlCommand CreateCommandInTransaction()
{
SqlCommand cmd = this.transaction.Connection.CreateCommand();
cmd.Transaction = this.transaction;
return cmd;
}
/// <summary>
/// Helper function that constructs an AssemblySet instance using the
/// first column of the resultset resulting from the query that was passed in.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private AssemblySet GetAssemblySetFromQuery(string query)
{
SqlCommand cmd = CreateCommandInTransaction();
AssemblySet set = new AssemblySet();
cmd.CommandText = query;
using (SqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
set.Add(rd.GetInt32(0));
}
}
return set;
}
/// <summary>
/// Constructs a DROP ASSEMBLY T-SQL statement using the AssemblySet
/// passed in as a parameter.
/// </summary>
/// <param name="set"></param>
/// <returns></returns>
private void DropAssemblies(AssemblySet unusedAssemblySet)
{
if (unusedAssemblySet.Count > 0)
{
StringBuilder assemblyNamesToDrop = new StringBuilder();
// Gather the list of assembly names we will drop later
SqlCommand cmd = CreateCommandInTransaction();
cmd.CommandText = String.Format(CultureInfo.InvariantCulture,
"SELECT name FROM sys.assemblies WHERE assembly_id IN ({0});",
unusedAssemblySet.ToCommaSeparatedList());
using (SqlDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
assemblyNamesToDrop.Append("[");
assemblyNamesToDrop.Append(rd.GetString(0));
assemblyNamesToDrop.Append("],");
}
}
// Remove trailing comma
assemblyNamesToDrop.Length--;
// Drop all assemblies at the same time
cmd.CommandText = String.Format(CultureInfo.InvariantCulture,
"DROP ASSEMBLY {0}", assemblyNamesToDrop.ToString());
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Serves as the stored procedure entry point and drives the process
/// of expanding the "assemblies in use" set, negating it, and
/// dropping the results
/// </summary>
/// <param name="visibleAssemblies">If set to true, will also drop
/// unused visible assemblies. Otherwise, will only drop unused invisible
/// assemblies.</param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void CleanupUnusedAssemblies(bool visibleAssemblies)
{
bool succeeded = false;
SqlConnection conn;
SqlTransaction transaction;
string sqlStatement;
AssemblySet assembliesToDrop;
AssemblyCleanup assemblyCleanup;
conn = new SqlConnection("context connection=true");
conn.Open();
transaction = conn.BeginTransaction();
try
{
// Create a set of assemblies in use by looking at
// the metadata of the current database
sqlStatement =
"DECLARE @UsedAssembly TABLE (AssemblyID int NOT NULL); " +
"DECLARE @RowCount int; " +
"INSERT INTO @UsedAssembly " +
"SELECT DISTINCT([assembly_id]) " +
"FROM sys.assembly_modules " +
"UNION " +
"SELECT [assembly_id] " +
"FROM sys.assembly_types; " +
"SET @RowCount = @@ROWCOUNT; " +
"WHILE @RowCount > 0 " +
"BEGIN " +
"INSERT INTO @UsedAssembly " +
"SELECT [referenced_assembly_id] " +
"FROM sys.assembly_references ar " +
"INNER JOIN @UsedAssembly ua " +
"ON ar.[assembly_id] = ua.AssemblyID " +
"WHERE NOT EXISTS (SELECT * FROM @UsedAssembly WHERE AssemblyID = [referenced_assembly_id]) " +
"SET @RowCount = @@ROWCOUNT; " +
"END;";
if (visibleAssemblies)
{
sqlStatement +=
"SELECT assembly_id " +
"FROM sys.assemblies " +
"WHERE assembly_id NOT IN (SELECT AssemblyID FROM @UsedAssembly);";
}
else
{
sqlStatement +=
"SELECT assembly_id " +
"FROM sys.assemblies " +
"WHERE assembly_id NOT IN (SELECT AssemblyID FROM @UsedAssembly) " +
" AND is_visible = 0;";
}
// This marks the beginning of the transaction
assemblyCleanup = new AssemblyCleanup(transaction);
// Assemblies that are currently in use
assembliesToDrop
= assemblyCleanup.GetAssemblySetFromQuery(sqlStatement);
assemblyCleanup.DropAssemblies(assembliesToDrop);
// Mark as succeeded
succeeded = true;
}
finally
{
// We must guarantee that we explicitly call either Commit()
// or Rollback() before we return.
if (succeeded)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
conn.Dispose();
}
}
}
Visual Basic
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Globalization
Imports System.Text
Imports System.Transactions
Public NotInheritable Class AssemblyCleanup
Private transaction As SqlTransaction
Friend Class AssemblySet
Private m_dictionary As Dictionary(Of Integer, Object)
''' <summary>
''' Initialize internal structures
''' </summary>
''' <returns></returns>
Public Sub New()
m_dictionary = New Dictionary(Of Integer, Object)
End Sub
''' <summary>
''' Adds an assembly id into the current AssemblySet if it is not already part of it.
''' </summary>
''' <returns></returns>
Public Sub Add(ByVal assemblyId As Integer)
If Not m_dictionary.ContainsKey(assemblyId) Then
m_dictionary.Add(assemblyId, Nothing)
End If
End Sub
''' <summary>
''' Number of assembly ids stored in this instance
''' </summary>
''' <returns></returns>
Public ReadOnly Property Count() As Integer
Get
Return m_dictionary.Count
End Get
End Property
''' <summary>
''' Returns the comma-separated list of assembly ids contained in this instance
''' </summary>
''' <returns>string value that represents a comma-separated list of assembly ids</returns>
Public Function ToCommaSeparatedList() As String
Dim sb As New StringBuilder()
If m_dictionary.Count > 0 Then
For Each kv As KeyValuePair(Of Integer, Object) In m_dictionary
If (True) Then
sb.Append(kv.Key)
sb.Append(",")
End If
Next
sb.Length -= 1 ' remove the trailing comma
End If
Return sb.ToString()
End Function
End Class
''' <summary>
''' Initializes an instance of AssemblyCleanup with a SqlTransaction
''' </summary>
''' <returns></returns>
Private Sub New(ByVal trans As SqlTransaction)
Me.transaction = trans
End Sub
''' <summary>
''' Helper function that creates a SqlCommand object as part of the
''' current transaction
''' </summary>
''' <returns></returns>
Private Function CreateCommandInTransaction() As SqlCommand
Dim cmd As SqlCommand = transaction.Connection.CreateCommand()
cmd.Transaction = Me.transaction
Return cmd
End Function
''' <summary>
''' Helper function that constructs an AssemblySet instance using the
''' first column of the resultset resulting from the query that was passed in.
''' </summary>
''' <param name="query"></param>
''' <returns></returns>
Private Function GetAssemblySetFromQuery(ByVal query As String) As AssemblySet
Dim cmd As SqlCommand = CreateCommandInTransaction()
Dim [set] As New AssemblySet()
cmd.CommandText = query
Dim rd As SqlDataReader = cmd.ExecuteReader()
Try
While rd.Read()
[set].Add(rd.GetInt32(0))
End While
Finally
rd.Dispose()
End Try
Return [set]
End Function
''' <summary>
''' Constructs a DROP ASSEMBLY T-SQL statement using the AssemblySet
''' passed in as a parameter.
''' </summary>
''' <param name="set"></param>
''' <returns></returns>
Private Sub DropAssemblies(ByVal unusedAssemblySet As AssemblySet)
If unusedAssemblySet.Count > 0 Then
Dim assemblyNamesToDrop As New StringBuilder()
' Gather the list of assembly names we will drop later
Dim cmd As SqlCommand = CreateCommandInTransaction()
cmd.CommandText = String.Format(CultureInfo.InvariantCulture, _
"SELECT name FROM sys.assemblies WHERE assembly_id IN ({0});", _
unusedAssemblySet.ToCommaSeparatedList())
Dim rd As SqlDataReader = cmd.ExecuteReader()
Try
While rd.Read()
assemblyNamesToDrop.Append("[")
assemblyNamesToDrop.Append(rd.GetString(0))
assemblyNamesToDrop.Append("],")
End While
Finally
rd.Dispose()
End Try
' Remove trailing comma
assemblyNamesToDrop.Length -= 1
' Drop all assemblies at the same time
cmd.CommandText = String.Format(CultureInfo.InvariantCulture, _
"DROP ASSEMBLY {0}", assemblyNamesToDrop.ToString())
cmd.ExecuteNonQuery()
End If
End Sub
''' <summary>
''' Serves as the stored procedure entry point and drives the process of
''' expanding the "assemblies in use" set, negating it, and dropping
''' the results.
''' </summary>
''' <param name="visibleAssemblies">If set to true, will also drop unused
''' visible assemblies. Otherwise, will only drop unused invisible assemblies.</param>
''' <returns></returns>
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub CleanupUnusedAssemblies(ByVal visibleAssemblies As Boolean)
Dim succeeded As Boolean = False
Dim conn As SqlConnection
Dim transaction As SqlTransaction
Dim sqlStatement As String
Dim assembliesToDrop As AssemblySet
Dim assemblyCleanup As AssemblyCleanup
conn = New SqlConnection("context connection=true")
conn.Open()
transaction = conn.BeginTransaction()
Try
' Create a set of assemblies in use by looking at
' the metadata of the current database
sqlStatement = "DECLARE @UsedAssembly TABLE (AssemblyID int NOT NULL); " & _
"DECLARE @RowCount int; " & _
"INSERT INTO @UsedAssembly " & _
"SELECT DISTINCT([assembly_id]) " & _
"FROM sys.assembly_modules " & _
"UNION " & _
"SELECT [assembly_id] " & _
"FROM sys.assembly_types; " & _
"SET @RowCount = @@ROWCOUNT; " & _
"WHILE @RowCount > 0 " & _
"BEGIN " & _
"INSERT INTO @UsedAssembly " & _
"SELECT [referenced_assembly_id] " & _
"FROM sys.assembly_references ar " & _
"INNER JOIN @UsedAssembly ua " & _
"ON ar.[assembly_id] = ua.AssemblyID " & _
"WHERE NOT EXISTS (SELECT * FROM @UsedAssembly WHERE AssemblyID = [referenced_assembly_id]) " & _
"SET @RowCount = @@ROWCOUNT; " & _
"END;"
If visibleAssemblies Then
sqlStatement += "SELECT assembly_id " & _
"FROM sys.assemblies " & _
"WHERE assembly_id NOT IN (SELECT AssemblyID FROM @UsedAssembly);"
Else
sqlStatement += "SELECT assembly_id " & _
"FROM sys.assemblies " & _
"WHERE assembly_id NOT IN (SELECT AssemblyID FROM @UsedAssembly) " & _
" AND is_visible = 0;"
End If
' This marks the beginning of the transaction
assemblyCleanup = New AssemblyCleanup(transaction)
' Assemblies that are currently in use
assembliesToDrop _
= assemblyCleanup.GetAssemblySetFromQuery(sqlStatement)
assemblyCleanup.DropAssemblies(assembliesToDrop)
' Mark as succeeded
succeeded = True
Finally
' We must guarantee that we explicitly call either Commit()
' or Rollback() before we return.
If succeeded Then
transaction.Commit()
Else
transaction.Rollback()
End If
conn.Dispose()
End Try
End Sub
End Class
Este es el script de instalación de Transact-SQL (Install.sql), que implementa el ensamblado y crea el procedimiento almacenado en la base de datos.
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'CleanupUnusedAssemblies')
DROP PROCEDURE CleanupUnusedAssemblies;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'AssemblyCleanupUtils')
DROP ASSEMBLY AssemblyCleanupUtils;
GO
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
Set @SamplesPath = 'C:\MySample\'
CREATE ASSEMBLY AssemblyCleanupUtils
FROM @SamplesPath + 'AssemblyCleanup.dll'
WITH permission_set = Safe;
GO
CREATE PROCEDURE CleanupUnusedAssemblies (
@visible_assemblies bit
) AS
EXTERNAL NAME [AssemblyCleanupUtils].[AssemblyCleanup].CleanupUnusedAssemblies;
GO
Se trata de test.sql, que prueba el ejemplo ejecutando el procedimiento almacenado.
USE AdventureWorks;
GO
PRINT 'Before cleanup...'
SELECT [name] FROM sys.assemblies;
GO
-- pass in false, which means the cleanup will only include invisible assemblies
EXEC dbo.CleanupUnusedAssemblies false;
GO
PRINT 'After cleanup'
SELECT [name] FROM sys.assemblies;
El siguiente Transact-SQL quita el ensamblado y el procedimiento almacenado de la base de datos.
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'CleanupUnusedAssemblies')
DROP PROCEDURE CleanupUnusedAssemblies;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'AssemblyCleanupUtils')
DROP ASSEMBLY AssemblyCleanupUtils;
GO
Véase también
Escenarios de uso y ejemplos para la integración de Common Language Runtime (CLR)