February 23, 2006

PHP to ASP.NET Scalar Query Port

Lets pretend for a moment that you are writing a bit of PHP code that determines if a value is present in your database. Such code might look something like the following:
<?php
function UserExists($username, $db) {
$sql = "SELECT * FROM Accounts WHERE UserName = '" . $username . "'";
$result = mysql_query($sql, $db);
if (mysql_num_rows($result) > 0) {
return true;
}
else {
return false;
}
}
?>

Now suppose you've been asked to switch this application to ASP.NET 2.0/C#. What would you do? One possible port of this scalar query is listed below:
protected bool UserExists(string userName)
{
int results = 0;
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Sample ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT COUNT(*) FROM Account WHERE UserName = @username";

try
{
SqlParameter paraUserName = new SqlParameter("@username", SqlDbType.NChar, 40);
paraUserName.Value = userName;
conn.Open();
cmd.Parameters.Add(paraUserName);
results = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
conn.Close();
}

//Return true if userName was found in the database
if (results > 0)
{
return true;
}
else
{
return false;
}
}

1 comment:

Ian said...

Thanks, this helped a lot.