Vb.net help

[Doodoo]

I have a listbox which is populated using csv file names from a directory. The user will then select one of these files and the csv will populate a datagrid. Using the datagrid, they can then edit the data. At the end of the day, they will then export this data to a text file or database so I can work with the edited data.

Since the user can select one file...do their edits...and then choose another file...then maybe even go back to the original file, would it be easier to read the csv into separate datatables within a dataset and then populate the datagrid or read the csv to create a sql database and use that to populate the datagrid? This app would be running on a mobile device so I figured creating a database onto the storage card on the fly would be more efficient. Hopefully all of that makes sense..and thanks for any help. I havent programmed since college, so I'm trying to get the hang of things again.

 

[imported_Dhaval00]

How much memory are you dealing with on the mobile device? Is it using SQL Server Mobile Edition? Also, how big can these files be? I am just asking all these questions because you may not have enough memory to manipulate your data directly on the device. Remember that a .NET DataSet object is raw, verbose XML - you may end adding more overhead than required. In that case using a DataTable would make more sense. Also, if you're planning to manipulate data using a SqlDataAdapter, your life will be easier if you just use a DataTable. A SqlDataAdapter has "SelectCommand," "UpdateCommand," and "DeleteCommand." Needless to say, all these commands can be associated to different databases (scale out). Also, don't forget that almost all these ADO.NET objects are disposable - since you may be dealing with limited memory, dispose them as soon as you can 🙂

As a side note, if your database is located remotely (and you're connecting to it via wireless?) then it may make sense to manipulate data on the server-side. Again, your users will be affected depending on the speed of the connection, but the device won't "crash!"

 

[Doodoo]

They most likely will have 64 megs of ram and 128 of rom...though after the operating system..probably only 70 megs. It will have SQL Server Mobile Edition. I dont think the files would be so big. They are about 500 records with each having about 10 or so fields. I'm currently reading in the csv line by line and populating a datatable...then binding that to a datagrid. I ran this on my pc with about 5000 records and it took about a minute and a half to load the datagrid.

I was going to change it so that i would read in the csv and do a sql create table to physically create the database on the flash card. So that if the user were to choose a different file and then go back, the database would already be there...it wouldnt have to read from the csv file anymore. Also..aren't datatable's in memory? What would happen if the battery were to die?

Sorry if some of these questions dont make sense...i've only programmed with C++ for 4 years and that was 5 years ago. I just started learning vb.net two weeks ago.

 

[imported_Dhaval00]

Why are you using VB.NET? I say so because the switch from C++ to C# is way easier and there are many things that will make more sense in C# (because of your background) than in VB.NET. In any case, stay with VB.NET if you feel its easier.

I am not sure, but does the BCP (Bulk Copy Utility) ship with SQL Server Mobile? If it does, then you might be in luck and your initial idea will work without any "major" issues. BCP will be able to copy those comma-delimited rows in batches (in your case you can simply set the batch size to 500 directly). Then you can use your native ADO.NET logic to bind that DataGrid directly to your DataTable. You may still end up using a SqlDataAdapater, but posting changes in a "batch" isn't hard. Google for articles on how to post updates to SQL Server via the DataRow object's RowVersion property.

There is a downside to all this, though: what if the user/device runs into a bad device error and needs to turn off the device? Your SqlDataAdapter is an in-memory representation of the DB schema. If you run into problems, your users may end up losing valuable changes (if you rely on batch updating, i.e.). What we usually end up doing is bind to the "page-change" event of whatever viewer we're using (in your case it is the DataGrid) and then submit the changes to the DB on a page-by-page basis as opposed to a batch update. In your case, maybe it makes sense to do this, but instead of posting the changes to the DB Schema, you can post them directly to the CSV file 🙂

 

[Doodoo]

I actually started coding this in evb...then switched over to vb.net since its no longer supported in windows mobile 5. I probably shouldve started with C#...but i always figured vb was easier. Ive decided to read in the csv to a local database when the user selects the file from drop down list. Then i'll bind the database to a datagrid. If the user updates any data, it'll trigger a sql update statement to modify the database. If they select another file, the program will check to see if the table exits in the database and if not create one and read in the csv file. If it already exists...it'll just bind the table to the datagrid. I think if i do this, if something goes wrong i wont lose any data. These users aren't the most technical people...so i can almost guarantee something will go wrong.

If a user were to select a row on the datagrid and update the data...it would only update the dataset right? How would I make sure the actual physical database is updated with the updated record? I guess I could output the results to a csv like you said.

Thanks for the help.

 

[imported_Dhaval00]

When I have to deal with mobile devices, I usually have the leisure to save to the persistent memory. So if something goes wrong, I always have a state to recover from. In your case, if you decide to persist the database to the physical memory as opposed to the RAM, I would suggest go through the database on your way to the CSV file. The main reason being DBs give you transactions to recover from (ALWAYS perform your updates/inserts/deletes in a transaction) while file systems don't. Obviously, this may add extra overhead, but overhead that is justified.

If you don't have the leisure to persist your DB (and when I say persist, I mean persist it as long as the application runs - you can always clear out the DB every time the form is closed), go directly to the CSV file...

In regards to your last question, go back to my first post. Try and avoid a DataSet simply because it adds extra information in the form of XML that you don't need. Instead use the SqlDataAdapter to fill a DataTable (there is an overloaded method that takes a DataTable). The SqlDataAdapter doesn't care if you use DataTable or DataSet... it provides the same functionality across both. Look here for a rudimentry example:
http://samples.gotdotnet.com/q.../UpdateDataFromDB.aspx

Again, avoid a DataSet if you can (I insist on this only because this stand-alone mobile app and there is no intercommunication between devices - almost everywhere else, nothing can come close to a DataSet).

 

[Doodoo]

Thanks for all your help...i really appreciate it. I think I can save to persistent memory. We'll have either a compact flash or sd card for the database. So each time the user selects a file, I will load it into a datatable instead of a dataset. These will be used out in the field all day...so they will definately have some issues at one point or another. Thanks again.