How to retrieve return row value from a SQL Server Compact database using C#.NET winform

Question

Table with columns:

rowID, username, password, administrator, 

My code:

SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Visual Studio 2010\Projects\CompactSQL\CompactSQL\DBCompact.sdf;");
con.Open();

SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM DBLogin WHERE username = '" + textBox1.Text + "' AND password = '" + textBox2.Text + "'", con);

cmd.ExecuteNonQuery();

Int32 cnt = (Int32)cmd.ExecuteScalar();

MessageBox.Show(cnt.ToString());
DataTable dt = new DataTable();

SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
da.Fill(dt);

foreach (DataRowView dr in dt.Rows) 
{
    // how do I get the column result based on the SqlCeCommand I execute? 
}

/* ex.: this only work on LinQ to SQL where I use a "foreach" to 
        gather up results of the column retrieved based on the 
        query statement executed.

        foreach(var r in row) 
        {
            id = r.rowID;
            user = r.username;
            pass = r.password;
            access = r.access;
            logID = r.logID  
       }
*/

I use a SQL Server Compact database for a login form what I want is that when a user login input the username and password on the text field a query is execute to compare if a user exist on the compact db and when found return back the username, access, and logid in a loop with a count of how many row record exist.

Answer 1

Try this

foreach (DataRow dr in dt.Rows) 
{
    id = dr["rowID"].ToString();
    user = dr["username"].ToString();;
    pass = dr["password"].ToString();;
    access = dr["access"].ToString();;
    logID = dr["logID"].ToString();
}

You may need to convert some values to their appropriate type like Id and logID looks to be int type.