identifying tables

[studentoflife]

I return 30 tables or so from sql in a stored procedure.

In the stored procedure I select these tables via...

select * from myTable1
select * from myTable2
select * from myTable3

etc.

These tables lose their sql table name when returned in a dataset and have default names of Table, Table1, Table2 etc.

So how do I identify a particular table out of the now 30 'random' tables in my dataset.

Thank you.

[jockeyvn]

hello,

Try to thinking about this simple way by my idea, hope to help you

1. declare array including number 1..30 (number of your table
2. Get randomize number from 1..30
3. declare a string to use as sql string, and replace this number to sql string
example : "select * from table" + n

[Mitja Bonca]

I hope you are talking aobur DataTables. Simple name them by some uniqe name. If there is no other way, you can use numbers from 1 to 30 (as jockeyvn proposed), or use the name from sql table (table1, table2,table3 - if they are all different). Or use more sql table names, if there is any dataTable which has two or more sql tables (like: "... FROM Table1, Table2" - name dataTable as :Table1_Table2.

Remember its always good to name dataTables to something that you know what is in it (where it comes from). It easier to work. Thats why I would not recommend you to use numbers only.

[studentoflife]

Hi,

Thanks for the reply but I fear you have completely misunderstood what I was asking.

I do not want a random table.

I have 30 tables of which I want to find a table by name.

e.g. if I have 30 DataTables in a DataSet, all returned from sql with default names of Table, Table1, Table2... etc.

How do I find the table called table_passenger_details

[Mitja Bonca]

Simple. YOu simply add a name in the brackets of the ds.Tables:

DataTable myTable = ds.Tables["table_passenger_details"];

[studentoflife]

Simple. YOu simply add a name in the brackets of the ds.Tables:

DataTable myTable = ds.Tables["table_passenger_details"];

Simple... I thought so also.

But when my DataSet containing the DataTables are returned via stored procedure ds.Tables["table_passenger_details"] does not exist, the tables have lost their names and are now called Table, Table1, Table2 etc.

I then can't find a table by table name (which I need to) because they have been renamed.

Any ideas?

[Mitja Bonca]

How did your tables loose their names? Cant be?
If you do it right, the names will for sure stay (be there from the time of creation on...

YOu have to do code in C#, (create DataTable as well) and only call a storedProcedure (which only can include SELECT statement, nothing else), and fill in up:

DataSet ds = new DataSet();
        public Form1()
        {
            InitializeComponent();
        }

        private void GetData()
        {
            //DO THE SAME FOR EVERY SINLGE DATATABLE, LIKE THIS EXAMPLE:
            using (SqlConnection sqlConn = new SqlConnection("connString"))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "YourProcedureName";
                cmd.Connection = sqlConn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable("MyTable1");
                ds.Tables.Add(table);
                da.Fill(table);
            }
        }

        private void ReadData()
        {
            DataTable table = ds.Tables["MyTable1"];
            foreach (DataRow dr in table.Rows)
            {
                //code to get each value form dataTable goes here
            }
        }

If you will do anything different, you will surely look tables, or something else.

[studentoflife]

My code is as follows

SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = 'spName'; //this one sp returns many tables and pk/fks
            command.Connection = connection;
                           
            // I ADD PARAMETERS HERE

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataSet ds = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(ds);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                connection.Close();
            }

the stored procedure, simply returns multiple tables (the schema), by calling 'select * from' on each table

e.g.

Select * from tbl_passenger_details
Select * from tbl_airport_details
Select * from tbl_airline_details

For some reason these tables are returned as expected into the DataSet but with renamed TableNames???

[Mitja Bonca]

Sure, you do not specify the table names. Look into my code, how I did it. I did named the dataTable, so it has a name, your do not have, because you fill dataTables (all togeter) in one stored procedure.

One more thing: the SqlDataAdapter will not look at the physical table names in your database to determine the table names in the ADO.NET DataSet. Sorry, there's really no way to do this automagically.

Why dont you create 3 sepersted stored procedures and use the code I did?

[jockeyvn]

Hello studentoflife.

Mitja Bonca's code is standard, and you clear that when you select many tables without relationship, they will return multi group of rows, return each data of table with their group.
And you will see difficult for read to dataset. Otherwise, you can use View instead SProcedure.

[studentoflife]

Sure, you do not specify the table names. Look into my code, how I did it. I did named the dataTable, so it has a name, your do not have, because you fill dataTables (all togeter) in one stored procedure.

One more thing: the SqlDataAdapter will not look at the physical table names in your database to determine the table names in the ADO.NET DataSet. Sorry, there's really no way to do this automagically.

Why dont you create 3 sepersted stored procedures and use the code I did?

Thank you Mitja Bonca.

What I have now done is created a method/sp that gets a list of the system tables.

Then another method that loops around each table name and calls a select * from it, also naming the DataTable.

Basically put your code into a loop, but it is dynamic which is what I wanted.

I have been stuck on this for three days so thank you so much.