Jacob Jewett
Reputation: 359
C# - "Connection must be valid and open" with multiple Queries and Methods | Connection not closed?
For many hours now I have lost it over this same error no matter what I change.
I'm trying to make a command line application that will retrieve info from a database based off of two argument inputs, the modname and the optional author. The queries are all handled in the jobs.cs file. The entry-point file calls the method beginJob() which gets the target modname and author (if specified) to query. What's different from the hundred or so other posts I've read here is that the first query issued works completely fine, and I'm sure to close the reader and connection. (Won't close connection every time in production, but I did here for debugging purposes). The next time I call the connection it results in this error.
System.InvalidOperationException: Connection must be valid and open.
at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)
at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)
at MySql.Data.MySqlClient.MySqlCommand.CheckState()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at vmcmod.Job.queryAuthor(String authorUID) in F:\Projects\VisualStudio\VMC-MOD\vmcmod\job.cs:line 235
at vmcmod.Job.StartJob() in F:\Projects\VisualStudio\VMC-MOD\vmcmod\job.cs:line 47
at vmcmod.Job.queryModAndAuthor(String modname, String author) in F:\Projects\VisualStudio\VMC-MOD\vmcmod\job.cs:line 205
at vmcmod.Job.beginJob() in F:\Projects\VisualStudio\VMC-MOD\vmcmod\job.cs:line 27
at vmcmod.VMCMOD.parseArgs() in F:\Projects\VisualStudio\VMC-MOD\vmcmod\vmcmod.cs:line 58
Some posts I've looked at but don't seem to help:
- C# Mysql Connection must be valid and open
- Connection must be valid and open error
- C# Query: 'System.InvalidOperationException' | Additional information: Connection must be valid and open
C# Source Files (Please try and not cringe too hard, this is my first C# application):
jobs.cs [updated]
using MySql.Data.MySqlClient;
using System;
namespace vmcmod
{
class Job
{
public bool checkInput()
{
if (Global.currentJobTargetModname == "undefined")
{
Utils.consoleLog("The mod name is required.", 3);
return false;
}
else
{
return true;
}
}
public void beginJob()
{
string targetModname = Global.currentJobTargetModname.ToLower();
string targetModAuthor = Global.currentJobTargetAuthor.ToLower();
if (targetModAuthor != "undefined")
{
queryModAndAuthor(targetModname, targetModAuthor);
Utils.consoleLog("Job Call. (Author defined)", 4);
}
else
{
queryMod(targetModname);
Utils.consoleLog("Job Call. (Author undefined)", 4);
}
}
private void StartJob()
{
string author = null;
string targetModAuthor = Global.currentJobTargetAuthor.ToLower();
Utils.consoleLog("Mod exists.", 5);
if (targetModAuthor == "undefined")
{
author = "(None; First in row)";
}
else
{
queryAuthor(Global.queryResModAuthorID);
author = "'" + Global.queryResModAuthor + "' (UUID:" + Global.queryResModAuthorID + ") ";
}
var collaborators_obj = Newtonsoft.Json.Linq.JObject.Parse(Global.queryResModCollab);
var tags_obj = Newtonsoft.Json.Linq.JObject.Parse(Global.queryResModTags);
Console.BackgroundColor = ConsoleColor.Blue;
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine("MOD INSTALL SPECIFICATIONS:");
Console.BackgroundColor = ConsoleColor.Black;
Console.WriteLine(" Mod Name: '" + Global.queryResModName + "'");
Console.Write(" Mod Author: " + author);
if (Global.queryResModAuthorVerified == true && targetModAuthor != "undefined")
{
Utils.consoleTag("*VERIFIED*", ConsoleColor.Green);
}
if (Global.queryResModAuthorVerified == false && targetModAuthor != "undefined")
{
Utils.consoleTag("*UNVERIFIED*", ConsoleColor.Red);
}
Console.Write("\n");
Console.WriteLine(" Mod Version: " + Global.queryResModVersion);
Console.WriteLine(" Installations: " + Global.queryResModInstalls);
Console.WriteLine(" Description:\n " + Global.queryResDescription);
Console.WriteLine(" Mod UUID: " + Global.queryResModUid);
if (Global.advInfo == true)
{
Console.WriteLine(" Rep. Entry #" + Global.queryResModID);
Console.WriteLine(" Mod Created On: " + Utils.UnixTimeStampToDateTime(Convert.ToDouble(Global.queryResDateCreated)));
Console.WriteLine(" Mod Last Modified On: " + Utils.UnixTimeStampToDateTime(Convert.ToDouble(Global.queryResDateLastModf)));
Console.WriteLine(" Tags: \n " + Convert.ToString(tags_obj["tags"]));
Console.WriteLine(" Collaborators: \n " + Convert.ToString(collaborators_obj["collaborators"]));
}
Utils.consoleSeparator();
bool user_response = Utils.consoleAskYN("Are you sure you want to install this mod?");
if (user_response == true)
{
queryGetModJSON(Global.queryResModUid);
Utils.consoleLog("JSON Data: "+Global.queryResModJSON,5);
//FileIO fio = new FileIO();
//fio.jsonToFiles(fio.jsonToArray(Global.queryResModJSON));
}
else
{
Utils.consoleSetKeyExit();
}
}
private void queryGetModJSON(string uuid)
{
var dbCon = new DBConnection();
if (dbCon.IsConnect() == false)
{
Utils.consoleLog("Unable to query repository or no results.", 3);
Utils.consoleSetKeyExit();
}
else
{
string q = "SELECT data_json FROM vmcmod_repository_data WHERE uid='" + uuid + "' LIMIT 1;";
MySqlCommand cmd = new MySqlCommand(q, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
Utils.consoleLog("Mod data not found.", 3);
reader.Close();
dbCon.Close();
}
else
{
while (reader.Read())
{
Global.queryResModJSON = reader.GetString(0);
}
reader.Close();
dbCon.Close();
}
}
}
private void queryMod(string modname)
{
var dbCon = new DBConnection();
if (dbCon.IsConnect() == false)
{
Utils.consoleLog("Unable to query repository or no results.", 3);
Utils.consoleSetKeyExit();
}
else
{
string q = "SELECT id,uid,author_id,mod_name,status,dependencies_json,description,mod_version,date_created,date_last_modified,collaborators,tags_json,installs FROM vmcmod_repository_info WHERE mod_name LIKE'" + modname + "' LIMIT 1;";
MySqlCommand cmd = new MySqlCommand(q, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
Utils.consoleLog("Mod not found.", 3);
reader.Close();
dbCon.Close();
}
else
{
while (reader.Read())
{
Global.queryResModInstalls = reader.GetInt32(12);
Global.queryResModTags = reader.GetString(11);
Global.queryResModCollab = reader.GetString(10);
Global.queryResDateLastModf = reader.GetInt32(9);
Global.queryResDateCreated = reader.GetInt32(8);
Global.queryResModVersion = reader.GetFloat(7);
Global.queryResDescription = reader.GetString(6);
Global.queryResModDependencies = reader.GetString(5);
Global.queryResModStatus = reader.GetInt16(4);
Global.queryResModName = reader.GetString(3);
Global.queryResModAuthorID = reader.GetString(2);
Global.queryResModUid = reader.GetString(1);
Global.queryResModID = Convert.ToInt32(reader.GetInt32(0));
}
reader.Close();
dbCon.Close();
StartJob();
}
}
}
private void queryModAndAuthor(string modname, string author)
{
var dbCon = new DBConnection();
if (dbCon.IsConnect() == false)
{
Utils.consoleLog("Unable to query repository or no results.", 3);
Utils.consoleSetKeyExit();
}
else
{
string q = "SELECT id,uid,author_id,mod_name,status,dependencies_json,description,mod_version,date_created,date_last_modified,collaborators,tags_json,installs FROM vmcmod_repository_info WHERE mod_name LIKE'" + modname + "' LIMIT 1;";
MySqlCommand cmd = new MySqlCommand(q, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
Utils.consoleLog("Mod not found.", 3);
reader.Close();
dbCon.Close();
}
else
{
while (reader.Read())
{
Global.queryResModInstalls = reader.GetInt32(12);
Global.queryResModTags = reader.GetString(11);
Global.queryResModCollab = reader.GetString(10);
Global.queryResDateLastModf = reader.GetInt32(9);
Global.queryResDateCreated = reader.GetInt32(8);
Global.queryResModVersion = reader.GetFloat(7);
Global.queryResDescription = reader.GetString(6);
Global.queryResModDependencies = reader.GetString(5);
Global.queryResModStatus = reader.GetInt16(4);
Global.queryResModName = reader.GetString(3);
Global.queryResModAuthorID = reader.GetString(2);
Global.queryResModUid = reader.GetString(1);
Global.queryResModID = Convert.ToInt32(reader.GetInt32(0));
}
reader.Close();
dbCon.Close();
StartJob();
}
}
}
private bool queryCheckAuthorExists(string author)
{
var dbCon = new DBConnection();
string q = "SELECT * FROM vmcmod_users WHERE username='"+author+ "' AND is_author=true LIMIT 1;";
MySqlCommand cmd = new MySqlCommand(q, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (reader.HasRows == false)
{
Utils.consoleLog("Author not found.", 3);
reader.Close();
dbCon.Close();
return false;
}
else
{
Utils.consoleLog("Author found.", 4);
reader.Close();
dbCon.Close();
return true;
}
}
private void queryAuthor(string authorUID)
{
var dbCon = new DBConnection();
string q = "SELECT username,is_verified_user FROM vmcmod_users WHERE uid='" + authorUID + "' AND is_author=true LIMIT 1;";
MySqlCommand cmd = new MySqlCommand(q, dbCon.Connection);
var reader = cmd.ExecuteReader();
if (reader.HasRows == false)
{
Utils.consoleLog("Author not found.", 3);
reader.Close();
dbCon.Close();
}
else
{
while (reader.Read())
{
Global.queryResModAuthor = reader.GetString(0);
Global.queryResModAuthorVerified = reader.GetBoolean(1);
}
Utils.consoleLog("Author found.", 4);
reader.Close();
dbCon.Close();
}
}
}
}
dbinterface.cs (this wasn't written by me, rather taken and edited from a post on stackoverflow, here) [updated]
using MySql.Data.MySqlClient;
using System;
namespace vmcmod
{
public class DBConnection
{
public DBConnection()
{
}
public string Password { get; set; }
private MySqlConnection connection = null;
public MySqlConnection Connection
{
get { return connection; }
}
public bool IsConnect()
{
bool result = true;
if (Connection == null)
{
string connString = "Server=...; Port=...; Database=...; Uid=...; Pwd=...;";
connection = new MySqlConnection(connString);
try
{
connection.Open();
Utils.consoleLog("Connected to repository.", 4);
result = true;
}
catch (Exception e)
{
Utils.consoleLog("Error occured while connecting to repository.", 3);
Utils.consoleLog("MySQL Exception: "+e,5);
result = false;
}
}
return result;
}
public void Close()
{
connection.Close();
connection = null;
}
}
}
global.cs (global variable storage)
using System;
namespace vmcmod
{
class Global
{
internal const float version = 0.1F;
internal static string currentJobWorld = "New World";
internal static string currentJobTargetModname = "undefined";
internal static string currentJobTargetAuthor = "undefined";
internal static string currentJobProfile = "default";
internal static int currentJobAccountPIN;
internal static bool verbose = false;
internal static bool debug = false;
internal static bool advInfo = false;
internal static bool queryResModAuthorVerified = false;
internal static string queryResModUid;
internal static string queryResModAuthorID;
internal static string queryResModAuthor;
internal static string queryResModName;
internal static string queryResModDependencies = "{\"dependencies\":[]}";
internal static int queryResModStatus;
internal static float queryResModVersion;
internal static string queryResDescription = "None provided.";
internal static int queryResDateCreated;
internal static int queryResDateLastModf;
internal static int queryResModID;
internal static string queryResModCollab = "{\"collaborators\":[]}";
internal static string queryResModTags = "{\"tags\":[]}";
internal static int queryResModInstalls;
internal static string queryResModJSON = "{}";
}
}
Upvotes: 0
Views: 2493
Answers (3)
sBanda
Reputation: 379
I see you are using singletne pattern for DBConnection this is not a good idea.
private static DBConnection _instance = null;
public static DBConnection Instance()
{
if (_instance == null)
_instance = new DBConnection();
return _instance;
}
You might want to check this answer out for more information: https://stackoverflow.com/a/814606/8143718
Upvotes: 1
Jacob Jewett
Reputation: 359
As per @mjwills' response, the DB interface script I'd swiped was quite funky and only allowed one instance at a time.
Working DB Interface
using MySql.Data.MySqlClient;
using System;
namespace vmcmod
{
public class DBConnection
{
public DBConnection()
{
}
public string Password { get; set; }
private MySqlConnection connection = null;
public MySqlConnection Connection
{
get
{
IsConnect();
return connection;
}
}
public bool IsConnect()
{
bool result = true;
if (connection == null)
{
string connString = "Server=...; Port=...; Database=...; Uid=...; Pwd=...;";
connection = new MySqlConnection(connString);
try
{
connection.Open();
Utils.consoleLog("Connected to repository.", 4);
result = true;
}
catch (Exception e)
{
Utils.consoleLog("Error occured while connecting to repository.", 3);
Utils.consoleLog("MySQL Exception: "+e,5);
result = false;
}
}
return result;
}
public void Close()
{
connection.Close();
connection = null;
}
}
}
Upvotes: 0
mjwills
Reputation: 23820
The quick and dirty solution would be to change:
public void Close()
{
connection.Close();
}
to:
public void Close()
{
connection.Close();
connection = null;
}
Also, remove this code:
private static DBConnection _instance = null;
public static DBConnection Instance()
{
if (_instance == null)
_instance = new DBConnection();
return _instance;
}
and rather than use DBConnection.Instance, just use new DBConnection();
And change:
public MySqlConnection Connection
{
get { return connection; }
}
to:
public MySqlConnection Connection
{
get {
IsConnect();
return connection; }
}
and change:
public bool IsConnect()
{
bool result = true;
if (Connection == null)
to:
public bool IsConnect()
{
bool result = true;
if (connection == null)
Upvotes: 1
Related Questions
- MySQL C# Error; "connection must be valid and open"
- Connection already open error c#
- C# SqlConnection stop at method: Open()
- C# connection.Open() exception
- c# Connection must be valid and open when fetching multiple tables
- Connection must be open C#
- C# Mysql Connection must be valid and open
- Why can't OpenConnection() be called twice in one method?
- Connection must be valid and open error
- Connection state is opened but not valid?