How to get aliased column names for dynamic InsertCommand?

C

CDoze

Hi all,

Hope someone can help me with this one. I am currently using
c#/ado.net 1.1 with SQL 2000.

My problem is I cannot get the actual column name from a stored
procedure result set with aliased columns. I have a stored procedure
which returns a simple result set like follows...

SELECT rptReportCd AS 'Report Code',
rptFileNm AS 'File Name',
rptDisplayNm AS 'Display Name',
rptType AS 'Report Type',
rptShowTotals AS 'Show Totals'
FROM rptReport

I tested using a commandbuilder and the correct field names are
referenced in my InsertCommand (rptReportCd, rptFileNm...)

However if I try and generate the InsertCommand dynamically by using a
datareader and the GetSchemaTable method the column name and base
column name in my datareader both have the same name (Report Code,
File Name...) so I cannot get the actual table column name for my
insertcommand.

I need INSERT INTO [rptReport] (rptReportCd, rptFileNm...)
not INSERT INTO [rptReport] (Report Code, File Name...)

How can I get the actual column name, I thought it should be returned
in the datareader BaseColumnName field from the GetSchemaTable method
call. Is there another way to get the column name or am I doing
something wrong with my GetSchemaTable call???

FYI...I cannot use a CommandBuilder even if I wanted to, because I am
attaching to several different databases Sql and Oracle using a
generic data access layer and there is no interface for the
CommandBuilder so I cannot use it with an IDBDataAdapter.

Here is an example of the code I am using to get the column names

// create generic dataaccess factory object which will be used to
create provider specific dal
DataAccessFactory daFactory = new DataAccessFactory();

// create generic dal using IData interface
genericDal = (IData)daFactory.getDataAccessObject(providerType);

// create object array to pass dataconnect value into data access
layer
object[] oConnection = {dataConnection};

// create connection object
this.dbConnection = genericDal.Connection(oConnection);

this.appDataTable = new DataTable();

//Set the command used to retrieve table data
this.dbCommand = dbConnection.CreateCommand();
this.dbCommand.Connection = this.dbConnection;

// set up stored procedure call
this.dbCommand.CommandType = CommandType.StoredProcedure;
this.dbCommand.CommandText = storedProcedure;

// create data adapter
this.dbDataAdapter = genericDal.DataAdapter();
this.dbDataAdapter.SelectCommand = this.dbCommand;

this.dbDataAdapter.TableMappings.Add(tableName,tableName);
this.dbDataAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
....

// get schema info based on sp table mappings
IDataReader schemaReader =
genericDal.DataReader(dbCommand,CommandBehavior.SchemaOnly);

DataTable schemaTable = schemaReader.GetSchemaTable();

....
foreach (DataRow dr in schemaTable.Rows)
{
Console.WriteLine(dr["ColumnName"]);
Console.WriteLine(dr["IsKey"]);
Console.WriteLine(dr["BaseColumnName"]);
} }


Thanks in advance for any help
Chris
 
F

Fredrik Wahlgren

CDoze said:
Hi all,

Hope someone can help me with this one. I am currently using
c#/ado.net 1.1 with SQL 2000.

My problem is I cannot get the actual column name from a stored
procedure result set with aliased columns. I have a stored procedure
which returns a simple result set like follows...

SELECT rptReportCd AS 'Report Code',
rptFileNm AS 'File Name',
rptDisplayNm AS 'Display Name',
rptType AS 'Report Type',
rptShowTotals AS 'Show Totals'
FROM rptReport

I tested using a commandbuilder and the correct field names are
referenced in my InsertCommand (rptReportCd, rptFileNm...)

However if I try and generate the InsertCommand dynamically by using a
datareader and the GetSchemaTable method the column name and base
column name in my datareader both have the same name (Report Code,
File Name...) so I cannot get the actual table column name for my
insertcommand.

I need INSERT INTO [rptReport] (rptReportCd, rptFileNm...)
not INSERT INTO [rptReport] (Report Code, File Name...)

How can I get the actual column name, I thought it should be returned
in the datareader BaseColumnName field from the GetSchemaTable method
call. Is there another way to get the column name or am I doing
something wrong with my GetSchemaTable call???

FYI...I cannot use a CommandBuilder even if I wanted to, because I am
attaching to several different databases Sql and Oracle using a
generic data access layer and there is no interface for the
CommandBuilder so I cannot use it with an IDBDataAdapter.

Here is an example of the code I am using to get the column names

// create generic dataaccess factory object which will be used to
create provider specific dal
DataAccessFactory daFactory = new DataAccessFactory();

// create generic dal using IData interface
genericDal = (IData)daFactory.getDataAccessObject(providerType);

// create object array to pass dataconnect value into data access
layer
object[] oConnection = {dataConnection};

// create connection object
this.dbConnection = genericDal.Connection(oConnection);

this.appDataTable = new DataTable();

//Set the command used to retrieve table data
this.dbCommand = dbConnection.CreateCommand();
this.dbCommand.Connection = this.dbConnection;

// set up stored procedure call
this.dbCommand.CommandType = CommandType.StoredProcedure;
this.dbCommand.CommandText = storedProcedure;

// create data adapter
this.dbDataAdapter = genericDal.DataAdapter();
this.dbDataAdapter.SelectCommand = this.dbCommand;

this.dbDataAdapter.TableMappings.Add(tableName,tableName);
this.dbDataAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
...

// get schema info based on sp table mappings
IDataReader schemaReader =
genericDal.DataReader(dbCommand,CommandBehavior.SchemaOnly);

DataTable schemaTable = schemaReader.GetSchemaTable();

...
foreach (DataRow dr in schemaTable.Rows)
{
Console.WriteLine(dr["ColumnName"]);
Console.WriteLine(dr["IsKey"]);
Console.WriteLine(dr["BaseColumnName"]);
} }


Thanks in advance for any help
Chris
Click to expand...

If you do something like SELECT * From SomeTable WHERE 1 = 0 you will get
the column names for the SomeTable table.

/Fredrik
 
C

CDoze

Hi all,

Hope someone can help me with this one. I am currently using
c#/ado.net 1.1 with SQL 2000.

My problem is I cannot get the actual column name from a stored
procedure result set with aliased columns. I have a stored procedure
which returns a simple result set like follows...

SELECT rptReportCd AS 'Report Code',
rptFileNm AS 'File Name',
rptDisplayNm AS 'Display Name',
rptType AS 'Report Type',
rptShowTotals AS 'Show Totals'
FROM rptReport

I tested using a commandbuilder and the correct field names are
referenced in my InsertCommand (rptReportCd, rptFileNm...)

However if I try and generate the InsertCommand dynamically by using a
datareader and the GetSchemaTable method the column name and base
column name in my datareader both have the same name (Report Code,
File Name...) so I cannot get the actual table column name for my
insertcommand.

I need INSERT INTO [rptReport] (rptReportCd, rptFileNm...)
not INSERT INTO [rptReport] (Report Code, File Name...)

How can I get the actual column name, I thought it should be returned
in the datareader BaseColumnName field from the GetSchemaTable method
call. Is there another way to get the column name or am I doing
something wrong with my GetSchemaTable call???

FYI...I cannot use a CommandBuilder even if I wanted to, because I am
attaching to several different databases Sql and Oracle using a
generic data access layer and there is no interface for the
CommandBuilder so I cannot use it with an IDBDataAdapter.

Here is an example of the code I am using to get the column names

// create generic dataaccess factory object which will be used to
create provider specific dal
DataAccessFactory daFactory = new DataAccessFactory();

// create generic dal using IData interface
genericDal = (IData)daFactory.getDataAccessObject(providerType);

// create object array to pass dataconnect value into data access
layer
object[] oConnection = {dataConnection};

// create connection object
this.dbConnection = genericDal.Connection(oConnection);

this.appDataTable = new DataTable();

//Set the command used to retrieve table data
this.dbCommand = dbConnection.CreateCommand();
this.dbCommand.Connection = this.dbConnection;

// set up stored procedure call
this.dbCommand.CommandType = CommandType.StoredProcedure;
this.dbCommand.CommandText = storedProcedure;

// create data adapter
this.dbDataAdapter = genericDal.DataAdapter();
this.dbDataAdapter.SelectCommand = this.dbCommand;

this.dbDataAdapter.TableMappings.Add(tableName,tableName);
this.dbDataAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
...

// get schema info based on sp table mappings
IDataReader schemaReader =
genericDal.DataReader(dbCommand,CommandBehavior.SchemaOnly);

DataTable schemaTable = schemaReader.GetSchemaTable();

...
foreach (DataRow dr in schemaTable.Rows)
{
Console.WriteLine(dr["ColumnName"]);
Console.WriteLine(dr["IsKey"]);
Console.WriteLine(dr["BaseColumnName"]);
} }


Thanks in advance for any help
Chris
Click to expand...


If I look at the DataAdapter in the watch window at runtime I can see
the values I want under the following nodes

dbDataAdapter.SelectCommand.[System.Data.SqlClient.SqlCommand]._cachedMetaData[0].[System.Data.SqlClient.SqlMetaData].baseColumn

dbDataAdapter.SelectCommand.[System.Data.SqlClient.SqlCommand]._cachedMetaData[0].[System.Data.SqlClient.SqlMetaData].isKey

dbDataAdapter.SelectCommand.[System.Data.SqlClient.SqlCommand]._cachedMetaData[0].[System.Data.SqlClient.SqlMetaData].tableName

Anyone know how I can reference these values or any other way to get
them?
Again I am looking to build a dynamic insert, update, or delete
command from a stored procedure with fields that may be aliased, where
I may not know the table or fields being returned


Thanks again
Chris
 
D

David Sceppa

Chris,

To request this additional schema information, include
CommandBehavior.KeyInfo in the call to ExecuteReader. You should then see
the base column name in the DataTable returned by DataReader.GetSchemaTable.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

datagridview cell - get row/column info for updating data- how? 3
SqlServer: How to know if a a column is a primary key ? 1
Get input from spreadsheet control to server for processing 1
DataColumnChangeEventHandler how to get value before change? 1
How to create menu for listed names? 3
Get column names from Access crosstab query via ADO? 3
how to get the column names from the gridview 2
How to get ValueType default for DBNull column using reflection? 2

Top