Beispiel #1
0
        /// <summary>
        /// Executes our example using an <see cref="AceQLConnection"/>
        /// </summary>
        /// <param name="connection"></param>
        private static async Task ExecuteExample(AceQLConnection connection)
        {
            string IN_DIRECTORY  = AceQLConnection.GetAceQLLocalFolder() + "\\";
            string OUT_DIRECTORY = IN_DIRECTORY + "out\\";

            _ = Directory.CreateDirectory(OUT_DIRECTORY);

            await connection.OpenAsync();

            AceQLConsole.WriteLine("ConnectionString: " + connection.ConnectionInfo.ConnectionString);
            AceQLConsole.WriteLine();
            AceQLConsole.WriteLine("AceQLConnection.GetClientVersion(): " + AceQLConnection.GetClientVersion());
            AceQLConsole.WriteLine("AceQLConnection.GetServerVersion(): " + await connection.GetServerVersionAsync());
            AceQLConsole.WriteLine("AceQL local folder: ");
            AceQLConsole.WriteLine(AceQLConnection.GetAceQLLocalFolder());

            if (!CONSOLE_INPUT_DONE)
            {
                AceQLConsole.WriteLine();
                AceQLConsole.WriteLine("Press enter to close....");
                Console.ReadLine();
                CONSOLE_INPUT_DONE = true;
            }

            AceQLTransaction transaction = await connection.BeginTransactionAsync();

            await transaction.CommitAsync();

            transaction.Dispose();

            string sql = "delete from customer";

            AceQLCommand command = null;

            command = new AceQLCommand()
            {
                CommandText = sql,
                Connection  = connection
            };
            command.Prepare();

            await command.ExecuteNonQueryAsync();

            sql = "delete from dustomer";

            command = new AceQLCommand()
            {
                CommandText = sql,
                Connection  = connection
            };
            command.Prepare();

            try
            {
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception exception)
            {
                AceQLConsole.WriteLine(exception.ToString());
            }

            sql     = "delete from dustomer where customer_id = @parm1 or fname = @parm2  ";
            command = new AceQLCommand()
            {
                CommandText = sql,
                Connection  = connection
            };
            command.Parameters.AddWithValue("@parm1", 1);
            command.Parameters.AddWithValue("@parm2", "Doe");

            try
            {
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception exception)
            {
                AceQLConsole.WriteLine(exception.ToString());
            }



            for (int i = 0; i < 3; i++)
            {
                sql =
                    "insert into customer values (@parm1, @parm2, @parm3, @parm4, @parm5, @parm6, @parm7, @parm8)";

                command = new AceQLCommand(sql, connection);

                int customer_id = i;

                command.Parameters.AddWithValue("@parm1", customer_id);
                command.Parameters.AddWithValue("@parm2", "Sir");
                command.Parameters.AddWithValue("@parm3", "André_" + customer_id);
                command.Parameters.Add(new AceQLParameter("@parm4", "Name_" + customer_id));
                command.Parameters.AddWithValue("@parm5", customer_id + ", road 66");
                command.Parameters.AddWithValue("@parm6", "Town_" + customer_id);
                command.Parameters.AddWithValue("@parm7", customer_id + "1111");
                command.Parameters.Add(new AceQLParameter("@parm8", new AceQLNullValue(AceQLNullType.VARCHAR))); //null value for NULL SQL insert.

                CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
                await command.ExecuteNonQueryAsync(cancellationTokenSource.Token);
            }

            command.Dispose();

            sql     = "select * from customer";
            command = new AceQLCommand(sql, connection);

            // Our dataReader must be disposed to delete underlying downloaded files
            using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
            {
                //await dataReader.ReadAsync(new CancellationTokenSource().Token)
                while (dataReader.Read())
                {
                    AceQLConsole.WriteLine();
                    int i = 0;
                    AceQLConsole.WriteLine("GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++));
                }
            }

            AceQLConsole.WriteLine("Before delete from orderlog 2");

            // Do next delete in a transaction because of BLOB
            transaction = await connection.BeginTransactionAsync();

            sql     = "delete from orderlog";
            command = new AceQLCommand(sql, connection);
            await command.ExecuteNonQueryAsync();

            command.Dispose();

            AceQLConsole.WriteLine("After delete from orderlog 2");

            await transaction.CommitAsync();

            Boolean doBlob = true;

            if (!doBlob)
            {
                return;
            }

            // Do next inserts in a transaction because of BLOB
            transaction = await connection.BeginTransactionAsync();

            try
            {
                for (int j = 1; j < 4; j++)
                {
                    sql =
                        "insert into orderlog values (@parm1, @parm2, @parm3, @parm4, @parm5, @parm6, @parm7, @parm8, @parm9)";

                    command = new AceQLCommand(sql, connection);

                    int customer_id = j;

                    string blobPath = null;

                    int index = getIndexFromDatabase();
                    blobPath = IN_DIRECTORY + "username_koala_" + index + ".jpg";

                    Stream stream = new FileStream(blobPath, FileMode.Open, System.IO.FileAccess.Read);

                    //customer_id integer NOT NULL,
                    //item_id integer NOT NULL,
                    //description character varying(64) NOT NULL,
                    //cost_price numeric,
                    //date_placed date NOT NULL,
                    //date_shipped timestamp without time zone,
                    //jpeg_image oid,
                    //is_delivered numeric,
                    //quantity integer NOT NULL,

                    command.Parameters.AddWithValue("@parm1", customer_id);
                    command.Parameters.AddWithValue("@parm2", customer_id);
                    command.Parameters.AddWithValue("@parm3", "Description_" + customer_id);
                    command.Parameters.Add(new AceQLParameter("@parm4", new AceQLNullValue(AceQLNullType.DECIMAL))); //null value for NULL SQL insert.
                    command.Parameters.AddWithValue("@parm5", DateTime.Now);
                    command.Parameters.AddWithValue("@parm6", DateTime.Now);
                    // Adds the Blob. (Stream will be closed by AceQLCommand)

                    command.Parameters.AddWithValue("@parm7", stream);

                    command.Parameters.AddWithValue("@parm8", 1);
                    command.Parameters.AddWithValue("@parm9", j * 2000);

                    AceQLConsole.WriteLine("Before await command.ExecuteNonQueryAsync()");
                    await command.ExecuteNonQueryAsync();

                    AceQLConsole.WriteLine("After await command.ExecuteNonQueryAsync()");
                }

                AceQLConsole.WriteLine("transaction.CommitAsync()");
                await transaction.CommitAsync();
            }
            catch (Exception)
            {
                await transaction.RollbackAsync();

                throw;
            }

            AceQLConsole.WriteLine("Before select *  from orderlog");

            // Do next selects in a transaction because of BLOB
            transaction = await connection.BeginTransactionAsync();

            sql     = "select * from orderlog";
            command = new AceQLCommand(sql, connection);

            using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
            {
                int k = 0;
                while (dataReader.Read())
                {
                    AceQLConsole.WriteLine();
                    int i = 0;
                    AceQLConsole.WriteLine("Get values using ordinal values:");
                    AceQLConsole.WriteLine("GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++) + "\n"
                                           + "GetValue: " + dataReader.GetValue(i++));

                    //customer_id
                    //item_id
                    //description
                    //item_cost
                    //date_placed
                    //date_shipped
                    //jpeg_image
                    //is_delivered
                    //quantity

                    AceQLConsole.WriteLine();
                    AceQLConsole.WriteLine("Get values using column name values:");
                    AceQLConsole.WriteLine("GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("customer_id"))
                                           + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("item_id")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("description")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("item_cost")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("date_placed")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("date_shipped")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("jpeg_image")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("is_delivered")) + "\n"
                                           + "GetValue: " + dataReader.GetValue(dataReader.GetOrdinal("quantity")));

                    AceQLConsole.WriteLine("==> dataReader.IsDBNull(3): " + dataReader.IsDBNull(3));
                    AceQLConsole.WriteLine("==> dataReader.IsDBNull(4): " + dataReader.IsDBNull(4));

                    // Download Blobs
                    int    index    = getIndexFromDatabase();
                    string blobPath = OUT_DIRECTORY + "username_koala_" + index + "_" + k + ".jpg";
                    k++;

                    using (Stream stream = await dataReader.GetStreamAsync(6))
                    {
                        using (var fileStream = File.Create(blobPath))
                        {
                            stream.CopyTo(fileStream);
                        }
                    }
                }
            }

            await transaction.CommitAsync();
        }