How do you save a single DataRow in a DataSet?

  • Thread starter Thread starter Vance Kessler
  • Start date Start date
V

Vance Kessler

I am wondering if there is a way to save a single DataRow in a DataSet. I
already have a reference to the DataRow I wish to save but I cannot see how
to update anything other than the entire DataSet.

Here is the reason I need to do this. I have designed some business objects
to use a DataRow (from a typed DataSet) to store the various settings for
each object. I also have a collection object which can hold a number of
these business objects. When using a collection all objects in that
collection use a shared DataSet to hold their data. When working with
single business objects, each one will have its own DataSet. All these
objects are fairly simple objects and do not span multiple tables. Is this
a reasonable approach? I am fairly new to ADO.NET.

This works fine for a single object when you want to save changes (it just
updates the single DataRow in its own DataSet). However, unexpected results
can occur when using a collection since they share a single DataSet.
Calling Save in a business object in a collection will actually save changes
to ALL business objects in that collection since they all share a single
DataSet which is not what I want. The collection object itself has a Save
method that will save the changes to all objects in the collection if that
is really what you wanted to do.

So, I would like to be able to Save changes to a single business object
without worrying whether it happens to be in a collection or not.

My initial thoughts are that maybe I could call GetChanges to generate a new
DataSet and then call RejectChanges on all the rows except the one I really
want to save. I could then call Update() and save that single row. Then I
would merge that dataset back into the original set. My concern is that by
calling RejectChanges on the other rows it would merge that information back
into the original DataSet and Reject those changes there as well.

Thanks
Vance
 
Hi Vance,

There is an overload Update method that accepts an array or rows - you might
pass something like
new DataRow[]{yourRow}
 
Well look at that IntelliSense pays off when the Help files don't! This
version of the Update function is NOT in the help files!

Well, that sounds like just what I need. I assume this will update the
DataSet to show this row has not been modified?

Thank you VERY much. You were much more helpful than the Help files. :)


Miha Markic said:
Hi Vance,

There is an overload Update method that accepts an array or rows - you might
pass something like
new DataRow[]{yourRow}

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


Vance Kessler said:
I am wondering if there is a way to save a single DataRow in a DataSet. I
already have a reference to the DataRow I wish to save but I cannot see how
to update anything other than the entire DataSet.

Here is the reason I need to do this. I have designed some business objects
to use a DataRow (from a typed DataSet) to store the various settings for
each object. I also have a collection object which can hold a number of
these business objects. When using a collection all objects in that
collection use a shared DataSet to hold their data. When working with
single business objects, each one will have its own DataSet. All these
objects are fairly simple objects and do not span multiple tables. Is this
a reasonable approach? I am fairly new to ADO.NET.

This works fine for a single object when you want to save changes (it just
updates the single DataRow in its own DataSet). However, unexpected results
can occur when using a collection since they share a single DataSet.
Calling Save in a business object in a collection will actually save changes
to ALL business objects in that collection since they all share a single
DataSet which is not what I want. The collection object itself has a Save
method that will save the changes to all objects in the collection if that
is really what you wanted to do.

So, I would like to be able to Save changes to a single business object
without worrying whether it happens to be in a collection or not.

My initial thoughts are that maybe I could call GetChanges to generate a new
DataSet and then call RejectChanges on all the rows except the one I really
want to save. I could then call Update() and save that single row.
Click to expand...
Then
I
would merge that dataset back into the original set. My concern is that by
calling RejectChanges on the other rows it would merge that information back
into the original DataSet and Reject those changes there as well.

Thanks
Vance
Click to expand...
Click to expand...
 
For anyone who is interested I wrote a little sample app to test the update
method passing in an array of rows. And it works just as you would expect.
The dataset is modified to show the updated row no longer has changes. here
is my sample.

Table to test with:

SQL table creation:

CREATE TABLE [Messages] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[Msg] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time] [datetime] NOT NULL CONSTRAINT [DF__Messages__Time__6D381B7D]
DEFAULT (getdate())
) ON [PRIMARY]
GO

INSERT INTO Messages(Msg) Values('Row1')
INSERT INTO Messages(Msg) Values('Row2')
INSERT INTO Messages(Msg) Values('Row3')
INSERT INTO Messages(Msg) Values('Row4')



Sample c# App:
----------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;

namespace SaveSingleRow
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
SqlConnection oCon = new SqlConnection("server=<YourServer>;
Trusted_Connection=yes; database=<YourDatabase>");
oCon.Open();

// This is temporary data so clean it up when done with it
using (DataSet ds = new DataSet() )
{
SqlCommand cmd = new SqlCommand("select * from Messages",
oCon);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);

sqlDataAdapter.Fill(ds, "Messages");
new SqlCommandBuilder(sqlDataAdapter);

if (1 == ds.Tables.Count)
{
Console.WriteLine("\nOriginal Values");
foreach (DataRow dr in ds.Tables["Messages"].Rows)
{
Console.WriteLine("{0}: {1}", dr["Time"].ToString(),
dr["Msg"].ToString() );
}

Console.WriteLine("\nMaking some changes");
int iCount = 0;
foreach (DataRow dr in ds.Tables["Messages"].Rows)
{
++iCount;
string sExtra = "";
if ((iCount & 1) == 0)
{
sExtra = "Updated";
dr["Msg"] = dr["Msg"].ToString() + ".";
}
Console.WriteLine("{0}: {1} {2}",
dr["Time"].ToString(), dr["Msg"].ToString(), sExtra );
}

Console.WriteLine("Changed DataSet:\n{0}",
ds.GetChanges().GetXml() );

Console.WriteLine("\nSave some changes");
iCount = 0;
foreach (DataRow dr in ds.Tables["Messages"].Rows)
{
++iCount;
string sExtra = "";
if (2 == iCount)
{
sExtra = "Saved";
DataRow[] arRows = { dr };
sqlDataAdapter.Update(arRows);
}
Console.WriteLine("{0}: {1} {2}",
dr["Time"].ToString(), dr["Msg"].ToString(), sExtra );
}
Console.WriteLine("\nFinal State\n{0}",
ds.GetChanges().GetXml() );
}
}
oCon.Close();
}
}
}
 
Thank you VERY much. You were much more helpful than the Help files. :)
Click to expand...

Hehe, I wish (press "send" in favorite newsreader instead of F1 :) ).
Acutally, it is listed in help files :)
 
Back
Top