using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.Common;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;

using C1.C1Report;
using C1.C1Report.Util;
using SqlBuilder;

namespace ReportDesignerApp.Util
{
    public partial class DataSourcePicker : UserControl
    {
        private const int c_MaxMRUSize = 10;
        private const int c_TablesImageIndex = 0;
        private const int c_TableImageIndex = 1;
        private const int c_RecordsetsImageIndex = 0;
        private const int c_RecordsetImageIndex = 4;
        private const int c_ViewsImageIndex = 0;
        private const int c_ViewImageIndex = 2;
        private const int c_SchemaImageIndex = 0;
        private const int c_StoredProceduresImageIndex = 0;
        private const int c_StoredProcedureImageIndex = 3;
        private const int c_ConnectionDrawLeftOffset = 5;
        private const int c_ConnectionDrawSpacing = 10;
        private static readonly string c_Tables = Strings.DataSourcePicker.Tables;
        private static readonly string c_Views = Strings.DataSourcePicker.Views;
        private static readonly string c_StoredProcedures = Strings.DataSourcePicker.StoredProcedures;
        private static readonly string c_Recordsets = Strings.DataSourcePicker.Recordsets;

        private static StringFormat s_CaptionStringFormat;
        private static StringFormat s_DetailsStringFormat;

        private int _updateCounter;
        private Hashtable _dataSchema;
        private IsolationLevel _isolationLevel;
        private string _oldConnectionString;

        static DataSourcePicker()
        {
            s_CaptionStringFormat = new StringFormat(StringFormat.GenericTypographic);
            s_CaptionStringFormat.Alignment = StringAlignment.Near;
            s_CaptionStringFormat.LineAlignment = StringAlignment.Center;
            s_DetailsStringFormat = new StringFormat(StringFormat.GenericTypographic);
            s_DetailsStringFormat.Alignment = StringAlignment.Near;
            s_DetailsStringFormat.LineAlignment = StringAlignment.Center;
            s_DetailsStringFormat.Trimming = StringTrimming.EllipsisPath;
        }

        public DataSourcePicker()
        {
            InitializeComponent();

            this._ttMain.SetToolTip(this._btnEditConnection, Strings.DataSourcePicker.TtipBtnEditConnection);
            this._ttMain.SetToolTip(this._btnDataSourceProperties, Strings.DataSourcePicker.TtipBtnDataSourceProperties);

            // Fill _cbDataProvider
            foreach (DsgnDataLinkBase dl in DsgnDataLinkBase.Links)
                _cbDataProvider.Items.Add(new DataProviderDesc(dl));

            // Fill _cbConnectionString
            ArrayList connections = AppSettings.LoadList("recentconnections", MessageForm.Warn);
            foreach (string s in connections)
                _cbConnectionString.Items.Add(ConnectionDesc.FromSettingsString(s));
        }

        #region Private properties
        private bool Updating
        {
            get { return _updateCounter > 0; }
        }
        #endregion

        #region Private
        private void BeginUpdate()
        {
            _updateCounter++;
        }

        private void EndUpdate()
        {
            _updateCounter--;
        }

        // searches TreeNode in _tvDataObjects linked with DataSet in
        // specified _dataSchema, returns null if not found
        private TreeNode FindDataSetObject(
            string s)
        {
            foreach (TreeNode n in _tvDataObjects.Nodes)
                if (n.Tag is DataSet && string.Compare(n.Text, s, true) == 0)
                    return n;
            return null;
        }

        // searches TreeNode in _tvDataObjects linked with DataTable in
        // specified _dataSchema, returns null if not found
        private TreeNode FindDataTableObject(
            TreeNodeCollection nodes,
            string s)
        {
            foreach (TreeNode n in nodes)
            {
                if (n.Tag is DataTable && string.Compare(n.Text, s, true) == 0)
                    return n;
                TreeNode result = FindDataTableObject(n.Nodes, s);
                if (result != null)
                    return result;
            }
            return null;
        }

        private int IndexOfConnectionDesc(
            DsgnDataLinkBase dataLink,
            string connectionString)
        {
            for (int i = 0; i < _cbConnectionString.Items.Count; i++)
            {
                ConnectionDesc cd = (ConnectionDesc)_cbConnectionString.Items[i];
                if (cd.Link == dataLink && cd.ConnectionString == connectionString)
                    return i;
            }
            return -1;
        }

        // updates state of buttons linked with other controls
        private void UpdateButtons()
        {
            DataProviderDesc dpd = _cbDataProvider.SelectedItem as DataProviderDesc;
            _btnEditConnection.Enabled = dpd != null && dpd.Link.Available;
            _btnDataSourceProperties.Enabled = _tvDataSource.SelectedNode != null && _tvDataSource.SelectedNode.Tag is ExternalRecordsetDesc;
            _btnQueryBuilder.Enabled = dpd != null && dpd.Link == DsgnOledbLink.Instance;
        }

        // Updates list of available datasources (tables, views, stored procs)
        // for connection string specified by _cbDataProvider, _cbConnectionString
        // selects specified recordSource if possible
        private void ConnectionStringChanged(
            string recordSource)
        {
            // store current value of connection string to prevent update when
            // _cbConnectionString lost focus
            _oldConnectionString = _cbConnectionString.Text;

            BeginUpdate();
            Cursor = Cursors.WaitCursor;
            try
            {
                DsgnDataLinkBase dl = ((DataProviderDesc)_cbDataProvider.SelectedItem).Link;
                _tvDataSource.Nodes.Clear();
                if (!string.IsNullOrEmpty(_cbConnectionString.Text) && dl.Available)
                {
                    if (dl.UpdateControls(this, recordSource))
                    {
                        // current connection string should be stored in MRU list
                        int i = IndexOfConnectionDesc(dl, _cbConnectionString.Text);
                        if (i >= 0)
                        {
                            ConnectionDesc ci = (ConnectionDesc)_cbConnectionString.Items[i];
                            _cbConnectionString.Items.Remove(ci);
                            _cbConnectionString.Items.Insert(0, ci);
                            _cbConnectionString.SelectedIndex = 0;
                        }
                        else
                        {
                            if (_cbConnectionString.Items.Count > c_MaxMRUSize)
                                _cbConnectionString.Items.RemoveAt(_cbConnectionString.Items.Count - 1);
                            _cbConnectionString.Items.Add(new ConnectionDesc(dl, _cbConnectionString.Text));
                        }
                        // save MRU connections to application's settings
                        // SaveList uses ToString() method of objects in list so we can
                        // simple pass _cbConnectionString.Items items will be automatically converted to string like:
                        // OLEDB;Data source=...
                        List<string> l = new List<string>();
                        foreach (ConnectionDesc cd in _cbConnectionString.Items)
                            l.Add(cd.ToSettingsString());
                        AppSettings.SaveList("recentconnections", l, true, MessageForm.Warn);
                    }
                }
                UpdateButtons();
            }
            finally
            {
                Cursor = Cursors.Default;
                EndUpdate();
            }
        }
        #endregion

        #region Public properties
        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public IsolationLevel IsolationLevel
        {
            get { return _isolationLevel; }
            set { _isolationLevel = value; }
        }

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

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public Hashtable DataSchema
        {
            get { return _dataSchema; }
            set
            {
                _dataSchema = value;
                _tvDataObjects.Nodes.Clear();
                BeginUpdate();
                if (_dataSchema == null)
                {
                    // we should hide _tpDataObjects if DataSchema is empty
                    _tcMain.TabPages.Remove(_tpDataObjects);
                }
                else
                {
                    if (!_tcMain.TabPages.Contains(_tpDataObjects))
                        _tcMain.TabPages.Add(_tpDataObjects);
                    foreach (DataSet ds in _dataSchema.Values)
                    {
                        TreeNode ndDataSet = new TreeNode(ds.DataSetName, 4, 4);
                        ndDataSet.Tag = ds;
                        _tvDataObjects.Nodes.Add(ndDataSet);
                        foreach (DataTable dt in ds.Tables)
                        {
                            TreeNode ndTable = new TreeNode(dt.TableName, -1, -1);
                            ndTable.Tag = dt;
                            ndDataSet.Nodes.Add(ndTable);
                        }
                    }
                }
                EndUpdate();
            }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public DataProvider DataProvider
        {
            get
            {
                if (_tcMain.SelectedTab != _tpConnection)
                    return DataProvider.Auto;
                DataProviderDesc dpd = _cbDataProvider.SelectedItem as DataProviderDesc;
                return dpd == null ? DataProvider.Auto : dpd.Link.DataProvider;
            }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public string ConnectionString
        {
            get
            {
                if (_tcMain.SelectedTab == _tpDataObjects)
                {
                    if (_tvDataObjects.SelectedNode == null)
                        return string.Empty;
                    DataSet dataSet = _tvDataObjects.SelectedNode.Tag as DataSet;
                    if (dataSet != null)
                        return _tvDataObjects.SelectedNode.Text;
                    DataTable dataTable = _tvDataObjects.SelectedNode.Tag as DataTable;
                    if (dataTable != null)
                        return _tvDataObjects.SelectedNode.Parent.Text;
                    return string.Empty;
                }
                if (_tcMain.SelectedTab == _tpConnection)
                {
                    return _cbConnectionString.Text;
                }
                return string.Empty;
            }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public bool RecordSourceSelected
        {
            get
            {
                if (_tcMain.SelectedTab == _tpDataObjects)
                {
                    return _tvDataObjects.SelectedNode != null && _tvDataObjects.SelectedNode.Tag is DataTable;
                }
                if (_tcMain.SelectedTab == _tpConnection)
                {
                    if (_tcConnection.SelectedTab == _tpDataSource)
                    {
                        if (_tvDataSource.SelectedNode == null)
                            return false;
                        return _tvDataSource.SelectedNode.Tag is ObjectDesc;
                    }
                    if (_tcConnection.SelectedTab == _tpSql)
                    {
                        return !string.IsNullOrEmpty(_tbSql.Text);
                    }
                    return false;
                }
                return false;
            }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public string RecordSource
        {
            get
            {
                if (_tcMain.SelectedTab == _tpDataObjects)
                {
                    if (_tvDataObjects.SelectedNode == null)
                        return string.Empty;
                    DataTable dataTable = _tvDataObjects.SelectedNode.Tag as DataTable;
                    return dataTable == null ? string.Empty : dataTable.TableName;
                }
                if (_tcMain.SelectedTab == _tpConnection)
                {
                    if (_tcConnection.SelectedTab == _tpDataSource)
                    {
                        if (_tvDataSource.SelectedNode == null)
                            return string.Empty;
                        ObjectDesc od = _tvDataSource.SelectedNode.Tag as ObjectDesc;
                        if (od != null)
                            return od.RecordSourceString;
                        return string.Empty;
                    }
                    if (_tcConnection.SelectedTab == _tpSql)
                    {
                        return _tbSql.Text;
                    }
                    return string.Empty;
                }
                return string.Empty;
            }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public object Recordset
        {
            get
            {
                if (_tcMain.SelectedTab == _tpDataObjects)
                {
                    if (_tvDataObjects.SelectedNode == null)
                        return null;
                    return _tvDataObjects.SelectedNode.Tag as DataTable;
                }
                return null;
            }
        }
        #endregion

        #region Public
        public void SetDataSource(
            DataProvider dataProvider,
            string connectionString,
            string recordSource)
        {
            BeginUpdate();
            try
            {
                if (connectionString == null)
                    connectionString = string.Empty;

                // Check if connectionString equals to DataSet name in _dataSchema.
                if (_dataSchema != null && _dataSchema.ContainsKey(connectionString))
                {
                    _tpDataObjects.Select();
                    ActiveControl = _tvDataObjects;
                    _tvDataObjects.SelectedNode = FindDataTableObject(_tvDataObjects.Nodes, recordSource);
                    if (_tvDataObjects.SelectedNode == null)
                        _tvDataObjects.SelectedNode = FindDataSetObject(connectionString);

                    // init controls on _tcConnection with default values
                    _cbDataProvider.SelectedIndex = 0;
                    _oldConnectionString = string.Empty;
                    _cbConnectionString.Text = string.Empty;
                    _tvDataSource.Nodes.Clear();
                    _tbSql.Text = string.Empty;

                    return;
                }

                // Select item in _cbDataProvider
                _tpConnection.Select();
                foreach (DataProviderDesc dpd in _cbDataProvider.Items)
                    if (dpd.Link.DataProvider == dataProvider)
                    {
                        _cbDataProvider.SelectedItem = dpd;
                        break;
                    }

                //
                _cbConnectionString.Text = connectionString;
                ConnectionStringChanged(recordSource);

                //
                if (_tvDataSource.SelectedNode != null)
                {
                    _tcConnection.SelectTab(_tpDataSource);
                }
                else
                {
                    if (_tcConnection.TabPages.Contains(_tpSql))
                    {
                        _tcConnection.SelectTab(_tpSql);
                        _tbSql.Text = recordSource;
                    }
                }

                //
                ActiveControl = _cbConnectionString;
            }
            finally
            {
                EndUpdate();
            }
        }
        #endregion

        #region Nested types
        private class DataProviderDesc
        {
            public DsgnDataLinkBase Link;

            #region Constructors
            public DataProviderDesc(
                DsgnDataLinkBase link)
            {
                Link = link;
            }
            #endregion

            #region Public
            public override string ToString()
            {
                return Link.Caption;
            }
            #endregion
        }

        private class ConnectionDesc
        {
            public DsgnDataLinkBase Link;
            public string ConnectionString;
            public string Caption;
            public string Details;

            #region Constructors
            public ConnectionDesc(
                DsgnDataLinkBase link,
                string connectionString)
            {
                Link = link;
                ConnectionString = connectionString;
                Link.Parse(ConnectionString, out Caption, out Details);
            }
            #endregion

            #region Public static
            public static ConnectionDesc FromSettingsString(
                string s)
            {
                int i = s.IndexOf(';');
                if (i == -1)
                    return new ConnectionDesc(DsgnAutoLink.Instance, s);
                if (Enum.IsDefined(typeof(DataProvider), s.Substring(0, i)))
                    return new ConnectionDesc(DsgnDataLinkBase.GetLink((DataProvider)Enum.Parse(typeof(DataProvider), s.Substring(0, i))), s.Substring(i + 1));
                else
                    return new ConnectionDesc(DsgnAutoLink.Instance, s);
            }
            #endregion

            #region Public
            public string ToSettingsString()
            {
                return Link.DataProvider.ToString() + ";" + ConnectionString;
            }

            public override string ToString()
            {
                return ConnectionString;
            }
            #endregion
        }

        private abstract class ObjectDesc
        {
            #region Public properties
            public abstract string RecordSourceString { get; }
            #endregion
        }

        private class DataObjectDesc : ObjectDesc
        {
            public string Name;
            public string SchemaName;

            #region Constructors
            public DataObjectDesc(
                string name,
                string schemaName)
                : base()
            {
                Name = name;
                SchemaName = schemaName;
            }
            #endregion

            #region Public properties
            public override string RecordSourceString
            {
                get
                {
                    return string.IsNullOrEmpty(SchemaName) ? Name : string.Format("{0}.{1}", SchemaName, Name);
                }
            }
            #endregion
        }

        private class ExternalRecordsetDesc : ObjectDesc
        {
            public IC1ReportExternalRecordset Recordset;
            public string Parameters;

            #region Constructors
            public ExternalRecordsetDesc(
                IC1ReportExternalRecordset recordset,
                string parameters)
                : base()
            {
                Recordset = recordset;
                Parameters = parameters;
            }
            #endregion

            #region Public properties
            public override string RecordSourceString
            {
                get { return Recordset.GetType().FullName + ";" + Parameters; }
            }
            #endregion
        }

        private class DataTableDesc : DataObjectDesc
        {
            #region Constructors
            public DataTableDesc(
                string name,
                string schemaName)
                : base(name, schemaName)
            {
            }
            #endregion
        }

        private class StoredProcedureDesc : DataObjectDesc
        {
            public List<StoredProcedureParamDesc> Params = new List<StoredProcedureParamDesc>();

            #region Constructors
            public StoredProcedureDesc(
                string name,
                string schemaName)
                : base(name, schemaName)
            {
            }
            #endregion

            #region Public properties
            public override string RecordSourceString
            {
                get
                {
                    string procName = base.RecordSourceString;
                    if (Params.Count > 0)
                    {
                        StringBuilder sb = new StringBuilder("PARAMETERS ");
                        StringBuilder sb2 = new StringBuilder(Misc.BracketSqlObject(procName) + "(");
                        foreach (StoredProcedureParamDesc sppd in Params)
                        {
                            sb.Append(string.Format("{0} {1} {2}, ", sppd.Name, sppd.Type, sppd.DefaultValue));
                            sb2.Append(string.Format("{0}, ", sppd.Name));
                        }
                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(";\r\n");
                        sb2.Remove(sb2.Length - 2, 2);
                        sb2.Append(")");
                        return sb.ToString() + sb2.ToString();
                    }
                    else
                        return procName;
                }
            }
            #endregion
        }

        private class StoredProcedureParamDesc
        {
            public string Name;
            public string Type;
            public string DefaultValue;

            #region Constructors
            public StoredProcedureParamDesc(
                string name,
                string type,
                string defaultValue)
            {
                Name = name;
                Type = type;
                DefaultValue = defaultValue;
            }
            #endregion
        }

        private abstract class DsgnDataLinkBase
        {
            public readonly static DsgnDataLinkBase[] Links = new DsgnDataLinkBase[] { DsgnAutoLink.Instance, DsgnOledbLink.Instance, DsgnOdbcLink.Instance, DsgnXmlLink.Instance, DsgnExternalObjectLink.Instance, DsgnSqlServerCe3_5Link.Instance, DsgnSqlServerCe4_0Link.Instance };

            #region Public static
            public static DsgnDataLinkBase GetLink(
                DataProvider dataProvider)
            {
                foreach (DsgnDataLinkBase dl in Links)
                    if (dl.DataProvider == dataProvider)
                        return dl;
                // should never be here
                throw new ArgumentException();
            }
            #endregion

            #region Public
            public abstract bool EditConnection(DataSourcePicker dsp, ref string connectionString);

            public abstract bool UpdateControls(DataSourcePicker dsp, string recordSource);

            public virtual void Parse(
                string connectionString,
                out string caption,
                out string details)
            {
                details = null;
                caption = ShortCaption;
                ConnectionStringParser csp = ConnectionStringParser.Parse(connectionString);
                if (csp != null)
                    details = csp.GetDataLocation();
                if (string.IsNullOrEmpty(details))
                    details = connectionString;
            }
            #endregion

            #region Public properties
            public abstract DataLinkBase Link { get; }

            public abstract string Caption { get; }

            public abstract string ShortCaption { get; }

            public abstract DataProvider DataProvider { get; }

            public virtual bool Available
            {
                get { return Link.Available; }
            }
            #endregion
        }

        private class DsgnAutoLink : DsgnDataLinkBase
        {
            public readonly static DsgnAutoLink Instance = new DsgnAutoLink();

            #region Public
            public DsgnDataLinkBase GetRealLink(
                DataSourcePicker dsp)
            {
                DataProvider dp = DataLinkBase.GetDataProviderForConnectionString(dsp._cbConnectionString.Text);
                return GetLink(dp);
            }

            public override bool UpdateControls(
                DataSourcePicker dsp,
                string recordSource)
            {
                return GetRealLink(dsp).UpdateControls(dsp, recordSource);
            }

            public override bool EditConnection(
                DataSourcePicker dsp, 
                ref string connectionString)
            {
                // should never be here
                return false;
            }
            #endregion

            #region Public properties
            public override DataLinkBase Link
            {
                get { return null; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.AutoCaption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.AutoShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.Auto; }
            }

            public override bool Available
            {
                get { return true; }
            }
            #endregion
        }

        private abstract class DsgnDbLink : DsgnDataLinkBase
        {
            #region Private
            private TreeNode FindNode(
                TreeNodeCollection nodes,
                string recordSource)
            {
                foreach (TreeNode n in nodes)
                {
                    DataObjectDesc dod = n.Tag as DataObjectDesc;
                    if (dod != null && dod.RecordSourceString == recordSource)
                        return n;
                    TreeNode result = FindNode(n.Nodes, recordSource);
                    if (result != null)
                        return result;
                }
                return null;
            }
            #endregion

            #region Protected
            protected enum ObjectType
            {
                TableOrView,
                StoredProcedure,
            }

            protected TreeNode AddTableViewNode(
                string name,
                string schema,
                int imageIndex,
                TreeNode parentNode)
            {
                //
                DataTableDesc dtd = new DataTableDesc(name, schema);

                //
                TreeNode result = new TreeNode(name);
                result.Tag = dtd;
                result.ImageIndex = imageIndex;
                result.SelectedImageIndex = imageIndex;
                if (string.IsNullOrEmpty(schema))
                {
                    parentNode.Nodes.Add(result);
                }
                else
                {
                    TreeNode nSchema = FindSchemaNode(parentNode.Nodes, schema);
                    if (nSchema == null)
                    {
                        nSchema = new TreeNode(schema, c_SchemaImageIndex, c_SchemaImageIndex);
                        parentNode.Nodes.Add(nSchema);
                    }
                    nSchema.Nodes.Add(result);
                }

                //
                return result;
            }

            protected virtual bool IsDbObjectVisible(
                ObjectType objectType,
                string name,
                string schema,
                string type)
            {
                if (schema == "sys" || schema == "INFORMATION_SCHEMA")
                    return false;
                switch (objectType)
                {
                    case ObjectType.TableOrView:
                        if (string.Compare(type, "TABLE", true) != 0 && 
                            string.Compare(type, "VIEW", true) != 0 &&
                            string.Compare(type, "LINK", true) != 0)
                            return false;
                        return true;
                    case ObjectType.StoredProcedure:
                        if (name.StartsWith("~") || name.StartsWith("dt_"))
                            return false;
                        return true;
                }
                return false;
            }

            protected TreeNode AddStoredProcedureNode(
                StoredProcedureDesc spd,
                TreeNode parentNode)
            {
                TreeNode result = new TreeNode();
                result.Tag = spd;
                // build caption of stored procedure node
                if (spd.Params.Count > 0)
                {
                    StringBuilder sb = new StringBuilder(spd.Name + "(");
                    foreach (StoredProcedureParamDesc sppd in spd.Params)
                    {
                        sb.Append(sppd.Type);
                        sb.Append(", ");
                    }
                    sb.Remove(sb.Length - 2, 2);
                    result.Text = sb.ToString() + ")";
                }
                else
                    result.Text = spd.Name;
                result.ImageIndex = c_StoredProcedureImageIndex;
                result.SelectedImageIndex = c_StoredProcedureImageIndex;
                if (string.IsNullOrEmpty(spd.SchemaName))
                {
                    parentNode.Nodes.Add(result);
                }
                else
                {
                    TreeNode nSchema = FindSchemaNode(parentNode.Nodes, spd.SchemaName);
                    if (nSchema == null)
                    {
                        nSchema = new TreeNode(spd.SchemaName, c_SchemaImageIndex, c_SchemaImageIndex);
                        parentNode.Nodes.Add(nSchema);
                    }
                    nSchema.Nodes.Add(result);
                }
                return result;
            }

            protected TreeNode FindSchemaNode(
                TreeNodeCollection nodes,
                string schemaName)
            {
                foreach (TreeNode n in nodes)
                    if (n.Text == schemaName)
                        return n;
                return null;
            }

            protected abstract bool UpdateControlsInt(DataSourcePicker dsp);
            #endregion

            #region Public
            public override bool UpdateControls(
                DataSourcePicker dsp,
                string recordSource)
            {
                // show _tpSql page if it hidden
                if (!dsp._tcConnection.TabPages.Contains(dsp._tpSql))
                    dsp._tcConnection.TabPages.Add(dsp._tpSql);
                string connectionString = dsp._cbConnectionString.Text;

                //
                try
                {
                    UpdateControlsInt(dsp);
                    if (!string.IsNullOrEmpty(recordSource))
                        dsp._tvDataSource.SelectedNode = FindNode(dsp._tvDataSource.Nodes, recordSource);
                    return true;
                }
                catch (Exception e)
                {
                    MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetTablesDbFmt, connectionString, e.Message));
                    return false;
                }
            }
            #endregion
        }

        private class DsgnOledbLink : DsgnDbLink
        {
            public readonly static DsgnOledbLink Instance = new DsgnOledbLink();

            #region Private
            private bool GetTablesAndViews(
                DataSourcePicker dsp,
                OleDbConnection connection)
            {
                DataTable dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                try
                {
                    if (!dataTable.Columns.Contains("TABLE_TYPE") ||
                        !dataTable.Columns.Contains("TABLE_NAME"))
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetTablesOleDbFmt, connection.ConnectionString));
                        return false;
                    }

                    //
                    TreeNode nTables = new TreeNode(c_Tables, c_TablesImageIndex, c_TablesImageIndex);
                    TreeNode nViews = new TreeNode(c_Views, c_ViewsImageIndex, c_ViewsImageIndex);
                    int schemaIndex = dataTable.Columns.IndexOf("TABLE_SCHEMA");
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        // check the table type
                        string type = dr["TABLE_TYPE"].ToString();
                        string name = dr["TABLE_NAME"].ToString();
                        string schema = string.Empty;
                        if (schemaIndex >= 0)
                            schema = dr[schemaIndex].ToString();
                        if (!IsDbObjectVisible(ObjectType.TableOrView, name, schema, type))
                            continue;

                        //
                        if (type == "VIEW")
                            AddTableViewNode(name, schema, c_ViewImageIndex, nViews);
                        else
                            AddTableViewNode(name, schema, c_TableImageIndex, nTables);
                    }
                    if (nTables.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nTables);
                    if (nViews.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nViews);
                    return true;
                }
                finally
                {
                    dataTable.Dispose();
                }
            }

            private bool GetStoredProcedures(
                DataSourcePicker dsp,
                OleDbConnection connection)
            {
                DataTable dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);
                DataTable parameters = null;
                try
                {
                    if (!dataTable.Columns.Contains("PROCEDURE_NAME"))
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetStoredProcsFmt, connection.ConnectionString));
                        return false;
                    }
                    // get table with procedures parameters not all data sources supports it
                    try
                    {
                        // Jet doesn't support this
                        if (connection.ConnectionString.IndexOf("Microsoft.Jet") < 0)
                            parameters = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Procedure_Parameters, null);
                    }
                    catch
                    {
                        parameters = null;
                    }


                    TreeNode nProcedures = new TreeNode(c_StoredProcedures, c_StoredProceduresImageIndex, c_StoredProceduresImageIndex);
                    int schemaIndex = dataTable.Columns.IndexOf("PROCEDURE_SCHEMA");
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        // get the procedure name, skip system stuff
                        string name = dr["PROCEDURE_NAME"].ToString();
                        string schema = string.Empty;
                        if (schemaIndex >= 0)
                            schema = dr[schemaIndex].ToString();
                        if (!IsDbObjectVisible(ObjectType.StoredProcedure, name, schema, string.Empty))
                            continue;

                        // truncate at ';'
                        int j = name.LastIndexOf(';');
                        string userName;
                        if (j > -1)
                            userName = name.Substring(0, j);
                        else
                            userName = name;

                        //
                        StoredProcedureDesc spd = new StoredProcedureDesc(userName, schema);

                        // get procedure parameters
                        if (parameters != null)
                        {
                            DataView dataView = parameters.DefaultView;
                            dataView.RowFilter = string.Format("PROCEDURE_NAME = '{0}' AND PARAMETER_NAME <> 'RETURN_VALUE' AND PARAMETER_NAME <> '@RETURN_VALUE'", name);
                            for (int i = 0; i < dataView.Count; i++)
                            {
                                DataRowView drv = dataView[i];
                                string pName = drv["PARAMETER_NAME"].ToString();
                                if (pName.StartsWith("@"))
                                    pName = pName.Substring(1); // eat @
                                string pType = drv["TYPE_NAME"].ToString().ToLower();
                                if (pType.IndexOf("date") >= 0)
                                {
                                    DateTime d = DateTime.Now;
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "date", d.ToString("yyyy-MM-dd")));
                                }
                                else if (pType.IndexOf("char") >= 0 || pType.IndexOf("text") >= 0 || pType == "xml")
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "string", string.Empty));
                                else
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "int", "0"));
                            }
                        }

                        //
                        AddStoredProcedureNode(spd, nProcedures);
                    }
                    if (nProcedures.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nProcedures);


                    return true;
                }
                finally
                {
                    dataTable.Dispose();
                }
            }
            #endregion

            #region Protected
            protected override bool UpdateControlsInt(
                DataSourcePicker dsp)
            {
                OleDbConnection connection = null;
                try
                {
                    //
                    connection = (OleDbConnection)OledbLink.Instance.CreateConnection(dsp._cbConnectionString.Text);
                    connection.Open();

                    dsp._tvDataSource.BeginUpdate();
                    try
                    {
                        // add to _tvDataSource tables and views
                        GetTablesAndViews(dsp, connection);

                        // add to _tvDataSource stored procedures
                        GetStoredProcedures(dsp, connection);
                    }
                    finally
                    {
                        dsp._tvDataSource.EndUpdate();
                    }

                    //
                    return true;
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }
            }
            #endregion

            #region Public
            public override bool EditConnection(
                DataSourcePicker dsp,
                ref string connectionString)
            {
                MSDASC.DataLinksClass dlc = new MSDASC.DataLinksClass();
                if (dlc == null)
                {
                    // AppUtils.ShowWarning(Strings.DataSourcePicker.WarnFailedToCreateDataLinks);
                    MessageForm.Warn(Strings.DataSourcePicker.WarnFailedToCreateDataLinks);
                    return false;
                }
                dlc.hWnd = (int)dsp.Handle; // << make it modal
                bool result;
                if (string.IsNullOrEmpty(connectionString))
                {
                    ADODB.Connection connection = (ADODB.Connection)dlc.PromptNew();
                    result = connection != null;
                    if (result)
                        connectionString = connection.ConnectionString;
                }
                else
                {
                    object connection = new ADODB.Connection();
                    ((ADODB.Connection)connection).ConnectionString = connectionString;
                    result = dlc.PromptEdit(ref connection);
                    if (result)
                        connectionString = ((ADODB.Connection)connection).ConnectionString;
                }
                return result;
            }
            #endregion

            #region Public properties
            public override DataLinkBase Link
            {
                get { return OledbLink.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.OleDbCaption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.OleDbShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.OLEDB; }
            }
            #endregion
        }

        private abstract class DsgnSqlServerCeLink : DsgnDbLink
        {
            #region Private
            private bool GetTables(
                DataSourcePicker dsp,
                DbConnection connection)
            {
                // from here:
                // http://social.msdn.microsoft.com/Forums/eu/adodotnetdataproviders/thread/89c25dfb-5f1d-48d6-b2c2-82044733a822
                // GetSchema has been implemented in SQL Server Compact 4.0,
                // for earlier versions you must use other methods to obtain schema information.
                DbCommand command = null;
                try
                {
                    command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
                    command.Transaction = connection.BeginTransaction();

                    using (DbDataReader ddr = command.ExecuteReader())
                    {
                        //
                        TreeNode nTables = new TreeNode(c_Tables, c_TablesImageIndex, c_TablesImageIndex);
                        while (ddr.Read())
                        {
                            // check the table type
                            string type = ddr["TABLE_TYPE"].ToString().ToUpper();
                            string name = ddr["TABLE_NAME"].ToString();
                            string schema = ddr["TABLE_SCHEMA"].ToString();
                            if (!IsDbObjectVisible(ObjectType.TableOrView, name, schema, type))
                                continue;

                            //
                            AddTableViewNode(name, schema, c_TableImageIndex, nTables);
                        }
                        if (nTables.Nodes.Count > 0)
                            dsp._tvDataSource.Nodes.Add(nTables);
                    }

                    return true;
                }
                finally
                {
                    if (command != null)
                        command.Transaction.Dispose();
                    command.Dispose();
                }
            }
            #endregion

            #region Protected
            protected override bool UpdateControlsInt(
                DataSourcePicker dsp)
            {
                DbConnection connection = null;
                try
                {
                    //
                    connection = ((DbLink)Link).CreateConnection(dsp._cbConnectionString.Text);
                    connection.Open();

                    dsp._tvDataSource.BeginUpdate();
                    try
                    {
                        // SqlServerCe provides only list of tables
                        GetTables(dsp, connection);
                    }
                    finally
                    {
                        dsp._tvDataSource.EndUpdate();
                    }

                    //
                    return true;
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }
            }
            #endregion

            #region Public
            public override bool EditConnection(
                DataSourcePicker dsp,
                ref string connectionString)
            {
                using (SqlServerCeConnectionStringEditorForm f = new SqlServerCeConnectionStringEditorForm())
                    return f.Edit(ref connectionString);
            }
            #endregion
        }

        private class DsgnSqlServerCe3_5Link : DsgnSqlServerCeLink
        {
            public readonly static DsgnSqlServerCe3_5Link Instance = new DsgnSqlServerCe3_5Link();

            #region Public properties
            public override DataLinkBase Link
            {
                get { return SqlServerCe3_5Link.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.SqlServerCe3_5Caption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.SqlServerCe3_5ShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.SqlServerCe3_5; }
            }
            #endregion
        }

        private class DsgnSqlServerCe4_0Link : DsgnSqlServerCeLink
        {
            public readonly static DsgnSqlServerCe4_0Link Instance = new DsgnSqlServerCe4_0Link();

            #region Public properties
            public override DataLinkBase Link
            {
                get { return SqlServerCe4_0Link.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.SqlServerCe4_0Caption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.SqlServerCe4_0ShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.SqlServerCe4_0; }
            }
            #endregion
        }

        private class DsgnOdbcLink : DsgnDbLink
        {
            public readonly static DsgnOdbcLink Instance = new DsgnOdbcLink();

            #region Private
            /*private TreeNode AddTableViewNode(
                string name,
                string schema,
                int imageIndex,
                TreeNode parentNode)
            {
                //
                DataTableDesc dtd = new DataTableDesc(name, schema);

                //
                TreeNode result = new TreeNode(name);
                result.Tag = dtd;
                result.ImageIndex = imageIndex;
                result.SelectedImageIndex = imageIndex;
                if (string.IsNullOrEmpty(schema))
                {
                    parentNode.Nodes.Add(result);
                }
                else
                {
                    TreeNode nSchema = FindSchemaNode(parentNode.Nodes, schema);
                    if (nSchema == null)
                    {
                        nSchema = new TreeNode(schema, c_SchemaImageIndex, c_SchemaImageIndex);
                        parentNode.Nodes.Add(nSchema);
                    }
                    nSchema.Nodes.Add(result);
                }

                //
                return result;
            }*/

            private bool GetTables(
                DataSourcePicker dsp,
                OdbcConnection connection)
            {
                DataTable dataTable = connection.GetSchema(OdbcMetaDataCollectionNames.Tables);
                try
                {
                    if (!dataTable.Columns.Contains("TABLE_TYPE") ||
                        !dataTable.Columns.Contains("TABLE_NAME"))
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetTablesOdbcFmt, connection.ConnectionString));
                        return false;
                    }
                    
                    //
                    TreeNode nTables = new TreeNode(c_Tables, c_TablesImageIndex, c_TablesImageIndex);
                    int schemaIndex = dataTable.Columns.IndexOf("TABLE_SCHEM");
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        // check the table type
                        string type = dr["TABLE_TYPE"].ToString().ToUpper();
                        string name = dr["TABLE_NAME"].ToString();
                        string schema = string.Empty;
                        if (schemaIndex >= 0)
                            schema = dr[schemaIndex].ToString();
                        if (!IsDbObjectVisible(ObjectType.TableOrView, name, schema, type))
                            continue;

                        //
                        AddTableViewNode(name, schema, c_TableImageIndex, nTables);
                    }
                    if (nTables.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nTables);
                    
                    return true;
                }
                finally
                {
                    dataTable.Dispose();
                }
            }

            private bool GetViews(
                DataSourcePicker dsp,
                OdbcConnection connection)
            {
                DataTable dataTable = connection.GetSchema(OdbcMetaDataCollectionNames.Views);
                try
                {
                    if (!dataTable.Columns.Contains("TABLE_TYPE") ||
                        !dataTable.Columns.Contains("TABLE_NAME"))
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetViewsOdbcFmt, connection.ConnectionString));
                        return false;
                    }

                    //
                    TreeNode nViews = new TreeNode(c_Views, c_ViewsImageIndex, c_ViewsImageIndex);
                    int schemaIndex = dataTable.Columns.IndexOf("TABLE_SCHEM");
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        // check the table type
                        string type = dr["TABLE_TYPE"].ToString().ToUpper();
                        string name = dr["TABLE_NAME"].ToString();
                        string schema = string.Empty;
                        if (schemaIndex >= 0)
                            schema = dr[schemaIndex].ToString();
                        if (!IsDbObjectVisible(ObjectType.TableOrView, name, schema, type))
                            continue;

                        //
                        AddTableViewNode(name, schema, c_ViewImageIndex, nViews);
                    }
                    if (nViews.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nViews);

                    return true;
                }
                finally
                {
                    dataTable.Dispose();
                }
            }

            private bool GetStoredProcedures(
                DataSourcePicker dsp,
                OdbcConnection connection)
            {
                DataTable dataTable = null;
                try
                {
                    dataTable = connection.GetSchema(OdbcMetaDataCollectionNames.Procedures, null);
                }
                catch
                {
                    // simple eat an exception, not all data sources supports it
                    return true;
                }
                DataTable parameters = null;
                try
                {
                    if (!dataTable.Columns.Contains("PROCEDURE_NAME"))
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotGetStoredProcsOdbcFmt, connection.ConnectionString));
                        return false;
                    }
                    // get table with procedures parameters not all data sources supports it
                    try
                    {
                        parameters = connection.GetSchema(OdbcMetaDataCollectionNames.ProcedureParameters, null);
                    }
                    catch
                    {
                        parameters = null;
                    }

                    TreeNode nProcedures = new TreeNode(c_StoredProcedures, c_StoredProceduresImageIndex, c_StoredProceduresImageIndex);
                    int schemaIndex = dataTable.Columns.IndexOf("PROCEDURE_SCHEM");
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        string name = dr["PROCEDURE_NAME"].ToString();
                        string schema = string.Empty;
                        if (schemaIndex >= 0)
                            schema = dr[schemaIndex].ToString();
                        if (!IsDbObjectVisible(ObjectType.StoredProcedure, name, schema, string.Empty))
                            continue;

                        // truncate at ';'
                        int j = name.LastIndexOf(';');
                        string userName;
                        if (j > -1)
                            userName = name.Substring(0, j);
                        else
                            userName = name;

                        //
                        StoredProcedureDesc spd = new StoredProcedureDesc(userName, schema);

                        // set filter to parameters
                        if (parameters != null)
                        {
                            DataView dataView = parameters.DefaultView;
                            dataView.RowFilter = string.Format("PROCEDURE_NAME = '{0}'", name);
                            for (int i = 0; i < dataView.Count; i++)
                            {
                                DataRowView drv = dataView[i];
                                string pName = drv["COLUMN_NAME"].ToString();
                                if (pName.StartsWith("@"))
                                    pName = pName.Substring(1); // eat @
                                string pType = drv["TYPE_NAME"].ToString().ToLower();
                                if (pType.IndexOf("date") >= 0)
                                {
                                    DateTime d = DateTime.Now;
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "date", d.ToString("yyyy-MM-dd")));
                                }
                                else if (pType.IndexOf("char") >= 0 || pType.IndexOf("text") >= 0 || pType == "xml")
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "string", string.Empty));
                                else
                                    spd.Params.Add(new StoredProcedureParamDesc(pName, "int", "0"));
                            }
                        }

                        //
                        AddStoredProcedureNode(spd, nProcedures);
                    }
                    if (nProcedures.Nodes.Count > 0)
                        dsp._tvDataSource.Nodes.Add(nProcedures);


                    return true;
                }
                finally
                {
                    dataTable.Dispose();
                }
            }
            #endregion

            #region Protected
            protected override bool UpdateControlsInt(
                DataSourcePicker dsp)
            {
                OdbcConnection connection = null;
                try
                {
                    //
                    connection = (OdbcConnection)OdbcLink.Instance.CreateConnection(dsp._cbConnectionString.Text);
                    connection.Open();

                    dsp._tvDataSource.BeginUpdate();
                    try
                    {
                        // add to _tvDataSource tables and views
                        GetTables(dsp, connection);
                        GetViews(dsp, connection);

                        // add to _tvDataSource stored procedures
                        GetStoredProcedures(dsp, connection);
                    }
                    finally
                    {
                        dsp._tvDataSource.EndUpdate();
                    }

                    //
                    return true;
                }
                finally
                {
                    if (connection != null)
                        connection.Dispose();
                }
            }
            #endregion

            #region Public
            public override bool EditConnection(
                DataSourcePicker dsp,
                ref string connectionString)
            {
                IntPtr henv = IntPtr.Zero;
                IntPtr hdbc = IntPtr.Zero;
                short result = 0;
                try
                {
                    result = Win32.SQLAllocEnv(out henv);
                    if (!Win32.SQL_SUCCEEDED(result))
                        throw new ApplicationException("OdbcConnectionUIControl_SQLAllocConnectFailed");

                    result = Win32.SQLAllocConnect(henv, out hdbc);
                    if (!Win32.SQL_SUCCEEDED(result))
                        throw new ApplicationException("OdbcConnectionUIControl_SQLAllocConnectFailed");

                    string currentConnectionString = connectionString;
                    StringBuilder newConnectionString = new StringBuilder(1024);
                    short newConnectionStringLength = 0;
                    result = Win32.SQLDriverConnect(hdbc, dsp.Handle, currentConnectionString, (short)currentConnectionString.Length, newConnectionString, 1024, out newConnectionStringLength, Win32.SQL_DRIVER_PROMPT);
                    if (!Win32.SQL_SUCCEEDED(result) && result != Win32.SQL_NO_DATA)
                    {
                        // Try again without the current connection string, in case it was invalid
                        result = Win32.SQLDriverConnect(hdbc, dsp.Handle, null, 0, newConnectionString, 1024, out newConnectionStringLength, Win32.SQL_DRIVER_PROMPT);
                    }
                    if (!Win32.SQL_SUCCEEDED(result) && result != Win32.SQL_NO_DATA)
                        throw new ApplicationException("OdbcConnectionUIControl_SQLDriverConnectFailed");

                    Win32.SQLDisconnect(hdbc);

                    if (newConnectionStringLength > 0)
                    {
                        connectionString = newConnectionString.ToString();
                        return true;
                    }
                    return false;
                }
                finally
                {
                    if (hdbc != IntPtr.Zero)
                        Win32.SQLFreeConnect(hdbc);
                    if (henv != IntPtr.Zero)
                        Win32.SQLFreeEnv(henv);
                }
            }
            #endregion

            #region Public properties
            public override DataLinkBase Link
            {
                get { return OdbcLink.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.OdbcCaption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.OdbcShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.ODBC; }
            }
            #endregion
        }

        private class DsgnXmlLink : DsgnDataLinkBase
        {
            public readonly static DsgnXmlLink Instance = new DsgnXmlLink();

            #region Public
            public override bool EditConnection(
                DataSourcePicker dsp,
                ref string connectionString)
            {
                using (OpenFileDialog dlg = new OpenFileDialog())
                {
                    dlg.DereferenceLinks = true;
                    dlg.DefaultExt = "xsd";
                    dlg.Title = Strings.DataSourcePicker.XmlDialogTitle;
                    dlg.Filter = Strings.DataSourcePicker.XmlDialogFilter;
                    if (File.Exists(connectionString))
                        dlg.FileName = connectionString;
                    if (dlg.ShowDialog() != DialogResult.OK)
                        return false;

                    connectionString = dlg.FileName;
                    return true;
                }
            }

            public override bool UpdateControls(
                DataSourcePicker dsp,
                string recordSource)
            {
                dsp._tcConnection.SelectedTab = dsp._tpDataSource;
                dsp._tcConnection.TabPages.Remove(dsp._tpSql);
                string fileName = dsp._cbConnectionString.Text;
                if (!File.Exists(fileName))
                {
                    MessageForm.Error(string.Format(Strings.DataSourcePicker.XmlFileNotFoundFmt, fileName));
                    return false;
                }

                try
                {
                    DataSet dataSet = C1.C1Preview.DataBinding.DatabindingUtils.ReadXmlSchema(fileName);
                    if (dataSet.Tables.Count <= 0)
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.NoTablesInXmlFileFmt, fileName));
                        return false;
                    }
                    TreeNode nTables = new TreeNode(c_Tables, DataSourcePicker.c_TablesImageIndex, DataSourcePicker.c_TablesImageIndex);
                    dsp._tvDataSource.Nodes.Add(nTables);
                    foreach (DataTable dt in dataSet.Tables)
                    {
                        TreeNode n = new TreeNode(dt.TableName, DataSourcePicker.c_TableImageIndex, DataSourcePicker.c_TableImageIndex);
                        n.Tag = new DataTableDesc(dt.TableName, string.Empty);
                        nTables.Nodes.Add(n);
                    }
                    dsp._tvDataSource.SelectedNode = AppUtils.FindNodeByText(dsp._tvDataSource.Nodes, recordSource, false);
                    return true;
                }
                catch (Exception e)
                {
                    MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrLoadingDatasetFromXmlFmt, fileName, e.Message));
                    return false;
                }
            }

            public override void Parse(
                string connectionString,
                out string caption,
                out string details)
            {
                caption = ShortCaption;
                details = connectionString;
            }
            #endregion

            #region Public properties
            public override DataLinkBase Link
            {
                get { return XmlLink.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.XmlCaption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.XmlShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.XmlFile; }
            }
            #endregion
        }

        private class DsgnExternalObjectLink : DsgnDataLinkBase
        {
            public readonly static DsgnExternalObjectLink Instance = new DsgnExternalObjectLink();

            #region Public
            #endregion

            #region Private static
            private static IC1ReportExternalRecordset[] GetRecordsets(
                Assembly assembly)
            {
                Type[] types;
                try
                {
                    types = assembly.GetTypes();
                }
                catch (ReflectionTypeLoadException ex)
                {
                    types = ex.Types;
                }
                catch (Exception)
                {
                    throw;
                }

                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

            #region Public
            public override bool EditConnection(
                DataSourcePicker dsp,
                ref string connectionString)
            {
                using (OpenFileDialog dlg = new OpenFileDialog())
                {
                    dlg.DereferenceLinks = true;
                    dlg.DefaultExt = "dll";
                    dlg.Title = Strings.DataSourcePicker.ExternalDialogTitle;
                    dlg.Filter = Strings.DataSourcePicker.ExternalDialogFilter;
                    if (File.Exists(connectionString))
                        dlg.FileName = connectionString;
                    if (dlg.ShowDialog() != DialogResult.OK)
                        return false;

                    connectionString = dlg.FileName;
                    return true;
                }
            }

            public override bool UpdateControls(
                DataSourcePicker dsp,
                string recordSource)
            {
                dsp._tcConnection.SelectedTab = dsp._tpDataSource;
                dsp._tcConnection.TabPages.Remove(dsp._tpSql);
                string fileName = dsp._cbConnectionString.Text;
                Assembly assembly = ExternalObjectLink.GetExternalAssembly(DataLinkBase.ReplaceSpecialFolderTags(fileName));
                if (assembly == null)
                {
                    MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotLoadAsmbFmt, fileName));
                    return false;
                }

                try
                {
                    IC1ReportExternalRecordset[] recordsets = GetRecordsets(assembly);
                    if (recordsets == null || recordsets.Length <= 0)
                    {
                        MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrNoDataInAsmbFmt, fileName));
                        return false;
                    }
                    TreeNode nExternalRecordsets = new TreeNode(c_Recordsets, c_RecordsetsImageIndex, c_RecordsetsImageIndex);
                    dsp._tvDataSource.Nodes.Add(nExternalRecordsets);
                    int i = recordSource.IndexOf(';');
                    string typeName = i < 0 ? string.Empty : recordSource.Substring(0, i);
                    foreach (IC1ReportExternalRecordset rer in recordsets)
                    {
                        TreeNode n = new TreeNode(rer.Caption, c_RecordsetImageIndex, c_RecordsetImageIndex);
                        n.Tag = new ExternalRecordsetDesc(rer, string.Empty);
                        nExternalRecordsets.Nodes.Add(n);
                        if (rer.GetType().FullName == typeName)
                        {
                            ((ExternalRecordsetDesc)n.Tag).Parameters = recordSource.Substring(i + 1);
                            dsp._tvDataSource.SelectedNode = n;
                        }
                    }

                    // done
                    return true;
                }
                catch (Exception e)
                {
                    MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotLoadDataFromAsmbFmt, fileName, e.Message));
                    return false;
                }
            }

            public override void Parse(
                string connectionString,
                out string caption,
                out string details)
            {
                caption = ShortCaption;
                details = connectionString;
            }
            #endregion

            #region Public properties
            public override DataLinkBase Link
            {
                get { return ExternalObjectLink.Instance; }
            }

            public override string Caption
            {
                get { return Strings.DataSourcePicker.ExternalCaption; }
            }

            public override string ShortCaption
            {
                get { return Strings.DataSourcePicker.ExternalShortCaption; }
            }

            public override DataProvider DataProvider
            {
                get { return DataProvider.ExternalObject; }
            }
            #endregion
        }

        private static class Win32
        {
            #region ODBC constants
            // ODBC Driver prompt options
            internal const ushort
                SQL_DRIVER_PROMPT = 2;

            // ODBC return values
            internal const short
                SQL_NO_DATA = 100;
            #endregion

            #region ODBC functions
            internal static bool SQL_SUCCEEDED(
                short rc)
            {
                return (((rc) & (~1)) == 0);
            }

            [DllImport("odbc32.dll")]
            internal static extern short SQLAllocEnv(
                out IntPtr EnvironmentHandle);

            [DllImport("odbc32.dll")]
            internal static extern short SQLAllocConnect(
                IntPtr EnvironmentHandle,
                out IntPtr ConnectionHandle);

            [DllImport("odbc32.dll", EntryPoint = "SQLDriverConnectW", CharSet = CharSet.Unicode)]
            internal static extern short SQLDriverConnect(
                IntPtr hdbc,
                IntPtr hwnd,
                string szConnStrIn,
                short cbConnStrIn,
                System.Text.StringBuilder szConnStrOut,
                short cbConnStrOutMax,
                out short pcbConnStrOut,
                ushort fDriverCompletion);

            [DllImport("odbc32.dll")]
            internal static extern short SQLDisconnect(
                IntPtr ConnectionHandle);

            [DllImport("odbc32.dll")]
            internal static extern short SQLFreeConnect(
                IntPtr ConnectionHandle);

            [DllImport("odbc32.dll")]
            internal static extern short SQLFreeEnv(
                IntPtr EnvironmentHandle);

            [DllImport("odbccp32.dll", CharSet = CharSet.Unicode)]
            internal static extern bool SQLGetInstalledDrivers(
                char[] lpszBuf,
                int cbBufMax,
                ref int pcbBufOut);

            [DllImport("odbccp32.dll", CharSet = CharSet.Unicode)]
            internal static extern int SQLGetPrivateProfileString(
                string lpszSection,
                string lpszEntry,
                string lpszDefault,
                StringBuilder RetBuffer,
                int cbRetBuffer,
                string lpszFilename);
            #endregion
        }
        #endregion

        private void _cbDataProvider_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (Updating)
                return;

            // check connection string and clear it if it is not valid
            // for selected data provider
            DataProviderDesc dpd = _cbDataProvider.SelectedItem as DataProviderDesc;
            if (dpd.Link != DsgnAutoLink.Instance)
            {
                if (!dpd.Link.Link.Available)
                {
                    // selected data provider is not installed on the system
                    // show warning for user
                    MessageForm.Warn(string.Format(Strings.DataSourcePicker.ProviderNotInstalledFmt, dpd.Link.Caption));
                    _cbConnectionString.Text = string.Empty;
                }
                else
                {
                    if (!dpd.Link.Link.Validate(_cbConnectionString.Text))
                        // connection string is not valid clear it
                        _cbConnectionString.Text = string.Empty;
                }
            }

            ConnectionStringChanged(RecordSource);
        }

        private void _cbConnectionString_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (Updating)
                return;

            ConnectionDesc cd = _cbConnectionString.SelectedItem as ConnectionDesc;
            if (cd != null)
            {
                BeginUpdate();
                foreach (DataProviderDesc dpd in _cbDataProvider.Items)
                    if (dpd.Link == cd.Link)
                    {
                        _cbDataProvider.SelectedItem = dpd;
                        break;
                    }
                ConnectionStringChanged(RecordSource);
                EndUpdate();
            }
        }

        private void _cbConnectionString_Enter(object sender, EventArgs e)
        {
            _oldConnectionString = _cbConnectionString.Text;
        }

        private void _cbConnectionString_Leave(object sender, EventArgs e)
        {
            if (Updating)
                return;

            if (_cbConnectionString.Text != _oldConnectionString)
                ConnectionStringChanged(RecordSource);
        }

        private void _tvDataSource_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (Updating)
                return;

            UpdateButtons();
        }

        private void _btnEditConnection_Click(object sender, EventArgs e)
        {
            DataProviderDesc dpd = _cbDataProvider.SelectedItem as DataProviderDesc;
            DsgnDataLinkBase dl;
            if (dpd == null || dpd.Link == DsgnAutoLink.Instance)
            {
                // auto link can not be used for editing parameters
                // we should determine real type of connection string
                // and use needed Link class
                dl = DsgnAutoLink.Instance.GetRealLink(this);
            }
            else
                dl = dpd.Link;

            try
            {
                string connectionString = _cbConnectionString.Text;
                if (dl.EditConnection(this, ref connectionString))
                    SetDataSource(dl.DataProvider, connectionString, RecordSource);
            }
            catch (Exception ex)
            {
                MessageForm.Error(string.Format(Strings.DataSourcePicker.ErrCannotEditConnFmt, ex.Message));
            }
            ActiveControl = _cbConnectionString;
        }

        private void _cbWordWrap_CheckedChanged(object sender, EventArgs e)
        {
            _tbSql.WordWrap = _cbWordWrap.Checked;
        }

        private void _btnDataSourceProperties_Click(object sender, EventArgs e)
        {
            if (_tvDataSource.SelectedNode != null)
            {
                ExternalRecordsetDesc erd = _tvDataSource.SelectedNode.Tag as ExternalRecordsetDesc;
                if (erd != null)
                {
                    erd.Recordset.Params = erd.Parameters;
                    erd.Recordset.EditParams();
                    erd.Parameters = erd.Recordset.Params;
                    return;
                }
            }
        }

        private void _btnQueryBuilder_Click(object sender, EventArgs e)
        {
            using (SqlBuilderDialog dlg = new SqlBuilderDialog())
            {
                // initialize sql wizard
                try
                {
                    dlg.ConnectionString = _cbConnectionString.Text;
                    dlg.Sql = RecordSource;
                }
                catch
                {
                    // error initializing, don't show the dialog
                    return;
                }

                // show sql wizard
                if (dlg.ShowDialog(this) == DialogResult.OK)
                {
                    BeginUpdate();
                    if (_cbConnectionString.Text != dlg.ConnectionString)
                    {
                        // copy connection string from SqlBuilderDialog
                        // select OleDb link
                        for (int i = 0; i < _cbDataProvider.Items.Count; i++)
                            if (((DataProviderDesc)_cbDataProvider.Items[i]).Link == DsgnOledbLink.Instance)
                            {
                                _cbDataProvider.SelectedIndex = i;
                                break;
                            }

                        // set connection string text
                        _cbConnectionString.Text = dlg.ConnectionString;

                        // update controls
                        ConnectionStringChanged(RecordSource);
                    }
                    _tbSql.Text = dlg.Sql;
                    EndUpdate();
                }
            }
        }

        private void _cbConnectionString_DrawItem(object sender, DrawItemEventArgs e)
        {
            //
            ConnectionDesc cd = (ConnectionDesc)_cbConnectionString.Items[e.Index];

            e.DrawBackground();
            Color color = ((e.State & DrawItemState.Selected) != 0) ? SystemColors.HighlightText : SystemColors.WindowText;
            using (Font f = new Font(_cbConnectionString.Font, FontStyle.Bold))
            {
                Size sz = TextRenderer.MeasureText(e.Graphics, cd.Caption, f, Size.Empty, TextFormatFlags.Default);
                Rectangle captionRect = new Rectangle(e.Bounds.X + c_ConnectionDrawLeftOffset, e.Bounds.Y, sz.Width, e.Bounds.Height);
                Rectangle detailsRect = new Rectangle(e.Bounds.X + sz.Width + c_ConnectionDrawLeftOffset + c_ConnectionDrawSpacing, e.Bounds.Y, e.Bounds.Width - sz.Width - c_ConnectionDrawLeftOffset - c_ConnectionDrawSpacing, e.Bounds.Height);
                TextRenderer.DrawText(e.Graphics, cd.Caption, f, captionRect, color, TextFormatFlags.Left | TextFormatFlags.VerticalCenter);
                TextRenderer.DrawText(e.Graphics, cd.Details, _cbConnectionString.Font, detailsRect, color, TextFormatFlags.Left | TextFormatFlags.VerticalCenter | TextFormatFlags.PathEllipsis);
            }
            e.DrawFocusRectangle();
        }

        private void _cbDataProvider_DrawItem(object sender, DrawItemEventArgs e)
        {
            DataProviderDesc dpd = (DataProviderDesc)_cbDataProvider.Items[e.Index];
            e.DrawBackground();
            Color color;
            if ((e.State & DrawItemState.Selected) != 0)
                color = SystemColors.HighlightText;
            else
                color = dpd.Link.Available ? SystemColors.WindowText : SystemColors.GrayText;
            TextRenderer.DrawText(e.Graphics, dpd.ToString(), _cbDataProvider.Font, e.Bounds, color, TextFormatFlags.Left | TextFormatFlags.VerticalCenter);
            e.DrawFocusRectangle();
        }
    }
}
