I have two questions about the TableAdapter Configuration Wizard

  • Thread starter Thread starter Big Daddy
  • Start date Start date
B

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
 
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
 
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 said:
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
Click to expand...
Click to expand...
 
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)
-----------------------------------------------------------------------------------------------------------------------

Big Daddy said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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)
-----------------------------------------------------------------------------------------------------------------------

Big Daddy said:
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
Click to expand...
Click to expand...
Click to expand...
 
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)
-----------------------------------------------------------------------------------------------------------------------

Big Daddy said:
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)
-----------------------------------------------------------------------------------------------------------------------

Big Daddy said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top