d4ne d4ne - 4 months ago 18
SQL Question

C# execute SQL query and return or show response

I'm trying to run a SELECT query in my C# application, but im literally stuck actually. How would I select something from a MySql database?

My current code looks like the following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace Helpful
{
class database_connector
{
private MySqlConnection connection;
private string server;
private string database;
private string uid;
private string password;

// Constructor
public database_connector()
{
Initialize();
}

//Initialize values
private void Initialize()
{
server = "xxx";
database = "xxx";
uid = "xxx";
password = "xxx";
string connectionString;
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

connection = new MySqlConnection(connectionString);
}

//open connection to database
private bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server. Contact administrator.");
break;
case 1045:
MessageBox.Show("Invalid username/password, please try again");
break;
}
return false;
}
}

//Close connection
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}

//Select statement
public void user_check(string username, string password)
{
string query = "SELECT * FROM swear_tool WHERE username =" + username;

if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Close();
this.CloseConnection();
}
}
}
}


But how could I see if this actually worked? Because if I try to show the result in a messagebox it would return, could not be translated to a string.

if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
MessageBox.Show(dataReader);
dataReader.Close();
this.CloseConnection();
}

Answer

You need to know what field you want from database. Use a SQL like this SELECT Id, Name FROM swear_tool WHERE username =" + username and the code for retrieve the Name field.
Index 0 = Id
Index 1 = Name <-- GetString(1)

if (dataReader.HasRows) {
    while (dataReader.Read()) {
        Console.WriteLine("{0}", dataReader.GetString(1) );
    }
} else {
    Console.WriteLine("No rows.");
}
Comments