Results 1 to 5 of 5

Thread: Need Database Connection Design Advice

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Need Database Connection Design Advice

    Here is the set up:

    I am using Visual Basic 2010, with a SQL Server Express database. The application is for a auto repair facility and will run on 3 to 5 PC's, accessing a shared database. The application will provide a stop-light type functionality per service bay providing status on the repairs in each bay, utilizing a timer and updating based on entry time into each bay, estimated time for the service and elapsed time.

    The database will be very simple, providing a single record per bay, which will be updated upon advisor inputs.

    Updates to the database will be based on the following:

    1) Input of a new service into a bay.
    2) Addition of time to a service already in the bay.
    3) Completion of a job and changing the status of the bay to available.

    The following database calls/updates will be needed:

    1) Read the status of each bay and update information on each PC.
    2) Update bay records with new inputs of either a new job or a change in estimated time for job.
    3) Update bay record with a completion of a job.

    I have the screen built and program logic built. I've successfully added a data source to the program which connects to the SQL server database on my server.

    My Request:

    I used to program many years ago but am a newbie to VB 2010. I need help with the database operations, which will need to include multiple users reading and updating records simultaneously. What database access methods/controls would you use for this application.

    Thanks in advance.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Need Database Connection Design Advice

    Hello,

    A good place to start is MSDN Data Walkthroughs (look at the windows form articles). In regards to multiple users, this is handled by the database. Start with the article, see if that helps and if not come back and ask specifically what you are having troubles with.

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Need Database Connection Design Advice

    Quote Originally Posted by kevininstructor View Post
    Hello,

    A good place to start is MSDN Data Walkthroughs (look at the windows form articles). In regards to multiple users, this is handled by the database. Start with the article, see if that helps and if not come back and ask specifically what you are having troubles with.
    Much of the content would be great if I was using a bound control or wanting a dataset that would be manipulated and then updated in total back to the database in total. However, here are my specific questions:

    1) What is the setup to query the database so that I can cycle through the results and update the data on the screens.
    --- Would this be a dataset?
    2) What is the setup to UPDATE sql statements for individual records.
    --- Would this be a TableAdapter or something else?

    Thanks
    Joe

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Need Database Connection Design Advice

    Quote Originally Posted by JosFKirby View Post
    Much of the content would be great if I was using a bound control or wanting a dataset that would be manipulated and then updated in total back to the database in total. However, here are my specific questions:

    1) What is the setup to query the database so that I can cycle through the results and update the data on the screens.
    --- Would this be a dataset?
    2) What is the setup to UPDATE sql statements for individual records.
    --- Would this be a TableAdapter or something else?

    Thanks
    Joe
    ADO.NET is the standard .NET data access technology. There are other technologies that can sit on top of that and provide extra features, but ADO.NET still does the grunt work under the hood.

    There are three main ways to retrieve data from a database using ADO.NET:

    1. Create a DbCommand and call ExecuteScalar to retrieve a single value. That will execute a query and return the first column from the first row of the result set.
    2. Create a DbCommand and call ExecuteReader to create a DbDataReader. That will allow you to read the result set of a query row by row and use it as you go.
    3. Create a DbDataAdapter and call Fill to populate a DataTable. That will populate the DataTable with the result set of a query, making it easy to make changes and save them back to the database.

    There are two main ways to save changes to a database using ADO.NET:

    1. Create a DbCommand and call ExecuteNonQuery. That will make changes directly to records in the database.
    2. Create a DbDataAdapter and call Update to save changes from a DataTable. That would usually be the same DbDataAdapter and DataTable from option 3 above.

    Follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for examples of these and other common ADO.NET scenarios.

    Note that you need to create a DbConnection to actually connect to the database. That is then associated with a DbCommand to execute SQL code over that connection. A DbDataAdapter is a way to group DbCommands representing the four CRUD operations. Its SelectCommand is executed when you call Fill and its InsertCommand, UpdateCommand and DeleteCommand are executed as needed when you call Update.

    When you create a typed DataSet using the Data Source Wizard, you're still using ADO.NET but the system extends and enhances the types you use for working specifically with your data. The type DataSet itself inherits the standard DataSet class and adds a property for each table. Each typed DataTable inherits the standard DataTable class and adds methods for working with data with the specific schema of the database table it corresponds to. Each typed DataRow inherits the standard DataRow class and adds a property for each column in the table. Each table adapter wraps up a DbDataAdapter, complete with commands and connection. The Fill method of the table adapter internally calls the Fill method of the wrapped DbDataAdapter and likewise for the Update methods. You can add additional queries and table adapters in the DataSet designer and that will generate additional methods that may call ExecuteScalar or the like.

    For more info on table adapters, check this out:

    http://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Need Database Connection Design Advice

    Quote Originally Posted by jmcilhinney View Post
    ADO.NET is the standard .NET data access technology. There are other technologies that can sit on top of that and provide extra features, but ADO.NET still does the grunt work under the hood.

    There are three main ways to retrieve data from a database using ADO.NET:

    1. Create a DbCommand and call ExecuteScalar to retrieve a single value. That will execute a query and return the first column from the first row of the result set.
    2. Create a DbCommand and call ExecuteReader to create a DbDataReader. That will allow you to read the result set of a query row by row and use it as you go.
    3. Create a DbDataAdapter and call Fill to populate a DataTable. That will populate the DataTable with the result set of a query, making it easy to make changes and save them back to the database.

    There are two main ways to save changes to a database using ADO.NET:

    1. Create a DbCommand and call ExecuteNonQuery. That will make changes directly to records in the database.
    2. Create a DbDataAdapter and call Update to save changes from a DataTable. That would usually be the same DbDataAdapter and DataTable from option 3 above.

    Follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for examples of these and other common ADO.NET scenarios.

    Note that you need to create a DbConnection to actually connect to the database. That is then associated with a DbCommand to execute SQL code over that connection. A DbDataAdapter is a way to group DbCommands representing the four CRUD operations. Its SelectCommand is executed when you call Fill and its InsertCommand, UpdateCommand and DeleteCommand are executed as needed when you call Update.

    When you create a typed DataSet using the Data Source Wizard, you're still using ADO.NET but the system extends and enhances the types you use for working specifically with your data. The type DataSet itself inherits the standard DataSet class and adds a property for each table. Each typed DataTable inherits the standard DataTable class and adds methods for working with data with the specific schema of the database table it corresponds to. Each typed DataRow inherits the standard DataRow class and adds a property for each column in the table. Each table adapter wraps up a DbDataAdapter, complete with commands and connection. The Fill method of the table adapter internally calls the Fill method of the wrapped DbDataAdapter and likewise for the Update methods. You can add additional queries and table adapters in the DataSet designer and that will generate additional methods that may call ExecuteScalar or the like.

    For more info on table adapters, check this out:

    http://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx

    Thank you so much for your input! This was exactly what I needed to get a handle on the overall approach. I've used a combination of SQLDataReader to read the data and Commands for updates. I had a syntax issue with UPDATING a date/time field into SQL which I was able to research and resolve and now can focus on enhancing the program logic of this performance management tool. Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width