---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第13章 CLR関数とトリガ
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 13.1 CLR関数
---------------------------------------------------------------------

[SqlFunction]
public static String cf_DateConv(DateTime dt, string format)
{
    return dt.ToString(format);
}


CREATE ASSEMBLY MyCShrpUdf FROM 'MyCShrpUdf.dll'
GO
CREATE FUNCTION dbo.cf_DateConv(@dt DATETIME, @format NVARCHAR(50))
RETURNS NVARCHAR(30)
AS EXTERNAL NAME MyCShrpUdf.CLRModules.cf_DateConv


SELECT [Asset5].[dbo].[cf_DateConv] (GETDATE(), 'dddd - d - MMMM')


[SqlFunction]
public static String cf_DateConv_DtFmtCult(string dt,
                                           string format,
                                           string culture)
{
    CultureInfo MyCultureInfo = new CultureInfo(culture);
    DateTime MyDateTime = DateTime.Parse(dt, MyCultureInfo);
    return MyDateTime.ToString(format);
}


SELECT dbo.cf_DateConv_DtFmtCult ('12 June 2002',
                                  'dddd - d - MMMM, yyyy',
                                  'de-DE')


[SqlFunction]
public static bool cf_IsValidEmail(string email)
{
    // 有効な電子メールアドレスかどうかを検査する
    return Regex.IsMatch(email,
        @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");
}


SELECT dbo.cf_IsValidEmail('dejan@trigonblue.com')
SELECT dbo.cf_IsValidEmail('dejan@trigon.blue..com')


[SqlFunction(DataAccess = DataAccessKind.None)]


[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int cf_OrderCount()
{
    using (SqlConnection sqlConn
        = new SqlConnection("context connection=true"))
    {
        sqlConn.Open();
        SqlCommand sqlCmd = new SqlCommand(
            "SELECT COUNT(*) AS 'Order Count' FROM dbo.OrderHeader",
             sqlConn);
        return (int)sqlCmd.ExecuteScalar();
    }
}


SELECT Asset5.dbo.cf_OrderCount()


[SqlFunction(IsDeterministic = true)]


[SqlFunction(IsDeterministic = true, IsPrecise = true)]


[SqlFunction(SystemDataAccess = SystemDataAccessKind.Read)]


[SqlFunction(Name = "cf_OrderCount",
                     DataAccess = DataAccessKind.Read)]
public static int OrderCount()
...


[SqlFunction(FillRowMethodName = "FillRow",


public static void FillRow(object obj,
                           out string fileName,
                           out long size)
{
    Object[] row = (object[])obj;

    fileName = (string)row[0];
    size = (long)row[1];
}


public static IEnumerable ClrTvfFolderList(string folder)
{
    ArrayList fileArray = new ArrayList();

    // フォルダ内のファイルに対してループ処理を実行する
    foreach (string file in Directory.GetFiles(folder,
        "*.*", SearchOption.TopDirectoryOnly))
    {
        FileInfo fi = new FileInfo(file);

        object[] row = new object[2];
        row[0] = fi.FullName;
        row[1] = fi.Length;

        fileArray.Add(row);
    }

    return fileArray;
}


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.IO;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition = "fileName nvarchar(max), size bigint")]

    public static IEnumerable ClrTvfFolderList(string folder)
    {
        ArrayList fileArray = new ArrayList();

        // フォルダ内のファイルに対してループ処理を実行する
        foreach (string file in Directory.GetFiles(folder,
            "*.*", SearchOption.TopDirectoryOnly))
        {
            FileInfo fi = new FileInfo(file);

            object[] row = new object[2];
            row[0] = fi.FullName;
            row[1] = fi.Length;
            fileArray.Add(row);
        }

        return fileArray;
    }

    public static void FillRow(object obj, out string fileName, out long size)
    {
        Object[] row = (object[])obj;

        // objからデータを取得する
        fileName = (string)row[0];
        size = (long)row[1];
    }
};


CREATE ASSEMBLY ClrTvfFolderList
FROM 'C:\Projects\ClrTvfFolder\bin\Debug\ClrTvfFolderList.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION ClrTvfFolderList(@folder nvarchar(max))
RETURNS TABLE (fileName nvarchar(max), size bigint)
AS
EXTERNAL NAME ClrTvfFolderList.UserDefinedFunctions.ClrTvfFolderList
GO


using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;
using System.Collections;

namespace Samples.SqlServer
{
    public class stringsplits
    {
        [SqlFunction(
            FillRowMethodName = "FillRow",
            TableDefinition = "segment nvarchar(max)")]
        public static IEnumerable clrtvf_Split(SqlString str, string splitChar)
        {
            string[] m_strlist;
            if (!str.IsNull)
            {
                m_strlist = str.Value.Split(splitChar.ToCharArray());
                return m_strlist;
            }
            else
                return "";
        }

        public static void FillRow(Object obj, out string segment)
        {
            segment = (string)obj;
        }
    }
};


SELECT *
FROM clrtvf_Split('Memory,CPU,Resolution,Size,Weight,
Color,HDD-count,HDD-capacity', ',')


using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;
using System.Collections;

namespace Samples.SqlServer
{
    public class stringsplit
    {
        internal struct SplitParts
        {
            // 分割部分を格納するための構造体
            public int id;
            public string result;
            public SplitParts(int splitId, string split)
            {
                id = splitId;
                this.result = split;
            }
        }

        [Microsoft.SqlServer.Server.SqlFunctionAttribute(
            FillRowMethodName = "FillRow",
            TableDefinition = "segment nvarchar(max)," +
                              "i int")]
        public static IEnumerable clrtvf_Split1(SqlString str,
                                                string splitChar)
        {
            // splitCharの区切り文字を使ってstrを分割し、
            // それをsegment varchar(max), i (row_number) int
            // という形式のテーブル値関数として返す

            string[] m_strlist;
            SplitParts[] a;
            if (!str.IsNull)
            {
                m_strlist = str.Value.Split(splitChar.ToCharArray());
                int count = m_strlist.GetUpperBound(0);
                a = new SplitParts[count + 1];
                for (int i = 0; i < m_strlist.GetUpperBound(0) + 1; i++)
                {
                    a[i] = new SplitParts(i, m_strlist[i]); ;
                }
                return a;
            }
            else
                return "";
        }

        public static void FillRow(Object obj, out string segment, out int i)
        {
            SplitParts ab = (SplitParts)obj;
            segment = ab.result;
            i = ab.id;
        }
    }
}


SELECT *
FROM clrtvf_Split1('Memory,CPU,Resolution,Size,Weight,' +'Color,HDD-count,HDD-capacity', ',')


Public Shared Sub FillRow (ByVal obj As Object,
                           <Out()> ByRef Segment As SqlChars,
                           <Out()> ByRef i As SqlInt32)


using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

using System.Management;
using System.Collections;
using System.Collections.Generic;

internal struct LogicalDriveList
{
    public int DriveId;
    public string DriveLogicalName;
    public Int64 DriveFreeSpace;
    public string DriveType;

    public LogicalDriveList(int driveId,
                            string driveLogicalName,
                            string driveType,
                            int driveFreeSpace)
    {
        DriveId = driveId;
        this.DriveLogicalName = driveLogicalName;
        this.DriveType = driveType;
        this.DriveFreeSpace = driveFreeSpace;
    }
}

public class GetDrives
{
    [Microsoft.SqlServer.Server.SqlFunctionAttribute(
        FillRowMethodName = "FillRow",
        TableDefinition = "i int," +
                           DriveLogicalName nvarchar(255), " +
                           DriveType nvarchar(255), " +
                           DriveFreeSpace bigint")]
    public static IEnumerable clrtvf_GetLogicalDrives()
    {
        List<LogicalDriveList> drs = new List<LogicalDriveList>();
        ManagementObjectCollection queryCollection = getDrives();
        const int Removable = 2;
        const int LocalDisk = 3;
        const int Network = 4;
        const int CD = 5;
        int count = 0;
        string driveType = string.Empty;
        foreach (ManagementObject mo in queryCollection)
        {
            count++;
            switch (int.Parse(mo["DriveType"].ToString()))
            {
                case Removable: // リムーバブルドライブ
                    driveType = "Removable";
                    break;
                case LocalDisk: // ローカルドライブ
                    driveType = "LocalDisk";
                    break;
                case CD: // CD-ROMドライブ
                    driveType = "CD";
                    break;
                case Network: // ネットワークドライブ
                    driveType = "Network";
                    break;
                default: // 上記以外
                    driveType = "UnKnown";
                    break;
            }

            LogicalDriveList a = new LogicalDriveList();
            a.DriveId = count;
            a.DriveLogicalName = mo["Name"].ToString();
            a.DriveType = driveType;
            if (mo["FreeSpace"] != null)
                a.DriveFreeSpace = Int64.Parse(mo["FreeSpace"].ToString());
            else
                a.DriveFreeSpace = 0;
            drs.Add(a);
        }
        return drs;
    }

    protected static ManagementObjectCollection getDrives()
    {
        // ドライブのコレクションを取得する
        ManagementObjectSearcher query = new
            ManagementObjectSearcher("SELECT * FROM Win32_LogicalDisk ");
        ManagementObjectCollection queryCollection = query.Get();
        return queryCollection;
    }

    public static void FillRow(Object obj,
        out int i,
        out string DriveLogicalName,
        out string DriveType,
        out Int64 DriveFreeSpace)
    {
        LogicalDriveList ld = (LogicalDriveList)obj;
        DriveLogicalName = ld.DriveLogicalName;
        i = ld.DriveId;
        DriveType = ld.DriveType;
        DriveFreeSpace = ld.DriveFreeSpace;
    }
}


CREATE ASSEMBLY Management
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET=UNSAFE;
GO


SELECT * FROM dbo.clrtvf_GetLogicalDrives()

---------------------------------------------------------------------
-- 13.2 CLRトリガ
---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    // ターゲットの既存のテーブルまたはビューを入力して、
    // 属性行のコメントを解除します
    // [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1",
    //                                         Target="Table1",
    //                                         Event="FOR UPDATE")]
    public static void Trigger1()
    {
        // ユーザーのコードで置き換えてください
        SqlContext.Pipe.Send("Trigger FIRED");
    }
}


[SqlTrigger (Name="dbo.ctr_Contact_iu",
             Target="Contact",
             Event="AFTER UPDATE, INSERT")]


SqlTriggerContext triggerContext = SqlContext.TriggerContext;


string action = triggerContext.TriggerAction.ToString();
string sObj = triggerContext.ToString();
SqlContext.Pipe.Send("Trigger " + sObj
                   + " FIRED on " + action);


string s = "";
int iCount = triggerContext.ColumnCount;
for (int i = 0; i < iCount; i++)
{
    if (triggerContext.IsUpdatedColumn(i) == true)
        s = s + i.ToString() + ", ";
}
SqlContext.Pipe.Send("Trigger updated columns: " + s);


SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();


using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class Triggers
{
    [SqlTrigger(Name = "dbo.ctr_Contact_iu_Email",
                Target = "Contact",
                Event = "AFTER UPDATE, INSERT")]
    public static void ctr_Contact_iu_Email()
    {
        // トリガ関連の機能にアクセスするためにトリガのコンテキストを取得する
        SqlTriggerContext triggerContext = SqlContext.TriggerContext;

        // 電子メールの有効性をテストする
        using (SqlConnection con
            = new SqlConnection("context connection = true"))
        {
            con.Open();
            using (SqlCommand cmd = con.CreateCommand())
            {
                if (triggerContext.TriggerAction == TriggerAction.Insert)
                {
                    cmd.CommandText = "SELECT * FROM inserted";
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            string email = rdr.GetValue(5).ToString();

                            if (Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$") == false)
                            {
                                SqlContext.Pipe.Send("不適正なメールアドレス!");
                                //Transaction.Current.Rollback();
                            }
                        }
                    }
                }
            }
        }
    }
}


// 更新された列名の一覧
using (SqlConnection con = new SqlConnection("context connection = true"))
{
    con.Open();
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM inserted";

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            rdr.Read();

            if (triggerContext.TriggerAction == TriggerAction.Update)
            {
                string sCol = "更新された列: ";
                for (int icol = 0; icol < triggerContext.ColumnCount; icol++)
                    if (triggerContext.IsUpdatedColumn(icol) == true)
                        sCol = sCol + rdr.GetName(icol) + ", ";
                SqlContext.Pipe.Send(sCol);
            }
        }
    }
}


StreamWriter file = new StreamWriter("C:\\Audit.txt", true);
file.WriteLine("DELETE トリガが起動しました!");
file.Close();


using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

using System.IO;

public partial class Triggers
{
    [SqlTrigger(Name = "citr_OrderItem_D",
                Target = "OrderItem",
                Event = "INSTEAD OF DELETE")]
    public static void TriggerDelete()
    {
        string lines;

        // 新しいファイルを作成するか、既存のファイルの末尾に追加する
        using (StreamWriter file =
            new StreamWriter("C:\\Audit.txt", true))
        {
            using (SqlConnection con =
                new SqlConnection("context connection = true"))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT system_user AS [user], "
                        + " GETDATE() AS [time], 'OrderItem' AS [table]"
                        + " FROM deleted for xml raw";

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        // 複数のレコードが存在する場合がある
                        while (rdr.Read())
                        {
                            lines = rdr.GetValue(0).ToString();
                            //SqlContext.Pipe.Send(lines);
                            file.WriteLine(lines);
                        }
                    }
                }
            }
            file.Close();
        }
    }
}


Imports System

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Imports System.IO

Partial Public Class Triggers

    <SqlTrigger(Name:="ctrd_DdlProcedureEvents_vb", _
        Target:="DATABASE", _
        Event:="AFTER DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS")> _
    Public Shared Sub trigger_DDL_PROCEDURE_EVENTS()
        'データベースのすべてのプロシージャの変更に関する情報を取得する

        'トリガのコンテキストを取得する
        Dim triggerContext As SqlTriggerContext = SqlContext.TriggerContext

        'イベント情報を取得する
        Dim sXml As String = triggerContext.EventData.Value

        '情報を記録する
        Using file As New StreamWriter("C:\\sp_change.log", True)
            file.WriteLine(sXml)
            file.Close()
        End Using

    End Sub

End Class


<EVENT_INSTANCE>
    <EventType>CREATE_PROCEDURE</EventType>
    <PostTime>2005-12-17T16:07:00</PostTime>
    <SPID>54</SPID>
    <ServerName>LG\RC</ServerName>
    <LoginName>LG\dsunderic</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>Asset5</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>ap_test</ObjectName>
    <ObjectType>PROCEDURE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
        <CommandText>
            CREATE PROC dbo.ap_Invetory_List
            AS
            SELECT * FROM dbo.Inventory
        </CommandText>
    </TSQLCommand>
</EVENT_INSTANCE>


using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

using System.Xml;
using System.Diagnostics;
using System.IO;

public partial class Triggers
{

[SqlTrigger(
    Name = "ctrd_TableDdl",
    Target = "DATABASE",
    Event = "AFTER DDL_TABLE_EVENTS")]
public static void ddl_table()
{
    // トリガ関連の機能にアクセスするためにトリガのコンテキストを取得する
    SqlTriggerContext triggerContext = SqlContext.TriggerContext;
    // トリガを起動したイベントに関する情報が記載されたxmlを読み取る
    string sXml = triggerContext.EventData.Value;
    string login = "";
    string sql = "";

using (StringReader srXml = new StringReader(sXml))
{
    using (XmlReader rXml = new XmlTextReader(srXml))
    {
        // XMLの末尾までノードにループ処理を実行する
        while (!(rXml.EOF))
        {
            rXml.MoveToContent();
            if (rXml.IsStartElement()
                    && rXml.Name.Equals("LoginName"))
                login = rXml.ReadElementString("LoginName");
            else if (rXml.IsStartElement()
                    && rXml.Name.Equals("CommandText"))
                sql = rXml.ReadElementString("CommandText");

            // 次のノードに移動する
            rXml.Read();
        }
            // ソースが存在しない場合は作成する
            if (!EventLog.SourceExists("Asset5"))
                EventLog.CreateEventSource("Asset5", "Asset5 database");

            // イベントログにイベント情報を書き込む
            EventLog EventLog1 = new
                EventLog("Asset5 database", "LG", "Asset5");
            string log = "ログイン: "+login+" executed: ["+sql+"]";
            EventLog1.WriteEntry(log);
        }
    }
}
}


CREATE ASSEMBLY VbTriggers
FROM 'C:\Projects\VbTriggers\VbTriggers\bin\VbTriggers.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE TRIGGER ctrd_TableDdl
ON DATABASE
FOR CREATE_ASSEMBLY
AS EXTERNAL NAME VbTriggers.Triggers.ddl_table
GO

