Trabalhando com SQL CLR – Resumo

Então pessoas, uma das features que mais gosto do SQL Server é o suporte a CLR, que permite muitas possibilidades na criação de Stored Procedures, Funções, Types e Triggers com as linguagens C# e Visual Basic, e outras linguagens .NET, como o Delphi e F#.

No blog, busquei explorar de forma introdutória como trabalhar com esta feature e uma visão geral de cada objeto que é possível desenvolver. Em paralelo também tratei algumas extensibilidades, como trabalhar como F#, Web Services, referenciar projetos de terceiros nos projetos CLR e novidades no lançamento do Visual Studio 2010.

Introdução:

Este é um passo-a-passo de como iniciar com SQL CLR, desde questões mais á nível de SQL Server, até questões á nível de desenvolvimento (debug e publicação).

Integração SQL CLR no SQL Server – Uma Visão Geral [NOVO]

https://sqlfromhell.wordpress.com/2012/04/20/integracao-sql-clr-no-sql-server-visao-geral/

Habilitando CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr

Criando funções CLR para o SQL Server:

https://sqlfromhell.wordpress.com/2009/09/26/funcoes-clr

Debug de funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Publicando Assemblies e Funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/10/publicando-clr

Publicando Assemblies no SQL Server pelo Visual Studio:

https://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Visão Geral:

Para quem quer conhecer cada um dos objetos que permitem desenvolvimento na integração SQL CLR, elaborei uma visão geral orientada á muitos exemplos de cada um.

Stored Procedure:

https://sqlfromhell.wordpress.com/2010/07/03/clr-stored-procedure

https://sqlfromhell.wordpress.com/2010/07/13/clr-stored-procedure-2

User Defined Function Scalar:

https://sqlfromhell.wordpress.com/2010/07/25/trabalhando-com-clr-user-defined-function-scalar

Table-value User Defined Functions:

https://sqlfromhell.wordpress.com/2010/12/02/sql-clr-table-value-user-defined-functions

User Defined Aggregate Functions:

https://sqlfromhell.wordpress.com/2010/12/14/user-defined-aggregate-functions

User Defined Type:

https://sqlfromhell.wordpress.com/2010/12/22/sql-clr-user-defined-type

Triggers:

https://sqlfromhell.wordpress.com/2010/12/31/sql-clr-trigger

Estendendo:

Atendendo alguns pedidos e uma “aventura” pessoal (F#).

Web Services:

https://sqlfromhell.wordpress.com/2010/03/07/webservice-e-funes-clr-no-sql-server

F# no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/17/fsharp

Referenciando uma Class Library em Projetos SQL-CLR (Draft):

https://sqlfromhell.wordpress.com/2009/09/30/crl-classlibrary

Trabalhando com SQL CLR e Spatial Data [NOVO]

https://sqlfromhell.wordpress.com/2011/07/27/trabalhando-com-sql-clr-e-spatial-data/

Novidades:

Como com cada lançamento do Visual Studio temos o suporte SQL CLR permitindo a utilização das novas .NET Framework, mostrei que no caso do Visual Studio 2010 tivemos até algumas novidades mais interessantes.

Novidades do Visual Studio 2010:

https://sqlfromhell.wordpress.com/2010/05/16/o-que-mudou-nos-projetos-sql-clr-do-visual-studio-2010

Imagens no banco de dados – Resumo

Nestes três anos, trabalhei várias formas de utilizar imagens armazenadas em banco de dados com C# e SQL Server, eu espero que tenham gostado:

Imagens no banco de dados 1 – ASP.NET Web Forms

Imagens no banco de dados 2 – ASP.NET MVC

Imagens no banco de dados 3 – Windows Forms

Imagens no banco de dados 4.1 – Relatórios em RDLC

Imagens no banco de dados 4.2 – Relatórios em RDLC

Imagens no banco de dados 5 – Importando arquivos por T-SQL

Trabalhando com SMO – Resumo

Então pessoas, abaixo os links dos artigos que trataram sobre SQL Server Management Objects (SMO) que escrevi utilizando PowerShell e .NET para administrar o SQL Server.

Primeiramente os artigos em C#:

Gerenciando o SQL Server por meio de aplicações .NET – Primeiros passos

Gerando backups do SQL Server por .NET

Gerando scripts do banco de dados por .NET – Passo 1

Gerando scripts do banco de dados por .NET – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com .NET

Criando Constraints e Índices no SQL Server com .NET

Capturando consultas executadas no SQL Server (Trace) com .NET via SMO

Criando um arquivo de trace com .NET via SMO

Capturando eventos do SQL Server com .NET via SMO sem Triggers ou Trace

Capturando eventos de um determinado bancos de dados com .NET via SMO sem Triggers ou Trace

Monitorando Deadlocks do SQL Server com SMO

E os artigos que traduzi para PowerShell:

Gerando backups do SQL Server por PowerShell

Gerando scripts do banco de dados por PowerShell – Passo 1

Gerando scripts do banco de dados por PowerShell – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com PowerShell

Criando Constraints e Índices no SQL Server com PowerShell

Capturando consultas executadas no SQL Server (Trace) com PowerShell via SMO

Criando um arquivo de trace com PowerShell via SMO

Monitorando Deadlocks do SQL Server com SMO

Na semana passada vimos um exemplo de Deadlock com SQL Server, e para monitorar e rastrear eventos deste gênero podemos optar por várias alternativas, dentre elas a utilização de Trace Flags e SQL Server Profiler, e também podemos monitorar de forma bem transparente com os eventos do SMO.

De uma maneira bem simples, o código abaixo monitora os eventos de Deadlock de uma determinada instancia do SQL Server, e quando ocorre uma Deadlock, ele informa os SPID que estão relacionadas a este evento, e gera um Deadlock File para obter maiores detalhes sobre o deadlock.

using System;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent3
    {
        public static void Run()
        {
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            var eventSet = new ServerTraceEventSet(
                ServerTraceEvent.LockDeadlock,
                ServerTraceEvent.LockDeadlockChain,
                ServerTraceEvent.DeadlockGraph
                );

            server.Events.SubscribeToEvents(eventSet, OnDeadlock);

            server.Events.StartEvents();

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnDeadlock(object sender, ServerEventArgs e)
        {
            switch (e.EventType)
            {
                case EventType.DeadlockGraph:
                    DeadlockGraph(e);
                    break;
                case EventType.LockDeadlockChain:
                    LockDeadlockChain(e);
                    break;
                case EventType.LockDeadlock:
                    Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
                    Console.WriteLine("Vítima escolhida: SPID {0}", e.Spid);
                    break;
            }

            Console.WriteLine();
        }

        private static void LockDeadlockChain(ServerEventArgs e)
        {
            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine(((string)textData.Value).TrimEnd());
        }

        private static void DeadlockGraph(ServerEventArgs e)
        {
            var path = string.Format(@"C:\TEMP\{0}.xdl", Guid.NewGuid());

            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            var xml = XDocument.Parse(((string)textData.Value))
                .Elements("TextData")
                .Elements("deadlock-list")
                .FirstOrDefault();

            if (xml == null) return;

            using (var file = new StreamWriter(path)) file.Write(xml.ToString(SaveOptions.DisableFormatting));

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine("Arquivo gerado: {0}", path);
        }
    }
}

A execução do código:

Os arquivos gerados:

E um arquivo de deadlock file gerado pelo SMO aberto no SQL Server Management Studio, possuindo informações sobre os processos, os objetos em lock e a query que foi executada.

Assim terminamos os artigos sobre SMO, espero que tenham gostado de conhecer um pouco mais sobre esta feature.

Capturando eventos de um determinado bancos de dados com .NET via SMO sem Triggers ou Trace

Assim como explicado no artigo da semana passada, o monitoramento de eventos do SMO pode capturar eventos tanto no nível mais macro do SQL Server, quanto nos níveis mais específicos, como eventos relativos a tabelas ou outras estruturas do banco de dados.

Quem viu o artigo da semana passada irá perceber que não há muita diferença para capturar eventos de objetos específicos do SQL Server, pois as diferenças básicas são os objetos aos quais se associam os eventos (eventos de banco de dados, se associa a um objeto Database, eventos de instancia, a um objeto Server) e o grupo de eventos (EventSet) a ser utilizado para identificar quais os eventos que serão monitorados.

No exemplo a seguir veremos como implementar a captura de eventos de um determinado banco de dados:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent2
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var dbEventSet = new DatabaseEventSet(DatabaseEvent.CreateTable);

            // Relacionando os eventos de um determinado banco de dados
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            #region Criando um banco de dados

            var db1 = new Database(server, "NovoBanco");

            db1.Create();

            #endregion

            var database = server.Databases["NovoBanco"];

            database.Events.SubscribeToEvents(dbEventSet, OnCreate);

            // Iniciando a captura dos eventos
            database.Events.StartEvents();

            #region Criando uma tabela no banco de dados criado

            Console.ReadKey();

            var table1 = new Table(db1, "NovaTabela", "dbo");

            table1.Columns.Add(new Column(table1, "Codigo", DataType.Int) { Nullable = false });

            table1.Create();

            #endregion

            // Terminando a captura dos eventos

            Console.ReadKey();

            database.Events.StopEvents();

            database.Events.UnsubscribeFromEvents(dbEventSet);

            #region Excluindo o banco de dados criado

            db1.Drop();

            #endregion
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            Console.WriteLine("Foi criada uma tabela!");

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como resultado deste exemplo:

Aqueles que gostariam de saber como funciona a implementação de eventos de notificação, podem encontrar no link abaixo referências de como implementá-los com T-SQL, no qual conhecimento de Service Broker será indispensável:
http://msdn.microsoft.com/en-us/library/ms178080.aspx

Capturando eventos do SQL Server com .NET via SMO sem Triggers ou Trace

Neste artigo de número 300, vermos uma funcionalidade bem interessante do SMO, que permite capturar eventos do SQL Server, seja em nível de instância, de banco de dados ou até no nível de tabela, permitindo monitorar criação e alterações de estruturas e configurações do SQL Server sem a necessidade de usar triggers ou trace, tudo por meio do serviço de notificações de eventos do Service Broker. Mas se você não “manja” de Service Broker, fique tranquilo, o SMO permite a utilização desta funcionalidade de forma bem transparente.

Neste exemplo, veremos como capturar/monitorar eventos de criação de banco de dados e de tabelas com o SMO:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent1
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var eventSet = new ServerEventSet(ServerEvent.CreateDatabase, ServerEvent.CreateTable);

            // Relacionando os eventos a uma instância do SQL Server
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            server.Events.SubscribeToEvents(eventSet, OnCreate);

            // Iniciando a captura dos eventos
            server.Events.StartEvents();

            Console.ReadKey();

            // Executando alguns comandos para testar se os eventos estão sendo monitorados

            const string query = @"
USE [master]
GO

CREATE DATABASE [NovoBanco]
GO

USE [NovoBanco]

CREATE TABLE [NovaTabela] (CODIGO INT)
GO

USE [master]
GO

DROP DATABASE [NovoBanco]
GO";

            server.ConnectionContext.ExecuteNonQuery(query);

            // Terminando a captura dos eventos

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            switch (e.EventType)
            {
                case EventType.CreateDatabase:
                    Console.WriteLine("Foi criado um banco de dados!");
                    break;
                case EventType.CreateTable:
                    Console.WriteLine("Foi criada uma tabela!");
                    break;
            }

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "TableName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como os eventos serão exibidos neste exemplo:

Próxima semana, eu pretendo demonstrar como capturar/monitorar eventos em um nível mais específico, e na última semana deste mês, veremos como monitorar alguns eventos que vão além de alterações estruturais do banco de dados.

Criando um arquivo de trace com .NET via SMO

Uma prática rápida para otimizar um ou vários bancos de dados é a criação de um arquivo de Trace por meio do SQL Server Profiler e utilizar este arquivo como carga para o Database Engine Tuning Advisor (DTA). O DTA, por sua vez, gerará uma relação de índices e estatísticas para os bancos de dados baseado no arquivo utilizado como carga.

Para a criação deste arquivo de trace, se utiliza normalmente o SQL Server Profiler com o template Tuning. Mas também podemos utilizar as bibliotecas do SMO para gerar este arquivo trace, como veremos a seguir.

Em relação a utilização do SMO, da mesma forma que utilizamos um objeto TraceServer como para capturar as consultas executadas no SQL Server, este objeto também será utilizado neste exemplo para o mesmo fim, só que em conjunto de um objeto TraceFile que escreverá a captura em um arquivo trace.

Então vamos ao código:

using System;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace DemoSmo
{
    static class DemoTrace2
    {
        // Caminho do template de Tuning
        private const string TuningFile =
            @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Tuning.tdf";
        
        /// <summary>
        /// Ref.: http://technet.microsoft.com/en-US/library/ms345134.aspx
        /// </summary>
        public static void Run()
        {
            // Caminho onde será salvo o arquivo de trace
            var file = string.Format(@"C:\Temp\Trace{0:yyyy-MM-dd HH-mm}.trc", DateTime.Now);

            var conn = new SqlConnectionInfo { ServerName = @"DALILAH" };

            // Criando o objeto TraceServer
            var traceReader = new TraceServer();

            // Iniciando a leitura do trace a partir do template Tuning do SQL Server Profiler
            traceReader.InitializeAsReader(conn, TuningFile);

            // Criando o objeto TraceFile
            var traceFile = new TraceFile();

            // Iniciando a escrita da captura no arquivo de trace
            traceFile.InitializeAsWriter(traceReader, file);

            // Executando a escrita dos traces
            var ts = new Thread(() => { while (traceFile.Write()) { } });

            ts.Start();

            // Esperar 30 segundos
            Thread.Sleep(30000);

            // Terminando a escrita do trace
            traceFile.Close();

            // Terminando a captura do trace
            traceReader.Close();
        }
        
    }
}

E o nosso resultado:

Capturando consultas executadas no SQL Server (Trace) com .NET via SMO

Como visto na semana passada, é possível utilizar o SQL Server Profiler para identificar as consultas que são executadas em uma instancia do SQL Server, além de outros dados, como o tempo de execução, quem esta executando e etc…

Mas seria possível automatizar este processo com SMO?

A resposta a esta pergunta é “Sim”, mas para um primeiro exemplo, veremos como criar e exibir Traces com o SMO, e na próxima semana veremos como automatizar o processo de criação de um Trace para Tuning (ótimo para quem vai utilizar o Database Engine Tuning Advisor) gerando um arquivo de trace compatível com o SQL Server Profiler.

Então vamos ao exemplo!

As DLLs responsáveis para a utilização das bibliotecas de trace são:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.ConnectionInfoExtended.dll

Para este exemplo utilizei o Template Standard do SQL Server Profiler, mas é possível utilizar outros templates ou até mesmo criar novos templates pelo SQL Server Profiler.

E abaixo nosso código de utilização de trace:

using System;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace DemoSmo
{
    static class DemoTrace1
    {
        private const string StandardFile =
            @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf";

        public static void Run()
        {
            var conn = new SqlConnectionInfo { ServerName = @".\SQLEXPRESS" };

            // Criando o objeto TraceServer
            var traceReader = new TraceServer();

            // Iniciando a leitura do trace a partir do template Standard do SQL Server Profiler
            traceReader.InitializeAsReader(conn, StandardFile);

            // Criando uma Thread para a leitura do trace
            var ts =
                new Thread(
                    () =>
                    {
                        while (traceReader.Read())
                        {
                            // Identificando o tipo de evento
                            var name = traceReader.GetValue(traceReader.GetOrdinal("EventClass"));

                            if (!name.Equals("SQL:BatchCompleted") && !name.Equals("RPC:Completed")) continue;

                            // Identificando o SPID que esta executando o comando
                            var spid = traceReader.GetValue(traceReader.GetOrdinal("SPID"));

                            // Identificando informações do que é executado
                            var query = traceReader.GetValue(traceReader.GetOrdinal("TextData"));

                            // .. duração da execução
                            var duration = traceReader.GetValue(traceReader.GetOrdinal("Duration"));

                            Console.WriteLine("--SPID: {0} | Duration: {1}", spid, duration);

                            Console.WriteLine();

                            Console.WriteLine(query);

                            Console.WriteLine();
                        }
                    });

            // Iniciando a Thread
            ts.Start();

            // Esperando 30 segundos
            Thread.Sleep(30000);

            // Fechando o trace de leitura
            traceReader.Close();

            // Fechando a Thread
            ts.Join();
        }
    }
}

E por fim, o resultado da execução deste código:

Desculpe para quem não “manja” de Thread no C#, mas não conheço maneira melhor de criar este exemplo sem Thread.

Abaixo o artigo do Slavik Krassovsky, que utilizei para entender o funcionamento dos objetos de Trace do SMO:
http://technet.microsoft.com/en-US/library/ms345134.aspx

Criando Constraints e Índices no SQL Server com .NET

No artigo da semana passada vimos como criar colunas e tabelas com .NET e com PowerShell, nesta semana veremos como é possível também criar Constraints (Foreign Key, Default, Unique Key, Primary Key) e Índices com SMO.

Inicialmente, criaremos duas tabelas bem simples:

const string serverName = @".\SQLEXPRESS";

var server = new Server(serverName);

var database = new Database(server, "Estoque");
database.Create();

// CREATE DATABASE [Estoque] ...
var table = new Table(database, "Produto", "dbo");
table.Columns.Add(new Column(table, "Id", DataType.Int) { Nullable = false, Identity = true });
table.Columns.Add(new Column(table, "Nome", DataType.VarChar(250)) { Nullable = false });
table.Columns.Add(new Column(table, "Quantidade", DataType.Int) { Nullable = false });
table.Columns.Add(new Column(table, "Valor", DataType.Money) { Nullable = false });
table.Columns.Add(new Column(table, "Peso", DataType.Decimal(2, 5)) { Nullable = false });
table.Columns.Add(new Column(table, "CategoriaId", DataType.Int) { Nullable = false });
table.Columns.Add(new Column(table, "DataCadastro", DataType.DateTime) { Nullable = false });
table.Create();

// CREATE TABLE [dbo].[Categoria] ...
var table2 = new Table(database, "Categoria", "dbo");
table2.Columns.Add(new Column(table2, "Id", DataType.Int) { Nullable = false, Identity = true });
table2.Columns.Add(new Column(table2, "Nome", DataType.VarChar(250)) { Nullable = false });
table2.Create();

E em seguida, criamos as Primary Keys destas tabelas, por meio de um objeto Index, especificado como DriPrimaryKey:

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [PK_Produto] PRIMARY KEY ([Id])
var pk = new Index(table, "PK_Produto") { IndexKeyType = IndexKeyType.DriPrimaryKey };
pk.IndexedColumns.Add(new IndexedColumn(pk, "Id"));
pk.Create();

// ALTER TABLE [dbo].[Categoria]
//   ADD CONSTRAINT [PK_Categoriao] PRIMARY KEY (Id)
var pk2 = new Index(table2, "PK_Categoria") { IndexKeyType = IndexKeyType.DriPrimaryKey };
pk2.IndexedColumns.Add(new IndexedColumn(pk2, "Id"));
pk2.Create();

No caso das Unique Keys, é possível utilizar também de um objeto Index, agora como DriUniqueKey:

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [UK_Produto_Nome] UNIQUE ([Nome])
var uk = new Index(table, "UK_Produto_Nome") { IndexKeyType = IndexKeyType.DriUniqueKey };
uk.IndexedColumns.Add(new IndexedColumn(uk, "Nome"));
uk.Create();

Para a criação de índices, criamos um objeto Index sem especificar a propriedade IndexKeyType, e definimos quais as colunas que o compõe, assim como as colunas inclusas (IsIncluded):

// CREATE INDEX [IX_Produto_NmQtVl]
//   ON [dbo].[Produto] ([Nome], [Quantidade], [Valor])
//   INCLUDE ([Peso])
var ix = new Index(table, "IX_Produto_NmQtV");
ix.IndexedColumns.Add(new IndexedColumn(ix, "Nome"));
ix.IndexedColumns.Add(new IndexedColumn(ix, "Quantidade"));
ix.IndexedColumns.Add(new IndexedColumn(ix, "Valor"));
ix.IndexedColumns.Add(new IndexedColumn(ix, "Peso") { IsIncluded = true });
ix.Create();

As Default Contraints são um pouco mais chatas de serem criadas, pois requerem a utilização de uma coluna (objeto Column) e a utilização do método AddDefaultConstraint para serem adicionadas:

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [DF_Produto_DataCadastro] DEFAULT (GETDATE()) FOR [DataCadastro]
var col = table.Columns["DataCadastro"];
var def = col.AddDefaultConstraint("DF_Produto_DataCadastro");
def.Text = "(GETDATE())";
def.Create();

As Foreign Keys, somente requerem a especificação da tabela a ser referenciada e das colunas que a referenciam.

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [PK_Categoriao] FOREIGN KEY ([CategoriaId])
//   REFERENCE [dbo].[Categoria] ([Id])
var fk = new ForeignKey(table, "FK_Produto_Categoria")
                {
                    ReferencedTableSchema = table2.Schema,
                    ReferencedTable = table2.Name
                };
fk.Columns.Add(new ForeignKeyColumn(fk, "CategoriaId", "Id"));
fk.Create();

Criando e manipulando Tabelas e Colunas no SQL Server com .NET

Mesmo se tratando de algo simples para ser feito com T-SQL, as bibliotecas do SMO permitem ao DBA/desenvolvedor uma forma transparente de criar e manipular objetos do SQL Server sem necessidade de uma linha de T-SQL.

Para um primeiro exemplo, vamos criar um banco de dados:

const string serverName = @".\SQLEXPRESS";

var server = new Server(serverName);

// CREATE DATABASE [Estoque]
var database = new Database(server, "Estoque");
database.Create();

Neste banco de dados, vamos criar uma tabela e suas colunas:

// CREATE TABLE [dbo].[Produto]
var table = new Table(database, "Produto", "dbo");

//   [Id] [int] IDENTITY(1,1) NOT NULL
table.Columns.Add(new Column(table, "Id", DataType.Int) { Nullable = false, Identity = true });

//   [Nome] [varchar](250) NOT NULL
table.Columns.Add(new Column(table, "Nome", DataType.VarChar(250)) { Nullable = false });

//   [Quantidade] [int] NOT NULL
table.Columns.Add(new Column(table, "Quantidade", DataType.Int) { Nullable = false });

//   [Valor] [money] NOT NULL
table.Columns.Add(new Column(table, "Valor", DataType.Money) { Nullable = false });

//   [Peso] [decimal](5, 2) NOT NULL
table.Columns.Add(new Column(table, "Peso", DataType.Decimal(2, 5)) { Nullable = false });

//   [Frete] [money] NULL
table.Columns.Add(new Column(table, "Frete", DataType.Money) { Nullable = true });

table.Create();

Nesta tabela, podemos remover colunas de forma bem simples:

// ALTER TABLE [dbo].[Produto]
//   DROP COLUMN [Frete]
table.Columns["Frete"].Drop();

Assim como adicionar uma Primary Key para a tabela:

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [PK_Produto] PRIMARY KEY ([Id])
var pk = new Index(table, "PK_Produto") { IndexKeyType = IndexKeyType.DriPrimaryKey };
pk.IndexedColumns.Add(new IndexedColumn(pk, "Id"));
pk.Create();

Ao se tratar de adicionar colunas em uma tabela, de duas formas isso poderá ser feito, sendo a primeira por meio o objeto Table e o método Alter:

// ALTER TABLE [dbo].[Produto]
//   ADD [CategoriaId] [int] NOT NULL
table.Columns.Add(new Column(table, "CategoriaId", DataType.Int) { Nullable = false });
table.Alter();

E a segunda forma, pelo objeto Column e o método Create:

// ALTER TABLE [dbo].[Produto]
//   ADD [DataCadastro] [datetime] NOT NULL
var col = new Column(table, "DataCadastro", DataType.DateTime) { Nullable = false };
col.Create();

Para apagar uma tabela ou até mesmo o banco de dados inteiro, podemos utilizar o método Drop do respectivo objeto a ser apagado:

// DROP TABLE [dbo].[Produto]
table.Drop();

// DROP DATABASE [dbo].[Estoque]
database.Drop();

Na próxima semana, veremos também como é possível criar Foreign Keys, Unique Keys, Default Constraints e Indexes com SMO.