none
Creating 45 variables and properties for CRUD program RRS feed

  • Question

  • Hi, I'm new in OOP world, I've been using functional paradigm in vb past 5 years
    and I have a task to link a vb form to a table which has got 45 fields.

    It's a Form with Textbox, comboxbox, richtextbox... and all of them is a total of 45 fields.

    As I learnt OOP, the book told me to always follow the below 4 steps,
    and I always do so when I write a small program in OOP, but today I have many fields (45).

    Below is the step I use :

    1. Create 45 variables (private)

    2. Create 45 properties (public - I can go for Auto-Implemented one to make it easy)

    3. Create a method to store those 45 properties into the table/database

    4. Create an object for the instance using "New Keyword"...


    My concern is now to know if going the way I usually do is right?  or maybe there's another way.

    Note that I won't be able to understand advanced concept now, as I'm a newbie in OOP.

    I also heard about Interface (I) and I tried to understand it but still don't get it right...
    as they said, when you want to create interface, they ask a question, What do they have in common ?   but I still don't get it right??

    I usually use Qt/C++ and the best thing with Qt is it's force you to use OOP when you're writing code but VB doesn't.

    'Variable declaration

    Private variable1 as string Private variable2 as string Private variable3 as string . .

    Private variable45 as string



    • Edited by Christine25 Saturday, February 16, 2019 12:19 PM
    Saturday, February 16, 2019 9:56 AM

Answers

  • Hello,

    The following will work with SQL-Server.

    Option 1, use Entity Framework 6 which will generate classes for you an when relationships have been setup it will create properties for each class to relate tables together

    Option 2, There are two ways to run the script below. The basics are shown in a post focused on C# while the script below does VB.NET

    Use SSMS (SQL-Server Management Studio), it's free. Select the database, create a new query, add the script,  replace 'Customers' in the first line with the table name you want to work with then run the query which generates a class as shown below the script. The second way is to create a text file in your project, name it script.sql, connect to the database via buttons at the top of the .sql file (there will be buttons) and run the script to get the class.

    Script

    DECLARE @TableName sysname = 'Customers';
    DECLARE @Result VARCHAR(MAX) = 'Public Class ' + @TableName; 
    
    SELECT  @Result = @Result + '
        Public Property ' + ColumnName + ' As ' + ColumnType + NullableSign
    FROM    ( SELECT    REPLACE(col.name, ' ', '_') ColumnName ,
                        column_id ColumnId ,
                        CASE typ.name
                          WHEN 'bigint' THEN 'Long'
                          WHEN 'binary' THEN 'Byte[]'
                          WHEN 'bit' THEN 'Boolean'
                          WHEN 'char' THEN 'String'
                          WHEN 'date' THEN 'DateTime'
                          WHEN 'datetime' THEN 'DateTime'
                          WHEN 'datetime2' THEN 'DateTime'
                          WHEN 'datetimeoffset' THEN 'DateTimeOffset'
                          WHEN 'decimal' THEN 'Decimal'
                          WHEN 'float' THEN 'Float'
                          WHEN 'image' THEN 'Byte()'
                          WHEN 'int' THEN 'Integer'
                          WHEN 'money' THEN 'Decimal'
                          WHEN 'nchar' THEN 'String'
                          WHEN 'ntext' THEN 'String'
                          WHEN 'numeric' THEN 'Decimal'
                          WHEN 'nvarchar' THEN 'String'
                          WHEN 'real' THEN 'Double'
                          WHEN 'smalldatetime' THEN 'DateTime'
                          WHEN 'smallint' THEN 'Short'
                          WHEN 'smallmoney' THEN 'Decimal'
                          WHEN 'text' THEN 'String'
                          WHEN 'time' THEN 'TimeSpan'
                          WHEN 'timestamp' THEN 'DateTime'
                          WHEN 'tinyint' THEN 'Byte'
                          WHEN 'uniqueidentifier' THEN 'Guid'
                          WHEN 'varbinary' THEN 'Byte()'
                          WHEN 'varchar' THEN 'String'
                          ELSE 'UNKNOWN_' + typ.name
                        END ColumnType ,
                        CASE WHEN col.is_nullable = 1
                                  AND typ.name IN ( 'bigint', 'bit', 'date',
                                                    'datetime', 'datetime2',
                                                    'datetimeoffset', 'decimal',
                                                    'float', 'int', 'money',
                                                    'numeric', 'real',
                                                    'smalldatetime', 'smallint',
                                                    'smallmoney', 'time',
                                                    'tinyint', 'uniqueidentifier' )
                             THEN '?'
                             ELSE ''
                        END NullableSign
              FROM      sys.columns col
                        JOIN sys.types typ ON col.system_type_id = typ.system_type_id
                                              AND col.user_type_id = typ.user_type_id
              WHERE     object_id = OBJECT_ID(@TableName)
            ) t
    ORDER BY ColumnId;
    
    SET @Result = @Result + '
    End Class';
    
    PRINT @Result;

    In this case I only have a few fields but the script picks up on all fields in a table.

    Public Class Customers
        Public Property CustomerIdentifier As Integer
        Public Property CompanyName As String
        Public Property ContactName As String
        Public Property ContactIdentifier As Integer?
        Public Property ContactTypeIdentifier As Integer?
        Public Property Street As String
        Public Property City As String
        Public Property PostalCode As String
        Public Property CountryIdentfier As Integer?
        Public Property Phone As String
        Public Property ModifiedDate As DateTime?
        Public Property InUse As Boolean?
    End Class
    

    Create a new class file and insert the generated class. Now let's say you want to relate Customers in this case with Orders table, run the above script againsts the child table orders.

    Public Class Orders
        Public Property OrderID As Integer
        Public Property CustomerIdentifier As Integer?
        Public Property EmployeeID As Integer?
        Public Property OrderDate As DateTime?
        Public Property RequiredDate As DateTime?
        Public Property ShippedDate As DateTime?
        Public Property ShipVia As Integer?
        Public Property Freight As Decimal?
        Public Property ShipAddress As String
        Public Property ShipCity As String
        Public Property ShipRegion As String
        Public Property ShipPostalCode As String
        Public Property ShipCountry As String
    End Class

    In the Customers class add a new property as shown below so now you can store orders for each customer.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Christine25 Thursday, August 15, 2019 10:25 AM
    Saturday, February 16, 2019 11:14 AM
    Moderator

All replies

  • Hi Christine25,

    In advance, I'm afraid a several tags (e.g. "<g class=" -- "</g>") are in your description, which makes it hard to read.  I hope you will remove these tags for easy reading. (Are you using some spell checking add-in?)

    I suppose VB.NET can be said as Object Oriented Program.
    I'm not sure why you think that, showing 'Variable declaration'.  That would not be related to OOP.
    But, if it is lengthy or redundant, you can declare it like this.

    Private aryVariable(44) as string
    A variable, 1-demension array, "aryVariable(44)" is an alternative of variable1, variable2, ...., variable45.
    We can use aryVariable(0) as an alternative of variable1.  (the first element of an array begins from zero)
    Let's use an array for simple and not-redundant code.


    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Saturday, February 16, 2019 11:00 AM
    Saturday, February 16, 2019 10:59 AM
  • Hello,

    The following will work with SQL-Server.

    Option 1, use Entity Framework 6 which will generate classes for you an when relationships have been setup it will create properties for each class to relate tables together

    Option 2, There are two ways to run the script below. The basics are shown in a post focused on C# while the script below does VB.NET

    Use SSMS (SQL-Server Management Studio), it's free. Select the database, create a new query, add the script,  replace 'Customers' in the first line with the table name you want to work with then run the query which generates a class as shown below the script. The second way is to create a text file in your project, name it script.sql, connect to the database via buttons at the top of the .sql file (there will be buttons) and run the script to get the class.

    Script

    DECLARE @TableName sysname = 'Customers';
    DECLARE @Result VARCHAR(MAX) = 'Public Class ' + @TableName; 
    
    SELECT  @Result = @Result + '
        Public Property ' + ColumnName + ' As ' + ColumnType + NullableSign
    FROM    ( SELECT    REPLACE(col.name, ' ', '_') ColumnName ,
                        column_id ColumnId ,
                        CASE typ.name
                          WHEN 'bigint' THEN 'Long'
                          WHEN 'binary' THEN 'Byte[]'
                          WHEN 'bit' THEN 'Boolean'
                          WHEN 'char' THEN 'String'
                          WHEN 'date' THEN 'DateTime'
                          WHEN 'datetime' THEN 'DateTime'
                          WHEN 'datetime2' THEN 'DateTime'
                          WHEN 'datetimeoffset' THEN 'DateTimeOffset'
                          WHEN 'decimal' THEN 'Decimal'
                          WHEN 'float' THEN 'Float'
                          WHEN 'image' THEN 'Byte()'
                          WHEN 'int' THEN 'Integer'
                          WHEN 'money' THEN 'Decimal'
                          WHEN 'nchar' THEN 'String'
                          WHEN 'ntext' THEN 'String'
                          WHEN 'numeric' THEN 'Decimal'
                          WHEN 'nvarchar' THEN 'String'
                          WHEN 'real' THEN 'Double'
                          WHEN 'smalldatetime' THEN 'DateTime'
                          WHEN 'smallint' THEN 'Short'
                          WHEN 'smallmoney' THEN 'Decimal'
                          WHEN 'text' THEN 'String'
                          WHEN 'time' THEN 'TimeSpan'
                          WHEN 'timestamp' THEN 'DateTime'
                          WHEN 'tinyint' THEN 'Byte'
                          WHEN 'uniqueidentifier' THEN 'Guid'
                          WHEN 'varbinary' THEN 'Byte()'
                          WHEN 'varchar' THEN 'String'
                          ELSE 'UNKNOWN_' + typ.name
                        END ColumnType ,
                        CASE WHEN col.is_nullable = 1
                                  AND typ.name IN ( 'bigint', 'bit', 'date',
                                                    'datetime', 'datetime2',
                                                    'datetimeoffset', 'decimal',
                                                    'float', 'int', 'money',
                                                    'numeric', 'real',
                                                    'smalldatetime', 'smallint',
                                                    'smallmoney', 'time',
                                                    'tinyint', 'uniqueidentifier' )
                             THEN '?'
                             ELSE ''
                        END NullableSign
              FROM      sys.columns col
                        JOIN sys.types typ ON col.system_type_id = typ.system_type_id
                                              AND col.user_type_id = typ.user_type_id
              WHERE     object_id = OBJECT_ID(@TableName)
            ) t
    ORDER BY ColumnId;
    
    SET @Result = @Result + '
    End Class';
    
    PRINT @Result;

    In this case I only have a few fields but the script picks up on all fields in a table.

    Public Class Customers
        Public Property CustomerIdentifier As Integer
        Public Property CompanyName As String
        Public Property ContactName As String
        Public Property ContactIdentifier As Integer?
        Public Property ContactTypeIdentifier As Integer?
        Public Property Street As String
        Public Property City As String
        Public Property PostalCode As String
        Public Property CountryIdentfier As Integer?
        Public Property Phone As String
        Public Property ModifiedDate As DateTime?
        Public Property InUse As Boolean?
    End Class
    

    Create a new class file and insert the generated class. Now let's say you want to relate Customers in this case with Orders table, run the above script againsts the child table orders.

    Public Class Orders
        Public Property OrderID As Integer
        Public Property CustomerIdentifier As Integer?
        Public Property EmployeeID As Integer?
        Public Property OrderDate As DateTime?
        Public Property RequiredDate As DateTime?
        Public Property ShippedDate As DateTime?
        Public Property ShipVia As Integer?
        Public Property Freight As Decimal?
        Public Property ShipAddress As String
        Public Property ShipCity As String
        Public Property ShipRegion As String
        Public Property ShipPostalCode As String
        Public Property ShipCountry As String
    End Class

    In the Customers class add a new property as shown below so now you can store orders for each customer.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Christine25 Thursday, August 15, 2019 10:25 AM
    Saturday, February 16, 2019 11:14 AM
    Moderator
  • Sorry about those Tags,

    I installed a Chrome add-in called Grammarly by mistake, now it's cleaned.

    Saturday, February 16, 2019 12:21 PM
  • Unforntunately, we're using MySQL as database, not sure if the above process will work on MySQL too.
    Saturday, February 16, 2019 12:38 PM
  • Hi Christine25,

    In advance, I'm afraid a several tags (e.g. "<g class=" -- "</g>") are in your description, which makes it hard to read.  I hope you will remove these tags for easy reading. (Are you using some spell checking add-in?)

    I suppose VB.NET can be said as Object Oriented Program.
    I'm not sure why you think that, showing 'Variable declaration'.  That would not be related to OOP.
    But, if it is lengthy or redundant, you can declare it like this.

    Private aryVariable(44) as string
    A variable, 1-demension array, "aryVariable(44)" is an alternative of variable1, variable2, ...., variable45.
    We can use aryVariable(0) as an alternative of variable1.  (the first element of an array begins from zero)
    Let's use an array for simple and not-redundant code.


    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Not sure why you're saying VARIABLE DECLARATION is not related to OOP.

    it's a rule of thumb when creating Class, and it's called "Instance Variable", it's purely related to OOP




    • Edited by Christine25 Saturday, February 16, 2019 1:17 PM
    Saturday, February 16, 2019 1:06 PM
  • Unforntunately, we're using MySQL as database, not sure if the above process will work on MySQL too.
    As stated, only SQL-Server. I sure I could modify it to work with MySql but don't have MySql installed at home. 

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 16, 2019 1:23 PM
    Moderator
  • BTW If you are use to C++ perhaps C# would be a better fit especially since there are MySql code generators for what I mentioned for SQL-Server but in MySql.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 16, 2019 2:39 PM
    Moderator
  • Thanks Karen Payne for your answer.

    What about the variables at the top, is it worth to create them? I'm asking because I'm not very skilled in OOP,

    and the book I use to learn OOP says that it's a rule of thumb to first create VARIABLE, then PROPERTIES, then METHOD and finally INSTANTIATE an OBJECT.

    Can I stick to the rule of thumb or there's a shorter way to go?

    and will also using ODBC to query / for database connection.

    In addition, I also heard about Interface in OOP... ???


    Please I need a suggestion to go for?
    • Edited by Christine25 Saturday, February 16, 2019 4:34 PM
    Saturday, February 16, 2019 4:31 PM
  • Please guys,

    I'm waiting on your answers in regards with my last post in order to start coding.

    Thanks in advance !


    Saturday, February 16, 2019 5:02 PM
  • If the book indicates to use variables then properties it's old.

    This is how we do it now

    Public Class Person
        Public Property Id() As Integer
        Public Property FirstName() As String
        Public Property LastName() As String
        Public ReadOnly Property FullName() As String
            Get
                Return $"{FirstName} {LastName}"
            End Get
        End Property
    
        Public Overrides Function ToString() As String
            Return Id.ToString()
        End Function
    End Class

    This is how it was done in the past.

    Public Class Person
        Private _Id As Integer
        Private _FirstName As String
        Private _LastName As String
    
        Public Property Id() As Integer
            Get
                Return _Id
            End Get
            Set
                _Id = Value
            End Set
        End Property
    
        Public Property FirstName() As String
            Get
                Return _FirstName
            End Get
            Set
                _FirstName = Value
            End Set
        End Property
    
        Public Property LastName() As String
            Get
                Return _LastName
            End Get
            Set
                _LastName = Value
            End Set
        End Property
    
        Public ReadOnly Property FullName() As String
            Get
                Return $"{FirstName} {LastName}"
            End Get
        End Property
    
        Public Overrides Function ToString() As String
            Return Id.ToString()
        End Function
    End Class

    Create a new Person

    Dim person As New Person With {.Id = 1, .FirstName = "Karen", .LastName = "Payne"}

    Create a list of Person

    Dim PeopleList As New List(Of Person) From
            {
                New Person With {.Id = 1, .FirstName = "Karen", .LastName = "Payne"},
                New Person With {.Id = 2, .FirstName = "Mary", .LastName = "Adams"}
            }

    Then getting into it with inheritance 

    Public Class Person
        Public Property Id() As Integer
        Public Property FirstName() As String
        Public Property LastName() As String
        Public ReadOnly Property FullName() As String
            Get
                Return $"{FirstName} {LastName}"
            End Get
        End Property
    
        Public Overrides Function ToString() As String
            Return Id.ToString()
        End Function
    End Class
    Public Class Manager
        Inherits Person
        Public Property Division() As String
        Public Property Employees() As List(Of Person)
    End Class

    .

    Dim PeopleList As New List(Of Person) From
        {
    		New Person With {.Id = 1, .FirstName = "Jim", .LastName = "Jones"},
    		New Person With {.Id = 2, .FirstName = "Mary", .LastName = "Adams"}
        }
    
    Dim manager As New Manager With {.Id = 1, .FirstName = "Karen", .LastName = "Payne", .Employees = PeopleList}

    Deeper down the rabbit hole

    Public Class Employee
        Inherits Person
    
        Public Property StartTime() As TimeSpan
        Public Property EndTime() As TimeSpan
        Public Property Manager() As Manager
    End Class
    

    Not to sound rude but this could go on And on, we are here to assist but Not teach all aspects of OOP.


     

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Saturday, February 16, 2019 6:35 PM
    Moderator
  • Christine,

    You ask this question in my opinion as if Functional programming is something different from OOP. 

    The same way as persons who tell that people with a black skin are different than humans. 

    Sorry, that is not the way I think about it.  In functional programming you can perfectly use OOP. 

    OOP means that you are programming with objects, and not everytime resuse the same data. 

    For the rest, there is more difference than in difference because the skin of a human, but not as much as you seem to think. 


    Success
    Cor

    Saturday, February 16, 2019 8:55 PM
  • Christine,

    You ask this question in my opinion as if Functional programming is something different from OOP. 

    The same way as persons who tell that people with a black skin are different than humans. 

    Sorry, that is not the way I think about it.  In functional programming you can perfectly use OOP. 

    OOP means that you are programming with objects, and not everytime resuse the same data. 

    For the rest, there is more difference than in difference because the skin of a human, but not as much as you seem to think. 


    Success
    Cor

    How can I use OOP in functional programming... cause I've been writing in functional and never heard about OOP in it.    I'm curious to know that !!!

    But I remember back in vb6 you can create class and so on... but wasn't the entire OOP paradigm like in VB.net

    Saturday, February 16, 2019 9:24 PM

  • Why did you overrides this??  what is it for??    as Id is already declared Integer.

    Why do they overrides a property ??   and why in the override it's changed to STRING ?

        Public Overrides Function ToString() As String
            Return Id.ToString()
        End Function

    Microsoft doc says :

    If an inherited property or method has to behave differently in the derived class it can bbe overridden

    My question is now, how/why "id" will behave differently ??

    • Edited by Christine25 Saturday, February 16, 2019 10:28 PM
    Saturday, February 16, 2019 10:19 PM

  • Why did you overrides this??  what is it for??    as Id is already declared Integer.

    Why do they overrides a property ??   and why in the override it's changed to STRING ?

        Public Overrides Function ToString() As String
            Return Id.ToString()
        End Function

    Microsoft doc says :

    If an inherited property or method has to behave differently in the derived class it can bbe overridden

    My question is now, how/why "id" will behave differently ??

    There is nothing overridden in the code I presented, this is instead known as inheritance.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 16, 2019 11:31 PM
    Moderator
  • In addition to my last reply, I use similar logic when working with database connections in regards to inheritance as per my NuGet BaseConnection package.

      And a point to be made, we are way beyond the 45 variables in the initial question and typically when replies get to 15 plus responses we are moving off track typically and are now so I'm done here. Good luck with this task.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, February 16, 2019 11:35 PM
    Moderator