How to get all tables from Database?

  • Thread starter Thread starter Kruno Milicevic
  • Start date Start date
K

Kruno Milicevic

How to fill DataSet with ALL tables from database without naming each of
them in sqlString for myOleDbCommand.Commandtext...
Is there some simple sql expresion like this:

string selectString="SELECT someColumns FROM AllTablesFromMyDataBase"

Or must i try to find solution written with c#-code (something with DataSet
and similar)?
Thanks....

P.S: Im asking this because i use database with lot of tables and I would
not write all tables in sqlString expresion...there are too many of them : (
Please, make my life simple...
 
Hi Kruno,

Generally no, there is no way to do this. The SQL query has to tell
the database engine explicitly what tables to use and how to join
records between the tables.

A tool like the VS.NET query designer can build this string for you,
and some database engines will have views and stored procedures to
hide what tables you are using, but somewhere in the chain of events
will be a SQL statement with all the table names explicitly spelled
out.
 
taken from
http://samples.gotdotnet.com/quicks...onalData.src&file=CS\relationaldata.cs&font=3

in a nutshell, just use one connection, one DataSet, and multiple
DataAdapters.

using System;
using System.Data;
using System.Data.SqlClient;

public class relationaldata
{
public static void Main()
{
relationaldata myrelationaldata = new relationaldata();
myrelationaldata.Run();
}

public void Run()
{
DataSet myDataSet = new DataSet();


SqlConnection myConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northw
ind");
SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from
customers", myConnection);
SqlDataAdapter mySqlDataAdapter2 = new SqlDataAdapter("select * from
orders", myConnection);

try
{
mySqlDataAdapter1.Fill(myDataSet,"Customers");
mySqlDataAdapter2.Fill(myDataSet,"Orders");

// ADD RELATION

myDataSet.Relations.Add("CustOrders",myDataSet.Tables["Customers"].Columns["
CustomerId"],myDataSet.Tables["Orders"].Columns["CustomerId"]);

// Iterate over data of Customers and their orders
foreach (DataRow myDataRow1 in myDataSet.Tables["Customers"].Rows)
{
Console.WriteLine("Customer: " +
myDataRow1["ContactName"].ToString());

// Iterate over orders data.
foreach (DataRow myDataRow2 in
myDataRow1.GetChildRows(myDataSet.Relations["CustOrders"]))
{
Console.WriteLine("Order # " + myDataRow2["OrderId"].ToString());
}

Console.WriteLine();
}
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
}
}
 
Even with this approach you have to explicitly name all the tables.

--
Scott

taken from
http://samples.gotdotnet.com/quicks...onalData.src&file=CS\relationaldata.cs&font=3

in a nutshell, just use one connection, one DataSet, and multiple
DataAdapters.

using System;
using System.Data;
using System.Data.SqlClient;

public class relationaldata
{
public static void Main()
{
relationaldata myrelationaldata = new relationaldata();
myrelationaldata.Run();
}

public void Run()
{
DataSet myDataSet = new DataSet();


SqlConnection myConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northw
ind");
SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from
customers", myConnection);
SqlDataAdapter mySqlDataAdapter2 = new SqlDataAdapter("select * from
orders", myConnection);

try
{
mySqlDataAdapter1.Fill(myDataSet,"Customers");
mySqlDataAdapter2.Fill(myDataSet,"Orders");

// ADD RELATION

myDataSet.Relations.Add("CustOrders",myDataSet.Tables["Customers"].Columns["
CustomerId"],myDataSet.Tables["Orders"].Columns["CustomerId"]);

// Iterate over data of Customers and their orders
foreach (DataRow myDataRow1 in myDataSet.Tables["Customers"].Rows)
{
Console.WriteLine("Customer: " +
myDataRow1["ContactName"].ToString());

// Iterate over orders data.
foreach (DataRow myDataRow2 in
myDataRow1.GetChildRows(myDataSet.Relations["CustOrders"]))
{
Console.WriteLine("Order # " + myDataRow2["OrderId"].ToString());
}

Console.WriteLine();
}
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
}
}
Click to expand...
 
¤ How to fill DataSet with ALL tables from database without naming each of
¤ them in sqlString for myOleDbCommand.Commandtext...
¤ Is there some simple sql expresion like this:
¤
¤ string selectString="SELECT someColumns FROM AllTablesFromMyDataBase"
¤
¤ Or must i try to find solution written with c#-code (something with DataSet
¤ and similar)?
¤ Thanks....
¤
¤ P.S: Im asking this because i use database with lot of tables and I would
¤ not write all tables in sqlString expresion...there are too many of them : (
¤ Please, make my life simple...
¤

I don't know what kind of database you are using but you should be able to use GetOleDbSchemaTable:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, Nothing})

Dim RowCount As Int32

For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
Next RowCount

DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top