By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Call Stored Procedure via ASP.Net (VB)

P: n/a
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.
Nov 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)


"Sam" wrote:
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.

Nov 19 '05 #2

P: n/a
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

"HARI PRASD BARU" <HA***********@discussions.microsoft.com> wrote in message
news:D0**********************************@microsof t.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)


"Sam" wrote:
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.

Nov 19 '05 #3

P: n/a


*** Sent via Developersdex http://www.developersdex.com ***
Nov 19 '05 #4

P: n/a
And if using DataSet DataAdpater u can do this:-

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

*** Sent via Developersdex http://www.developersdex.com ***
Nov 19 '05 #5

P: n/a
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!
***************************


"Sam" wrote:
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.

Nov 19 '05 #6

P: n/a
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
"Sam" wrote:
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.

Nov 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.