How can I get as a table name by naming a query in the Stored Procedure ?

How can I get as a table name by naming a query in the Stored Procedure ?

• psk

  Kannan,
  
  Are Typed DataSets an option for you Here's how I handle this situation.
  
  1) Create an .xsd and a Typed DataSet.
  
  2) Sub-class this Typed DataSet and create properties that basically rename the tables.
  
  Here's an example of what I mean:
  
  

  [Serializable()]  [System.ComponentModel.DesignerCategoryAttribute("code")]  [System.Diagnostics.DebuggerStepThrough()]  [System.ComponentModel.ToolboxItem(true)]  public class MyDataSet : GeneratedTypedDataSet  {   #region Constructor   public MyDataSet(SerializationInfo info, StreamingContext context) : base(info, context)   {   }   public MyDataSet()   {   }   #endregion   #region Properties   [System.ComponentModel.Browsable(false)]   [System.ComponentModel.DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Content)]   public tableDataTable MyTableOne   {    get {return this.table;}   }   [System.ComponentModel.Browsable(false)]   [System.ComponentModel.DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Content)]   public Table1DataTable MyTableTwo   {    get {return this.Table1;}   }   [System.ComponentModel.Browsable(false)]   [System.ComponentModel.DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Content)]   public Table2DataTable MyTableThree   {    get {return this.Table2;}   }   #endregion  }

  
  
  

• UNODevelopment

  i'm trying to figure this out as well.
  
  I am in the same position, i have a stored procedure that returns two queries in the same dataTable.
  One is say customers and the other is their activity.
  Same problem, i don't just want to say dataset.Tables[0]  i should be able to go dataset.Table["Customer"] because what is going to happen when i update the query 6 months from now
  
  Databinding isn't really an option, as it gets you into problems where you should never query directly onto a Table, but you should be going through a view or stored procedure incase the underlying table changes.
  
  

• ToolmakerSteve

  I'll say it again ... Typed DataSets.   See my previous reply.
  
  

• Ahmed Essam

  Hi Phil,
  
  Thanks a lot for ur suggestion.
  
  My problem not yet solved . . .
  
  For example, I have two queries in the stored procedure and one returns the Customers and another query returns the Address.
  
  Customer and Address may be a table of the database or representing the Query.
  
  So I should be able to name those two queries in the stored procedure.
  
  Using those names, I have to access the table of rows in the DataSet which is filled using SqlDataAdapter
  
  ie. dataSet.Tables [ "Customer" ] instead of dataSet.Tables [ 0 ]
  
  Since I write the query on the stored procedure, I know the order.
  So I can access like this,
  DataTable customerTable = dataSet.Tables [ 0 ] ;
  DataTable addressTable = dataSet.Tables [ 1 ] ;
  
  But I can't always rely on this order. I have to access the tables by name . . .
  
  
  Once again thanks Phil . . .
  
  Regards,
  
  Kannan

  
  

• Pieter-Paulus Vertongen

  You can easily use the DataAdapter's DataTableMapping object to specify the mapping of the result tables to a typed DataSet's schema.
  e.g. dataAdapter.TableMappings.Add("Table", "Customer");
        dataAdapter.TableMappings.Add("Table1", "Address" );
  Check out these MSDN articles:
  a) Setting Up DataTable and DataColumn Mappings.
  b) Working with a Typed DataSet (if you have VS you can just add a Typed DataSet file to your project).
  Another option for returning multiple tables in one query is to obtain data as XML from SQL Server.
  Hope this helps,
  Phil

  
  

• StacyJ

  Hi Kannan,

  I agree that having to know the order of the tables and mapping is not that pretty, but this is probably the fastest way I am aware of for mapping multiple result sets using the .Net Framework.
  The DataSet's Fill method underneath uses a DataReader object to cursor through the result sets from the stored procedure or query making use of the NextResult method. This DataReader can obtain detailed column information including each column's source table name via the method GetSchemaTable, but the table name of the DataTable is always returned as "SchemaTable".
  As per my first response the only other option I would suggest is to use Microsoft SQL Server to obtain the response as XML, which means you can read from multiple tables and have them named in the store procedure, but at the cost of marshalling via XML.
  See this article: SQL Server 2000 and XML
  
  Regards,
  Phil

  
  
• How can I get as a table name by naming a query in the Stored Procedure ?