### Creating User Defined Type PointType and Table Function Dept_GeoLocations that Uses PointType (so Table Function Dept_GeoLocations uses PointType)

1. Create User Defined Type named PointType as follow:

a) Write the body of UDT PointType in C# .Net

Structure: x // x coordinate

y // y coordinate

ToString //x:y

Methods: Parse an input string x:y,

Get each member / Set each Member,

Check IsNull

Example: if (s.IsNull) { return Null; }

b) Create CLR UDT PointType to be able to be used in the following two scripts below for the lab output of Part 1.

c) Test your UDT with the following two scripts:

Example use of PointType:

This is a possible test script that uses the UDT you created.

Test Script1: CREATE TABLE dbo.TablePoints ( ID int IDENTITY(1,1) PRIMARY KEY, Pnt PointType)

INSERT INTO dbo.TablePoints (Pnt) VALUES (CONVERT(PointType, '3:4'))

INSERT INTO dbo.TablePoints (Pnt) VALUES (CONVERT(PointType, '-1:5'))

INSERT INTO dbo.TablePoints (Pnt) VALUES (CAST ('1:99' AS PointType))

SELECT ID, Pnt.ToString() as StringPoint, Pnt.X as X, Pnt.Y as Y

FROM dbo.TablePoints

Test Script2: CREATE TABLE Locations (grid_location dbo.PointType)

INSERT INTO Locations (grid_location) VALUES ('3:2')

INSERT INTO Locations (grid_location) VALUES ('-1:1')

INSERT INTO Locations (grid_location) VALUES ('-1:-1')

INSERT INTO Locations (grid_location) VALUES ('-8:-9')

INSERT INTO Locations (grid_location) VALUES ('4:-9')

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

-- -- view the data --

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

SELECT * FROM dbo.Locations

SELECT grid_location.X AS "X", grid_location.Y AS "Y", grid_location.ToString() AS "Point"

FROM Locations

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

-- -- update some rows --

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

UPDATE dbo.Locations SET grid_location.X = 5 WHERE grid_location.Y < 0

SELECT grid_location.X AS "X", grid_location.Y AS "Y", grid_location.ToString() AS "Point"

FROM Locations;

2. Create Table Function Dept_GeoLocations that uses the above created PointType and RETURNS TABLE Dept_Geolocations

Example : CREATE FUNCTION Geolocations()

RETURNS @Dept_GeoLocations TABLE (

Dnumber int, Dlocation varchar(15), Geolocation PointType )

AS

BEGIN

PS: Also with all the code please explain me how to make a connection between visual studio and sql server. I'm using visual studio 2017 and ssms 17.