ASP.NET Stored Procedures

[kwilliams]

I'm pretty much a newbie when it comes to stored procedures and ASP.NET, but I'm wanting to convert all of my recordsets to stored procedures throughout my site. I'm running into trouble with what should be a simple stored procedure.

This is what I classically would have done using a recordset:
<%
Response.Buffer = True
'Declare form variables
Dim strUsername As Object = trim(Request.Form("txtUsername"))
Dim strPassword As Object = trim(Request.Form("txtPassword"))

'Recordset
Dim objConn As Object, rsAccounts As Object
objConn = Server.CreateObject("ADODB.Connection")
objConn.Open (strConnAccounts)
rsAccounts = "SELECT * FROM tblAccounts WHERE Username = '" & strUsername & "' AND Password = '" & strPassword & "'"
rsAccounts = objConn.Execute(rsAccounts)
objConn.Close
rsAccounts = nothing
objConn = nothing

'Test output
Response.Write("FName: " & rsAccounts("FName") & "<br />")
Response.Write("LName: " & rsAccounts("LName") & "<br />")
%>

How can I accomplish this with using a Stored Procedure? I don't understand how I can use variables within an ASP.NET page within a Stored Procedure's WHERE clause. Thanks for any help.

[takkie]

Dont hold me on syntax errors...

but something lke this..

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader

myConnection = New SqlConnection(myConnectionStr)
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "myStoreProc"
myCommand.CommandType = CommandType.StoredProcedure

myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@myFirstParam"
myParameter.SqlDbType = SqlDbType.Int
myCommand.Parameters.Add(myParameter)

myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@mySecondParam"
myParameter.SqlDbType = SqlDbType.Money
myCommand.Parameters.Add(myParameter)

myConnection.Open()
myCommand.ExecuteNonQuery()

tak

[sirpelidor]

In addition to what Tak shows you, this is what you wanted to do from your SqlServer side:

1) open enterprise manager
2) select and expand the database tree
3) right click "stored procedure" and choose "New stored procedure..."
3) give a name "myStoreProc" (follow by Tak's example)
4) put your code in ur stored procedure...


CREATE PROCEDURE dbo.myStoreProc
@userName nvarchar(50),
@password nvarchar(50)

as
Begin

SELECT * FROM tblAccounts WHERE Username = @userName and Password = @password

GO


-dbo stands for DataBaseOwner, you may use something else depends on how you setup your permission
-nvarchar(50) is quite large for normal username and password (who uses 50 chars for username!?) you may wanna review the data type first.

hope that help

[kwilliams]

Thanks for all of the great feedback. Between my original post and tak's post, I came up with this solution:

Stored Procedure
CREATE PROCEDURE [dbo].[spAccounts]
@Username nvarchar (50), @Password nvarchar (50)
AS
SELECT * FROM Accounts_test
WHERE Username = @Username AND Password = @Password
GO

ASP.NET Page
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" aspcompat="true" Debug="true" %>
<%
Response.Buffer = True
'Declare variables
strUsername_form As Object, strPassword_form As Object

'Declare form variables
strUsername_form = Server.HTMLEncode(lcase(Left(Trim(Request.Form("txtUsername")), 14)))
strPassword_form = Server.HTMLEncode(lcase(Left(Trim(Request.Form("txtPassword")), 14)))

'------------------Database Query Begins-----------------------
Dim objConn As Object, sqlAccounts As Object, rsAccounts As Object
objConn = Server.CreateObject("ADODB.Connection")
objConn.Open (strConnAccounts)
sqlAccounts = "spAccounts @Username = '" & strUsername_form & "', @Password = '" & strPassword_form & "'"
rsAccounts = objConn.Execute(sqlAccounts)

'Set session var's for this user
Session("GroupID") = rsAccounts("GroupID").value
Session("FullName") = rsAccounts("FullName").value
Session("LicApp") = rsAccounts("LicApp").value
Session("FullCourt") = rsAccounts("FullCourt").value

objConn.Close
objConn = nothing
'------------------Database Query Ends-----------------------

'Redirect to display page
Response.Redirect("http://www.MYSITE.com/index.aspx")
%>

So all of the parameter assignment is done within the stored procedure declaration. Obviously there's a big difference in how this is implemented, but is there any difference in performance between this version and tak's version? I'd appreciate any constructive criticism. Thanks.

[sirpelidor]

So all of the parameter assignment is done within the stored procedure declaration. Obviously there's a big difference in how this is implemented, but is there any difference in performance between this version and tak's version? I'd appreciate any constructive criticism. Thanks.


the major diff of using Tak's version is,
1)you will be protected yourself from SQL injection attack
2)you can define data Type at sqlcommand object level, which avoid datatype confusion (e.g: '1' as char is not = 1 as integer)
3)It is a OOP programming style vs. procedure (scripting) programming style.

I don't know much about brenchmark, you'll need to check that with someone else.

[takkie]

kwilliams,

You have,

sqlAccounts = "spAccounts @Username = '" & strUsername_form & "', @Password = '" & strPassword_form & "'"

let say, i entered this into the username field and password field,

uname, i will put

myname' or 'a'='a

and for pw, i will put,

mypw' or 'a'='a

then your sqlAccounts variable will be

"spAccounts @Username = 'myname' or 'a'='a', @Password = 'mypw' or 'a'='a'"

I am not 100% if this will work, but sql injections goes with something like that...

So, becareful..

or better yet...

if you just have sql statements directly in your code... ppl can do this....

put whatever for uname

then put, mypw'; delete from users where 'a'='a

then your sqlAccounts will be,

"spAccounts @Username = 'whatever', @Password = 'mypw'; delete from users where 'a'='a'"

- tak