Typed Datasets and Temp Tables

.Net, SQL Add comments

The scenario: you have a stored procedure in SQL and a typed dataset in Visual Studio that references the stored procedure. In your stored procedure, you create a temporary table and fill it with data and use it as output. When you go to create the typed dataset, it does not recognize the columns that the stored procedure returns.

Solution: in Visual Studio, create you typed dataset and use the stored procedure as a source (even if it doesn’t recognize the columns). After it is done, right clck the xsd file in Solution Explorer and click “View Code”. You should get an xml representation of your dataset. Here is where you create your columns manually. You need to add the column definitions in 2 places:

  1. Find “TableAdapter”, under it look for “Mappings”, inside mappings you will add a node for each column like:
    <Mapping SourceColumn="CompanyName" DataSetColumn="CompanyName" />
  2. Find
    <xs:element name="dsGetCompany"…..
         <xs:element name="CompanyName" msprop:Generator_UserColumnName="CompanyName" ….

    you will add “<xs: element….” for each column you want to add. I found the best way is to copy these nodes from similar columns from another dataset.

For more information

Leave a Reply

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in