Use the ColumnName property in the DataColumn object. Here's an example:
// Populate the the row with the column names
foreach (DataColumn dcin dt.Columns)
{
_ColIndex++;
oSheet.Cells[_RowIndex, _ColIndex] = dc.ColumnName;
// Bold the column headings
Excel.Range oRange = (Excel.Range)oSheet.Cells[_RowIndex, _ColIndex];
oRange.Font.Bold =true;
}
|||You could execute a query, and pull them from the SysColumns tables:Select
SC.Name
From Syscolumns SC
inner join systypes ST on SC.xtype=ST.xtype
Where OBJECT_NAME(SC.id) = 'MyTableName'
|||Do i use the query as usaul commandtext?
because i get error massage- "invalid object name SC"|||
If you are using sql statements it is recommended that you use the schema views in the database instead of the sysobject tables directly. Here is an example that will output all of the table information for you(using the Northwind database):
declare @.tablename varchar(255)
select @.tablename = 'Employees'
SELECT INFORMATION_SCHEMA.COLUMNS.*,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename),
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')) AS IsComputed,
(SELECT COL_LENGTH(@.tablename, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)) AS ColumnLength,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'), 0)) AS IsPrimaryKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'FOREIGN KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME=@.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'), 0)) AS IsForeignKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'UNIQUE' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename
AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE'), 0)) AS HasUniqueConstraint
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.tablename
Thanks,
Tyler
|||this is the code:
PrivateSub settablecolumn()
Dim cmdAsNew SqlCommand()
Dim firstcolumnAsString
If oConnection.State = ConnectionState.ClosedThen oConnection.Open()
cmd.Connection = oConnection
cmd.CommandText = "Select L.Name() From Syscolumns L inner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '" & table.SelectedItem.Value & "'"
Dim readerAs SqlDataReader = cmd.ExecuteReader
If reader.Read =TrueThen
firstcolumn = reader.Item(0)
EndIf
oConnection.Close()
EndSub
great thanks...
cmd.CommandText = "Select L.Name From Syscolumns Linner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '"& table.SelectedItem.Value & "'"
If not, let me know what the text of the Error message is.
Thanks,
Tyler
|||working great now, thank you very much.
