CODEWITHSUNDEEP
c#databaseconnection
Stonep123
Stonep123

Reputation: 625

Using database connection in separate form

I have my main form which has a datagrid view connected to a database. Then I have a button that opens a separate form and I've got a few buttons etc on that secondary form.

I need to query the database from the secondary form but I'm not sure how to do that without creating a whole new connection, which I don't think I need since the program is already connected to the database. I'm just not sure how to reference the oleDB connection I made in that first form (I didn't code it in, I used the little arrow on the datagridview to connect it to the database using visual studio)

Now instead of creating that new connection, how do I reference the first connection made in the primary form?

Here is my code:

//parameterized update query
string updateCommandString = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE ID = @id";
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb"))
{

    using (OleDbCommand updateCommand = new OleDbCommand())
    {
        OleDbTransaction transaction = null;
        updateCommand.Connection = conn;
        updateCommand.Transaction = transaction;    
        updateCommand.CommandText = updateCommandString;
        updateCommand.CommandType = CommandType.Text;
        updateCommand.Parameters.AddWithValue("@checkedDate", this.dateTimePicker1.Value.ToShortDateString());
        updateCommand.Parameters.AddWithValue("@id", row.roomID);
        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();
            updateCommand.Transaction = transaction;
            updateCommand.ExecuteNonQuery();
            transaction.Commit();
            conn.Close();
            conn.Dispose();
        }
        catch(OleDbException ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }
}

Upvotes: 1

Views: 1009

Answers (2)

Yatrix
Yatrix

Reputation: 13775

From a design standpoint, you should consider making a data access layer for your forms to utilize. You can create methods to retrieve those Db results for you so you consolidate that code and separate it from your form functionality. It may be just a small project, but it's good practice and if it's a project you want to grow, you'll want it laid out to be extendable.

Something like

class SomethingDA {
   static DataTable GetMyStuff(your params) {
     // establish connection, get your results
   }
}

You can then call SomethingDa.GetMyStuff() to get what you need.

Upvotes: 1

Spevy
Spevy

Reputation: 1325

If you are Using the "Using" statement, your connection is closed after this code is run. You don't in fact need the conn.Close() and conn.Dispose() statements. Using does that for you.

Your best bet is to open up the connection again. It is generally a good practice to open and close connections as quickly as possible, although likely less important if your Access DB local. This generally does not impact performance too much as the OLE DB driver behind the scene will pool the connection and keep it open for a period of time.

Upvotes: 0

Related Questions