using System;
using System.Data.OracleClient;
using System.Globalization;

namespace TimestampTest
{
    internal class Program
    {
        private const string Password = "...";
        private const string Tns = "...";
        private const string User = "...";
        private const string CreateTableBlock = "create table timestamp_test(t timestamp)";
        private const string DropTableBlock = "drop table timestamp_test";
        private const string InsertBlock = "insert into timestamp_test(t) values ({0})";
        private const string OraDate = "to_timestamp('31.12.2009 00:00:00.123000', 'DD.MM.YYYY HH24:MI:SS.FF')";
        private const string OraErrDate = "to_timestamp('31.12.9999', 'DD.MM.YYYY')";
        private const string SelectBlock = "select count(1) from timestamp_test where t = :t";

        private static readonly DateTime NetDate = DateTime.ParseExact("31.12.2009 00:00:00.123", "dd.MM.yyyy HH:mm:ss.fff",
                                                                       CultureInfo.InvariantCulture);

        private static readonly DateTime NetErrDate = DateTime.ParseExact("31.12.9999 00:00", "dd.MM.yyyy HH:mm",
                                                                          CultureInfo.InvariantCulture);

        private static OracleCommand MakeCommand(string text, OracleConnection connection,
                                                 params OracleParameter[] parameters)
        {
            var command = new OracleCommand(text, connection);
            command.Parameters.AddRange(parameters);
            return command;
        }

        private static OracleConnection MakeConnection()
        {
            var connection =
                new OracleConnection(string.Format("Server={0}; User ID={1}; Password={2}", Tns, User, Password));
            connection.Open();
            return connection;
        }

        private static OracleCommand DropTableCommand(OracleConnection connection)
        {
            return MakeCommand(DropTableBlock, connection);
        }

        private static OracleCommand CreateTableCommand(OracleConnection connection)
        {
            return MakeCommand(CreateTableBlock, connection);
        }

        private static OracleCommand InsertCommand(string timestampText, OracleConnection connection)
        {
            return MakeCommand(string.Format(InsertBlock, timestampText), connection);
        }

        private static OracleCommand InsertCommand(DateTime timestamp, OracleConnection connection)
        {
            var parameter = new OracleParameter("t", timestamp) {OracleType = OracleType.Timestamp};
            return MakeCommand(string.Format(InsertBlock, ":t"), connection, new[] { parameter });
        }

        private static OracleCommand SelectCommand(DateTime timestamp, bool specifyType, OracleConnection connection)
        {
            var parameter = new OracleParameter("t", timestamp);
            if (specifyType)
                parameter.OracleType = OracleType.Timestamp;
            return MakeCommand(SelectBlock, connection, new[] { parameter });
        }

        private static void Main(string[] args)
        {
            OracleConnection connection = MakeConnection();
            try
            {
                DropTableCommand(connection).ExecuteNonQuery();
            }
            catch (OracleException e)
            {
                if (e.Code != 942)
                    throw;
            }
            CreateTableCommand(connection).ExecuteNonQuery();
            InsertCommand(OraDate, connection).ExecuteNonQuery();
            InsertCommand(OraErrDate, connection).ExecuteNonQuery();
            InsertCommand(NetErrDate, connection).ExecuteNonQuery();
            var countExp = (decimal) SelectCommand(NetDate, true, connection).ExecuteScalar();
            var countImp = (decimal)SelectCommand(NetDate, false, connection).ExecuteScalar();
            var errCountExp = (decimal) SelectCommand(NetErrDate, true, connection).ExecuteScalar();
            var errCountImp = (decimal)SelectCommand(NetErrDate, false, connection).ExecuteScalar();

            Console.WriteLine("Count(explicit type): {0}\nCount(implicit type): {1}\nErrCount(explicit type): {2}\nErrCount(implicit type): {3}", countExp, countImp, errCountExp, errCountImp);
            Console.ReadLine();
        }
    }
}