how to extract table name from SqlDataReader

  • Thread starter Thread starter yamini
  • Start date Start date
Y

yamini

Hi,
I get a query from out side of my program. then I have to execute
and create an xml file. To put in xml file I need to know the table
name.
I am able to get field names and field types, values but how to get
the table name of that field?.
one option is parse the input sql query and get string after
"from". but if query is on multiple tables then how to know which
field from which table?

Please help me.

(e-mail address removed)
 
Yamini:

I'm not sure what youare using to fire the query, but if you're using a
DataReader for instance, you can use the GetSchemaTable method and there's a
property BaseTableName as well as ColumName...used together you can find out
the column name of each column in the query (name or alias in the DB) as
well as the table it belongs to. SO if you have a join, you can use
BaseTableName to find out who each column belongs to. If you don't have a
join, then BaseTableName will be the same for all columns and you only need
to reference it once to find out the table name. I'm assuming you are using
a DataReader which makes it very easy and since you aren't walking through
the records, it's very efficient. You want to make sure you use the KeyInfo
enum though. Here's the code to do it::

Dim sql As String = "SELECT TOP 100 PERCENT
dbo.Tbl_Facilities.Dept_Number, dbo.Tbl_Facilities.Facility,
dbo.Tbl_Work_Description.Work_Type, dbo.Tbl_Work_Description.STAT,
dbo.Tbl_Work_Description.RAD, dbo.Tbl_Work_Description.ECHOS,
dbo.Tbl_Work_Description.Work_Type_Desc, dbo.Tbl_Work_Description.TAT,
dbo.Tbl_Work_Description.FTP_Site, dbo.Tbl_Work_Description.Delivery_Method
FROM dbo.Tbl_Work_Description CROSS JOIN dbo.Tbl_Facilities ORDER BY
dbo.Tbl_Facilities.Dept_Number"
Dim cmd As New SqlCommand(sql, cn)
Dim dr As SqlDataReader
cn.Open()
dr = cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim dt As New DataTable
dt = dr.GetSchemaTable
For i As Integer = 0 To dt.Rows.Count - 1
Debug.WriteLine(dt.Rows(i)("ColumnName").ToString & " " &
dt.Rows(i)("BaseTableName").ToString)
Next
cn.Close()

Here's the output, although I got column and table name backward:
Dept_Number Tbl_Facilities
Facility Tbl_Facilities
Work_Type Tbl_Work_Description
STAT Tbl_Work_Description
RAD Tbl_Work_Description
ECHOS Tbl_Work_Description
Work_Type_Desc Tbl_Work_Description
TAT Tbl_Work_Description
FTP_Site Tbl_Work_Description
Delivery_Method Tbl_Work_Description
KEYA Tbl_Work_Description
The program '[19192] WindowsApplication1.exe' has exited with code 0 (0x0).

I think this is what you want b/c even if you don't have a join you can
still use BaseTableName. If you do have a join with two or more tables,
you'll still be able to determine them all.

HTH,

Bill
 
Back
Top