/// <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;
}
