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