using System; 
  using System.Collections.Generic; 
  using System.ComponentModel; 
  using System.Data; 
  using System.Drawing; 
  using System.Text; 
  using System.Windows.Forms; 
   
  using System.Data.SqlClient; 
  using System.Collections; 
   
  namespace DataAccessLayerDeneme 
  { 
      public partial class Form1 : Form 
      { 
          public class Connection : IDisposable 
          { 
              SqlConnection conn = null; 
   
              private string mConstr;//ConnectionString 
   
              public string Constr 
              { 
                  get { return mConstr; } 
                  set { mConstr = value; } 
              }          
  	 
              public Connection() // Default Constructor 
              {  
              } 
   
              public Connection(string constr) 
              { 
                  conn = new SqlConnection(constr); 
                  conn.Open();                 
              } 
   
              public void ConnectToDB() // Bağlantı Açılıyor 
              { 
                  if (conn == null) 
                  { 
                      conn = new SqlConnection(mConstr); 
                      conn.Open(); 
                  } 
                                   
              }  
   
              public void CloseConnection() // Bağlantı Kapatılıyor 
              { 
                  if (conn != null)                 
                      conn.Close();                 
              } 
   
              public void Dispose() // Kaynaklar Geri Veriliyor 
              { 
                  if (conn != null) 
                  { 
                      conn.Dispose(); 
                      conn = null; 
                  } 
              } 
               
              public SqlParameter SetParameter(string parName, object value) 
              { 
                  SqlParameter pars = new SqlParameter(); 
                  pars.ParameterName = parName; 
                  pars.Value = value; 
   
                  return pars; 
              } 
   
              // StoredProcedure Kullanana Komut İşleme Metodu 
              public SqlCommand ExecuteNonQuery(string spName, SqlParameter pars) 
              { 
                  SqlCommand cmd = new SqlCommand(spName,conn); 
                  cmd.CommandType = CommandType.StoredProcedure;         
   
                  if (conn.State == ConnectionState.Closed) 
                      conn.Open(); 
   
                  cmd.ExecuteNonQuery(); 
   
                  conn.Close(); 
   
                  return cmd; 
              } 
   
              // StoredProcedure Kullanana Komut İşleme Metodu 
              public object ExecuteScalar(string spName, SqlParameter[] pars) 
              { 
                  object retVal = null; 
   
                  SqlCommand cmd = new SqlCommand(spName,conn); 
                  cmd.CommandType = CommandType.StoredProcedure; 
                   
                  if (conn.State == ConnectionState.Closed) 
                      conn.Open(); 
   
                  retVal = cmd.ExecuteScalar(); 
   
                  conn.Close(); 
   
                  return retVal; 
              } 
   
              // Sql Kodu İle Komut İşleme Metodu 
              public SqlCommand ExecuteNonQuery(string komut) 
              { 
                  SqlCommand cmd = new SqlCommand(komut,conn); 
   
                  if (conn.State == ConnectionState.Closed) 
                      conn.Open(); 
   
                  cmd.ExecuteNonQuery(); 
   
                  conn.Close(); 
   
                  return cmd; 
              } 
   
              // Sql Kodu İle Komut İşleme Metodu 
              public object ExecuteScalar(string komut) 
              { 
                  object retVal = null; 
   
                  SqlCommand cmd = new SqlCommand(komut,conn); 
                   
                  if (conn.State == ConnectionState.Closed) 
                      conn.Open(); 
   
                  retVal = cmd.ExecuteScalar(); 
   
                  conn.Close(); 
   
                  return retVal;                 
              } 
   
              // Daha Önceden Olusturulan Bir Command Nesnesi İle DataTable Doldurma 
              public DataTable FillTable(SqlCommand cmd) 
              { 
                  SqlDataAdapter dap = new SqlDataAdapter(cmd); 
                  DataTable dt = new DataTable(); 
                  dap.Fill(dt); 
   
                  return dt; 
              } 
   
              // Sadece Komut Verilerek DataTable Doldurma 
              public DataTable FillTable(string komut) 
              { 
                  SqlCommand cmd = ExecuteNonQuery(komut); 
   
                  SqlDataAdapter dap = new SqlDataAdapter(cmd); 
                  DataTable dt = new DataTable(); 
                  dap.Fill(dt); 
   
                  return dt; 
              } 
          } 
   
          public Form1() 
          { 
              InitializeComponent(); 
          } 
   
          private void Form1_Load(object sender, EventArgs e) 
          { 
              // Bu Tip Connection Türetmede otomatik olarak conn.Open() Yapılıyor 
              // Ayrıca bizim ConnectToDB() Metodunu kullanmamız Gerekmiyor. 
              Connection bag = new Connection("Data Source =.; Initial Catalog = Deneme; Integrated Security = SSPI");            
   
              SqlCommand cmd = bag.ExecuteNonQuery("select * from tbl_Musteriler"); 
              dataGridView1.DataSource = bag.FillTable(cmd); 
                                   
              bag.CloseConnection();                                                
          } 
      } 
  }