Importing XML - Need some help please

[Guest]

I am having a problem pulling in XML data into a PPC Compact .NET app and
saving it to a SQL CE .SDF file. I get an error message that is simply the
name of the first column. I have checked everything I can think of to no
avail.

My steps in the code:
- Create a connection to the SQL CE .SDF file
- Create a command for table direct access to the main table
- Create a data adapter from the command
- Create a data set
- Read in the XML schema from the XML schema file I created on the desktop
- Create a command for an insert and store it to the data adapter insert
command
- Add all the necessary parameters using the correct data types
- Do a DataAdapter.Update(DataSet, "Table")

On this command is where I get my exception. The message is simply the name
of the first column.

What am I doing wrong???????

---------------------------------------------------------------------------------------
Here is the code. I stripped out all the error checking and such. Also, note
I am reusing the Command object and just reseting the properties to what I
need.

cn = New SqlCeConnection("Data Source=\My Documents\emsw.sdf;Password=")
cn.Open()

cmd = cn.CreateCommand
cmd.CommandText = "People"
cmd.CommandType = CommandType.TableDirect
daPeople = New SqlCeDataAdapter(cmd)

Dim ds As New DataSet("LoadTables")
ds.ReadXmlSchema(DataSchema)

cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO people " & _
"(RegistrationNumber, LastName, FirstName, MiddleInitial, LocalUnion, "
& _
" Address, State, ZipCode, PhoneNumber) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
cmd.Parameters.Add("RegistrationNumber", SqlDbType.Int)
cmd.Parameters.Add("LastName", SqlDbType.NChar)
cmd.Parameters.Add("FirstName", SqlDbType.NChar)
cmd.Parameters.Add("MiddleInitial", SqlDbType.NChar)
cmd.Parameters.Add("LocalUnion", SqlDbType.Int)
cmd.Parameters.Add("Address", SqlDbType.NChar)
cmd.Parameters.Add("State", SqlDbType.NChar)
cmd.Parameters.Add("ZipCode", SqlDbType.Int)
cmd.Parameters.Add("PhoneNumber", SqlDbType.NChar)
daPeople.InsertCommand = cmd

daPeople.Update(ds, "People") <----------------------- Error happens
here
ds.Tables("People").AcceptChanges()

 

[Darren Shaffer]

Your approach does not make sense to me - Even if you got this working
(and it's not working because you never fill your data adapter with
anything,
so the TableDirect command does nothing) you will be most unhappy with
the performance.

Best performing approach: iterate your XML data with XmlTextReader and
use a parameterized insert command to insert each record into the underlying
SQL CE table. You are not benefitting from using the extra level of caching
that the adapter represents because you are not doing any data binding.

Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

 

[Guest]

Thanks. If you haven't figured it out yet, I am a newbie with .NET. Been
coding awhile, but not getting the hang of the data side of .NET as quickly
as I would have liked.

I went that way trying to conserve objects. When I start up, I create the
necessary connections and data objects for the data binding that will happen
in the app. I check to see if the XML file exists. If it does, I delete
everything in the tables in SQL CE and reload from the file, deleting the
file when I am done. The data on the PPC is meant to be read/lookup only, so
this seems like a logical approach. I was trying to use the same objects to
pull the data in that I was using for the bindings.

Can you point me to an example of what you speak of?

Thanks!