missing fundamentals of updating a database

[michael sorens]

I have written a few applications to read data from a database but now, as
I turn my attention to writing to a database, I am apparently missing some
fundamentals. I created a new database with a new (empty) table called
Version. I then created a new data source in the Data Sources pane in
Visual Studio 2005, yielding a DataSet named MyTestDataSet to reference
the Version table. I then created a short WinForms program to just run
this method:

private void TestIt()
{
MyTestDataSet myTestDataSet = new MyTestDataSet();
MyTestDataSetTableAdapters.VersionTableAdapter versionTableAdapter
= new MyTestDataSetTableAdapters.VersionTableAdapter();
MyTestDataSet.VersionRow newRow = myTestDataSet.Version.NewVersionRow();
newRow.Server = "server A";
newRow.Version = "v1";
newRow.RecordDateTime = DateTime.Now;
versionTableAdapter.Update(newRow);
}

It runs without error or exception, but the table remains empty. Why?

 

[Gina_Marano]

michael,

I have shied away from using datasets. My day will come though. If you
want to insert/edit data then it looks like the prefered way is through
a data reader.

Also look up parametrized queries.

example: (almost quaranteed not to compile but hopefully I am near)

private void InsertRecord()
{
string sSQLStatement;
SqlCommand ProcLotCmd;
SqlTransaction myTransaction = null;

string sPersonName = "Gina";
string sPersonID = "1";

// Create the connection and transaction objects.
SqlConnection myPrivConnection = new SqlConnection("user
id=myuserid;" +

"password=mypwd;server=myserver;" +
"database=mydb; "
+
"connection
timeout=30");
myPrivConnection.Open();
try
{
myTransaction = myPrivConnection.BeginTransaction();
try
{
sSQLStatement = "INSERT INTO People (PeopleID, PeopleName) " +
sPersonID + ", " + sPersonName;
ProcLotCmd = new SqlCommand(sSQLStatement, myPrivConnection);

ProcLotCmd.Transaction = myTransaction;
ProcLotCmd.ExecuteNonQuery();
myTransaction.Commit();
}
catch (Exception ex)
{
myTransaction.Rollback();
}
}
finally
{
// Close the connection.
if (myPrivConnection.State == ConnectionState.Open)
myPrivConnection.Close();
}
}

 

[Gina_Marano]

sorry my example covers writing to the database. If you want to read
from it use the datareader.

~Gina~

 

[Linda Liu [MSFT]]

Hi Michael,

The reason that the new record isn't saved to the DB is that the new row
has not been added to the Version datatable before you call
versionTableAdapter.Update(newRow).

You should add the following line of code before you call the Update method
of the versionTableAdapter.

myTestDataSet.Version.Rows.Add(newRow);

Hope this helps.
If you have anything unclear, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[sloan]

Michael

Add a few

Console.WriteLine(myTestDataSet.GetXml());
lines into your debugging process.

That will show you more quickly than anything whats going on.

But yes, another poster is correct.

myTestDataSet.Version.AddVersionRow (newRow);
(or something like that) is needed.




I have written a few applications to read data from a database but now, as
I turn my attention to writing to a database, I am apparently missing some
fundamentals. I created a new database with a new (empty) table called
Version. I then created a new data source in the Data Sources pane in
Visual Studio 2005, yielding a DataSet named MyTestDataSet to reference
the Version table. I then created a short WinForms program to just run
this method:

private void TestIt()
{
MyTestDataSet myTestDataSet = new MyTestDataSet();
MyTestDataSetTableAdapters.VersionTableAdapter versionTableAdapter
= new MyTestDataSetTableAdapters.VersionTableAdapter();
MyTestDataSet.VersionRow newRow = myTestDataSet.Version.NewVersionRow();
newRow.Server = "server A";
newRow.Version = "v1";
newRow.RecordDateTime = DateTime.Now;
versionTableAdapter.Update(newRow);
}

It runs without error or exception, but the table remains empty. Why?

 

[michael sorens]

The suggestion to add the new row to the dataset, as in --
myTestDataSet.Version.Rows.Add(newRow);

--does in fact work, but it leaves me befuddled.
If the xxxTableAdapter.Update method relies on the underlying DataSet, why
does the Update method require an argument at all? And what effect does
that choice of argument make?

My reading of the MSDN docs led me to believe that an Update with a
DataSet would essentially compare the entire DataSet with the entire
database table, then execute inserts, updates, or deletes to make the
database match the DataSet. So when I saw that the Update method could be
given an argument of a DataRow array, I thought--aha!--it would then only
insert or update the items in the DataRows, leaving the rest of the
database table alone. But obviously my interpretation was wrong. Once I
added the above statement, then either of these:
versionTableAdapter.Update(newRow);
versionTableAdapter.Update(myTestDataSet);
produce the identical result, i.e. to add the new row to the database
table, and leave everything else in the database table alone.

Some clarification would be appreciated

 

[Linda Liu [MSFT]]

Hi Michael,

You're almost right.

When we call the Update method of the TableAdapter with a DataSet or
DataTable, all the changes including insert, update and delete in the
DataSet or DataTable would be saved to the DB.

When we call the Update method of the TableAdapetr with a DataRow, changes
including insert, update and delete in the DataRow will be saved to the DB,
leaving the rest rows of the datatable alone. Note that the precondition is
that the datarow BELONGS TO a datatable.

If we create a new row, we should add it to a DataTable before calling the
TableAdapter.Update method with the new row. Otherwise, TableAdapter won't
know which table the new row would be saved to.

Hope this helps.
If you have anything unclear, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

 

[michael sorens]

After I read your note a couple times, Linda, I think I realized
something, but still need some confirmation.
Update(DataSet) will examine every row in the DataSet (not in the
underlying DB table, then see if it needs updating
or needs inserting into the underlying DB table. That explains why the two
lines I indicated produce the same result, because the DataSet contained
just the one new row:
versionTableAdapter.Update(newRow);
versionTableAdapter.Update(myTestDataSet);

The one part I am missing is when would a delete operation be needed? If I
delete a row from the DataSet it is no longer there to be compared to the
DB table, right? Or is it retained in the DataSet and just marked as
"deleted" so the Update() will then know to delete it from the underlying
DB table?

Final thought: If I want an Update() to mirror the entire underlying DB
table, I could simply initialize it to the entire table, then proceed to
add rows, delete rows, modify rows... When I do an Update() in this case,
it will examine every row in the DataSet (and thus every row in the
underlying DB table) and synchronize them, right?

 

[Linda Liu [MSFT]]

Hi Michael,

As for the question of delete operation, the second comment is correct.
When we delete a row from a DataTable, this row is just marked as
'deleted'. In fact, this row won't be removed from the DatTable until we
call DataTable.AcceptChanges method.

As for your last question, your thought is right. We could add, delete or
modify rows in the DataTable and then call the Update method to save all
these changes to the DB at one time.

If you have any concerns, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support