I have two questions about the TableAdapter Configuration Wizard

[Big Daddy]

1. I have a table in the database (SQL Server) that I created an
adapter for using Visual Studio 2005. It has
select/insert/delete/update methods created, but not using stored
procedures, because I check the option that says:

Create methods to send updates directly to the database
(GenerateDBDirectMethods)

The INSERT sql that is created by the wizard is:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId);

But I want the INSERT sql to be:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId); select * from
Patients WHERE (PatientNo = @@IDENTITY)

Because there's an identity autonumber in the table that I want to read
back into my DataTable after in the insert. So I manually added the
text

select * from Patients WHERE (PatientNo = @@IDENTITY)

To the insert statement. Is there any way to make the wizard
automatically add this to the INSERT statement so I don't have to do it
manually?


2. I want to have a TableAdapter that encapsulates a bunch of stored
procedures, but that doesn't have a DataTable that goes with it. For
example, I might have 10 delete sprocs that I want to put into one
TableAdapter. When I create a new TableAdapter, I select the radio
button "use existing stored procedures". It forces me to have a SELECT
sproc, even though none of the sprocs I want to put in the adapter is a
SELECT. So if I choose a SELECT sproc first, it creates a DataAdapter
and DataTable for me. Then I can add all of my DELETE sprocs that I
want to. But I don't want the DataTable or the SELECT query at all. I
just want to put in a bunch of DELETE sprocs. Is there any way to do
what I want?

Thanks in advance,
John

 

[Cor Ligthert [MVP]]

Big Daddy,

Be aware that a where clause is nice, but as long as a row does not change,
the dataadapter (which the tableadapter inherits from) does never give a row
to the database to update.

To add to the designer created class you can add a partial clase where you
can put all your delete transact code in. You are than even able to recreate
the designer generated part.

I hope this gives some ideas,

Cor

 

[Big Daddy]

Thanks,
Does anyone have any ideas on question #2 from my original post?

Big Daddy,

Be aware that a where clause is nice, but as long as a row does not change,
the dataadapter (which the tableadapter inherits from) does never give a row
to the database to update.

To add to the designer created class you can add a partial clase where you
can put all your delete transact code in. You are than even able to recreate
the designer generated part.

I hope this gives some ideas,

Cor

1. I have a table in the database (SQL Server) that I created an
adapter for using Visual Studio 2005. It has
select/insert/delete/update methods created, but not using stored
procedures, because I check the option that says:

Create methods to send updates directly to the database
(GenerateDBDirectMethods)

The INSERT sql that is created by the wizard is:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId);

But I want the INSERT sql to be:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId); select * from
Patients WHERE (PatientNo = @@IDENTITY)

Because there's an identity autonumber in the table that I want to read
back into my DataTable after in the insert. So I manually added the
text

select * from Patients WHERE (PatientNo = @@IDENTITY)

To the insert statement. Is there any way to make the wizard
automatically add this to the INSERT statement so I don't have to do it
manually?


2. I want to have a TableAdapter that encapsulates a bunch of stored
procedures, but that doesn't have a DataTable that goes with it. For
example, I might have 10 delete sprocs that I want to put into one
TableAdapter. When I create a new TableAdapter, I select the radio
button "use existing stored procedures". It forces me to have a SELECT
sproc, even though none of the sprocs I want to put in the adapter is a
SELECT. So if I choose a SELECT sproc first, it creates a DataAdapter
and DataTable for me. Then I can add all of my DELETE sprocs that I
want to. But I don't want the DataTable or the SELECT query at all. I
just want to put in a bunch of DELETE sprocs. Is there any way to do
what I want?

Thanks in advance,
John

 

[William \(Bill\) Vaughn]

What's the purpose of the TableAdapter if you don't want a table to manage?
Are you simply looking for a way to invoke SPs more easily?

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

Thanks,
Does anyone have any ideas on question #2 from my original post?

Big Daddy,

Be aware that a where clause is nice, but as long as a row does not
change,
the dataadapter (which the tableadapter inherits from) does never give a
row
to the database to update.

To add to the designer created class you can add a partial clase where
you
can put all your delete transact code in. You are than even able to
recreate
the designer generated part.

I hope this gives some ideas,

Cor

1. I have a table in the database (SQL Server) that I created an
adapter for using Visual Studio 2005. It has
select/insert/delete/update methods created, but not using stored
procedures, because I check the option that says:

Create methods to send updates directly to the database
(GenerateDBDirectMethods)

The INSERT sql that is created by the wizard is:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId);

But I want the INSERT sql to be:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId); select * from
Patients WHERE (PatientNo = @@IDENTITY)

Because there's an identity autonumber in the table that I want to read
back into my DataTable after in the insert. So I manually added the
text

select * from Patients WHERE (PatientNo = @@IDENTITY)

To the insert statement. Is there any way to make the wizard
automatically add this to the INSERT statement so I don't have to do it
manually?


2. I want to have a TableAdapter that encapsulates a bunch of stored
procedures, but that doesn't have a DataTable that goes with it. For
example, I might have 10 delete sprocs that I want to put into one
TableAdapter. When I create a new TableAdapter, I select the radio
button "use existing stored procedures". It forces me to have a SELECT
sproc, even though none of the sprocs I want to put in the adapter is a
SELECT. So if I choose a SELECT sproc first, it creates a DataAdapter
and DataTable for me. Then I can add all of my DELETE sprocs that I
want to. But I don't want the DataTable or the SELECT query at all. I
just want to put in a bunch of DELETE sprocs. Is there any way to do
what I want?

Thanks in advance,
John

 

[Big Daddy]

Yes, exactly. I want it to create an easy C# interface to invoke SPs.
So if I have a SP called DeleteOrder, it will create a C# function for
me called DeleteOrder().
thanks,
John

What's the purpose of the TableAdapter if you don't want a table to manage?
Are you simply looking for a way to invoke SPs more easily?

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

Thanks,
Does anyone have any ideas on question #2 from my original post?

Big Daddy,

Be aware that a where clause is nice, but as long as a row does not
change,
the dataadapter (which the tableadapter inherits from) does never give a
row
to the database to update.

To add to the designer created class you can add a partial clase where
you
can put all your delete transact code in. You are than even able to
recreate
the designer generated part.

I hope this gives some ideas,

Cor


"Big Daddy" <[email protected]> schreef in bericht
1. I have a table in the database (SQL Server) that I created an
adapter for using Visual Studio 2005. It has
select/insert/delete/update methods created, but not using stored
procedures, because I check the option that says:

Create methods to send updates directly to the database
(GenerateDBDirectMethods)

The INSERT sql that is created by the wizard is:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId);

But I want the INSERT sql to be:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId); select * from
Patients WHERE (PatientNo = @@IDENTITY)

Because there's an identity autonumber in the table that I want to read
back into my DataTable after in the insert. So I manually added the
text

select * from Patients WHERE (PatientNo = @@IDENTITY)

To the insert statement. Is there any way to make the wizard
automatically add this to the INSERT statement so I don't have to do it
manually?


2. I want to have a TableAdapter that encapsulates a bunch of stored
procedures, but that doesn't have a DataTable that goes with it. For
example, I might have 10 delete sprocs that I want to put into one
TableAdapter. When I create a new TableAdapter, I select the radio
button "use existing stored procedures". It forces me to have a SELECT
sproc, even though none of the sprocs I want to put in the adapter is a
SELECT. So if I choose a SELECT sproc first, it creates a DataAdapter
and DataTable for me. Then I can add all of my DELETE sprocs that I
want to. But I don't want the DataTable or the SELECT query at all. I
just want to put in a bunch of DELETE sprocs. Is there any way to do
what I want?

Thanks in advance,
John

 

[William \(Bill\) Vaughn]

Consider that the VS team is pretty "table oriented" and has take OUT
functionality to make invoking SPs easier. I would look around for a 3rd
party app to do this. I checked with RedGate but they did not support this.
I also passed a note to the dev team asking if they knew of a tool to build
this code. I expect that the problem with third-party tools might have
something to do with the difficulty in adding functionality to VS2005. I'll
keep looking.

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

Yes, exactly. I want it to create an easy C# interface to invoke SPs.
So if I have a SP called DeleteOrder, it will create a C# function for
me called DeleteOrder().
thanks,
John

What's the purpose of the TableAdapter if you don't want a table to
manage?
Are you simply looking for a way to invoke SPs more easily?

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

Thanks,
Does anyone have any ideas on question #2 from my original post?


Cor Ligthert [MVP] wrote:
Big Daddy,

Be aware that a where clause is nice, but as long as a row does not
change,
the dataadapter (which the tableadapter inherits from) does never give
a
row
to the database to update.

To add to the designer created class you can add a partial clase where
you
can put all your delete transact code in. You are than even able to
recreate
the designer generated part.

I hope this gives some ideas,

Cor


"Big Daddy" <[email protected]> schreef in bericht
1. I have a table in the database (SQL Server) that I created an
adapter for using Visual Studio 2005. It has
select/insert/delete/update methods created, but not using stored
procedures, because I check the option that says:

Create methods to send updates directly to the database
(GenerateDBDirectMethods)

The INSERT sql that is created by the wizard is:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId);

But I want the INSERT sql to be:

INSERT INTO [Patients] ([PatientId]) VALUES (@PatientId); select *
from
Patients WHERE (PatientNo = @@IDENTITY)

Because there's an identity autonumber in the table that I want to
read
back into my DataTable after in the insert. So I manually added the
text

select * from Patients WHERE (PatientNo = @@IDENTITY)

To the insert statement. Is there any way to make the wizard
automatically add this to the INSERT statement so I don't have to do
it
manually?


2. I want to have a TableAdapter that encapsulates a bunch of stored
procedures, but that doesn't have a DataTable that goes with it.
For
example, I might have 10 delete sprocs that I want to put into one
TableAdapter. When I create a new TableAdapter, I select the radio
button "use existing stored procedures". It forces me to have a
SELECT
sproc, even though none of the sprocs I want to put in the adapter
is a
SELECT. So if I choose a SELECT sproc first, it creates a
DataAdapter
and DataTable for me. Then I can add all of my DELETE sprocs that I
want to. But I don't want the DataTable or the SELECT query at all.
I
just want to put in a bunch of DELETE sprocs. Is there any way to
do
what I want?

Thanks in advance,
John