How to get aliased column names for dynamic InsertCommand?

  • Thread starter Thread starter CDoze
  • Start date Start date
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
 
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
 
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
 
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.
 
Back
Top