Ejemplo n.º 1
0
        public static String GetColumnDbType(String strTableName, string strCol)
        {
            TableColumnData col = GetTableColumn(strTableName, strCol);

            if (col == null)
            {
                return(String.Empty);
            }

            return(col.DBType);
        }
Ejemplo n.º 2
0
        private static void InitDataTableList(bool isSystemTable)
        {
            bool isSQL = (isSystemTable && DataQueryProvider.IsSystemSQLConnection) || (isSystemTable == false && DataQueryProvider.IsCompanySQLConnection);

            String strQuery;

            Dictionary <String, TableStructureData> lstTables = new Dictionary <string, TableStructureData>();

            if (isSystemTable)
            {
                SystemTables = new List <string>();
            }

            #region Table
            String strCol;

            if (isSQL)
            {
                strQuery = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                                        WHERE (TABLE_TYPE='BASE TABLE') AND (TABLE_NAME<>'sysdiagrams')
		                                ORDER BY TABLE_NAME"        ;
                strCol   = "TABLE_NAME";
            }
            else
            {
                strQuery = @"SELECT * FROM sqlite_master WHERE type='table'";
                strCol   = "tbl_name";
            }

            DataSet ds;
            if (isSystemTable)
            {
                ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(strQuery);
            }
            else
            {
                ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    String strTableName = row[strCol].ToString();
                    if (lstTables.ContainsKey(strTableName) == false)
                    {
                        TableStructureData table = new TableStructureData(strTableName);
                        lstTables.Add(strTableName, table);
                        if (isSystemTable)
                        {
                            SystemTables.Add(strTableName);
                        }
                    }
                }
            }
            #endregion

            if (isSQL)
            {
                #region All Columns
                strQuery = String.Format(@"SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
	                                                            FROM INFORMATION_SCHEMA.COLUMNS "    );

                ds = null;
                if (isSystemTable)
                {
                    ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(strQuery);
                }
                else
                {
                    ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
                }

                int iCount = -1;
                if (ds != null && ds.Tables.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        TableStructureData table = null;
                        if (lstTables.TryGetValue(dr["TABLE_NAME"].ToString(), out table))
                        {
                            iCount++;

                            String strColumnName = dr["COLUMN_NAME"].ToString();
                            if (table.ColumnsList.ContainsKey(strColumnName) == false)
                            {
                                #region ColumnData

                                TableColumnData colData = new TableColumnData();
                                colData.Index = iCount;
                                colData.Name  = strColumnName;

                                String strMaxLength = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();
                                if (String.IsNullOrWhiteSpace(strMaxLength) == false && strMaxLength != "NULL" && dr["DATA_TYPE"].ToString() != "ntext")
                                {
                                    colData.DBType = String.Format(@"{0}({1})", dr["DATA_TYPE"].ToString(), strMaxLength);
                                }
                                else
                                {
                                    colData.DBType = dr["DATA_TYPE"].ToString();
                                }

                                colData.Nullable = (dr["IS_NULLABLE"].ToString() == "YES");

                                #region Primary Column
                                if (strColumnName.EndsWith("ID"))
                                {
                                    String strTableName = strColumnName.Substring(0, strColumnName.Length - 2) + "s";
                                    if (table.TableName.Equals(strTableName))
                                    {
                                        table.PrimaryColumn = strColumnName;
                                    }
                                }
                                #endregion

                                #region NO column
                                if (strColumnName.ToUpper().Equals("NO"))
                                {
                                    table.NOColumn = strColumnName;
                                }
                                else if (strColumnName.ToUpper().EndsWith("NO"))
                                {
                                    String stTemp = table.TableName.ToUpper().Substring(0, table.TableName.Length - 1) + "NO";
                                    if (strColumnName.ToUpper().Equals(stTemp))
                                    {
                                        table.NOColumn = strColumnName;
                                    }
                                }
                                #endregion

                                #region NAME column
                                if (strColumnName.ToUpper().Equals("NAME"))
                                {
                                    table.NAMEColumn = strColumnName;
                                }
                                if (strColumnName.ToUpper().EndsWith("NAME"))
                                {
                                    String stTemp = table.TableName.ToUpper().Substring(0, table.TableName.Length - 1) + "NAME";
                                    if (strColumnName.ToUpper().Equals(stTemp))
                                    {
                                        table.NAMEColumn = strColumnName;
                                    }
                                }

                                #endregion

                                #endregion

                                table.ColumnsList.Add(strColumnName, colData);
                                if (strColumnName.Contains("FK_"))
                                {
                                    String strTemp             = strColumnName.Split('_')[1];
                                    String strPrimaryTableName = strTemp.Substring(0, strTemp.Length - 2) + "s";
                                    if (table.ForeignColumnsList == null)
                                    {
                                        table.ForeignColumnsList = new Dictionary <String, String>();
                                    }
                                    table.ForeignColumnsList.Add(strColumnName, strPrimaryTableName);
                                }
                            }
                        }
                    }
                }
                #endregion
            }

            foreach (String strTableName in lstTables.Keys)
            {
                if (DataTablesList.ContainsKey(strTableName) == false)
                {
                    DataTablesList.Add(strTableName, lstTables[strTableName]);
                }
            }
        }