VB.NET Classes from SQL DB

• Download demo project - 17.9 KB

Introduction

This application is for developers who want to gain time by automatically generating their Object Classes from the SQL database.

Background

I was looking for a migration from a VB application that uses modules and functions and make it more Object Oriented using Classes. I used several recursive loops in order to speed the process.

Using the Code

This application is very straight forward. All you need to do is:

1. Identify your connection string to the SQL Server
2. Identify the database you wish for the classes to be generated

The main select statements to get the database tables and columns are given below:

Dim con As New SqlConnection(TextBox1.Text)
Dim con1 As New SqlConnection(TextBox1.Text)
Dim dbase As String
dbase = TextBox2.Text
Dim cmd As New SqlCommand_
("Select " & dbase & ".INFORMATION_SCHEMA.TABLES.TABLE_NAME " & _
"from " & dbase & ".INFORMATION_SCHEMA.TABLES", con)

con.Open()
Dim cols = 0
Dim dbr As SqlDataReader = cmd.ExecuteReader()
Dim i = 0

While dbr.Read()
    TableArray(i) = dbr.Item("Table_Name")
    RichTextBox1.AppendText(TableArray(i) & vbCrLf)
    Dim ColArr(20) As String
    Dim sw As New StreamWriter(TableArray(i) & ".vb")
    Dim cmdcol As New SqlCommand("Select INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME" & _
     " from " & _
    dbase & ".INFORMATION_SCHEMA.COLUMNS " & _
    "where table_name = N'" & TableArray(i) & "'", con1)
    con1.Open()
    Dim dcr As SqlDataReader = cmdcol.ExecuteReader()
    Dim j = 0
    sw.WriteLine("Public Class " & TableArray(i))
    sw.WriteLine()
    
    While dcr.Read()
        ColArr(j) = dcr.Item("Column_Name")
        RichTextBox1.AppendText("    * " & ColArr(j) & vbCrLf)
        sw.WriteLine("Public m" & ColArr(j) & " as string")
        
        j += 1
        
    End While

Points of Interest

I am trying to reach faster development time by creating these small tools.
It is interesting for now, and I am working on some other tool to create the database functions:

• Insert
• Update
• Delete
• etc.

History

This is the first version of this app. I will keep you posted on future releases.