using Dapper;
using Sap.Data.SQLAnywhere;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProcedureBug
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SAConnection("SERVER=setup;DBN=simplic;uid=dba;pwd=sql;links=tcpip;charset=utf-8"))
            {
                connection.Open();

                connection.Execute(@"
                    CREATE TABLE IF NOT EXISTS ""admin"".""Bug_Sample"" (

                        ""Guid"" UNIQUEIDENTIFIER NOT NULL,
                        ""SampleText"" VARCHAR(200) NULL,
                        PRIMARY KEY(""Guid"" ASC)
                    ) IN ""system"";");

                connection.Execute(@"
                    CREATE OR REPLACE PROCEDURE admin.SampleInsert(@in_ui uniqueidentifier, @in_txt varchar(200))
                    BEGIN
                        INSERT INTO admin.Bug_Sample (Guid, SampleText) VALUES (@in_ui, @in_txt);
                        MESSAGE 'Hey!';
                        SELECT NewId() as NewGuid;
                    END;
                ");

                var id = Guid.NewGuid();
                var text = "SAMPLE TEXT01";

                ExecuteSqlWithResult("SELECT * FROM admin.SampleInsert(?, ?)", "", id, text);
            }
        }

        public static void ExecuteSqlWithResult(string SqlStatement, string ConnectionName, params object[] Parameter)
        {
            using (SAConnection connection = new SAConnection("SERVER=setup;DBN=simplic;uid=dba;pwd=sql;links=tcpip;charset=utf-8"))
            {
                connection.Open();
                IDataReader cDataReader = null;

                try
                {
                    SACommand cCommand = new SACommand(SqlStatement, connection);

                    if (Parameter != null)
                    {
                        foreach (var cParam in Parameter)
                        {
                            cCommand.Parameters.Add(new SAParameter() { Value = cParam });
                        }
                    }

                    cDataReader = cCommand.ExecuteReader(CommandBehavior.CloseConnection);

                    while (cDataReader.Read())
                    {
                        // ....
                    }
                }
                catch (Exception ex)
                {
                    if (cDataReader != null)
                    {
                        cDataReader.Close();
                    }

                    if (connection != null)
                    {
                        if (connection.State != ConnectionState.Closed)
                        {
                            connection.Close();
                        }
                    }

                    throw ex;
                }

                // Without this line, no data will be written!!!!
                // cDataReader.Close();
                connection.Close();
            }
        }
    }
}