none
Understanding Databasing in Visual Studio 2019 (come from access) RRS feed

  • Question

  • Hi All,

    Just wanted to ask a couple of questions to understand how it all works a little better?

    So. i can see there are two main ways to get data into objects/controls on forms.

    Method 1: use data config wizards to set connection to db file (now using MySQL) and generate bindingsources and tabeladapters

    Method 2; Construct entirely from code, declaring string connection, creating New datasets and tableadapters and managing Fill() etc.

    When i first starting playing, i used data config wizards.

    Then i took an online lesson and constructed a connection and a fill() entirely by code.

    However it seems as though, Using Method 1, its difficult to see whats going on because, at least with code, you can read what is happening. at the moment with method one, i'm not seeing any code that declares a connection string, or a dataset, they're just "there".
    That isn't really helping me to understand what is going on and how it all clicks together.

    But what about mix and match?

    Lets say i set up everything using wizards, the dataset/tableadapter are all there waiting to be used. by default the form_Load has a This.Tableadapter.fill(This.Dataset.DataTable)

    Now, i want to start playing with the tableadapter. its already been used to Fill in Form_Load. heres the tricky bit.

    Basically, i want this form to load up, and attempt to read the data inside the dbfile. (which is empty) I want the form to Check for data. if no data then.

    So my very little c# knowledge thought. Ok. for this i need to countRows. set countrows as 0 by default, and if countrows still = 0 after tableadapter.Fill() is called, then prompt to enter some details.

    The details the person will enter, will be their name and address, and will be used as default on every subsequent execution of the program, (with an option to change these details of course)

    This brings me back to previous problem. because the adapters/datasets are just there as opposed to being coded in, i'm struggling to work out how they're called and how to run additional functions through them. Please can someone tell me the best way forward? if i was attempt to code it, it would be like this, (baring in mind its in my dumb language and not a correct syntax)

    Any advice as to which method is best? code it all, or use VS wizards?

    I just dont like that in the VS wizards, you can't clearly see  on the code what is happening, because .displaymember is just set in the wizard, not visible in code, if that makes sense?



    Code:

    int countrows = 0;
    countrows = tableadapter.table.rows.count
    And then something like:
    Code:
    if countrows =< 0 Then
    Msgbox(No Default details are registered, please enter your details)
    But i'm assuming i'd need to attempt to use a Try Statement? Never done it before.

    im assuming its like, If /Else ? but instead its Try and Catch using {} to seperate right

    If anyone has any ideas, i'd really appreciate your time and advice.

    Thanks.

    Jack
    Friday, October 25, 2019 8:05 AM

Answers

  • Hello,

    It's best to steer clear of data wizards. Since strong typed classes are generated it can be a real problem updating them if a) a field type changes in the database table b) if fields are added or removed. The actual process is not apparent but once learned is easy to update the model but can be problematic when dealing with bound controls on a form.

    Other issues with TableAdapter, sharing data between forms, reusing models in other projects. Then there is also the need to fine tune "code" but that is not easy as you can not modify generated code which the wizard created without creating partial classes which are still connected to the generated code.

    Out of the two methods you learned about (and there are more), hand coding is better and when hand coding it should not be done directly in a form, instead read do updates, perform deletes and inserts in a class. Then there is the decision to use DataSet/DataTable, DataTable or classes to interact with databases.

    The next method is Entity Framework or Entity Framework Core. When reading about EF or EF Core there will be little to nothing on using them in Windows Forms but instead with web projects. 

    Taking one path for EF, you can create a database with table, add EF via wizards via "Code first with existing database" and classes are created for each table were in this case the code is easy to access. Downside is you are now in control of when table schema changes, dig into code and adjust.

    I have a code sample which has not been completed yet, there are two projects showing the basics with reading data, no edits, deletes or inserts as the code sample is a part one of an article I'm writing were part 2 will have edits, deletes, inserts. The samples may appear overkill for your initial step into working with data but it's good to see this as when a developer first starts out and sees just simple examples they get a sense of "easy" and later see it's not always easy.

    Then this code sample will introduces the basics of validating user input with EF. For instance, see the following code method for adding a new record were there are interactions with both a main table and reference tables.

    My recommendation is to start with Entity Framework or Entity Framework Core. Best to use SQL-Server as a database rather than MS-Access but if MS-Access is your choice then use Entity Framework Core as Entity Framework does not support MS-Access, only EF Core. Get SSMS (SQL-Server Management Studio), it's free to work with your database(s).

    To reiterate, what I provides to look at with code samples is not starting with the basics but is more and more gives insight to what is possible while starting out you need to "do" simple after studying the higher level code which has been written so it's easy to follow if you take the time to studying and not think this is past your current level.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Jackjsmith88 Monday, November 4, 2019 10:41 AM
    Friday, October 25, 2019 10:03 AM
    Moderator

All replies

  • Hello,

    It's best to steer clear of data wizards. Since strong typed classes are generated it can be a real problem updating them if a) a field type changes in the database table b) if fields are added or removed. The actual process is not apparent but once learned is easy to update the model but can be problematic when dealing with bound controls on a form.

    Other issues with TableAdapter, sharing data between forms, reusing models in other projects. Then there is also the need to fine tune "code" but that is not easy as you can not modify generated code which the wizard created without creating partial classes which are still connected to the generated code.

    Out of the two methods you learned about (and there are more), hand coding is better and when hand coding it should not be done directly in a form, instead read do updates, perform deletes and inserts in a class. Then there is the decision to use DataSet/DataTable, DataTable or classes to interact with databases.

    The next method is Entity Framework or Entity Framework Core. When reading about EF or EF Core there will be little to nothing on using them in Windows Forms but instead with web projects. 

    Taking one path for EF, you can create a database with table, add EF via wizards via "Code first with existing database" and classes are created for each table were in this case the code is easy to access. Downside is you are now in control of when table schema changes, dig into code and adjust.

    I have a code sample which has not been completed yet, there are two projects showing the basics with reading data, no edits, deletes or inserts as the code sample is a part one of an article I'm writing were part 2 will have edits, deletes, inserts. The samples may appear overkill for your initial step into working with data but it's good to see this as when a developer first starts out and sees just simple examples they get a sense of "easy" and later see it's not always easy.

    Then this code sample will introduces the basics of validating user input with EF. For instance, see the following code method for adding a new record were there are interactions with both a main table and reference tables.

    My recommendation is to start with Entity Framework or Entity Framework Core. Best to use SQL-Server as a database rather than MS-Access but if MS-Access is your choice then use Entity Framework Core as Entity Framework does not support MS-Access, only EF Core. Get SSMS (SQL-Server Management Studio), it's free to work with your database(s).

    To reiterate, what I provides to look at with code samples is not starting with the basics but is more and more gives insight to what is possible while starting out you need to "do" simple after studying the higher level code which has been written so it's easy to follow if you take the time to studying and not think this is past your current level.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Jackjsmith88 Monday, November 4, 2019 10:41 AM
    Friday, October 25, 2019 10:03 AM
    Moderator
  • Lets say i set up everything using wizards, the dataset/tableadapter are all there waiting to be used. by default the form_Load has a This.Tableadapter.fill(This.Dataset.DataTable)

    Who do you want to be? Do you want to be Mr. Peabody the wizard, or do you want to be boy Sherman that can do nothing without the wizard? :)

    Friday, October 25, 2019 4:42 PM
  • Hi Karen,

    Thanks for your really helpful explanation, i can see you've really invested some time to provide that, and i really appreciate it, im now on GITHUB studying your code :-)

    One think i never understood is storing the methods in seperate classes to call on, ive always done everything in the "form" and it gets very messy.
    Monday, November 4, 2019 11:01 AM
  • Hi Karen,

    Thanks for your really helpful explanation, i can see you've really invested some time to provide that, and i really appreciate it, im now on GITHUB studying your code :-)

    One think i never understood is storing the methods in seperate classes to call on, ive always done everything in the "form" and it gets very messy.

    Yes writing code in a form tends to become messy plus not reusable. Given a random code example below the form code is clean, the method used may be used in other forms.

    As you write more applications as a developer a pattern tends to emerge and some methods become candidates to put into a shared library which can be used in other projects. A very simple example is the NuGet package in my signature which makes connecting to databases simple, consistent and usable from .NET Framework 4.x and higher. 

    Then one of my favorites, language extension methods such as a starter solution I created. 

    namespace ProductsByCategory
    {
        public partial class Form1 : Form
        {
            private DataOperations _operations = new DataOperations();
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                CategoryComboBox.DataSource = _operations.GetCategories();
            }
        }
    }

    Class for data operations

    namespace NorthWindLibrary.Classes
    {
        public class DataOperations
        {
            /// <summary>
            /// Get all categories
            /// </summary>
            /// <returns></returns>
            public List<Category> GetCategories()
            {
                using (var context = new NorthWindContext())
                {
                    context.Configuration.LazyLoadingEnabled = false;
                    return context.Categories.ToList();
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 4, 2019 11:24 AM
    Moderator