IdeaMonk

thoughts, ideas, code and other things...

Tuesday, March 24, 2009

Finally implemented LINQ to SQL in SpaceLock's Web Interface

A typical ASP.NET MVC tutorial on Models talks about LINQ to SQL. Initially I had great troubles understanding this new syntax and style of LINQ as I am new to ASP.NET and C#. I had decided to write old ADO style connectivity routines for accessing the DB. The password validation code actually became too long and was almost broken, letting blank passwords pass through. No wonder it would be prone to SQL injections too.
While the tutorials from http://asp.net/mvc talks all about simple queries, nothing introduced me on how my classical SQL queries would look like in LINQ. I was looking for a LINQ equivalent of "SELECT password from users WHERE username==$user;"
Bumping around some YouTube videos I found a video called Using LINQ from David Bush. Clear, short and simple. And, with a little reference from 'LINQ for Visual C# 2008' (Apress) I was able to convert the whole SQL garbage to a better more organized LINQ query. See the difference -
// Old classical SQL code
public ActionResult DoLogin(string txtUser, string txtPass)
{
string password = "";
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\spacelockDB.mdf;Integrated Security=True;User Instance=True");
SqlDataReader rdr = null;

if (txtPass == "") txtPass = "!";

try
{
conn.Open();
}
catch
{
ViewData["message"] = "Failed to Create Connection to DB!";
return View("ShowLogin");
}
try
{
SqlCommand cmd = new SqlCommand("Select password from users where username='" + txtUser + "'", conn);
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
password = (string)rdr.GetSqlString(0);
}
if (txtPass == password)
{
Session["LoggedIn"] = "True";
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
return RedirectToAction("Index");
}
else
{
ViewData["message"] = "Wrong Username or Password!";
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
return View("ShowLogin");
}

}
catch
{
ViewData["message"] = "Somethig Wrong with Query!";
return View("ShowLogin");
}
// Shorter, Safer, Organized LINQ code
public ActionResult DoLogin(string txtUser, string txtPass)
{
try
{
spaceDBDataContext spacelockDC = new spaceDBDataContext();
}
catch
{
ViewData["message"] = "Failed to Connect to DB [DataContext failed]!";
return View("ShowLogin");
}

var query = from user1 in spacelockDC.users where user1.username == txtUser select user1.password;

if (query.ToList().Count == 1)
{
// test the password
if (query.ToArray().Last() == txtPass)
{
// Successful Login
Session["LoggedIn"] = "True";
RedirectToAction("Index");
}
else
{
ViewData["message"] = "Wrong Username or Password!";
}
}
else
{
// nothing was selected - invalid username
ViewData["message"] = "Wrong Username or Password!";
}

return View("ShowLogin");
}

Labels: , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home