In this final installment of the series, you will learn how to authenticate users using the credentials from a database. I will use the database named “Wintergreen,” which we created in the first part of this series. Towards the end of this article, you will also learn how to apply Web Services for performing database operations. Typically, a site validates its users with their username and password. Some sites require you to feed in an e-mail address, which you gave at the time of registration. In this article, I’ll examine how to authenticate a user with the username and password. Here, you will have to write a separate stored procedure (see Listing 1). The procedure returns the respective values and notifies an ASP.NET application, based on which the appropriate action takes place.
Listing 1
create procedure DBVerify2 ( @username varchar(25), @password varchar(25) ) as declare @ID INT declare @actualpassword varchar(25) select @ID = identitycol, @actualpassword = @password from Employees where username = @username if @ID IS NOT NULL if @password = @actualpassword return @ID else return -2 else return -1
The user-interface for our project looks like what is shown in Figure 1.
Figure 1
The whole process is tied up in a function named Userverify (see Listing 2). This function accepts two arguments, namely username and password objects. The objects are typically derived from TextBox controls. As usual, the connection string is established and a stored procedure (DBVerify2) called. The add method accepts the username and password objects as parameters. Finally, if the return value is less than 0 or -1, it’s an indication that the user has not been registered. The user is automatically shown the respective error message with the help of the Label control. Otherwise, the password inputted by the user is invalid and the appropriate error message is displayed as output. The whole code for this function is shown below:
Listing 2
function Userverify(strusername as string, strpassword as string) _
as integer
dim sconn as sqlconnection
dim scmd as sqlcommand
dim sparam as sqlparameter
dim intresult as integer
sconn = new sqlconnection _
("server=localhost;uid=sa;pwd=;database=wintergreen")
' DBVerify2 is the stored procedure for checking usernames
' and passwords
scmd = new sqlcommand("DBVerify2",sconn)
scmd.commandtype = commandtype.storedprocedure
sparam = scmd.parameters.add("RETURN_VALUE",sqldbtype.int)
sparam.direction = parameterdirection.returnvalue
scmd.parameters.add("@username",strusername)
scmd.parameters.add("@password",strpassword)
sconn.open()
scmd.executenonquery()
intresult = scmd.parameters("RETURN_VALUE").value
sconn.close()
if intresult < 0 then
if intresult = -1 then
lblMessage.text = "Username not registered"
else
lblMessage.text = "Invalid Password"
end if
end if
return intresult
end function
As specified above, our user-interface includes a button control. The validation and the above function are called only upon clicking this button. So, you have to dress up the button click event with some code (see Listing 3). Here, we have to verify a fact that if the function returns a value of greater than 0, the server redirects to some other Web page. It could be a page meant only for members of the site. Any action can occur depending upon the circumstances. In our example, the server displays a text message denoting that the information you entered is correct.
Listing 3
Sub btnLogin_Click(sender As Object, e As EventArgs)
if isvalid then
if Userverify(txtUsername.text, txtPassword.text) > 0 then
lblMessage.text = "Information is Correct"
end if
end if
End Sub
In this part and from the previous two parts, you had learned a lot about performing database manipulations using ASP.NET. With a little effort, you can easily implement these functionalities in your own project.
Using Web Services for Database Operations
Instead of creating an ASP.NET page, you can put the code for manipulating the database on a Web Service so that you can call them from anywhere using HTTP. Look at the typical Web Service code in Listing 4 below:
Listing 4
<%@ webservice class = "Data_Aware3" %>
imports system
imports system.web.services
imports system.data
imports system.data.sqlclient
<webservice> public class Data_Aware3 : inherits webservice
<webmethod()> public function Getproducts() as dataset
dim cn as sqlconnection = new sqlconnection _
("server=localhost;uid=sa;pwd=;database = northwind")
dim adapter as sqldataadapter = new _
sqldataadapter("select * from products",cn)
dim mydataset as dataset = new dataset
adapter.fill(mydataset, "products")
return mydataset
end function
end class
Save the above file as Data_Aware3.asmx in the Inetpub/wwwroot directory. All .NET Web Services are saved with an .asmx extension. The above listing pulls all the records from the products table on the Northwind database. This database is automatically installed during the SQL Server setup. If you desire, you can substitute the database name with any other existing one. As you may notice from the above code listing, you have to include a new namespace called system.web.services and provide the necessary class and function declaration on the <webservice> and <webmethod> attributes. The client can access only those methods that are marked with the <webmethod> attribute. You now can view the output of this file with Internet Explorer by entering the following URL:
http://localhost/Data_Aware3.asmx
The first page will display the function name and, upon clicking it, a new page will be displayed with an Invoke button. If you click the button, the output will be displayed in raw XML format.
The next step is to create a proxy file using the wsdl utility provided with the .NET Framework; the resulting file should be compiled with either the VB.NET or C# command line option. The proxy file can be created using the following syntax from the Visual Studio .NET Command Prompt:
wsdl /l:vb http://localhost/Data_Aware3.asmx
Upon successful completion, a file named Data_Aware3.vb will be created. The next step is to compile this proxy class using the Visual Basic compiler, vbc, as shown in the listing given below:
vbc /t:library /r:system.dll,system.web.services.dll,system.xml.dll,system.data.dll Data_Aware3.vb
The end result will be a .DLL file (Data_Aware3.DLL); it can be called in an ASP.NET program by creating an instance as shown in the listing given below:
sub page_load Dim dw3 as Data_Aware3 dw3 = new Data_Aware3() dgrid.datasource = dw3.Getproducts() dgrid.databind() end sub
The whole code for the above listing is included in the zip file along with this article. The above example is illustrated purely by using the commands that are to be given on the command prompt. But Visual Studio .NET provides an easy way for developing and deploying Web Services. You will learn more about this in one of my upcoming articles, titled “Developing Web Services with Visual Studio .NET 2003.”
Demo
A live demo can be seen at:
http://learnxpress.europe.webmatrixhosting.net/database_part3/Verify_user.aspx
Free ASP.NET Hosting Providers
http://europe.webmatrixhosting.net—Provides hosting on Windows Server 2003 and provides 20MB for files, 10MB in SQL Server database, FTP access, and much more
http://www.brinkster.com—Provides 30 MB disk space with 500 MB data transfer
http://www.dotnetplayground.com—Provides 30 MB disk space, MS Access database support, unlimited FTP, DSN, WAP, and much more
Code Download
About the Author
Anand Narayanaswamy, a Microsoft Most Valuable Professional, works as a freelance Web/Software developer and technical writer. He runs and maintains learnxpress.com, and provides technical support to users. His areas of interest include Web development using ASP, ASP.NET, software development using Visual Basic, and in the design and preparation of courseware, technical articles, help files, and tutorials. He can be reached at anandnswamy@rediffmail.com.
######
