Update() and stored procedures

[Paul R]

Hi, I currently have a system using ADO.NET to update a SQLServer2000
database.

I use DataSets (not strongly typed) and set up an appropriate SQL
SELECT/UPDATE/INSERT/DELETE method for each.

I am now in the middle of moving all of my data access to stored procedures.
I have done most of the query type statements, but am unsure of how to
approach the inserts/updates.

Is it straightforward to use stored procedures with the automatic updating
mechanism of ADO.NET?

Also is it possible to have a stored procedure that takes more than one
record to insert/update? Or do stored procedures normally only insert or
update one record?

Thanks!

 

[Stephen Muecke]

Paul

For the following simple StoreProcedure

CREATE PROCEDURE dbo.InsertProject
(@ID int OUTPUT,
@Name varchar(100))
AS INSERT INTO dbo.Projects
([Name])
VALUES
(@Name)
SET @ID = @@Identity

In your code to insert the data (assumes it is a function which returns the
ID of the inserted data)

Dim newID as Integer
Dim cmd as New SQLCommand

With cmd
.Connection = <your SQLConnection here (perhaps passed to the function)>
.CommandText = "dbo.InsertProject" 'ie the name of your procedure
.CommandType = CommandType.StoredProcdure
'Add parameters
.Parameters.Add("@ID", SqlDbType.Int)
.Parameters.Add("@Name", SqlDbType.VarChar, 100)
'Set values
.Parameters("@ID").Direction = ParameterDirection.Output
'.Parameters("@Name").Value = <Your value to insert here (perhaps passed
to the function as a class)>
Try
.Connection.Open 'If its not already open
.ExecuteNonQuery
newID = .Parameters("@ID").Value
Catch ex as Exception
....
Finally
.Connection.Close
End Try
'Return the ID of the new row
Return newID
End With

Note that the Parameters.Add method of a SQLCommand has numerous
constructors you should look at

Stephen

 

[Paul R]

Thanks, very helpful!!

-----Original Message-----
Paul

For the following simple StoreProcedure

CREATE PROCEDURE dbo.InsertProject
(@ID int OUTPUT,
@Name varchar(100))
AS INSERT INTO dbo.Projects
([Name])
VALUES
(@Name)
SET @ID = @@Identity

In your code to insert the data (assumes it is a function which returns the
ID of the inserted data)

Dim newID as Integer
Dim cmd as New SQLCommand

With cmd
.Connection = <your SQLConnection here (perhaps passed to the function)>
.CommandText = "dbo.InsertProject" 'ie the name of your procedure
.CommandType = CommandType.StoredProcdure
'Add parameters
.Parameters.Add("@ID", SqlDbType.Int)
.Parameters.Add("@Name", SqlDbType.VarChar, 100)
'Set values
.Parameters("@ID").Direction = ParameterDirection.Output
'.Parameters("@Name").Value = <Your value to insert here (perhaps passed
to the function as a class)>
Try
.Connection.Open 'If its not already open
.ExecuteNonQuery
newID = .Parameters("@ID").Value
Catch ex as Exception
....
Finally
.Connection.Close
End Try
'Return the ID of the new row
Return newID
End With

Note that the Parameters.Add method of a SQLCommand has numerous
constructors you should look at

Stephen

Hi, I currently have a system using ADO.NET to update a SQLServer2000
database.

I use DataSets (not strongly typed) and set up an appropriate SQL
SELECT/UPDATE/INSERT/DELETE method for each.

I am now in the middle of moving all of my data access

to stored
procedures.

I have done most of the query type statements, but am unsure of how to
approach the inserts/updates.

Is it straightforward to use stored procedures with the automatic updating
mechanism of ADO.NET?

Also is it possible to have a stored procedure that takes more than one
record to insert/update? Or do stored procedures normally only insert or
update one record?

Thanks!

.