sql - How do I reduce number of database connection in my project in c#? -

i new c# , using windows forms.

i building project contains 25 forms , 25 user controls , each 1 of these forms , user controls uses sql database read data database ( note using connection string inside app.config file).

as shown in code, put sql query inside 50 form/user control constructors read data (such controls text , other things) database;

my program works fine no issue imagine when run program 25 forms , 25 user controls query database "at same time" , makes me feel doing wrong , might has side effect later in program.

i not know if right way put query inside constructors (for example buttons text query)? feel 25 forms , 25 user controls attack sql database @ same time when run there way reduce number of connections database? please guide me. thank you

public partial class submenu1 : usercontrol {     sqlconnection myconnection = new sqlconnection(configurationmanager.connectionstrings["myconnectionstring"].connectionstring);     sqlcommand mycommand = new sqlcommand();     datatable datatable = new datatable();     sqldataadapter sql_data_adapter = new sqldataadapter();       public submenu1()     {        initializecomponent();            datatable.rows.clear();         datatable.columns.clear();          myconnection.open();         mycommand.commandtext = "select * table21 ";         mycommand.connection = myconnection;         sql_data_adapter.selectcommand = mycommand;         sql_data_adapter.fill(datatable);           myconnection.close();       }      public void myfunction()     {         //  stuff datatable ...     }      } 

you have lot of problem code. first don't use global connection, there connection pool.

connection pooling reduces number of times new connections must opened. pooler maintains ownership of physical connection. manages connections keeping alive set of active connections each given connection configuration. whenever user calls open on connection, pooler looks available connection in pool. if pooled connection available, returns caller instead of opening new connection. when application calls close on connection, pooler returns pooled set of active connections instead of closing it. once connection returned pool, ready reused on next open call.

also should use connection in using or in try/catch/finally block. needed because if exception happen connection never closed , lead exception when try reuse or never return connection pool.

again sqldataadapter should wrapped in using block, need close adapter after finishing work it. need call dispose, sqladapter release resources used component. rule every idisposable object. if don't dispose it, dispose happen on gc finalizer, when executed decided gc. not disposing object can have high cost. here article in msdn idisposable

i advise create separate data access layer, made simple 1 in question show design problems: checking user name or user email exists

if don't want separate layer data access code should this:

public partial class submenu1 : usercontrol {       public submenu1()      {          initializecomponent();      }       public void myfunction()     {          datatable datatable = new datatable();          using(sqlconnection myconnection = new sqlconnection(configurationmanager.connectionstrings["myconnectionstring"].connectionstring))          using(sqldataadapter sqldataadapter = new sqldataadapter())          {               myconnection.open();               sqlcommand mycommand = new sqlcommand();                mycommand.commandtext = "select * table21";               mycommand.connection = myconnection;                sqldataadapter.selectcommand = mycommand;               sqldataadapter.fill(datatable);          }           if(datatable.rows.count > 0)          {               //do stuff ....          }       } }