Program Design - Master DataSet/Adapter for program?
-
Hi All! I am new to using the .NET framework database objects and have a question about how to design my program using Adapter/DataTable queries. My Database...My database consists of about 10 different tables. No one table is indepedent. All have at least one relationship with another table. My Program...The program needs to be designed such, that when the user clicks "OK" on whichever dialog, the program will commit the new data to the database. Here is the jist of what I'm curious about...What is best design for the interaction between my OleDbDataAdapter and the DataSet or DataTables? Do you think these objects should be created with each form? When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk and not an In-memory object. Currently, on startup my program reads each table into a DataSet object. For basic queries involving a single table this works fine (using the Select method of DataTable). But anything more complex I now need to create a new DataAdapter and DbCommand objects. Would it be reasonable to have one OleDbConnection object for application. Then mutiple OleDbAdapter objects for each form. I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data. To illustrate my startup code is below. Thanks for your help!!!
m_dsPTData = New DataSet m_dbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilename) m_dbConnection.Open() m_dbDataAdapter = New OleDbDataAdapter() m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Congregations", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "Congregations") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM HomeTalks", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "HomeTalks") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakers", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakers") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakerOutlines", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakerOutlines") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Outlines", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "Outlines") -
Hi All! I am new to using the .NET framework database objects and have a question about how to design my program using Adapter/DataTable queries. My Database...My database consists of about 10 different tables. No one table is indepedent. All have at least one relationship with another table. My Program...The program needs to be designed such, that when the user clicks "OK" on whichever dialog, the program will commit the new data to the database. Here is the jist of what I'm curious about...What is best design for the interaction between my OleDbDataAdapter and the DataSet or DataTables? Do you think these objects should be created with each form? When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk and not an In-memory object. Currently, on startup my program reads each table into a DataSet object. For basic queries involving a single table this works fine (using the Select method of DataTable). But anything more complex I now need to create a new DataAdapter and DbCommand objects. Would it be reasonable to have one OleDbConnection object for application. Then mutiple OleDbAdapter objects for each form. I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data. To illustrate my startup code is below. Thanks for your help!!!
m_dsPTData = New DataSet m_dbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilename) m_dbConnection.Open() m_dbDataAdapter = New OleDbDataAdapter() m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Congregations", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "Congregations") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM HomeTalks", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "HomeTalks") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakers", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakers") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakerOutlines", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakerOutlines") m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Outlines", m_dbConnection) m_dbDataAdapter.Fill(m_dsPTData, "Outlines")I think your post would have been better off in Design and Architecture forum, but anyway.
David Hovey wrote:
when the user clicks "OK" on whichever dialog, the program will commit the new data to the database
Just a note: Take care that database operations are done transactionally and no user interference is allowed while transaction is running.
David Hovey wrote:
Do you think these objects should be created with each form
Forms and data (and logic) should be separated so that you can modify each (almost) independently. Get to know for example MVC (Model-View-Controller) concept.
David Hovey wrote:
When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk
I don't see any reason why you should store the results to a disk. You use joins in the SQL statement.
David Hovey wrote:
But anything more complex I now need to create a new DataAdapter and DbCommand objects
That would be logical in many cases, although you can reuse the command if you change the command text.
David Hovey wrote:
Would it be reasonable to have one OleDbConnection object for application
You should get and open the connection to the database when you need to operate with it. After executing the statements, you close the connection. Also use connection pooling.
David Hovey wrote:
Then mutiple OleDbAdapter objects for each form
Again, separate forms and database operations. For example if you someday want to run the logic on a middle-tier and only the UI on client (or web page) it greately helps you if you have a good and logical layering in your code.
David Hovey wrote:
I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.
Same as previous, but note what I wrote in the beginning about transactions and user interference. Hope this helps you forward, Mika
The need to optimize rises from a bad design.My articles[
-
I think your post would have been better off in Design and Architecture forum, but anyway.
David Hovey wrote:
when the user clicks "OK" on whichever dialog, the program will commit the new data to the database
Just a note: Take care that database operations are done transactionally and no user interference is allowed while transaction is running.
David Hovey wrote:
Do you think these objects should be created with each form
Forms and data (and logic) should be separated so that you can modify each (almost) independently. Get to know for example MVC (Model-View-Controller) concept.
David Hovey wrote:
When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk
I don't see any reason why you should store the results to a disk. You use joins in the SQL statement.
David Hovey wrote:
But anything more complex I now need to create a new DataAdapter and DbCommand objects
That would be logical in many cases, although you can reuse the command if you change the command text.
David Hovey wrote:
Would it be reasonable to have one OleDbConnection object for application
You should get and open the connection to the database when you need to operate with it. After executing the statements, you close the connection. Also use connection pooling.
David Hovey wrote:
Then mutiple OleDbAdapter objects for each form
Again, separate forms and database operations. For example if you someday want to run the logic on a middle-tier and only the UI on client (or web page) it greately helps you if you have a good and logical layering in your code.
David Hovey wrote:
I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.
Same as previous, but note what I wrote in the beginning about transactions and user interference. Hope this helps you forward, Mika
The need to optimize rises from a bad design.My articles[
It does very much. I found a PDF of excerpt from a book "Object-Orientated Development with ActionScript 2.0" (chapter 18) that talks about MVC. I can see the basic concepts of each part of MVC and I can see the benefits, but exactly how to relate it to my program is difficult. The example in the book is a Clock with digital and analog views. Rather simple. Can you help me a beginning question I have though? From my understanding each View should have one Controller. Does that mean the Controller is designed specifically for that particular view? :wtf: This sure does require alot more planning that the way I was going about it. It will take more time... Will need to study MVC further obviously.
-
It does very much. I found a PDF of excerpt from a book "Object-Orientated Development with ActionScript 2.0" (chapter 18) that talks about MVC. I can see the basic concepts of each part of MVC and I can see the benefits, but exactly how to relate it to my program is difficult. The example in the book is a Clock with digital and analog views. Rather simple. Can you help me a beginning question I have though? From my understanding each View should have one Controller. Does that mean the Controller is designed specifically for that particular view? :wtf: This sure does require alot more planning that the way I was going about it. It will take more time... Will need to study MVC further obviously.
David Hovey wrote:
how to relate it to my program is difficult
In the beginning it is, but don't worry. When you get over the 'new concepts' and 'ideology that changes everything' part you actually discover that it's no rocket sience. Just a new three (or four) letter abbreviation to a very simple and logical thing. I think there are several good articles about MVC at this site, have a look at them, for example: Building an MVP Framework for .NET. Part 1: The Basics of MVC and MVP[^]
David Hovey wrote:
each View should have one Controller
That's the basic idea but in some implementations one controller can have several views.
David Hovey wrote:
This sure does require alot more planning that the way I was going about it
It does, but you don't have to use Visual Studio or Rational Rose etc. In the simplest form it's basic OOP principles and what code do you write and where. So actually what I typically use is pencil and paper. Well, lots of paper because the first versions always go to the garbage pin :)
David Hovey wrote:
It will take more time
It will in this project, but you also learn a lot. That's an investment!
The need to optimize rises from a bad design.My articles[^]