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); } } }