Mattias Mattias - 3 months ago 42
MySQL Question

MySql connection in differenct class C#

Okey, so I am new to C# and I have tried to move my Mysql connection string to another class but I can't seem to open the connection once I call the method and I really can't see what's wrong.

So this is the connection method in a new class(DatabaseC)

public static void Connection()
{
try
{
ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["cs"];
string conn = conSettings.ConnectionString;
MySqlConnection connect = new MySqlConnection(conn);
connect.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}


And here I call the method in a Form

private bool validate_login(string u, string p)
{
DatabaseC.Connection();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT * FROM account WHERE Password COLLATE latin1_general_cs = @password AND User COLLATE latin1_general_cs = @username";
cmd.Parameters.AddWithValue("@username", u);
cmd.Parameters.AddWithValue("@password", p);
MySqlDataReader login = cmd.ExecuteReader();


Sorry for if the code looks bad but as I said im new.

Answer

You should return the instance of the MySqlConnection opened

public static MySqlConnection Connection()
{
    ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["cs"];
    string conn = conSettings.ConnectionString;
    MySqlConnection connect = new MySqlConnection(conn);
    connect.Open();
    return connect;
}

Now you can change your calling code to receive the connection and use it

private bool validate_login(string u, string p)
{
    using(MySqlConnection cnn = DatabaseC.Connection())
    using(MySqlCommand cmd = cnn.CreateCommand())
    {
        cmd.CommandText = "......"
        ...
        using(MySqlDataReader reader = cmd.ExecuteReader())
        {
           .....
        }  // Here the reader is closed and destroyed
    } // Here the connection closed and destroyed with the command 
}

Notice that a connection is a disposable object and thus you should be sure to destroy it once you have finished to use it. This is the work of the using statement.

Another problem fixed with this code is the fact that a command needs to know the connection to use, your actual code doesn't link the command with the connection and thus it cannot work.

EDIT: you comment below should be added to the answer. The Try/Catch in the Connection method should be removed. You do nothing there and catching the exception creates only complications in the calling code that need to handle a null return value. It is better to let the exception bubble up until there is a method that has something to do with that (like logging it for example)

Comments