How to check if the values are present in the database (ASP.NET C#)

Codemaniac · July 13th, 2012, 03:11 AM

Hi Everybody,

I have an application developed in ASP.NET in C#, I want to ask how could I check if the values are present in the database (username and password). The username and passwords will come from a different website (lets say in text!) and would display an appropriate message to the user, either login successful or denied.
Lets assume its a shared a database.

Much appreciate guys!! Thanks

guenfire · July 13th, 2012, 10:13 AM

Hi Codemaniac. Are you asking how to write the code to query the database or how to pass the user name and password from one site to another? Again, this is not an easy question to answer without knowing a bit more about how things are set up on the current site.

As an aside, there are many people happy to help you on the forums here but you should keep your questions as focused as possible, so we can answer briefly. There is plenty of information on the broader topics you're asking about within the Wrox books, as well as via a Google search. And I would strongly encourage you to consult MSDN Library, as well.

However, I've added a code snippet that should point you in the right direction as far as querying a database for credentials:

Code:

string userName = txtUserName.Text;
string password = txtPassword.Text;
int isPresent = 0;

private bool AuthenticateUser(string userName, string password)
{
   using (SqlConnection cn = new SqlConnection("[YourConnectionString]"))
   {
        cn.Open();

        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT Count(*) FROM [yourTableName] WHERE userName = @userName AND password = @password";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@userName", userName);
            cmd.Parameters.AddWithValue("@password", password);

            try
            {
               isPresent = (int)cmd.ExecuteScalar();
               return isPresent == 1;
            }
            catch (SqlException)
            {
               throw;
            }
        }
    }
}

Codemaniac · July 16th, 2012, 02:38 PM

Sorry for the delayed reply Guenfire,

I shifted my focus to stored procedure than to pass credentials from one website to another.

I was just testing on my local computer on how to check if the username and password is present in the table. (The user will enter the username and password in two textboxes. )

I have the database present in my local machine and wanted to write a store procedure that will check the database if the value is present. if both the credentials are correct i.e. it matches to the record present in the database, It will redirect to a certain page (lets say index.aspx). if they don't match it will just say "access denied".

Also could you please help me with how to call that specific stored procedure in C# code.

Could you please help me with the C# code to implement the above query.
Thanks in advance.

guenfire · July 16th, 2012, 05:15 PM

Here's what it would look like if using a stored procedure:

Code:

string userName = txtUserName.Text;
string password = txtPassword.Text;
int isPresent = 0;

private bool AuthenticateUser(string userName, string password)
{
   using (SqlConnection cn = new SqlConnection("[YourConnectionString]"))
   {
        cn.Open();

        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = cn;
            cmd.CommandText = "name_of_stored_procedure";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userName", userName);
            cmd.Parameters.AddWithValue("@password", password);

            try
            {
               isPresent = (int)cmd.ExecuteScalar();
               return isPresent == 1;
            }
            catch (SqlException)
            {
               throw;
            }
        }
    }
}

Make sure that when you write the stored procedure that you add this line to the end of the query:

Code:

SELECT CAST(SCOPE_IDENTITY() AS INT);

This is because you're using the ExecuteScalar() method, which is designed to return the identity record. This will ONLY work if your first column is of type int and unique.

Codemaniac · July 16th, 2012, 05:21 PM

Thanks Guenfire.

I tried to write the stored procedure, but apparently its wrong, coz its not working:
__________________________________________________ ______
ALTER PROCEDURE dbo.StoredProcedure1
@userNameTXT varchar(10), @passwordTXT varchar(10)
AS

if exists (select userName, password from TB_credentials where userName = @userNameTXT and password = @passwordTXT)
begin
select 88888, 'MR.'+ upper (@userNameTXT)+ 'logged in successful'
return 2
end

else
begin
select 99999, 'MR.'+ upper (@userNameTXT)+ 'logged in successful'

RETURN 0
end
__________________________________________________ _____

Please help!

guenfire · July 16th, 2012, 06:26 PM

Why are you trying to return user interface elements in a stored procedure??? You need to separate concerns with any web application.

Code:

ALTER PROCEDURE dbo.StoredProcedure1 
	-- Add the parameters for the stored procedure here
(
	@userNameTXT varchar(10), 
	@passwordTXT varchar(10)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT COUNT(*)
	FROM TB_credentials
	WHERE ([userName] = @userNameTXT AND [password] = @passwordTXT);
	SELECT CAST(SCOPE_IDENTITY() AS INT);	      
END

Codemaniac · July 16th, 2012, 06:46 PM

That was just my laziness, I was trying to do everything at one go.

Apart from that I did Exactly as you told. But my application doesn't do anything.

**Default.aspx.cs**

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Net;

namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           
        }
        
        private bool AuthenticateUser(string userNameTXT, string passwordTXT)
        {
            string userName1 = TextBox1.Text;
            string password1 = TextBox2.Text;
            int isPresent = 0;

            using (SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS; Initial Catalog=DB_Rental; Integrated Security=True;"))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = cn;
                    cmd.CommandText = "StoredProcedure1";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@userNameTXT", userName1);
                    cmd.Parameters.AddWithValue("@passwordTXT", password1);
                    try
                    {
                        isPresent = (int)cmd.ExecuteScalar();
                        return isPresent == 1;

                    }
                    catch (SqlException)
                    {
                        throw;
                    }
                }

            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            AuthenticateUser(TextBox1.Text, TextBox2.Text);
        }

                
        
    }
}

**Stored Procedure**

Code:

ALTER PROCEDURE dbo.StoredProcedure1 
	-- Add the parameters for the stored procedure here
(
	@userNameTXT varchar(10), 
	@passwordTXT varchar(10)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT COUNT(*)
	FROM TB_credentials
	WHERE ([userName] = @userNameTXT AND [password] = @passwordTXT);
	SELECT CAST(SCOPE_IDENTITY() AS INT);	      
END

But when I click on the login button. It doesn't do anything.
I want it to display something, If the credentials does not match then "Access denied" if it matches then redirect it to "index.aspx".

I AM SORRY ABOUT BUGGING YOU SO MUCH, BUT I JUST NEED A LITTLE PUSH, THATS ALL!

THANKS

Codemaniac · July 16th, 2012, 07:09 PM

Sorry, That was a stupid question.
I got it sorted!!

Thanks a lot Guenfire. You were of great help!

Codemaniac · July 17th, 2012, 02:02 AM

Guenfire, I need an expert advice from you regarding the "REST call".

I have developed a dashboard which contains username and password. Now I want to transfer these credentials to another website so that user don't have to login again. AND I am asked to implement it through REST calls.

Could you please explain what is REST call. As far as I have researched REST is just a style just like object oriented programming is a style of coding. Could you please just break it down for me or point me to some tutorial.

Everywhere I see, REST call are implemented through a web service. Can't it implemented between a web application and a website.

Hoping for your expert reply. Thanks :)

Imar · July 17th, 2012, 03:03 AM

Hi Guenfire,

I like your solution, but would like to make a few minor tweaks / corrections:

1.

Quote:

This is because you're using the ExecuteScalar() method, which is designed to return the identity record.

It's not; ExecuteScalar is designed to return the first cell of the first row, not the record's identity. For details: ExecuteScalar.

2. Why are you executing SELECT SCOPE_IDENTITY when you're reading data? SCOPE_IDENTITY returns the last identity value inserted into an identity column in the current scope. Since you're not inserting, but only selecting, this will return null. Executing a COUNT and then use ExecuteScalar to read that value should be all you need.

3. Don't use try/catch and then just throw in the catch. This would behave exactly the same as no try/catch at all. However, by looking at the code now you may be given the impression that it handles exceptions which it doesn't.

Other than that: great answers!

Cheers,

Imar