The CLR in SQL Server 2005

Question

The CLR in SQL Server 2005

Viewing 5 posts - 61 through 64 (of 64 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

Apologies if this is not the right forum for code samples, but here is a quick example of how to set up a sproc called CLR_usp_CopyFile to copy files. For the sake of brevity I have omitted any code not needed for this illustration:

.NET code:

-----------

using System;

using System.Collections;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.IO;

using Microsoft.SqlServer.Server;

namespace fred

{

public sealed partial class FileIO

{

public static void CopyFile( string sourceFileName, string destFileName )

{

using (SqlConnection conn = new SqlConnection("context connection = true"))

{

File.Copy( sourceFileName, destFileName );

}

//end of code

Compile into an assembly, copy dll to location accessible to SQL Server instance.

SQL Script to deploy assembly and build SQLCLR sproc:

-----------------------------------------------------

USE [master]

GO

-- Enter correct path of dll:

CREATE ASYMMETRIC KEY FileIO_Key FROM EXECUTABLE FILE = 'C:\SQLCLR\FileIO.dll';

CREATE LOGIN FileIO_Login FROM ASYMMETRIC KEY FileIO_Key

GRANT EXTERNAL ACCESS ASSEMBLY TO FileIO_Login

GO

-- Create the assembly

CREATE ASSEMBLY FileIO

FROM 'C:\SQLCLR\FileIO.dll' -- Enter correct path of dll

WITH permission_set = EXTERNAL_ACCESS;

GO

--CLR_usp_CopyFile

CREATE PROCEDURE dbo.CLR_usp_CopyFile

(

@SourceFileName NVARCHAR(255)

,@DestFileName NVARCHAR(255)

)

AS EXTERNAL NAME FileIO.[fred.FileIO].CopyFile;

GO

--end of code

Usage:

------

EXEC master.dbo.CLR_usp_CopyFile @SourceFileName = 'c:\fred', @DestFileName = 'd:\fred'

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Sean Zhang-369248 Valued Member Points: 54 More actions · Answer 2

I totally agree with Alan Robbins. .Net framework is powerful, solid and beautiful. Hosting CLR in SQL Server brings the core of .Net to us SQL developers and administrators. It is up to us to learn to harness that power to our advantage.

In our experience with the integration, in addition to the trivial CLR string manipulation (mainly Regex) functions and UDTs, we have successfully brought thirty-party "sealed" objects (either in Win32 DLL, COM, or .Net assemblies) into our SQL applications. For example, we can do massive VIN decoding, address parsing and geo-decoding, yellow/white paging, SSN decoding, and IBM MQ messaging, etc, within our databases natively. That has greatly enhanced programming productivity and processs integrity. Of course, we have had to carefully architect the CLR integration so that all those heavy duty thirty-party objects are hosted on a separate .Net application server with the SQL server being only the .Net remoting client.

System.Runtime.Remoting is not fully supported in SQL 2005. We are looking forward to SQL 2008 to have a more complete CLR support.

Martin Bastable SSCommitted Points: 1766 More actions · Answer 3

On the backend business logic front, ive been steadily increasing how much I put directly in the db rather than in the front end for some time now.

My main db here has several front ends crossing seperate technologies. being able to write a single shared set of instructions on the server once, rather than having to re-write several versions in each front end, I find is a real saver.

CLR wise, most of my interest has been (as others seem to like too) in mroe `support` functions - data validation, string manipulation, that kind of thing. And to that end, I have a dream!

Part of the sql server environment in management studio, an extra `CLR` branch under your database somewhere around Programmability - where adding/editing/deleting/etc. simple (e.g. .net) functions is as easy as doing the same with stored procedures 🙂 No messing around!

martin 🙂

martin!

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 4

rick (1/25/2008)
Grasshopper: I have to take issue with your statement that TSQL is a horrible language. From my 38 years first as a developer and then a DBA, I can vouch for the fact that for the languages I have used over the years, Assembler, Autocoder, Cobol, Basic, RPG, Fortran, and for the past 15 yeas SQL, including TSQL, there are many more bad developers than bad languages. SQL, IF you know how to use it, is an extremely powerful and fast tool. But it's just like woodworking. Having the best tools doesn't buy you a thing if you don't know how to use them. ...

I have to completely agree with Rick. SQL is a beautiful language for what it is intended. There are some things for which it is simply not meant that you can shoehorn it into, but within its domain it is unsurpassed and graceful.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Ryan P. Fonkert SSC Enthusiast Points: 152 More actions · Answer 5

I agree with what Alan and Steve have said. It's great tool that has its uses, but shouldn't be used whenever possible simply because it can be. So far I've only had one case where I found it necessary to create a .NET (CLR) stored procedure, but it was an invaluable tool when I needed it. The procedure involved some pretty advanced string manipulation, with an eventual query string of over 8k, pulling data down from an Oracle db to our SQL Svr instance. For all my other report and ETL processes, I've never actually needed it, even though it'd be cool to use, so didn't use it. But that one time it was a lifesaver, and I wouldn't hesitate to do it again if the situation presented itself.