This source code shows how to get all database tables and their columns from an Oracle database using Oracle .NET Data Provider available in the .NET Framework class library.
To run this sample, you need to add your connection string to the following line:
OracleConnection connection = new OracleConnection("Connection String");
Here is the complete source code:
using System;
using System.Data.OracleClient;
using System.Windows.Forms;
using System.Data;
Public Class Query : System.Windows.Forms.Form
{
System.Windows.Forms.ListBox lstColumns;
System.Windows.Forms.ListBox lstTables;
Public Query()
{
BindTables();
}
//Fill tables and columns into the listboxes
Private void BindTables()
{
lstTables.Items.Clear();
//Execute the query.
try{
OracleConnection connection = new OracleConnection("Connection String");
OracleDataAdapter adap = new OracleDataAdapter ("select * from tabs", connection);
DataTable dt = new DataTable();
adap.Fill(dt);
//Display the table name from each row in the schema
foreach (DataRow row In dt.Rows)
lstTables.Items.Add(row("Table_Name"));
}
catch(Exception ex)
{
MessageBox.Show("Error" + ex.Message, "Error");
}
}
//Table listBox select event to change columns in the colum listbox accordingly
Private void lstTables_SelectedIndexChanged(object sender, System.EventArgs e )
{
lstColumns.Items.Clear();
//Select a table name.
string selTbl = lstTables.SelectedItem.ToString();
try
{
//List the schema info for the selected table
OracleDataAdapter adap = new OracleDataAdapter("select column_name from user_tab_columns where table_name = '" + selTbl + "' order by column_id", connection);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow row In dt.Rows)
{lstColumns.Items.Add(row("column_name"));}
}
catch( Exception ex)
{MessageBox.Show("Error" + ex.Message, "Error");}
//Label for Column Names
lblListColumn.Text = "Column Names in " + selTbl + " Table";
}
}