Compartir a través de

Tutorial: Crear un procedimiento almacenado en código administrado

  • Artículo

Puede escribir procedimientos almacenados para bases de datos de Microsoft SQL Server 2005 y SQL Server 2008 en código administrado con lenguajes de .NET Framework como Visual Basic y Visual C#. Los procedimientos almacenados que se escriben en código administrado se denominan procedimientos almacenados de Common Language Runtime de SQL Server o procedimientos almacenados CLR de SQL.

Puede crear procedimientos almacenados de SQL agregando elementos de tipo Procedimiento almacenado a los proyectos de base de datos CLR de SQL. Después de implementar los procedimientos almacenados creados en código administrado, esos procedimientos se llaman y se ejecutan como cualquier otro procedimiento almacenado.

Las tareas que se ilustran en este tutorial son las siguientes:

  • Crear un nuevo proyecto de base de datos CLR de SQL

  • Crear un procedimiento almacenado en código administrado

  • Implementar el procedimiento almacenado en una base de datos de SQL Server 2008.

  • Crear un script para probar el procedimiento almacenado en la base de datos

  • Consultar datos en la base de datos para confirmar que el procedimiento almacenado se ejecuta correctamente

Requisitos previos

Para completar las tareas de este tutorial, necesitará:

  • Una conexión a la base de datos de ejemplo AdventureWorks2008 que se ejecuta en SQL Server 2008. Para obtener más información, vea la página Microsoft SQL Server Product Samples en el sitio web de Microsoft.

Crear el proyecto

Para crear el nuevo proyecto de base de datos CLR de SQL

  1. En el menú Archivo, cree un nuevo proyecto.

  2. Seleccione Proyecto de base de datos SQL CLR, asigne al proyecto el nombre SQLCLRStoredProcedure y haga clic en Aceptar. Para obtener más información, vea Cómo: Crear un proyecto para objetos de base de datos que usan la integración de Common Language Runtime de SQL Server.

Conectar a una base de datos de SQL Server 2008

Este tutorial requiere una conexión a la base de datos de ejemplo AdventureWorks2008 que se ejecuta en SQL Server 2008. Si hay una conexión a la base de datos de ejemplo AdventureWorks2008 en el Explorador de servidores, aparecerá en la lista de Agregar referencia de base de datos (Cuadro de diálogo).

Para conectarse a la base de datos de ejemplo AdventureWorks

Configurar el proyecto de base de datos CLR de SQL

Después de crear el proyecto de base de datos CLR de SQL, quizás necesite modificar algunas de sus propiedades para su correcta implementación.

Para configurar las propiedades del proyecto de base de datos CLR de SQL

  1. En el Explorador de soluciones, seleccione el proyecto cuyas propiedades desea configurar.

  2. En el menú Proyecto, haga clic en Propiedades de NombreProyecto, donde NombreProyecto es el nombre con el que designó el proyecto de base de datos CLR de SQL.

  3. En la pestaña Aplicación, en Nombre del ensamblado, especifique el nombre que desea asignar al ensamblado que se compila desde el proyecto de base de datos CLR de SQL.

  4. En la pestaña Aplicación, en Versión de .NET Framework de destino, haga clic en la versión de .NET Framework compatible con la versión de destino de SQL Server.

    Nota importanteImportante

    SQL Server 2005 y SQL Server 2008 solo admiten proyectos de SQL Server compilados con .NET Framework 2.0. Si intenta implementar un proyecto de SQL Server en SQL Server 2005 o SQL Server 2008, aparece un error: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (donde AssemblyName es el nombre del ensamblado que se implementa).

  5. En la pestaña Base de datos, en Propietario del ensamblado, escriba el nombre de un usuario o rol como propietario del ensamblado.

    Nota importanteImportante

    Este valor debe ser el nombre de un rol del que sea miembro el usuario actual o el usuario actual debe tener el permiso IMPERSONATE. Si no especifica un propietario del ensamblado, se confiere la propiedad al usuario actual. Este valor corresponde al argumento AUTHORIZATION nombre_de_propietario de la instrucción CREATE ASSEMBLY de SQL Server. Para obtener más información, vea CREATE ASSEMBLY (Transact-SQL) en el sitio web de Microsoft.

  6. En el menú Archivo, haga clic en Guardar todo.

Crear el procedimiento almacenado SQL Server

Después de crear el proyecto de base de datos CLR de SQL, agréguele un procedimiento almacenado.

Para crear el procedimiento almacenado de SQL Server

  1. En el menú Proyecto, haga clic en Agregar nuevo elemento.

  2. Seleccione Procedimiento almacenado en el cuadro de diálogo Agregar nuevo elemento.

  3. Escriba InsertCurrency como Nombre para el nuevo procedimiento almacenado.

  4. Haga clic en Add.

  5. Reemplace el código en el Editor de código con lo siguiente:

    Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

Implementar, ejecutar y depurar el procedimiento almacenado

Después de crear un nuevo procedimiento almacenado, se puede generar, implementar en el servidor SQL y depurar presionando F5. Primero, en el archivo Test.sql situado en la carpeta TestScripts de su proyecto, agregue código para ejecutar y probar su procedimiento almacenado. Para obtener más información acerca de cómo crear scripts de prueba, vea Cómo: Editar el script Test.sql para ejecutar objetos que usan la integración de Common Language Runtime de SQL Server.

Para obtener más información acerca de la depuración de SQL, vea Debugging SQL Database Objects.

Para implementar y ejecutar el procedimiento almacenado InsertCurrency

  1. En el Explorador de soluciones, expanda la carpeta SecuenciasDePrueba y haga doble clic en el archivo Test.sql.

    Nota

    Puede especificar otros scripts como script de depuración predeterminado. Para obtener más información, vea Cómo: Editar el script Test.sql para ejecutar objetos que usan la integración de Common Language Runtime de SQL Server.

  2. Reemplace el código del archivo Test.sql con el código siguiente:

    'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

  3. Presione F5 para generar, implementar y depurar el procedimiento almacenado. Para obtener información sobre cómo implementar sin depurar, vea Cómo: Implementar elementos de proyecto de base de datos CLR de SQL en un servidor SQL Server.

    Vea los resultados que se muestran en Salida ventana y seleccione Muestra la salida: Resultado de base de datos.

Vea también

Tareas

Cómo: Crear y ejecutar un procedimiento almacenado de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar un desencadenador de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar un agregado de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar una función definida por el usuario de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar un tipo definido por el usuario de SQL Server mediante la integración de Common Language Runtime

Cómo: Depurar un procedimiento almacenado de SQL CLR

Referencia

Atributos para proyectos de base de datos CLR de SQL Server y objetos de base de datos

Conceptos

Introducción a la integración de CLR y SQL Server (ADO.NET)

Ventajas de utilizar código administrado para crear objetos de base de datos