how to work with procedure and asp.net

[adugenet]

I am with three text boxes and a gridview to display the result. I created a procedure which I am passing three values and I am just wondering how I can work with procedure and pass these three variables from my asp.net page to the procedure.thanks

Here is my procedure.

PROCEDURE BiddersInformation
(p_letting in letprop.letting%type,
p_contid in proposal.contid%type,
p_call in letprop.call%type,
p_results out sys_refcursor
)
AS
BEGIN
OPEN p_results FOR

SELECT v.vendor, INITCAP (v.vnames) vnames,
TO_CHAR (d.calcbtot, '$99,999,999.99') AS bd,
TO_CHAR (SUM (q.qty * q.price), '$99,999,999.99') AS engest,
TO_CHAR (SUM (q.qty * q.price)) AS engest,
TO_CHAR ((d.calcbtot / l.contamt) * 100, '999.99') AS ovelow,
TO_CHAR (d.calcbtot / SUM (q.qty * q.price) * 100, '999.99') AS overest
FROM letprop l,
proposal p,
propitem q,
bidlet b,
bidtabs c,
itemlist i,
vendor v,
bidders d
WHERE (l.letting = b.letting)
AND b.letting = c.letting
AND p.cspecyr = i.ispecyr
AND q.prpitem = i.item
AND p.contid = l.lcontid
AND q.contid = p.contid
AND c.vendor = l.avendor
AND l.CALL = c.CALL
AND q.lineflag = c.lineflag
AND q.iplineno = c.iplineno
AND l.letting = d.letting
AND c.letting = l.letting
AND c.CALL = d.CALL
AND c.letting = d.letting
AND v.vendor = d.vendor
AND l.letstat = 'A'
AND l.letting = p_letting
AND p.contid = p_contid
AND l.CALL = p_call
GROUP BY v.vendor, v.vnames, d.calcbtot, (d.calcbtot / l.contamt)
ORDER BY bd;
END;

 

[jmeckley]

it appears this is a sql statement. i'm not fimilar with the db this syntax relates to. this part has be stumped:

PROCEDURE  BiddersInformation
(p_letting in letprop.letting%type,
 p_contid in proposal.contid%type,
 p_call in letprop.call%type,
 p_results out sys_refcursor
 )
 AS
BEGIN
      OPEN p_results FOR

to access a stored proc you'll need a database connection and database command object. you will also need an object to return your results (DataTable, DataSet, Object Collection).
I believe .net has objects specific to oracle, if this is an oracle proc. if not you can use the oledb objects. In simple terms you would:[ol]
[li]create instance of connection[/li]
[li]create instance of command[/li]
[li]pass connection to command[/li]
[li]set command type to stored proc[/li]
[li]define parameter objects for each of the procs parameters[/li]
[li]open connection and execute sql[/li]
[li]pass results from command to return object.[/li]
[li]close connection, dispose of connection and command[/li]
[li]return results[/li]
[/ol]
there are many products/projects that automate these tasks. MS Enterprise Library 1, 2 or 3 have a database access layer. there are also companies that specialize this this area if you have a budget to purchase 3rd party developement tools.

A google search for ".net OR mapper" will return quite a few results.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[adugenet]

thank you for taking time to help me out.After so many hours of hard work I figured out how to asp.net with procedure.below is the code i used to connect to my procedure from the asp.net page. Just copied over here if someone is interested ....thank you all for the help



Imports System.Data
Imports System.Data.OracleClient
Imports System.Configuration.ConfigurationManager
Partial Class test_spBiddersInfo
Inherits System.Web.UI.Page


Protected Sub btnBidders_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBidders.Click
Dim drBindLetData As OracleDataReader
Dim connectionString As String = ConnectionStrings("costEstimating").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()

Dim cmdLetting As OracleCommand = New OracleCommand()
cmdLetting.Connection = oOracleConn
cmdLetting.CommandText = "LettingInformation.BiddersInformation"
cmdLetting.CommandType = CommandType.StoredProcedure
cmdLetting.Parameters.Add(New OracleParameter("p_letting", OracleType.VarChar)).Value = lettingId.Text
cmdLetting.Parameters.Add(New OracleParameter("p_contid", OracleType.VarChar)).Value = ContId.Text
cmdLetting.Parameters.Add(New OracleParameter("p_call", OracleType.VarChar)).Value = CallNum.Text
cmdLetting.Parameters.Add(New OracleParameter("p_result", OracleType.Cursor)).Direction = ParameterDirection.Output

drBindLetData = cmdLetting.ExecuteReader()
gvlet.DataSource = drBindLetData
gvlet.DataBind()
drBindLetData.Close()
End Sub
End Class