/// <summary>
/// Shows the form as a dialog and the specified database.
/// </summary>
/// <param name="owner">The owner window.</param>
/// <param name="database">The database.</param>
/// <param name="isSelected">Indicates if the database is selected.</param>
/// <returns>The dialog result.</returns>
public DialogResult ShowDialog(IWin32Window owner, DbObjectDatabase database, bool isSelected)
{
// If the server is null, do nothing.
if (null == database) return DialogResult.Abort;
// Set the server.
this.control.Database = database;
this.control.IsSelected = isSelected;
// Set the title.
this.Text = "{0} Database Properties".FormatWith(database.Name);
// Open the dialog.
return base.ShowDialog(owner);
}
/// <summary>
/// Loads the current server configuration from the registry.
/// </summary>
private void LoadInternalConfiguration()
{
// Load the default database.
this.database = DbObject.CreateFromRegistry<DbObjectSqlDatabase>(this.Key.Name, "Database");
}
// Private methods.
/// <summary>
/// Returns full the table name, including database and schema if required.
/// </summary>
/// <param name="table">The database table.</param>
/// <param name="database">The database.</param>
/// <returns>The table name as a string.</returns>
private static string GetTableName(ITable table, DbObjectDatabase database)
{
// Return the table name made of the database name, schema name, and table name.
return "[{0}].[{1}].[{2}]".FormatWith(
table.DefaultDatabase ? database.Name : table.Database,
table.Schema,
table.DatabaseName
);
}
/// <summary>
/// Returns the string of conditions corresponding to matching the relationships list.
/// </summary>
/// <param name="relationships">The list of relationships.</param>
/// <param name="database">The default database.</param>
/// <returns>The conditions string.</returns>
private static string GetRelationshipsMatch(IEnumerable<IRelationship> relationships, DbObjectDatabase database)
{
// Create the string builder.
StringBuilder builderConditions = new StringBuilder();
// Add a condition for each relationship.
foreach (IRelationship relationship in relationships)
{
ITable tableLeft = relationship.LeftTable;
ITable tableRight = relationship.RightTable;
DbField fieldLeft = tableLeft[relationship.LeftField];
DbField fieldRight = tableRight[relationship.RightField];
builderConditions.AppendFormat(" AND ({0}.[{1}] = {2}.[{3}])",
DbQuerySql.GetTableName(tableLeft, database),
fieldLeft.GetDatabaseName(),
DbQuerySql.GetTableName(tableRight, database),
fieldRight.GetDatabaseName()
);
}
// Returns the conditions string.
return builderConditions.ToString();
}
/// <summary>
/// Returns the string of statements that matches all the fields of a table, exclusing a specified field,
/// with a set of parameter indices, separated by a comma.
/// </summary>
/// <param name="table">The database table.</param>
/// <param name="fieldExclude">The field name to exclude.</param>
/// <param name="database">The database.</param>
/// <param name="fieldIndex">The field index.</param>
/// <returns>The string of conditions.</returns>
private static string GetFieldsSetExcluding(ITable table, string fieldExclude, DbObjectDatabase database, ref int fieldIndex)
{
// Get the table name.
string tableName = DbQuerySql.GetTableName(table, database);
// Create the string builder.
StringBuilder stringBuilder = new StringBuilder();
// Set the start index.
int startIndex = fieldIndex;
// For all the fields.
foreach (DbField field in table.Fields)
{
// If the field matches the field to exclude, ignore the field.
if (field.LocalName == fieldExclude) continue;
// Add the field match to the query.
stringBuilder.AppendFormat("{0} {1} = {{{2}}}",
fieldIndex != startIndex ? "," : string.Empty,
DbQuerySql.GetFieldName(field, tableName),
fieldIndex);
// Increment the field index.
fieldIndex++;
}
// Return the string.
return stringBuilder.ToString();
}
/// <summary>
/// Returns the string of conditions that matches the fields of a table with a set of parameter indices,
/// separated by an AND condition.
/// </summary>
/// <param name="table">The database table.</param>
/// <param name="fields">The field names.</param>
/// <param name="database">The database.</param>
/// <param name="fieldIndex">The field index.</param>
/// <returns>The string of conditions.</returns>
private static string GetFieldsMatch(ITable table, string[] fields, DbObjectDatabase database, ref int fieldIndex)
{
// Get the table name.
string tableName = DbQuerySql.GetTableName(table, database);
// Create the string builder.
StringBuilder stringBuilder = new StringBuilder();
// Set the start index.
int startIndex = fieldIndex;
// For all the fields.
foreach (string fieldName in fields)
{
// Get the on field.
DbField field = table[fieldName];
// 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(fieldName, table.LocalName));
// Add the field match to the query.
stringBuilder.AppendFormat(" {0} ({1} = {{{2}}})",
fieldIndex != startIndex ? "AND" : string.Empty,
DbQuerySql.GetFieldName(field, tableName),
fieldIndex);
// Increment the field index.
fieldIndex++;
}
// Return the string.
return stringBuilder.ToString();
}
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;
}
// Public methods.
/// <summary>
/// Selects a table at the given database server for display.
/// </summary>
/// <param name="server">The database server.</param>
/// <param name="table">The table.</param>
public void Select(DbServerSql server, ITable table)
{
// Set the parameters.
this.server = server;
this.table = table;
// Reset the results.
this.resultTables = null;
this.resultDatabases = null;
this.resultSchemas = null;
this.resultColumns = null;
this.resultTable = null;
this.resultDatabase = null;
this.resultSchema = null;
// Initialize the control.
this.labelTitle.Text = table.LocalName;
this.textBoxId.Text = table.Id.ToString();
this.textBoxNameLocal.Text = table.LocalName;
this.textBoxNameDatabase.Text = table.DatabaseName;
this.textBoxSchema.Text = table.Schema;
this.textBoxDatabase.Text = table.DefaultDatabase ? "(default)" : table.Database;
this.checkBoxDefaultDatabase.Checked = table.DefaultDatabase;
this.checkBoxReadOnly.Checked = table.IsReadOnly;
this.pictureBox.Image = table.IsConfigured ? Resources.TableSuccess_32 : Resources.TableWarning_32;
// The table fields.
this.listViewFields.Items.Clear();
foreach (DbField field in table.Fields)
{
// If the property type is nullable, replace it with the boxed type.
ListViewItem item = new ListViewItem(new string[] {
field.Property.Name,
field.HasName ? field.GetDatabaseName() : string.Empty,
field.LocalType,
field.DatabaseType,
field.IsNullable ? "Yes" : "No"
});
item.ImageKey = field.HasName ? "Field" : "FieldWarning";
item.Tag = field;
this.listViewFields.Items.Add(item);
}
// The table relationships.
this.listViewRelationships.Items.Clear();
foreach (IRelationship relationship in table.Relationships)
{
ListViewItem item = new ListViewItem(new string[] {
relationship.RightTable.LocalName,
relationship.LeftField,
relationship.RightField });
item.ImageKey = "Relationship";
item.Tag = relationship;
this.listViewRelationships.Items.Add(item);
}
// Set the enabled state.
this.buttonSelectTable.Enabled = !table.IsReadOnly;
this.buttonSelectDatabase.Enabled = !table.IsReadOnly;
this.buttonSelectField.Enabled = false;
this.checkBoxDefaultDatabase.Enabled = !table.IsReadOnly && !table.DefaultDatabase;
// Set the focus.
this.tabControl.SelectedTab = this.tabPageGeneral;
this.textBoxNameLocal.Select();
this.textBoxNameLocal.SelectionStart = 0;
this.textBoxNameLocal.SelectionLength = 0;
}
/// <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 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;
}
/// <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;
}
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;
}
// Protected methods.
/// <summary>
/// An event handler called when a new database has been set.
/// </summary>
/// <param name="oldDatabase">The old database.</param>
/// <param name="newDatabase">The new database.</param>
protected virtual void OnDatabaseSet(DbObjectDatabase oldDatabase, DbObjectDatabase newDatabase)
{
// If the database has not changed, do nothing.
if (oldDatabase == newDatabase) return;
if (newDatabase == null)
{
this.labelTitle.Text = "No database selected";
this.tabControl.Visible = false;
}
else
{
this.labelTitle.Text = newDatabase.Name;
this.textBoxName.Text = newDatabase.Name;
this.textBoxId.Text = newDatabase.DatabaseId.ToString();
this.textBoxDateCreated.Text = newDatabase.CreateDate.ToString();
this.checkBoxSelected.Enabled = false;
this.pictureBox.Image = Resources.Database_32;
this.propertyGrid.SelectedObject = newDatabase;
this.tabControl.Visible = true;
}
this.tabControl.SelectedTab = this.tabPageGeneral;
if (this.Focused)
{
this.textBoxName.Select();
this.textBoxName.SelectionStart = 0;
this.textBoxName.SelectionLength = 0;
}
}
/// <summary>
/// An event handler called when the user selects a new database.
/// </summary>
/// <param name="sender">The sender object.</param>
/// <param name="e">The event arguments.</param>
private void OnSelectDatabase(object sender, EventArgs e)
{
try
{
// Open a new database select window that selects all database tables for the given server.
if (this.formDatabaseSelect.ShowDialog(this, this.server, this.server.TableDatabase, this.resultDatabases) == DialogResult.OK)
{
// Get the result.
this.resultDatabases = this.formDatabaseSelect.AllResults;
this.resultDatabase = this.formDatabaseSelect.SelectedResult as DbObjectDatabase;
this.resultColumns = null;
// Set the name.
this.textBoxDatabase.Text = "{0} (custom)".FormatWith(this.resultDatabase.Name);
// Uncheck and enable the uses default database check box.
this.checkBoxDefaultDatabase.Checked = false;
this.checkBoxDefaultDatabase.Enabled = true;
// Raise a configuration changed event.
if (this.ConfigurationChanged != null) this.ConfigurationChanged(this, EventArgs.Empty);
// Set the changes to true.
this.changes = true;
}
}
catch (Exception exception)
{
// If an error occurs, show an error message.
MessageBox.Show(this,
"Changing the table database failed. {0}".FormatWith(exception.Message),
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
/// <summary>
/// An event handler called when the current database has changed.
/// </summary>
/// <param name="oldDatabase">The old database.</param>
/// <param name="newDatabase">The new database.</param>
protected void OnDatabaseChanged(DbObjectDatabase oldDatabase, DbObjectDatabase newDatabase)
{
// Call the event.
if (this.DatabaseChanged != null) this.DatabaseChanged(this, new DbServerDatabaseChangedEventArgs(this, oldDatabase, newDatabase));
}
/// <summary>
/// Creates a database query that returns the field for the specified table.
/// </summary>
/// <param name="table">The database table.</param>
/// <param name="nameSelect">The field to select.</param>
/// <param name="database">The datase, if needed.</param>
/// <param name="userState">The user state.</param>
/// <returns>The query.</returns>
public static DbQuerySql CreateSelectField(ITable table, string nameSelect, 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[nameSelect];
// Create and return the query.
return new DbQuerySql("SELECT {0} FROM {1}".FormatWith(
field.GetDatabaseName(),
DbQuerySql.GetTableName(table, database)), table, userState);
}
/// <summary>
/// Creates a new event arguments instance.
/// </summary>
/// <param name="oldDatabase">The old database.</param>
/// <param name="newDatabase">The new database.</param>
public DbServerDatabaseChangedEventArgs(DbServerSql server, DbObjectDatabase oldDatabase, DbObjectDatabase newDatabase)
: base(server)
{
this.OldDatabase = oldDatabase;
this.NewDatabase = newDatabase;
}
/// <summary>
/// Compares two database objects.
/// </summary>
/// <param name="obj">The object to compare.</param>
/// <returns><b>True</b> if the two objects are equal, <b>false</b> otherwise.</returns>
public bool Equals(DbObjectDatabase obj)
{
return obj != null ? (this.Name == obj.Name) && (this.DatabaseId == obj.DatabaseId) && (this.CreateDate == obj.CreateDate) : false;
}
