//----------------------------------------------------------------------------
// DataSourcePicker.cs
//
// DataSource picker control for use with C1ReportDesigner application.
// Displays a list of connection strings, allows the user to select or
// build a new one, and displays the data sources available for each 
// connection (Tables, Views, Stored Procs).
//
// This is a part of the C1ReportDesigner package.
// Copyright (C) ComponentOne LLC
// All rights reserved.
//
// This source code is intended as a supplement to the C1ReportDesigner 
// control and related documentation provided with the product.
//
// See these sources for detailed information regarding the
// C1ReportDesigner product.
//----------------------------------------------------------------------------
using System;
using System.Xml;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Drawing.Text;
using System.Drawing.Imaging;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using Microsoft.Win32;
using C1.C1Report.Util;

namespace ReportDesignerApp.Util
{
	using SqlBuilder;
    using C1.C1Report;

	/// <summary>
	/// Summary description for DataSourcePicker.
	/// </summary>
	public class DataSourcePicker : UserControl //Control
	{
		// ** constants
		private const int MRU_MAX = 10;
		private const int CTL_SIZEX = 390;
		private const int CTL_SIZEY = 270;
		private const string PROC_DEF = "PROCEDURE_DEFINITION";
        private const string c_RecordsetsNodeCaption = "Recordsets";

		// ** fields
        private string _conn;
        private DataTable _dtParms;
		private ImageList _img;
        private Hashtable _htSchema;

		private IContainer components;
        private System.Windows.Forms.ToolTip _ttip;
        private System.Windows.Forms.TabControl _tab;
        private System.Windows.Forms.TabPage tbConnections;
        private System.Windows.Forms.TabPage tbObjects;
        private System.Windows.Forms.Label _label;
        private System.Windows.Forms.RadioButton _btnDataSources;
        private System.Windows.Forms.ComboBox _cmbConn;
        private System.Windows.Forms.Button _btnConn;
        private System.Windows.Forms.RadioButton _btnSQL;
        private System.Windows.Forms.Button _btnXSD;
        private System.Windows.Forms.TreeView _treeObjects;
		private System.Windows.Forms.Panel _panelRS;
		private System.Windows.Forms.Panel _panelConn;
		private System.Windows.Forms.TextBox _txtSql;
		private System.Windows.Forms.Button _btnWizard;
        private System.Windows.Forms.CheckBox _chkWrap;
        private Panel panel1;
        private TreeView _treeDataSources;
        private Button btnProperties;

        private static StringFormat _fmt = null;

        #region Constructors
        // ** ctors
		static DataSourcePicker()
		{
			_fmt = new StringFormat(StringFormat.GenericTypographic);
			_fmt.Trimming = StringTrimming.EllipsisPath;
		}

		public DataSourcePicker()
		{
			InitializeComponent();
			LoadMRU();
        }
        #endregion

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public bool Wrap
        {
            get { return _chkWrap.Checked; }
            set { _chkWrap.Checked = value; }
        }

        [DefaultValue("")]
        public string ConnectionString
		{
            get 
            {
                if (_tab.SelectedTab == tbObjects)
                {
                    TreeNode nd = _treeObjects.SelectedNode;
                    if (nd != null && nd.Tag is DataTable)
                        return nd.Parent.Text;
                }
                return (_conn != null)? _conn: string.Empty; 
            }
			set
			{
				// no nulls
				if (value == null) value = string.Empty;
				if (_conn == null) _conn = string.Empty;

				// no work?
				if (_conn == value) return;

                // save new value
                _conn = value;

                // look for DataSet in Data Objects schema list
                if (_htSchema != null && value != null && _htSchema.Contains(value))
                {
                    tbObjects.Select();
                    return;
                }

                // not there, show connection string page
                tbConnections.Select();

                // set up connection string combo list
                _cmbConn.Text = value;
                if (_conn == null || _conn.Length == 0) return;

                // get table list for this connection
                //_txtSql.Text = string.Empty; << don't clear this <<B178>>
                _treeDataSources.Nodes.Clear();
				Cursor = Cursors.WaitCursor;
				bool connOK = GetTableList(_conn);
				Cursor = Cursors.Default;

				// manage MRU list
				IList mru = (IList)_cmbConn.Items;
				if (connOK)
				{
					// make sure this one is on top
					if (mru.Contains(_conn))
					{
						if (mru.IndexOf(_conn) > 0 && _cmbConn.DroppedDown == false)
						{
							mru.Remove(_conn);
							mru.Insert(0, _conn);
							SaveMRU();
							_cmbConn.SelectedIndex = 0;
						}
					}
					else
					{
						mru.Insert(0, _conn);
						SaveMRU();
					}
				}
				else
				{
					// remove bad connections from the list
					if (mru.Contains(_conn))
					{
						mru.Remove(_conn);
						SaveMRU();
					}
				}
			}
		}

        [DefaultValue("")]
        public string RecordSource
		{
            get 
            {
                // return DataTable
                TreeNode nd;
                if (_tab.SelectedTab == tbObjects)
                {
                    nd = _treeObjects.SelectedNode;
                    if (nd != null && nd.Tag is DataTable)
                        return nd.Text;
                }
                
                // return SQL test
				if (_btnSQL.Checked)
				{
					return _txtSql.Text;
				}

                // return table/view or sql statement
                nd = _treeDataSources.SelectedNode;
                if (nd != null)
                {
                    IC1ReportExternalRecordset recordset = nd.Tag as IC1ReportExternalRecordset;
                    if (recordset != null)
                        return ExternalRecordset.GetRecordSourceString(recordset);

                    // start with simple table/view name
                    string rs = nd.Text;

                    // handle stored proc parameters
                    if (_dtParms != null)
                        rs = BuildParameters(rs, _dtParms);

                    // return table/view name
                    return rs;
                }

                // no dice
                return null;
            }
			set
			{
                // select item in object tree
                TreeNode nd = FindNode(_conn, _treeObjects.Nodes);
                if (nd != null)
                {
                    // find table
                    nd = FindNode(value, nd.Nodes);
                    if (nd != null)
                    {
                        _treeObjects.SelectedNode = nd;
                        _treeObjects.Focus();
                        nd.EnsureVisible();
                        return;
                    }
                }
                
                // check for external recordset
                if (_treeDataSources.Nodes.Count > 0 && _treeDataSources.Nodes[0].Text == c_RecordsetsNodeCaption)
                {
                    nd = ExternalRecordset.FindNode(_treeDataSources.Nodes[0].Nodes, value);
                    if (nd != null)
                    {
                        _btnDataSources.Checked = true;
                        _treeDataSources.SelectedNode = nd;
                        _treeDataSources.Focus();
                        nd.EnsureVisible();
                        return;
                    }
                }

                // select item in table tree
				nd = FindNode(value, _treeDataSources.Nodes);
				if (nd != null)
				{
					_btnDataSources.Checked = true;
					_treeDataSources.SelectedNode = nd;
					_treeDataSources.Focus();
                    nd.EnsureVisible();
                    return;
				}

				// set SQL string
				_btnSQL.Checked = true;
				_txtSql.Text = value;
            }
		}

        [DefaultValue(null)]
        public Hashtable DataSchema
        {
            get { return _htSchema; }
            set 
            {
                // save object
                _htSchema = value;
                _treeObjects.Nodes.Clear();
                if (_htSchema == null) return;

                // populate tree
                foreach (DataSet ds in _htSchema.Values)
                {
                    TreeNode ndDataSet = new TreeNode(ds.DataSetName, 4, 4);
                    _treeObjects.Nodes.Add(ndDataSet);
                    foreach (DataTable dt in ds.Tables)
                    {
                        TreeNode ndTable = new TreeNode(dt.TableName, -1, -1);
                        ndTable.Tag = dt;
                        ndDataSet.Nodes.Add(ndTable);
                    }
                }
                _treeObjects.Invalidate();
            }
        }
        public DataTable Recordset
        {
            get
            {
                TreeNode nd = _treeObjects.SelectedNode;
                return (nd != null)? nd.Tag as DataTable: null;
            }
        }

		// ** utilities

		private void InitializeComponent()
		{
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(DataSourcePicker));
            this._img = new System.Windows.Forms.ImageList(this.components);
            this._ttip = new System.Windows.Forms.ToolTip(this.components);
            this._btnConn = new System.Windows.Forms.Button();
            this._btnXSD = new System.Windows.Forms.Button();
            this._btnWizard = new System.Windows.Forms.Button();
            this._tab = new System.Windows.Forms.TabControl();
            this.tbObjects = new System.Windows.Forms.TabPage();
            this._treeObjects = new System.Windows.Forms.TreeView();
            this.tbConnections = new System.Windows.Forms.TabPage();
            this.panel1 = new System.Windows.Forms.Panel();
            this.btnProperties = new System.Windows.Forms.Button();
            this._treeDataSources = new System.Windows.Forms.TreeView();
            this._txtSql = new System.Windows.Forms.TextBox();
            this._panelRS = new System.Windows.Forms.Panel();
            this._chkWrap = new System.Windows.Forms.CheckBox();
            this._btnSQL = new System.Windows.Forms.RadioButton();
            this._btnDataSources = new System.Windows.Forms.RadioButton();
            this._panelConn = new System.Windows.Forms.Panel();
            this._cmbConn = new System.Windows.Forms.ComboBox();
            this._label = new System.Windows.Forms.Label();
            this._tab.SuspendLayout();
            this.tbObjects.SuspendLayout();
            this.tbConnections.SuspendLayout();
            this.panel1.SuspendLayout();
            this._panelRS.SuspendLayout();
            this._panelConn.SuspendLayout();
            this.SuspendLayout();
            // 
            // _img
            // 
            this._img.ImageStream = ((System.Windows.Forms.ImageListStreamer)(resources.GetObject("_img.ImageStream")));
            this._img.TransparentColor = System.Drawing.Color.Red;
            this._img.Images.SetKeyName(0, "");
            this._img.Images.SetKeyName(1, "");
            this._img.Images.SetKeyName(2, "");
            this._img.Images.SetKeyName(3, "");
            this._img.Images.SetKeyName(4, "");
            this._img.Images.SetKeyName(5, "");
            // 
            // _btnConn
            // 
            this._btnConn.Dock = System.Windows.Forms.DockStyle.Right;
            this._btnConn.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
            this._btnConn.ImageList = this._img;
            this._btnConn.Location = new System.Drawing.Point(304, 0);
            this._btnConn.Name = "_btnConn";
            this._btnConn.Size = new System.Drawing.Size(26, 24);
            this._btnConn.TabIndex = 2;
            this._btnConn.Text = "...";
            this._ttip.SetToolTip(this._btnConn, "Build connection string");
            this._btnConn.Click += new System.EventHandler(this._btnConn_Click);
            // 
            // _btnXSD
            // 
            this._btnXSD.Dock = System.Windows.Forms.DockStyle.Right;
            this._btnXSD.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
            this._btnXSD.ImageIndex = 4;
            this._btnXSD.ImageList = this._img;
            this._btnXSD.Location = new System.Drawing.Point(330, 0);
            this._btnXSD.Name = "_btnXSD";
            this._btnXSD.Size = new System.Drawing.Size(26, 24);
            this._btnXSD.TabIndex = 3;
            this._ttip.SetToolTip(this._btnXSD, "Select Xsd data file");
            this._btnXSD.Click += new System.EventHandler(this._btnXSD_Click);
            // 
            // _btnWizard
            // 
            this._btnWizard.Dock = System.Windows.Forms.DockStyle.Right;
            this._btnWizard.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
            this._btnWizard.ImageIndex = 5;
            this._btnWizard.ImageList = this._img;
            this._btnWizard.Location = new System.Drawing.Point(356, 0);
            this._btnWizard.Name = "_btnWizard";
            this._btnWizard.Size = new System.Drawing.Size(24, 24);
            this._btnWizard.TabIndex = 4;
            this._ttip.SetToolTip(this._btnWizard, "Build Sql Statement");
            this._btnWizard.Click += new System.EventHandler(this._btnWizard_Click);
            // 
            // _tab
            // 
            this._tab.Controls.Add(this.tbObjects);
            this._tab.Controls.Add(this.tbConnections);
            this._tab.Dock = System.Windows.Forms.DockStyle.Fill;
            this._tab.Location = new System.Drawing.Point(0, 0);
            this._tab.Name = "_tab";
            this._tab.SelectedIndex = 0;
            this._tab.Size = new System.Drawing.Size(400, 266);
            this._tab.TabIndex = 0;
            this._tab.TabStop = false;
            // 
            // tbObjects
            // 
            this.tbObjects.Controls.Add(this._treeObjects);
            this.tbObjects.ImageIndex = 0;
            this.tbObjects.Location = new System.Drawing.Point(4, 22);
            this.tbObjects.Name = "tbObjects";
            this.tbObjects.Padding = new System.Windows.Forms.Padding(6);
            this.tbObjects.Size = new System.Drawing.Size(392, 240);
            this.tbObjects.TabIndex = 1;
            this.tbObjects.Text = "Data Objects";
            // 
            // _treeObjects
            // 
            this._treeObjects.Dock = System.Windows.Forms.DockStyle.Fill;
            this._treeObjects.FullRowSelect = true;
            this._treeObjects.HideSelection = false;
            this._treeObjects.ImageIndex = 0;
            this._treeObjects.ImageList = this._img;
            this._treeObjects.Location = new System.Drawing.Point(6, 6);
            this._treeObjects.Name = "_treeObjects";
            this._treeObjects.SelectedImageIndex = 0;
            this._treeObjects.Size = new System.Drawing.Size(380, 228);
            this._treeObjects.TabIndex = 15;
            this._treeObjects.DoubleClick += new System.EventHandler(this._tree_DoubleClick);
            // 
            // tbConnections
            // 
            this.tbConnections.BackColor = System.Drawing.SystemColors.Control;
            this.tbConnections.Controls.Add(this.panel1);
            this.tbConnections.Controls.Add(this._txtSql);
            this.tbConnections.Controls.Add(this._panelRS);
            this.tbConnections.Controls.Add(this._panelConn);
            this.tbConnections.Controls.Add(this._label);
            this.tbConnections.ImageIndex = 3;
            this.tbConnections.Location = new System.Drawing.Point(4, 22);
            this.tbConnections.Name = "tbConnections";
            this.tbConnections.Padding = new System.Windows.Forms.Padding(6);
            this.tbConnections.Size = new System.Drawing.Size(392, 240);
            this.tbConnections.TabIndex = 0;
            this.tbConnections.Text = "Connections";
            // 
            // panel1
            // 
            this.panel1.Controls.Add(this.btnProperties);
            this.panel1.Controls.Add(this._treeDataSources);
            this.panel1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.panel1.Location = new System.Drawing.Point(6, 74);
            this.panel1.Name = "panel1";
            this.panel1.Size = new System.Drawing.Size(380, 160);
            this.panel1.TabIndex = 9;
            // 
            // btnProperties
            // 
            this.btnProperties.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
            this.btnProperties.Location = new System.Drawing.Point(301, 128);
            this.btnProperties.Name = "btnProperties";
            this.btnProperties.Size = new System.Drawing.Size(75, 23);
            this.btnProperties.TabIndex = 10;
            this.btnProperties.Text = "Properties...";
            this.btnProperties.UseVisualStyleBackColor = true;
            this.btnProperties.Click += new System.EventHandler(this.btnProperties_Click);
            // 
            // _treeDataSources
            // 
            this._treeDataSources.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
                        | System.Windows.Forms.AnchorStyles.Left)
                        | System.Windows.Forms.AnchorStyles.Right)));
            this._treeDataSources.FullRowSelect = true;
            this._treeDataSources.HideSelection = false;
            this._treeDataSources.ImageIndex = 0;
            this._treeDataSources.ImageList = this._img;
            this._treeDataSources.Location = new System.Drawing.Point(3, 3);
            this._treeDataSources.Name = "_treeDataSources";
            this._treeDataSources.SelectedImageIndex = 0;
            this._treeDataSources.Size = new System.Drawing.Size(374, 121);
            this._treeDataSources.TabIndex = 7;
            this._treeDataSources.DoubleClick += new System.EventHandler(this._tree_DoubleClick);
            this._treeDataSources.AfterSelect += new System.Windows.Forms.TreeViewEventHandler(this._treeDataSources_AfterSelect);
            // 
            // _txtSql
            // 
            this._txtSql.AcceptsReturn = true;
            this._txtSql.Dock = System.Windows.Forms.DockStyle.Fill;
            this._txtSql.Location = new System.Drawing.Point(6, 74);
            this._txtSql.MaxLength = 2147483647;
            this._txtSql.Multiline = true;
            this._txtSql.Name = "_txtSql";
            this._txtSql.ScrollBars = System.Windows.Forms.ScrollBars.Both;
            this._txtSql.Size = new System.Drawing.Size(380, 160);
            this._txtSql.TabIndex = 8;
            this._txtSql.Visible = false;
            this._txtSql.WordWrap = false;
            // 
            // _panelRS
            // 
            this._panelRS.Controls.Add(this._chkWrap);
            this._panelRS.Controls.Add(this._btnSQL);
            this._panelRS.Controls.Add(this._btnDataSources);
            this._panelRS.Dock = System.Windows.Forms.DockStyle.Top;
            this._panelRS.Location = new System.Drawing.Point(6, 48);
            this._panelRS.Name = "_panelRS";
            this._panelRS.Size = new System.Drawing.Size(380, 26);
            this._panelRS.TabIndex = 1;
            // 
            // _chkWrap
            // 
            this._chkWrap.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
            this._chkWrap.AutoSize = true;
            this._chkWrap.Location = new System.Drawing.Point(295, 6);
            this._chkWrap.Name = "_chkWrap";
            this._chkWrap.Size = new System.Drawing.Size(81, 17);
            this._chkWrap.TabIndex = 7;
            this._chkWrap.Text = "Word Wrap";
            this._chkWrap.CheckedChanged += new System.EventHandler(this._chkWrap_CheckedChanged);
            // 
            // _btnSQL
            // 
            this._btnSQL.AutoSize = true;
            this._btnSQL.FlatStyle = System.Windows.Forms.FlatStyle.System;
            this._btnSQL.Location = new System.Drawing.Point(104, 6);
            this._btnSQL.Name = "_btnSQL";
            this._btnSQL.Size = new System.Drawing.Size(97, 18);
            this._btnSQL.TabIndex = 6;
            this._btnSQL.TabStop = true;
            this._btnSQL.Text = "&Sql Statement";
            this._btnSQL.CheckedChanged += new System.EventHandler(this._btnTables_CheckedChanged);
            // 
            // _btnDataSources
            // 
            this._btnDataSources.AutoSize = true;
            this._btnDataSources.Checked = true;
            this._btnDataSources.FlatStyle = System.Windows.Forms.FlatStyle.System;
            this._btnDataSources.Location = new System.Drawing.Point(0, 6);
            this._btnDataSources.Name = "_btnDataSources";
            this._btnDataSources.Size = new System.Drawing.Size(94, 18);
            this._btnDataSources.TabIndex = 5;
            this._btnDataSources.TabStop = true;
            this._btnDataSources.Text = "&Data sources";
            // 
            // _panelConn
            // 
            this._panelConn.Controls.Add(this._cmbConn);
            this._panelConn.Controls.Add(this._btnConn);
            this._panelConn.Controls.Add(this._btnXSD);
            this._panelConn.Controls.Add(this._btnWizard);
            this._panelConn.Dock = System.Windows.Forms.DockStyle.Top;
            this._panelConn.Location = new System.Drawing.Point(6, 24);
            this._panelConn.Name = "_panelConn";
            this._panelConn.Size = new System.Drawing.Size(380, 24);
            this._panelConn.TabIndex = 1;
            // 
            // _cmbConn
            // 
            this._cmbConn.Dock = System.Windows.Forms.DockStyle.Fill;
            this._cmbConn.DrawMode = System.Windows.Forms.DrawMode.OwnerDrawFixed;
            this._cmbConn.DropDownWidth = 420;
            this._cmbConn.Location = new System.Drawing.Point(0, 0);
            this._cmbConn.Name = "_cmbConn";
            this._cmbConn.Size = new System.Drawing.Size(304, 21);
            this._cmbConn.TabIndex = 1;
            this._cmbConn.Validating += new System.ComponentModel.CancelEventHandler(this._cmbConn_Validating);
            this._cmbConn.DrawItem += new System.Windows.Forms.DrawItemEventHandler(this._cmbConn_DrawItem);
            this._cmbConn.SelectedValueChanged += new System.EventHandler(this._cmbConn_SelectedValueChanged);
            // 
            // _label
            // 
            this._label.Dock = System.Windows.Forms.DockStyle.Top;
            this._label.FlatStyle = System.Windows.Forms.FlatStyle.System;
            this._label.Location = new System.Drawing.Point(6, 6);
            this._label.Name = "_label";
            this._label.Size = new System.Drawing.Size(380, 18);
            this._label.TabIndex = 0;
            this._label.Text = "&Connection String";
            this._label.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
            // 
            // DataSourcePicker
            // 
            this.Controls.Add(this._tab);
            this.Name = "DataSourcePicker";
            this.Size = new System.Drawing.Size(400, 266);
            this._tab.ResumeLayout(false);
            this.tbObjects.ResumeLayout(false);
            this.tbConnections.ResumeLayout(false);
            this.tbConnections.PerformLayout();
            this.panel1.ResumeLayout(false);
            this._panelRS.ResumeLayout(false);
            this._panelRS.PerformLayout();
            this._panelConn.ResumeLayout(false);
            this.ResumeLayout(false);

		}

		// prompt user for a connection string
		private string PromptConnectionString(string initial)
		{
			bool ok = false;
			ADODB.Connection conn = null;
			MSDASC.DataLinksClass dlinks = null;
			try
			{
				Cursor = Cursors.WaitCursor;

				// create objects we'll need
				dlinks = new MSDASC.DataLinksClass();
				conn = new ADODB.ConnectionClass();

				// sanity <<B154>>
				if (dlinks == null || conn == null)
					throw new ApplicationException(@"Failed to create DataLinks.\r\n" +
					@"Please check that oledb32.dll is properly installed and registered." +
					@"(the usual location is \Program Files\Common Files\System\Ole DB\oledb32.dll).");

				// initialize object
				if (initial != null)
					conn.ConnectionString = initial;

				// show connection picker dialog
				object obj = conn;
				dlinks.hWnd = (int)Handle; // << make it modal
				ok = dlinks.PromptEdit(ref obj);
			}
			catch (Exception x)
			{
				Warning("Cannot build connection string because:\r\n" + x.Message);
			}
			finally
			{
				Cursor = Cursors.Default;
			}

			// return what we got
			return (ok)? conn.ConnectionString: initial;
		}

		// get a list of tables available for a given conn string
		private bool GetTableList(string connString)
		{
			// assume all will work
			bool ok = true;

			// translate ODBC connection strings into Access or SqlServer <<B153>>
			connString = TranslateConnectionString(connString);

			// initialize table tree
			TreeNodeCollection nodes = _treeDataSources.Nodes;
			nodes.Clear();
			TreeNode ndTables = new TreeNode("Tables", 0, 0);
			TreeNode ndViews = new TreeNode("Views", 1, 1);
			TreeNode ndProcs = new TreeNode("Stored Procedures", 2, 2);
            TreeNode ndExternalRecordsets = new TreeNode(c_RecordsetsNodeCaption, 1, 1);
			TreeNode nd;

            //
            Assembly asmb = ExternalRecordset.GetExternalAssembly(connString);
            if (asmb != null)
            {
                try
                {
                    IC1ReportExternalRecordset[] recordsets = ExternalRecordset.GetRecordsets(asmb);
                    if (recordsets == null || recordsets.Length <= 0)
                    {
                        Warning("The selected assembly has no external recordsets.");
                        return false;
                    }
                    nodes.Add(ndExternalRecordsets);
                    foreach (IC1ReportExternalRecordset r in recordsets)
                    {
                        nd = new TreeNode(r.Caption, -1, -1);
                        nd.Tag = r;
                        ndExternalRecordsets.Nodes.Add(nd);
                    }
                    _treeDataSources.Invalidate();


                    // no sql source here
                    _btnDataSources.Checked = true;
                    _btnSQL.Enabled = false;

                    // done
                    return true;
                }
                catch (Exception e)
                {
                    Warning(string.Format("Cannot get list of external recordsets:\r\n{0}", e.Message));
                    return false;
                }
            }

            // special handling for xml data sources <<B132>>
            if (connString.IndexOf(';') < 0 && File.Exists(connString))
            {
				// try loading DataSet from xsd file
                try 
                {
                    // get schema
                    DataSet ds = C1.C1Preview.DataBinding.DatabindingUtils.ReadXmlSchema(connString);

                    // get all tables
                    foreach (DataTable dt in ds.Tables)
                    {
                        nd = new TreeNode(dt.TableName, -1, -1);
                        if (!nodes.Contains(ndTables))
                            nodes.Add(ndTables);
                        ndTables.Nodes.Add(nd);
                    }
                    _treeDataSources.Invalidate();

                    // no sql source here
                    _btnDataSources.Checked = true;
                    _btnSQL.Enabled = false;

                    // done
                    return ok;
                }
                catch {}
            }

            // oledb sources allow SQL
            _btnSQL.Enabled = true;
            
            // parameters for stored procedures
			_dtParms = null;

			// get schema
			OleDbConnection conn = null;
			try
			{
				// create connection
				conn = new OleDbConnection(connString);
				conn.Open();

				// get available record sources
				DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
				foreach (DataRow dr in dt.Rows)
				{
					// check the table type
					string type = dr["TABLE_TYPE"].ToString().ToUpper();
					if (type != "TABLE" && type != "VIEW" && type != "LINK")
						continue;

					// get the table name
					string name = dr["TABLE_NAME"].ToString();
                    string schema = dr["TABLE_SCHEMA"].ToString();
                    if (!string.IsNullOrEmpty(schema))
                        name = schema + "." + name;

					// add this one to the tree
					nd = new TreeNode(name, -1, -1);
					nd.Tag = dr;
					if (type == "VIEW")
					{
						if (!nodes.Contains(ndViews))
							nodes.Add(ndViews);
						ndViews.Nodes.Add(nd);
					}
					else
					{
						if (!nodes.Contains(ndTables))
							nodes.Add(ndTables);
						ndTables.Nodes.Add(nd);
					}

					// <<B167>>
					nd.ImageIndex = nd.Parent.ImageIndex;
					nd.SelectedImageIndex = nd.ImageIndex;
				}

				// include stored procedures
                try
                {
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);
                    foreach (DataRow dr in dt.Rows)
                    {
                        // get the procedure name, skip system stuff
                        string name = dr["PROCEDURE_NAME"].ToString();
                        if (name.StartsWith("~") || name.StartsWith("dt_"))
                            continue;

                        // add this one to the tree
                        if (!nodes.Contains(ndProcs))
                            nodes.Add(ndProcs);
                        nd = new TreeNode(name, -1, -1);
                        nd.Tag = dr;
                        ndProcs.Nodes.Add(nd);

                        // <<B167>>
                        nd.ImageIndex = nd.Parent.ImageIndex;
                        nd.SelectedImageIndex = nd.ImageIndex;
                    }
                }
                catch (System.Data.OleDb.OleDbException)
                {
                    // This handles a specific situation that occurs with SQL Azure that I dunno what to do with otherwise:
                    //
                    // Message=Unspecified error
                    // The stored procedure required to complete this operation could not be found on the server
                    // (they were supplied with the ODBC setup disk for the SQL Server driver).
                    // Please contact your system administrator.
                    //   Source=Microsoft OLE DB Provider for SQL Server
                    //   ErrorCode=-2147467259
                    //
                    // --dima 9-dec-2010.
                }

				// save parameters for stored procedures (not always supported)
                try
                {
					// <<B132>> Jet doesn't support this
					if (conn.ConnectionString.IndexOf("Microsoft.Jet") < 0)
						_dtParms = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedure_Parameters, null);
                } 
                catch {}

				// clean up
				conn.Close();
			}
			catch (Exception x)
			{
				Warning("Cannot get schema for this connection because:\r\n" + x.Message);
				ok = false;
			}
			if (conn != null && conn.State != ConnectionState.Closed)
				conn.Close();

			// done
            _treeDataSources.Invalidate();
            return ok;
		}

		// translate ODBC connection strings into Access or SqlServer <<B153>>
		private static string TranslateConnectionString(string connString)
		{
			// we are only interested in the MSDASQL provider (ODBC data sources)
			if (connString.ToLower().IndexOf("provider=msdasql") < 0)
				return connString;

			// get name of ODBC data source
			Match m = Regex.Match(connString, "Data Source=(?<ds>[^;]+)", RegexOptions.IgnoreCase);
			string ds = m.Groups["ds"].Value;
			if (ds == null || ds.Length == 0)
				return connString;

			// look up ODBC entry in registry (LocalMachine and CurrentUser) <<B166>>
			string keyName = @"software\odbc\odbc.ini\" + ds;
			using (RegistryKey key = Registry.LocalMachine.OpenSubKey(keyName))
			{
				if (key != null)
					return TranslateConnectionString(connString, key);
			}
			using (RegistryKey key = Registry.CurrentUser.OpenSubKey(keyName))
			{
				if (key != null)
					return TranslateConnectionString(connString, key);
			}

			// key not found...
			return connString;
		}
		private static string TranslateConnectionString(string connString, RegistryKey key)
		{
			// get driver
			string driver = key.GetValue("driver") as string;

			// translate Access (jet) data sources
			if (driver != null && driver.ToLower().IndexOf("odbcjt") > -1)
			{
				string mdb = key.GetValue("dbq") as string;
				if (mdb != null && mdb.ToLower().EndsWith(".mdb"))
				{
					return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb + ";";
				}
			}

			// translate SqlServer data sources
			if (driver != null && driver.ToLower().IndexOf("sqlsrv") > -1)
			{
				string server = key.GetValue("server") as string;
				string dbase  = key.GetValue("database") as string;
				if (server != null && server.Length > 0 && dbase != null && dbase.Length > 0)
				{
					string fmt = "Provider=SQLOLEDB.1;" +
						"Integrated Security=SSPI;" +
						"Persist Security Info=False;" +
						"Initial Catalog={0};" +
						"Data Source={1}";
					return string.Format(fmt, dbase, server);
				}
			}

			// unsupported data source...
			return connString;
		}
		
		// add Parameters clause to stored procedure rs
		private string BuildParameters(string rs, DataTable parms)
		{
			// filter parameter list <<B184>>
			DataView dv = parms.DefaultView;
			dv.RowFilter = string.Format("PROCEDURE_NAME = '{0}' AND "
				+ "PARAMETER_NAME <> 'RETURN_VALUE' AND "
				+ "PARAMETER_NAME <> '@RETURN_VALUE'", rs);

			// truncate at ';'
			int pos = rs.LastIndexOf(';');
			if (pos > -1) rs = rs.Substring(0, pos);

			// no parameters? we're done
			if (dv.Count == 0 || pos < 0) return rs;

			// bracket it
			if (rs.IndexOf(' ') > -1)
				rs = string.Format("[{0}]", rs);

			// add parameters
			string sp = "PARAMETERS";
			rs = rs + "(";
			for (int i = 0; i < dv.Count; i++)
			{
				// get this parameter row
				DataRowView drv = dv[i];

				// get parameter name <<B152>>
				string pname = drv["PARAMETER_NAME"] as string;
				if (pname.StartsWith("@"))
					pname = pname.Substring(1); // eat @
				if (pname.ToUpper() == "RETURN_VALUE") // SqlServer 2k
					continue;

				// initialize parameter value
				string pval = "''";

				// get parameter type
				string ptype = drv["TYPE_NAME"] as string;
				if (ptype.IndexOf("date") > -1)
				{
					ptype = "date";
					pval = "'1/1/2001'";
				}
				else if (ptype.IndexOf("char") > -1)
				{
					ptype = "string";
				}
				else
				{
					ptype = "int";
					pval = "0";
				}
				
				sp += string.Format(" {0} {1} {2}{3}",
					pname, ptype, pval, (i == dv.Count-1)? ";\r\n": ", ");
				rs += string.Format("{0}{1}", pname, (i == dv.Count-1)? ")": ", ");
			}

			// done
			return sp + rs;
		}

		// find a recordsource node based on a RecordSource string
		private TreeNode FindNode(string value, TreeNodeCollection nodes)
		{
			foreach (TreeNode nd in nodes)
			{
				// compare table/view name
				if (string.Compare(nd.Text, value, false) == 0)
					return nd;

				// look in subnodes
				if (nd.Nodes != null)
				{
					TreeNode sub = FindNode(value, nd.Nodes);
					if (sub != null) return sub;
				}
			}

			// not found...
			return null;
		}

		// ** load/save most recently used connections

		private void LoadMRU()
		{
			_cmbConn.Items.Clear();
			foreach (string conn in AppSettings.LoadList("recentconnections"))
			{
				if (!_cmbConn.Items.Contains(conn))
					_cmbConn.Items.Add(conn);
			}
		}
		private void SaveMRU()
		{
			AppSettings.SaveList("recentconnections", _cmbConn.Items, true);
		}
		private void Warning(string msg)
		{
			MessageBox.Show(msg, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

        #region Nested types

        private static class ExternalRecordset
        {
            #region Public static

            public static Assembly GetExternalAssembly(string conn)
            {
                return DataSource.GetExternalAssembly(conn);
            }

            public static string GetRecordSourceString(IC1ReportExternalRecordset r)
            {
                return string.Format("{0};{1}", r.GetType().FullName, r.Params);
            }

            public static TreeNode FindNode(TreeNodeCollection nodes, string value)
            {
                int i = value.IndexOf(';');
                string typeName, recordsetParams;
                if (i == -1)
                {
                    typeName = value;
                    recordsetParams = null;
                }
                else
                {
                    typeName = value.Substring(0, i);
                    recordsetParams = value.Substring(i + 1);
                }

                foreach (TreeNode nd in nodes)
                {
                    IC1ReportExternalRecordset r = nd.Tag as IC1ReportExternalRecordset;
                    if (r != null && r.GetType().FullName == typeName)
                    {
                        r.Params = recordsetParams;
                        return nd;
                    }
                }
                return null;
            }

            public static IC1ReportExternalRecordset[] GetRecordsets(Assembly asmb)
            {
                Type[] types;
                try
                {
                    types = asmb.GetTypes();
                }
                catch (ReflectionTypeLoadException ex)
                {
                    types = ex.Types;
                }
                catch (Exception)
                {
                    return null;
                }

                List<IC1ReportExternalRecordset> result = new List<IC1ReportExternalRecordset>();
                foreach (Type t in types)
                {
                    if (t == null)
                        continue;
                    if (t.IsAbstract)
                        continue;
                    Type it = t.GetInterface("IC1ReportExternalRecordset");
                    if (it == null)
                        continue;
                    ConstructorInfo ci = t.GetConstructor(
                        BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic,
                        null,
                        new Type[0],
                        null);
                    if (ci == null)
                        continue;
                    IC1ReportExternalRecordset r = ci.Invoke(null) as IC1ReportExternalRecordset;
                    if (r == null)
                        continue;
                    result.Add(r);
                }
                return result.ToArray();
            }

            #endregion
        }

        #endregion

        // ** event handlers

        // loaded control, remove DataObjects tab if no _htSchema
		override protected void OnLoad(System.EventArgs e)
		{
			// adjust height of connection panel
			_panelConn.Height = _cmbConn.Height;

			// hide/show schema page
			if (_htSchema == null || _htSchema.Count == 0)
			{
				_tab.TabPages.Remove(tbObjects);
				_tab.Appearance = TabAppearance.FlatButtons;
				OnSizeChanged(EventArgs.Empty);
			}
			else
			{
				_tab.Appearance = TabAppearance.Normal;
			}
        }

		// keep tabs hidden
		override protected void OnSizeChanged(EventArgs e)
		{
			if (!DesignMode && _tab.Appearance == TabAppearance.FlatButtons)
			{
				Rectangle rc = ClientRectangle;
				int height = _tab.ItemSize.Height + 3;
				rc.Y -= height;
				rc.Height += height;
				_tab.Dock = DockStyle.None;
				_tab.Bounds = rc;
			}
		}
		
		// finish selection by double-clicking a data source on a tree control
		private void _tree_DoubleClick(object sender, System.EventArgs e)
		{
            // fire DoubleClick event when user double-clicks on a childless item on a tree
            TreeView tree = sender as TreeView;
            if (tree != null && tree.SelectedNode != null && tree.SelectedNode.Nodes.Count == 0)
				OnDoubleClick(e);
		}

        // show connection picker dialog
		private void _btnConn_Click(object sender, System.EventArgs e)
		{
			// get starting connection string
			// (if empty or no provider, start with SQL source as default)
			string s = ConnectionString;
			if (s == null || s.Length == 0 || s.ToLower().IndexOf("provider=") < 0)
				s = "Provider=SQLOLEDB.1;";

			// let user change it
			ConnectionString = PromptConnectionString(s);
		}

		// use owner draw to trim the connection strings (they're way too long!)
		private void _cmbConn_DrawItem(object sender, System.Windows.Forms.DrawItemEventArgs e)
		{
			// get the objects we need
			Brush brush = ((e.State & DrawItemState.Selected) != 0)
				? SystemBrushes.HighlightText
				: SystemBrushes.WindowText;
			string s = (string)_cmbConn.Items[e.Index];

            // do a smart trim for Access and SQL sources
            //int posDS = s.IndexOf("Data Source");
            //int posIC = s.IndexOf("Initial Catalog");
            //if (posIC > -1 && posIC < posDS) posDS = posIC;
            //if (posDS > -1) s = s.Substring(posDS);

			// and draw the item
			e.DrawBackground();
			e.Graphics.DrawString(s, _cmbConn.Font, brush, e.Bounds, _fmt);
			e.DrawFocusRectangle();
		}

		// flip between tables and SQL statement
		private void _btnTables_CheckedChanged(object sender, System.EventArgs e)
		{
			if (_btnDataSources.Checked)
			{
				_txtSql.Visible = _chkWrap.Visible = false;
				_txtSql.Dock = DockStyle.None;
                panel1.Dock = DockStyle.Fill;
                panel1.Visible = true;
                panel1.BringToFront();
                _treeDataSources.Focus();
			}
			else
			{
				panel1.Visible = false;
                panel1.Dock = DockStyle.None;
				_txtSql.Dock = DockStyle.Fill;
				_txtSql.Visible = _chkWrap.Visible = true;
				_txtSql.BringToFront();
				_txtSql.Focus();
			}
		}

        // load DataSet from an XML file
        private void _btnXSD_Click(object sender, System.EventArgs e)
        {
            // get file name
			using (OpenFileDialog dlg = new OpenFileDialog())
			{
				dlg.DereferenceLinks = true;
				dlg.DefaultExt = "xsd";
				dlg.Title = "Select Xml Schema Definition File or assembly with external datasource";
				dlg.Filter = "Xsd Data Files (*.xsd)|*.xsd|Xml Data Files (*.xml)|*.xml|Assemblies (*.exe; *.dll)|*.xml; *.dll|All Files|*.*";
				if (dlg.ShowDialog() != DialogResult.OK) return;

				// make sure it's a DataSet
				try
				{
                    Assembly asmb = DataSource.GetExternalAssembly(dlg.FileName);
                    if (asmb != null)
                    {
                        ConnectionString = dlg.FileName;
                        return;
                    }

                    DataSet ds = C1.C1Preview.DataBinding.DatabindingUtils.ReadXmlSchema(dlg.FileName);

					// assign new conn string
					ConnectionString = dlg.FileName;
					return;
				}
				catch {}

				// if we got here, this isn't a valid data source
				Warning(string.Format("Failed to load schema from '{0}'", dlg.FileName));
			}
        }

#if false // TODO
		private void GetC1DataSchema(string fileName)
		{
			using (StreamReader sr = new StreamReader(fileName))
			{
				// load xml and discard VERSIONxx: at the start
				string xml = sr.ReadToEnd();
				int pos = xml.IndexOf(':');
				if (pos > -1)
				{
					// parse xml
					xml = xml.Substring(pos + 1);
					XmlDocument doc = new XmlDocument();
					doc.LoadXml(xml);

					XmlNodeList tables = doc.SelectNodes("Schema/Tables/dbtable");
					foreach (XmlNode table in tables)
					{
						string tblName = table.Attributes["Name"].Value;
						XmlNodeList fields = table.SelectNodes("Fields/dbtable");
						foreach (XmlNode field in fields)
						{
							string fldName = field.Attributes["Name"].Value;
							string fldType = field.Attributes["DataType"].Value;
							Type tp = Type.GetType("System." + fldType);
							Console.WriteLine("aa");
						}
					}
				}
			}
		}
#endif

        // update table list and sql statement when connection changes
        private void _cmbConn_SelectedValueChanged(object sender, System.EventArgs e)
        {
            ConnectionString = _cmbConn.Text;
        }
        private void _cmbConn_Validating(object sender, System.ComponentModel.CancelEventArgs e)
        {
            ConnectionString = _cmbConn.Text;
        }

		// show sql wizard
		private void _btnWizard_Click(object sender, System.EventArgs e)
		{
			using (SqlBuilderDialog dlg = new SqlBuilderDialog())
			{
				// initialize sql wizard
				try
				{
					Cursor = Cursors.WaitCursor;
					dlg.ConnectionString = ConnectionString;
					dlg.Sql = RecordSource;
				}
				catch
				{
					// error initializing, don't show the dialog
					return;
				}
				finally
				{
					Cursor = Cursors.Default;
				}

				// show sql wizard
				if (dlg.ShowDialog(this) == DialogResult.OK)
				{
					ConnectionString = dlg.ConnectionString;
					_btnSQL.Checked = true;
					_txtSql.Text = dlg.Sql;
				}
			}
		}

		// word-wrap sql statement
		private void _chkWrap_CheckedChanged(object sender, System.EventArgs e)
		{
			_txtSql.WordWrap = _chkWrap.Checked;
        }

        private void _treeDataSources_AfterSelect(object sender, TreeViewEventArgs e)
        {
            btnProperties.Enabled = (e.Node != null) && e.Node.Tag is IC1ReportExternalRecordset;
        }

        private void btnProperties_Click(object sender, EventArgs e)
        {
            if (_treeDataSources.SelectedNode == null)
                return;
            IC1ReportExternalRecordset r = _treeDataSources.SelectedNode.Tag as IC1ReportExternalRecordset;
            if (r == null)
                return;

            r.EditParams();
        }
    }
}
