Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you're just manually setting up a db connection for literally just one query, there isn't really an easier way to do it. I would suggest that the code be cleaned up a little, though. As it is written in your example, <code>MyConn</code> will remain open if there is an error during your query. That can be fixed by switching it up and putting it in a <code>using</code>:</p> <pre><code>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 to ask string query = "SELECT * FROM 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; } </code></pre> <p>If you wanted to switch this up and make it easier for you if you need to add more queries in the future, though, then I'd suggest moving to a <strong>3-tier architecture</strong> with a Business Logic Layer (BLL) and a Data Access Layer (DAL). In this, you could have a base DAL class that defines some standard methods for things like <code>Fill()</code>, <code>ExecuteScalar()</code>, <code>ExecuteNonQuery()</code>, etc. </p> <p>You can find countless examples of this kind of set up across the internet, but I've put together a fairly simple example:</p> <p>Here's a rough sketch of a possible DAL base class. Notice how it manages the actual connections to the database based on a DB command that is passed in:</p> <pre><code>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 any other methods you need here } </code></pre> <p>And then you can make a table-specific DAL class for your Student table to handle the queries and commands. This keeps your query logic all in one place:</p> <pre><code>public class StudentDAL : DALBase { public DataSet GetAllStudents() { DataSet ds = null; //query to ask string query = "SELECT * FROM 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 WHERE StudentID = @StudentID"; using (OleDbCommand command = new OleDbCommand(query)) { command.Parameters.AddWithValue("@Name", name); command.Parameters.AddWithValue("@StudentID", studentID); ExecuteNonQuery(command); } } } </code></pre> <p>Then, you would make a table-specific BLL class to handle any intermediary logic that needs to happen between the DAL and the class that needs the information from your database:</p> <pre><code>public class StudentBLL { private _studentDAL = new StudentDAL(); public DataSet GetAllStudents() { return _studentDAL.GetAllStudents(); } public void UpdateStudentName(Student student) { _studentDAL.UpdateStudentName(student.StudentID, student.Name); } } </code></pre> <p>In this particular case, the methods pretty much just make calls to the corresponding DAL. If you needed to do any other logic, though (type conversion, some kind of formula, etc.), this is where it would happen. I guess my hypothetical <code>UpdateStudentName</code> method, is a minor example of this. If you look at it, you'll see that it just takes in a <code>Student</code> object and splits it out to send to the DAL. This prevents the UI layer (or other calling class) from needing to worry about this.</p> <p>Finally, you would make a call to your database <em>through</em> a BLL object from the class that needs the information:</p> <pre><code>public class SomeOtherClass { DataGridView dataGridView1; public void PopulateDataGridView1() { dataGridView1.DataSource = new StudentBLL().GetAllStudents(); } } </code></pre> <p>Now, this may not fit exactly into your needs, and I'm sure people could argue about this sort of approach, but this is meant to be more of an example of how you could go about streamlining your data access to make it far more maintainable and scalable down the road.</p>
    singulars
    1. This table or related slice is empty.
    1. POadding and quering the database in visual studio 2012
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USvalverij
    1. USvalverij
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload