Need Oracle Column Names

[Luc Pattyn]

Hi, I don't know for Oracle, however I did similar things with OLEDB for Access, basically it consisted of: - opening a connection - creating an OleDbCommand with the appropriate SQL, probably SELECT * FROM tableName - getting a OleDbDataReader by executing OleDbCommand.ExecuteReader (CommandBehavior.SchemaOnly should suffice); - getting the table's schema from OleDbDataReader.GetSchemaTable. - cleaning up. The schema itself is a DataTable that holds all the field information. You'll have to work out the details and try it on Oracle. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Mycroft Holmes]

I have the script in the office that gets the table names of tables and views - if you don't have an answer I'll post it tomorrow.

Never underestimate the power of human stupidity RAH

[PDTUM]

Thank You RAH, I am going to try Luc's Schema solution later and see if it works, but I would still like to have a look at your proven solution. A post tomorrow, as well as your reply today, is much appreciated. Thank You, Pat

[PDTUM]

Thank you for your input Luc. I recall that you have supplied me with some other solutions in times past and I appreciate your input. I will try your scripting later or tomorrow as time permits and post it here if it works. Thanks again...Pat

[Luc Pattyn]

You're welcome. In the mean time I found a matching code snippet:

using (OleDbConnection con=new OleDbConnection(connectionString)) {
con.Open();
using (OleDbCommand cmd=new OleDbCommand("SELECT * FROM ["+tableName+"]", con)) {
// ref: http://www.pcreview.co.uk/forums/getschema-columns-results-t2877051.html
DbDataReader rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema=rdr.GetSchemaTable();
foreach (DataColumn col in schema.Columns) log("colName="+col.ColumnName);
}
}

:)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[A J Wegierski]

Another trouble: if there are more than one table visible with thesame name (in other schemas for instance) you got mixed output from those tables. Try use user_tab_columns instead or add next condition to WHERE clause

AND owner = 'myowner'

where 'myowner' is owner name.

--- AW

[Lost User]

I think I found out your problem. Your select statement is correct and should return the names of all columns in your table. You can verify this by running the query in a client tool like SQL * Plus or TOAD. It's how you bind the data table to the ListView that makes the difference. Instead of querying the DataTable's columns, you should query its rows because the column information is returned as rows. Try this:

foreach (DataRow row dt.Rows) {
listViewColumns.Items.Add(row[0].ToString());
}

modified on Sunday, August 28, 2011 6:22 AM

[PIEBALDconsult]

As Luc said, use a DataReader; it always* works, no matter the database system**, and you can use it for queries as well as tables. Plus it can tell you the proper .net datatype that maps to the table column. :thumbsup: * Well, I haven't yet seen a situation in which it doesn't. ** I haven't tried it with XML, but I don't consider that a database system. I suppose, if this doesn't work, then the target must not be a database system. :-D

[PDTUM]

YES YES YES...Thank you, that did it! I really need to have a better understanding of what is really going on in these ADO calls. Now I see the column information better since you explained that the columns are returned as rows. I have posted my final code below for your perusal and for the many others that I have accidentally found while trying to resolve this. Thanks again...Pat

string Sql = "select column_name from all_tab_columns where table_name = '" + sSelectedTable + "' order by column_id";
OleDbCommand Comm = new OleDbCommand(Sql, Conn);

                    listViewColumns.Items.Clear();

                    try
                    {
                        //List the schema info for the selected table
                        OleDbDataAdapter da = new OleDbDataAdapter(Sql, Conn);
                        DataTable dt = new DataTable();
                        da.Fill(dt);

                        foreach (DataRow row in dt.Rows) 
                        {
                            listViewColumns.Items.Add(row\[0\].ToString());
                        }
                    }

[PDTUM]

Luc, A minute of my time to thank you for your time. I received this solution (below) from Mr Holmes that is short and sweet. I am sure that if I had a better understanding of the "schema" calls, this would have worked as well, but when I ran it, it placed all the schema headings into the listview. I am sure there is some small argument that would fix it, and I should look nto it as this might help with some other calls. Thank you for your help...I hope that you will answer my 'other issues' as they come up. I appreciate your time and skill...Pat

[PDTUM]

Thank you...I have worked with Luc before and he has helped me a lot, but the solution that worked perfectly this time is posted above by Mr Holmes. I thought you might want to note it for future use. Thank you for your input..Best Regards, Pat

[Lost User]

PDTUM wrote:

Mr Holmes Shameel

FTFY :-)

[PDTUM]

Mr Shameel, PLEASE accept my apology...sometimes I write before I think. MANY thanks for the great and simple solution. I hope that you will want to respond to me the next time (and I am sure there will be one)...Best Regards, Pat

[PIEBALDconsult]

No, I most certainly do not; I very much prefer a database-agnostic solution when one is available and especially when such is better (more flexible, more informative) than the database-specific one.

[Mycroft Holmes]

Not sure if this is relevant anymore but here is the code that gets the tables and views from an oracle database. DBOpsO creates a valid oracle connection and executes a sql string to return a datatable.

		StringBuilder sSQL = new StringBuilder();
		TableDB oTableDB;
		sSQL.AppendLine("select T.owner,T.table\_name, NVL (V.HasView, 0)HasView");
		sSQL.AppendLine("From SYS.ALL\_TABLES T ");
		sSQL.AppendLine("left join (select owner,view\_name, 1 HasView from SYS.ALL\_VIEWS where owner in ('SchemaName1','SchemaName2')) V on upper(V.View\_Name) = 'VW' || T.table\_name ");
		sSQL.AppendFormat("and V.owner = T.owner").AppendLine();
		sSQL.AppendFormat("where T.owner in ({0})", sSchema.ToString()).AppendLine();
		sSQL.AppendLine("order by table\_name");			

		if (oDBOpsO == null || oDBOpsO.Creds.Database != oDB.DatabaseName)
		{
			oDBOpsO = clsMain.GetDBOpsO(oDB.Server.ServerName, oDB.DatabaseName);
		}

		DataTable dtData = oDBOpsO.GetTableSQL(sSQL.ToString());

		TableDB oTable = new TableDB();
		List lTbl = new List();
		foreach (DataRow orow in dtData.Rows)

Note I have a convention that all views are prefixed with 'vw'

Never underestimate the power of human stupidity RAH

[Lost User]

PDTUM wrote:

PLEASE accept my apology...

no problem.

PDTUM wrote:

I hope that you will want to respond to me the next time

Sure, why not?