Call Stored Procedure via ASP.Net (VB)

[Sam]

I had created stored procedure at SQL Server 2000 and how do I call it via
ASP.Net using VB Language?

CREATE PROCEDURE STK As

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[STOCK_RESULT]
GO

CREATE TABLE [dbo].[STOCK_RESULT] (
[StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [decimal](18, 4) NULL ,
[weight] [decimal](18, 4) NULL ,
) ON [PRIMARY]
GO


INSERT INTO STOCK_RESULT (StockNum,qty,weight)
select StockNum,sum(qty) as qty,sum(weight) as weight
from INVENTORYLEDGER
where Julian_Date <= 101001
group by StockNum
order by StockNum
go

Will stored procedure faster than SQL statement via ADO.Net?

Please advise.

Many thanks.

 

[Guest]

Hi Sam

Writing SQL stored procedure is faster than writing SQl statements in
ADO.NET since stored procedure will be compiled and optimized executed plan
will be create in sqlserver for sp(stored proc) and more over maintenance
will be less comparing to writing sql in ado.net
to call sp from ado.net u need to created sql command object with command
type as stored procedure
sqlcommand sqlcmd = new sqlcommand("sp_name",slqconnection)

 

[Dave Fancher]

You'll also want to specify the CommandType of the SqlCommand as
CommandType.StoredProcedure.

sqlcmd.CommandType = CommandType.StoredProcedure

HTH
----------------
Dave Fancher
http://davefancher.blogspot.com

Hi Sam

Writing SQL stored procedure is faster than writing SQl statements in
ADO.NET since stored procedure will be compiled and optimized executed
plan
will be create in sqlserver for sp(stored proc) and more over maintenance
will be less comparing to writing sql in ado.net
to call sp from ado.net u need to created sql command object with command
type as stored procedure
sqlcommand sqlcmd = new sqlcommand("sp_name",slqconnection)

I had created stored procedure at SQL Server 2000 and how do I call it
via
ASP.Net using VB Language?

CREATE PROCEDURE STK As

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[STOCK_RESULT]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[STOCK_RESULT]
GO

CREATE TABLE [dbo].[STOCK_RESULT] (
[StockNum] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [decimal](18, 4) NULL ,
[weight] [decimal](18, 4) NULL ,
) ON [PRIMARY]
GO


INSERT INTO STOCK_RESULT (StockNum,qty,weight)
select StockNum,sum(qty) as qty,sum(weight) as weight
from INVENTORYLEDGER
where Julian_Date <= 101001
group by StockNum
order by StockNum
go

Will stored procedure faster than SQL statement via ADO.Net?

Please advise.

Many thanks.

 

[Patrick Olurotimi Ige]

And if using DataSet DataAdpater u can do this:-

Dim da As SqlDataAdapter = New SqlDataAdapter("Stored_Procedure",
connection)

 

[Guest]

Yes, sprocs are faster than ad hoc code. I have a problem with your sproc,
however, as I see no reason to drop and create a table on a regular basis. It
is preferable to delete the records or even truncate the table to dropping
and creating objects. You are actually incurring a bit of a penalty with the
drop, as the sproc cannot be fully optimized, as the object it is calling
cannot be compiled into the code.

I would also consider setting up a DTS job on SQL Server to empty and
populate the table on a regular basis, rather than using your ASP.NET app to
do it. I see no reason to write a lot of code for something that can be done
declarative and automated.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

Sam -

The VB language would go something like this:

Try
Dim cnn as New SqlClient.SqlConnection("Your connection info in quotes")
Dim cmd as New SqlCommand("STK", cnn)
cmd.CommandType = CommandType.StoredProcedure

cnn.Open
cmd.ExecuteNonQuery

Catch ex as Exception

Label1.Text = "There was an error. " & ex.Source & " " & ex.Message

Finally

cnn.Close
cnn.Dispose

End Try

Use of stored procedures is also strongly advised for security reasons.

Hope this helps!

Sandy