How was GPDI Data and Tech 2019 in Fortaleza / Ceará

Views: 113 views

Reading Time: 3 minutes

Speak guys!
In this post I would like to share with you the photos and materials of my presentation on GPDI Data and Tech 2019 in Fortaleza / Ceará, organized by Rafael Mendonça. And it was a pleasure to meet this angry guys from GPDI and Fortaleza. It is always a pleasure to share knowledge about what we like and try to bring something new and / or things different from what people already knew.

My talk was on the topic “SQLCLR: Transforming your SQL Server into something much more than a database”, where I was able to explain to the event participants what SQLCLR is, how it works, what we can do with it, what are its advantages and disadvantages, part of security, performance benefits (which are many and VERY significant) and, of course, a demo of almost 20 mins showing several incredible features that we can use within SQL Server with this feature.

Slides and Demo of my talk

DEMO script used (must have my library installed):

-- Serviços e Processos
EXEC CLR.dbo.stpServicos_Listar
    @Ds_Servidor = N'dirceu-notebook', -- nvarchar(max)
    @Ds_Servico = N'SQL Server%' -- nvarchar(max)

EXEC CLR.dbo.stpProcessos_Listar 
	@Ds_Servidor = N'dirceu-notebook' -- nvarchar(max)

SELECT * FROM CLR.dbo.fncServicos_Listar('dirceu-notebook')

SELECT * FROM CLR.dbo.fncProcessos_Listar('dirceu-notebook') WHERE Ds_Processo LIKE 'sql%'


SELECT * FROM CLR.dbo.fncProcessos_Listar('dirceu-notebook') WHERE Ds_Processo = 'notepad.exe'

EXEC CLR.dbo.stpProcessos_Eliminar 
	@Nr_PID = 17136 -- int


-- SERVER INFO
SELECT * FROM CLR.dbo.fncDBA_Informacao_Disco('dirceu-notebook')
SELECT * FROM CLR.dbo.fncDBA_Server_Info('dirceu-notebook')
SELECT * FROM CLR.dbo.fncDBA_Server_Info_Instancias('dirceu-notebook')
SELECT * FROM CLR.dbo.fncDBA_Server_Info_Minimal('dirceu-notebook')

SELECT * FROM CLR.dbo.fncEvent_Viewer_Listar('dirceu-notebook', 'Application', '2019-05-31', '2019-06-02')

EXEC CLR.dbo.stpEvent_Viewer_Gravar
    @Ds_Servidor = N'dirceu-notebook', -- nvarchar(max)
    @Ds_Tipo_Log = N'Application', -- nvarchar(max)
    @Ds_Tipo_Evento = N'Information', -- nvarchar(max)
    @Ds_Fonte = N'SQLCLR', -- nvarchar(max)
    @Ds_Mensagem = N'Teste de mensagem do SQLCLR ', -- nvarchar(max)
    @Id_Evento = 1234 -- int


SELECT * 
FROM CLR.dbo.fncEvent_Viewer_Listar('dirceu-notebook', 'Application', '2019-05-01', '2019-06-02')
WHERE Ds_Fonte = 'SQLCLR'



-- WEB
SELECT * FROM CLR.dbo.fncFeriados(2019, 'CE', 'Fortaleza')


EXEC CLR.dbo.stpEnvia_Torpedo_Pushbullet
    @Nr_Numero = N'+552798888888888', -- nvarchar(max)
    @Ds_Mensagem = N'Teste SQLCLR' -- nvarchar(max)


EXEC CLR.dbo.stpDownload_Arquivo_Remoto
    @URL = N'//dirceuresende.com/wp-content/uploads/2016/09/Pushbullet-API-send-sms-to-smartphone-php-csharp-java-sql-server-clr-3.jpg', -- nvarchar(max)
    @strArquivoDestino = N'C:\Temporario\Minha imagem.jpg', -- nvarchar(max)
    @usuario = N'', -- nvarchar(max)
    @senha = N'' -- nvarchar(max)


EXEC CLR.dbo.stpCompacta_Arquivo
    @caminho = N'C:\Temporario\', -- nvarchar(max)
    @filtro = N'*.jpg', -- nvarchar(max)
    @arquivoCompactado = N'C:\Temporario\Meu Arquivo.zip', -- nvarchar(max)
    @nivelCompactacao = 5, -- int
    @senha = N'dirceu' -- nvarchar(max)


CREATE OR ALTER PROCEDURE dbo.stpProcedure_Criptografada
WITH encryption
AS
BEGIN
	SELECT 1
	PRINT 'Teste de procedure criptografada'
END

sp_helptext 'dbo.stpProcedure_Criptografada'

PRINT CLR.dbo.fncDescriptografa_Objeto('dirceu-notebook\sql2017', 'master', 'dbo', 'stpProcedure_Criptografada')



DECLARE @Ds_Retorno_OUTPUT NVARCHAR(MAX);

EXEC CLR.dbo.stpWs_Requisicao
    @Ds_Url = N'https://rastrojs.herokuapp.com/track/PU187010046BR/json', -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = N'', -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/json', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = N'', -- nvarchar(max)
    @Qt_Segundos_Timeout = 20, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


SELECT 
	jsonImportado.code,
    jsonImportado.[message],
    arrData.isDelivered,
    arrData.postedAt,
    arrData.updatedAt,
    arrTrack.[status],
    arrTrack.observation,
    arrTrack.trackedAt,
    arrTrack.unit
FROM OPENJSON(@Ds_Retorno_OUTPUT, '$') WITH (
	[code] INT,
	[message] VARCHAR(100),
	[data] NVARCHAR(MAX) AS JSON
) AS jsonImportado
OUTER APPLY OPENJSON(jsonImportado.[data]) WITH (
	isDelivered VARCHAR(10),
	postedAt DATETIME,
	updatedAt DATETIME,
	[track] NVARCHAR(MAX) AS JSON
) AS arrData
OUTER APPLY OPENJSON(arrData.track) WITH (
	[status] VARCHAR(50),
	observation VARCHAR(500),
	trackedAt DATETIME,
	[unit] VARCHAR(100)
) AS arrTrack





-- ARQUIVOS
SELECT * FROM CLR.dbo.fncArquivo_Listar('C:\Temporario', '*', 0)
SELECT * FROM CLR.dbo.fncArquivo_Ler('C:\Temporario\Injection.txt')


IF (OBJECT_ID('tempdb..##Teste') IS NOT NULL) DROP TABLE ##Teste
EXEC CLR.dbo.stpImporta_CSV2
    @Ds_Caminho_Arquivo = N'C:\Temporario\FL_insurance_sample.csv', -- nvarchar(max)
    @Ds_Separador = N',', -- nvarchar(max)
    @Fl_Primeira_Linha_Cabecalho = 1, -- bit
    @Nr_Linha_Inicio = 0, -- int
    @Nr_Linhas_Retirar_Final = 0, -- int
    @Ds_Tabela_Destino = '##Teste', -- nvarchar(max)
    @Ds_Codificacao = N'utf-8' -- nvarchar(max)

SELECT * FROM ##Teste


EXEC CLR.dbo.stpImporta_Excel2
    @Caminho = N'C:\Temporario\Planilha.xlsx', -- nvarchar(max)
    @Aba = N'Plan1', -- nvarchar(max)
    @Colunas = N'*' -- nvarchar(max)



EXEC CLR.dbo.stpExporta_Query_Txt
    @query = N'SELECT * FROM sys.objects', -- nvarchar(max)
    @separador = N'|', -- nvarchar(max)
    @caminho = N'C:\Temporario\objects.csv', -- nvarchar(max)
    @Fl_Coluna = 1 -- int


EXEC CLR.dbo.stpRenomeia_Arquivo
    @Caminho_Origem = N'C:\Temporario\objects.csv', -- nvarchar(max)
    @Caminho_Destino = N'C:\Temporario\objetos.csv', -- nvarchar(max)
    @Fl_Sobrescrever = 1 -- bit


EXEC CLR.dbo.stpCopia_Arquivo
    @origem = N'C:\Temporario\objetos.csv', -- nvarchar(max)
    @destino = N'C:\Temporario\objetos2.csv', -- nvarchar(max)
    @sobrescrever = 1 -- bit


EXEC CLR.dbo.stpApaga_Arquivo 
	@caminho = N'C:\Temporario\objetos.csv' -- nvarchar(max)


SELECT 
	CLR.dbo.fncArquivo_Tamanho('C:\Temporario\objetos2.csv'),
	CLR.dbo.fncArquivo_Tamanho_Disco('C:\Temporario\objetos2.csv'),
	CLR.dbo.fncArquivo_Existe('C:\Temporario\objetos2.csv'),
	CLR.dbo.fncArquivo_Existe('C:\Temporario\objetos.csv')


SELECT * FROM CLR.dbo.fncArquivo_Ler('C:\Temporario\objetos2.csv')

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

-- Serviços e Processos

EXEC CLR.dbo.stpServicos_Listar

@Ds_Servidor = N'dirceu-notebook', -- nvarchar(max)

@Ds_Servico = N'SQL Server%' -- nvarchar(max)

EXEC CLR.dbo.stpProcessos_Listar

@Ds_Servidor = N'dirceu-notebook' -- nvarchar(max)

SELECT * FROM CLR.dbo.fncServicos_Listar('dirceu-notebook')

SELECT * FROM CLR.dbo.fncProcessos_Listar('dirceu-notebook') WHERE Ds_Processo LIKE 'sql%'

SELECT * FROM CLR.dbo.fncProcessos_Listar('dirceu-notebook') WHERE Ds_Processo = 'notepad.exe'

EXEC CLR.dbo.stpProcessos_Eliminar

@Nr_PID = 17136 -- int

-- SERVER INFO

SELECT * FROM CLR.dbo.fncDBA_Informacao_Disco('dirceu-notebook')

SELECT * FROM CLR.dbo.fncDBA_Server_Info('dirceu-notebook')

SELECT * FROM CLR.dbo.fncDBA_Server_Info_Instancias('dirceu-notebook')

SELECT * FROM CLR.dbo.fncDBA_Server_Info_Minimal('dirceu-notebook')

SELECT * FROM CLR.dbo.fncEvent_Viewer_Listar('dirceu-notebook', 'Application', '2019-05-31', '2019-06-02')

EXEC CLR.dbo.stpEvent_Viewer_Gravar

@Ds_Servidor = N'dirceu-notebook', -- nvarchar(max)

@Ds_Tipo_Log = N'Application', -- nvarchar(max)

@Ds_Tipo_Evento = N'Information', -- nvarchar(max)

@Ds_Fonte = N'SQLCLR', -- nvarchar(max)

@Ds_Mensagem = N'Teste de mensagem do SQLCLR ', -- nvarchar(max)

@Id_Evento = 1234 -- int

SELECT *

FROM CLR.dbo.fncEvent_Viewer_Listar('dirceu-notebook', 'Application', '2019-05-01', '2019-06-02')

WHERE Ds_Fonte = 'SQLCLR'

-- WEB

SELECT * FROM CLR.dbo.fncFeriados(2019, 'CE', 'Fortaleza')

EXEC CLR.dbo.stpEnvia_Torpedo_Pushbullet

@Nr_Numero = N'+552798888888888', -- nvarchar(max)

@Ds_Mensagem = N'Teste SQLCLR' -- nvarchar(max)

EXEC CLR.dbo.stpDownload_Arquivo_Remoto

@URL = N'//dirceuresende.com/wp-content/uploads/2016/09/Pushbullet-API-send-sms-to-smartphone-php-csharp-java-sql-server-clr-3.jpg', -- nvarchar(max)

@strArquivoDestino = N'C:\Temporario\Minha imagem.jpg', -- nvarchar(max)

@usuario = N'', -- nvarchar(max)

@senha = N'' -- nvarchar(max)

EXEC CLR.dbo.stpCompacta_Arquivo

@caminho = N'C:\Temporario\', -- nvarchar(max)

@filtro = N'*.jpg', -- nvarchar(max)

@arquivoCompactado = N'C:\Temporario\Meu Arquivo.zip', -- nvarchar(max)

@nivelCompactacao = 5, -- int

@senha = N'dirceu' -- nvarchar(max)

CREATE OR ALTER PROCEDURE dbo.stpProcedure_Criptografada

WITH encryption

BEGIN

SELECT 1

PRINT 'Teste de procedure criptografada'

END

sp_helptext 'dbo.stpProcedure_Criptografada'

PRINT CLR.dbo.fncDescriptografa_Objeto('dirceu-notebook\sql2017', 'master', 'dbo', 'stpProcedure_Criptografada')

DECLARE @Ds_Retorno_OUTPUT NVARCHAR(MAX);

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = N'https://rastrojs.herokuapp.com/track/PU187010046BR/json', -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'utf-8', -- nvarchar(max)

@Ds_Accept = N'', -- nvarchar(max)

@Ds_ContentType = N'application/json', -- nvarchar(max)

@Fl_Autentica_Proxy = 0, -- bit

@Ds_Headers = N'', -- nvarchar(max)

@Qt_Segundos_Timeout = 20, -- int

@Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)

SELECT

jsonImportado.code,

jsonImportado.[message],

arrData.isDelivered,

arrData.postedAt,

arrData.updatedAt,

arrTrack.[status],

arrTrack.observation,

arrTrack.trackedAt,

arrTrack.unit

FROM OPENJSON(@Ds_Retorno_OUTPUT, '$') WITH (

[code] INT,

[message] VARCHAR(100),

[data] NVARCHAR(MAX) AS JSON

) AS jsonImportado

OUTER APPLY OPENJSON(jsonImportado.[data]) WITH (

isDelivered VARCHAR(10),

postedAt DATETIME,

updatedAt DATETIME,

[track] NVARCHAR(MAX) AS JSON

) AS arrData

OUTER APPLY OPENJSON(arrData.track) WITH (

[status] VARCHAR(50),

observation VARCHAR(500),

trackedAt DATETIME,

[unit] VARCHAR(100)

) AS arrTrack

-- ARQUIVOS

SELECT * FROM CLR.dbo.fncArquivo_Listar('C:\Temporario', '*', 0)

SELECT * FROM CLR.dbo.fncArquivo_Ler('C:\Temporario\Injection.txt')

IF (OBJECT_ID('tempdb..##Teste') IS NOT NULL) DROP TABLE ##Teste

EXEC CLR.dbo.stpImporta_CSV2

@Ds_Caminho_Arquivo = N'C:\Temporario\FL_insurance_sample.csv', -- nvarchar(max)

@Ds_Separador = N',', -- nvarchar(max)

@Fl_Primeira_Linha_Cabecalho = 1, -- bit

@Nr_Linha_Inicio = 0, -- int

@Nr_Linhas_Retirar_Final = 0, -- int

@Ds_Tabela_Destino = '##Teste', -- nvarchar(max)

@Ds_Codificacao = N'utf-8' -- nvarchar(max)

SELECT * FROM ##Teste

EXEC CLR.dbo.stpImporta_Excel2

@Caminho = N'C:\Temporario\Planilha.xlsx', -- nvarchar(max)

@Aba = N'Plan1', -- nvarchar(max)

@Colunas = N'*' -- nvarchar(max)

EXEC CLR.dbo.stpExporta_Query_Txt

@query = N'SELECT * FROM sys.objects', -- nvarchar(max)

@separador = N'|', -- nvarchar(max)

@caminho = N'C:\Temporario\objects.csv', -- nvarchar(max)

@Fl_Coluna = 1 -- int

EXEC CLR.dbo.stpRenomeia_Arquivo

@Caminho_Origem = N'C:\Temporario\objects.csv', -- nvarchar(max)

@Caminho_Destino = N'C:\Temporario\objetos.csv', -- nvarchar(max)

@Fl_Sobrescrever = 1 -- bit

EXEC CLR.dbo.stpCopia_Arquivo

@origem = N'C:\Temporario\objetos.csv', -- nvarchar(max)

@destino = N'C:\Temporario\objetos2.csv', -- nvarchar(max)

@sobrescrever = 1 -- bit

EXEC CLR.dbo.stpApaga_Arquivo

@caminho = N'C:\Temporario\objetos.csv' -- nvarchar(max)

SELECT

CLR.dbo.fncArquivo_Tamanho('C:\Temporario\objetos2.csv'),

CLR.dbo.fncArquivo_Tamanho_Disco('C:\Temporario\objetos2.csv'),

CLR.dbo.fncArquivo_Existe('C:\Temporario\objetos2.csv'),

CLR.dbo.fncArquivo_Existe('C:\Temporario\objetos.csv')

SELECT * FROM CLR.dbo.fncArquivo_Ler('C:\Temporario\objetos2.csv')

If you are interested in learning more about SQLCLR and how to extend SQL Server functionality using C # / VB.NET, I recommend reading my article. Introduction to SQL Common Language Runtime (CLR) in SQL Server, where I explain all the concepts of SQLCLR (as I did in this talk) and demonstrate how to create your first SQLCLR library until you publish and use your project in SQL Server.

Very soon, I will be making available on GitHub, the source code of my SQLCLR library, which I use in some clients and also in those we give in my lectures. Stay tuned to follow this news and start increasing (and much) the your daily productivity.