Stored Procedures, DAL & Returning Values

[scparker]

Hello,

We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@IDENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert(Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?
Sincerely,

SP

[Hans Kesting]

Hello,

>
We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@IDENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert(Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?

Sincerely,

SP

What is that Adapter you use?

To call a stored procedure and use the returned parameters, I use
something like:
- create a SqlCommand object
- set the CommandText property to the name of the stored procedure to
call and the CommandType to CommandType.StoredProcedure)
- add parameters. Two in your case: @Company is an input param,
@SuppliedId is output (or in/out).
- set the Connection property
- open the connection
- Execute the command (ExecuteNonQuery method on the command in your
case)
- close the connection (you might want to put the execute in a
"try"-block and the connection-close in the "finally")
- read the value from the @SupplierId parameter.

this works in 1.1 and I don't expect that to be changed (much) in 2.0

Hans Kesting

[Milosz Skalecki]

I created an example that should help. Note everything is coded in the code
behing, but logic should be separated from the presentation.

-- begin vb.net suxx code --

Partial Class _Default
Inherits System.Web.UI.Page

Private Const ConnectionString As String =
"server=serverName;uid=UserName;password=Password; database=DatebaseName"

Public Class Supplier

Private _id As Integer
Private _company As String

Public Sub New()
End Sub

Public Property Id() As Integer
Get
Return _id
End Get
Set(ByVal value As Integer)
If (value < 0) Then
Throw New ArgumentOutOfRangeException()
End If
_id = value
End Set
End Property

Public Property Company() As String
Get
Return _company
End Get
Set(ByVal value As String)
_company = value
End Set
End Property

End Class

Private Sub InsertSupplier(ByVal supplier As Supplier)

Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("InsertSupplier", connection)

command.Parameters.Add("@Company", SqlDbType.NVarChar).Value =
supplier.Company
command.Parameters.Add("@SupplierId", SqlDbType.Int).Direction =
ParameterDirection.Output
command.CommandType = CommandType.StoredProcedure

Try

connection.Open()
command.ExecuteNonQuery()

Catch ex As Exception
Throw ex
Finally
connection.Close()
End Try

supplier.Id = CType(command.Parameters("@SupplierId").Value, Integer)

End Sub

Protected Sub InsertBUtton_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles InsertBUtton.Click

Dim supplier As New Supplier

supplier.Company = txtCompany.Text

InsertSupplier(supplier)
Response.Write("inserted. id = " & supplier.Id.ToString())

End Sub
End Class
-- end code --

-- begin T-SQL code --

CREATE PROCEDURE [dbo].[InsertSupplier]
@Company nvarchar(1000),
@SupplierId int OUTPUT
AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId = SCOPE_IDENTITY()
END
GO

-- end code

--
Milosz Skalecki
MCAD
"scparker" wrote:

Hello,

We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier Architecture.

The Stored Procedures are used through TableAdaptors, which in turn are
used by Class Files.

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

An Example Stored Procedure:
--------------------------------------------
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SET @SupplierId=@@IDENTITY
END
GO
--------------------------------------------

An Example Portion of The Class (VB.NET 2.0):

--------------------------------------------
'*** Create New Supplier
Public Function CreateSupplier( _
ByVal Company As String, _
ByVal SupplierId As Integer) As Boolean

'*** Declare Variables
Dim Result As Boolean

'*** Insert Supplier
Result = Adapter.Insert(Company, SupplierId)

'*** Publish Result
Return Result

End Function
--------------------------------------------

As you can see from the class, this deals wwith actually enterting the
values into the stored procedure, which will then add it into the table
itself. How would I be able to represent the returning value in this
portion of the class?

I would like to be able to do this without having to do another call to
the database. I come from ASP 3.0 (Classic) background and am used to
using Stored Procedures with Command objects that will allow the
returning of values. I have as yet, been unable to simulate such
operations using ASP.NET 2.0 and am looking for help with this
situation.

Anyone have any ideas, solutions?
Sincerely,

SP

[Olaf Rabbachin]

Hi,

scparker wrote:

I wish to be able to return this new ID value using the Stored
Procedure, TableAdaptor and for it to be available in the Class file.
At present we are unable to get this working. Does anybody have
experience of getting this method of operation working at all?

FWIW - as an alternative to the regular approaches mentioned in this
thread, you could simply return your new ID within the resultset if your SP
has to return that new ID only, as in:

--- 8< ---
CREATE PROCEDURE [dbo].[InsertSupplier]
(
@Company nvarchar(1000),
@SupplierId int OUTPUT
)

AS
BEGIN
/* Insert Supplier */
INSERT INTO Supplier (Company)
VALUES (@Company)

/* Retrieve Supplier Id */
SELECT @@IDENTITY
END
GO
--- 8< ---

Retrieving your new ID could then be handled using i.e.
intNewID=ctype(cmdSomeCommand.ExecuteScalar,intege r)

Cheers,
Olaf
--
My .02: www.Resources.IntuiDev.com

[scparker]

Hello,

Thank you for your responses. It appears you all do follow the same
line of thinking.

To answer Hans Kesting;
The Adapter is an object created using Visual Studio 2005. It creates
the Data Access Layer (DAL). What it effectively does is bind the four
operations of Select, Insert, Update & Delete into One Object. This
results in the following code:

Adapter.Insert(Company,SupplierId)
or
Adapter.Select(SupplierId)
etc
So, you create a stored procedure that is the definitive Select
statement you are going to use to communicate with that particular
table. Using VS2005 you use the graphical interface to select a
particular stored procedure to assume the Select command. The same goes
for Insert, Update and Delete. The name of these stored procedures
could be anything
(say: CREATE PROCEDURE WeWantToSelectASupplier(@SupplierID as INT) AS
....)
and when you select that to be the defined Select Bind, instead of
calling Adpater.WeWantToSelectASupplier - you call Adapter.Select. I do
not know if 1.1 had this functionality as I have come from Classic ASP
to ASP.NET 2.0 - so I hope this makes sense to you.

To answer Milosz Skalecki;
This really continues with my answer to Hans Kesting. Because of this
use of the TableAdaptor, it appears we can negate the need for the use
of Command and Connection objects. We have learnt that what we are
looking to do is to return a scalar value from a dataset. The following
links may help to elaborate the issue we are having:
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
The above details very similar to the example you have provided and
then goes on to discuss the return value being used in the stored
procedure. It is this value we wish to extract using the TableAdaptor
objects.

http://blogs.gotdotnet.com/youngjoo/...25/678320.aspx
The link above continues the discussion by showing how to get the
return value by using the scalar extractor to bring the actual value
through. It is however, hard to follow and this is where we have
reached a stumbling block. Are you able to elaborate on what is going
on here, in the context of what we have been talking about?

To answer Olaf Rabbachin;
Thank you for the contribution with regard to the Stored Procedure
change. It makes little difference whether you use @@IDENTITY or
SCOPE_IDENTITY() to retrieve the value required. However, I will be
using the SCOPE_IDENTITY() in future, so thank you for your assistance
with this.

If any of you are able to help with the understanding of using a Scalar
value with TableAdaptors, you will be of great assistance.

Sincerely,

SP