Generate Class.vb File Programmatically

[Alex_AMF]

Hello,

I want to create a class builder which will get the tables from the specified database and build my [className].vb file - at the push of a button!

For example:

        Dim strBuilder As New StringBuilder

        strBuilder.Append("Public class clsExample" & vbCrLf)

        'Create variables
        strBuilder.Append("Private _ID As String")
        strBuilder.Append(vbCrLf)

        'Create properties
        strBuilder.Append("Public Property _ID() As Integer" & vbCrLf)
        strBuilder.Append("Get" & vbCrLf)
        strBuilder.Append("return _ID" & vbCrLf)
        strBuilder.Append("end Get" & vbCrLf)
        strBuilder.Append("Set(ByVal Value As Integer)" & vbCrLf)
        strBuilder.Append("_ID = Value" & vbCrLf)
        strBuilder.Append("End Set" & vbCrLf)
        strBuilder.Append("End Property " & vbCrLf)
        strBuilder.Append(vbCrLf)
        strBuilder.Append(vbCrLf & "End Class")

        Console.Write(strBuilder.ToString())

To get this result in the clsExample.vb file that was created using the previous code

Public Class clsExample
        Private _ID As String
        Public Property _ID() As Integer
            Get
                Return _ID
            End Get
            Set(ByVal Value As Integer)
                _ID = Value
            End Set
        End Property


    End Class

I'm not sure how to go about telling the program to create the [className].vb file and include my strBuilder as code in the file. Hope this makes sense...

[dunfiddlin]

Hope this makes sense...

Well, now you mention it .....

What is the ultimate purpose here? I can't, for the moment, think of any use for such a beast.

[Alex_AMF]

Well, every time I create new projects that will use a Database, i must create the basic (and redundant) code that will contain it's properties, methods and functions.

For example, I would create a table named "Employees" in my database. Once all the table columns are entered with their types, I would go into my ClassGenerator program to (at the click of a button):
Create a folder named "Classes" if none exist
Create a class named clsEmployees.vb, and store it in that classes folder
Append all the code that is normally entered by hand for the properties, methods and functions.


The ultimate purpose is to save time. If I have 10 tables to create, that means 10 class files in VB to communicate with the database and build my objects so I can use them in the given program. In the long run, it's always the same information but it's time consuming. I figured if my button click event would do all that for me - I'd be winning.

[dunfiddlin]

If I have 10 tables to create, that means 10 class files in VB

Since when? The whole purpose of a class is to create a generic code which may be passed values to distinguish one instance from another. If you're creating a new class for every datatable specific only to that table then you're not merely producing redundant code, you're completely missing the point! In any case ten tables require just one dataset, as do twenty. thirty, forty .... or every table you've ever created!

Even if you succeeded in this scheme, how would you then include the classes in the project, allowing them to be executed?

[Alex_AMF]

Yeah that's what I was wondering... How would I add that into the project? We use classes to create objects, and we split them up so that it is easier to deal with information being loaded / saved or modified - that's why we have so many clsFiles.vb in our project folder called "Classes".

[Alex_AMF]

IO.File.WriteAllText(path, strBuilder.ToString())

Creates the file and appends the text to that file. Works nice, now I just can't see it appear in the project solution explorer. I need to do "Include in project" manually... Gotta figure out how to do that programmatically and everything will work great!

[Shaggy Hiker]

What you are doing is starting down the path of creating a code generator. There are a few of them on the market, and I have a bit of experience working with various forms. Frankly, I don't like the idea. Taking it to it's logical conclusion, you end up with the Entity Framework that MS put out where you can build all your objects off of your database nice and easy. You are doing a stripped down version of that, which will have lesser functionality....which is probably a good thing, but ultimately, I've never seen a design like this that looked good. You end up with a maintenance headache, and for what? What do you gain from having a class for each table? Are you going to go on and write the code so that your project changes when you change the DB, or are you going to make all those changes by hand? If each one of these classes wraps a row of data, will you have a class that wraps the whole datatable and includes a List(of Row)? If you do that, how is that different from a datatable, and what greater benefit does it convey?

Essentially, I think you are doing a whole lot of work to recreate a datatable, or perhaps a strongly typed datatable. I've seen people try it before, but I've never seen any good justification for it.

[Alex_AMF]

Yeah it will be a very small code generator that will just help me / the company create it's classes faster. The reason I keep this method is because 90% of the programs are designed like that. My boss wanted to keep that method so I just went with the flow and never really explored any alternatives. I have a bout 5 months of experience in VB.Net (used to be more oriented towards web programming). Therefore I do not really know the best concept of information setting/gathering from the database. Managing 10 tables with 1 datatable sounds crazy to me for now, I'll need to explore that.

[Shaggy Hiker]

You wouldn't put 10 tables in a datatable, you would put one table in a datatable. You could have 10 datatables in a dataset, though.

You might look into the Entity Framework, as it does what you are doing and far more. However, if you want to add the files automatically, it has to do with something like the sln or proj file or the manifest, or something like that. I don't remember exactly, but if you look at those files, you'll find one that is a text file where you will see the other files you have added.

[Niya]

I wrote an application to convert tables from SQL Server 2005 to classes for use in VB.Net programs. Although I agree with SH in that you should use the Entity Framework instead, which would surely be far superior, I'll be willing to provide my tool as its far simpler to learn and apply than the Entity Framework. Here is an example of a class generated by my tool:-

'
Imports System.Data
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.Text
Imports System.ComponentModel
Imports System.Collections.ObjectModel
Public Class STItemsRow 
    Inherits STDataRowBase
 
    Private Shared gshr_lstDbFields As IList(Of DatabaseField)
    Private _ItemID As Integer
    Private _ItemCode As String
    Private _Description As String
    Private _Quantity As Integer
    Private _CostPrice As Decimal
    Private _SellPrice As Decimal
    Private _Taxable As Boolean
 
Protected Overrides Function GetDatabaseFields() As List(Of DatabaseField)
    If gshr_lstDbFields IsNot Nothing Then Return gshr_lstDbFields
    Dim lst As New List(Of DatabaseField)
    lst.Add(New DatabaseField("ItemID", True, True, False, SqlDbType.Int))
    lst.Add(New DatabaseField("ItemCode", False, False, False, SqlDbType.VarChar))
    lst.Add(New DatabaseField("Description", False, False, False, SqlDbType.VarChar))
    lst.Add(New DatabaseField("Quantity", False, False, False, SqlDbType.Int))
    lst.Add(New DatabaseField("CostPrice", False, False, False, SqlDbType.Decimal))
    lst.Add(New DatabaseField("SellPrice", False, False, False, SqlDbType.Decimal))
    lst.Add(New DatabaseField("Taxable", False, False, False, SqlDbType.Bit))
    gshr_lstDbFields = lst
    Return lst
End Function
    Public Property ItemID() As Integer
        Get
            Return _ItemID
        End Get
        Set(ByVal value As Integer)
            OnPropertyChanging(New PropertyChangingEventArgs("ItemID"))
            _ItemID = value
            OnPropertyChanged(New PropertyChangedEventArgs("ItemID"))
        End Set
    End Property
    Public Property ItemCode() As String
        Get
            Return _ItemCode
        End Get
        Set(ByVal value As String)
            OnPropertyChanging(New PropertyChangingEventArgs("ItemCode"))
            _ItemCode = value
            OnPropertyChanged(New PropertyChangedEventArgs("ItemCode"))
        End Set
    End Property
    Public Property Description() As String
        Get
            Return _Description
        End Get
        Set(ByVal value As String)
            OnPropertyChanging(New PropertyChangingEventArgs("Description"))
            _Description = value
            OnPropertyChanged(New PropertyChangedEventArgs("Description"))
        End Set
    End Property
    Public Property Quantity() As Integer
        Get
            Return _Quantity
        End Get
        Set(ByVal value As Integer)
            OnPropertyChanging(New PropertyChangingEventArgs("Quantity"))
            _Quantity = value
            OnPropertyChanged(New PropertyChangedEventArgs("Quantity"))
        End Set
    End Property
    Public Property CostPrice() As Decimal
        Get
            Return _CostPrice
        End Get
        Set(ByVal value As Decimal)
            OnPropertyChanging(New PropertyChangingEventArgs("CostPrice"))
            _CostPrice = value
            OnPropertyChanged(New PropertyChangedEventArgs("CostPrice"))
        End Set
    End Property
    Public Property SellPrice() As Decimal
        Get
            Return _SellPrice
        End Get
        Set(ByVal value As Decimal)
            OnPropertyChanging(New PropertyChangingEventArgs("SellPrice"))
            _SellPrice = value
            OnPropertyChanged(New PropertyChangedEventArgs("SellPrice"))
        End Set
    End Property
    Public Property Taxable() As Boolean
        Get
            Return _Taxable
        End Get
        Set(ByVal value As Boolean)
            OnPropertyChanging(New PropertyChangingEventArgs("Taxable"))
            _Taxable = value
            OnPropertyChanged(New PropertyChangedEventArgs("Taxable"))
        End Set
    End Property
Protected Overrides Function CollectDataFromFields() As IList(Of ClassFieldData)
    Dim r As New List(Of ClassFieldData)
 
    r.Add(New ClassFieldData(MyBase.DatabaseField("ItemID"), Me.ItemID))
    r.Add(New ClassFieldData(MyBase.DatabaseField("ItemCode"), Me.ItemCode, True, True))
    r.Add(New ClassFieldData(MyBase.DatabaseField("Description"), Me.Description, True, True))
    r.Add(New ClassFieldData(MyBase.DatabaseField("Quantity"), Me.Quantity))
    r.Add(New ClassFieldData(MyBase.DatabaseField("CostPrice"), Me.CostPrice))
    r.Add(New ClassFieldData(MyBase.DatabaseField("SellPrice"), Me.SellPrice))
    r.Add(New ClassFieldData(MyBase.DatabaseField("Taxable"), Me.Taxable))
    Return r
End Function
Public Overrides Sub FetchIntoFields(ByVal row As System.Data.DataRow)
    If TryGetColumnData(VBDataTypes.VBInteger, row, "ItemID", Me.ItemID) = False Then ThrowColumnNotExists("ItemID")
    If TryGetColumnData(VBDataTypes.VBString, row, "ItemCode", Me.ItemCode) = False Then ThrowColumnNotExists("ItemCode")
    If TryGetColumnData(VBDataTypes.VBString, row, "Description", Me.Description) = False Then ThrowColumnNotExists("Description")
    If TryGetColumnData(VBDataTypes.VBInteger, row, "Quantity", Me.Quantity) = False Then ThrowColumnNotExists("Quantity")
    If TryGetColumnData(VBDataTypes.VBDecimal, row, "CostPrice", Me.CostPrice) = False Then ThrowColumnNotExists("CostPrice")
    If TryGetColumnData(VBDataTypes.VBDecimal, row, "SellPrice", Me.SellPrice) = False Then ThrowColumnNotExists("SellPrice")
    If TryGetColumnData(VBDataTypes.VBBool, row, "Taxable", Me.Taxable) = False Then ThrowColumnNotExists("Taxable")
End Sub
Protected Overrides Sub SetIdentityFieldValue(ByVal IdentityValue As Long)
    Me.ItemID = IdentityValue
End Sub
End Class
Public Class STItemsRowCollection 
    Inherits STDataRowCollection(Of STItemsRow)
 
    Public Sub New()
        SetTable("Items")
    End Sub
    Public Enum Fields
        ItemID
        ItemCode
        Description
        Quantity
        CostPrice
        SellPrice
        Taxable
    End Enum
 
    Public Sub AddWhereClauseCondition(ByVal field As Fields, ByVal SqlOperator As enumSqlOperators, ByVal value As Object)
        If field = Fields.ItemID Then InternalAddWhereClauseCondition(New WhereClauseData("ItemID", value, SqlOperator))
        If field = Fields.ItemCode Then InternalAddWhereClauseCondition(New WhereClauseData("ItemCode", value, SqlOperator))
        If field = Fields.Description Then InternalAddWhereClauseCondition(New WhereClauseData("Description", value, SqlOperator))
        If field = Fields.Quantity Then InternalAddWhereClauseCondition(New WhereClauseData("Quantity", value, SqlOperator))
        If field = Fields.CostPrice Then InternalAddWhereClauseCondition(New WhereClauseData("CostPrice", value, SqlOperator))
        If field = Fields.SellPrice Then InternalAddWhereClauseCondition(New WhereClauseData("SellPrice", value, SqlOperator))
        If field = Fields.Taxable Then InternalAddWhereClauseCondition(New WhereClauseData("Taxable", value, SqlOperator))
    End Sub
End Class

[kareninstructor]

I agree with Shaggy in regards to Entity Framework or DataContext class which does an excellent job and creating strongly type classes for data. Once the classes are generated you can add functions, procedures and properties and events of your own. Your code is placed into a Partial class and is not distrubed when generating new code via the data wizards.

http://en.wikipedia.org/wiki/Entity%...tionship_model
http://msdn.microsoft.com/en-us/library/fxsa23t6.aspx
http://msdn.microsoft.com/en-us/libr...tacontext.aspx

[CoachBarker]

I created a small program a couple years ago that allows you to select a database, create an entity class for either all or some selected tables. (Access database since that is what I use for small apps I write with certain naming conventions). It also creates a data access class which includes add, update, delete and a basic select statement functions. It creates a module for database connection and closing the database. It creates folders on my C drive in a folder named after the database I am using, a folder for each item I am creating(Database, EntityClass, DAClass, Modules)For me it is simpler to run the app and then import the folders or just the code to any new projects I am creating. A lot less code I have to write and covers all the basic requirements for my projects.

[Alex_AMF]

I presented the program I developed over the weekend to my boss this morning. It works flawlessly and will enable us to save an insane amount of time. It fetches the database specified, allows you to choose which tables to transform into vb code and stores it into a "Classes" folder specified at your desired location. Thanks for everyone who helped

[Niya]

I created a small program a couple years ago that allows you to select a database, create an entity class for either all or some selected tables. (Access database since that is what I use for small apps I write with certain naming conventions). It also creates a data access class which includes add, update, delete and a basic select statement functions. It creates a module for database connection and closing the database. It creates folders on my C drive in a folder named after the database I am using, a folder for each item I am creating(Database, EntityClass, DAClass, Modules)For me it is simpler to run the app and then import the folders or just the code to any new projects I am creating. A lot less code I have to write and covers all the basic requirements for my projects.

I presented the program I developed over the weekend to my boss this morning. It works flawlessly and will enable us to save an insane amount of time. It fetches the database specified, allows you to choose which tables to transform into vb code and stores it into a "Classes" folder specified at your desired location. Thanks for everyone who helped

Wow...I though I was the only one who made such a tool lol....Would be nice to compare our approaches if time could have permitted that.

[CoachBarker]

After I clean mine up a little bit I would be willing to post it in the code bank forum if anyone is interested.

[Niya]

I'm curious does yours use CodeDOM ? I wanted to use CodeDom for mine but GOD!!!....It takes a million lines of CodeDom to generate a few lines of code. I opted for my own ad-hoc code generation scheme.

[CoachBarker]

no nothing complex straight vb.net in VS 2005 if you mean me

Just ran across this again. Mine uses StreamReader and StreamWriter to do all the work.

[Niya]

Lol CodeDom is VB.Net....its made up of a few namespaces in the Framework. It allows you to generate source code in either VB.Net or C#.

[Evil_Giraffe]

If you're looking at Entity Framework, I'd urge you to explore all the ORMs available in .NET, not just whatever Microsoft provides. There are a number of very mature ORMs (Object-Relational Mapper), notably NHibernate, and a few "micro-ORMs" such as Massive that come with much less power but much less overhead and faff as well.

[Evil_Giraffe]

What do you gain from having a class for each table?

Well, for one thing, you get a place to put behaviour related to the entity that is being modelled. Code Generators should create partial classes such that they dump all their code into one file, and you write all the "programmed" bits in another file. That way the generator can re-generate the generated code without affecting or needing to deal with your code. This is exactly the way that the WinForms Designer works - do you refuse to use that as well? (Okay, to be fair, I don't like the Designer and would avoid it if I could, which somewhat detracts from my argument here)