[RESOLVED] ExecuteNoneQuery() problem!

[toraj58]

i have written this code to check the user that wants to log in but i think it is not efficient:


protected void Button1_Click(object sender, EventArgs e)
{
if (Button1.Text == "Sign out")
{
Session.Abandon();
Button1.Text = "Sign in";
//Label1.Text = "You signed out successfully!";
Label1.Visible = true;
Response.Redirect("~/default.aspx");
}
else
{
//Session["logged"] = true.ToString();
string constring = WebConfigurationManager.ConnectionStrings["gisConnectionString1"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
string selectCommand = "select id from members where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(selectCommand, con);

cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);

try
{
con.Open();
SqlDataReader data = cmd.ExecuteReader();
if (data.HasRows)
{
Session["logged"] = true.ToString();
Label1.Text = "Signed in!";
Label1.Visible = true;
Button1.Text = "Sign out";
// MessageBox.Show("hi!");

}
else
{
Session["logged"] = false.ToString();
}
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
finally
{
con.Close();
}
}
}


i have highlighted the line i think it is not efficient in RED.
i also test this approach:
i replaced the red line with this code:


int result = cmd.ExecuteNoneQuery();



and then i check result to see if it is greater than 0 to let the user log in but it is always 0 (or less than it) when i use ExecuteNoneQuery().

i don't want to load data to SqlDataReader because it takes memory, i just want to know whether the query has result or not. what i have done wrong with ExecuteNoneQuery() that always retruned value from it, is not greater than 0?
i guess that ExecuteNoneQuery() does not work with select statement; am i right?

any idea?

[vuyiswam]

First you are using a Reader. Let me tell you how i do it

First i have Written a StoreProcedure that will bring the 1 OR 0, that means one will be success and 0 will failure , like this


create Proc [dbo].[prcLoginv]
(
@Username VarChar(50),
@UPassword varChar(50),
@OutRes int OUTPUT
)
AS
set @OutRes = (SELECT count(*) FROM [dbo].[LOGINS]
WHERE Username = @Username And Password = @UPassword)
select case @OutRes
when 1 then 1
else
0
end


and in my DAL i will pass the integer value to the BLL that will tell the PL that the logion is fine or not. My DAL Function will look like this



SqlCommand cmdselect;

public int Check_Login(String Username, String Password)
{

con = new SqlConnection(strcon);

cmdselect = new SqlCommand();


cmdselect.CommandTimeout = 0;


cmdselect.CommandType = CommandType.StoredProcedure;


cmdselect.CommandText = "dbo.prcLoginv";


cmdselect.Parameters.Add("@Username", SqlDbType.VarChar, 55, "UserName").Value = Username;


cmdselect.Parameters.Add("@UPassword", SqlDbType.VarChar, 55, "UPassword").Value = Password;


cmdselect.Parameters.Add("@OutRes", SqlDbType.Int);


cmdselect.Parameters["@OutRes"].Direction = ParameterDirection.Output;


cmdselect.Connection = con;


int res = 0;

try
{
con.Open();

cmdselect.ExecuteNonQuery();

res = Convert.ToInt32(cmdselect.Parameters["@OutRes"].Value);


}
catch (SqlException)
{
throw;

}
finally
{
con.Close();
}

return res;
}


And in PL i will test and Display Messages like this


String strusername = txtusername.Text;

String strPassword = txtpassword.Text;


BLL.BLL obj = new BLL.BLL();


int bl = 0;

int Role=0;

try
{

bl = obj.Check_Login(strusername, strPassword);

Role = obj.Get_Role(strusername);

if (bl == 1 && Role== 1 )
{
Session["USERNAME"] = strusername;

Response.Redirect("~/Secure/WelcomeUser.aspx", false);
}
else if(bl == 1 && Role==2)
{
Session["USERNAME"] = strusername;

Response.Redirect("~/Secure/Welcome_Admin.aspx", false);
}
else if (bl == 0 && Role== 0)
{
Response.Write("<script language='javascript'>alert('Invalid Login');</script>");

}
else if (bl == 0 && Role == 1)
{
Response.Write("<script language='javascript'>alert('Invalid Login');</script>");
}
else if (bl == 0 && Role == 2)
{
Response.Write("<script language='javascript'>alert('Invalid Login');</script>");
}

}


As you can see mine has a Role, Because in any Decent System, there are Different type of Users , and that means the users cannot just navigate to any page.

Hope i Helped, If you have more Question just Shout :)

[eclipsed4utoo]

ExecuteNonQuery() is used to insert, update, or delete. The returning int value tells you how many rows were effected. This can't be used for a select statement. (unless you do something like vuyiswam states)

If it was me, I would write a simple select query using the Count function....similar to this:


bool authenticated = false;

using (SqlConnection cn = new SqlConnection(connectionString))
{
string selectCommand = "select COUNT(id) from members where username=@username and password=@password";

SqlCommand cmd = new SqlCommand(selectCommand, con);

cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);

// ExecuteScalar returns the first column of the first row
int result = (int)cmd.ExecuteScalar();

if (result > 0)
authenticated = true;
}

return authenticated;

[toraj58]

yes, using count and ExecuteScalar() is better approach that i did not think about it. thanks.