/// <summary> /// Example of an SELECT of a BLOB /// </summary> /// <param name="customerId">The customer ID.</param> /// <param name="itemId">the item ID.</param> /// <exception cref="AceQLException">If any Exception occurs.</exception> public async Task SelectBlob(int customerId, int itemId) { // Create a transaction because some database engines require autocommit off AceQLTransaction transaction = await connection.BeginTransactionAsync(); try { string sql = "select customer_id, item_id, jpeg_image from orderlog" + " where customer_id = @customer_id and item_id = @item_id"; AceQLCommand command = new AceQLCommand(sql, connection); command.Parameters.AddWithValue("@customer_id", customerId); command.Parameters.AddWithValue("@item_id", itemId); using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { while (dataReader.Read()) { int i = 0; AceQLConsole.WriteLine("customer_id : " + dataReader.GetValue(i++)); AceQLConsole.WriteLine("item_id: " + dataReader.GetValue(i++)); string userPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); string blobPath = userPath + "\\koala_download.jpg"; AceQLConsole.WriteLine("Creating file from server BLOB in: " + blobPath); // Download Blob using (Stream stream = await dataReader.GetStreamAsync(i++)) { using (var fileStream = File.Create(blobPath)) { stream.CopyTo(fileStream); } } } await transaction.CommitAsync(); } } catch (Exception e) { // Transaction must always be terminated by a CommitAsync() or RollbackAsync() await transaction.RollbackAsync(); throw e; } }
/// <summary> /// Returns a Stream on the blob in product_image table /// </summary> /// <param name="productId"></param> /// <returns></returns> public async Task <Stream> SelectProductImageAsync(int productId) { AceQLTransaction transaction = await connection.BeginTransactionAsync(); Stream stream = null; try { String sql = "select image from product_image where product_id = @product_id"; AceQLCommand command = new AceQLCommand(sql, connection); command.Parameters.AddWithValue("@product_id", productId); using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { while (dataReader.Read()) { stream = await dataReader.GetStreamAsync(0); } await transaction.CommitAsync(); } } catch (Exception e) { try { await transaction.RollbackAsync(); } catch (Exception) { // Don't care } throw e; } return(stream); }
/// <summary> /// Example of 2 SELECT. /// </summary> /// <param name="customerId">The cutomer ID.</param> private async Task SelectCustomerAndOrderLogAsync(int customerId) { // Display the created Customer: string sql = "select customer_id, fname, lname from customer " + " where customer_id = @customer_id"; AceQLCommand command = new AceQLCommand(sql, connection); command.Parameters.AddWithValue("@customer_id", customerId); using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { while (dataReader.Read()) { int i = 0; int customerId2 = dataReader.GetInt32(i++); string fname = dataReader.GetString(i++); string lname = dataReader.GetString(i++); AceQLConsole.WriteLine(); AceQLConsole.WriteLine("customer_id : " + customerId2); AceQLConsole.WriteLine("fname : " + fname); AceQLConsole.WriteLine("lname : " + lname); } } sql = "select * from orderlog where customer_id = @customer_id and item_id = @item_id "; command = new AceQLCommand(sql, connection); command.Parameters.AddWithValue("@customer_id", customerId); command.Parameters.AddWithValue("@item_id", customerId); using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { while (dataReader.Read()) { int i = 0; int customerId2 = dataReader.GetInt32(i++); int itemId2 = dataReader.GetInt32(i++); string description = dataReader.GetString(i++); Decimal costPrice = dataReader.GetDecimal(i++); DateTime datePlaced = dataReader.GetDateTime(i++).Date; DateTime dateShipped = dataReader.GetDateTime(i++); Stream stream = await dataReader.GetStreamAsync(i ++); // null stream bool is_delivered = dataReader.GetInt32(i++) == 1 ? true : false; int quantity = dataReader.GetInt32(i++); AceQLConsole.WriteLine("customer_id : " + customerId2); AceQLConsole.WriteLine("item_id : " + itemId2); AceQLConsole.WriteLine("description : " + description); AceQLConsole.WriteLine("cost_price : " + costPrice); AceQLConsole.WriteLine("date_placed : " + datePlaced.Date); AceQLConsole.WriteLine("date_shipped: " + dateShipped); AceQLConsole.WriteLine("is_delivered: " + is_delivered); AceQLConsole.WriteLine("quantity : " + quantity); } } }
/// <summary> /// Executes our example using an <see cref="AceQLConnection"/> /// </summary> /// <param name="connection"></param> private static async Task ExecuteExample(AceQLConnection connection) { //Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); string IN_DIRECTORY = "c:\\test\\"; string OUT_DIRECTORY = "c:\\test\\out\\"; await connection.OpenAsync(); AceQLConsole.WriteLine("AceQLConnection.GetClientVersion(): " + AceQLConnection.GetClientVersion()); AceQLConsole.WriteLine("AceQLConnection.GetServerVersion(): " + await connection.GetServerVersionAsync()); AceQLConsole.WriteLine("AceQL local folder: "); AceQLConsole.WriteLine(await AceQLConnection.GetAceQLLocalFolderAsync()); AceQLTransaction transaction = await connection.BeginTransactionAsync(); await transaction.CommitAsync(); transaction.Dispose(); AceQLConsole.WriteLine("Before delete from orderlog"); // Do next delete in a transaction because of BLOB transaction = await connection.BeginTransactionAsync(); string sql = "delete from orderlog"; AceQLCommand command = new AceQLCommand(sql, connection); await command.ExecuteNonQueryAsync(); command.Dispose(); await transaction.CommitAsync(); // Do next inserts in a transaction because of BLOB transaction = await connection.BeginTransactionAsync(); AceQLConsole.WriteLine("Before insert into orderlog"); try { sql = "insert into orderlog values (@parm1, @parm2, @parm3, @parm4, @parm5, @parm6, @parm7, @parm8, @parm9)"; command = new AceQLCommand(sql, connection); int customer_id = 1; string blobPath = IN_DIRECTORY + "username_koala.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", 1 * 2000); await command.ExecuteNonQueryAsync(); await transaction.CommitAsync(); } catch (Exception exception) { await transaction.RollbackAsync(); throw exception; } AceQLConsole.WriteLine("Before select * from orderlog"); // Do next selects in a transaction because of BLOB transaction = await connection.BeginTransactionAsync(); sql = "select * from orderlog where cutomer_id = 1 and order_id = 1"; command = new AceQLCommand(sql, connection); using (AceQLDataReader dataReader = await command.ExecuteReaderAsync()) { int k = 0; 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++) + "\n" + "GetValue: " + dataReader.GetValue(i++)); // Download Blobs string blobPath = OUT_DIRECTORY + "username_koala_" + k + ".jpg"; k++; using (Stream stream = await dataReader.GetStreamAsync(6)) { using (var fileStream = File.Create(blobPath)) { //stream.CopyTo(fileStream); CopyStream(stream, fileStream); } } } } await transaction.CommitAsync(); }
/// <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(); }