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));
}
}
}
public static string Build()
{
String connectionString = null;
if (useLocal)
{
if (useLdapAuth)
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultLocalLdapAuth();
}
else
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultLocal();
}
}
else
{
if (useLdapAuth)
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultRemoteLdapAuth(useAuthenticatedProxy);
}
else
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultRemote(useAuthenticatedProxy);
}
}
AceQLConsole.WriteLine("connectionString: " + connectionString);
return(connectionString);
}
static async Task DoIt()
{
AceQLConsole.WriteLine("Building connection with credential...");
AceQLConnection connection = await ConnectionBuilderAsyncWithCredential().ConfigureAwait(false);;
DocSamples docSamples = new DocSamples(connection);
await docSamples.DeleteCustomers().ConfigureAwait(false);;
AceQLConsole.WriteLine("Insert customer...");
await docSamples.InsertCustomer().ConfigureAwait(false);;
AceQLConsole.WriteLine("display customer...");
await docSamples.SelectCustomer().ConfigureAwait(false);;
await docSamples.DeleteCustomers().ConfigureAwait(false);;
await docSamples.DeleteOrderlogs().ConfigureAwait(false);;
await docSamples.InsertCustomerAndOrderLogAsync(1, 1).ConfigureAwait(false);;
await docSamples.DeleteOrderlogs().ConfigureAwait(false);;
AceQLConsole.WriteLine("Insert BLOB...");
await docSamples.InsertBlob(1, 1).ConfigureAwait(false);;
AceQLConsole.WriteLine("Select BLOB...");
await docSamples.SelectBlob(1, 1);
await docSamples.DeleteOrderlogs().ConfigureAwait(false);;
AceQLConsole.WriteLine("Insert BLOB with ProgressIndicator...");
await docSamples.InsertBlobProgressIndicator(1, 1).ConfigureAwait(false);;
AceQLConsole.WriteLine("Select BLOB...");
await docSamples.SelectBlob(1, 1).ConfigureAwait(false);;
}
/// <summary>
/// Example of 2 INSERT in the same transaction.
/// </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 InsertCustomerAndOrderLogAsync(int customerId, int itemId)
{
// Create a transaction
AceQLTransaction transaction = await connection.BeginTransactionAsync();
string sql = "insert into customer values " +
"(@customer_id, @customer_title, @fname, " +
"@lname, @addressline, @town, @zipcode, @phone)";
try
{
AceQLCommand command = new AceQLCommand(sql, connection)
{
Transaction = transaction // Not required, will do nothing.
};
command.Parameters.AddWithValue("@customer_id", customerId);
command.Parameters.AddWithValue("@customer_title", "Sir");
command.Parameters.AddWithValue("@fname", "John");
command.Parameters.AddWithValue("@lname", "Doe");
command.Parameters.AddWithValue("@addressline", "1 Madison Ave");
command.Parameters.AddWithValue("@town", "New York");
command.Parameters.AddWithValue("@zipcode", "NY 10010");
command.Parameters.Add(new AceQLParameter("@phone", new AceQLNullValue(AceQLNullType.VARCHAR)));
await command.ExecuteNonQueryAsync();
sql = "insert into orderlog values " +
"(@customer_id, @item_id, @description, " +
"@item_cost, @date_placed, @date_shipped, " +
"@jpeg_image, @is_delivered, @quantity)";
command = new AceQLCommand(sql, connection);
AceQLConsole.WriteLine("insert into orderlog...");
command.Parameters.AddWithValue("@customer_id", customerId);
command.Parameters.AddWithValue("@item_id", itemId);
command.Parameters.AddWithValue("@description", "Item Description");
command.Parameters.AddWithValue("@item_cost", 99D);
command.Parameters.AddWithValue("@date_placed", DateTime.Now);
command.Parameters.AddWithValue("@date_shipped", DateTime.Now);
// No blob for now
command.Parameters.Add(new AceQLParameter("@jpeg_image", new AceQLNullValue(AceQLNullType.BLOB)));
command.Parameters.AddWithValue("@is_delivered", 1);
command.Parameters.AddWithValue("@quantity", 1);
await command.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch (Exception e)
{
// Transaction must always be terminated by a CommitAsync() or RollbackAsync()
await transaction.RollbackAsync();
throw;
}
}
static async Task DoIt(string[] args)
{
AceQLConsole.WriteLine("Building connection with credential...");
AceQLConnection connection = await ConnectionBuilderAsyncWithCredential().ConfigureAwait(false);
WebDocSample webDocSamples = new WebDocSample(connection);
await webDocSamples.DeleteCustomers().ConfigureAwait(false);
AceQLConsole.WriteLine("Insert customer...");
await webDocSamples.InsertCustomer().ConfigureAwait(false);
AceQLConsole.WriteLine("display customer 1...");
await webDocSamples.SelectCustomerOne().ConfigureAwait(false);
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("update customer...");
await webDocSamples.UpdateCustomer().ConfigureAwait(false);
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("display customer...");
await webDocSamples.SelectCustomer().ConfigureAwait(false);
AceQLConsole.WriteLine();
await webDocSamples.DeleteCustomers().ConfigureAwait(false);
await webDocSamples.DeleteOrderlogs().ConfigureAwait(false);
await webDocSamples.InsertCustomerAndOrderLogAsync(1, 1).ConfigureAwait(false);
AceQLConsole.WriteLine();
await webDocSamples.DeleteOrderlogs().ConfigureAwait(false);
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("Insert BLOB...");
await webDocSamples.InsertBlob(1, 1).ConfigureAwait(false);
AceQLConsole.WriteLine("Select BLOB...");
await webDocSamples.SelectBlob(1, 1).ConfigureAwait(false);
await webDocSamples.DeleteOrderlogs();
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("Insert BLOB with ProgressIndicator...");
await webDocSamples.InsertBlobProgressIndicator(1, 1).ConfigureAwait(false);
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("Select BLOB...");
await webDocSamples.SelectBlob(1, 1).ConfigureAwait(false);
await connection.CloseAsync();
AceQLConsole.WriteLine("Connection closed.");
}
private async Task InsertCustomer()
{
string sql = "insert into customer values (1, 'Sir', 'John', 'Doe', " +
"'1 Madison Ave', 'New York', 'NY 10010', NULL)";
using (AceQLCommand command = new AceQLCommand(sql, connection))
{
int rows = await command.ExecuteNonQueryAsync();
AceQLConsole.WriteLine("Rows updated: " + rows);
}
}
/// <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 UpdateCustomer()
{
string sql = "update customer set fname = @fname where customer_id = @customer_id";
using (AceQLCommand command = new AceQLCommand(sql, connection))
{
command.Prepare();
command.Parameters.AddWithValue("@customer_id", 1);
command.Parameters.AddWithValue("@fname", "Jim");
int rows = await command.ExecuteNonQueryAsync();
AceQLConsole.WriteLine("Rows updated: " + rows);
}
}
/// <summary>
/// Example of an INSERT 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 InsertBlobProgressIndicator(int customerId, int itemId)
{
// Create a transaction because some database engines require autocommit off
AceQLTransaction transaction = await connection.BeginTransactionAsync();
try
{
string sql = "insert into orderlog values " +
"(@customer_id, @item_id, @description, " +
"@item_cost, @date_placed, @date_shipped, " +
"@jpeg_image, @is_delivered, @quantity)";
AceQLCommand command = new AceQLCommand(sql, connection);
string userPath =
Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string blobPath = userPath + "\\koala.jpg";
Stream stream = new FileStream(blobPath, FileMode.Open, FileAccess.Read);
long length = new FileInfo(blobPath).Length;
AceQLConsole.WriteLine("blobPath: " + blobPath);
AceQLConsole.WriteLine("insert into orderlog...");
command.Parameters.AddWithValue("@customer_id", customerId);
command.Parameters.AddWithValue("@item_id", itemId);
command.Parameters.AddWithValue("@description", "Item Description");
command.Parameters.AddWithValue("@item_cost", 99D);
command.Parameters.AddWithValue("@date_placed", DateTime.Now);
command.Parameters.AddWithValue("@date_shipped", DateTime.Now);
command.Parameters.AddWithValue("@jpeg_image", stream, length);
command.Parameters.AddWithValue("@is_delivered", 1);
command.Parameters.AddWithValue("@quantity", 1);
AceQLProgressIndicator progressIndicator = new AceQLProgressIndicator();
connection.SetProgressIndicator(progressIndicator);
await command.ExecuteNonQueryAsync();
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++));
}
}
}
public static void TheMain(string[] args)
{
try
{
DoIt(args).Wait();
//DoIt(args).GetAwaiter().GetResult();
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("Press enter to close....");
Console.ReadLine();
}
catch (Exception exception)
{
AceQLConsole.WriteLine(exception.ToString());
AceQLConsole.WriteLine(exception.StackTrace);
AceQLConsole.WriteLine("Press enter to close...");
Console.ReadLine();
}
}
public static string Build()
{
String filePath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\aceql.client.ini";
PropFileReader propFileReader = new PropFileReader(filePath);
useLocal = Boolean.Parse(propFileReader.getProperty("useLocal"));
useLdapAuth = Boolean.Parse(propFileReader.getProperty("useLdapAuth"));
typeAuthenticatedProxy = int.Parse(propFileReader.getProperty("typeAuthenticatedProxy"));
AceQLConsole.WriteLine("useLocal : " + useLocal);
AceQLConsole.WriteLine("useLdapAuth : " + useLdapAuth);
AceQLConsole.WriteLine("typeAuthenticatedProxy: " + typeAuthenticatedProxy);
AceQLConsole.WriteLine();
String connectionString = null;
if (useLocal)
{
if (useLdapAuth)
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultLocalLdapAuth();
}
else
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultLocal();
}
}
else
{
if (useLdapAuth)
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultRemoteLdapAuth(typeAuthenticatedProxy);
}
else
{
connectionString = ConnectionStringBuilderFactory.CreateDefaultRemote(typeAuthenticatedProxy);
}
}
AceQLConsole.WriteLine("connectionString: " + connectionString);
return connectionString;
}
/// <summary>
/// Does it.
/// </summary>
/// <param name="args">The arguments.</param>
/// <returns></returns>
public static async Task DoIt(string[] args)
{
try
{
int customerId = 1;
int itemId = 1;
// Make sure connection is always closed in order to close and release
// server connection into the pool
using (AceQLConnection connection = await ConnectionBuilderAsync())
{
MyRemoteConnection myRemoteConnection = new MyRemoteConnection(
connection);
// Delete previous instances, so that user can recall
AceQLConsole.WriteLine("deleting customer...");
await myRemoteConnection.DeleteCustomerAsync(customerId).ConfigureAwait(false);;
AceQLConsole.WriteLine("deleting orderlog...");
await myRemoteConnection.DeleteOrderlogAsync(customerId, itemId).ConfigureAwait(false);;
await myRemoteConnection.InsertCustomerAndOrderLogAsync(customerId, itemId).ConfigureAwait(false);;
await myRemoteConnection.SelectCustomerAndOrderLogAsync(customerId).ConfigureAwait(false);;
await connection.CloseAsync().ConfigureAwait(false);;
AceQLConsole.WriteLine("The end...");
}
AceQLConsole.WriteLine();
AceQLConsole.WriteLine("Press enter to close....");
Console.ReadLine();
}
catch (Exception exception)
{
AceQLConsole.WriteLine(exception.ToString());
AceQLConsole.WriteLine(exception.StackTrace);
AceQLConsole.WriteLine("Press enter to close...");
Console.ReadLine();
}
}
/// <summary>
/// RemoteConnection Quick Start client example.
/// Creates a Connection to a remote database and open it.
/// </summary>
/// <returns>The connection to the remote database</returns>
/// <exception cref="AceQLException">If any Exception occurs.</exception>
public static async Task <AceQLConnection> ConnectionBuilderAsync()
{
// C# Example: connection to a remote database
string server = "https://www.acme.com:9443/aceql";
string database = "sampledb";
string username = "******";
string password = "******";
string connectionString = $"Server={server}; Database={database}; "
+ $"Username={username}; Password={password}";
AceQLConnection connection = new AceQLConnection(connectionString);
// Attempt to establish a connection to the remote SQL database:
await connection.OpenAsync();
AceQLConsole.WriteLine("Connected to database " + database + "!");
return(connection);
}
/// <summary>
/// RemoteConnection Quick Start client example.
/// Creates a Connection to a remote database and open it.
/// </summary>
/// <returns>The connection to the remote database</returns>
/// <exception cref="AceQLException">If any Exception occurs.</exception>
public static async Task <AceQLConnection> ConnectionBuilderAsyncWithCredential()
{
string server = "https://www.aceql.com:9443/aceql";
string database = "sampledb";
string connectionString = $"Server={server}; Database={database}";
string username = "******";
char[] password = { 'M', 'y', 'S', 'e', 'c', 'r', 'e', 't' };
AceQLConnection connection = new AceQLConnection(connectionString)
{
Credential = new AceQLCredential(username, password)
};
// Opens the connection with the remote database
await connection.OpenAsync();
AceQLConsole.WriteLine("Successfully connected to database " + database + "!");
return(connection);
}
internal async Task InsertCustomerPreparedStatement()
{
string sql = "insert into customer values " + "" +
"(@customer_id, @customer_title, @fname, " +
"@lname, @addressline, @town, @zipcode, @phone)";
AceQLCommand command = new AceQLCommand(sql, connection);
command.Parameters.AddWithValue("@customer_id", 1);
command.Parameters.AddWithValue("@customer_title", "Sir");
command.Parameters.AddWithValue("@fname", "Doe");
command.Parameters.AddWithValue("@lname", "John");
// Alternate syntax
command.Parameters.Add(new AceQLParameter("@addressline", "1 Madison Ave"));
command.Parameters.AddWithValue("@town", "New York");
command.Parameters.AddWithValue("@zipcode", "NY 10010");
// We don't know the phone number
command.Parameters.Add(new AceQLParameter("@phone", new AceQLNullValue(AceQLNullType.VARCHAR)));
int rows = await command.ExecuteNonQueryAsync();
AceQLConsole.WriteLine("Rows updated: " + rows);
}
/// <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);
}
}
}
