Update() and stored procedures

  • Thread starter Thread starter Paul R
  • Start date Start date
P

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!
 
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
 
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


Paul R said:
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
Click to expand...
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!
Click to expand...


.
Click to expand...
 
Back
Top