Updating a P.SQL DataSet

[mitzoe]

I apologize for any inappropriate cross-posting, but wasn't sure where
whether this is a Pervasive or ADO.NET issue.

I'm trying to migrate data from a propietary system that's based on
btrieve into Pervasive SQL 9.5 using a .NET application written in
C#. The main problem is that the existing system stores its data in
separate folders and files, so I'm having to read each file and copy
its data to its corresponding P.SQL table.

There are hundreds of folders, so doing this by hand is not feasible,
thus my little application.

What I've done is create an array of the files appropriate to each
table, iterate and copy each file into the P.SQL data folder, query
the table, and put into a DataSet using the Fill method, appending to
the records already there.

So at this point I have a DataSet of all the records. I'm leaving the
last file copied for that table and trying to use the DataAdapter's
Update method to move the DataSet into P.SQL, but when I do so nothing
happens. No error, but when I query the table in P.SQL, it's still
just the single record that resides in that last data file.

I suspect the problem is that I'm using repeated Fill calls, but I'm
not sure. I've tried the DataSet's AcceptChanges method before
calling Update, but no dice. I'm new to ADO.NET's DataSet and
DataAdapter objects, so any help would be appreciated.

Thanks in advance.

Michael Itzoe
(e-mail address removed)

 

[Bill Bach]

I suspect that you have an ADO problem, and posting your code would
probably be the best next step.

However, since PSQLv9 includes Btrieve v9, why are you doing a
conversion? The new engine should be able to read the old data files
just fine, if you have data dictionary files for them. (Of course,
without DDF's, there is no ADO access, so you must have the.) Are you
just updating the app from an old language to ADO.NET?
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
(e-mail address removed)
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***

 

[mitzoe]

I'm using P.SQL because of its btrieve-ness. It will read the old
data files fine, but the problem is they're spread across hundreds of
folders. The previous system saved each company's information in it's
own folder, so for Entity A, folder A has a data file for its profile,
customers, etc. Entity B's folder has the same data file name, with
their own information, and so on. All of the entities are owned by a
single company, and I need to get all these disseparate records into
single tables.

Each data file for a given filename is identical, so I'm able to
quickly create the array of files, loop through and read records into
the dataset.

Instead, I could open each data file individually, copy the data, and
paste it to the final table, but because there are thousands of data
files, I was hoping it would be easier and faster to write a quick-and-
dirty app to do it for me.

Here's the pertinate code:

string sql = "select * from \"" + tablename + "\"";

DataSet ds = new DataSet();
OleDbConnection con = new OleDbConnection( ConnectionString );
OleDbCommand cmd = new OleDbCommand( sql, con );

OleDbDataAdapter da = new OleDbDataAdapter( cmd );
da.SelectCommand = new OleDbCommand( sql, con );

OleDbCommandBuilder cb = new OleDbCommandBuilder( da );

foreach( string file in DataFiles ) {
//copy the data file to data folder
if( !CopyFile( DataFiles[count], targetFile ) ) return;

try {
con.Open();
da.Fill( ds );
} catch( OleDbException err ) {
ErrBox( err.Message, err.ErrorCode, err.TargetSite );
}
}

try {
ds.AcceptChanges();
da.Update();
} catch( OleDbException err ) {
ErrBox( err.Message, err.ErrorCode, err.TargetSite );
} finally {
con.Close();
}

cmd.Dispose();
con.Dispose();
da.Dispose();
ds.Dispose();

Any insight is appreciated. Thanks.

 

[mitzoe]

Oops, the update should be:
da.Update( ds );

I'm using P.SQL because of itsbtrieve-ness. It will read the old
data files fine, but the problem is they're spread across hundreds of
folders. The previous system saved each company's information in it's
own folder, so for Entity A, folder A has a data file for its profile,
customers, etc. Entity B's folder has the same data file name, with
their own information, and so on. All of the entities are owned by a
single company, and I need to get all these disseparate records into
single tables.

Each data file for a given filename is identical, so I'm able to
quickly create the array of files, loop through and read records into
the dataset.

Instead, I could open each data file individually, copy the data, and
paste it to the final table, but because there are thousands of data
files, I was hoping it would be easier and faster to write a quick-and-
dirty app to do it for me.

Here's the pertinate code:

string sql = "select * from \"" + tablename + "\"";

DataSet ds = new DataSet();
OleDbConnection con = new OleDbConnection( ConnectionString );
OleDbCommand cmd = new OleDbCommand( sql, con );

OleDbDataAdapter da = new OleDbDataAdapter( cmd );
da.SelectCommand = new OleDbCommand( sql, con );

OleDbCommandBuilder cb = new OleDbCommandBuilder( da );

foreach( string file in DataFiles ) {
//copy the data file to data folder
if( !CopyFile( DataFiles[count], targetFile ) ) return;

try {
con.Open();
da.Fill( ds );
} catch( OleDbException err ) {
ErrBox( err.Message, err.ErrorCode, err.TargetSite );
}

}

try {
ds.AcceptChanges();
da.Update();} catch( OleDbException err ) {

ErrBox( err.Message, err.ErrorCode, err.TargetSite );

} finally {
con.Close();
}

cmd.Dispose();
con.Dispose();
da.Dispose();
ds.Dispose();

Any insight is appreciated. Thanks.

I suspect that you have an ADO problem, and posting your code would
probably be the best next step.

However, since PSQLv9 includesBtrievev9, why are you doing a
conversion? The new engine should be able to read the old data files
just fine, if you have data dictionary files for them. (Of course,
without DDF's, there is no ADO access, so you must have the.) Are you
just updating the app from an old language to ADO.NET?
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
(e-mail address removed)
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***

- Show quoted text -- Hide quoted text -

- Show quoted text -