// update table tree to reflect new connection string
private void UpdateTableTree()
{
// initialize table tree
NGTreeNode rootNode = new NGTreeNodeWithImageIndex();
var ndTables = new NGTreeNodeWithImageIndex {
Text = Current.ResourceService.GetString("Gui.DataConnection.Tables"), ImageIndex = 0, SelectedImageIndex = 0
};
var ndViews = new NGTreeNodeWithImageIndex {
Text = Current.ResourceService.GetString("Gui.DataConnection.Views"), ImageIndex = 1, SelectedImageIndex = 1
};
// populate using current schema
if (Schema != null)
{
// populate the tree
foreach (System.Data.DataTable dt in Schema.Tables)
{
// create new node, save table in tag property
var node = new NGTreeNodeWithImageIndex {
Text = dt.TableName
};
node.Tag = dt;
// add new node to appropriate parent
switch (OleDbSchema.GetTableType(dt))
{
case TableType.Table:
ndTables.Nodes.Add(node);
node.ImageIndex = node.SelectedImageIndex = 0;
AddDataColumns(node, dt);
break;
case TableType.View:
ndViews.Nodes.Add(node);
node.ImageIndex = node.SelectedImageIndex = 1;
AddDataColumns(node, dt);
break;
}
}
// add non-empty nodes to tree
foreach (NGTreeNode nd in new NGTreeNode[] { ndTables, ndViews })
{
if (nd.Nodes.Count > 0)
{
nd.Text = string.Format("{0} ({1})", nd.Text, nd.Nodes.Count);
rootNode.Nodes.Add(nd);
}
}
// expand tables node
ndTables.IsExpanded = true;
_treeTableNodes = rootNode;
if (null != _view)
{
_view.SetTableTreeDataSource(_treeTableNodes);
}
}
}
// creates a control of appropriate type for the parameter.
// note: parameter values are stored as invariant strings.
private Control GetControl(OleDbParameter p)
{
var value = p.Value as string;
var type = OleDbSchema.GetType(p.OleDbType);
if (OleDbSchema.IsNumeric(type))
{
var num = new Altaxo.Gui.Common.DecimalUpDown
{
Minimum = decimal.MinValue,
Maximum = decimal.MaxValue
};
decimal dec = 0;
if (!string.IsNullOrEmpty(value))
{
decimal.TryParse(value, _numberStyle, _invariant, out dec);
}
num.Value = dec;
return(num);
}
if (type == typeof(DateTime))
{
var dtp = new DatePicker
{
SelectedDateFormat = p.OleDbType == OleDbType.Filetime
? DatePickerFormat.Long
: DatePickerFormat.Short
};
DateTime dt = DateTime.Now;
if (!string.IsNullOrEmpty(value))
{
DateTime.TryParse(value, _invariant, _dateStyle, out dt);
}
dtp.SelectedDate = dt;
return(dtp);
}
if (type == typeof(bool))
{
var chk = new CheckBox();
bool b = false;
if (!string.IsNullOrEmpty(value))
{
bool.TryParse(value as string, out b);
}
chk.IsChecked = b;
return(chk);
}
// default: textbox
var tb = new TextBox
{
Text = value
};
return(tb);
}
private void EhViewResults()
{
// make sure we have a select statement
var sql = _selectionStatement;
if (string.IsNullOrEmpty(sql))
{
return;
}
// create table to load with data and display
var dt = new System.Data.DataTable("Query");
// get table/view name
var selNode = _treeRootNode.AnyBetweenHereAndLeaves(x => x.IsSelected);
var table = selNode == null ? null : selNode.Tag as System.Data.DataTable;
dt.TableName = table.TableName;
// get view parameters if necessary
var parms = OleDbSchema.GetTableParameters(table);
if (parms != null && parms.Count > 0)
{
var ctrl = new ParametersController(parms);
if (!Current.Gui.ShowDialog(ctrl, "Parameter", false))
{
return;
}
}
// get data
try
{
using (var da = new System.Data.OleDb.OleDbDataAdapter(_selectionStatement, ConnectionString.ConnectionStringWithTemporaryCredentials))
{
// get data
da.Fill(0, MAX_PREVIEW_RECORDS, dt);
// show the data
var ctrl = new DataPreviewController(dt);
string title = string.Format("{0} ({1:n0} records)", dt.TableName, dt.Rows.Count);
Current.Gui.ShowDialog(ctrl, title, false);
}
}
catch (Exception x)
{
Current.Gui.ErrorMessageBox(string.Format("Failed to retrieve data:\r\n{0}", x.Message));
}
}
private void EhSelectedSchemaNodeChanged()
{
_selectionStatement = string.Empty;
var selNode = _treeRootNode.AnyBetweenHereAndLeaves(x => x.IsSelected);
if (null == selNode)
{
return;
}
if (selNode.Tag is System.Data.DataTable)
{
_selectionStatement = OleDbSchema.GetSelectStatement(selNode.Tag as System.Data.DataTable);
}
//UpdateUI();
}
/// <summary>
/// 创建一个数据库架构实例
/// </summary>
/// <param name="db">数据提供程序实例</param>
/// <returns></returns>
public static DbSchema Create(Database db)
{
DbSchema schema = null;
switch (db.DatabaseType)
{
case DatabaseType.SqlServer:
schema = new SqlServerSchema(db);
break;
case DatabaseType.Oracle:
schema = new OracleSchema(db);
break;
case DatabaseType.MySql:
schema = new MySqlSchema(db);
break;
case DatabaseType.DB2:
schema = new DB2Schema(db);
break;
case DatabaseType.SQLite:
schema = new SQLiteSchema(db);
break;
case DatabaseType.OleDb:
schema = new OleDbSchema(db);
break;
default:
break;
}
return(schema);
}
public ArbitrarySqlQueryController()
{
_schema = new OleDbSchema();
}
public ArbitrarySqlQueryController()
{
_schema = new OleDbSchema();
}
public EntireTableQueryController()
{
_schema = new OleDbSchema();
_treeRootNode = new NGTreeNode();
}
private void UpdateTableTree()
{
// initialize table tree
var nodes = _treeRootNode.Nodes;
nodes.Clear();
var ndTables = new NGTreeNodeWithImageIndex()
{
Text = Current.ResourceService.GetString("Gui.DataConnection.Tables"), ImageIndex = 0, SelectedImageIndex = 0
};
var ndViews = new NGTreeNodeWithImageIndex()
{
Text = Current.ResourceService.GetString("Gui.DataConnection.Views"), ImageIndex = 1, SelectedImageIndex = 1
};
var ndProcs = new NGTreeNodeWithImageIndex()
{
Text = Current.ResourceService.GetString("Gui.DataConnection.StoredProcedures"), ImageIndex = 2, SelectedImageIndex = 2
};
// populate using current schema
if (_schema != null)
{
// populate the tree
foreach (System.Data.DataTable dt in _schema.Tables)
{
// create new node, save table in tag property
var node = new NGTreeNodeWithImageIndex()
{
Text = dt.TableName
};
node.Tag = dt;
if (IsTableNameIdentical(dt.TableName, TableName))
{
node.IsExpanded = true;
node.IsSelected = true;
}
// add new node to appropriate parent
switch (OleDbSchema.GetTableType(dt))
{
case TableType.Table:
ndTables.Nodes.Add(node);
node.ImageIndex = node.SelectedImageIndex = 0;
break;
case TableType.View:
ndViews.Nodes.Add(node);
node.ImageIndex = node.SelectedImageIndex = 1;
break;
case TableType.Procedure:
ndProcs.Nodes.Add(node);
node.ImageIndex = node.SelectedImageIndex = 2;
break;
}
}
// add non-empty nodes to tree
foreach (NGTreeNode nd in new NGTreeNode[] { ndTables, ndViews, ndProcs })
{
if (nd.Nodes.Count > 0)
{
nd.Text = string.Format("{0} ({1})", nd.Text, nd.Nodes.Count);
nodes.Add(nd);
}
}
// expand tables node
ndTables.IsExpanded = true;
// done
if (null != _view)
{
_view.SetTreeSource(_treeRootNode);
}
}
}
public EntireTableQueryController()
{
_schema = new OleDbSchema();
_treeRootNode = new NGTreeNode();
}