DataAdapter Mapping only works for the first two tables.

Hi,

I am writing an application where the select command returns multiple
record sets and write the infomation to a xml file. I used typed
dataset to store the returned info and use the dataset.writexml command
to save the data to a xml file. Oh one more thing I am used the DAB
(data application block) to fill the dataset. I tried the same solution
with the "Northwind" database but no luck.

Here's my code:

SQL stroed proc:

CREATE PROCEDURE [dbo].[proc_name]
@EmployeeID INT
AS

SELECT * FROM Employees
WHERE EmployeeID = @EmployeeID;

SELECT * FROM EmployeeTerritories;

SELECT * FROM Territories;

GO

Here's the code that fills the dataset:

TestDto dto = new TestDto(); //xsd contains the tables def
string[] tableList = new string[3] ;
tableList[0] = "Employees";
tableList[1] = "EmployeeTerritories";
tableList[2] = "Territories";

SqlHelper.FillDataset("connectionstring", CommandType.StoredProcedure,
"proc_name", dto, tableList, new SqlParameter("@EmployeeID", 1) );

dto.WriteXml(@"C:\xxx.xml");

When I look at the "xxx.xml" file i see the table mapping being done
for "Employees" and "EmployeeTerritories" but "Territories" mapping is
set to "Table2"...Crazy!!!!

Can someone please tell me why?

Sahil
Tue Nov 29 05:11:22 CST 2005

Can you verify your TableMappings right before the Fill?

Also is this .NET 1.1 or 2.0?


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



<vrjaya@hotmail.com> wrote in message
news:1133257983.035361.95060@g44g2000cwa.googlegroups.com...
> Hi,
>
> I am writing an application where the select command returns multiple
> record sets and write the infomation to a xml file. I used typed
> dataset to store the returned info and use the dataset.writexml command
> to save the data to a xml file. Oh one more thing I am used the DAB
> (data application block) to fill the dataset. I tried the same solution
> with the "Northwind" database but no luck.
>
> Here's my code:
>
> SQL stroed proc:
>
> CREATE PROCEDURE [dbo].[proc_name]
> @EmployeeID INT
> AS
>
> SELECT * FROM Employees
> WHERE EmployeeID = @EmployeeID;
>
> SELECT * FROM EmployeeTerritories;
>
> SELECT * FROM Territories;
>
> GO
>
> Here's the code that fills the dataset:
>
> TestDto dto = new TestDto(); //xsd contains the tables def
> string[] tableList = new string[3] ;
> tableList[0] = "Employees";
> tableList[1] = "EmployeeTerritories";
> tableList[2] = "Territories";
>
> SqlHelper.FillDataset("connectionstring", CommandType.StoredProcedure,
> "proc_name", dto, tableList, new SqlParameter("@EmployeeID", 1) );
>
> dto.WriteXml(@"C:\xxx.xml");
>
> When I look at the "xxx.xml" file i see the table mapping being done
> for "Employees" and "EmployeeTerritories" but "Territories" mapping is
> set to "Table2"...Crazy!!!!
>
> Can someone please tell me why?
>

vrjaya
Fri Dec 02 13:17:42 CST 2005

Hi Sahil,

When i debug i saw the table mapping being done via the data adapter,
After the fill the first two tables are mapped fine but not after.
Sahil Malik [MVP] wrote:
> Can you verify your TableMappings right before the Fill?
>
> Also is this .NET 1.1 or 2.0?
>
>
> --
>
> - Sahil Malik [MVP]
> ADO.NET 2.0 book -
> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
> ----------------------------------------------------------------------------
>
>
>
> <vrjaya@hotmail.com> wrote in message
> news:1133257983.035361.95060@g44g2000cwa.googlegroups.com...
> > Hi,
> >
> > I am writing an application where the select command returns multiple
> > record sets and write the infomation to a xml file. I used typed
> > dataset to store the returned info and use the dataset.writexml command
> > to save the data to a xml file. Oh one more thing I am used the DAB
> > (data application block) to fill the dataset. I tried the same solution
> > with the "Northwind" database but no luck.
> >
> > Here's my code:
> >
> > SQL stroed proc:
> >
> > CREATE PROCEDURE [dbo].[proc_name]
> > @EmployeeID INT
> > AS
> >
> > SELECT * FROM Employees
> > WHERE EmployeeID = @EmployeeID;
> >
> > SELECT * FROM EmployeeTerritories;
> >
> > SELECT * FROM Territories;
> >
> > GO
> >
> > Here's the code that fills the dataset:
> >
> > TestDto dto = new TestDto(); //xsd contains the tables def
> > string[] tableList = new string[3] ;
> > tableList[0] = "Employees";
> > tableList[1] = "EmployeeTerritories";
> > tableList[2] = "Territories";
> >
> > SqlHelper.FillDataset("connectionstring", CommandType.StoredProcedure,
> > "proc_name", dto, tableList, new SqlParameter("@EmployeeID", 1) );
> >
> > dto.WriteXml(@"C:\xxx.xml");
> >
> > When I look at the "xxx.xml" file i see the table mapping being done
> > for "Employees" and "EmployeeTerritories" but "Territories" mapping is
> > set to "Table2"...Crazy!!!!
> >
> > Can someone please tell me why?
> >

ShaunMc
Mon Feb 06 10:33:17 CST 2006

Having had the same issue, I found that there is a bug in the Microsoft Data
Access Application Block for c# in the filldataset method.

The table mappings are added incorrectly and named as follows

Table
Table1
Table12
Table123
Table1234
etc.

But it should read

Table
Table1
Table2
Table3
Table4
etc.


One way of fixing this, is to reassign the tableName sting to "Table" before
the tableName is set for the next iteration of the loop.

// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index=0; index < tableNames.Length; index++)
{
if( tableNames[index] == null || tableNames[index].Length == 0 )
throw new ArgumentException( "The tableNames parameter must contain a list of
tables, a value was provided as null or empty string.", "tableNames" );

dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName = "Table";
tableName += (index + 1).ToString();
}
}

regards

Shaun