Re: Please Urgent - Update Data Source Problem ???

From: William Ryan eMVP (bill_at_NoSp4m.devbuzz.com)
Date: 03/21/04 

Date: Sat, 20 Mar 2004 19:03:42 -0500

1) When the app starts, I'd check to see if the table exists. Creating a
database/table every time the app loads is most likely unnecessary. If you
have data in the database from the last session, you'll delete it under your
scenario, and if you don't need to persist the data, then there's better
ways than handle data than SqlCe. You could create a datatable/dataset
locally and just never serialize it so it goes away when the app does.
That'd probably be a preferable method to creating the database each time
and deleting it beforehand if it does.

As far as the connection..think of it this way. If you put connection close
anywhere in your try block and you throw an exception, you'll never close
the connection. If you put it in the catch block, you'll only close it if
you throw an exception. Neither of which is desireable. So, here's what I
recommend. In the finally block, use a test if(cn.State <>
ConnectionState.Closed){cn.Close();} Now, you can still close it
immediately when you are finished with the connection but this will make
sure it's closed. If you are using a DataAdapter, I'd highly recommend
letting it open and close itself so you don't accidentally forget to close
it. However, I'd still use the finally block in case something blows up.
with the command object methods, you have no choice but to open the
connection manually, and consequently, you're responsible for closing it.

2) Yes
3) You can do it in form_load, that's fine. Remember that this isn't doing
anything with the data adapter other than setting it's properties. It only
needs to happen once, so Form_Load is fine for this
4) No, all that HasChanges tells you is if the dataset has changes. If it
doesn't , then you can call update all year and nothign will happen.
However, if it does, you can call update and hopefully you're changes will
be commmited. They may not though because of many things, like no available
connection. Usually though, everything will be fine.

DataAdapter.Update returns an Integer value indicating how many rows were
changed in the DB. So, something like int i = DataAdapter.Update(dataSet,
TableName);
if i > 0 it worked.
5) Unnecessary. SqlCE isn't going to be updated by someone else other than
the user. So, all of the changes will be reflected locally. If your update
fails, and you repopulate it, you'll overwrite your changes. Update only
sends the changes back to the database, it doesn't pull any new values back
unless your code is such that it fires additional select statements.
6) Right
7) add using System.Diagnostics; at the top of your code.

Let me know how it goes and if you have any problems.

Cheers,

Bill

"Lianna" <anonymous@discussions.microsoft.com> wrote in message
news:2E2E7414-D9AD-4669-B206-F760218C3BD2@microsoft.com...
> Thanks a million. I really appreciate that you willing to help me all this
while. However, since the scenario seems complicated, I've decide to code
the program again. But First I would describe my scenario again and then
would like to clarify few questions.
>
> Scenario:
> I want to write a simple program that allows users to view their daily
schedule, and they can update the existing schedule, add new schedule and
delete schedule made.
>
> 1. If I put the create table code in "form_load" does it correct ?? I've
followed your
> suggestion and catch the exception with below code:
>
> catch (SqlCeException ex)
> {
> for(int cu = 0; cu < ex.Errors.Count; ++cu)
> {
> MessageBox.Show("Error:" + ex.Errors[cu].ToString()+ "\n");
> }
> }
>
> This code does show error message that the table I create already
> exists. So I've modified the code:
>
> if (! File.Exists (@"\My Documents\MSFA.sdf"))
> {
> SqlCeEngine engine = new SqlCeEngine (
> @"Data Source = \My Documents\MSFA.sdf");
> engine.CreateDatabase();
> }
> else
> {
> File.Delete(@"\My Documents\MSFA.sdf");
> SqlCeEngine engine = new SqlCeEngine (
> @"Data Source = \My Documents\MSFA.sdf");
> engine.CreateDatabase();
> }
>
>
> And I do close the connection which is shown in the "finally"
> statement. Or it's advisable to close the connection once create the
> table. If I put before "catch", does it work ???
>
>
>
> 2. Does the way I bind the control is correct ?? I bind the control
> with following code and the binding code is put in the "form_load".
>
>
> SqlCeDataAdapter da = new SqlCeDataAdapter();
> DataSet ds = new DataSet();
> DataTable dt = new DataTable();
> da.Fill(ds, "Schedule");
> dt = ds.Tables["Schedule"];
> dataGrid.DataSource = dt;
> txtDate.DataBindings.Clear();
> txtDate.DataBindings.Add("Text", dt, "Date");
> txtTime.DataBindings.Clear();
> txtTime.DataBindings.Add("Text", dt, "Time");
> txtTask.DataBindings.Clear();
> txtTask.DataBindings.Add("Text", dt, "Task");
>
>
>
> 3. Where should I place the following DataAdapter command?? Is it also
> put in "form_load" ?? Is it necessary to have all this command in
> order to update the data source ??
>
> SelectCommand, UpdateCommand, InsertCommand and DeleteCommand
>
> Example:
> cmdUpdate = cnn.CreateCommand();
> cmdUpdate.CommandText = "UPDATE Schedule SET DATE = @Date,Time =
> @Time, Task = @Task WHERE Date = @Date AND Time = @Time AND Task =
> @Task ";
>
> prm = cmdUpdate.Parameters.Add("@Date", SqlDbType.NVarChar, 0,
> "Date");
> prm = cmdUpdate.Parameters.Add("@Time", SqlDbType.NVarChar, 0,
> "Time");
> prm = cmdUpdate.Parameters.Add("@Task", SqlDbType.NVarChar, 0,
> "Task");
> prm.SourceVersion = DataRowVersion.Original;
> da.UpdateCommand = cmdUpdate;
>
>
> 4. To check whether the dataset is updated or not is it I can use
following code: If not then how can I know the row is change ??. In my
existing program, I try to put it after call the -- da.update(ds,
"Schedule") and the result show the dataset does not change.
>
> if(ds.HasChange())
> {
> // Do something
> }
>
> 5. I put the btnRetreive because I think after I call the da.Update(ds,"
Schedule") then I check whether the
> data source is updated by click the retrieve button and select all the
row from the table again. Does it
> logic ??
>
> 6. Actually what I need in the "UpdateButton_Click" ??? Is it just need
the following code ??
>
> try{
> da.Update(ds, "Schedule");
> ds.AcceptChanges();
> }
> catch (Exception ex)
> {
> // Do something
> }
>
> 7. The "Assertion (Debug.Assert(ds.HasChanges())); " code that you ask me
try does not work, the system
> display - "The type or namespace name 'Debug' could not be found (are
you missing a using directive or
> an assembly reference?)"
>