XDocument to dataSet, my nested nodes are getting treated as new tables?
When looping through elements it thinks the child node is a table, when it should just be a column. I have most of the code working well, until I the code gets to this part: (all of my code is at the bottom)
new XElement("TBL_Magic_TripCountries", lstCountry.Items
.Cast<ListItem>()
.Where(i=> i.Selected)
.Select(x => new XElement("CountryCode", x.Value))
),
I am receiving a Cannot access destination table 'CountryCode'. I have no idea why this thinks this is a table if its nested within another node. I am trying to set CountryCode from TBL_Magic_TripCountries, but its not working as I thought. Any help?
XDocument xmlDoc = new XDocument(
new XDeclaration("1.0", "utf-8", "yes"),
new XComment("Created: " + DateTime.Now.ToString()),
new XElement("Trips",
new XElement("TBL_TripDetails",
new XElement("DepartureDate", txtDepartureDate.Text),
new XElement("ReturnDate", txtReturnDate.Text),
new XElement("TripRecommendation", tripRecommend),
new XElement("TripRecommendationComment", txtTripRecommendComment.Text),
new XElement("TripFavouriteThing", txtLikeMostComment.Text)
),
new XElement("TBL_Magic_TripCountries", lstCountry.Items
.Cast<ListItem>()
.Where(i=> i.Selected)
.Select(x => new XElement("CountryCode", x.Value))
),
new XElement("TBL_Cities", lstCities.Items
.Cast<ListItem>()
.Select(x => new XElement("City", x.Text))),)
));
...
DataSet ds = new DataSet();
ds.ReadXml(xDoc.CreateReader());
string StrConn = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
SqlConnection cnn = new SqlConnection(StrConn);
SqlBulkCopy blkcopy = new SqlBulkCopy(cnn);
cnn.Open();
foreach (DataTable dt in ds.Tables)
{
BulkCopyTable(dt, blkcopy);
}
EDIT my XML looks like this before I start putting it in the dataset
<!--Created: 8/4/2010 8:28:10 AM-->
<Trips>
<TBL_TripDetails>
<DepartureDate>2/2/2010</DepartureDate>
<ReturnDate>2/2/2010</ReturnDate>
<TripTypeA>1</TripTypeA>
<TripTypeB>2</TripTypeB>
<PurposeOfTrip>vacation</PurposeOfTrip>
<RegionID>2</RegionID>
<OverallRating>0</OverallRating>
<SupplierID>3</SupplierID>
<SupplierComment>great supplier</SupplierComment>
<FoodRating>0</FoodRating>
<CulinaryComments></CulinaryComments>
<RecommendRestaurant>false</RecommendRestaurant>
<AttractionDisappoint>false</AttractionDisappoint>
<TripRecommendation>false</TripRecommendation>
</TBL_TripDetails>
<TBL_Magic_TripCountries>
<CountryCode>USA</CountryCode>
<CountryCode>CND</CountryCode>
</TBL_Magic_TripCountries>
<TBL_Cities>
<City>New York</City>
<City>Ottawa</City>
<City>Las Vegas</City>
</TBL_Cities>
<TBL_Magic_TripTransportation>
<TransportType>3&开发者_运维知识库lt;/TransportType>
<TransportType>4</TransportType>
</TBL_Magic_TripTransportation>
</Trips>
I can't be certain this is the cause, but
lstCountry.Items
.Cast<ListItem>()
.Where(i=> i.Selected)
.Select(x => new XElement("CountryCode", x.Value))
retruns an IEnumerable<XElement> and would be interpreted by the dataset as an element with multiplicity of 0..∞. Datasets will translate that element as a table internally because that's how datasets handle nested relationships. I'm not sure if you'd end up with two tables ("TBL_Magic_TripCountries" associated with your main table and then "CountryCode" associated with it) or a single table "TBL_Magic_TripCountries" with CountryCode as the only column. It's been too long since I dealt with auto-schema-generated datasets.
精彩评论