Sql Query converting to Linq Query By using Wcf Service Platefrom

Question

I am currently working on Wcf Service and consuming it in Angular JS application. I am trying converting Sql query to Linq Query. I am creating user login system based on username and password . If user enter wrong username and password four time then I want locked user account into sql database . Otherwise if user enter wrong username and password first time but enter correct username and password second time then I want to return the method true and recount the Attempt to Zero . But I am getting expected result with the Linq query .

Here is my ADO.NET Code ...

 public bool AuthenticateUser(UserLogin userLogin)
    {

        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(userLogin.Password, "SHA1");
            SqlParameter paramUsername = new SqlParameter("@UserName", userLogin.Username);
            SqlParameter paramPassword = new SqlParameter("@Password", encryptedpassword);

            cmd.Parameters.Add(paramUsername);
            cmd.Parameters.Add(paramPassword);

            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                int RetryAttempts = Convert.ToInt32(rdr["RetryAttempts"]);
                if (Convert.ToBoolean(rdr["AccountLocked"]))
                {
                    return false;
                }
                else if (RetryAttempts > 0)
                {
                    int AttemptsLeft = (4 - RetryAttempts);

                }

                else if (Convert.ToBoolean(rdr["Authenticated"]))
                {
                    return true;
                }

                else
                {
                    return false;
                }
            }
        }
    }

Here is my Linq query I tried to converted but it is not giving expecting result .

 public bool AuthenticateUser(UserLogin userLogin)
                {
            using (HalifaxDatabaseEntities db = new HalifaxDatabaseEntities())
                    {
      var attamp = from X in db.tblUsers
     where X.Username == userLogin.Username&&  X.Password == userLogin.Password
      select X.RetryAttempts != 4;
      return attamp.Single();

                    }
                }

What will the correct Linq for Following Sql query

Because with the ADO.NET I am receiving Null Reference Exception but with Linq Query I am able to run it successful — Mohammad
Do you want the linq to return whether the limit exceeded or not? — Pon Saravanan
Yes . I want to return the number of attempt user made as well — Mohammad

Pon Saravanan Pon Saravanan · Accepted Answer · 2017-10-02T00:42:29

You may want to take out the password from filter to calculate total number of retries. but use it while actually authenticating the user.

Also once it is successfully authenticated reset the counter to zero.

bool LimitExceeded()
{
var exceeded=false;
var totalRetries =-1;
var attamp = from X in db.tblUsers
                where X.Username == userLogin.Username 
                select X

 if( attamp.Any())
 {
        if(attamp.firstOrDefault().RetryAttempts.HasValue)      totalRetries =attamp.firstOrDefault().RetryAttempts;
        exceeded=  totalRetries>4;
 }
 return exceeded
}

The above code is not written using any code editor. That may not compile. I just wrote to give you an idea.

Sql Query converting to Linq Query By using Wcf Service Platefrom

1 Answers