Spatial SQL -> MS-SQL 2012. Create CLR Stored Procedure. C# script

This C# script creates demo CLR Stored Procedure, that executes select from database table and returns dataset to client. It recives lat/long coordinates and builds geographic point from them. Point object is returned via Output variable. It just shows basic sintax of how to do these things. In order to know how to create C# class library project, type "Create CLR Stored Procedure with Visual Studio" in google search, and you will find guidelines. Create project and save it under name CLR_Lib, for example. Copy compiled dll to database server. Example path: C:\MSSQL\ASSEMBLIES\CLR_Lib.dll

I have to mention that creating CLR Stored Procedures is a cool expirience and this technology can help to produce a lot of usefull functionality.


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System.Collections.Generic;

// ===========================================================================
// Test CLR-Procedures
// ===========================================================================

namespace TestSpace.MyProcs
{
public partial class StoredProcedures
{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void BuildPointGeog(SqlInt32 citId, SqlDouble in_Lat, SqlDouble in_Long, out SqlGeography Geog)
// input parameters:
// citId - city Id
// in_Lat - lattitude
// in_Long - longitude
// Output parameter: Geog (Geography)
{

int i;
List street_list = new List(); // list to store street names

try // try block begins here
{

// create point Geography
Geog = SqlGeography.Point((double)in_Lat, (double)in_Long, 4326);

// Retrieve the Streets of a given city from SQLServer
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
  conn.Open();

  SqlCommand cmdSelectStreets = new SqlCommand("SELECT street FROM Roads WHERE CityID = @id", conn);
  SqlParameter id_param = new SqlParameter("@id", SqlDbType.Int);
  id_param.Value = citId;
  cmdSelectStreets.Parameters.Add(id_param);

  using (SqlDataReader dr = cmdSelectStreets.ExecuteReader())
  {
  while (dr.Read()) // read sected dataset
  {
  street_list.Add((string)dr.GetValue(0));
  }
  }

  cmdSelectStreets.Dispose();
  conn.Close();
}

// ------------------------------------------------
// Send the result dataset records to the client
// ------------------------------------------------

// Dataset Variables
SqlMetaData column1Info;
SqlMetaData column2Info;
SqlDataRecord Record;

// Create the columns metadata
column1Info = new SqlMetaData("n", SqlDbType.Int);
column2Info = new SqlMetaData("street", SqlDbType.VarChar, 80);

// Create a new record based on column metadata
Record = new SqlDataRecord(new SqlMetaData[] { column1Info, column2Info });

SqlContext.Pipe.SendResultsStart(Record);
// Loop through list of values
for (i = 0; i < street_list.Count; i++)
{
  // Set the record fields
  Record.SetValue(0, i+1); // sequential record number
  Record.SetValue(1, street_list[i]); // street name
  // Send
  SqlContext.Pipe.SendResultsRow(Record);
}
SqlContext.Pipe.SendResultsEnd();

// debug print
SqlContext.Pipe.Send("streets dataset count: " + Convert.ToString(i));

return;

} // try block ends here

// Exception trap ---------------------------------------------------------
catch
{
  SqlContext.Pipe.Send("Error. Forced Exit !");
  throw;
}
// ------------------------------------------------------------------------

} // end procedure

// end of class StoredProcedures
}
}



TSQL script that creates CLR ASSEMBLY in your database
USE colombo
GO

EXEC sp_configure 'clr enabled', '1';
GO

RECONFIGURE;
GO

ALTER DATABASE colombo SET TRUSTWORTHY ON;
GO

-- This script registers the SQLCLR procedure

DROP PROCEDURE dbo.BuildPointGeog;

DROP ASSEMBLY CLR_Lib;

CREATE ASSEMBLY CLR_Lib
AUTHORIZATION [dbo]
FROM 'C:\MSSQL\ASSEMBLIES\CLR_Lib.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE dbo.BuildPointGeog
(@citId int, @in_Lat float, @in_Long float, @Geog Geography Out)
AS EXTERNAL NAME CLR_Lib.[TestSpace.MyProcs.StoredProcedures].BuildPointGeog;
GO



Execute test:

DECLARE @Geog Geography;
EXEC dbo.BuildPointGeog 44, 45.466320, 9.196234, @Geog OUT;

SELECT @Geog.STBuffer(1) AS g;





sqlexamples.info