public void SyncFromDataSourceToTableModel(IHost host)
        {
            using (IDbConnection connection = GetConnection())
            {
                try
                {
                    connection.Open();
                    DataTable schema = GetSchema(connection);
                    DataTable tables = GetSchema(connection, "tables");
                    DataTable columns = GetSchema(connection, "columns");

                    var tableElements = new Dictionary<string, TableElement>();
                    foreach (TableElement child in AllChildren)
                    {
                        string key = child.GetDisplayName();
                        tableElements.Add(key, child);
                    }

                    ClearChildren();

                    Engine.MuteNotify();

                    foreach (DataRow row in tables.Rows)
                    {
                        string tableName = row["table_name"].ToString();

                        TableElement table = null;
                        if (!tableElements.ContainsKey(tableName))
                        {
                            table = new TableElement();
                            table.Name = tableName;
                            tableElements.Add(tableName, table);
                        }

                        table = tableElements[tableName];
                        AddChild(table);

                        IDbCommand command = connection.CreateCommand();
                        command.CommandText = string.Format("select * from [{0}]", tableName);
                        IDataReader reader = command.ExecuteReader();
                        DataTable tableSchema = reader.GetSchemaTable();
                        reader.Close();

                        table.Columns.ClearChildren();
                        foreach (DataRow columnRow in tableSchema.Rows)
                        {
                            var column = new ColumnElement();
                            column.Name = (string) columnRow["ColumnName"];
                            column.IsNullable = (bool) columnRow["AllowDBNull"];
                            column.IsAutoIncrement = (bool) columnRow["IsAutoIncrement"];
                            column.IsIdentity = (bool) columnRow["IsIdentity"];
                            column.DefaultValue = "";
                            column.NativeType = (Type) columnRow["DataType"];
                            column.IsUnique = (bool) columnRow["IsUnique"];
                            column.Ordinal = (int) columnRow["ColumnOrdinal"];
                            column.DbType = (string) columnRow["DataTypeName"];
                            column.MaxLength = (int) columnRow["ColumnSize"];
                            table.Columns.AddChild(column);
                        }
                    }

                    //foreach (DataRow row in columns.Rows)
                    //{
                    //    string tableName = row["table_name"].ToString();
                    //    string columnName = row["column_name"].ToString();

                    //    TableElement table = tableElements[tableName];
                    //    ColumnElement column = new ColumnElement();
                    //    column.Name = columnName;
                    //    column.IsNullable = false;
                    //    column.IsPK = false;
                    //    column.DefaultValue = "";

                    //    table.AddChild(column);

                    //}

                    Engine.EnableNotify();
                }
                catch
                {
                    MessageBox.Show("Connection failed");
                }

                host.RefreshProjectTree();
            }
        }
Example #2
0
        public void SyncFromDataSourceToTableModel(IHost host)
        {
            using (IDbConnection connection = GetConnection())
            {
                try
                {
                    connection.Open();
                    DataTable schema  = GetSchema(connection);
                    DataTable tables  = GetSchema(connection, "tables");
                    DataTable columns = GetSchema(connection, "columns");

                    var tableElements = new Dictionary <string, TableElement>();
                    foreach (TableElement child in AllChildren)
                    {
                        string key = child.GetDisplayName();
                        tableElements.Add(key, child);
                    }

                    ClearChildren();

                    Engine.MuteNotify();


                    foreach (DataRow row in tables.Rows)
                    {
                        string tableName = row["table_name"].ToString();

                        TableElement table = null;
                        if (!tableElements.ContainsKey(tableName))
                        {
                            table      = new TableElement();
                            table.Name = tableName;
                            tableElements.Add(tableName, table);
                        }

                        table = tableElements[tableName];
                        AddChild(table);

                        IDbCommand command = connection.CreateCommand();
                        command.CommandText = string.Format("select * from [{0}]", tableName);
                        IDataReader reader      = command.ExecuteReader();
                        DataTable   tableSchema = reader.GetSchemaTable();
                        reader.Close();

                        table.Columns.ClearChildren();
                        foreach (DataRow columnRow in tableSchema.Rows)
                        {
                            var column = new ColumnElement();
                            column.Name            = (string)columnRow["ColumnName"];
                            column.IsNullable      = (bool)columnRow["AllowDBNull"];
                            column.IsAutoIncrement = (bool)columnRow["IsAutoIncrement"];
                            column.IsIdentity      = (bool)columnRow["IsIdentity"];
                            column.DefaultValue    = "";
                            column.NativeType      = (Type)columnRow["DataType"];
                            column.IsUnique        = (bool)columnRow["IsUnique"];
                            column.Ordinal         = (int)columnRow["ColumnOrdinal"];
                            column.DbType          = (string)columnRow["DataTypeName"];
                            column.MaxLength       = (int)columnRow["ColumnSize"];
                            table.Columns.AddChild(column);
                        }
                    }

                    //foreach (DataRow row in columns.Rows)
                    //{
                    //    string tableName = row["table_name"].ToString();
                    //    string columnName = row["column_name"].ToString();

                    //    TableElement table = tableElements[tableName];
                    //    ColumnElement column = new ColumnElement();
                    //    column.Name = columnName;
                    //    column.IsNullable = false;
                    //    column.IsPK = false;
                    //    column.DefaultValue = "";


                    //    table.AddChild(column);

                    //}

                    Engine.EnableNotify();
                }
                catch
                {
                    MessageBox.Show("Connection failed");
                }

                host.RefreshProjectTree();
            }
        }