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