2

I have a database(username(key),Fullname,Pass), I want to search for the username and write out the Fullname for this username in a console. I have these:

static DataTable dt = new DataTable();
static SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""path\login.mdf"";Integrated Security=True");

con.Open();
string username="mate";
    SqlDataAdapter sda = new SqlDataAdapter("SELECT Nev FROM Login WHERE username='" + username+ "'", con);
        sda.Fill(dt);
        string fullname;
        if (dt.Rows[0][0].ToString() == "1")then?

"Nev" means Full name

I want it to write the Fullname into the "fullname" string, then just:

Console.Writeline("Welcome {0}",fullname);

Can you help me with this code?
I just don't know how to continue my code. Users can login and register to the app, and when they login i want to greet them like "Hello FullName" when they register their username, fullname and password is stored, and they login with username & pass. I just don't know how to find the Fullname if i know the username. (I'm a beginner in SQL)

  • You don't seem to have opened a connection. – chaosifier Oct 06 '17 at 15:56
  • Just missed a line, there's a con.open @chaosifier –  Oct 06 '17 at 15:58
  • What is your question? What is the specific problem you are having? This should be in the body of the question. https://stackoverflow.com/help/how-to-ask –  Oct 06 '17 at 15:58
  • 1
    Why are you using DataAdapter? you just want to get ONE username, yes? Then use ExecuteScalar (it will directly return the string you need, and it's much faster) – Renato Afonso Oct 06 '17 at 15:59
  • So you want to know how to get the Fullname from the DataTable? – chaosifier Oct 06 '17 at 15:59
  • I just don't know how to continue my code. Users can login and register to the app, and when they login i want to greet them like "Hello FullName" @jdv –  Oct 06 '17 at 16:01
  • Exactly! if i know the username @chaosifier –  Oct 06 '17 at 16:01
  • @Skickpause you should **update the body of the question with these details** –  Oct 06 '17 at 16:02
  • @RenatoAfonso yes, when they register their username, fullname and password is stored, and they login with username & pass. I just don't know how to find the Fullname if i know the username. (I'm a beginner in SQL) –  Oct 06 '17 at 16:03
  • 2
    You should not concatenate a SQL statement like that especially if the value you are concatenating with (username) comes from user input it leaves you open to SQL injection. you should use parameters. An in the case you are looking for a single distinct value you can use ExecteScalar. There are a lot of examples of this on SO – Matt Oct 06 '17 at 16:04
  • 1
    one example: https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i. Without parameters someone could literally drop all of your data, delete users, delete databases, and worse – Matt Oct 06 '17 at 16:06

2 Answers2

2

I think this is what you're after:

string fullname = null;
using(var con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""path\login.mdf"";Integrated Security=True")) {
    string username = "mate";
    SqlCommand cmd = new SqlCommand("SELECT Nev FROM Login WHERE username=@Username", con);
    cmd.Parameters.AddWithValue("@Username", username);

    con.open();
    fullname = cmd.ExecuteScalar()?.ToString();
}

if (fullname == null) {
    Console.WriteLine("The username could not be found!");
}

The using ensures that the connection is cleaned up once the block is exited. Also, you should use Parameters to be more resilient to SQL injection.

Nate Jenson
  • 2,664
  • 1
  • 25
  • 34
  • System.TypeInitializationException was unhandled Message: An unhandled exception of type 'System.TypeInitializationException' occurred in mscorlib.dll - app goes to break mode" –  Oct 06 '17 at 16:27
  • Are you sure your connection string ("Data Source=(LocalDB)..." is correct? – Nate Jenson Oct 06 '17 at 16:30
  • Now it is. But still " Object reference not set to any object instance." –  Oct 06 '17 at 16:42
  • @Skickpause Are you absolutely certain that there is a username `'mate'` in your database? To me it sounds like the query isn't returning any results, so `cmd.ExecuteScalar()` is coming back null. – Nate Jenson Oct 06 '17 at 16:51
  • https://imgur.com/a/qfDtL I am 1000% sure. or is it a problem that the database is in the other project's folder? But the connection string is correct –  Oct 06 '17 at 16:54
  • @Skickpause Hmm. I have an idea, try removing the single quotes `'` around `@Username` in the SQL: `"SELECT Nev FROM Login WHERE username=@Username"`. I've edited my answer accordingly. – Nate Jenson Oct 06 '17 at 17:00
  • 1
    YES. YOU'RE AWESOME :D –  Oct 06 '17 at 17:01
  • one more question. if i use parameters like this. the this can't be hacked with sql injection, right? –  Oct 06 '17 at 17:02
  • Yeah, parameterization is the safer way to put input into SQL: https://stackoverflow.com/a/4892205/2329708 – Nate Jenson Oct 06 '17 at 17:04
0

For your specific problem, try this code:

if(dt.Rows.Count > 0)
      fullname = dt.Rows[0]["Nev"].ToString();

But here's a better and complete solution to your problem:

string userName = "mate";
string fullName = string.Empty;

string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=path\login.mdf;Initial Catalog=Login;Integrated Security=True";
string query = "SELECT Nev FROM Login WHERE username=@UserName;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.Add("@UserName", SqlDbType.NVarChar);
     command.Parameters["@UserName"].Value = userName;

     try
     {
         connection.Open();
         fullName = command.ExecuteScalar().ToString();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
}

Console.WriteLine("Hello {0}!", fullName);
chaosifier
  • 2,666
  • 25
  • 39