HELP! Question about C#

I am creating a simple DBA Application but I am a newbie having trouble determining how to know if the connection is open from a different method. This is part of my code can anyone help me in how to

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Connect2
{
class ConnectInfo
{
public string DatabaseName { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public string SourceIP { get; set; }
public string Query { get; set; }

public ConnectInfo(string dbaname, string username, string password, string sourceip, string query)
{
DatabaseName = dbaname;
Username = username;
Password = password;
SourceIP = sourceip;
Query = query;
}


public void Connect()
{
string connectionString;
connectionString = "provider=MSDAORA;data source={2};user id={1};password={0}" +DatabaseName +Password +SourceIP;
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
myOleDbConnection.Open();

}

public void ExecuteQuery()
{
// How can i get that myOledbConnection to this method from connect method?



SqlCommand command = new SqlCommand(query, myOleDbConnection);
command.ExecuteNonQuery();

}



}
}

This is being created in a windows form I want to be able to press connect on a button and then press execute on another button.

move this.

string connectionString;

after this.

public string Query { get; set; }

this how i use the oledb[access]
hope this helps you

Code:

---

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace DV_Messenger
{
    class OleDBConnection
    {
        OleDbConnection con;
        String conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=T:\AST\DV\database\DV.mdb;Persist Security Info=False";
        OleDbDataAdapter dataAdapter;
        OleDbCommand cmd;

        public void startConnection()
        {
            try
            {
                con = new OleDbConnection(conString);
                if (con.State != ConnectionState.Open) con.Open();
            }
            catch (Exception ex) { }
        }
        public void doQuery(String Query)
        {
            startConnection();
            cmd = new OleDbCommand(Query, con);
            cmd.ExecuteNonQuery();
        }
        public DataTable getData(String Query)
        {
            startConnection();
            DataTable dt = new DataTable();
            cmd = new OleDbCommand(Query, con);
            dataAdapter = new OleDbDataAdapter(cmd);
            dataAdapter.Fill(dt);
            return dt;
        }
    }
}

---

Code:

---


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
 
namespace Connect2
{
class ConnectInfo
{
public string DatabaseName { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public string SourceIP { get; set; }
public string Query { get; set; }
private OleDbConnection  _myOleDbConnection = null;
 
public ConnectInfo(string dbaname, string username, string password, string sourceip, string query)
{
DatabaseName = dbaname;
Username = username;
Password = password;
SourceIP = sourceip;
Query = query;
}
 
 
public bool Connect()
{
string connectionString;
try{
connectionString = "provider=MSDAORA;data source={2};user id={1};password={0}" +DatabaseName +Password +SourceIP;
_myOleDbConnection = new OleDbConnection(connectionString);
_myOleDbConnection.Open();
return true;
}catch{
return false;
}
}
 
public void ExecuteQuery()
{
// How can i get that myOledbConnection to this method from connect method?
// you simple access the  _myOleDbConnection Field here
 
if ( Connect()){
SqlCommand command = new SqlCommand(query, _myOleDbConnection);
command.ExecuteNonQuery();
 }
}
 
}
}

---

There are lots of ways doing it best would be to use a using command.

For example look here

Code:

---

public DataTable GetAllCustomers() {
  string connString = Properties.Resources.ConnStringPart + Properties.Settings.Default.servDatPfad;
 
  using (OleDbConnection connection = new OleDbConnection(connString)) {
      DataTable table = null;
      string sqlQuery = "SELECT * FROM KundenTab";
      OleDbDataAdapter prodAdapter = null;
      try {
          using (prodAdapter = new OleDbDataAdapter(sqlQuery, connection)) {
              table = new DataTable("Customers");// Die Struktur erstellen
              prodAdapter.Fill(table); // füllen
          }
      } catch (OleDbException e) {
          table.Dispose();
          table = null;
          throw e;
      }
      Console.WriteLine("CustomersTable Filled");
      return table;
  }
}

---

This additional shows using a DataAdapter, but how to create and use connection could be used the same way in your project.

The database providers in C# are smart and actually cache the connections to the underlying database, so even if you open and close a connection, the provider maintains and manages a pool of connections underneath the covers.

What this means is you don't need to open one connection and keep that connection open (nor do you need to incur the problems that arise with having to test the 'open' connection before using it).

Follow the 'using' block pattern that JonnyPoet posted and let the provider cache the connections.