First,
never build a SQL script by concatenating strings. Use
parameterized queries instead.
How to Fix SQL Injection Using Microsoft .Net Parameterized Queries[
^]
Create Parameterized Queries in ADO.NET -- Visual Studio Magazine[
^]
Second, why doing a
SELECT *
when you could simply do a
SELECT COUNT(id)
? Why selecting all columns when you only need a single value?
Third, you do not need to fill a datatable just for the sake of counting its elements. If you follow second advise, all you have to do is call
ExecuteScalar
on your command object. Plus, your actual logic is wrong; if the count is greater than zero, the email is NOT available. Maybe you intended to check whether email exists instead?
Example with first method:
private bool CheckEmail()
{
int count = 0;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
conn.Open();
String myquery = "SELECT COUNT([id]) from [test].[dbo].[MYFORM] WHERE [email]=@email";
using (SqlCommand cmd = new SqlCommand(myquery, conn))
{
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = TXTEmail.Text;
try
{
count = cmd.ExecuteScalar();
}
catch (Exception ex)
{
}
}
}
bool emailExists = count > 0;
bool emailAvailable = count == 0;
return emailExists;
}