c# - adding and quering the database in visual studio 2012 -

i have added database data source c# project in visual studio. now, query database. need manually set connection string database?

public void setsql() {     string connstr = "provider=microsoft.ace.oledb.12.0;data source=c:\\users\\jasper\\desktop\\autoreg\\autoreg.accdb;";      oledbconnection myconn = new oledbconnection(connstr);     myconn.open();      dataset ds = new dataset();      //query ask     string query = "select * student";      using (oledbcommand command = new oledbcommand(query, myconn))     {         using (oledbdataadapter adapter = new oledbdataadapter(command))         {             adapter.fill(ds);             datagridview1.datasource = ds;             myconn.close();         }     } } 

do need go through of process every time when need query database?

if you're manually setting db connection literally 1 query, there isn't easier way it. suggest code cleaned little, though. written in example, myconn remain open if there error during query. can fixed switching , putting in using:

public void setsql() {     string connstr = @"provider=microsoft.ace.oledb.12.0;data source=c:\users\jasper\desktop\autoreg\autoreg.accdb;";     dataset ds = new dataset();      //query ask     string query = "select * student";      using (oledbconnection myconn = new oledbconnection(connstr))     {         myconn.open();          using (oledbcommand command = new oledbcommand(query, myconn))         {             using (oledbdataadapter adapter = new oledbdataadapter(command))             {                 adapter.fill(ds);                                        }         }     }      datagridview1.datasource = ds;  } 

if wanted switch , make easier if need add more queries in future, though, i'd suggest moving 3-tier architecture business logic layer (bll) , data access layer (dal). in this, have base dal class defines standard methods things fill(), executescalar(), executenonquery(), etc.

you can find countless examples of kind of set across internet, i've put simple example:

here's rough sketch of possible dal base class. notice how manages actual connections database based on db command passed in:

public abstract class dalbase {     private const string connstr = @"provider=microsoft.ace.oledb.12.0;data source=c:\users\jasper\desktop\autoreg\autoreg.accdb;";      protected dataset fill(oledbcommand command)     {         dataset ds = new dataset();          using (oledbconnection myconn = new oledbconnection(connstr))         {             command.connection = myconn;             myconn.open();              using (oledbdataadapter adapter = new oledbdataadapter(command))             {                                                     adapter.fill(ds);                                                 }         }         return ds;     }      protected void executenonquery(oledbcommand command)     {         using (oledbconnection myconn = new oledbconnection(connstr))         {             command.connection =  myconn;                                         myconn.open();             command.executenonquery();         }     }      // put other methods need here      } 

and can make table-specific dal class student table handle queries , commands. keeps query logic in 1 place:

public class studentdal : dalbase {     public dataset getallstudents()     {         dataset ds = null;          //query ask         string query = "select * student";          using (oledbcommand command = new oledbcommand(query))         {             ds = fill(command);                     }             return ds;          }      public void updatestudentname(int studentid, string name)     {                     string query = "update student set name = @name studentid = @studentid";          using (oledbcommand command = new oledbcommand(query))         {             command.parameters.addwithvalue("@name", name);             command.parameters.addwithvalue("@studentid", studentid);              executenonquery(command);                     }             } } 

then, make table-specific bll class handle intermediary logic needs happen between dal , class needs information database:

public class studentbll {     private _studentdal = new studentdal();      public dataset getallstudents()     {                 return _studentdal.getallstudents();                         }      public void updatestudentname(student student)     {                 _studentdal.updatestudentname(student.studentid, student.name);                         } } 

in particular case, methods pretty make calls corresponding dal. if needed other logic, though (type conversion, kind of formula, etc.), happen. guess hypothetical updatestudentname method, minor example of this. if @ it, you'll see takes in student object , splits out send dal. prevents ui layer (or other calling class) needing worry this.

finally, make call database through bll object class needs information:

public class someotherclass {     datagridview datagridview1;              public void populatedatagridview1()     {         datagridview1.datasource = new studentbll().getallstudents();     }         } 

now, may not fit needs, , i'm sure people argue sort of approach, meant more of example of how go streamlining data access make far more maintainable , scalable down road.