How To Add/Update Record In ADO.NET

[Jeff Gaines]

I am trying to learn ADO.NET having previously used ADODB, the data I am
using is in an Access 2007 database.

I want to know whether I should be updating an existing record or adding a
new one, in ADODB I would use Recordset.EOF to decide which action was
appropriate.

I have written the following for ADO.NET:

object intNumRows = oleAdaptor.SelectCommand.ExecuteScalar();
if (Convert.ToInt32(intNumRows) != 0)
{
CreateUpdateCommand(ref oleAdaptor, oleConnection);
oleAdaptor.UpdateCommand.ExecuteNonQuery();
}
else
{
CreateInsertCommand(ref oleAdaptor, oleConnection);
oleAdaptor.InsertCommand.ExecuteNonQuery();
}

It works in that if I pass in a valid record number it gets updated and
for a non existent record number a new record is added. Using
ExecuteNonQuery() instead of ExecuteScalar() always returns 0 so that
doesn't help.

I can post the whole class if it would help but I am trying to avoid
clutter. Is there a better way to do this? I am not using DataAdapter
controls, I want to learn how to do this in code. Ultimately I will alter
an existing app that creates a class for accessing data via ADODB to do
the same for ADO.NET, but I need to know what's going on first

The Record Number is an auto number field in Access and is the only unique
identifier.

Many thanks.

 

[Cor Ligthert[MVP]]

Jeff,

Forget ADODB, most regulars in this newsgroup see this as history. Hava a
look at AdoNet and Dataset. Don't think that a Dataset is a replacement for
a recordset. The DataTable, which is a member of a dataset meets the most
the recordSet.

I would advice you to buy a good book, I myself find the Hitchhikersguide
very well, especially because it is practical.

http://www.hitchhikerguides.net/

Cor

 

[William Vaughn]

Thanks Cor.

Actually, Jeff, since you're re-writing, I might consider another DBMS
engine--one that's not being phased out by Microsoft. SQL Server Compact
Edition or Express Edition come to mind. This gives you the flexibility to
write more sophisticated SQL statements that include logic to execute the
appropriate command in a single round-trip.
Consider that if JET says the row is not present, you might still get an
concurrency collision when another application is sharing the database and
inserts a row after your SELECT but before your INSERT. I might try to run
this in a transaction or simply try the INSERT first. In this case you'll
have to consider that your instance of the application is overlaying someone
else's data. Is your data right or is theirs?

If this is a single-user application, then SQLCe is likely ideal for your
needs. It also makes the logic simpler. In this case you can create an ADO
classic-like Recordset object that can be updated in place.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
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)

 

[Jeff Gaines]

Jeff,

Forget ADODB, most regulars in this newsgroup see this as history. Hava a
look at AdoNet and Dataset. Don't think that a Dataset is a replacement
for a recordset. The DataTable, which is a member of a dataset meets the
most the recordSet.

I am trying to get myself up to date
I'm a bit confused though, I thought what I posted was ADO.NET, if it
isn't I am getting it wrong in a big way!
All the examples I have seen cover adding and deleting records but I
haven't found one to update a record, i.e the record exists but some of
the fields need updating. Is there a way to know if the record already
exists, and so should be updated, or doesn't, and so should be added?

I would advice you to buy a good book, I myself find the Hitchhikersguide
very well, especially because it is practical.

http://www.hitchhikerguides.net/

I will certainly consider that but I only write apps for my own use so
they are pretty straightforward.

 

[Jeff Gaines]

Thanks Cor.

Actually, Jeff, since you're re-writing, I might consider another DBMS
engine--one that's not being phased out by Microsoft. SQL Server Compact
Edition or Express Edition come to mind. This gives you the flexibility to
write more sophisticated SQL statements that include logic to execute the
appropriate command in a single round-trip.
Consider that if JET says the row is not present, you might still get an
concurrency collision when another application is sharing the database and
inserts a row after your SELECT but before your INSERT. I might try to run
this in a transaction or simply try the INSERT first. In this case you'll
have to consider that your instance of the application is overlaying
someone else's data. Is your data right or is theirs?

If this is a single-user application, then SQLCe is likely ideal for your
needs. It also makes the logic simpler. In this case you can create an ADO
classic-like Recordset object that can be updated in place.

hth

Hi William.

I have just paid a small fortune for Access 2007 so I don't want to give
up on it yet!
I write small apps as a hobby, all single user, the dog can't use the
computer - he doesn't know my password!

As I said in my reply to Cor, I am looking for a simple way of knowing if
a record exists or not so I can decide whether to update it or add a new
record.

 

[William Vaughn]

That's okay--we get all skill levels here and people doing everything from
simple to complex stuff. When it comes time to choose it's probably best to
use the free stuff first--especially when you're on a budget. Access is an
entirely separate and distinct way of handling data. It's just fine for home
and (very) small business use. It has a number of limitations that don't
make it particularly suitable where data has to be secure or shared beyond a
few casual users.

Keep asking questions--just tell them the whole story when you do.
As to the "test to see if it's already there" issue, put a unique primary
key on the Identity column. Next, when it's time to add a row, it does not
make any difference if it's there or not--it's always added. If you want to
make sure there aren't duplicate rows (for example, by Name or StoreName,
put a unique index on those columns which make up the unique key of the
table. Then JET (the database engine) prevents duplicates from being added.
In this case, just do your INSERT. If the row is already there (there is
already someone with the same name in the database), the INSERT will fail.
If it does, turn around and execute the UPDATE.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Cor Ligthert[MVP]]

Jeff,

It is very easy to see if a record exist, if it is on the database you can
use the ExecuteScalar funciton.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(VS.71).aspx


If it is about the dataset you can see the rowstate of a row in the table.
If it is a new one, than that is set in the row, if it is an updated one
than it is set the same, if it is deleted, than you can't see the row,
however you can see its rowstate.

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


If you want recordset behaviour on an SQL server with a datatable
http://www.vb-tips.com/TableAdapterTransaction.aspx


Maybe is this as well a nice tip.
http://www.vb-tips.com/CreateMDB.aspx

Succes

Cor

 

[Jeff Gaines]

On 09/09/2007 in message
<[email protected]> Cor Ligthert[MVP]
wrote:

Cor and William, many thanks for your replies, I think I have made some
progress

Generally I will know if a record exists or not so I have separated the
Add/Update functions. In doing this I have added functionality to return
the new record number after a record is added. It was actually what
started the current train of questions and when I realised I needed to
upgrade from Access 97.

However, I have ended up using different methods in Add and Update to
achieve this.

In the Add function I create a new DataRow, populate it, add it to the
existing rows and call OleDbDataAdapter.Update to add it to the database.
This triggers the OleDbDataAdapter.RowUpdated event and enables me to
capture the new record number (an Access AutoNumber field) and return it
which is exactly what I wanted.

In the Update function I create a Select Command which selects the record
by RecordNumber, then create an Update Command with an "insert" string to
put the new data in. I then call
OleDbDataAdapter.UpdateCommand.ExecuteNonQuery() to update the database.

Is that an "acceptable" way of doing things or should I try and use the
same method for both adding and updating?

I also have a question relating to the architecture of an application. I
usually have a main form (these are all Windows Forms apps) and then a
separate class for the data. For example my ToolBox app has a class
cHelpCategory so I can keep information in categories and cHelpRecord
which is the data relating to a specific help subject. Using ADODB every
instance of those classes had all the functions needed to open, write to,
save and close the database.

I wonder in my move to ADO.NET whether I should re-think that approach and
have a data class where several/many instances will be created and a
separate (perhaps static) data update class which has static functions and
just reads from/writes to the database. The data update class would then
be passed an instance of the data class to work with. It would save
creating an OleDbConnection, OleDbDataAdapter, DataSet and DataTable for
every instance of the data class.

Apologies if that is obvious and what the professionals already do, I am
probably carrying forward dirty habits from my VB6 days

Many thanks again.

 

[William \(Bill\) Vaughn]

The transition from VB6/ADO classic is easiest if you consider they are
really radically diferent than VB.NET and ADO.NET. No, the old paradigms
don't map well to VB.NET data access architectures. Consider that Visual
Studio has several class-generators that do what you need done without you
writing hardly any code at all. These can be extended with "partial" classes
that extend the generated code classes so you can cusomize the approach.

--
____________________________________
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)

 

[WenYuan Wang [MSFT]]

Hello Jeff,
Thanks for Cor and William help.

It seems Cor and William has shared many great ideas in this thread.
I just want to check you have anything else we can help with. Please feel
free to update here. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeff Gaines]

Hello Jeff,
Thanks for Cor and William help.

It seems Cor and William has shared many great ideas in this thread.
I just want to check you have anything else we can help with. Please feel
free to update here. We are glad to assist you.

Yes indeed

It makes for a good learning process with support from people whose
commercial fees I probably couldn't afford!