MARKUS



exec sp_configure

exec sp_configure 'clr enabled',1

reconfigure with override

exec sp_configure 'show advanced options',1

reconfigure with override

exec sp_configure 'clr strict security',0;

reconfigure with override

USE master;   

GO    

CREATE ASSEMBLY MarkupDataParse 

FROM 'D:\Markus_V3\dbFunctions\MarkupDataParse.dll'

WITH PERMISSION_SET = SAFE;  

-- dll 트러스터 오류 발생시/ 어셈블리 참조를 찾지 못한다고 나오는 경우

EXEC sp_changedbowner 'sa'

ALTER DATABASE [markus_SEC] SET TRUSTWORTHY ON;

-- 특정 .net dll을 못찾는 경우 select * from sys.dm_clr_properties로 .net framework의 경로에 들어가서 복사한다.

select * from sys.assemblies

USE markus_SEC;   

GO    

CREATE FUNCTION MarkupDataCompress(@inputString NVARCHAR(MAX))

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME MarkupDataParse.[MarkupDataParse.MarkupDataFunctions].CompressString;

using Microsoft.SqlServer.Server;

using System;

using System.IO;

using System.IO.Compression;

using System.Runtime.Serialization.Json;

using System.Text;

namespace MarkupDataParse

{

    public class MarkupDataFunctions

    {

        [SqlFunction]

        public static string CompressString(string str)

        {

            string result = null;

            using (MemoryStream outStream = new MemoryStream())

            {

                using (GZipStream gzipStream = new GZipStream(outStream, CompressionMode.Compress))

                using (MemoryStream srcStream = new MemoryStream(Encoding.UTF8.GetBytes(str)))

                    srcStream.CopyTo(gzipStream);

                result = Convert.ToBase64String(outStream.ToArray());

            }

            return result;

        }

        [SqlFunction]

        public static string DecompressString(string str)

        {

            string result = null;

            str = str.Replace("|DZ|", "");

            byte[] buffer = Convert.FromBase64String(str);

            using (MemoryStream outStream = new MemoryStream())

            {

                using (GZipStream gzipStream = new GZipStream(new MemoryStream(buffer), CompressionMode.Decompress))

                    gzipStream.CopyTo(outStream);

                result = Encoding.UTF8.GetString(outStream.ToArray());

            }

            return result;

        }

        [SqlFunction]

        public static string GetMarkupText(string str)

        {

            string result = null;

            var markupdata = DecompressString(str);

            //var control = Newtonsoft.Json.JsonConvert.DeserializeObject<TextControl>(markupdata);

            //result = control.Text;

            DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(TextControl));

            using (MemoryStream mstream = new MemoryStream(Encoding.Unicode.GetBytes(markupdata)))

            {

                var control = (TextControl)serializer.ReadObject(mstream);

                if (control.Text != null)

                {

                    result = control.Text;

                }

                else if(control.ArrowText != null)

                {

                    result = control.ArrowText;

                }

            }

            return result;

        }

    }

    public class TextControl

    {

        public string Text { get; set; }

        public string ArrowText { get; set; }

    }

}

<?xml version="1.0" encoding="utf-8"?>

<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />

  <PropertyGroup>

    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>

    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>

    <ProjectGuid>{C8BB3E31-C512-4B87-B78F-6042A02594B0}</ProjectGuid>

    <OutputType>Library</OutputType>

    <AppDesignerFolder>Properties</AppDesignerFolder>

    <RootNamespace>MarkupDataParse</RootNamespace>

    <AssemblyName>MarkupDataParse</AssemblyName>

    <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>

    <FileAlignment>512</FileAlignment>

    <Deterministic>true</Deterministic>

    <TargetFrameworkProfile>

    </TargetFrameworkProfile>

  </PropertyGroup>

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">

    <DebugSymbols>true</DebugSymbols>

    <DebugType>full</DebugType>

    <Optimize>false</Optimize>

    <OutputPath>bin\Debug\</OutputPath>

    <DefineConstants>DEBUG;TRACE</DefineConstants>

    <ErrorReport>prompt</ErrorReport>

    <WarningLevel>4</WarningLevel>

    <Prefer32Bit>false</Prefer32Bit>

    <PlatformTarget>AnyCPU</PlatformTarget>

  </PropertyGroup>

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">

    <DebugType>pdbonly</DebugType>

    <Optimize>true</Optimize>

    <OutputPath>bin\Release\</OutputPath>

    <DefineConstants>TRACE</DefineConstants>

    <ErrorReport>prompt</ErrorReport>

    <WarningLevel>4</WarningLevel>

    <Prefer32Bit>false</Prefer32Bit>

  </PropertyGroup>

  <PropertyGroup>

    <SignAssembly>true</SignAssembly>

  </PropertyGroup>

  <PropertyGroup>

    <AssemblyOriginatorKeyFile>MarkupDataParseKey.pfx</AssemblyOriginatorKeyFile>

  </PropertyGroup>

  <PropertyGroup>

    <AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>

  </PropertyGroup>

  <ItemGroup>

    <Reference Include="System" />

    <Reference Include="System.Core" />

    <Reference Include="System.Runtime.Serialization" />

    <Reference Include="System.Xml.Linq" />

    <Reference Include="System.Data.DataSetExtensions" />

    <Reference Include="Microsoft.CSharp" />

    <Reference Include="System.Data" />

    <Reference Include="System.Net.Http" />

    <Reference Include="System.Xml" />

  </ItemGroup>

  <ItemGroup>

    <Compile Include="MarkupDataFunctions.cs" />

    <Compile Include="Properties\AssemblyInfo.cs" />

  </ItemGroup>

  <ItemGroup>

    <None Include="MarkupDataParseKey.pfx" />

    <None Include="package.nuspec" />

  </ItemGroup>

  <ItemGroup>

    <Content Include="InsertAss.SQL" />

  </ItemGroup>

  <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />

</Project>

using System.Reflection;

using System.Runtime.CompilerServices;

using System.Runtime.InteropServices;

// 어셈블리에 대한 일반 정보는 다음 특성 집합을 통해 

// 제어됩니다. 어셈블리와 관련된 정보를 수정하려면

// 이러한 특성 값을 변경하세요.

[assembly: AssemblyTitle("MarkupDataParse")]

[assembly: AssemblyDescription("")]

[assembly: AssemblyConfiguration("")]

[assembly: AssemblyCompany("")]

[assembly: AssemblyProduct("MarkupDataParse")]

[assembly: AssemblyCopyright("Copyright ©  2023")]

[assembly: AssemblyTrademark("")]

[assembly: AssemblyCulture("")]

// ComVisible을 false로 설정하면 이 어셈블리의 형식이 COM 구성 요소에 

// 표시되지 않습니다. COM에서 이 어셈블리의 형식에 액세스하려면

// 해당 형식에 대해 ComVisible 특성을 true로 설정하세요.

[assembly: ComVisible(false)]

// 이 프로젝트가 COM에 노출되는 경우 다음 GUID는 typelib의 ID를 나타냅니다.

[assembly: Guid("c8bb3e31-c512-4b87-b78f-6042a02594b0")]

// 어셈블리의 버전 정보는 다음 네 가지 값으로 구성됩니다.

//

//      주 버전

//      부 버전 

//      빌드 번호

//      수정 버전

//

// 모든 값을 지정하거나 아래와 같이 '*'를 사용하여 빌드 번호 및 수정 번호를

// 기본값으로 할 수 있습니다.

// [assembly: AssemblyVersion("1.0.*")]

[assembly: AssemblyVersion("1.0.0.0")]

[assembly: AssemblyFileVersion("1.0.0.0")]

<?xml version="1.0"?>

<package>

  <metadata>

    <!--*-->

    <id>$id$</id>

    <!--*-->

    <version>$version$</version>

    <title></title>

    <!--*-->

    <authors>$author$</authors>

    <owners>$author$</owners>

<!--

    <licenseUrl></licenseUrl>

    <projectUrl></projectUrl>

    <iconUrl></iconUrl>

-->

    <requireLicenseAcceptance>false</requireLicenseAcceptance>

    <!--*-->

    <description>$description$</description>

    <!--*-->

    <releaseNotes> </releaseNotes>

    <copyright>$copyright$</copyright>

    <tags> </tags>

    <dependencies>

      <!--

      <dependency id="SampleDependency" version="1.0" />

-->

    </dependencies>

  </metadata>

</package>

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>

    <TargetFramework>net48</TargetFramework>

    <IsPackable>false</IsPackable>

  </PropertyGroup>

  <ItemGroup>

    <PackageReference Include="NUnit" Version="3.12.0" />

    <PackageReference Include="NUnit3TestAdapter" Version="3.16.1" />

    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.5.0" />

  </ItemGroup>

  <ItemGroup>

    <ProjectReference Include="..\MarkupDataParse\MarkupDataParse.csproj" />

  </ItemGroup>

</Project>

using NUnit.Framework;

namespace MarkupDataParseTest

{

    public class Tests

    {

        string markupData = "|DZ|H4sIAAAAAAAEAJVSwW4TMRD9lWi4gLC29tpee1cCqU0oQWoLIgEOFFVW4k0sNuvK60LaEokP4cIvwJkfoj/B7G5CgVPxxX5jzbw3b+Ya9utFZaGgBIbBmminboUQTvdGCO6zTAtNqeaKcv2Q5pQ+ON0DAk/q+Qvv6gjFNZytoWBcpQnlMhVCikxJqQmcXUKhM5ZozE0Fk5wqLTYEju3KQ1FfVBWBE9Ox7YfgP07tOg59HYOvkOH5uZlhXQIdz8Qi1dstV6pkInKVsVwJqaWQPZdSIhFMy5wLyaiWXG3ITl3KE5VlgqdMZJxKvlXHdMIEStaaphRRfptx137eEZhEE+LfdvyHRJgGUzelD6ttCaCfKBrwqrHh2Whn1GsbGufrHewMm8TLdnQpufUPs8c+uCu00VSDR48HH2yIbmaqm69fBjffP//89gNLH/gwt2Hirmwr+MDH6FdQoClHtozd46VbLPvX1J/jvWmT1mPbhzPdwTduHpeIMkFgZJplX7AXeOz+XZC7jwDaQt3EgUNrb/Dv7dBXPmDk3iEeigd/SmwTN6Z0C1wO3KX6YvXUx6Wb4d8JGmqqPx+SAg7LNYdubedQlKZqbIvH2NRR39jvWDeTLd78AsRac6QlAwAA";

        [SetUp]

        public void Setup()

        {

        }

        [Test]

        public void Test1()

        {

            var text =MarkupDataParse.MarkupDataFunctions.GetMarkupText(markupData);

            Assert.IsNotNull(text);

        }

    }

}