/// <summary> /// Makes use of the DataBase INFORMATION_SCHEMA to get a list of Table names for the current DataBase Connection. /// </summary> /// <param name="subAppName_in">Table Filter. If your Application is to be hosted at some ASP, which provides you with one DataBase only, and you're using that DataBase for more than one Application. I assume you're using some convention for Table naming like: AP1_Table1, AP1_Table2, AP2_Table1, ... . Or even if you have several modules sharing same data base. If so, you can use this parameter to filter Table names for some specific Application, like: AP1 or AP2</param> /// <returns>String array, representing a list of Table names</returns> public cDBTable[] getTables( string subAppName_in, string sqlFuncion_in ) { cDBTable[] getTables_out; #region DataTable _dtemp = base.Execute_SQLQuery_returnDataTable(gettables(subAppName_in)); DataTable _dtemp; if ( (sqlFuncion_in == null) || (sqlFuncion_in == string.Empty) ) { _dtemp = Execute_SQLQuery_returnDataTable( getTables_query( Connectionstring_DBName, subAppName_in ) ); } else { _dtemp = Execute_SQLFunction_returnDataTable( sqlFuncion_in, new IDbDataParameter[] { newDBDataParameter("dbName_", DbType.String, ParameterDirection.Input, Connectionstring_DBName, Connectionstring_DBName.Length), newDBDataParameter("subApp_", DbType.String, ParameterDirection.Input, subAppName_in, subAppName_in.Length) } ); } #endregion getTables_out = new cDBTable[_dtemp.Rows.Count]; for (int r = 0; r < _dtemp.Rows.Count; r++) { getTables_out[r] = new cDBTable( (string)_dtemp.Rows[r][INFORMATION_SCHEMA_TABLES_TABLE_NAME], (1 == (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_TABLES_IS_VIEW], typeof(int))), // ToDos: here! string.Empty ); } _dtemp.Dispose(); _dtemp = null; return getTables_out; }
/// <summary> /// Makes use of the DataBase INFORMATION_SCHEMA to get a list of Table names for the current DataBase Connection. /// </summary> /// <param name="subAppName_in">Table Filter. If your Application is to be hosted at some ASP, which provides you with one DataBase only, and you're using that DataBase for more than one Application. I assume you're using some convention for Table naming like: AP1_Table1, AP1_Table2, AP2_Table1, ... . Or even if you have several modules sharing same data base. If so, you can use this parameter to filter Table names for some specific Application, like: AP1 or AP2</param> /// <returns>String array, representing a list of Table names</returns> public cDBTable[] getTables( string subAppName_in, string sqlFuncion_in ) { cDBTable[] getTables_out; #region DataTable _dtemp = ...; DataTable _dtemp; if ( (sqlFuncion_in == null) || (sqlFuncion_in == string.Empty) ) { StringBuilder _query = new StringBuilder(string.Empty); switch (dbservertype_) { #if PostgreSQL case eDBServerTypes.PostgreSQL: #endif case eDBServerTypes.SQLServer: #region query.Append("SELECT ..."); _query.Append(@" SELECT TABLE_NAME AS ""Name"", CASE WHEN (TABLE_TYPE = 'VIEW') THEN CAST(1 AS Int) ELSE CAST(0 AS Int) END AS ""isVT"" FROM INFORMATION_SCHEMA.TABLES WHERE ( (TABLE_TYPE = 'BASE TABLE') OR (TABLE_TYPE = 'VIEW') ) AND ( (TABLE_TYPE != 'VIEW') OR ( (TABLE_TYPE = 'VIEW') AND (TABLE_NAME NOT LIKE 'v0_%') ) ) AND (TABLE_NAME != 'dtproperties') AND (TABLE_NAME NOT LIKE 'sql_%') AND (TABLE_NAME NOT LIKE 'pg_%') AND (TABLE_NAME NOT LIKE 'sys%') AND (TABLE_NAME NOT LIKE '%__base') AND (TABLE_SCHEMA NOT LIKE 'information_schema') "); if (subAppName_in != "") { _query.Append("AND ("); string[] _subAppNames = subAppName_in.Split('|'); for (int i = 0; i < _subAppNames.Length; i++) { _query.Append(string.Format( "(TABLE_NAME {0} '{1}'){2}", (_subAppNames[i].IndexOf('%') >= 0) ? "LIKE" : "=", _subAppNames[i], (i == _subAppNames.Length - 1) ? "" : " OR " )); } _query.Append(") "); } _query.Append(@"ORDER BY ""Name"" "); #endregion break; #if MySQL case eDBServerTypes.MySQL: string _database = Connectionstring_database(); #region _query.Append("SELECT ..."); _query.Append(string.Format(@" SELECT TABLE_NAME AS ""Name"", CASE WHEN (TABLE_TYPE = 'VIEW') THEN CAST(1 AS Signed Int) ELSE CAST(0 AS Signed Int) END AS ""isVT"" FROM INFORMATION_SCHEMA.TABLES WHERE ( (TABLE_TYPE = 'BASE TABLE') OR (TABLE_TYPE = 'VIEW') ) AND (TABLE_SCHEMA = '{0}') ", _database )); if (subAppName_in != "" ) { _query.Append("AND ("); string[] _subAppNames = subAppName_in.Split('|'); for (int i = 0; i < _subAppNames.Length; i++) { _query.Append(string.Format( "(TABLE_NAME {0} '{1}'){2}", (_subAppNames[i].IndexOf('%') >= 0) ? "LIKE" : "=", _subAppNames[i], (i == _subAppNames.Length - 1) ? "" : " OR " )); } _query.Append(") "); } _query.Append(@"ORDER BY ""Name"" "); #endregion break; #endif default: throw new Exception("not implemented"); } _dtemp = Execute_SQLQuery_returnDataTable(_query.ToString()); } else { _dtemp = Execute_SQLFunction_returnDataTable( sqlFuncion_in, new IDbDataParameter[] { newDBDataParameter("subApp_", DbType.String, ParameterDirection.Input, subAppName_in, subAppName_in.Length) } ); } #endregion getTables_out = new cDBTable[_dtemp.Rows.Count]; for (int r = 0; r < _dtemp.Rows.Count; r++) getTables_out[r] = new cDBTable( (string)_dtemp.Rows[r]["Name"], #if MySQL (dbservertype_ == eDBServerTypes.MySQL) ? ((long)_dtemp.Rows[r]["isVT"] == 1L) : #endif ((int)_dtemp.Rows[r]["isVT"] == 1) , // ToDos: here! string.Empty ); _dtemp.Dispose(); _dtemp = null; return getTables_out; }