using Dapper;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CLOBTEST
{
    class Program
    {
        static string cs = "data source=XE;user id=...;password=...;";
        static void Main(string[] args)
        {
            using (var cn = new OracleConnection(cs))
            {
                cn.Execute("DELETE FROM CLOBTEST");
                cn.Execute("INSERT INTO CLOBTEST (ID,TEXT) VALUES (:id, :text)", new
                {
                    id = 1,
                    text = new String('A', 4000)
                });
                //注意：使用 OracleClobParameter 前要先 Open()
                cn.Open();
                cn.Execute("INSERT INTO CLOBTEST (ID,TEXT) VALUES (:id, :text)", new
                {
                    id = 2,
                    //將長於 4000 字串轉成 OracleClobParameter
                    text = new OracleClobParameter(new String('A', 4001))
                });
                Console.WriteLine(cn.Query("SELECT * FROM CLOBTEST").Count());
            }
            Console.ReadLine();
        }

        //REF: https://github.com/DapperLib/Dapper/issues/142
        internal class OracleClobParameter : SqlMapper.ICustomQueryParameter
        {
            private readonly string value;

            public OracleClobParameter(string value)
            {
                this.value = value;
            }

            public void AddParameter(IDbCommand command, string name)
            {

                // accesing the connection in open state.
                var clob = new OracleClob(command.Connection as OracleConnection);

                // It should be Unicode oracle throws an exception when
                // the length is not even.
                var bytes = Encoding.Unicode.GetBytes(value);
                var length = Encoding.Unicode.GetByteCount(value);

                int pos = 0;
                int chunkSize = 1024; // Oracle does not allow large chunks.

                while (pos < length)
                {
                    chunkSize = chunkSize > (length - pos) ? chunkSize = length - pos : chunkSize;
                    clob.Write(bytes, pos, chunkSize);
                    pos += chunkSize;
                }

                var param = new OracleParameter(name, OracleDbType.Clob);
                param.Value = clob;

                command.Parameters.Add(param);
            }
            
        }
    }
}