---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 12 CLRXgAhvV[WJ̊b
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 12.1 CLRXgAhvV[W̍\
---------------------------------------------------------------------

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 XgAhvV[Wł̊{Iȏ
---------------------------------------------------------------------

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();
// ڑgp鏈


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


using(SqlConnection con = new SqlConnection("Context Connection=true"))
{
    con.Open();
    // ڑgp鏈
}


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);


// R[hݒ
SqlDataRecord record = new SqlDataRecord(fields);

// R[hZbgJn
SqlContext.Pipe.SendResultsStart(record);

// ŏ̃R[hgݗ
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);

// R[h𑗐M
SqlContext.Pipe.SendResultsRow(record);

// 2Ԗڂ̃R[hgݗ
record.SetInt32(0, 1002);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7891");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 4000);

// R[h𑗐M
SqlContext.Pipe.SendResultsRow(record);

// R[hZbg𑗐M
SqlContext.Pipe.SendResultsEnd();


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


public static void ExtPriceSum(out SqlMoney value)

---------------------------------------------------------------------
-- 12.4 ADO .NET IuWFNg
---------------------------------------------------------------------

[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;

        // R}hs
        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̃J}؂胊XgԂ
// ʂԂXgAhvV[Wf[^擾
// [_[IuWFNggp
{
    using (SqlConnection conn = new SqlConnection("Context Connection=true"))
    {
        EqType = "";
        // XgAhvV[W̎sɎgpR}hIuWFNg
        SqlCommand cmd = new SqlCommand("dbo.ap_EqType_List", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //XgAhvV[Ws
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read()) // \ȊԂ1si߂
            {
                // Ԃꂽf[^Xg[ɊÂďo̓p[^Ԃ
                //id = reader.GetInt32(0); // 擪͕sv
                EqType = EqType + reader.GetString(1) + ", ";
            }
        }
    }
}

---------------------------------------------------------------------
-- 12.5 LOB^̃p[^̈
---------------------------------------------------------------------

[SqlProcedure]
public static void cp_EqImage_Save(int EqID, string FileName)
{
    // XgAhvV[WԂVARBINARY(max)t@Cɕۑ
    const int bufferSize = 8000;

    // GetBytesŊi[BLOBbyte[]obt@
    byte[] outbyte = new byte[bufferSize];
    using (SqlConnection conn =
        new SqlConnection("Context Connection=true"))
    {
        conn.Open();
        SqlCommand command = conn.CreateCommand();

        // XgAhvV[W̎sɎgpR}h
        command.CommandText = "dbo.ap_EqImage_List";
        command.CommandType = CommandType.StoredProcedure;

        // ̓p[^
        SqlParameter paramID =
            new SqlParameter("@EqID", SqlDbType.Int);
        paramID.Value = EqID;
        command.Parameters.Add(paramID);

        // XgAhvV[Ws
        command.ExecuteNonQuery();

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

                // t@CiXg[j`
                using (FileStream fileStream =
                new FileStream(FileName,
                               FileMode.OpenOrCreate,
                               FileAccess.Write))
                {
                    // SqlContext.Pipe.Send("file stream created.");

                    // BLOBt@CiFileStreamj
                    // ރC^[
                    using (BinaryWriter binaryWriter =
                        new BinaryWriter(fileStream))
                    {
                        // SqlContext.Pipe.Send("BinaryWriter created");
                        // BLOBo͂̊Jnʒu
                        long startIndex = 0;

                        // outbyte[]zɃoCgǂݎA
                        // ǂݎoCg擾
                        long retval = reader.GetBytes(0,
                            startIndex, outbyte, 0, bufferSize);

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

                        // ǂݎoCgƃobt@
                        // TCYԂ́Aǂݎ
                        // 
                        while (retval == bufferSize)
                        {
                            // Xg[ɏ
                            binaryWriter.Write(outbyte);
                            //SqlContext.Pipe.Send("outbyte written.");

                            // t@Cɏ
                            binaryWriter.Flush();

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

                            // JnCfbNXړ
                            startIndex += bufferSize;
                            // R[hēxǂݎ
                            // obt@Ɋi[
                            retval = reader.GetBytes(
                                0, startIndex, outbyte, 0, bufferSize);
                        }

                        // SqlContext.Pipe.Send("outbyte last time.");
                        // ct@Cɏ
                        binaryWriter.Write(outbyte);
                        // t@Cɏ
                        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)
{
    // t@C摜ǂݎVARBINARY(max)Ɋi[

    // t@CiXg[j`
    using (FileStream fileStream =
    new FileStream(FileName,
                   FileMode.Open,
                   FileAccess.Read))
    {
        // SqlContext.Pipe.Send("file stream created.");

        // BLOBt@CiFileStreamj
        // ǂݍރ[_[
        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();

                // XgAhvV[W̎sɎgpR}h
                command.CommandText = "ap_EqImage_Update";
                command.CommandType = CommandType.StoredProcedure;

                // ̓p[^
                SqlParameter paramID =
                    new SqlParameter("@EqID", SqlDbType.Int);
                paramID.Value = EqID;
                command.Parameters.Add(paramID);

                // 摜擾o̓p[^
                SqlParameter paramEqImage =
                    new SqlParameter("@EqImage",SqlDbType.VarBinary,-1);
                paramEqImage.Value = blob;
                command.Parameters.Add(paramEqImage);

                // XgAhvV[Ws
                command.ExecuteNonQuery();
            }
        }
    }
    return;
}

