title: Where am I supposed to call the method connection.Open() using C#?
body: <p>Hi I am trying to get data from a database shown in a textbox. Doing this I have created three classes: Dal, Controller and TestForm. The thing is that I dont really know where to open the connection nor where to close it. This is what I've done.</p>
  
  <p>In the class Dal I have:</p>
  
  <pre><code>  using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
      using System.Data.SqlClient;
      using System.Data.Odbc;
  
      namespace TestingDatabaseConnection
      {
          class Dal
          {
      private SqlConnection connection = new SqlConnection();
  
          public SqlConnection GetConnection()
          {
  
              if (connection == null)
              {
                 connection.ConnectionString = "Server=Mnemonics-DAT;Database=mem;    Integrated Security = true;";
              }
  
              return connection;
          }
  
       public SqlDataReader GetData() 
          {
              SqlDataReader sqlReads = null;
  
                  SqlCommand sqlCommand = new SqlCommand("select * from table_name", GetConnection());
                   sqlReads = sqlCommand.ExecuteReader();
  
              return sqlReads;
  
  
          }
      }
      }
  </code></pre>
  
  <p>In the class Controller I have: </p>
  
  <pre><code> using System;
      using System.Collections.Generic;
      using System.Data.SqlClient;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
  
      namespace TestingDatabaseConnection
      {
      class Controller
      {
          private Dal dal = new Dal();
  
          public SqlDataReader GetData()
          {
              return dal.GetData();
          }
      }
      }
  </code></pre>
  
  <p>and finally in the form:</p>
  
  <pre><code>public partial class TestForm : Form
  {
  Controller controll; 
  
  public TestForm()
  {
      InitializeComponent();
      controll = new Controller();
  }
  
  private void showBtn_Click(object sender, EventArgs e)
  {
      try
      {
          SqlDataReader sqlReader = controll.GetData();
          while (sqlReader.Read())
          {
              infTxtBox.Text = sqlReader.ToString();
          }
      }
      catch (Exception e1)
      {
          MessageBox.Show("Something is wrong: " + e1);
      }
  
  }
  
  }
  </code></pre>
  
  <p>The message I get says "Something is wrong: ExecuteReader requires an open and available Connection. The connection's current state is closed.</p>
  
  <hr>
  
  <p>What I have tried to do to solve the problem(in the class Dal):</p>
  
  <p>Making a property that gets the connection value like this: </p>
  
  <pre><code>public SqlConnection Connect
      {
          get
          {
              return connection;
          }
      }
  </code></pre>
  
  <p>And then using it in the method GetData():</p>
  
  <pre><code> public SqlDataReader GetData() 
          {
              SqlDataReader sqlReads = null;
              try
              {
                   //I call the method Open() here
                  Connect.Open();
                  SqlCommand sqlCommand = new SqlCommand("select * from table_name", GetConnection());
                  sqlReads = sqlCommand.ExecuteReader();
              }
              catch (Exception e)
              {
                  Console.WriteLine("Error is: " + e);
              }
              finally
              {
                  //and close it here
                  Connect.Close();
              }
              return sqlReads;
  
  
          }
  </code></pre>
  
  <p>The error message I get now says: "Something is wrong: Invalid attempt to call Read when reader is closed"
  While referring to the class TestForm.</p>