Exemplo n.º 1
0
        /// <summary>
        /// extracts information from the COLUMNS table of INFORMATION_SCHEMA
        /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
        /// and if the datatype is ENUM, sets the extended property as a List&lt;string&gt; labeled Common.Constants.ENUM_COLUMN_VALUES
        /// </summary>
        private void GetColumnTypes()
        {
            DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " +
                                              "WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb +
                                              "\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION");

            Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>();

            BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb);

            tempWebDb.BeginTransaction();

            tempWebDb.query("SET SQL_SELECT_LIMIT=0");
            foreach (string tableName in TableList())
            {
                DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName));
                res[tableName] = schema.Columns;
            }
            tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
            tempWebDb.CommitTransaction();
            tempWebDb = null;


            foreach (DataRow r in stats.Rows)
            {
                DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string];        // set ColumnName


                string columnType = r["COLUMN_TYPE"] as string;
                if (columnType.StartsWith("enum"))                                                 // enum type
                {
                    string        vals       = columnType.Substring(5, columnType.Length - 5 - 1); // the brackets
                    string[]      split      = vals.Split(new char[] { ',' });
                    List <string> EnumValues = new List <string>();
                    foreach (string s in split)
                    {
                        EnumValues.Add(s.Substring(1, s.Length - 2));
                    }
                    col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues);
                }


                if (col.DataType == typeof(string))
                {
                    col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                string extra = r["EXTRA"] as string;    // set AutoIncrement
                if (extra == "auto_increment")
                {
                    col.AutoIncrement = true;
                }
                if (!col.AutoIncrement)
                {
                    col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
                }
                object colDefault = r["COLUMN_DEFAULT"];                                // set DefaultValue
                // only if there is something sensible has been retrieved from the INFORMATION SCHEMA and no default value has been set
                // in the default datatable retrieved in a SELECT
                if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
                {
                    string colDefaltStr = colDefault as string;

                    if (colDefaltStr == "CURRENT_TIMESTAMP")
                    {
                        col.DefaultValue = DBNull.Value;    // unneccessary, just for clarity
                        col.AllowDBNull  = true;
                    }
                    else if (col.DataType == typeof(string))
                    {
                        col.DefaultValue = colDefaltStr;
                    }
                    else if (col.DataType == typeof(bool))
                    {
                        switch (colDefaltStr)
                        {
                        case "0":
                            col.DefaultValue = false;
                            break;

                        case "1":
                            col.DefaultValue = true;
                            break;

                        default:
                            break;
                        }
                    }
                    else
                    {
                        object parsed = null;
                        if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
                        {
                            col.DefaultValue = parsed;
                        }
                    }
                }

                col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
                try
                {
                    if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
                    {
                        col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                    }
                }
                catch
                { }
            }       // for each row in stats
            columnTypes = res;

            ColumnsToDisplay = new Dictionary <string, List <string> >();
            IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer();

            foreach (string tableName in columnTypes.Keys)
            {
                List <DataColumn> innerList = new List <DataColumn>();
                foreach (DataColumn col in columnTypes[tableName])
                {
                    innerList.Add(col);
                }
                innerList.Sort(comparer);
                columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>();
            }

            foreach (string tableName in FKs.Keys)
            {
                foreach (FK fk in FKs[tableName])
                {
                    columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
                }
            }
        }
Exemplo n.º 2
0
        /// <summary>
        /// extracts information from the COLUMNS table of INFORMATION_SCHEMA
        /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness 
        /// and if the datatype is ENUM, sets the extended property as a List&lt;string&gt; labeled Common.Constants.ENUM_COLUMN_VALUES
        /// </summary>
        private void GetColumnTypes()
        {
            DataTable stats = driver.fetchAll("SELECT COLS.*,"
                 + " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
                 + " FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
                 + " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
                "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");

            Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();

            BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);

            tempWebDb.BeginTransaction();

            //tempWebDb.query("SET SQL_SELECT_LIMIT=0");
            foreach (string tableName in TableList())
            {
                DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
                res[tableName] = schema.Columns;
            }
            //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
            tempWebDb.CommitTransaction();
            tempWebDb = null;

            foreach (DataRow r in stats.Rows)
            {
                DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string];        // set ColumnName

                //string dataType = r["DATA_TYPE"] as string;

                if (col.DataType == typeof(string))
                {
                    col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                bool hasIdentity = (int)r["HAS_IDENTITY"] == 1;    // set AutoIncrement
                if (hasIdentity)
                    col.AutoIncrement = true;
                if (!col.AutoIncrement)
                    col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...

                object colDefault = r["COLUMN_DEFAULT"];      // set DefaultValue
                if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
                {

                    string colDefaltStr = colDefault as string;

                    // similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL.
                    // The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime.

                    if (col.DataType == typeof(string))
                    {
                        col.DefaultValue = colDefaltStr;
                    }
                    else if (col.DataType == typeof(bool))
                    {
                        switch (colDefaltStr)
                        {
                            case "0":
                                col.DefaultValue = false;
                                break;
                            case "1":
                                col.DefaultValue = true;
                                break;
                            default:
                                break;
                        }
                    }
                    else
                    {
                        object parsed = null;
                        if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
                        {
                            col.DefaultValue = parsed;
                        }
                    }
                }

                col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
                try
                {
                    if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                catch { }

            }       // for each row in stats
            columnTypes = res;

            ColumnsToDisplay = new Dictionary<string, List<string>>();
            IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
            foreach (string tableName in columnTypes.Keys)
            {
                List<DataColumn> innerList = new List<DataColumn>();
                foreach (DataColumn col in columnTypes[tableName])
                    innerList.Add(col);
                innerList.Sort(comparer);
                columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();

            }

            foreach (string tableName in FKs.Keys)
            {
                foreach (FK fk in FKs[tableName])
                {
                    columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
                }
            }
        }
Exemplo n.º 3
0
        /// <summary>
        /// extracts information from the COLUMNS table of INFORMATION_SCHEMA
        /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
        /// and if the datatype is ENUM, sets the extended property as a List&lt;string&gt; labeled Common.Constants.ENUM_COLUMN_VALUES
        /// </summary>
        private void GetColumnTypes()
        {
            DataTable stats = driver.fetchAll("SELECT COLS.*,"
                                              + " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
                                              + " FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
                                              + " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
                                              "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");

            Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>();

            BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);

            tempWebDb.BeginTransaction();

            //tempWebDb.query("SET SQL_SELECT_LIMIT=0");
            foreach (string tableName in TableList())
            {
                DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
                res[tableName] = schema.Columns;
            }
            //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
            tempWebDb.CommitTransaction();
            tempWebDb = null;


            foreach (DataRow r in stats.Rows)
            {
                DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string];        // set ColumnName


                //string dataType = r["DATA_TYPE"] as string;


                if (col.DataType == typeof(string))
                {
                    col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                bool hasIdentity = (int)r["HAS_IDENTITY"] == 1;    // set AutoIncrement
                if (hasIdentity)
                {
                    col.AutoIncrement = true;
                }
                if (!col.AutoIncrement)
                {
                    col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
                }
                object colDefault = r["COLUMN_DEFAULT"];                                // set DefaultValue
                if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
                {
                    string colDefaltStr = colDefault as string;

                    // similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL.
                    // The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime.

                    if (col.DataType == typeof(string))
                    {
                        col.DefaultValue = colDefaltStr;
                    }
                    else if (col.DataType == typeof(bool))
                    {
                        switch (colDefaltStr)
                        {
                        case "0":
                            col.DefaultValue = false;
                            break;

                        case "1":
                            col.DefaultValue = true;
                            break;

                        default:
                            break;
                        }
                    }
                    else
                    {
                        object parsed = null;
                        if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
                        {
                            col.DefaultValue = parsed;
                        }
                    }
                }

                col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
                try
                {
                    if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
                    {
                        col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                    }
                }
                catch { }
            }       // for each row in stats
            columnTypes = res;

            ColumnsToDisplay = new Dictionary <string, List <string> >();
            IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer();

            foreach (string tableName in columnTypes.Keys)
            {
                List <DataColumn> innerList = new List <DataColumn>();
                foreach (DataColumn col in columnTypes[tableName])
                {
                    innerList.Add(col);
                }
                innerList.Sort(comparer);
                columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>();
            }

            foreach (string tableName in FKs.Keys)
            {
                foreach (FK fk in FKs[tableName])
                {
                    columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
                }
            }
        }
Exemplo n.º 4
0
        /// <summary>
        /// extracts information from the COLUMNS table of INFORMATION_SCHEMA
        /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness 
        /// and if the datatype is ENUM, sets the extended property as a List&lt;string&gt; labeled Common.Constants.ENUM_COLUMN_VALUES
        /// </summary>
        private void GetColumnTypes()
        {
            DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " +
                "WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb +
                "\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION");

            Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();

            BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb);

            tempWebDb.BeginTransaction();

            tempWebDb.query("SET SQL_SELECT_LIMIT=0");
            foreach(string tableName in TableList()){
                DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName));
                res[tableName] = schema.Columns;
            }
            tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
            tempWebDb.CommitTransaction();
            tempWebDb = null;

            foreach (DataRow r in stats.Rows) {
                DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string];        // set ColumnName

                string columnType = r["COLUMN_TYPE"] as string;
                if (columnType.StartsWith("enum")) {        // enum type
                    string vals = columnType.Substring(5, columnType.Length - 5 - 1);       // the brackets
                    string[] split = vals.Split(new char[]{','});
                    List<string> EnumValues = new List<string>();
                    foreach (string s in split) {
                        EnumValues.Add(s.Substring(1, s.Length - 2));
                    }
                    col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues);
                }

                if (col.DataType == typeof(string)) {
                    col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                string extra = r["EXTRA"] as string;    // set AutoIncrement
                if (extra == "auto_increment")
                    col.AutoIncrement = true;
                if(!col.AutoIncrement)
                    col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...

                object colDefault = r["COLUMN_DEFAULT"];      // set DefaultValue
                if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){
                    string colDefaltStr = colDefault as string;
                    if(colDefaltStr == "CURRENT_TIMESTAMP")
                        col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE);
                    //else{
                       //object parsed;
                       //if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){
                       //     col.DefaultValue = parsed;
                       //}
                    //}
                }

                col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
                if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);

            }       // for each row in stats
            columnTypes = res;

            ColumnsToDisplay = new Dictionary<string, List<string>>();
            IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
            foreach (string tableName in columnTypes.Keys) {
                List<DataColumn> innerList = new List<DataColumn>();
                foreach (DataColumn col in columnTypes[tableName])
                    innerList.Add(col);
                innerList.Sort(comparer);
                columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();

            }

            foreach (string tableName in FKs.Keys) {
                foreach (FK fk in FKs[tableName]) {
                    columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
                }
            }
        }
Exemplo n.º 5
0
        /// <summary>
        /// extracts information from the COLUMNS table of INFORMATION_SCHEMA
        /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness 
        /// and if the datatype is ENUM, sets the extended property as a List&lt;string&gt; labeled Common.Constants.ENUM_COLUMN_VALUES
        /// </summary>
        private void GetColumnTypes()
        {
            DataTable stats = driver.fetchAll("SELECT COLS.*,"
                 +" COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
                 +" FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
                 +" JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
                "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");

            Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();

            BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);

            tempWebDb.BeginTransaction();

            //tempWebDb.query("SET SQL_SELECT_LIMIT=0");
            foreach(string tableName in TableList()){
                DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
                res[tableName] = schema.Columns;
            }
            //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
            tempWebDb.CommitTransaction();
            tempWebDb = null;

            foreach (DataRow r in stats.Rows) {
                DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string];        // set ColumnName

                //string dataType = r["DATA_TYPE"] as string;

                if (col.DataType == typeof(string)) {
                    col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
                }
                bool hasIdentity = (int)r["HAS_IDENTITY"] == 1;    // set AutoIncrement
                if (hasIdentity)
                    col.AutoIncrement = true;
                if(!col.AutoIncrement)
                    col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...

                object colDefault = r["COLUMN_DEFAULT"];      // set DefaultValue
                //if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){
                    //string colDefaltStr = colDefault as string;
                    //if(colDefaltStr == "CURRENT_TIMESTAMP")
                    //    col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE);
                    //else{
                       //object parsed;
                       //if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){
                       //     col.DefaultValue = parsed;
                       //}
                    //}
                //}

                col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
                if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);

            }       // for each row in stats
            columnTypes = res;

            ColumnsToDisplay = new Dictionary<string, List<string>>();
            IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
            foreach (string tableName in columnTypes.Keys) {
                List<DataColumn> innerList = new List<DataColumn>();
                foreach (DataColumn col in columnTypes[tableName])
                    innerList.Add(col);
                innerList.Sort(comparer);
                columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();

            }

            foreach (string tableName in FKs.Keys) {
                foreach (FK fk in FKs[tableName]) {
                    columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
                }
            }
        }