?
Solved

determine table name

Posted on 2006-05-15
9
Medium Priority
?
235 Views
Last Modified: 2012-05-05
I am writing a routine that will be called from many programs.  I will pass into it a parameter that is a SQL command string.

For instance:
SELECT SupplierMaster.*, SupplierAddress.*
FROM SupplierMaster INNER JOIN SupplierAddresses ON SupplierMaster.Supplier = SupplierAddresses.Supplier"

My routine will display a grid showing all returned rows.  This all works great.

However, there is some additional code I'd like to add and for this I will need to know the table that each column comes from.

I will cycle through all returned fields and need to know the table the field is from.  For instance, in the query above, there will be a field called Supplier in each table.  For each instance of the Supplier field, I will need to know the table name that it is from.

Is there a way to find the name of the table a field is from?

Thanks,
Chris
0
Comment
Question by:chlade
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:jake072
ID: 16685764
Well If your cycling through your ROWS, and you have them in a DataSet, the easiest way to figure out what Table a given row belongs to is:

Dim dr As DataRow = [YourRow]
Dim dt As DataTable = dr.Table
Dim strTableName As String = dr.Table.TableName

Let me know if you need more help,

Jake
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16687981
Hi
As far as I know, you can't figure that out,(I might just be wrong). For the duplicate column "Supplier" though, the ColumnName property would be SupplierMaster.Supplier or SupplierAddresses.Supplier (with SQLClient or OLEDB, not sure for others) so you can know which underlying table the column is from, but not for others. What you can do is Modify your Query and use "Aliases" eg.
Select SupplierMaster.Field1 As SM_Field1, SupplierMaster.Field2 As SM_Field2, SupplierAddresses.SomeField As SA_SomeField ....
so that you know which Table it corresponds to, using the first 2 Chars of the Column Name.
0
 

Author Comment

by:chlade
ID: 16690299
Jake, unfortunately I will have joined tables populating a single row so I don't think that will work.

As for using aliases, I'm not sure that will work as I will need to select using "*".

I know that I can get field information such as type and size using dr.GetSchemaTable.  Unfortunately, it doesn't include the source table.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:jake072
ID: 16690489
Have you tried using my suggestion?

Try it, and let me know...  If it doesn't work, then we can try and figure something out that will work.

Jake
0
 

Author Comment

by:chlade
ID: 16690515
I did, but it didn't look like it would work.  I have to run to a meeting but will post my code later.
0
 

Author Comment

by:chlade
ID: 16701703
I guess where I got stuck is that my datarow (ie. a row in my dataset) is derived from more than one table.  I'm not sure how to copy this dataset row to a datarow (whether that's even possible) and if so, how it will know the table since the table appears to be at the datarow level.  Perhaps I'm looking at this wrong.

The following is a test program that dumps all information about a SQL Query into the clipboard.  Paste the code into a new project, run it, and then open Excel and do a paste.  Of course, you'll have to adjust to point at tables on your system.  Be sure to do a JOIN.  You'll see all information on each column.

However, it doesn't include the table name and this is what I'm after.

Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Sub Main()

        Dim SqlString As String = "SELECT ApSupplier.*, ApSupplierAddr.* " & _
            "FROM ApSupplier INNER JOIN ApSupplierAddr ON ApSupplier.Supplier = ApSupplierAddr.Supplier"

        Dim cn As New System.Data.SqlClient.SqlConnection( _
            "Server=Phoenix;Database=CompanyA;Uid=SysproUser;Pwd=123;")
        cn.Open()
        Dim cmd As New System.Data.SqlClient.SqlCommand(SqlString, cn)
        Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
        Dim dt As DataTable = dr.GetSchemaTable

        Dim msg As String = ""

        For y As Integer = 0 To dt.Columns.Count - 1
            msg &= dt.Columns(y).ColumnName & ControlChars.Tab
        Next
        msg &= ControlChars.CrLf

        For x As Integer = 0 To dt.Rows.Count - 1
            For y As Integer = 0 To dt.Columns.Count - 1
                msg &= dt.Rows(x).Item(y).ToString() & ControlChars.Tab
            Next
            msg &= ControlChars.CrLf
        Next

        cn.Close()

        Clipboard.Clear()
        Clipboard.SetText(msg)

        MessageBox.Show("Information copied to clipboard")

    End Sub

End Module
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 2000 total points
ID: 16706145
jake072 the Table Name property that you see for a DataRow is not the actual underlying Table's name, it is the name of your dataTable that can be different from the Database Table. Also in case of a single table Query you can get the Table Name but here we have Two tables and nowhere does the Dataset/DataTable store the mapping for each column to the underlying table in case of a Join Query, that is why the QueryBuilder is not able to generate Update/Insert etc. quries for a Join.
So the only option I see here is to change the Design of Data Access, if you can't use Aliases, then do not give a Join Query at all, select the two tables fully and put in two DataTables in a Datset and define a relation on the Key Column. I see only these two possible solutions to this problem.
0
 
LVL 14

Expert Comment

by:jake072
ID: 16708226
arif_eqbal,

Upon further investigation, your right.  With a JOIN, there's no way around it (at least nothing that is easy and obvious).  I would recommend your solution, it's the only thing that I could get to work last night.

Good luck,

Jake
0
 

Author Comment

by:chlade
ID: 16721080
After digging a little more, unfortunately, I must agree.  It looks like I'll have to use a work-around of some sort.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question