private async Task SelectCustomer()
{
string sql = "select * from customer";
AceQLCommand command = new AceQLCommand(sql, connection);
// Our dataReader should be disposed to delete underlying downloaded files
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
// Read is synchronous because all data to read are already downloaded
// when AceQLDataReader instance is created. Read accesses a StreamReader.
while (dataReader.Read())
{
//customer_id integer not null,
AceQLConsole.WriteLine();
int i = 0;
AceQLConsole.WriteLine("customer_id : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("customer_title: " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("fname : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("lname : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("addressline : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("town : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("zipcode : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("phone : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("Is phone NULL? : " + dataReader.IsDBNull(7));
}
}
}
/// <summary>
/// select customer as an asynchronous operation.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <returns>Task<System.String>.</returns>
public async Task <string> SelectCustomerAsync(int customerId)
{
String sql = "select * from customer where customer_id = @customer_id";
AceQLCommand command = new AceQLCommand(sql, connection);
command.Parameters.AddWithValue("@customer_id", 1);
String resultString = null;
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
int i = 0;
resultString = dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
resultString += " / " + dataReader.GetString(i++);
}
}
return(resultString);
}
/// <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;
}
}
private async Task SelectCustomerOne()
{
string sql = "select customer_id, customer_title, lname from customer where customer_id = 1";
using (AceQLCommand command = new AceQLCommand(sql, connection))
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
AceQLConsole.WriteLine();
int i = 0;
AceQLConsole.WriteLine("customer_id : " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("customer_title: " + dataReader.GetValue(i++));
AceQLConsole.WriteLine("lname : " + dataReader.GetValue(i++));
}
}
}
/// <summary>
/// Example of MS SQL Server Stored Procedure.
/// </summary>
/// <exception cref="AceQLException">If any Exception occurs.</exception>
public async Task CallStoredProcedure()
{
string sql = "{call ProcedureName(@parm1, @parm2, @parm3)}";
AceQLCommand command = new AceQLCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
AceQLParameter aceQLParameter2 = new AceQLParameter("@parm2", 2)
{
Direction = ParameterDirection.InputOutput
};
AceQLParameter aceQLParameter1 = new AceQLParameter("@parm1", 0);
AceQLParameter aceQLParameter3 = new AceQLParameter("@parm3")
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(aceQLParameter1);
command.Parameters.Add(aceQLParameter2);
command.Parameters.Add(aceQLParameter3);
AceQLConsole.WriteLine(sql);
AceQLConsole.WriteLine("BEFORE execute @parm1: " + aceQLParameter1.ParameterName + " / " + aceQLParameter1.Value + " (" + aceQLParameter2.Value.GetType() + ")");
AceQLConsole.WriteLine("BEFORE execute @parm2: " + aceQLParameter2.ParameterName + " / " + aceQLParameter2.Value + " (" + aceQLParameter2.Value.GetType() + ")");
AceQLConsole.WriteLine("BEFORE execute @parm3: " + aceQLParameter3.ParameterName + " / " + aceQLParameter3.Value);
AceQLConsole.WriteLine();
// 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())
{
int i = 2;
AceQLConsole.WriteLine("GetValue: " + dataReader.GetValue(i));
}
}
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("AFTER execute @parm2: " + aceQLParameter2.ParameterName + " / " + aceQLParameter2.Value + " (" + aceQLParameter2.Value.GetType() + ")");
AceQLConsole.WriteLine("AFTER execute @parm3: " + aceQLParameter3.ParameterName + " / " + aceQLParameter3.Value);
}
/// <summary>
/// Executes our example using an <see cref="AceQLConnection"/>
/// </summary>
/// <param name="connection"></param>
public static async Task ExecuteExample(AceQLConnection connection)
{
await connection.OpenAsync();
AceQLConsole.WriteLine("Host: " + connection.ConnectionInfo.ConnectionString);
AceQLConsole.WriteLine("aceQLConnection.GetClientVersion(): " + AceQLConnection.GetClientVersion());
AceQLConsole.WriteLine("aceQLConnection.GetServerVersion(): " + await connection.GetServerVersionAsync());
AceQLConsole.WriteLine("AceQL local folder: ");
AceQLConsole.WriteLine(AceQLConnection.GetAceQLLocalFolder());
int maxSelect = 1;
for (int j = 0; j < maxSelect; j++)
{
string sql = "select * from customer where customer_id > @parm1 and lname = @parm2";
AceQLCommand command = new AceQLCommand(sql, connection);
command.Parameters.AddWithValue("@parm2", "Name_5");
command.Parameters.AddWithValue("@parm1", 1);
// 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();
AceQLConsole.WriteLine("" + DateTime.Now);
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));
}
}
}
}
static async Task DoIt(string[] args)
{
try
{
Console.WriteLine(new DateTime() + " AceQLTestReader start...");
//Stream readStream = File.OpenRead("C:\\Users\\Nicolas de Pomereu\\Desktop\\result-set.txt");
Stream readStream = File.OpenRead("C:\\Users\\Nicolas de Pomereu\\Desktop\\original.txt");
IFile file = await GetUniqueResultSetFileAsync().ConfigureAwait(false);
AceQLDataReader dataReader = null; // new AceQLDataReader(file, readStream, 3, null);
// Our dataReader must be disposed to delete underlying downloaded files
//await dataReader.ReadAsync(new CancellationTokenSource().Token)
while (dataReader.Read())
{
Console.WriteLine();
int i = 0;
Console.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++));
}
readStream.Dispose();
}
catch (Exception exception)
{
Console.WriteLine(exception.ToString());
Console.WriteLine(exception.StackTrace);
Console.WriteLine("Press enter to close...");
Console.ReadLine();
}
}
////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Select all customer asynchronous.
/// Builds a 2 dimensions string array: string[number of rows, number of colums]
/// </summary>
/// <returns>A 2 dimensions string array: string[number of rows, number of colums].</returns>
/// <remarks>Nicolas De Pomereu, 24/05/2017.</remarks>
////////////////////////////////////////////////////////////////////////////////////////////////////
public async Task <string[, ]> SelectAllCutomersAsync()
{
int max_rows_number = 100000;
int columns_number = 3;
string[,] items = new string[max_rows_number, columns_number];
int cpt = 0;
String sql = "select * from customer";
AceQLCommand command = new AceQLCommand(sql, connection);
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
while (dataReader.Read())
{
int i = 0;
int j = 0;
items[cpt, j++] = (String)dataReader.GetValue(i++).ToString();
i++; // Skip customer_title
items[cpt, j++] = (String)dataReader.GetValue(i++);
items[cpt, j++] = (String)dataReader.GetValue(i++);
cpt++;
}
}
// Copy items to itemsFinal, with a new fixed [GetLength(0), GetLength(1)]
string[,] itemsFinal = new string[cpt, items.GetLength(1)];
for (int i = 0; i < cpt; i++)
{
for (int j = 0; j < items.GetLength(1); j++)
{
itemsFinal[i, j] = items[i, j];
}
}
return(itemsFinal);
}
/// <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>
/// Executes our example using an <see cref="AceQLConnection"/>
/// </summary>
/// <param name="connection"></param>
private static async Task ExecuteExample(AceQLConnection connection)
{
await connection.OpenAsync();
AceQLConsole.WriteLine("host: " + connection.ConnectionInfo.ConnectionString);
AceQLConsole.WriteLine("aceQLConnection.GetClientVersion(): " + AceQLConnection.GetClientVersion());
AceQLConsole.WriteLine("aceQLConnection.GetServerVersion(): " + await connection.GetServerVersionAsync());
AceQLConsole.WriteLine("AceQL local folder: ");
AceQLConsole.WriteLine(AceQLConnection.GetAceQLLocalFolder());
AceQLTransaction transaction = await connection.BeginTransactionAsync();
await transaction.CommitAsync();
transaction.Dispose();
string sql = "delete from customer_2";
AceQLCommand command = new AceQLCommand
{
CommandText = sql,
Connection = connection
};
command.Prepare();
await command.ExecuteNonQueryAsync();
for (int i = 0; i < 3; i++)
{
sql =
"insert into customer_2 values (@parm1, @parm2, @parm3, @parm4, @parm5, @parm6, @parm7, @parm8, @parm9, @parm_10)";
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", AceQLNullType.VARCHAR)); //null value for NULL SQL insert.
command.Parameters.AddWithValue("@parm9", customer_id + "_row_2");
command.Parameters.AddWithValue("@parm_10", customer_id + "_row_count");
CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
await command.ExecuteNonQueryAsync(cancellationTokenSource.Token);
}
command.Dispose();
sql = "select * from customer_2";
command = new AceQLCommand(sql, connection);
// Our dataReader must be disposed to delete underlying downloaded files
using (AceQLDataReader dataReader = await command.ExecuteReaderAsync())
{
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++) + "\n"
+ "GetValue: " + dataReader.GetValue(i));
}
}
AceQLConsole.WriteLine("Done.");
}
/// <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();
}
