private void dataTableColumns_ColumnChanged(
object sender, System.Data.DataColumnChangeEventArgs e)
{
/*
MessageBox.Show(
"ColumnName = " + e.Column.ColumnName +";\n" +
"ProposedValue = " + (e.ProposedValue!=null?e.ProposedValue:"<null>") + "\n" +
"RowState = " + e.Row.RowState,
"ColumnChanged");
*/
DataRow row = e.Row;
DataRowState rowState = e.Row.RowState;
MetaData.Column oldCol = null;
string oldColumnName = "";
string oldTableName = "";
string oldTableNameSpec= "";
Object obj; // work object
int index = DataRowIndex(row); // index into table
string columnName;
obj = row["Column Name"];
if (!Convert.IsDBNull(obj))
columnName = ((string)obj).Trim();
else
columnName = String.Empty;
if (columnName.Length != 0)
{
string aliasSpec;
obj = row["Alias"];
if (!Convert.IsDBNull(obj))
aliasSpec = (string)obj;
else
aliasSpec = String.Empty;
string tableSpec;
obj = row["Table"];
if (!Convert.IsDBNull(obj))
tableSpec = (string)obj;
else
tableSpec = String.Empty;
MetaData.Column newCol = new MetaData.Column();
newCol.ColumnName = columnName;
//newCol.TableName = tableSpec;
newCol.AliasName = aliasSpec;
newCol.AliasNameSpecification =
MetaData.WrapInQuotesIfEmbeddedSpaces(aliasSpec);
if (index != -1 &&
index < this.md.Columns.Count)
{
oldCol = (MetaData.Column)this.md.Columns[index];
if (oldCol != null)
{
if (oldCol.ColumnName != null)
oldColumnName = oldCol.ColumnName;
if (oldCol.TableName != null)
oldTableName = oldCol.TableName;
if (oldCol.TableNameSpecification != null)
oldTableNameSpec =
oldCol.TableNameSpecification;
}
}
int token;
StringBuilder sb = new StringBuilder(100);
if (oldCol != null)
{
token = oldCol.Token; // replacing old entry
token = this.md.RemoveToken(index);
}
else
token = this.md.TokenFROM; // adding new entry
// if inserting last col, need a preceding comma
if (index > 0 && index + 1 >= this.md.Columns.Count)
sb.Append(", "); // append leading comma
sb.Append(newCol.ToString());
// if we are not the last column then append a
// trailing comma. e.g. "mycol,"
if (index + 1 < this.md.Columns.Count)
sb.Append(","); // append trailing comma
this.md.CmdTokenList.Insert(token, sb.ToString());
}
else // columnName == String.Empty (treat as delete)
{
this.md.RemoveToken(index); // remove the column's token
}
// update the query text pane
UpdateQueryTextBox(this.md.CmdTokenList);
// rebuild the metadata
this.md = this.Connection.GetMetaData(this.textQuery.Text);
// update table property pages display
UpdateTableColumnCheckBoxList(md);
this.rebuildDataGrid = true; // rebuild the data grid later
}
private void dataTableColumns_RowDeleting(
object sender, System.Data.DataRowChangeEventArgs e)
{
DataRowAction action = e.Action;
DataRow row = e.Row;
DataRowState rowState = e.Row.RowState;
//MetaData.Column oldCol = null;
int rowIndex = DataRowIndex(row); // index into table
this.md.RemoveToken(rowIndex); // remove the column's token
// update the query text pane
UpdateQueryTextBox(this.md.CmdTokenList);
// rebuild the metadata
this.md = this.Connection.GetMetaData(this.textQuery.Text);
// update table property pages display
UpdateTableColumnCheckBoxList(md);
}
/// <summary>
/// Add table reference to end of FROM list.
/// </summary>
/// <param name="catTable"></param>
internal void AddTableToQueryText(string tableName)
{
if (this.textQuery.Text.Length == 0)
this.textQuery.Text = "SELECT ";
if (md.TokenFROM == 0)
{
this.textQuery.Text += " FROM ";
// rebuild the metadata for the new tokens
md = this.Connection.GetMetaData(this.textQuery.Text);
if (md.TokenFROM == 0) // safety check; should not happen
return;
}
int insertpoint;
if (md.Tables.Count > 0)
{
MetaData.Table lastTable =
(MetaData.Table)(md.Tables[md.Tables.Count-1]);
insertpoint = lastTable.Token + lastTable.TokenCount;
md.CmdTokenList.Insert(insertpoint, ",");
insertpoint++;
}
else
insertpoint = md.TokenFROM + 1; // insert after the FROM
md.CmdTokenList.Insert(insertpoint, tableName);
UpdateQueryTextBox(md.CmdTokenList); // rebuild Text and MetaData
this.md = this.Connection.GetMetaData(this.textQuery.Text);
// update table property pages display
UpdateTableColumnCheckBoxList(md);
// update grid display
UpdateGridColumns(md);
}
private void checkedListTableColumns_SelectedIndexChanged(
object sender, System.EventArgs e)
{
CheckedListBox checkedListTableColumns =
(CheckedListBox)sender;
int selectedIndex = checkedListTableColumns.SelectedIndex;
if (selectedIndex == -1) // just return if our recursive call
return;
// save the column name
string columnName = (string)checkedListTableColumns.SelectedItem;
checkedListTableColumns.SelectedIndex = -1; // clear the highlight
// this triggers a recursive call to us
object obj = checkedListTableColumns.DataSource;
MetaData.Table table = (MetaData.Table) checkedListTableColumns.Tag;
bool isChecked = checkedListTableColumns.GetItemChecked(selectedIndex);
if (isChecked)
{
if (this.md.TokenFROM > 0) // if there is a FROM keyword (should be)
{
System.Text.StringBuilder sb =
new System.Text.StringBuilder(100);
if (this.md.Columns.Count > 0) // do we need a comma separator
sb.Append(", ");
if (table.AliasNameSpecification != null &&
table.AliasNameSpecification.Length > 0)
{
sb.Append(table.AliasNameSpecification);
sb.Append(".");
}
else
if (table.TableNameSpecification != null &&
table.TableNameSpecification.Length > 0 &&
this.md.Tables.Count > 1) // qualify if more than one table
{
sb.Append(table.TableNameSpecification);
sb.Append(".");
}
sb.Append(
MetaData.WrapInQuotesIfEmbeddedSpaces(columnName));
this.md.CmdTokenList.Insert(md.TokenFROM, sb.ToString());
}
}
else // field name is being unchecked; search and remove it from query
{
// compare on the table names rather than comparing on object
// instances since the Table in checkedListTableColumns.Tag
// might be older that the Table in the Metadata.Columns.
string fullTableName = table.ToString(); //tablename and alias
int columnIndex = -1;
foreach (MetaData.Column column in this.md.Columns)
{
columnIndex++; // track position in list
Catalog.Column catColumn = column.CatColumn;
if (catColumn == null) // skip non-column refs
continue; // like expressions
// skip if different columns names
if (columnName != catColumn.ColumnName)
continue;
// skip if same column name but different table alias
if (fullTableName != column.Table.ToString())
continue;
// found the column that matches the unchecked item!
// remove the tokens that make up the column (and comma)
this.md.RemoveToken(column, columnIndex);
// we're done
break;
} // end loop through md.Columns
} // end else removing item from query
// update the query text pane
UpdateQueryTextBox(this.md.CmdTokenList);
// rebuild the metadata
this.md = this.Connection.GetMetaData(this.textQuery.Text);
// update grid display
UpdateGridColumns(this.md);
}
private void UpdateGridColumns(MetaData md)
{
DataGridCell dataGridCurrentCell = this.gridColumns.CurrentCell;
// don't fire Changing events while we load the dataset/datatable
DisableDataTableChangeEvents(this.dataTableColumns);
DisableGridColumnsCurrentCellChangeEvents(this.gridColumns);
this.dataTableColumns.Clear();
if (md.Columns.Count == 0) // if no columns, add a blank row
this.dataTableColumns.Rows.Add(this.dataTableColumns.NewRow());
foreach (MetaData.Column column in md.Columns)
{
MetaData.Table table = column.Table;
Catalog.Table catTable;
if (column.Table != null)
catTable = column.Table.CatTable;
else
catTable = null;
System.Data.DataRow dataRow = this.dataTableColumns.NewRow();
if (column.ColumnName != null) // column reference
dataRow["Column Name"] = column.ColumnName;
else // expression definition
dataRow["Column Name"] = column.ColumnNameSpecification;
if (column.SortDirection != 0)
if (column.SortDirection == 1)
dataRow["Sort Type"] = "ASC";
else
if (column.SortDirection ==-1)
dataRow["Sort Type"] = "DESC";
if (column.SortOrder != 0)
dataRow["Sort Order"] = column.SortOrder.ToString();
if (column.AliasName != null)
dataRow["Alias"] = column.AliasName;
if (table != null)
if (table.AliasName != null && table.AliasName.Length > 0)
dataRow["Table"] = table.AliasName;
else
dataRow["Table"] = table.TableName;
this.dataTableColumns.Rows.Add(dataRow);
} // end loop through SELECT column references
this.dataTableColumns.AcceptChanges(); // start with clean slate
// accept Changing events from now on
EnableGridColumnsCurrentCellChangeEvents(this.gridColumns);
EnableDataTableChangeEvents(this.dataTableColumns);
}
private void UpdateTableColumnCheckBoxList(MetaData md)
{
bool visible = false; // assume that there are no pages
this.SuspendLayout();
this.panelTables.SuspendLayout();
this.tabTableColumns.SuspendLayout();
this.tabTableColumns.TabPages.Clear(); // clear all of the pages
this.tabTableColumns.SelectedIndex = 0;
if (md.Tables != null && md.Tables.Count > 0)
{
foreach (MetaData.Table table in md.Tables)
{
Catalog.Table catTable = table.CatTable;
if (catTable == null) // skip ones with no cat info
continue;
CheckedListBox checkedListTableColumns =
new CheckedListBox();
Panel panelKey = new Panel();
Label labelTableColumns = new Label();
TabPage tabPage = new TabPage();
PrimaryKeyIconList primaryKeyIconList =
new PrimaryKeyIconList(checkedListTableColumns.ItemHeight);
tabPage.SuspendLayout();
// Use the form's checkedListTableColumns as a template
// when coding this checkedListTableColumns entries
// in order to take advantage of designer settings.
checkedListTableColumns.BorderStyle =
this.checkedListTableColumns.BorderStyle;
checkedListTableColumns.CheckOnClick =
this.checkedListTableColumns.CheckOnClick;
checkedListTableColumns.Dock =
this.checkedListTableColumns.Dock;
checkedListTableColumns.Location = new Point(
this.panelKey.Width,
this.labelTableColumns.Height);
checkedListTableColumns.SelectionMode =
this.checkedListTableColumns.SelectionMode;
checkedListTableColumns.Size =
new Size(this.checkedListTableColumns.Size.Width,
this.checkedListTableColumns.Size.Height);
checkedListTableColumns.TabIndex =
this.checkedListTableColumns.TabIndex;
checkedListTableColumns.Name = table.TableName;
checkedListTableColumns.SelectedIndexChanged +=
new System.EventHandler(
this.checkedListTableColumns_SelectedIndexChanged);
checkedListTableColumns.Invalidated +=
new InvalidateEventHandler(
this.checkedListTableColumns_Invalidated);
checkedListTableColumns.Tag = table;
panelKey.Tag = primaryKeyIconList;
// This code has been disabled because there is no way
// to get .NET to fire an event that the CheckedListBox
// has been scrolled and its TopIndex has been changed.
// We need this information to repaint the "key" icon
// in the right location in the PanelKey. This code
// has been left in for the day when capability has
// been added. (thoda04)
//panelKey.Paint +=
// new System.Windows.Forms.PaintEventHandler(this.panelKey_Paint);
foreach (Catalog.Column catColumn in catTable.Columns)
{
if (catColumn.ColumnName == null) // safety check
continue;
bool isChecked = false;
// find if this catalog column is in the SELECT collist
foreach(MetaData.Column column in md.Columns)
{
// if right table and right catalog column
if (ReferenceEquals(column.Table, table) &&
ReferenceEquals(column.CatColumn, catColumn))
{
isChecked = true; // matched select col w/ cat col
break;
}
} // end foreach column
checkedListTableColumns.Items.Add(
catColumn.ColumnName, isChecked);
// save indication whether column is a primary key
//primaryKeyIconList.Add(catColumn.KeySequence > 0);
} // end foreach catColumn
panelKey.BackColor = this.panelKey.BackColor;
panelKey.Dock = this.panelKey.Dock;
panelKey.Location = this.panelKey.Location;
panelKey.Size =
new Size(this.panelKey.Size.Width,
this.panelKey.Size.Height);
panelKey.TabIndex = this.panelKey.TabIndex;
labelTableColumns.Dock = this.labelTableColumns.Dock;
labelTableColumns.Size =
new Size(this.labelTableColumns.Size.Width,
this.labelTableColumns.Size.Height);
labelTableColumns.TabIndex = this.labelTableColumns.TabIndex;
labelTableColumns.TextAlign = this.labelTableColumns.TextAlign;
// fully qualified tablename above the list
labelTableColumns.Text = table.ToString();
tabPage.Controls.AddRange(
new System.Windows.Forms.Control[] {
checkedListTableColumns,
panelKey,
labelTableColumns});
tabPage.Location = this.tabPage.Location;
tabPage.Dock = this.tabPage.Dock;
//tabPage.Name = "tabPage";
tabPage.Size = new Size(
this.tabPage.Size.Width,
this.tabPage.Size.Height);
tabPage.TabIndex = 0;
tabPage.Text = table.TableName;
this.tabTableColumns.TabPages.Add(tabPage);
this.tabPage.ResumeLayout(false);
visible = true; // there is at least one page
} // end for each
} // end if (md.Tables.Count > 0)
this.tabTableColumns.Enabled = visible;
// correctly size the controls in the property tabs based
// upon the tab control size.
tabTableColumns_Resize(this.tabTableColumns, System.EventArgs.Empty);
this.tabTableColumns.ResumeLayout(false);
this.panelTables.ResumeLayout(false);
this.ResumeLayout(false);
}
// Called (eventually) by form.ShowDialog().
// The constructors for the form and its controls was called already.
private void QueryDesignerForm_Load(object sender, System.EventArgs e)
{
string commandText;
if (this.CommandText != null)
commandText = this.CommandText.Trim();
else
commandText = String.Empty;
if (commandText.Length == 0)
commandText = "SELECT FROM"; // plant the seed
this.Text = VSNETConst.shortTitle +" Query Designer";
// force the panel sizes now
panelMain_Resize(panelMain, System.EventArgs.Empty);
if (Catalog != null)
{
ContextMenu cm = new ContextMenu(); // build the context menu
EventHandler eh = new EventHandler(this.contextMenuOnClick);
cm.MenuItems.Add(constAddTable, eh); // "Add Table..."
ContextMenu = cm;
}
// format the incoming CommandText into the textQuery box
Cursor cursor = Cursor.Current; // save cursor, probably Arrow
Cursor.Current = Cursors.WaitCursor; // hourglass cursor
md = this.Connection.GetMetaData(commandText);
Cursor.Current = cursor; // restore Arrow cursor
UpdateQueryTextBox(md.CmdTokenList);
// update table property pages display
UpdateTableColumnCheckBoxList(md);
// update grid display
UpdateGridColumns(md);
}
// Leave event fired when textQuery TextBox is losing focus
private void textQuery_Leave(object sender, System.EventArgs e)
{
// if no change in the QueryText panel, just return to save time
if (textQueryEnterText.Equals(textQuery.Text))
return;
Cursor cursor = Cursor.Current; // save cursor, probably Arrow
Cursor.Current = Cursors.WaitCursor; // hourglass cursor
md = this.Connection.GetMetaData(this.textQuery.Text);
Cursor.Current = cursor; // resored Arrow cursor
// Reformat, highlight, and make the SQL statement look pretty
UpdateQueryTextBox(md.CmdTokenList);
// update table property pages display
UpdateTableColumnCheckBoxList(md);
// update grid display
UpdateGridColumns(md);
}
/// <summary>
/// Remove Token(s) that make up the Column at the zero-based index
/// from the column list specified.
/// </summary>
/// <param name="columnList">The ArrayList of MetaData.Column's.</param>
/// <param name="column">MetaData.Column describing the column.</param>
/// <param name="columnIndex">Zero-based index into list.</param>
/// <returns>Token index (zero-based) of removal point.</returns>
public int RemoveToken(
ArrayList columnList, MetaData.Column column, int columnIndex)
{
if (columnIndex < 0 ||
columnIndex >= columnList.Count ||
column == null) // just return if token not found
return 0;
// number of tokens that make up the column reference
int token = column.Token;
int tokenCount = column.TokenCount;
// if we are not the last column
// then get rid of the following comma
if (columnIndex + 1 < columnList.Count)
tokenCount++;
// if we are the last column then get rid of the leading comma
if (columnList.Count > 1 && columnIndex + 1 == columnList.Count)
{
token--;
tokenCount++;
}
// remove the tokens that make up the column reference
CmdTokenList.RemoveRange(token, tokenCount);
return token;
}
/// <summary>
/// Remove Token(s) that make up the Column at the zero-based index.
/// </summary>
/// <param name="column">MetaData.Column describing the column.</param>
/// <param name="columnIndex">Zero-based index into Columns.</param>
/// <returns>Token index (zero-based) of removal point.</returns>
public int RemoveToken(MetaData.Column column, int columnIndex)
{
if (column.SortOrder > 0) // if column is in the ORDER BY, remove
{
int i = column.SortOrder-1; // index into ColumnsOrderBy
MetaData.Column col = (MetaData.Column)ColumnsOrderBy[ i ];
RemoveToken(ColumnsOrderBy, col, i);
}
return RemoveToken(Columns, column, columnIndex);
}
private IngresConnection _connection; // = null;
/*
* The constructors are marked as internal because an application
* can't create an IngresDataReader directly. It is created only
* by executing one of the reader methods in the IngresCommand.
*/
internal IngresDataReader(
AdvanRslt resultset,
IngresConnection connection,
CommandBehavior commandBehavior,
int recordsAffected,
MetaData keyInfoMetaData)
{
if (resultset == null) // if no result set, build an empty one
{
resultset = connection.advanConnect.CreateEmptyResultSet();
}
_resultset = resultset;
_fieldCount = resultset.rsmd.getColumnCount();
_connection = connection;
_valueList = new Object[_fieldCount];
_isOpen = true;
_commandBehavior = commandBehavior;
_recordsAffected = recordsAffected;
_keyInfoMetaData = keyInfoMetaData;
if (connection != null)
connection.ActiveDataReader = this;
// only one active DataReader allowed at a time
FireInfoMessageEvent(); // call the delegates with any warning messages
}
/*
** GetSchemaTable method
**
** Remarks:
** A DataTable object is constructed. One DataRow for each column in
** the result set. For each DataRow, there is a collection of
** DataColumns ("Columnname", "ColumnSize", etc.) that describe the
** metadata of each resultset column.
**
** History:
** 27-Aug-02 (thoda04)
** Created.
*/
/// <summary>
/// Returns a DataTable that describes the resultset column metadata.
/// </summary>
private DataTable GetSchemaTable(MetaData keyInfoMetaData)
{
try
{
int fieldCount = _resultset.rsmd.getColumnCount();
if (fieldCount == 0) // if no real result set then return null
return null;
DataTable table = new DataTable("SchemaTable");
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
table.Columns.Add("ColumnName", typeof(String));
table.Columns.Add("ColumnOrdinal", typeof(System.Int32));
table.Columns.Add("ColumnSize", typeof(System.Int32));
table.Columns.Add("NumericPrecision", typeof(System.Int16));
table.Columns.Add("NumericScale", typeof(System.Int16));
table.Columns.Add("DataType", typeof(System.Type));
table.Columns.Add("ProviderType", typeof(System.Int32));
table.Columns.Add("IsLong", typeof(System.Boolean));
table.Columns.Add("AllowDBNull", typeof(System.Boolean));
table.Columns.Add("IsReadOnly", typeof(System.Boolean));
table.Columns.Add("IsRowVersion", typeof(System.Boolean));
table.Columns.Add("IsUnique", typeof(System.Boolean));
table.Columns.Add("IsKey", typeof(System.Boolean));
table.Columns.Add("IsAutoIncrement", typeof(System.Boolean));
table.Columns.Add("BaseSchemaName", typeof(String));
table.Columns.Add("BaseCatalogName", typeof(String));
table.Columns.Add("BaseTableName", typeof(String));
table.Columns.Add("BaseColumnName", typeof(String));
for (int i = 0; i < fieldCount; i++)
{
DataRow row = table.NewRow();
ProviderType providerType = _resultset.rsmd.getColumnType(i);
bool hasPrecision = ProviderTypeMgr.hasPrecision(providerType);
bool hasScale = ProviderTypeMgr.hasScale (providerType);
row["ColumnName"] = _resultset.rsmd.getColumnName(i);
row["ColumnOrdinal"] = i;
row["ColumnSize"] = _resultset.rsmd.getColumnSizeMax(i);
if (hasPrecision)
row["NumericPrecision"]= _resultset.rsmd.getPrecision(i);
else
row["NumericPrecision"]= DBNull.Value;
if (hasScale)
row["NumericScale"] = _resultset.rsmd.getScale(i);
else
row["NumericScale"] = DBNull.Value;
row["DataType"] = // .NET data type
ProviderTypeMgr.GetNETType(providerType);
row["ProviderType"] = providerType;
row["IsLong"] = ProviderTypeMgr.isLong(providerType);
row["AllowDBNull"] = _resultset.rsmd.isNullable(i);
row["IsReadOnly"] = _resultset.rsmd.isReadOnly(i);
row["IsRowVersion"] = false;
row["IsUnique"] = false;
row["IsKey"] = false;
row["IsAutoIncrement"] = _resultset.rsmd.isAutoIncrement(i);
row["BaseSchemaName"] = DBNull.Value;
row["BaseCatalogName"] = DBNull.Value;
row["BaseTableName"] = DBNull.Value;
row["BaseColumnName"] = row["ColumnName"];
// if CommandBehavior.KeyInfo || CommandBehavior.SchemaOnly
// was specified then a MetaData object was built with the info.
if (keyInfoMetaData!=null && i < keyInfoMetaData.Columns.Count)
{
MetaData.Column col = (MetaData.Column)keyInfoMetaData.Columns[i];
if (col.ColumnName != null && // if a column from SELECT * ...
col.ColumnName.Equals("*"))
{
col.ColumnName =
_resultset.rsmd.getColumnName(i);
col.ColumnNameSpecification =
MetaData.WrapInQuotesIfEmbeddedSpaces(
col.ColumnName);
col.CatColumn =
keyInfoMetaData.FindCatalogColumnForColumn(col);
}
if (col.CatColumn != null)
{
Catalog.Column catCol = col.CatColumn;
row["BaseSchemaName"] = catCol.SchemaName;
row["BaseTableName"] = catCol.TableName;
row["BaseColumnName"] = catCol.ColumnName;
row["IsKey"] = catCol.PrimaryKeySequence != 0?
true:false;
row["IsUnique"] = catCol.IsUnique;
}
}
table.Rows.Add(row);
} // end loop
return table;
}
catch( SqlEx ex ) { throw ex.createProviderException(); }
}
/// <summary>
/// Get the primary key and index schema information about the query.
/// </summary>
/// <param name="cmdText">SQL statement to be examined.</param>
/// <returns></returns>
internal MetaData GetKeyInfoMetaData(string cmdText)
{
try
{
MetaData metadata =
new MetaData(this.advanConnect, cmdText, true);
if (metadata == null || //safety check
metadata.Columns == null ||
metadata.Columns.Count == 0)
return null;
return metadata;
}
catch( SqlEx ex ) { throw ex.createProviderException(); }
}
