static async Task DoIt(string[] args) { string username = "******"; string password = "******"; //customer_id integer NOT NULL, //customer_title character(4), //fname character varying(32), //lname character varying(32) NOT NULL, //addressline character varying(64), //town character varying(32), //zipscode character(10) NOT NULL, //phone character varying(32), string connectionString = null; bool useProxy = false; if (useProxy) { string proxyUsername = "******"; string proxyPassword = ""; connectionString = $"Server={server}; Database={database}; ProxyUsername={proxyUsername}; ProxyPassword= {proxyPassword}"; } else { connectionString = $"Server={server}; Database={database}"; } AceQLCredential credential = new AceQLCredential(username, password.ToCharArray()); // Make sure connection is always closed to close and release server connection into the pool using (AceQLConnection connection = new AceQLConnection(connectionString)) { //connection.SetTraceOn(true); connection.Credential = credential; await ExecuteExample(connection).ConfigureAwait(false); await connection.CloseAsync(); AceQLConnection connection2 = new AceQLConnection(connectionString) { Credential = credential }; await connection2.OpenAsync(); AceQLConsole.WriteLine("connection2.GetServerVersion(): " + await connection2.GetServerVersionAsync()); await connection2.LogoutAsync().ConfigureAwait(false); } }
/// <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)); } } } }
/// <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.ConnectionString); AceQLConsole.WriteLine("aceQLConnection.GetClientVersion(): " + AceQLConnection.GetClientVersion()); AceQLConsole.WriteLine("aceQLConnection.GetServerVersion(): " + await connection.GetServerVersionAsync()); AceQLConsole.WriteLine("AceQL local folder: "); AceQLConsole.WriteLine(await AceQLConnection.GetAceQLLocalFolderAsync()); RemoteDatabaseMetaData remoteDatabaseMetaData = connection.GetRemoteDatabaseMetaData(); string userPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile); string schemaFilePath = userPath + "\\db_schema.out.html"; // Download Schema in HTML format: using (Stream stream = await remoteDatabaseMetaData.DbSchemaDownloadAsync()) { using (var fileStream = File.Create(schemaFilePath)) { stream.CopyTo(fileStream); } } System.Diagnostics.Process.Start(schemaFilePath); AceQLConsole.WriteLine("Creating schema done."); JdbcDatabaseMetaData jdbcDatabaseMetaData = await remoteDatabaseMetaData.GetJdbcDatabaseMetaDataAsync(); AceQLConsole.WriteLine("Major Version: " + jdbcDatabaseMetaData.GetJDBCMajorVersion); AceQLConsole.WriteLine("Minor Version: " + jdbcDatabaseMetaData.GetJDBCMinorVersion); AceQLConsole.WriteLine("IsReadOnly : " + jdbcDatabaseMetaData.IsReadOnly); AceQLConsole.WriteLine("JdbcDatabaseMetaData: " + jdbcDatabaseMetaData.ToString().Substring(1, 200)); AceQLConsole.WriteLine(); AceQLConsole.WriteLine("Get the table names:"); List <String> tableNames = await remoteDatabaseMetaData.GetTableNamesAsync(); AceQLConsole.WriteLine("Print the column details of each table:"); foreach (String tableName in tableNames) { Table table = await remoteDatabaseMetaData.GetTableAsync(tableName); AceQLConsole.WriteLine("Columns:"); foreach (Column column in table.Columns) { AceQLConsole.WriteLine(column.ToString()); } } AceQLConsole.WriteLine(); String name = "orderlog"; Table tableOrderlog = await remoteDatabaseMetaData.GetTableAsync(name); AceQLConsole.WriteLine("table name: " + tableOrderlog.TableName); AceQLConsole.WriteLine("table keys: "); List <PrimaryKey> primakeys = tableOrderlog.PrimaryKeys; foreach (PrimaryKey primaryKey in primakeys) { AceQLConsole.WriteLine("==> primaryKey: " + primaryKey); } AceQLConsole.WriteLine(); AceQLConsole.WriteLine("Full table: " + tableOrderlog); AceQLConsole.WriteLine(); AceQLConsole.WriteLine("Done."); }
/// <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> /// 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(); }