I have a problem when i'm trying to update an SQL Server CE Database from a dataset.
Basically I have 2 tables that are on the same dataset. One table is loaded directly using a dataadapter from SQL Server CE database and the other one is loaded from XML. What I need to do is to save the datatable that I loaded from the XML into the SQL Server CE database.
Before the last update the mywareDS containts 2 tables mywareDS.Tables[0] and mywareDS .Tables[1] that contain the correct data. The mywareDS.Tables[1] as you can see from the XSD has 2 fields;WAREH_ID and WAREH_NAME. The mywareDS.Tables[0] has more than 2 fields including WAREH_ID and WAREH_NAME.
I have the following code that does not work :
private void pictureBox1_Click_1(object
sender, EventArgs e)
{
string temp = "temp.sdf";
string connstr = @"Data Source=" + temp;
DataSet mywareDS = new DataSet();
using (SqlCeConnection conn = new SqlCeConnection(connstr))
{
conn.Open();
string dmlPackageInfo = "SELECT * FROM WARH";
string dmlInsertPackage = "INSERT INTO " +
"WARH(WAREH_ID, WAREH_NAME) " +
"VALUES (?, ?)";
string dmlDeletePackage = "DELETE FROM " +
"WARH";
SqlCeDataAdapter daPackages = new SqlCeDataAdapter();
daPackages.SelectCommand = new SqlCeCommand(dmlPackageInf
o, conn);
daPackages.InsertCommand = new SqlCeCommand(dmlInsertPack
age, conn);
daPackages.InsertCommand.P
arameters.
Add("WAREH
_ID", SqlDbType.Int);
daPackages.InsertCommand.P
arameters.
Add("WAREH
_NAME", SqlDbType.NVarChar);
daPackages.DeleteCommand = new SqlCeCommand(dmlDeletePack
age, conn);
daPackages.Fill(mywareDS);
String xmlFileName = "MyWAREH.xml";
String xsdFileName = "MyWAREH.xsd";
FileStream FsXSD = new FileStream(xsdFileName, FileMode.Open);
FileStream FsXML = new FileStream(xmlFileName, FileMode.Open);
// Load the schema into the DataSet.
XmlTextReader xtrXSD = new XmlTextReader(FsXSD);
mywareDS.ReadXmlSchema(xtr
XSD);
xtrXSD.Close();
FsXSD.Close();
// Load the data into the DataSet.
XmlTextReader xtrXML = new XmlTextReader(FsXML);
mywareDS.ReadXml(xtrXML);
xtrXML.Close();
FsXML.Close();
//so basically the problem lies right here **************************
**********
**********
*
daPackages.Update(mywareDS
);
}
--- the xsd looks like the following
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="
http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-
microsoft-
com:xml-ms
data">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="WARH
" msdata:UseCurrentLocale="t
rue">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="WARH">
<xs:complexType>
<xs:sequence>
<xs:element name="WAREH_ID" type="xs:int" />
<xs:element name="WAREH_NAME" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//WARH" />
<xs:field xpath="WAREH_ID" />
</xs:unique>
</xs:element>
</xs:schema>
One option is to iteriate trough the xml file and then add the rows to the dataset
Then you could do the da.update(mywareDS)
vbturbo