Row deletion and DataRowVersion.Original

  • Thread starter Thread starter Scott Baker
  • Start date Start date
S

Scott Baker

Sort of a newby question...

I have a read only list view that is populated with information from a
dataset. If the user selects a listview item and presses the delete button,
then the item is removed fromt he list, and the underlying dataset is marked
as delete (all conditional and error checking has been removed for this
post):
expr = "RelationshipName ='" + relationshipName + "' AND LinkFromID = '" +
linkFromID + "' AND LinkToID = '" + linkToID + "'";
DataRow[] rows = this.objectObjectsTable.Select(expr,
relationship.LinkFromIDColumn, DataViewRowState.Unchanged);
rows[0].Delete();

According to all of the ADO.NET references I have seen, this does not
physically remove the data from the dataset, but simply marks its RowState
as deleted. And true to its word, when I interogate it in the quick-watch
window, it is marked as deleted.

Later in the session, I want to update the database to reflected the deleted
row. This is done as follows:

... Method Name ..

curTable = this.dataSet.Tables[relationship.TableName];
curAdapter = new OleDbDataAdapter();

OleDbCommand deleteCmd = this.deleteRelationshipCmds[0];
deleteCmd.Connection = oleDbConnection;
deleteCmd.Transaction = dbTransaction;
curAdapter.DeleteCommand = deleteCmd;

DataView relationshipView = new DataView(curTable, "", "",
DataViewRowState.Deleted);

IEnumerator enumRelationships = relationshipView.GetEnumerator();
while (enumRelationships.MoveNext() && !bAbort)
{
DataRowView curRelationshipView = (DataRowView) enumRelationships.Current;
objUpdateRows[0] = curRelationshipView.Row;

try
{
if(objUpdateRows[0].RowState == DataRowState.Deleted)
{
curAdapter.DeleteCommand.Parameters.Add(relationship.Name,
objUpdateRows[0][relationship.Name, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkFromIDColumn,
objUpdateRows[0][relationship.LinkFromIDColumn, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkToIDColumn,
objUpdateRows[0][relationship.LinkToIDColumn, DataRowVersion.Original]);
}
rowCount += curAdapter.Update(objUpdateRows);
}

catch { ... }
}

The building of the delete statement is done like this:

private static OleDbCommand
GenerateDeleteCommand(Elsinore.Engine.ClassRelationship objRelationship)
{
OleDbCommand cmd = null;
if (objRelationship != null)
{
string relationshipTableName = objRelationship.TableName;
if (relationshipTableName != null && relationshipTableName.Length > 0)
{
if (objRelationship.TableName == "Object_Objects")
{
string sqlText = "DELETE FROM " + Constants.DbOwner +
Constants.DbEscapePrefix + Constants.DbNamePrefix +
objRelationship.TableName + Constants.DbEscapeSuffix;
sqlText += " WHERE RelationshipName=? AND LinkFromID=? AND LinkToID=?";
cmd = new OleDbCommand(sqlText);
cmd.Parameters.Add("@p1", OleDbType.Char, 64, "RelationshipName");
cmd.Parameters.Add("@p2", OleDbType.Guid, 16, "LinkFromID");
cmd.Parameters.Add("@p3", OleDbType.Guid, 16, "LinkToID");
}
}
}
return cmd;
}

So, what I want to do is during the OleDBAdapter.Update call, is remove the
rows that are marked as Deleted. Since the current version of the Row's
ItemsArray is inaccessible (I assume because it is marked as deleted), then
I ask each column in the row to return me the original version of the data
for which I can manually add as parameters to my delete command.

Alas, this does not work, the original version of the row does not support
access to the items array. So, does anyone have a suggestion about this
problem that they could share with me? Thanks in advance.

Scott
 
Hi Scott,

You are right for the first part.
While you overcode for second.
Just call Update method on table with records to delete - it will do
everything you need.
You might configure the delete command in right way though.
See
Creating Data Adapters
..net help topic.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com



Scott Baker said:
Sort of a newby question...

I have a read only list view that is populated with information from a
dataset. If the user selects a listview item and presses the delete button,
then the item is removed fromt he list, and the underlying dataset is marked
as delete (all conditional and error checking has been removed for this
post):
expr = "RelationshipName ='" + relationshipName + "' AND LinkFromID = '" +
linkFromID + "' AND LinkToID = '" + linkToID + "'";
DataRow[] rows = this.objectObjectsTable.Select(expr,
relationship.LinkFromIDColumn, DataViewRowState.Unchanged);
rows[0].Delete();

According to all of the ADO.NET references I have seen, this does not
physically remove the data from the dataset, but simply marks its RowState
as deleted. And true to its word, when I interogate it in the quick-watch
window, it is marked as deleted.

Later in the session, I want to update the database to reflected the deleted
row. This is done as follows:

.. Method Name ..

curTable = this.dataSet.Tables[relationship.TableName];
curAdapter = new OleDbDataAdapter();

OleDbCommand deleteCmd = this.deleteRelationshipCmds[0];
deleteCmd.Connection = oleDbConnection;
deleteCmd.Transaction = dbTransaction;
curAdapter.DeleteCommand = deleteCmd;

DataView relationshipView = new DataView(curTable, "", "",
DataViewRowState.Deleted);

IEnumerator enumRelationships = relationshipView.GetEnumerator();
while (enumRelationships.MoveNext() && !bAbort)
{
DataRowView curRelationshipView = (DataRowView) enumRelationships.Current;
objUpdateRows[0] = curRelationshipView.Row;

try
{
if(objUpdateRows[0].RowState == DataRowState.Deleted)
{
curAdapter.DeleteCommand.Parameters.Add(relationship.Name,
objUpdateRows[0][relationship.Name, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkFromIDColumn,
objUpdateRows[0][relationship.LinkFromIDColumn, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkToIDColumn,
objUpdateRows[0][relationship.LinkToIDColumn, DataRowVersion.Original]);
}
rowCount += curAdapter.Update(objUpdateRows);
}

catch { ... }
}

The building of the delete statement is done like this:

private static OleDbCommand
GenerateDeleteCommand(Elsinore.Engine.ClassRelationship objRelationship)
{
OleDbCommand cmd = null;
if (objRelationship != null)
{
string relationshipTableName = objRelationship.TableName;
if (relationshipTableName != null && relationshipTableName.Length > 0)
{
if (objRelationship.TableName == "Object_Objects")
{
string sqlText = "DELETE FROM " + Constants.DbOwner +
Constants.DbEscapePrefix + Constants.DbNamePrefix +
objRelationship.TableName + Constants.DbEscapeSuffix;
sqlText += " WHERE RelationshipName=? AND LinkFromID=? AND LinkToID=?";
cmd = new OleDbCommand(sqlText);
cmd.Parameters.Add("@p1", OleDbType.Char, 64, "RelationshipName");
cmd.Parameters.Add("@p2", OleDbType.Guid, 16, "LinkFromID");
cmd.Parameters.Add("@p3", OleDbType.Guid, 16, "LinkToID");
}
}
}
return cmd;
}

So, what I want to do is during the OleDBAdapter.Update call, is remove the
rows that are marked as Deleted. Since the current version of the Row's
ItemsArray is inaccessible (I assume because it is marked as deleted), then
I ask each column in the row to return me the original version of the data
for which I can manually add as parameters to my delete command.

Alas, this does not work, the original version of the row does not support
access to the items array. So, does anyone have a suggestion about this
problem that they could share with me? Thanks in advance.

Scott
Click to expand...
 
After a healthly lunch break, I realized the folly:

System.Guid linkFromID = (System.Guid) curLinkRow[0]["LinkFromID",
DataRowVersion.Original];
System.Guid linkToID = (System.Guid) curLinkRow[0]["LinkToID",
DataRowVersion.Original];
objectObjectsAdapter.DeleteCommand.Parameters["@p1"].Value =
relationship.Name;
objectObjectsAdapter.DeleteCommand.Parameters["@p2"].Value = linkFromID;
objectObjectsAdapter.DeleteCommand.Parameters["@p3"].Value = linkToID;

as opposed to the Add method call I was attempting to make.

Thanks anyways
Scott Baker said:
Sort of a newby question...

I have a read only list view that is populated with information from a
dataset. If the user selects a listview item and presses the delete button,
then the item is removed fromt he list, and the underlying dataset is marked
as delete (all conditional and error checking has been removed for this
post):
expr = "RelationshipName ='" + relationshipName + "' AND LinkFromID = '" +
linkFromID + "' AND LinkToID = '" + linkToID + "'";
DataRow[] rows = this.objectObjectsTable.Select(expr,
relationship.LinkFromIDColumn, DataViewRowState.Unchanged);
rows[0].Delete();

According to all of the ADO.NET references I have seen, this does not
physically remove the data from the dataset, but simply marks its RowState
as deleted. And true to its word, when I interogate it in the quick-watch
window, it is marked as deleted.

Later in the session, I want to update the database to reflected the deleted
row. This is done as follows:

.. Method Name ..

curTable = this.dataSet.Tables[relationship.TableName];
curAdapter = new OleDbDataAdapter();

OleDbCommand deleteCmd = this.deleteRelationshipCmds[0];
deleteCmd.Connection = oleDbConnection;
deleteCmd.Transaction = dbTransaction;
curAdapter.DeleteCommand = deleteCmd;

DataView relationshipView = new DataView(curTable, "", "",
DataViewRowState.Deleted);

IEnumerator enumRelationships = relationshipView.GetEnumerator();
while (enumRelationships.MoveNext() && !bAbort)
{
DataRowView curRelationshipView = (DataRowView) enumRelationships.Current;
objUpdateRows[0] = curRelationshipView.Row;

try
{
if(objUpdateRows[0].RowState == DataRowState.Deleted)
{
curAdapter.DeleteCommand.Parameters.Add(relationship.Name,
objUpdateRows[0][relationship.Name, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkFromIDColumn,
objUpdateRows[0][relationship.LinkFromIDColumn, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkToIDColumn,
objUpdateRows[0][relationship.LinkToIDColumn, DataRowVersion.Original]);
}
rowCount += curAdapter.Update(objUpdateRows);
}

catch { ... }
}

The building of the delete statement is done like this:

private static OleDbCommand
GenerateDeleteCommand(Elsinore.Engine.ClassRelationship objRelationship)
{
OleDbCommand cmd = null;
if (objRelationship != null)
{
string relationshipTableName = objRelationship.TableName;
if (relationshipTableName != null && relationshipTableName.Length > 0)
{
if (objRelationship.TableName == "Object_Objects")
{
string sqlText = "DELETE FROM " + Constants.DbOwner +
Constants.DbEscapePrefix + Constants.DbNamePrefix +
objRelationship.TableName + Constants.DbEscapeSuffix;
sqlText += " WHERE RelationshipName=? AND LinkFromID=? AND LinkToID=?";
cmd = new OleDbCommand(sqlText);
cmd.Parameters.Add("@p1", OleDbType.Char, 64, "RelationshipName");
cmd.Parameters.Add("@p2", OleDbType.Guid, 16, "LinkFromID");
cmd.Parameters.Add("@p3", OleDbType.Guid, 16, "LinkToID");
}
}
}
return cmd;
}

So, what I want to do is during the OleDBAdapter.Update call, is remove the
rows that are marked as Deleted. Since the current version of the Row's
ItemsArray is inaccessible (I assume because it is marked as deleted), then
I ask each column in the row to return me the original version of the data
for which I can manually add as parameters to my delete command.

Alas, this does not work, the original version of the row does not support
access to the items array. So, does anyone have a suggestion about this
problem that they could share with me? Thanks in advance.

Scott
Click to expand...
 
Hey Miah,

I tried just a call to update on the table itself. This is the error I get.
Any suggestions:

The row has been removed from a table and does not have any data.
BeginEdit() will allo creation of new data in this row.

Scott

Miha Markic said:
Hi Scott,

You are right for the first part.
While you overcode for second.
Just call Update method on table with records to delete - it will do
everything you need.
You might configure the delete command in right way though.
See
Creating Data Adapters
.net help topic.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com



Scott Baker said:
Sort of a newby question...

I have a read only list view that is populated with information from a
dataset. If the user selects a listview item and presses the delete button,
then the item is removed fromt he list, and the underlying dataset is marked
as delete (all conditional and error checking has been removed for this
post):
expr = "RelationshipName ='" + relationshipName + "' AND LinkFromID = '" +
linkFromID + "' AND LinkToID = '" + linkToID + "'";
DataRow[] rows = this.objectObjectsTable.Select(expr,
relationship.LinkFromIDColumn, DataViewRowState.Unchanged);
rows[0].Delete();

According to all of the ADO.NET references I have seen, this does not
physically remove the data from the dataset, but simply marks its RowState
as deleted. And true to its word, when I interogate it in the quick-watch
window, it is marked as deleted.

Later in the session, I want to update the database to reflected the deleted
row. This is done as follows:

.. Method Name ..

curTable = this.dataSet.Tables[relationship.TableName];
curAdapter = new OleDbDataAdapter();

OleDbCommand deleteCmd = this.deleteRelationshipCmds[0];
deleteCmd.Connection = oleDbConnection;
deleteCmd.Transaction = dbTransaction;
curAdapter.DeleteCommand = deleteCmd;

DataView relationshipView = new DataView(curTable, "", "",
DataViewRowState.Deleted);

IEnumerator enumRelationships = relationshipView.GetEnumerator();
while (enumRelationships.MoveNext() && !bAbort)
{
DataRowView curRelationshipView = (DataRowView) enumRelationships.Current;
objUpdateRows[0] = curRelationshipView.Row;

try
{
if(objUpdateRows[0].RowState == DataRowState.Deleted)
{
curAdapter.DeleteCommand.Parameters.Add(relationship.Name,
objUpdateRows[0][relationship.Name, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkFromIDColumn,
objUpdateRows[0][relationship.LinkFromIDColumn, DataRowVersion.Original]);
curAdapter.DeleteCommand.Parameters.Add(relationship.LinkToIDColumn,
objUpdateRows[0][relationship.LinkToIDColumn, DataRowVersion.Original]);
}
rowCount += curAdapter.Update(objUpdateRows);
}

catch { ... }
}

The building of the delete statement is done like this:

private static OleDbCommand
GenerateDeleteCommand(Elsinore.Engine.ClassRelationship objRelationship)
{
OleDbCommand cmd = null;
if (objRelationship != null)
{
string relationshipTableName = objRelationship.TableName;
if (relationshipTableName != null && relationshipTableName.Length > 0)
{
if (objRelationship.TableName == "Object_Objects")
{
string sqlText = "DELETE FROM " + Constants.DbOwner +
Constants.DbEscapePrefix + Constants.DbNamePrefix +
objRelationship.TableName + Constants.DbEscapeSuffix;
sqlText += " WHERE RelationshipName=? AND LinkFromID=? AND LinkToID=?";
cmd = new OleDbCommand(sqlText);
cmd.Parameters.Add("@p1", OleDbType.Char, 64, "RelationshipName");
cmd.Parameters.Add("@p2", OleDbType.Guid, 16, "LinkFromID");
cmd.Parameters.Add("@p3", OleDbType.Guid, 16, "LinkToID");
}
}
}
return cmd;
}

So, what I want to do is during the OleDBAdapter.Update call, is remove the
rows that are marked as Deleted. Since the current version of the Row's
ItemsArray is inaccessible (I assume because it is marked as deleted), then
I ask each column in the row to return me the original version of the data
for which I can manually add as parameters to my delete command.

Alas, this does not work, the original version of the row does not support
access to the items array. So, does anyone have a suggestion about this
problem that they could share with me? Thanks in advance.

Scott
Click to expand...
Click to expand...
 
Hi Scott,

Scott Baker said:
Hey Miah,

I tried just a call to update on the table itself. This is the error I get.
Any suggestions:

The row has been removed from a table and does not have any data.
BeginEdit() will allo creation of new data in this row.
Click to expand...

The parameters are declared badly. They should be like that (note the
DataRowVersion):

OleDbParameter p = new OleDbParameter("@p1", OleDbType.Char, 64,
ParameterDirection.Input, false, 0, 0, "RelationshipName",
DataRowVersion.Original, null);
cmd.Parameters.Add(p);

instead of

cmd.Parameters.Add("@p1", OleDbType.Char, 64, "RelationshipName");

That is true for all three parameters.
 
Back
Top