www.webdeveloper.com
Results 1 to 6 of 6

Thread: ASP.NET Stored Procedures

  1. #1
    Join Date
    May 2003
    Posts
    599

    Question ASP.NET Stored Procedures

    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:
    Code:
    <%
    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.

  2. #2
    Join Date
    Oct 2005
    Location
    NYC
    Posts
    108
    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

  3. #3
    Join Date
    Apr 2005
    Posts
    634
    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...

    Code:
    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

  4. #4
    Join Date
    May 2003
    Posts
    599
    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
    Code:
    <%@ 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.

  5. #5
    Join Date
    Apr 2005
    Posts
    634
    Quote Originally Posted by kwilliams
    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.

  6. #6
    Join Date
    Oct 2005
    Location
    NYC
    Posts
    108
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles