RE: Data Insertion
From: Ilya Tumanov [MS] (ilyatum_at_online.microsoft.com)
Date: 03/26/04
- Next message: paul: "working with Brew? aGPS alternative?"
- Previous message: Geoff Schwab [MSFT]: "Re: any SP for .NET CF 1.1"
- In reply to: Dave: "RE: Data Insertion"
- Next in thread: Dave: "RE: Data Insertion"
- Reply: Dave: "RE: Data Insertion"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 26 Mar 2004 19:20:11 GMT
Please see inline...
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
> Thread-Topic: Data Insertion
> thread-index: AcQTVKa17g320Z3SRJiYVf230tNpDQ==
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
> From: "=?Utf-8?B?RGF2ZQ==?=" <anonymous@discussions.microsoft.com>
> References: <8F6152A2-6CE3-4887-876A-85349EE4AE69@microsoft.com>
<lVUadJdEEHA.3924@cpmsftngxa06.phx.gbl>
<06F32F48-C6C7-4097-9882-A6726F00BECE@microsoft.com>
<jFDCcVqEEHA.564@cpmsftngxa06.phx.gbl>
> Subject: RE: Data Insertion
> Date: Fri, 26 Mar 2004 09:06:16 -0800
> Lines: 211
> Message-ID: <3B85E9D8-03CA-4BEB-BACB-B6480F8DEEB2@microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> Newsgroups: microsoft.public.dotnet.framework.compactframework
> Path: cpmsftngxa06.phx.gbl
> Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:49591
> NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>
> Hi Ilya,
>Thanks for your answer.
>Could I clarify a couple of points.
>The physical database structure is already in place.
Do you mean the database data is coming from, SQL CE database on device or
both?
>The XML Schema will be fixed.
>I can determine the XML file and whether it contains a schema.
>The need for the embedded schema confuses me a little.
>Where is the schema used ?
In this particular case XML Schema is used to create a DataSet schema (set
of tables, columns, relations, constraints and so on).
As soon as you load XML schema into the DataSet, DataSet will have all the
tables, columns and so on, but no data.
XML schema could be inside XML file with data (embedded schema) or it could
be loaded separately.
You do not actually need XML schema, but you do need DataSet schema it
generates as you load it into the DataSet.
You can avoid using XML schema if you create DataSet schema
programmatically.
>Do you mean the schema is used with the dataadapter or dataset ?
It is used to create tables, columns and other DataSet elements.
>If it is intended to generate the database itself , that has already been
done .
Since DataSet is essentially in memory relational database, yes, it is
intended to do this.
Using DataSet without schema is pretty much like creating an empty SQL
database and trying to fill it with data.
Won't work, you need to create some tables and columns first, right?
Now, you're probably wondering how it's possible to load XML without XML
schema into the DataSet without schema (created programmatically or loaded
from XML schema file)…
It is possible because before loading any data DataSet would parse entire
XML in attempt to infer schema from XML structure using predefined set of
rules.
This is very slow process which consumes huge amount of memory, works only
for some XML files and might produce some unpredictable results.
Nobody should use inference to load XML into the DataSet.
>Another question :
>you say : "It'll take few minutes to load, but should be just fine."
>Do you mean loading the 4.6 mb file into a dataset using
DataSet.ReadXml() ?
Correct. As soon as you have data in the DataSet, you could copy it to the
SQL CE database.
>I am unclear as to how exactly you are suggesting updaing the database.
1. Load XML schema and data into the DataSet.
DataSet data = new DataSet(); // New DataSet, no
tables or columns, could not hold any data.
data.ReadXmlSchema (schemaFile); // Now you have tables,
columns and so on, but no data.
//
Remove if you have embedded schema in dataFile.
data.ReadXml (dataFile); // Now you have
data.
2. Create Connection, DataAdapter and Command.
SqlCeConnection con = new SqlCeConnection("Data Source = " +
dataFile );
//
Create a new connection
con.Open(); // Open
this connection
SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare
data adapter
SqlCeCommand cmd = con.CreateCommand(); // Prepare
command
3. Execute DataAdapter.Update() for each table:
foreach (DataTable table in data.Tables ) {
cmd.CommandText = "Inset Into ..."; // Set insert
command, add table name and parameters...
// Create and add parameters to command here...
da.InsertCommand = cmd; // Use
command as insert command on DataAdapter
da.Update(table);
// Update table in the SQL CE with data in DataSet's table
}
4. Close connection and dispose of all used objects as you normally do.
>To simplify ..If I have an XML file with a schema could you clarify what
steps I follow to get it into the database.
Please see above.
>Thanks for all your help,
>Dave.
----- \"Ilya Tumanov [MS]\" wrote: -----
4.6 MB is pretty bug, but device should be able to handle it.
It'll take few minutes to load, but should be just fine.
You _have_ to use schema to accomplish that, though.
Inference is OK for prototyping and, may be, loading 1-2K XML files;
it's
not good for anything else and should be avoided.
Here are your options:
1. Change your XML file to contain embedded schema. This might be
impossible if you have no control over XML.
2. Create schema using inference (or schema designer) on a desktop and
load
it from separate file before loading XML.
You can also reformat you XML to improve loading performance and
minimize XML size. You can use utility below to do that.
3. Create schema programmatically (i.e. add tables, columns,
relations,
etc.) before loading XML. This is not very flexible, though, but might
be
OK if your schema is fixed.
4. Create typed DataSet using desktop's designer, remove unsupported
stuff
manually and use it in device project.
This is pretty much the same as #3, but instead of coding you
would
have to comment out unsupported code.
Now to the XmlTextReader solution...
I do not recommend using it unless you really desperate. It's
inflexible
and would require significant coding on your part (not to mention
testing).
It is, however, the fastest solution and it uses the least possible
amount
of memory.
It would work even for 50 MB XML, but probably not worth is with 4.6
MB.
It won't be slower. DataAdapter.Fill() will add records one by one
anyway
as SQL CE does not support batching.
Thus, loading small batches into the DataSet would not improve
performance.
I see no good way to accomplish this, anyway. You might be able to use
Fragment mode, but it's not exactly what you need.
Best regards,
Ilya
PS Utility source code...
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Xml;
namespace FixSchema
{
class DoFix
{
const int OK = 0;
const int ERROR = 1;
static void DumpRelation ( DataRelation dr) {
string parentColumns = "";
string childColumns = "";
foreach (DataColumn dc in dr.ParentColumns)
parentColumns += String.Format("'{0}' ", dc.ColumnName);
foreach (DataColumn dc in dr.ChildColumns)
childColumns += String.Format("'{0}' ", dc.ColumnName);
Console.WriteLine ("Relation '{0}': '{1}' ({4})->'{2}'
({4}),
{3}", dr.RelationName, dr.ChildTable, dr.ParentTable, dr.Nested ?
"nested"
: "related",
parentColumns, childColumns );
}
static int Main(string[] args)
{
if (args.Length == 0) {
Console.WriteLine ("This utility loads XML file, changes relations
from
nexted to related,\nmaps all columns as attributes and saves resulted
schema and data.");
Console.WriteLine ("Usage: {0} xml_file",
Path.GetFileName(System.Reflection.Assembly.GetExecutingAssembly().GetName()
.CodeBase));
return ERROR;
}
try {
string fileName = args[0];
DataSet ds = new DataSet();
Console.WriteLine ("Loading XML file '{0}'", fileName);
XmlReadMode mode = ds.ReadXml (fileName);
if (XmlReadMode.InferSchema == mode ) {
Console.WriteLine ("WARNING! WARNING! WARNING! WARNING! WARNING!
WARNING!\nXML has been loaded in inference mode, all type information
is
now lost.\nConsider changing schema to restore type information.");
}
Console.WriteLine ("Done loading file, changing column mappings to
attributes...");
foreach (DataTable dt in ds.Tables) {
foreach (DataColumn dc in dt.Columns){
if (dc.ColumnMapping != MappingType.Attribute) {
Console.WriteLine ("Changing mapping '{0}' of column '{1}' from
table '{2}' to 'Attribute'",
dc.ColumnMapping, dc.ColumnName, dt.TableName );
dc.ColumnMapping = MappingType.Attribute;
}
}
}
Console.WriteLine ("Now changing relations from nested to
related...");
foreach (DataRelation r in ds.Relations ) {
DumpRelation (r);
if (r.Nested && (r.ParentColumns.Length == 1) &&
(r.ChildColumns.Length == 1)) {
Console.WriteLine ("Changing relation '{0}' from nested to
related...", r.RelationName);
r.Nested = false;
}
}
Console.WriteLine ("Done processing relations, saving schema...");
ds.WriteXmlSchema (fileName + ".xsd");
Console.WriteLine ("Schema saved to file '{0}'", fileName +
".xsd");
Console.WriteLine ("Now saving data with schema...");
ds.WriteXml (fileName + ".xml", XmlWriteMode.WriteSchema);
Console.WriteLine ("Data saved to file '{0}'", fileName + ".xml");
}
catch (Exception e) {
Console.WriteLine ("Error while processing: {0}", e);
return ERROR;
}
return OK;
}
}
}
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
> Thread-Topic: Data Insertion
> thread-index: AcQSTUsR44/NKI2IQW+5cd6h8lFY3w==
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
> From: "=?Utf-8?B?RGF2ZQ==?=" <anonymous@discussions.microsoft.com>>
References:
<8F6152A2-6CE3-4887-876A-85349EE4AE69@microsoft.com><lVUadJdEEHA.3924@cpmsft
ngxa06.phx.gbl>> Subject: RE: Data Insertion
> Date: Thu, 25 Mar 2004 01:41:05 -0800
> Lines: 12
> Message-ID: <06F32F48-C6C7-4097-9882-A6726F00BECE@microsoft.com>>
MIME-Version: 1.0
> Content-Type: text/plain;
> charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> Newsgroups: microsoft.public.dotnet.framework.compactframework
> Path: cpmsftngxa06.phx.gbl
> Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:49423
> NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>> The XML File is 4.6 MB.
There is no Schema.
You recomend using the xmltextreader an adding the records direct to
SQL CE
>
- Next message: paul: "working with Brew? aGPS alternative?"
- Previous message: Geoff Schwab [MSFT]: "Re: any SP for .NET CF 1.1"
- In reply to: Dave: "RE: Data Insertion"
- Next in thread: Dave: "RE: Data Insertion"
- Reply: Dave: "RE: Data Insertion"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
