---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第14章 CLRデータベースオブジェクトの高度なトピック
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 14.1 CLRユーザー定義型
---------------------------------------------------------------------

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

using System.IO;
using System.Runtime.InteropServices;

namespace MyUDTs
{
    [Serializable]
    [StructLayout(LayoutKind.Sequential)]
    [SqlUserDefinedType(Format.UserDefined,
                        IsByteOrdered = true,
                        MaxByteSize = 4096,
                        IsFixedLength = false)]
    public struct cudt_PriceDate : INullable, IBinarySerialize
    {
        #region Properties
        private Double stockPrice;
        private DateTime businessDay;

        public Double StockPrice
        {
            get
            {
                return this.stockPrice; ;
            }
            set
            {
                stockPrice = value;
            }
        }

        public DateTime BusinessDay
        {
            get
            {
                return this.businessDay;
            }
            set
            {
                businessDay = value;
            }
        }
        #endregion

        #region NULL
        private bool isNull;

        public bool IsNull
        {
            get
            {
                return (isNull);
            }
        }

        public static cudt_PriceDate Null
        {
            get
            {
                cudt_PriceDate h = new cudt_PriceDate();
                h.isNull = true;
                return h;
            }
        }
        #endregion

        #region Default
        public static cudt_PriceDate DefaultValue()
        {
            return cudt_PriceDate.Null;
        }
        #endregion

        #region Cast
        public override string ToString()
        {
            if (this.IsNull)
                return "NULL";
            else
            {
                return stockPrice + ";" + businessDay.Date;
            }
        }

        public static cudt_PriceDate Parse(SqlString s)
        {
            if (s.IsNull || s.Value.ToLower() == "null")
                return Null;
            cudt_PriceDate st = new cudt_PriceDate();
            string[] xy = s.Value.Split(";".ToCharArray());
            st.stockPrice = Double.Parse(xy[0]);
            st.businessDay = DateTime.Parse(xy[1]);
            return st;
        }
        #endregion

        #region Serialization
        public void Read(BinaryReader r)
        {
            stockPrice = r.ReadDouble();
            businessDay = DateTime.Parse(r.ReadString());
        }

        public void Write(BinaryWriter w)
        {
            w.Write(stockPrice);
            w.Write(businessDay.ToString());
        }
        #endregion

        #region Custom Methods
        // 以下の計算にはあまり意味はなく、
        // UDTのメソッドの例にすぎない

        [SqlMethod(
            OnNullCall = false,
            DataAccess = DataAccessKind.None,
            IsDeterministic = false,
            IsMutator = false,
            IsPrecise = false)]
        public static SqlInt32 DiffDate(cudt_PriceDate from,
                                        cudt_PriceDate to)
        {
            TimeSpan delta = to.BusinessDay.Subtract(from.BusinessDay);
            return delta.Days;
        }

        [SqlMethod(OnNullCall = false)]
        public static SqlDouble DiffPrice(cudt_PriceDate from,
                                          cudt_PriceDate to)
        {
            return to.StockPrice - from.StockPrice;
        }
        #endregion
    }
}


CREATE ASSEMBLY MyUDTs
FROM 'C:\Projects\CShrpPriceDate\bin\Debug\cudt_PriceDate.dll'
WITH PERMISSION_SET =SAFE;

CREATE TYPE cudt_PriceDate
EXTERNAL NAME MyUDTs.[MyUDTs.cudt_PriceDate];
GO


Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Imports System.IO

<Serializable()> _
<StructLayout(LayoutKind.Sequential)> _
<SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, _
                    MaxByteSize:=4096, IsFixedLength:=False, _
                    ValidationMethodName:="ValidateExtPrice")> _
Public Structure ExtPriceUDT
    Implements INullable, IBinarySerialize

#Region "Properties"
    ' フィールドメンバ
    Private m_Qty As Decimal
    Private m_Price As Decimal

    Public Property Price() As Decimal
        Get
            Return (Me.m_Price)
        End Get

        Set(ByVal Value As Decimal)
            m_Price = Value
        End Set
    End Property

    Public Property Qty() As Decimal
        Get
            Return (Me.m_Qty)
        End Get

        Set(ByVal Value As Decimal)
            m_Qty = Value
        End Set
    End Property
#End Region

#Region "Cast"
    <SqlMethod(OnNullCall:=False)> _
    Public Shared Function Parse(ByVal s As SqlString) As ExtPriceUDT
        If s.IsNull Then
            Return Null
        End If

        Dim u As ExtPriceUDT = New ExtPriceUDT
        Dim x() As String = s.Value.Split("x".ToCharArray())
        u.m_Qty = Decimal.Parse(x(0))
        u.m_Price = Decimal.Parse(x(1))

        If Not u.ValidateExtPrice() Then
            Throw New ArgumentException("Invalid extended price values.")
        End If

        Return u
    End Function

    <SqlMethod(OnNullCall:=False)> _
    Public Overrides Function ToString() As String
        Return m_Qty.ToString + " x $" + m_Price.ToString
    End Function
#End Region

#Region "Null"
    ' Privateメンバ
    Private m_Null As Boolean

    Public ReadOnly Property IsNull() As Boolean _
           Implements INullable.IsNull
        Get
            Return m_Null
        End Get
    End Property

    Public Shared ReadOnly Property Null() As ExtPriceUDT
        Get
            Dim h As ExtPriceUDT = New ExtPriceUDT
            h.m_Null = True
            Return h
        End Get
    End Property
#End Region

#Region "Method"
    <SqlMethod(OnNullCall:=False)> _
    Private Function ValidateExtPrice() As Boolean
        If (m_Qty >= 0) And (m_Price >= 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ' 合計額を返す
    <SqlMethod(OnNullCall:=False)> _
    Public Function ExtPrice() As Decimal
        Return m_Qty * m_Price
    End Function

    <SqlMethod(OnNullCall:=False)> _
    Public Function Diff(ByVal a As ExtPriceUDT) As ExtPriceUDT
        ' 次の計算には数学的な意味はなく、
        ' UDTのメソッドの例にすぎない

        Dim d As New ExtPriceUDT

        If a.ExtPrice >= Me.ExtPrice Then
           d.m_Price = a.m_Price - Me.m_Price
           d.m_Qty = a.m_Qty - Me.m_Qty
        Else
           d.m_Price = Me.m_Price - a.m_Price
           d.m_Qty = Me.m_Qty - a.m_Qty
        End If

        Return d

    End Function

    Public Shared Function DefaultValue() As ExtPriceUDT
        Return ExtPriceUDT.Parse("0 x 0")
    End Function
#End Region

#Region "Serialization"
    <SqlMethod(OnNullCall:=False)> _
    Public Sub Read(ByVal r As BinaryReader) _
            Implements IBinarySerialize.Read
        m_Qty = r.ReadDecimal()
        m_Price = r.ReadDecimal()
    End Sub

    <SqlMethod(OnNullCall:=False)> _
    Public Sub Write(ByVal w As BinaryWriter) _
            Implements IBinarySerialize.Write
        w.Write(m_Qty)
        w.Write(m_Price)
    End Sub
#End Region

End Structure


CREATE TABLE OrderItem_wUDT
(
    OrderItemId INT NOT NULL,
    OrderId INT NOT NULL,
    PartId INT NOT NULL,
    ExtPrice ExtPriceUDT NULL
)
GO


DECLARE @ext_price ExtPriceUDT


DECLARE @a cudt_PriceDate
SET @a = CONVERT(cudt_PriceDate, '911.799;2005-11-02')

DECLARE @b cudt_PriceDate
SET @b = CAST('912.35;2005-11-03' AS cudt_PriceDate)

SELECT cudt_PriceDate::DiffDate(@a, @b),
       cudt_PriceDate::DiffPrice(@a, @b)


CREATE TABLE OrderItem_wUDT
(
    OrderItemId int NOT NULL,
    OrderId int NOT NULL,
    PartId int NOT NULL,
    ExtPrice ExtPriceUDT NOT NULL DEFAULT dbo.ExtPriceUDT::DefaultValue()
)
GO


INSERT INTO [dbo].[OrderItem_wUDT]
    ([OrderItemId],[OrderId],[PartId],[ExtPrice])
VALUES(1, 1, 1, ExtPriceUDT::Parse('35 x 450'))


SELECT
    ExtPrice.Qty Qty,
    ExtPrice.Price Price,
    ExtPrice
FROM OrderItem_wUDT


SELECT
    ExtPrice.ExtPrice() [ExtPrice()],
    ExtPrice.ToString() [ToString()],
    ExtPrice.Diff(ExtPriceUDT::Parse('25 x 250')).ToString() [Diff()]
FROM OrderItem_wUDT


DECLARE @ext_price ExtPriceUDT
SELECT @ext_price = CONVERT(ExtPriceUDT, '25 x 250')
SELECT @ext_price [CONVERT()]


SELECT CONVERT(ExtPriceUDT, '25 x 250').ExtPrice() ExtPrice


SELECT CAST(ExtPrice AS VARCHAR(30)) ExtPrice
FROM OrderItem_wUDT


SELECT *
FROM OrderItem_wUDT
WHERE ExtPrice > CONVERT(ExtPriceUDT, '25 x 250')


UPDATE OrderItem_wUDT
SET ExtPrice = CONVERT(ExtPriceUDT, '25 x 250')
WHERE OrderItemId = 1


UPDATE OrderItem_wUDT
SET ExtPrice.Qty = 25
WHERE OrderItemId = 1


UPDATE OrderItem_wUDT
SET ExtPrice.Qty = 25,
    ExtPrice.Price = 250
WHERE OrderItemId = 1


USE Asset5
GO
CREATE TABLE #tmp(pd cudt_PriceDate)

---------------------------------------------------------------------
-- 14.3 CLRユーザー定義集計関数
---------------------------------------------------------------------

Public Sub Init()
    arr = New Double(100) {}
    count = 0
End Sub


Public Sub Accumulate(ByVal value As SqlDouble)
    arr(count) = value.Value
    count = count + 1
End Sub



Public Sub Merge(ByVal group As MedianFloat)
    For Each mem As Double In group.arr
        arr(count) = mem
        count = count + 1
    Next mem
End Sub


Public Function Terminate() As SqlDouble

    Array.Resize(arr, count)
    Array.Sort(arr)
    Dim n As Integer = count \ 2
    If n * 2 = count Then
        median = (arr(n + 1) + arr(n)) / 2.0
    Else
        median = arr(n + 1)
    End If

    Return median

End Function


<Serializable()> _
...
   Public Class MedianFloat
    Implements IBinarySerialize

...
    Public Sub Read(ByVal r As BinaryReader) _
               Implements IBinarySerialize.Read
        count = r.ReadInt32()
        arr = New Double(count) {}
        For i As Integer = 1 To count - 1
            arr(i) = r.ReadDouble()
        Next i
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) _
               Implements IBinarySerialize.Write

        w.Write(count)
        For Each m As Double In arr
            w.Write(m)
        Next m

    End Sub


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

Imports System.Collections
Imports System.IO
Imports System.Text

<Serializable()> _
<SqlUserDefinedAggregate( _
    Format.UserDefined, _
    IsInvariantToDuplicates:=True, _
    IsInvariantToNulls:=True, _
    IsInvariantToOrder:=True, _
    IsNullIfEmpty:=True, _
    MaxByteSize:=8000)> _
Public Class MedianFloat
    Implements IBinarySerialize

    Public Sub Init()
        arr = New Double(100) {}
        count = 0
    End Sub

    Public Sub Accumulate(ByVal value As SqlDouble)
        arr(count) = value.Value
        count = count + 1
    End Sub

    Public Sub Merge(ByVal group As MedianFloat)

        For Each mem As Double In group.arr
            arr(count) = mem
            count = count + 1
        Next mem

    End Sub

    Public Function Terminate() As SqlDouble

        Array.Resize(arr, count)
        Array.Sort(arr)

        Dim n As Integer = count \ 2
        If n * 2 = count Then
            median = (arr(n + 1) + arr(n)) / 2.0
        Else
            median = arr(n + 1)
        End If

        Return median
    End Function

    Public Sub Read(ByVal r As BinaryReader) _
               Implements IBinarySerialize.Read
        count = r.ReadInt32()
        arr = New Double(count) {}

        For i As Integer = 1 To count - 1
            arr(i) = r.ReadDouble()
        Next i
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) _
               Implements IBinarySerialize.Write

        w.Write(count)
        For Each m As Double In arr
            w.Write(m)
        Next m

    End Sub

    ' フィールドメンバ
    Private median As Double
    Public arr() As Double
    Public count As Integer

End Class


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

using System.Runtime.InteropServices;
using System.Collections;
using System.IO;
using System.Text;

namespace MyAggs
{
    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = false,
        MaxByteSize = 8000)
    ]
    public class MedianDouble : IBinarySerialize
    {
        private double[] arr;
        double median;
        private int count = 0;

        public void Init()
        {
            arr = new double[100];
        }

        public void Accumulate(SqlDouble value)
        {
            arr[count] = value.Value;
            count++;
        }

        public void Merge(MedianDouble group)
        {
            foreach (double mem in group.arr)
            {
                arr[count] = mem;
                count++;
            }
        }

        public SqlDouble Terminate()
        {
            Array.Resize(ref arr, count);
            Array.Sort(arr);

            int n = count / 2;

            if (n * 2 == count)
                median = (arr[n + 1] + arr[n]) / 2;
            else
                median = arr[n + 1];

            return median;
        }

        public void Read(BinaryReader r)
        {
            count = r.ReadInt32();

            arr = new double[count];

            for (int i = 1; i < count; i++)
                arr[i] = r.ReadDouble();
        }

        public void Write(BinaryWriter w)
        {
            w.Write(count);

            foreach (double m in arr)
                w.Write(m);
        }
    }
}


IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'VbAgg')
DROP ASSEMBLY VbAgg;
GO

CREATE ASSEMBLY VbAgg
FROM 'C:\Projects\VbAgg\VbAgg\bin\debug\VbAgg.dll'
WITH PERMISSION_SET=SAFE;
GO

CREATE AGGREGATE MedianFloat(@input FLOAT)
RETURNS FLOAT
EXTERNAL NAME [VbAgg].[MyAggs.MedianFloat];
GO


IF OBJECT_ID('Data') IS NOT NULL
    DROP TABLE dbo.Data

CREATE TABLE dbo.Data(id INT IDENTITY, num REAL)
INSERT INTO dbo.Data(num) VALUES(-911.5);
INSERT INTO dbo.Data(num) VALUES(9.6);
INSERT INTO dbo.Data(num) VALUES(91.88);
INSERT INTO dbo.Data(num) VALUES(509.86);
INSERT INTO dbo.Data(num) VALUES(-911.5);
INSERT INTO dbo.Data(num) VALUES(90.6);
INSERT INTO dbo.Data(num) VALUES(-1.88);
INSERT INTO dbo.Data(num) VALUES(19.86);
INSERT INTO dbo.Data(num) VALUES(18888.86);

SELECT * FROM Data ORDER BY num

SELECT dbo.MedianFloat(num) MedianFloat
FROM Data


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

using System.IO;
using System.Text;
using MyUDTs;

namespace MyAggs
{
    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = false,
        IsInvariantToOrder = false,
        MaxByteSize = 8000)
        ]
    public class agg_MovingAvg : IBinarySerialize
    {
        private double sum = 0;
        private DateTime startDt;
        private DateTime endDt;
        private double ma;
        private Int32 count = 1;
        public static readonly int daysNum = 50;

        #region Aggregation Methods
        /// <summary>
        /// 内部データ構造の初期化
        /// </summary>
        public void Init()
        {
            startDt = DateTime.Parse("2005-12-10 0:00:00");
            endDt = startDt.AddDays(-1 * daysNum);
        }

        /// <summary>
        /// Null値でなければ累算集計する
        /// </summary>
        /// <param name="value">集計される値</param>
        public void Accumulate(cudt_PriceDate value)
        {
            if (value.IsNull)
            {
                return;
            }
            if (value.BusinessDay > endDt && value.BusinessDay < this.startDt)
            {
                sum += (double)value.StockPrice;
                count++;
            }
        }

        /// <summary>
        /// 集計関数により部分的に計算された集計値の結合
        /// </summary>
        /// <param name="other">加算される別のデータのセット</param>
        public void Merge(agg_MovingAvg other)
        {
            sum += other.sum;
            count += other.count;
        }

        /// <summary>
        /// 集計結果を返すための集計の終了処理の呼び出し
        /// </summary>
        /// <returns>集計された値</returns>
        public SqlDouble Terminate()
        {
            ma = sum / count;
            return new SqlDouble(ma);
        }

        #endregion

        #region IBinarySerialize

        public void Read(BinaryReader r)
        {
            sum = r.ReadDouble();
            count = r.ReadInt32();
        }

        public void Write(BinaryWriter w)
        {
            w.Write(sum);
            w.Write(count);
        }
        #endregion
    }
}


IF OBJECT_ID('dbo.Stock') IS NOT NULL
    DROP TABLE dbo.Stock

CREATE TABLE dbo.Stock(
    id INT IDENTITY,
    stock VARCHAR(100),
    priceClose cudt_PriceDate)

INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('3.05;2005-12-02' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('3.08;2005-12-1' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('2.90;2005-11-30' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('2.84;2005-11-29' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('2.92;2005-11-28' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('3.01;2005-11-25' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('3.06;2005-11-23' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('3.04;2005-11-22' AS cudt_PriceDate))
INSERT INTO dbo.Stock(stock, priceClose)
VALUES('NT', CAST('5.10;2005-11-21' AS cudt_PriceDate))

SELECT
    Stock,
    dbo.agg_MovingAvg(priceClose) MovingAvg
FROM dbo.Stock
GROUP BY stock

---------------------------------------------------------------------
-- 14.4 CLRコードとトランザクション
---------------------------------------------------------------------

try
{
    Cmd1.ExecuteNonQuery();
}
catch
{
    // カスタムのエラー処理
    ...
    System.Transactions.Transaction.Current.Rollback()
}


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

Imports System.Transactions

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub cp_LeasedAsset_Insert( _
        ByVal EqId As Integer, ByVal LocId As Integer, _
        ByVal StatusId As Integer, ByVal LeaseId As Integer, _
        ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _
        ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)

        Dim Cmd1 As String = "insert Inventory(EqId, LocationId, " + _
             "StatusId,            LeaseId, " + _
             "LeaseScheduleId,     OwnerId, " + _
             "Lease,               AcquisitionTypeID)" + _
             "values (" + EqId.ToString() + ", " + LocId.ToString() + ", " + _
             StatusId.ToString() + " , " + LeaseId.ToString() + " , " + _
             LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _
             LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"

        Dim Cmd2 As String = "update dbo.LeaseSchedule " + _
            "Set PeriodicTotalAmount = PeriodicTotalAmount + " _
            + LeaseAmount.ToString() + _
            "where LeaseId = " + LeaseId.ToString()

        Using TxScope As TransactionScope = New TransactionScope()

            Using Con As New SqlConnection("Context Connection=true")
                Con.Open()

                Dim Cmd As SqlCommand = New SqlCommand(Cmd1, Con)
                Cmd.ExecuteNonQuery()

                ' 同じ接続とコマンドオブジェクトを再利用して
                ' 次の部分の処理を行う
                Cmd.CommandText = Cmd2
                Cmd.ExecuteNonQuery()

                ' トランザクションをコミットおよび完了する
                TxScope.Complete()

            End Using ' 接続は暗黙的に閉じられる
        End Using ' トランザクションは暗黙的にクローズまたはロールバックされる
    End Sub
End Class


Dim options As TransactionOptions = New TransactionOptions()
options.IsolationLevel = Transactions.IsolationLevel.ReadCommitted
options.Timeout = TransactionManager.DefaultTimeout

Using TxScope As TransactionScope = _
    New TransactionScope(TransactionScopeOption.Required, options)
...
End Using


<Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub cp_LeasedAsset_InsertDistributed( _
        ByVal EqId As Integer, ByVal LocId As Integer, _
        ByVal StatusId As Integer, ByVal LeaseId As Integer, _
        ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _
        ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)

    Dim sCmd1 As String = "INSERT INTO Inventory(EqId, LocationId, " + _
         "StatusId,            LeaseId, " + _
         "LeaseScheduleId,     OwnerId, " + _
         "Lease,               AcquisitionTypeID)" + _
         "VALUES (" + EqId.ToString() + ", " + LocId.ToString() + ", " + _
         StatusId.ToString() + " , " + LeaseId.ToString() + " , " + _
         LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _
         LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"

    Dim sCmd2 As String = "UPDATE dbo.LeaseSum " + _
        "SET PeriodicTotalAmount = PeriodicTotalAmount + " _
        + LeaseAmount.ToString() + _
        "WHERE LeaseId = " + LeaseId.ToString()

    Dim ConString1 As String = "Context Connection=true"

    Dim ConString2 As String = _
        "Data Source=lg/DW;Initial Catalog=Asset5DW;" + _
        "Integrated Security=True"

    Using TxScope As TransactionScope = New TransactionScope()

        Using Con1 As New SqlConnection(ConString1)

            Con1.Open()

    Dim Cmd2 As SqlCommand = New SqlCommand(sCmd1, Con1)
        Cmd2.ExecuteNonQuery()

        End Using

        Using Con2 As New SqlConnection(ConString2)

            ' 2番目の接続を作成すると、分散トランザクションに
            ' 自動的に昇格される
            Con2.Open()

    Dim Cmd2 As SqlCommand = New SqlCommand(sCmd2, Con2)
        Cmd2.ExecuteNonQuery()

        End Using ' 接続は暗黙的に閉じられる

        ' トランザクションをコミットおよび完了する
        TxScope.Complete()

    End Using ' トランザクションは暗黙的にクローズまたはロールバックされる

End Sub


<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub cp_LeasedAsset_InsertDeclarative( _
    ByVal EqId As Integer, ByVal LocId As Integer, _
    ByVal StatusId As Integer, ByVal LeaseId As Integer, _
    ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _
    ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)

   Dim sCmd1 As String = "INSERT INTO Inventory(EqId, LocationId, " + _
        "StatusId,            LeaseId, " + _
        "LeaseScheduleId,     OwnerId, " + _
        "Lease,               AcquisitionTypeID)" + _
        "VALUES (" + EqId.ToString() + ", " + LocId.ToString() + ", " + _
        StatusId.ToString() + " , " + LeaseId.ToString() + " , " + _
        LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _
        LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"

   Dim sCmd2 As String = "UPDATE dbo.LeaseSum " + _
       "SET PeriodicTotalAmount = PeriodicTotalAmount + " _
       + LeaseAmount.ToString() + _
       "WHERE LeaseId = " + LeaseId.ToString()

   ' tryとcatchはスコープが異なるため、ここで宣言する
   Dim trans As SqlTransaction = Nothing
   Dim connection As SqlConnection = _
   New SqlConnection("Context Connection=true")

   connection.Open()
   Try
      trans = connection.BeginTransaction

      Dim command As SqlCommand = _
          New SqlCommand(sCmd1, connection, trans)

      command.ExecuteNonQuery()
      command.CommandText = sCmd2
      command.ExecuteNonQuery()

      trans.Commit()

    Catch ex As Exception
        trans.Rollback()

        ' その他のエラー処理がここに必要

        ' 例外を再スローする
        Throw ex
     Finally
        ' 毎回呼び出される
        connection.Close()
     End Try

End Sub

