Partilhar via

Demonstra Passo a passo: Criar um procedimento armazenado no código gerenciado

  • Artigo

Você pode escrever procedimentos armazenados para Microsoft SQL Server 2005 e SQL Server 2008 de bancos de dados em código gerenciado usando.NET Framework, tais como Visual Basic e Visual C#. Procedimentos armazenados que são escritos em código gerenciado são chamados de procedimentos de SQL Server Common Language Runtime armazenado ou procedimentos armazenados CLR de SQL.

Você pode criar os procedimentos armazenado de SQL adicionando Procedimento armazenado itens SQL CLR projetos de banco de dados. Depois de implantar procedimentos armazenados que você criou no código gerenciado, esses procedimentos são chamados e executados como qualquer outro procedimento armazenado.

Tarefas ilustradas nesta explicação passo a passo incluem o seguinte:

  • Criar uma nova SQL projeto de banco de dados de CLR

  • Criar um procedimento armazenado no código gerenciado

  • Implantando o procedimento armazenado para um SQL Server 2008 banco de dados

  • Criar um script para testar o procedimento armazenado no banco de dados

  • Consultar dados no banco de dados para confirmar que o procedimento armazenado executa corretamente

Pré-requisitos

Para concluir esta explicação passo a passo, você precisa:

Criando o projeto

Para criar o novo projeto de banco de dados do CLR de SQL

  1. No menu File, crie um novo projeto.

  2. Selecione Projeto de banco de dados do CLR SQL, nomeie o projeto SQLCLRStoredProcedure e clique em OK. Para obter mais informações, consulte Como: Criar um projeto para os objetos de banco de dados que usam a integração de tempo de execução de linguagem comum SQL Server.

Conectando-se a um banco de dados SQL Server 2008

Esta explicação passo a passo requer uma conexão com o banco de dados de exemplo AdventureWorks2008 em execução no SQL Server 2008. Se uma conexão de banco de dados de exemplo AdventureWorks2008 está disponível em Server Explorer, ele será listado na Add Database Reference Dialog Box.

Para se conectar ao banco de dados de exemplo AdventureWorks

  • Conectar-se ao banco de dados de exemplo AdventureWorks2008 incluído no SQL Server 2008 completando a Add Connection caixa de diálogo. Para obter mais informações, consulte Adicionar/modificar conexão (Microsoft SQL Server).

    - ou -

  • Selecione uma conexão existente no banco de dados de amostra de AdventureWorks2008 na Add Database Reference caixa de diálogo. Para obter mais informações, consulte Add Database Reference Dialog Box.

Configurando o projeto de banco de dados CLR de SQL

Depois de criar seu projeto de banco de dados de SQL de CLR, talvez você precise modificar várias propriedades do projeto antes de implantar com êxito seu projeto.

Para configurar as propriedades do projeto de banco de dados do CLR SQL

  1. No Solution Explorer, selecione o projeto para o qual você deseja configurar propriedades.

  2. Sobre o projeto menu, clique em ProjectName propriedades, onde ProjectName é o nome que você deu o CLR SQL o projeto de banco de dados.

  3. Sobre o aplicativo tab, Assembly nome, especifique o nome que você deseja dar ao assembly que é criado a partir de seu projeto de banco de dados de SQL de CLR.

  4. Sobre o aplicativo tab, Target Framework, clique na versão do.NET Framework que é suportado pela versão de destino do SQL Server.

    Observação importanteImportante

    SQL Server 2005e SQL Server 2008 oferece suporte somente a os projetos deSQL Server que foram criados com o.NET Framework 2.0. Se você tentar implantar um SQL Server projeto SQL Server 2005 ou SQL Server 2008, um erro será exibida: 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(onde AssemblyName é o nome do assembly que você estiver implantando).

  5. Sobre o banco de dados tab, Proprietário do Assembly, digite o nome de um usuário ou função como o proprietário do assembly.

    Observação importanteImportante

    Esse valor deve ser o nome de uma função que o usuário atual for um membro ou o usuário atual deve ter IMPERSONATE permissão. Se você não especificar um proprietário do assembly, a propriedade é atribuída ao usuário atual. Essa configuração corresponde do AUTHORIZATION owner_name argumento para o SQL Server CREATE ASSEMBLY instrução. Para obter mais informações, consulte CREATE ASSEMBLY (Transact-sql) no site da Microsoft.

  6. No menu File, clique em Save All.

Criando o procedimento armazenado SQL Server

Depois de criar o projeto de banco de dados CLR de SQL, adicione um procedimento armazenado a ele.

Para criar o procedimento armazenado SQL Server

  1. No menu Project, clique em Add New Item.

  2. No Add New Item caixa de diálogo, selecione Procedimento armazenado.

  3. Digite InsertCurrency como o Name para o novo procedimento armazenado.

  4. Clique em Adicionar.

  5. Substitua o código no Code Editor com o seguinte:

    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();
        }
    }
}

Implantando, Executando, e Depurando o procedimento armazenado

Após você criar um novo procedimento armazenado, ele pode ser criado, implantado no SQL Server, e depurado, pressionando F5. Primeiro, no arquivo Test.sql encontrado na pasta TestScripts do seu projeto, adicione código para executar e testar seu procedimento armazenado. Para obter mais informações sobre como criar scripts de teste, consulte Como: Edite o Script de Test. SQL para executar objetos usar a integração de tempo de execução de linguagem comum de SQL Server.

Para obter mais informações sobre como depurar SQL, consulte Debugging SQL Database Objects.

Para distribuir, e executar o procedimento armazenado InsertCurrency

  1. No Solution Explorer, expanda a pasta TestScripts e clique duas vezes no arquivo Test.sql.

    ObservaçãoObservação

    Você pode especificar outros scripts como o seu script de depuração padrão. Para obter mais informações, consulte Como: Edite o Script de Test. SQL para executar objetos usar a integração de tempo de execução de linguagem comum de SQL Server.

  2. Substitua o código no arquivo Test.sql com o seguinte código:

    '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. Pressione F5 para criar, implantar e depurar o procedimento armazenado. Para obter informações sobre como implantar sem depuração, consulte Como: Implantar itens de projeto de banco de dados do CLR de SQL um SQL Server.

    Exibir os resultados mostrados na saída janela e selecione Show output from: Saída de banco de dados.

Consulte também

Tarefas

Como: Criar e executar um procedimento armazenado do SQL Server usando a integração do Common Language Runtime

Como: Criar e executar um disparador de SQL Server usando a integração do Common Language Runtime

Como: Criar e executar um agregado de SQL Server usando a integração do Common Language Runtime

Como: Criar e executar uma função de SQL Server User-Defined usando a integração do Common Language Runtime

Como: Criar e executar um tipo de SQL Server User-Defined usando a integração do Common Language Runtime

Como: Depurar um CLR SQL procedimento armazenado

Referência

Atributos para objetos de banco de dados e de projetos de banco de dados do CLR de SQL

Conceitos

Introduction to SQL Server CLR Integration (ADO.NET)

Vantagens de usar código gerenciado para criar objetos de bancos de dados