chlade
asked on
determine table name
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
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
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.SomeFiel d As SA_SomeField ....
so that you know which Table it corresponds to, using the first 2 Chars of the Column Name.
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
Select SupplierMaster.Field1 As SM_Field1, SupplierMaster.Field2 As SM_Field2, SupplierAddresses.SomeFiel
so that you know which Table it corresponds to, using the first 2 Chars of the Column Name.
ASKER
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.
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.
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
Try it, and let me know... If it doesn't work, then we can try and figure something out that will work.
Jake
ASKER
I did, but it didn't look like it would work. I have to run to a meeting but will post my code later.
ASKER
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.SqlC onnection( _
"Server=Phoenix;Database=C ompanyA;Ui d=SysproUs er;Pwd=123 ;")
cn.Open()
Dim cmd As New System.Data.SqlClient.SqlC ommand(Sql String, cn)
Dim dr As System.Data.SqlClient.SqlD ataReader = 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).ToStrin g() & ControlChars.Tab
Next
msg &= ControlChars.CrLf
Next
cn.Close()
Clipboard.Clear()
Clipboard.SetText(msg)
MessageBox.Show("Informati on copied to clipboard")
End Sub
End Module
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.SqlC
"Server=Phoenix;Database=C
cn.Open()
Dim cmd As New System.Data.SqlClient.SqlC
Dim dr As System.Data.SqlClient.SqlD
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).ToStrin
Next
msg &= ControlChars.CrLf
Next
cn.Close()
Clipboard.Clear()
Clipboard.SetText(msg)
MessageBox.Show("Informati
End Sub
End Module
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
After digging a little more, unfortunately, I must agree. It looks like I'll have to use a work-around of some sort.
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