/// <summary> /// Creates a new database command with the specified query in transactionless mode. /// </summary> /// <param name="connection">The database server connection.</param> /// <param name="query">The command query.</param> /// <param name="transaction">The database transaction, or <b>null</b> if no transaction is used.</param> public DbCommandSql(SqlConnection connection, DbQuerySql query, DbTransactionSql transaction = null) : base(query) { // Create a modified query for this server, replacing the query parameters. string queryServer = null; // Create an array of parameter names. string[] parameterNames = null; // If the command has parameters. if (query.Parameters.Count > 0) { // Process the query parameters, by creating the list of parameter names. parameterNames = new string[query.Parameters.Count]; for (int index = 0; index < query.Parameters.Count; index++) { parameterNames[index] = "@param{0}".FormatWith(index); } // Create the query. queryServer = query.Query.FormatWith(parameterNames); } else { // If there are no parameters, use the givem query. queryServer = query.Query; } // If the database transaction is not null. if (transaction != null) this.command = new SqlCommand(queryServer, connection, transaction.Transaction); else this.command = new SqlCommand(queryServer, connection); // Add the parameters to the command. for (int index = 0; index < query.Parameters.Count; index++) { this.command.Parameters.AddWithValue(parameterNames[index], query.Parameters[index]); } }
/// <summary> /// Creates a database query that returns the field for the specified table for a given field value of /// a related table. /// </summary> /// <param name="table">The database table.</param> /// <param name="tableOn">The related table.</param> /// <param name="fieldsOn">The related table field names.</param> /// <param name="valuesOn">The related table field values.</param> /// <param name="database">The datase, if needed.</param> /// <param name="userState">The user state.</param> /// <returns>The query.</returns> public static DbQuerySql CreateSelectAllOn(ITable table, ITable tableOn, string[] fieldsOn, object[] valuesOn, DbObjectDatabase database, object userState = null) { // If the table is not configured, throw an exception. if (!table.IsConfigured) throw new DbException("Cannot create a database query for the table \'{0}\'. The table is not configured.".FormatWith(table.LocalName)); // If the table requires a database, check that a database is configured for this server. if (table.DefaultDatabase && (database == null)) throw new DbException("Cannot create a database query for the table \'{0}\'. The table requires a database but the server does not have a database configured.".FormatWith(table.LocalName)); // Check the number of field names and values matches. if (fieldsOn.Length != valuesOn.Length) throw new DbException("Cannot create a database query for the table \'{0}\'. The number of field names (\'{1}\') and field values (\'{2}\') must be equal.".FormatWith(table.LocalName, fieldsOn.Length, valuesOn.Length)); // Get the relationships between the current table and the right table. List<IRelationship> relationships = new List<IRelationship>(); foreach (IRelationship relationship in table.Relationships) { if (relationship.RightTable == tableOn) { relationships.Add(relationship); } } // If there are no relationships between tables, throw an exception. if (relationships.Count == 0) throw new DbException("Cannot create a database query for table \'{0}\' based on a match in the table \'{1}\', because there is no relationship between the two tables.".FormatWith(table.LocalName, tableOn.LocalName)); // The field index. int fieldIndex = 0; // Get the table names. string tableNameLeft = DbQuerySql.GetTableName(table, database); string tableNameRight = DbQuerySql.GetTableName(tableOn, database); // Create the query. DbQuerySql query = new DbQuerySql( "SELECT {0} FROM {1} INNER JOIN {2} ON {3}{4}".FormatWith( DbQuerySql.GetFieldNames(table, tableNameLeft), tableNameLeft, tableNameRight, DbQuerySql.GetFieldsMatch(tableOn, fieldsOn, database, ref fieldIndex), DbQuerySql.GetRelationshipsMatch(relationships, database) ), table, userState); // Add the parameters. foreach (object valueOn in valuesOn) { query.parameters.Add(valueOn); } // Return the query. return query; }
/// <summary> /// Creates a new database command with the specified query. /// </summary> /// <param name="query">The command query.</param> public DbCommand(DbQuerySql query) { this.query = query; }
/// <summary> /// An event handler called when the database query completed successfully and the resulting data is object /// data. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The database result.</param> /// <param name="recordsAffected">The number of records affected.</param> private void DatabaseQuerySuccess(DbServerSql server, DbQuerySql query, DbDataObject result, int recordsAffected) { // Execute the code on the UI thread. this.Invoke(() => { this.OnQuerySucceeded(server, query, result, recordsAffected); }); }
/// <summary> /// A method called when the execution of the database query succeeded and the data is object. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The result data.</param> /// <param name="recordsAffected">The number of records affected.</param> protected virtual void OnQuerySucceeded(DbServerSql server, DbQuerySql query, DbDataObject result, int recordsAffected) { }
/// <summary> /// A method called when the executiopn of the database query has failed. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="exception">The exception.</param> protected virtual void OnQueryFailed(DbServerSql server, DbQuerySql query, Exception exception) { }
/// <summary> /// Creates a new database command with the specified query and transaction. /// </summary> /// <param name="query">The database query.</param> /// <param name="transaction">The database transaction.</param> /// <returns>The database command.</returns> public abstract DbCommand CreateCommand(DbQuerySql query, DbTransaction transaction);
/// <summary> /// Creates a new database command with the specified query and transaction. /// </summary> /// <param name="query">The database query.</param> /// <param name="transaction">The database transaction.</param> /// <returns>The database command.</returns> public override DbCommand CreateCommand(DbQuerySql query, DbTransaction transaction) { return new DbCommandSql(this.connection, query, transaction as DbTransactionSql); }
/// <summary> /// A method called when staring a query. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="command">The database command.</param> protected override void OnQueryStarted(DbServerSql server, DbQuerySql query, DbCommand command) { // Disable the control. this.tabControl.Enabled = false; // Raise the database operation started event. if (this.DatabaseOperationStarted != null) this.DatabaseOperationStarted(this, EventArgs.Empty); }
/// <summary> /// A method called when a query failed. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="exception">The exception.</param> protected override void OnQueryFailed(DbServerSql server, DbQuerySql query, Exception exception) { // Enable the control. this.tabControl.Enabled = true; // Raise the database operation finished event. if (this.DatabaseOperationFinished != null) this.DatabaseOperationFinished(this, EventArgs.Empty); }
/// <summary> /// An event handler called when the refresh operation completed successfully and the resulting data is object /// data. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The database result.</param> /// <param name="recordsAffected">The number of records affected.</param> protected override void OnQuerySucceeded(DbServerSql server, DbQuerySql query, DbDataObject result, int recordsAffected) { // Set the current result. this.result = result; try { // Add the table rows. for (int row = 0; row < this.result.RowCount; row++) { // Add the new row, and get the row index. int index = this.dataGrid.Rows.Add(this.query.Table.GetValues(this.result[row])); // Set the row tag with the table object. this.dataGrid.Rows[index].Tag = result[row]; } } catch (Exception exception) { // If an exception occurs, call the refresh fail method. this.OnQueryFailed(server, query, exception); return; } // Set the current command to null. this.command = null; // Enable the buttons. this.buttonRefresh.Enabled = true; this.buttonCancel.Enabled = false; this.buttonClose.Enabled = true; // Update the status box. this.labelStatus.Text = "{0} object{1} fetched.".FormatWith(result.RowCount, result.RowCount.PluralSuffix()); // Raise the database operation finished event. if (this.DatabaseOperationFinished != null) this.DatabaseOperationFinished(this, EventArgs.Empty); }
/// <summary> /// A method called when the execution of the database query starts. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="command">The database command.</param> protected override void OnQueryStarted(DbServerSql server, DbQuerySql query, DbCommand command) { // Save the current command. this.command = command; // Enable the cancel button. this.buttonRefresh.Enabled = false; this.buttonClose.Enabled = false; this.buttonCancel.Enabled = true; // Raise the database operation started event. if (this.DatabaseOperationStarted != null) this.DatabaseOperationStarted(this, EventArgs.Empty); }
/// <summary> /// A method called when the executiopn of the database query has failed. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="exception">The exception.</param> protected override void OnQueryFailed(DbServerSql server, DbQuerySql query, Exception exception) { // Enable the buttons. this.buttonRefresh.Enabled = true; this.buttonCancel.Enabled = false; this.buttonClose.Enabled = true; // Set the current command to null. this.command = null; // Update the status box. this.labelStatus.Text = "Query failed. {0}".FormatWith(exception.Message); // Raise the database operation finished event. if (this.DatabaseOperationFinished != null) this.DatabaseOperationFinished(this, EventArgs.Empty); }
/// <summary> /// A method called when the user cancels a current database query. /// </summary> /// <param name="query">The database query.</param> protected override void OnQueryCanceling(DbQuerySql query) { // Disable the cancel button. this.buttonCancel.Enabled = false; // If the current command is null, do nothing. if (null == this.command) return; // Cancel the command. base.DatabaseQueryCancel(this.command); }
/// <summary> /// Selects records from the specified database server given a custom query. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database table.</param> /// <param name="result">The database results, if any.</param> public void Select(DbServerSql server, DbQuerySql query, DbDataObject result) { // Check the database table is configured. if (!query.Table.IsConfigured) throw new DbException("Cannot select the list of database objects for table \'{0}\', because the table is not configured.".FormatWith(query.Table.LocalName)); // Check the results, if different from null, are for the current table and if not, ignore them. if (result != null) { if (result.Table != query.Table) result = null; } // Set the current server. this.server = server; // Set the current query. this.query = query; // Set the current results. this.result = result; // Clear the data grid. this.dataGrid.Rows.Clear(); this.dataGrid.Columns.Clear(); // Initialize the data grid columns. foreach (DbField field in this.query.Table.Fields) { this.dataGrid.Columns.Add(field.Property.Name, field.DisplayName); } // If a current result exists, intialize the data grid rows. if (this.result != null) { for (int row = 0; row < this.result.RowCount; row++) { // Add the new row, and get the row index. int index = this.dataGrid.Rows.Add(this.query.Table.GetValues(this.result[row])); // Set the row tag with the table object. this.dataGrid.Rows[index].Tag = result[row]; } } // Intialize the buttons. this.buttonRefresh.Enabled = true; this.buttonCancel.Enabled = false; this.buttonSelect.Enabled = false; this.buttonClose.Enabled = true; }
public static DbQuerySql CreateUpdateAllOn(ITable table, DbObject value, string fieldOn, object valueOn, DbObjectDatabase database, object userState = null) { // If the table is not configured, throw an exception. if (!table.IsConfigured) throw new DbException("Cannot create a database query for the table \'{0}\'. The table is not configured.".FormatWith(table.LocalName)); // If the table requires a database, check that a database is configured for this server. if (table.DefaultDatabase && (database == null)) throw new DbException("Cannot create a database query for the table \'{0}\'. The table requires a database but the server does not have a database configured.".FormatWith(table.LocalName)); // Check the table type matches the database object type. if (table.Type != value.GetType()) throw new DbException("Cannot create a database update query for table \'{0}\', because object type \'{1}\' does not match the table type \'{2}\'.".FormatWith(table.LocalName, table.Type.Name, value.GetType().Name)); // Get the table name. string tableName = DbQuerySql.GetTableName(table, database); // Get the on field. DbField field = table[fieldOn]; // Check the on field exists. if(null == field) throw new DbException("Cannot create a database query on matching field \'{0}\' for the table \'{1}\', because the field does not exist.".FormatWith(fieldOn, table.LocalName)); // Get the fields to update, excluding the on field. int fieldIndex = 0; string fields = DbQuerySql.GetFieldsSetExcluding(table, fieldOn, database, ref fieldIndex); // Create the query. DbQuerySql query = new DbQuerySql( "UPDATE {0} SET {1} WHERE {2}={{{3}}}".FormatWith( tableName, fields, field.GetDatabaseName(), fieldIndex++ ), table, userState); // Return the query. return query; }
/// <summary> /// Creates a new database command with the specified query. /// </summary> /// <param name="query">The database query.</param> /// <returns>The database command.</returns> public override DbCommand CreateCommand(DbQuerySql query) { return new DbCommandSql(this.connection, query); }
/// <summary> /// A method called when a query completed successfully. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The database result.</param> /// <param name="recordsAffected">The number of records affected.</param> protected override void OnQuerySucceeded(DbServerSql server, DbQuerySql query, DbDataObject result, int recordsAffected) { // Enable the control. this.tabControl.Enabled = true; // Raise the database operation finished event. if (this.DatabaseOperationFinished != null) this.DatabaseOperationFinished(this, EventArgs.Empty); // If the query state is not null. if (query.State != null) { // If the query state is a delegate. if (query.State is QuerySuccessAction) { // Get the call the delegate. QuerySuccessAction handler = query.State as QuerySuccessAction; handler(result, recordsAffected); } } }
/// <summary> /// Creates a new database command with the specified query. /// </summary> /// <param name="query">The database query.</param> /// <returns>The database command.</returns> public abstract DbCommand CreateCommand(DbQuerySql query);
/// <summary> /// A method called when the executiopn of the database query has failed. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="exception">The exception.</param> protected override void OnQueryFailed(DbServerSql server, DbQuerySql query, Exception exception) { // Enable the refresh button. this.buttonDatabaseRefresh.Enabled = true; }
/// <summary> /// A method called when the user cancels a current database query. /// </summary> /// <param name="query">The database query.</param> protected virtual void OnQueryCanceling(DbQuerySql query) { }
/// <summary> /// A method called when the execution of the database query succeeded and the data is object. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The result data.</param> /// <param name="recordsAffected">The number of records affected.</param> protected override void OnQuerySucceeded(DbServerSql server, DbQuerySql query, DbDataObject result, int recordsAffected) { // Add the databases to the list. for (int row = 0; row < result.RowCount; row++) { // Get the database object. DbObjectDatabase database = result[row] as DbObjectDatabase; // Add a new list view item. ListViewItem item = new ListViewItem(new string[] { database.Name, database.DatabaseId.ToString(), database.CreateDate.ToString() }); item.Tag = database; item.ImageIndex = database.Equals(this.server.Database) ? 1 : 0; this.listViewDatabases.Items.Add(item); } // Enable the refresh button. this.buttonDatabaseRefresh.Enabled = true; }
// Protected methods. /// <summary> /// A method called when the execution of the database query starts. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="command">The database command.</param> protected virtual void OnQueryStarted(DbServerSql server, DbQuerySql query, DbCommand command) { }
public static DbQuerySql CreateInsertAll(ITable table, DbObject value, DbObjectDatabase database, object userState = null) { // If the table is not configured, throw an exception. if (!table.IsConfigured) throw new DbException("Cannot create a database query for the table \'{0}\'. The table is not configured.".FormatWith(table.LocalName)); // If the table requires a database, check that a database is configured for this server. if (table.DefaultDatabase && (database == null)) throw new DbException("Cannot create a database query for the table \'{0}\'. The table requires a database but the server does not have a database configured.".FormatWith(table.LocalName)); // Check the table type matches the database object type. if (table.Type != value.GetType()) throw new DbException("Cannot create a database insert query for table \'{0}\', because object type \'{1}\' does not match the table type \'{2}\'.".FormatWith(table.LocalName, table.Type.Name, value.GetType().Name)); // Get the table name. string tableName = DbQuerySql.GetTableName(table, database); // Create the query. DbQuerySql query = new DbQuerySql( "INSERT INTO {0} ({1}) VALUES ({2})".FormatWith( DbQuerySql.GetFieldNames(table, tableName), DbQuerySql.GetFieldIndices(table, 0) ), table, userState); // Add the parameters. foreach (DbField field in table.Fields) { query.parameters.Add(value.GetValue(field.LocalName)); } // Return the query. return query; }
/// <summary> /// An event handler called when the refresh operation failed. /// </summary> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="exception">The exception.</param> private void DatabaseQueryFail(DbServerSql server, DbQuerySql query, Exception exception) { // Execute the code on the UI thread. this.Invoke(() => { this.OnQueryFailed(server, query, exception); }); }
/// <summary> /// Creates a database query that returns the field for the specified table for a given field value. /// </summary> /// <param name="table">The database table.</param> /// <param name="fieldOn">The field to check.</param> /// <param name="valueOn">The field to check value.</param> /// <param name="database">The datase, if needed.</param> /// <param name="userState">The user state.</param> /// <returns>The query.</returns> public static DbQuerySql CreateSelectAllOn(ITable table, string fieldOn, object valueOn, DbObjectDatabase database, object userState = null) { // If the table is not configured, throw an exception. if (!table.IsConfigured) throw new DbException("Cannot create a database query for the table \'{0}\'. The table is not configured.".FormatWith(table.LocalName)); // If the table requires a database, check that a database is configured for this server. if (table.DefaultDatabase && (database == null)) throw new DbException("Cannot create a database query for the table \'{0}\'. The table requires a database but the server does not have a database configured.".FormatWith(table.LocalName)); // Get the field. DbField field = table[fieldOn]; // Get the table name. string tableName = DbQuerySql.GetTableName(table, database); // Create the query. DbQuerySql query = new DbQuerySql("SELECT {0} FROM {1} WHERE {2} = {3}".FormatWith( DbQuerySql.GetFieldNames(table, tableName), tableName, field.GetDatabaseName(), "{0}"), table, userState); // Add the parameters. query.parameters.Add(valueOn); // Return the query. return query; }
/// <summary> /// Opens the modal dialog to select database objects from the specified database server and query. /// </summary> /// <typeparam name="T">The database object type.</typeparam> /// <param name="owner">The window owner.</param> /// <param name="server">The database server.</param> /// <param name="query">The database query.</param> /// <param name="result">The result to display when the dialog opens.</param> /// <returns>The dialog result.</returns> public DialogResult ShowDialog(IWin32Window owner, DbServerSql server, DbQuerySql query, DbDataObject result) { // Reset the result. this.SelectedResult = null; this.AllResults = result; // Initialize the control. this.control.Select(server, query, result); // Show the dialog. return base.ShowDialog(owner); }
/// <summary> /// Creates a database query that returns the field for the specified table for a given field value of /// a related table. /// </summary> /// <param name="table">The database table.</param> /// <param name="tableOn">The related table.</param> /// <param name="fieldOn">The related table field name.</param> /// <param name="valueOn">The related table field value.</param> /// <param name="database">The datase, if needed.</param> /// <param name="userState">The user state.</param> /// <returns>The query.</returns> public static DbQuerySql CreateSelectAllOn(ITable table, ITable tableOn, string fieldOn, object valueOn, DbObjectDatabase database, object userState = null) { // If the table is not configured, throw an exception. if (!table.IsConfigured) throw new DbException("Cannot create a database query for the table \'{0}\'. The table is not configured.".FormatWith(table.LocalName)); // If the table requires a database, check that a database is configured for this server. if (table.DefaultDatabase && (database == null)) throw new DbException("Cannot create a database query for the table \'{0}\'. The table requires a database but the server does not have a database configured.".FormatWith(table.LocalName)); // Get the relationships between the current table and the right table. List<IRelationship> relationships = new List<IRelationship>(); foreach (IRelationship relationship in table.Relationships) { if (relationship.RightTable == tableOn) { relationships.Add(relationship); } } // If there are no relationships between tables, throw an exception. if (relationships.Count == 0) throw new DbException("Cannot create a database query for table \'{0}\' based on a match in the table \'{1}\', because there is no relationship between the two tables.".FormatWith(table.LocalName, tableOn.LocalName)); // Get the on field. DbField fieldRight = tableOn[fieldOn]; // Check the on field exists. if(null == fieldRight) throw new DbException("Cannot create a database query on matching field \'{0}\' for the table \'{1}\', because the field does not exist.".FormatWith(fieldOn, tableOn.LocalName)); // Get the table names. string tableNameLeft = DbQuerySql.GetTableName(table, database); string tableNameRight = DbQuerySql.GetTableName(tableOn, database); // Create the query. DbQuerySql query = new DbQuerySql( "SELECT {0} FROM {1} INNER JOIN {2} ON ({3} = {4}){5}".FormatWith( DbQuerySql.GetFieldNames(table, tableNameLeft), tableNameLeft, tableNameRight, DbQuerySql.GetFieldName(fieldRight, tableNameRight), "{0}", DbQuerySql.GetRelationshipsMatch(relationships, database) ), table, userState); // Add the parameters. query.parameters.Add(valueOn); // Return the query. return query; }