How do you clear a datatable?

[Tony Girgenti]

Hello.

Working on a VS2005 SP1 Windows form program in .NET 2.0, on XP Pro, SP2.

I have a datatable with no keys and just three columns.

How do you clear a datatable to no rows ?

I tried :

itemsDataTable.Clear()
itemsDataTable.AcceptChanges()


itemsDataTable.Rows.Clear()
itemsDataTable.AcceptChanges()

I can load data into it it, but cannot clear it.

Searching the internet shows it as that simple.

Any help would be gratefully appreciated.

Thanks,
Tony

 

[William \(Bill\) Vaughn]

DataTable.Clear should remove all rows. There is no need to call
AcceptChanges. What makes you think the local DataTable object has any rows
after you call this method? This will NOT clear the database table--just the
in-memory DataTable object.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Tony Girgenti]

Hello Bill.

I am absolutely perplexed and frustated by this whole dataset/database
concept. I thought i understood it, but now i'm totally confused.

I thought a dataset was supposed to be disconnected from the physical
database.

When i look at a datatable properties in the dataset designer, it defines it
as the Items DataTable. If i right click on the Items DataTable in the
Dataset designer and select Preview Data..., and click on the Preview
button, it shows all of the rows. So if i did a itemsDataBase.Clear, why is
the data still there?

When i look at the database in Server Explorer, right click on the Items
table there and click Show Table Data, it shows the same data.

I'm sure that you know way more than i do about this stuff, but i just don't
get it.

Thanks,
Tony

 

[RobinS]

Because you are clearing the data table in memory, not deleting the records
from the database. If you want to delete the records in the database, you
can ShowTableData in the ServerExplorer on that table, select all the
records, and delete them, or do NewQuery and type in "DELETE FROM
tablename" and execute it.

This is probably explained in Bill's book, by the way. You should check it
out.

Robin S.
--------------------------------

 

[pvdg42]

Hello Bill.

I am absolutely perplexed and frustated by this whole dataset/database
concept. I thought i understood it, but now i'm totally confused.

I thought a dataset was supposed to be disconnected from the physical
database.

When i look at a datatable properties in the dataset designer, it defines
it as the Items DataTable. If i right click on the Items DataTable in the
Dataset designer and select Preview Data..., and click on the Preview
button, it shows all of the rows. So if i did a itemsDataBase.Clear, why
is the data still there?

When i look at the database in Server Explorer, right click on the Items
table there and click Show Table Data, it shows the same data.

I'm sure that you know way more than i do about this stuff, but i just
don't get it.

Thanks,
Tony

Everything you say about DataTable objects is correct, except for what
happens when you look at DataTable contents in the Dataset Designer. When
you preview data in the designer, the fill() method associated with the
DataTable object executes to show you what the DataTable *will* hold when
filled in your application when it runs. If you execute the Clear() method
in your application, after filling the DataTable, all rows in the DataTable
will be deleted.

Bill has already pointed out that none of the above has any effect on the
actual database table, so when you access the preview from Data Designer
after executing your program, the DataTable is *refilled* with the same data
(from the database) as before.

To test the effects of Clear(), add some code to your app to access the
Count property of your DataTable's DataRowCollection, after Clear executes.

http://msdn2.microsoft.com/en-us/library/system.data.datarowcollection_members.aspx

 

[Tony Girgenti]

Hello pvd.

Thanks for your reply. I understand now where the data is at different
points in my program. I was confused about DataTables in the dataset as
opposed to tables in the database.

I need to use a DELETE query to initialize the table in the database. Right?

Thanks,
Tony

 

[RobinS]

If you want to remove all of the data in the table in the database, you
need to use a DELETE query. ("DELETE FROM myTable" will do it.)

Good luck.
Robin S.
------------------------

 

[Tony Girgenti]

That's what i did Robin. Works Great.

Thanks,
Tony

 

[William \(Bill\) Vaughn]

Yes, FWIW TRUNCATE is faster than delete. It's not logged and let's the QO
know that it need not deal with other issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Rad [Visual C# MVP]]

Yes, FWIW TRUNCATE is faster than delete. It's not logged and let's the QO
know that it need not deal with other issues.

Though truncating is not always possible -- foreign key tables and
replicating tables for instance...

 

[RobinS]

That's true, I forget about Truncate because I so rarely clear out my data
tables. Thanks for the catch!

I bet that's covered fully in your book. ;-)

Robin S.
------------------------------------------------