---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 13 CLR֐ƃgK
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 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)
{
    // Lȓdq[AhXǂ
    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();

    // tH_̃t@Cɑ΂ă[vs
    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();

        // tH_̃t@Cɑ΂ă[vs
        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;

        // objf[^擾
        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
        {
            // i[邽߂̍\
            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̋؂蕶gstr𕪊A
            // segment varchar(max), i (row_number) int
            // Ƃ`̃e[ul֐ƂĕԂ

            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: // [ouhCu
                    driveType = "Removable";
                    break;
                case LocalDisk: // [JhCu
                    driveType = "LocalDisk";
                    break;
                case CD: // CD-ROMhCu
                    driveType = "CD";
                    break;
                case Network: // lbg[NhCu
                    driveType = "Network";
                    break;
                default: // LȊO
                    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()
    {
        // hCũRNV擾
        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 CLRgK
---------------------------------------------------------------------

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

public partial class Triggers
{
    // ^[Qbg̊̃e[u܂̓r[͂āA
    // s̃Rg܂
    // [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1",
    //                                         Target="Table1",
    //                                         Event="FOR UPDATE")]
    public static void Trigger1()
    {
        // [U[̃R[hŒuĂ
        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()
    {
        // gK֘A̋@\ɃANZX邽߂ɃgK̃ReLXg擾
        SqlTriggerContext triggerContext = SqlContext.TriggerContext;

        // dq[̗LeXg
        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("sKȃ[AhX!");
                                //Transaction.Current.Rollback();
                            }
                        }
                    }
                }
            }
        }
    }
}


// XVꂽ񖼂̈ꗗ
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 = "XVꂽ: ";
                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 gKN܂!");
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;

        // Vt@C쐬邩Ãt@C̖ɒǉ
        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())
                    {
                        // ̃R[h݂ꍇ
                        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()
        'f[^x[Xׂ̂ẴvV[W̕ύXɊւ擾

        'gK̃ReLXg擾
        Dim triggerContext As SqlTriggerContext = SqlContext.TriggerContext

        'Cxg擾
        Dim sXml As String = triggerContext.EventData.Value

        'L^
        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()
{
    // gK֘A̋@\ɃANZX邽߂ɃgK̃ReLXg擾
    SqlTriggerContext triggerContext = SqlContext.TriggerContext;
    // gKNCxgɊւ񂪋Lڂꂽxmlǂݎ
    string sXml = triggerContext.EventData.Value;
    string login = "";
    string sql = "";

using (StringReader srXml = new StringReader(sXml))
{
    using (XmlReader rXml = new XmlTextReader(srXml))
    {
        // XML̖܂Ńm[hɃ[vs
        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");

            // ̃m[hɈړ
            rXml.Read();
        }
            // \[X݂Ȃꍇ͍쐬
            if (!EventLog.SourceExists("Asset5"))
                EventLog.CreateEventSource("Asset5", "Asset5 database");

            // CxgOɃCxg
            EventLog EventLog1 = new
                EventLog("Asset5 database", "LG", "Asset5");
            string log = "OC: "+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

