SQL Question

Getting colum information in SQL

Iam somwhat new to SQL, so iam not sure iam going about this the right way.
iam trying to fetch data from my sql db where i want to find out if checkedin is 1/0, but it needs to search on a specific user and sort after the newest date aswell. What iam trying to do is something like this:

string connectionString = ".....";
SqlConnection cnn = new SqlConnection(connectionString);
SqlCommand checkForInOrOut = new SqlCommand("SELECT CHECKEDIN from timereg ORDER BY TIME DESC LIMIT 1 WHERE UNILOGIN = '" + publiclasses.unilogin + "'", cnn);

So my question, am i doing this right? and how do i fetch the data collected, if everything was handled corretly it should return 1 or 0. Should i use some sort of sqldatareader? Iam doing this in C#/WPF


Answer Source

There are some errors in your query. First WHERE goes before ORDER BY and LIMIT is an MySql keyword while you are using the Sql Server classes. So you should use TOP value instead.

int checkedIn = 0;
string cmdText = @"SELECT TOP 1 CHECKEDIN from timereg 
                   WHERE UNILOGIN = @unilogin
                   ORDER BY TIME DESC";
string connectionString = ".....";
using(SqlConnection cnn = new SqlConnection(connectionString))
using(SqlCommand checkForInOrOut = new SqlCommand(cmdText, cnn))
    cmd.Parameters.Add("@unilogin", SqlDbType.NVarChar).Value = publiclasses.unilogin;

    // You return just one row and one column, 
    // so the best method to use is ExecuteScalar

    object result = cmd.ExecuteScalar();

    // ExecuteScalar returns null if there is no match for your where condition
    if(result != null)
       MessageBox.Show("Login OK");

       // Now convert the result variable to the exact datatype 
       // expected for checkedin, here I suppose you want an integer
       checkedIN = Convert.ToInt32(result);
       MessageBox.Show("Login Failes");

Note how I have replaced your string concatenation with a proper use of parameters to avoid parsing problems and sql injection hacks. Finally every disposable object (connection in particular) should go inside a using block