Web-Service: How to authenticate users with the SQL Server database table using VB
Can somebody tell me how to authenticate users with the SQL Server database table using VB as a web service. I managed to connect to the SQL server via this VB code;
Public Sub ConnectToSQL() Dim con As New SqlConnection Dim cmd As New SqlCommand Try con.ConnectionString = "Data Source=(local);Initial Catalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=421" con.Open() Catch ex As Exception MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally con.Close() 'Whether there is error or not. Close the connection. End Try End Sub
Any one ?Thank you in advance.
I would recommend that you first go through basic tutorials on SQL server as well as ADO.Net and then post questions. This will help in making your concepts clear as well as any help can be provided in more focused, problem areas. BTW, no hard feelings, just want you to be comfortable on programming. :)
Step 1 :
Create relevant database tables. These may include a users table and other tables such as roles, etc... EDIT : You can use SQL scripts to create tables in DB. Please find a sample below.
-- Sample Table Creation and Index Creation script for MCIS-4423 -- This script is designed to be "re-runnable", but you need to be careful, -- Since this will DROP the table, which would be bad if it was an existing table with data -- Make sure you are in the correct database USE [AdventureWorks] GO -- Drop Check Constraint IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Team_TeamID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Team]')) ALTER TABLE [dbo].[Team] DROP CONSTRAINT [CK_Team_TeamID] GO -- Drop Table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND type in (N'U')) DROP TABLE [dbo].[Team] GO -- Create Table CREATE TABLE [dbo].[Team]( [TeamID] [char](3) NOT NULL, [TeamName] [varchar](20) NOT NULL, [City] [varchar](50) NOT NULL, [StateCode] [char](2) NULL, [PostalCode] [char](5) NULL, CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED ( [TeamID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- Add Check Constraint ALTER TABLE [dbo].[Team] WITH CHECK ADD CONSTRAINT [CK_Team_TeamID] CHECK (([TeamID] like '[A-Z][A-Z][A-Z]')) GO ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [CK_Team_TeamID] GO -- Drop index if it exists IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND name = N'IX_Team_TeamName') DROP INDEX [IX_Team_TeamName] ON [dbo].[Team] WITH ( ONLINE = OFF ) GO -- Add non-clustered index on StateCode column -- Use ONLINE = ON if you have Developer or Enterprise Edition -- Use MAXDOP = 2 (set to roughly 25% of the number of CPU cores to keep index creation from affecting performance) CREATE NONCLUSTERED INDEX [IX_Team_TeamName] ON [dbo].[Team] ( [StateCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, MAXDOP = 2, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Please note that this sample is copied from http://sqlserverperformance.wordpress.com/2007/09/27/sample-table-and-index-creation-script-for-sql-server-2005/ and all credit related to its content goes to the author who wrote it.
Step 2 :
Write SQL query to find a user based on his username and password. You may go for password encryption, but that is out of scope for current question. EDIT : Sample query can be as follows :
SELECT username FROM users WHERE username=@username and password=@password
You can read through SqlCommand documentation here to understand how to add parameters to a query.
Step 3 :
Add WebMethods so that you can create users records in database. Use SqlCommand to fire INSERT queries on your database tables. EDIT : Read here for a sample on how to write web services in VB.Net. And yes, you already know now, how to use SqlCommand to fire SQL queries, do the same for INSERT queries too.
Step 4 :
Create a WebMethod, that validates a user. For this, use SqlCommand object to fire your query written in Step 2. If you get a row as a result, the user is valid. EDIT : Refer instructions to above steps and you should be able to create this on your own.
Hope I am clear enough.