---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第12章 CLRストアドプロシージャ開発の基礎
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 12.1 CLRストアドプロシージャの構造
---------------------------------------------------------------------

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

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ap_First()
    {
        SqlContext.Pipe.Send("Hello world!\n");
    }
};


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

Partial Public Class StoredProcedures
    <SqlProcedure()> _
    Public Shared Sub ap_First ()
        SqlContext.Pipe.Send("Hello world!\n")
    End Sub
End Class

---------------------------------------------------------------------
-- 12.3 ストアドプロシージャでの基本的な処理
---------------------------------------------------------------------

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

public partial class StoredProcedures
{
    [SqlProcedure]
    public static System.Int32 ap_MyFirst()
    {
        SqlContext.Pipe.Send("Hello world!\n");
        return 0;
    }
};


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

Partial Public Class StoredProcedures
    <SqlProcedure()> _
    Public Shared Function ap_MyFirst () As System.Int32
        SqlContext.Pipe.Send("Hello world!\n")
        ap_MyFirst = 0
    End Function
End Class


SqlConnection con = new SqlConnection()
con.ConnectionString = "Data Source=SQL2005;
  Initial Catalog=Asset5; Integrated Security=True; ";
con.Open();
// 接続を使用する処理


SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
conn.Open();


using(SqlConnection con = new SqlConnection("Context Connection=true"))
{
    con.Open();
    // 接続を使用する処理
}


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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cp_EqTypeList()
    {
        using (SqlConnection conn = new SqlConnection("Context Connection=true"))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT * FROM dbo.EqType";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            SqlContext.Pipe.Send(rdr);
            rdr.Close();
        }
    }
};


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

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub cp_EqTypeListVB()
            Using conn As New SqlConnection("Context Connection=true")
                Dim cmd As New SqlCommand()
                cmd.Connection = conn
                cmd.CommandText = "SELECT * FROM dbo.EqType"
                cmd.CommandType = CommandType.Text

                conn.Open()

                Dim rdr As SqlDataReader
                rdr = cmd.ExecuteReader()
                SqlContext.Pipe.Send(rdr)
                rdr.Close()
            End Using
        End Sub
End Class


cmd.CommandText = "ap_Eq_List";
cmd.CommandType = CommandType.StoredProcedure;


[Microsoft.SqlServer.Server.SqlProcedure]
public static void cp_EqType_List2()
{
    using (SqlCommand cmd = new SqlCommand())
    {
        //cmd.Connection = conn;
        cmd.CommandText = @"SELECT * FROM dbo.EqType";
        SqlContext.Pipe.ExecuteAndSend(cmd);
    }
}


SqlMetaData[] fields = new SqlMetaData[3];
fields [0] = new SqlMetaData("LeaseId", SqlDbType.Int);
fields [1] = new SqlMetaData("LeaseVendor", SqlDbType.NVarChar, 50);
fields [2] = new SqlMetaData("LeaseNumber", SqlDbType.NVarChar, 50);
fields [3] = new SqlMetaData("ContactDate", SqlDbType.DateTime);
fields [4] = new SqlMetaData("TotalAmount", SqlDbType.Money);


SqlDataRecord record = new SqlDataRecord(fields);
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);


SqlContext.Pipe.Send(record);


// レコードを設定
SqlDataRecord record = new SqlDataRecord(fields);

// レコードセットを開始
SqlContext.Pipe.SendResultsStart(record);

// 最初のレコードを組み立て
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);

// レコードを送信
SqlContext.Pipe.SendResultsRow(record);

// 2番目のレコードを組み立て
record.SetInt32(0, 1002);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7891");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 4000);

// レコードを送信
SqlContext.Pipe.SendResultsRow(record);

// レコードセットを送信
SqlContext.Pipe.SendResultsEnd();


Public Shared Sub ExtPriceSum(<Out()> ByRef value As SqlMoney)


public static void ExtPriceSum(out SqlMoney value)

---------------------------------------------------------------------
-- 12.4 主なADO .NET オブジェクト
---------------------------------------------------------------------

[SqlProcedure]
public int ap_Eq_Insert(string Make, string Model, string EqType)
{
    using (SqlConnection conn = new SqlConnection("Context Connection=true"))
    {
        // ALTER procedure [dbo].[ap_Equipment_Insert]
        // @chvMake varchar(50),
        // @chvModel varchar(50),
        // @chvEqType varchar(50),
        // @intEqId int OUTPUT

        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.ap_Equipment_Insert", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@chvMake", Make);
        cmd.Parameters["@chvMake"].Direction = ParameterDirection.Input;

        cmd.Parameters.AddWithValue("@chvModel", Model);
        cmd.Parameters["@chvModel"].Direction = ParameterDirection.Input;

        cmd.Parameters.AddWithValue("@chvEqType", EqType);
        cmd.Parameters["@chvEqType"].Direction = ParameterDirection.Input;

        SqlParameter paramEqId = cmd.Parameters.Add("@intEqId", SqlDbType.Int);
        paramEqId.Direction = ParameterDirection.Output;

        SqlParameter paramRC = cmd.Parameters.Add("@return", SqlDbType.Int);
        paramRC.Direction = ParameterDirection.ReturnValue;

        // コマンドを実行
        cmd.ExecuteNonQuery();

        // idを返す
        int returnValue = (int)paramRC.Value;
        int EqId = (int)paramEqId.Value;
        return EqId;
    }
}


[SqlProcedure]
public static void cp_EqType_GetCommaDelimList(out string EqType)
// EqTypesのカンマ区切りリストを返す
// 結果を返すストアドプロシージャからデータを取得する例
// リーダーオブジェクトを使用
{
    using (SqlConnection conn = new SqlConnection("Context Connection=true"))
    {
        EqType = "";
        // ストアドプロシージャの実行に使用するコマンドオブジェクトを準備
        SqlCommand cmd = new SqlCommand("dbo.ap_EqType_List", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //ストアドプロシージャを実行
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read()) // 可能な間は1行ずつ進める
            {
                // 返されたデータストリームに基づいて出力パラメータを返す
                //id = reader.GetInt32(0); // 先頭列は不要
                EqType = EqType + reader.GetString(1) + ", ";
            }
        }
    }
}

---------------------------------------------------------------------
-- 12.5 LOB型のパラメータの扱い方
---------------------------------------------------------------------

[SqlProcedure]
public static void cp_EqImage_Save(int EqID, string FileName)
{
    // ストアドプロシージャが返したVARBINARY(max)をファイルに保存する
    const int bufferSize = 8000;

    // GetBytesで格納するBLOBのbyte[]バッファ
    byte[] outbyte = new byte[bufferSize];
    using (SqlConnection conn =
        new SqlConnection("Context Connection=true"))
    {
        conn.Open();
        SqlCommand command = conn.CreateCommand();

        // ストアドプロシージャの実行に使用するコマンドを準備
        command.CommandText = "dbo.ap_EqImage_List";
        command.CommandType = CommandType.StoredProcedure;

        // 入力パラメータを準備
        SqlParameter paramID =
            new SqlParameter("@EqID", SqlDbType.Int);
        paramID.Value = EqID;
        command.Parameters.Add(paramID);

        // ストアドプロシージャを実行
        command.ExecuteNonQuery();

        using (SqlDataReader reader =
            command.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            while (reader.Read())
            {
                // SqlContext.Pipe.Send("reader read");

                // ファイル（ストリーム）を定義
                using (FileStream fileStream =
                new FileStream(FileName,
                               FileMode.OpenOrCreate,
                               FileAccess.Write))
                {
                    // SqlContext.Pipe.Send("file stream created.");

                    // BLOBをファイル（FileStream）に
                    // 書き込むライターを準備
                    using (BinaryWriter binaryWriter =
                        new BinaryWriter(fileStream))
                    {
                        // SqlContext.Pipe.Send("BinaryWriter created");
                        // BLOB出力の開始位置
                        long startIndex = 0;

                        // outbyte[]配列にバイトを読み取り、
                        // 読み取ったバイト数を取得
                        long retval = reader.GetBytes(0,
                            startIndex, outbyte, 0, bufferSize);

                        // SqlContext.Pipe.Send("outbyte filled.");

                        // 読み取ったバイト数とバッファの
                        // サイズが等しい間は、読み取りを
                        // 続ける
                        while (retval == bufferSize)
                        {
                            // ストリームに書き込み
                            binaryWriter.Write(outbyte);
                            //SqlContext.Pipe.Send("outbyte written.");

                            // ファイルに書き込み
                            binaryWriter.Flush();

                            // SqlContext.Pipe.Send("writer flushed.");

                            // 開始インデックスを移動
                            startIndex += bufferSize;
                            // レコードを再度読み取って
                            // バッファに格納
                            retval = reader.GetBytes(
                                0, startIndex, outbyte, 0, bufferSize);
                        }

                        // SqlContext.Pipe.Send("outbyte last time.");
                        // 残りをファイルに書き込み
                        binaryWriter.Write(outbyte);
                        // ファイルに書き込み
                        binaryWriter.Flush();
                        // SqlContext.Pipe.Send("flushed last time.");
                    }
                }
            }
        }
        return;
    }
}


USE Asset5
GO
DECLARE @EqID INT
DECLARE @FileName NVARCHAR(4000)

SELECT @EqId = MIN(EqId) FROM Eq WHERE EqImage IS NOT NULL
SET @FileName = 'C:\download\Eq' + CAST(@EqId AS VARCHAR(30)) + '.jpg'

EXECUTE [Asset5].[dbo].[cp_EqImage_Save]
    @EqID,
    @FileName


[SqlProcedure]
public static void cp_EqImage_Update(int EqID, string FileName)
{
    // ファイルから画像を読み取ってVARBINARY(max)に格納

    // ファイル（ストリーム）を定義
    using (FileStream fileStream =
    new FileStream(FileName,
                   FileMode.Open,
                   FileAccess.Read))
    {
        // SqlContext.Pipe.Send("file stream created.");

        // BLOBをファイル（FileStream）から
        // 読み込むリーダーを準備
        using (BinaryReader binaryReader =
            new BinaryReader(fileStream))
        {
            //SqlContext.Pipe.Send("BinaryWriter created");
            byte[] blob=binaryReader.ReadBytes((int)fileStream.Length);

            using (SqlConnection conn =
                new SqlConnection("Context Connection=true"))
            {
                conn.Open();
                SqlCommand command = conn.CreateCommand();

                // ストアドプロシージャの実行に使用するコマンドを準備
                command.CommandText = "ap_EqImage_Update";
                command.CommandType = CommandType.StoredProcedure;

                // 入力パラメータを準備
                SqlParameter paramID =
                    new SqlParameter("@EqID", SqlDbType.Int);
                paramID.Value = EqID;
                command.Parameters.Add(paramID);

                // 画像を取得する出力パラメータを準備
                SqlParameter paramEqImage =
                    new SqlParameter("@EqImage",SqlDbType.VarBinary,-1);
                paramEqImage.Value = blob;
                command.Parameters.Add(paramEqImage);

                // ストアドプロシージャを実行
                command.ExecuteNonQuery();
            }
        }
    }
    return;
}

