John John - 2 months ago 22
C# Question

c# Connection must be valid and open when fetching multiple tables

I'm currently writing a simple program that fetches 4 tables. I can fetch the data all separately but not in a sequence.

DBConnection code:

/*
* Created by SharpDevelop.
* Date: 13.04.2017
* Time: 10:29
*/
using System;
using MySql.Data.MySqlClient;

namespace TWReporting.Database
{
public class DBConnection
{
private DBConnection()
{
}

private string databaseName = "**********";
private string username = "**********";
private string password = "**********";

public string DatabaseName {
get { return databaseName; }
set { databaseName = value; }
}

public string Password {
get { return password; }
set { password = value; }
}
public string Username {
get { return username; }
set { username = value; }
}

private MySqlConnection connection = null;
public MySqlConnection Connection {
get { return connection; }
}

private static DBConnection _instance = null;
public static DBConnection Instance()
{
if (_instance == null)
_instance = new DBConnection();
return _instance;
}

public bool IsConnect()
{
bool result = true;
if (Connection == null) {
if (String.IsNullOrEmpty(databaseName))
result = false;
string connstring = string.Format("Server=localhost; database={0}; UID={1}; password={2}", databaseName, Username, Password);
connection = new MySqlConnection(connstring);
connection.Open();
result = true;
}
return result;
}

public void Close()
{
connection.Close();
}
}
}


Example of a method using this:

public class DataFetcher
{
public List<TestSessionDut> duts;
public List<TestSession> testSessions;
public List<TestScriptRun> testScriptRuns;
public List<Engine> engines;

public DataFetcher()
{
duts = new List<TestSessionDut>();
testSessions = new List<TestSession>();
testScriptRuns = new List<TestScriptRun>();
engines = new List<Engine>();
FetchData();
}

public void FetchData()
{
FetchDuts();
FetchEngines();
FetchTestScriptRuns();
FetchTestSessions();
}
public void FetchDuts()
{
var dbCon = DBConnection.Instance();
try
{
if (dbCon.IsConnect())
{
const string query = "SELECT * FROM table";
var cmd = new MySqlCommand(query, dbCon.Connection);
var reader = cmd.ExecuteReader();
while(reader.Read())
{
var tsd = new TestSessionDut();
tsd.ID = reader.GetInt32(0);
tsd.Dut = reader.GetString(1);
this.duts.Add(tsd);
}
}
}
catch(Exception e)
{
Console.WriteLine("Fetch duts");
Console.WriteLine(e.Message);
}
finally
{
dbCon.Close();
}
}


I call 4 methods like these but all from a seperate table. I can retrieve data from all the tables separately but when I call the methods all together It will give this message.

Thanks in advance.

Answer

In IsConnect method you are initializing the connection if it's null. But next time when you call IsConnect the result will remain false as connection is not null any more so it won't go inside if(connection == null).

IsConnect method needs to be changed as following.

public bool IsConnect()
{
    bool result = false;

    if (connection == null)
    {
        //Checking if all required parameters for connection are having values.
        if (!string.IsNullOrEmpty(databaseName) && !string.IsNullOrEmpty(Username) && !string.IsNullOrEmpty(Password))
        {
            //Creating new connection.
            string connstring = string.Format("Server=.;database={0};UID={1};password={2}", databaseName, Username, Password);
            connection = new SqlConnection(connstring);
        }
    }

    //Check if connection is not null.
    if (connection != null)
    {
        //Check if current status of the connection is open. 
        //If Yes set result = true
        if (connection.State == ConnectionState.Open)
        {
            result = true;
        }
        else
        {
            // If connection not already opened, open it.
            connection.Open();
            result = true;
        }
    }

    return result;
}

And Close method should check for connection object and it's state before closing it.

public void Close()
{
    if (connection != null && connection.State != ConnectionState.Closed)
    {
        connection.Close();
    }
}