How do I: Pass SQLConnection from MDIParent to MDIChild.

It is necessary to close connections, if you do not, you WILL eventually exhaust the connection pool if you intend it to be a multi user system and at a time you do NOT want it to happen, Sam has OBVIOUSLY NEVER encountered this problem possibly because he has NEVER written complex systems that deal with these issues.  try leaving connections open and see what happens, oops an unhandled exception in blah blah this could be due to the connection pool being exhusted, increase the size of connection pool (or words to that effect)

You MUST be diligent in  this unless for some obscure reason that you need to manage connection outside of SQL Servers default pools.


using System;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

//example only should not be in sqlCon class.
public class app_user
{
    public string name;
    public string pwd;
    public string whatever;
    public void app_user()
    {
    }
}

public class sqlCon
    {
    public SqlConnection con = null;
    public SqlDataReader reader = null;
    public SqlCommand cmd = null;
    public SqlParameter Param = null;
    public sqlCon()
        {
        cmd = new SqlCommand();
        Param = new SqlParameter();
        }
    //-----------------------------------------------------------------
    public void addParam(SqlParameter param)
        {
        }
    //-----------------------------------------------------------------
    public void connect()
        {
        System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        con = new SqlConnection((string)asr.GetValue("TheConnectionString", typeof(string)));
        con.Open();
        //con.GetSchema();  this is used if you want to dynamically load grid view cells based on data type
        }
    //-----------------------------------------------------------------
    private static SqlDataAdapter DataAdapter(String sqlString)
    {
        // Assuming all the default settings, create a SqlDataAdapter working
        // current database in SQL Server.
        System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();
        return new SqlDataAdapter(sqlString, (string)asr.GetValue("TheConnectionString", typeof(string)));

// I do not use one but you can

    }
    //-----------------------------------------------------------------
    public string get(string field)
        {
        return reader[field].ToString();
        }
    //-----------------------------------------------------------------
    public bool getBoolean(string field)
        {
    return ((bool)(int.Parse(reader[field].ToString()) == 1 ? true : false));
        }
    //-----------------------------------------------------------------
    public void ExecuteReader()
        {
        reader = cmd.ExecuteReader();
        }
    //-----------------------------------------------------------------
    public SqlDataReader ExecReader()
        {
        return (cmd.ExecuteReader());
        }
    ///

    ///
    ///

    //-----------------------------------------------------------------
    public void write()
        {
        cmd.ExecuteNonQuery();
        }
    //-----------------------------------------------------------------
    public bool read()
        {
        return reader.Read();
        }
    //-----------------------------------------------------------------
    public void command(String s)
        {
        cmd.Connection = con;
        cmd.CommandText = s;
        }
    //-----------------------------------------------------------------
    public void terminate()
        {
        try
            {
            if (reader != null)
                {
                reader.Close();
                reader.Dispose();
                }
            }
        catch (Exception err)
            {
            }

        try
            {
            if (cmd != null)
                {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                }
            }
        catch (Exception err)
            {
            }
        try
            {
            if (con != null)
                {
                con.Close();
                con.Dispose();
                }
            }
        catch (Exception err)
            {
            }
    }
    //-----------------------------------------------------------------
    public void beginTrans()
        {
        cmd.Transaction = con.BeginTransaction();
        }
    //-----------------------------------------------------------------
    public void commitTrans()
        {
        cmd.Transaction.Commit();
        }
    //-----------------------------------------------------------------
    public void rollbackTrans()
        {
        try
            {
            if (cmd != null)
                cmd.Transaction.Rollback();
            }
        catch (Exception err)
            {
            }
        }
    //-----------------------------------------------------------------
    public string getFieldValue(string s, string field)
        {
        //usage con.getFieldValue("SELECT * FROM user WHERE name = 'bob'", "emailAddress")
        //this example usage will return the email address from database table.

        string fieldValue = "";

        sqlCon con = new sqlCon();
        try
            {
            con.connect();
            con.command(s);
            con.ExecuteReader();
            if (con.read())
                {
                fieldValue = con.get(field).ToString();
                }
            }
        catch (Exception err)
            {
            }
        finally
            {
            con.terminate();
            }
        return (fieldValue);

    }
  //-----------------------------------------------------------------
  public bool getFieldBool(string s, string field)
    {
    bool fieldValue = false;
    sqlCon con = new sqlCon();
    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      if (con.read())
        {
        fieldValue = con.getBoolean(field);
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    return (fieldValue);

    }
    //-----------------------------------------------------------------
    public bool findDuplicate(string s)
        {
        //usage con.findDuplicate("SELECT * FROM user WHERE name = 'bob" AND email = '[email protected]'")
        //build the sql string any way you want.

        bool found = false;

        sqlCon con = new sqlCon();

        try
            {
            con.connect();
            con.command(s);
            con.ExecuteReader();
            if (con.read())
                {
                found = true;
                }
            }
        catch (Exception err)
            {
            }
        finally
            {
            con.terminate();
            }
        return (found);
    }
    //--------------------------------------------------------------
    public bool getDuplicateValue(string table, string field, string value)
    {
        bool found = false;
        string s = "SELECT * FROM " + table + " WHERE " + field + " = @value";
        sqlCon con = new sqlCon();
       
        //how to add parameters
        con.cmd.Parameters.AddWithValue("value", value);
       
        try
            {
            con.connect();
            con.command(s);
            con.ExecuteReader();
            if (con.read())
                {
                found = true;
                }
            }
        catch (Exception err)
            {
            }
        finally
            {
            con.terminate();
            }
        return (found);

    }
    //-----------------------------------------------------------------
    public bool execSQL(string s)
        {
        bool success = true;
        sqlCon con = new sqlCon();
        try
            {
            con.connect();
            con.command(s);
            con.beginTrans();
            con.write();
            con.commitTrans();
            }
        catch (Exception err)
            {
            con.rollbackTrans();
            success = false;
            }
        finally
            {
            con.terminate();
            }
        return (success);

    }
  //-----------------------------------------------------------------
  public bool execSQL(sqlCon con, string s)
    {
    //an override with an sqlCon being sent rather than instantiating a new connection.
    bool success = true;
    try
      {
      con.connect();
      con.command(s);
      con.beginTrans();
      con.write();
      con.commitTrans();
      }
    catch (Exception err)
      {
      con.rollbackTrans();
      success = false;
      }
    finally
      {
      con.terminate();
      }
    return (success);
    }
  //-----------------------------------------------------------------
    public int getCount(string s)
        {
        // usage getCount("SELECT COUNT(whatever) as count FROM table [where]")
        int count = 0;
        sqlCon con = new sqlCon();
        try
            {
            con.connect();
            con.command(s);
            con.ExecuteReader();
            if (con.read())
                count = int.Parse( con.get("count"));
            }
        catch (Exception err)
            {
            count = 0;
            }
        finally
            {
            con.terminate();
            }
        return (count);

        }
  //-----------------------------------------------------------------
  public void loadDropDown(ComboBox cb, string s, string fieldName)
    {
    sqlCon con = new sqlCon();
    int count = 0;
    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      while (con.read())
        {
        cb.Items.Add(con.get(fieldName));
        }
      }
    catch (Exception err)
      {
      //do what ever is needed
      }
    finally
      {
      con.terminate();
      }
     return(ids);
    }
  //-----------------------------------------------------------------
  public void loadCheckList(CheckBoxList l, string s, string fieldName)
    {
    sqlCon con = new sqlCon();
    l.Items.Clear();
    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      while (con.read())
        {
        l.Items.Add(con.get("fieldName"));
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    }
  //-----------------------------------------------------------------
  public void getUser(string s, app_user user)
    {
    //usage con.getUser("SELECT * FROM user WHERE user_id = id", user); 
    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      if(con.read())
        {
            user.name = get("name");
            user.whatever = get("whatever");
          //any other field you want.
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    //at this point the app_user class is filled with all necessary details and will be available for you to use on return to calling point
    }
  //-----------------------------------------------------------------
  public bool userExists(string s)
    {
  //usage con.userExists("SELECT * FROM user WHERE name = 'whatever'");
    bool userExists = false;
    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      if(con.read())  //if it reads one, the user exists, however you would use more than one field to determine this.
        {
        userExists = true;
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    return(userExists);
    }
  //-----------------------------------------------------------------
  /*  I have left this here simply to demonstrate that these methods can be used in forms and web scenarios.
   *  make sure to add web referances if used for web.
   * 
    public string loadMainMenu(Page p, string s)
    {
    session headings = (session)p.Session["headings"];
    string html = "", mnuName;
    string id = "";
    long count=0;
    sqlCon con = new sqlCon();

    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      while (con.read())
        {
        count=0;
        mnuName = con.get("heading");
        id = con.get("id");
        html += "";
        //html += loadSubMainMenu(p, "SELECT * FROM menuGroup WHERE headingId = " + id + " ORDER BY groupHeading", id);
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    return (html);
    }
  //-----------------------------------------------------------------
  public string loadSubMainMenu(Page p, string s, string hid)
    {
    session headings = (session)p.Session["headings"];
    string html = "", mnuName;
    string id = "";
    sqlCon con = new sqlCon();

    try
      {
      con.connect();
      con.command(s);
      con.ExecuteReader();
      while (con.read())
        {
        mnuName = " > " + con.get("groupHeading");
        id = con.get("id");
        html += "";
        }
      }
    catch (Exception err)
      {
      }
    finally
      {
      con.terminate();
      }
    return (html);
    }
   */
   }