/// <summary> /// Makes use of the DataBase INFORMATION_SCHEMA to get a list of Field names for some specific Table. /// </summary> /// <param name="tableName_in">Table name for which Field names are to be retrieved</param> /// <returns>String array, representing a list of Field names</returns> public cDBTableField[] getTableFields( string tableName_in, string sqlFuncion_in ) { cDBTableField[] getTableFields_out; #region DataTable _dtemp = ...; DataTable _dtemp; if ( (sqlFuncion_in == null) || (sqlFuncion_in == string.Empty) ) { _dtemp = Execute_SQLQuery_returnDataTable( getTableFields_query( tableName_in ) ); } else { _dtemp = Execute_SQLFunction_returnDataTable( sqlFuncion_in, new IDbDataParameter[] { newDBDataParameter("dbName_", DbType.String, ParameterDirection.Input, Connectionstring_DBName, Connectionstring_DBName.Length), newDBDataParameter("tableName_", DbType.String, ParameterDirection.Input, tableName_in, tableName_in.Length) } ); } #endregion bool _includetablename = _dtemp.Columns.Contains(INFORMATION_SCHEMA_COLUMNS_TABLE_NAME); getTableFields_out = new cDBTableField[_dtemp.Rows.Count]; for (int r = 0; r < _dtemp.Rows.Count; r++) { getTableFields_out[r] = new cDBTableField(); getTableFields_out[r].TableName = _includetablename ? (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_TABLE_NAME] : string.Empty; getTableFields_out[r].Name = (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_COLUMN_NAME]; // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].Size = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_CHARACTER_MAXIMUM_LENGTH] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_CHARACTER_MAXIMUM_LENGTH]; getTableFields_out[r].Size = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_CHARACTER_MAXIMUM_LENGTH] == DBNull.Value) ? 0 : (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_CHARACTER_MAXIMUM_LENGTH], typeof(int)); // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].isNullable = ((int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_NULLABLE] == 1); getTableFields_out[r].isNullable = 1 == (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_NULLABLE], typeof(int)); getTableFields_out[r].FK_TableName = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_FK_COLUMN_NAME] == DBNull.Value) ? string.Empty : (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_FK_TABLE_NAME]; getTableFields_out[r].FK_FieldName = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_FK_COLUMN_NAME] == DBNull.Value) ? string.Empty : (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_FK_COLUMN_NAME]; // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].isIdentity = ((int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_IDENTITY] == 1); getTableFields_out[r].isIdentity = 1 == (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_IDENTITY], typeof(int)); // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].isPK = ((int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_PK] == 1); getTableFields_out[r].isPK = 1 == (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_IS_PK], typeof(int)); // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].Numeric_Precision = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_PRECISION] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_PRECISION]; getTableFields_out[r].Numeric_Precision = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_PRECISION] == DBNull.Value) ? 0 : (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_PRECISION], typeof(int)); // comment: some providers send int, other long, hence using convert change type: //getTableFields_out[r].Numeric_Scale = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_SCALE] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_SCALE]; getTableFields_out[r].Numeric_Scale = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_SCALE] == DBNull.Value) ? 0 : (int)Convert.ChangeType(_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_NUMERIC_SCALE], typeof(int)); getTableFields_out[r].DBType_inDB_name = (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_DATA_TYPE]; getTableFields_out[r].DBDefaultValue = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_COLUMN_DEFAULT] == DBNull.Value) ? string.Empty : (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_COLUMN_DEFAULT]; getTableFields_out[r].DBCollationName = (_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_COLLATION_NAME] == DBNull.Value) ? string.Empty : (string)_dtemp.Rows[r][INFORMATION_SCHEMA_COLUMNS_COLLATION_NAME]; // ToDos: here! getTableFields_out[r].DBDescription = string.Empty; } _dtemp.Dispose(); _dtemp = null; return getTableFields_out; }
/// <summary> /// Makes use of the DataBase INFORMATION_SCHEMA to get a list of Field names for some specific Table. /// </summary> /// <param name="tableName_in">Table name for which Field names are to be retrieved</param> /// <returns>String array, representing a list of Field names</returns> public cDBTableField[] getTableFields( string tableName_in, string sqlFuncion_in ) { cDBTableField[] getTableFields_out; #region DataTable _dtemp = ...; DataTable _dtemp; if ( (sqlFuncion_in == null) || (sqlFuncion_in == string.Empty) ) { string _query; switch (dbservertype_) { case eDBServerTypes.SQLServer: #region _query = "SELECT ..."; _query = string.Format(@" SELECT t1.COLUMN_NAME AS ""Name"", CASE WHEN t1.IS_NULLABLE = 'No' THEN CAST(0 AS Int) ELSE CAST(1 AS Int) END AS ""isNullable"", t1.DATA_TYPE AS ""Type"", t1.CHARACTER_MAXIMUM_LENGTH AS ""Size"", CASE WHEN (t6.TABLE_TYPE = 'VIEW') THEN CASE WHEN ( (SUBSTRING(t1.COLUMN_NAME, 3, 1) = ',') AND (SUBSTRING(t1.COLUMN_NAME, 2, 1) = 'K') ) THEN CAST(1 AS Int) ELSE CAST(0 AS Int) END WHEN t2.CONSTRAINT_NAME IS NULL THEN CAST(0 AS Int) ELSE CAST(1 AS Int) END AS ""isPK"", CASE WHEN (t6.TABLE_TYPE != 'VIEW') THEN CAST(COLUMNPROPERTY(OBJECT_ID(t1.TABLE_NAME), t1.COLUMN_NAME, 'IsIdentity') AS Int) ELSE CASE WHEN ( (SUBSTRING(t1.COLUMN_NAME, 3, 1) = ',') AND (SUBSTRING(t1.COLUMN_NAME, 1, 1) = 'I') ) THEN CAST(1 AS Int) ELSE CAST(0 AS Int) END --COLUMNPROPERTY(OBJECT_ID(t5.TABLE_NAME), t5.COLUMN_NAME, 'IsIdentity') END AS ""isIdentity"", CASE WHEN (t6.TABLE_TYPE != 'VIEW') THEN t8.TABLE_NAME ELSE /* CASE WHEN (SUBSTRING(t1.COLUMN_NAME, 3, 1) = ',') THEN SUBSTRING( t1.COLUMN_NAME, dbo.fnc__FIND( ',', t1.COLUMN_NAME, 1 ) + 1, dbo.fnc__FIND( ',', t1.COLUMN_NAME, dbo.fnc__FIND( ',', t1.COLUMN_NAME, 1 ) + 1 ) - dbo.fnc__FIND( ',', t1.COLUMN_NAME, 1 ) - 1 ) ELSE NULL END */ NULL --t5.TABLE_NAME END AS ""FK_TableName"", CASE WHEN (t6.TABLE_TYPE != 'VIEW') THEN t8.COLUMN_NAME ELSE /* CASE WHEN (SUBSTRING(t1.COLUMN_NAME, 3, 1) = ',') THEN SUBSTRING( t1.COLUMN_NAME, dbo.fnc__FIND( ',', t1.COLUMN_NAME, dbo.fnc__FIND( ',', t1.COLUMN_NAME, 1 ) + 1 ) + 1, LEN(t1.COLUMN_NAME) ) ELSE NULL END */ NULL --t5.COLUMN_NAME END AS ""FK_FieldName"", t1.COLUMN_DEFAULT, t1.COLLATION_NAME, t1.NUMERIC_PRECISION, t1.NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS AS t1 LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2 ON (t2.COLUMN_NAME = t1.COLUMN_NAME) AND (t2.TABLE_NAME = t1.TABLE_NAME) AND ( --(t2.CONSTRAINT_NAME LIKE 'PK%') (t2.CONSTRAINT_NAME = 'PK_' + t2.TABLE_NAME) -- the microsoft sql server way OR (t2.CONSTRAINT_NAME = t2.TABLE_NAME + '_PK') -- the microsoft visio way ) --LEFT JOIN INFORMATION_SCHEMA.Referential_Constraints t3 ON -- (t3.UNIQUE_CONSTRAINT_NAME = t2.CONSTRAINT_NAME) LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t4 ON (t4.COLUMN_NAME = t1.COLUMN_NAME) AND (t4.TABLE_NAME = t1.TABLE_NAME) AND ( (t4.CONSTRAINT_NAME LIKE '%_FK%') OR (t4.CONSTRAINT_NAME LIKE '%FK_%') ) --LEFT JOIN INFORMATION_SCHEMA.View_Column_Usage t5 ON -- (t5.VIEW_NAME = t1.TABLE_NAME) -- AND -- (t5.COLUMN_NAME = t1.COLUMN_NAME) LEFT JOIN INFORMATION_SCHEMA.TABLES t6 ON (t6.TABLE_NAME = t1.TABLE_NAME) LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t7 ON (t4.CONSTRAINT_NAME = t7.CONSTRAINT_NAME) LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t8 ON (t7.UNIQUE_CONSTRAINT_NAME = t8.CONSTRAINT_NAME) --WHERE (t1.TABLE_NAME LIKE 'vUserGroup%') --OR (t1.TABLE_NAME = 'UserGroup') OR (t1.TABLE_NAME = 'User') OR (t1.TABLE_NAME = 'Group') --WHERE (t1.TABLE_NAME != 'dtproperties') AND (t1.TABLE_NAME NOT LIKE 'sql_%') AND (t1.TABLE_NAME NOT LIKE 'pg_%') AND (t1.TABLE_NAME NOT LIKE 'sys%') WHERE (t1.TABLE_NAME = '{0}') ORDER BY t1.TABLE_NAME, t1.ORDINAL_POSITION", tableName_in ); #endregion break; #if PostgreSQL case eDBServerTypes.PostgreSQL: #region _query = "SELECT ..."; _query = string.Format(@" SELECT t1.COLUMN_NAME AS ""Name"", CASE WHEN t1.IS_NULLABLE = 'NO' THEN CAST(0 AS Int) ELSE CAST(1 AS Int) END AS ""isNullable"", t1.DATA_TYPE AS ""Type"", t1.CHARACTER_MAXIMUM_LENGTH AS ""Size"", CASE WHEN (t6.TABLE_TYPE = 'VIEW') THEN CAST(0 AS Int) WHEN t7.column_name IS NULL THEN CASE WHEN (t6.TABLE_TYPE != 'VIEW') THEN CASE WHEN (column_default LIKE 'nextval(''%') THEN CAST(1 AS Int) ELSE CAST(0 AS Int) END ELSE CAST(0 AS Int) END ELSE CAST(1 AS Int) END AS ""isPK"", CASE WHEN (t6.TABLE_TYPE != 'VIEW') THEN CASE WHEN (column_default LIKE 'nextval(''%') THEN CAST(1 AS Int) ELSE CAST(0 AS Int) END ELSE CAST(0 AS Int) END AS ""isIdentity"", -- CASE -- WHEN (t6.TABLE_TYPE != 'VIEW') THEN ---- CASE ---- WHEN t4.CONSTRAINT_NAME IS NULL THEN -- NULL ---- ELSE ---- t4.table_name ---- END -- ELSE NULL -- END AS ""FK_TableName"", -- CASE -- WHEN (t6.TABLE_TYPE != 'VIEW') THEN ---- CASE ---- WHEN t4.CONSTRAINT_NAME IS NULL THEN -- NULL ---- ELSE ---- t4.column_name ---- END -- ELSE NULL -- END AS ""FK_FieldName"", t1.COLUMN_DEFAULT, t1.COLLATION_NAME, CAST(0 AS Int) as ""NUMERIC_PRECISION"", CAST(0 AS Int) as ""NUMERIC_SCALE"" FROM INFORMATION_SCHEMA.COLUMNS AS t1 LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t7 ON ( (t7.column_name = t1.COLUMN_NAME) AND (t7.constraint_name = t1.table_name || '_pkey') ) -- LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t4 ON ( -- (t4.CONSTRAINT_NAME = t1.TABLE_NAME || '_' || t1.COLUMN_NAME || '_fkey') -- OR -- (t4.CONSTRAINT_NAME = t1.TABLE_NAME || '__base_' || t1.COLUMN_NAME || '_fkey') -- ) --LEFT JOIN INFORMATION_SCHEMA.View_Column_Usage t5 ON -- (t5.VIEW_NAME = t1.TABLE_NAME) -- AND -- (t5.COLUMN_NAME = t1.COLUMN_NAME) LEFT JOIN INFORMATION_SCHEMA.TABLES t6 ON (t6.TABLE_NAME = t1.TABLE_NAME) WHERE (t1.TABLE_NAME = '{0}') ORDER BY t1.TABLE_NAME, t1.ORDINAL_POSITION ", tableName_in ); #endregion break; #endif #if MySQL case eDBServerTypes.MySQL: string _database = Connectionstring_database(); #region _query = "SELECT ..."; _query = string.Format(@" SELECT t1.COLUMN_NAME AS ""Name"", -- CASE -- WHEN t1.IS_NULLABLE = 'NO' THEN CAST(0 AS Signed Int) -- ELSE -- CAST(1 AS Signed Int) -- END AS ""isNullable"", t1.DATA_TYPE AS ""Type"", t1.CHARACTER_MAXIMUM_LENGTH AS ""Size"", CASE WHEN ((t6.TABLE_TYPE != 'VIEW') AND (t1.COLUMN_KEY = 'PRI')) THEN CAST(1 AS Signed Int) ELSE CAST(0 AS Signed Int) END AS ""isPK"", CASE WHEN ((t6.TABLE_TYPE != 'VIEW') AND (t1.EXTRA = 'auto_increment')) THEN CAST(1 AS Signed Int) ELSE CAST(0 AS Signed Int) END AS ""isIdentity"", NULL AS ""FK_TableName"", NULL AS ""FK_FieldName"", t1.COLUMN_DEFAULT, t1.COLLATION_NAME, CAST(0 AS unsigned) as ""NUMERIC_PRECISION"", CAST(0 AS unsigned) as ""NUMERIC_SCALE"" FROM INFORMATION_SCHEMA.COLUMNS AS t1 LEFT JOIN INFORMATION_SCHEMA.TABLES t6 ON ((t6.TABLE_SCHEMA = t1.TABLE_SCHEMA) AND (t6.TABLE_NAME = t1.TABLE_NAME)) WHERE (t1.TABLE_NAME = '{0}') AND (t1.TABLE_SCHEMA = '{1}') ORDER BY t1.TABLE_NAME, t1.ORDINAL_POSITION ", tableName_in, _database ); #endregion break; #endif default: throw new Exception(string.Format( "{0}.{1}.getTableFields: - not implemented", this.GetType().Namespace, this.GetType().Name )); } _dtemp = Execute_SQLQuery_returnDataTable(_query); } else { _dtemp = Execute_SQLFunction_returnDataTable( sqlFuncion_in, new IDbDataParameter[] { newDBDataParameter("tableName_", DbType.String, ParameterDirection.Input, tableName_in, tableName_in.Length) } ); } #endregion getTableFields_out = new cDBTableField[_dtemp.Rows.Count]; for (int r = 0; r < _dtemp.Rows.Count; r++) { getTableFields_out[r] = new cDBTableField(); getTableFields_out[r].Name = (string)_dtemp.Rows[r]["Name"]; switch (dbservertype_) { #if MySQL case eDBServerTypes.MySQL: getTableFields_out[r].Size = (_dtemp.Rows[r]["Size"] == DBNull.Value) ? 0 : (int)(long)_dtemp.Rows[r]["Size"]; getTableFields_out[r].isNullable = ((long)_dtemp.Rows[r]["isNullable"] == 1L); //--- getTableFields_out[r].FK_TableName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_TableName"]; getTableFields_out[r].FK_FieldName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_FieldName"]; //--- getTableFields_out[r].isIdentity = ((long)_dtemp.Rows[r]["isIdentity"] == 1L); getTableFields_out[r].isPK = ((long)_dtemp.Rows[r]["isPK"] == 1L); //--- getTableFields_out[r].Numeric_Precision = (_dtemp.Rows[r]["Numeric_Precision"] == DBNull.Value) ? 0 : (int)(long)_dtemp.Rows[r]["Numeric_Precision"]; getTableFields_out[r].Numeric_Scale = (_dtemp.Rows[r]["Numeric_Scale"] == DBNull.Value) ? 0 : (int)(long)_dtemp.Rows[r]["Numeric_Scale"]; break; #endif #if PostgreSQL case eDBServerTypes.PostgreSQL: getTableFields_out[r].Size = (_dtemp.Rows[r]["Size"] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r]["Size"]; getTableFields_out[r].isNullable = ((int)_dtemp.Rows[r]["isNullable"] == 1); //--- getTableFields_out[r].FK_TableName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_TableName"]; getTableFields_out[r].FK_FieldName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_FieldName"]; //--- getTableFields_out[r].isIdentity = ((int)_dtemp.Rows[r]["isIdentity"] == 1); getTableFields_out[r].isPK = ((int)_dtemp.Rows[r]["isPK"] == 1); //--- getTableFields_out[r].Numeric_Precision = (_dtemp.Rows[r]["Numeric_Precision"] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r]["Numeric_Precision"]; getTableFields_out[r].Numeric_Scale = (_dtemp.Rows[r]["Numeric_Scale"] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r]["Numeric_Scale"]; break; #endif case eDBServerTypes.SQLServer: { getTableFields_out[r].Size = (_dtemp.Rows[r]["Size"] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r]["Size"]; getTableFields_out[r].isNullable = ((int)_dtemp.Rows[r]["isNullable"] == 1); //--- getTableFields_out[r].FK_TableName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_TableName"]; getTableFields_out[r].FK_FieldName = (_dtemp.Rows[r]["FK_FieldName"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["FK_FieldName"]; //--- getTableFields_out[r].isIdentity = ((int)_dtemp.Rows[r]["isIdentity"] == 1); getTableFields_out[r].isPK = ((int)_dtemp.Rows[r]["isPK"] == 1); //--- getTableFields_out[r].Numeric_Precision = (_dtemp.Rows[r]["Numeric_Precision"] == DBNull.Value) ? 0 : (int)(byte)_dtemp.Rows[r]["Numeric_Precision"]; getTableFields_out[r].Numeric_Scale = (_dtemp.Rows[r]["Numeric_Scale"] == DBNull.Value) ? 0 : (int)_dtemp.Rows[r]["Numeric_Scale"]; break; } default: { throw new Exception( string.Format( "{0}.{1}.getTables: - not implemented", this.GetType().Namespace, this.GetType().Name ) ); } } //--- #region //getTableFields_out[r].DBType_inDB_name = ...; //switch (dbservertype_) { // case eDBServerTypes.SQLServer: // getTableFields_out[r].DBType_inDB = (int)sConvert.SqlDbType_Parse((string)_dtemp.Rows[r]["Type"], false); // break; // case eDBServerTypes.PostgreSQL: // getTableFields_out[r].DBType_inDB = (int)sConvert.PgsqlDbType_Parse((string)_dtemp.Rows[r]["Type"]); // break; //} #endregion getTableFields_out[r].DBType_inDB_name = (string)_dtemp.Rows[r]["Type"]; getTableFields_out[r].DBDefaultValue = (_dtemp.Rows[r]["COLUMN_DEFAULT"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["COLUMN_DEFAULT"]; getTableFields_out[r].DBCollationName = (_dtemp.Rows[r]["COLLATION_NAME"] == DBNull.Value) ? "" : (string)_dtemp.Rows[r]["COLLATION_NAME"]; // ToDos: here! getTableFields_out[r].DBDescription = string.Empty; } _dtemp.Dispose(); _dtemp = null; return getTableFields_out; }