Doing a Database Create Table and Alter Table in Visual Basic

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

Working with databases is crucial to succeeding in the world of development. Extraction from a database, doing dynamic data inserts, and updates is quite straightforward. But sometimes, you will have to do more. This is what this article is about: Creating Databases with the Create Table statement and altering those databases.

For the uninformed:

Databases

A database’s sole function is to store information, hence the name: data base. A database consist of the following objects:

• Tables
• Queries
• Views
• Stored Procedures
• Functions

Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row, or record, contains all the information pertaining to one topic. A column, or field, is once piece of information. Here is a small example of records and fields:

Figure 1: Records and Fields

In Figure 1, you can see that there are seven records and three fields. We will create this database a bit later.

Although you may have many columns in each table and many tables at your disposal in a database, it doesn’t mean that you have to make use of all of them. With databases, less is more. This means that the least amount of fields and the least amount of tables used in your database will amount to the best performance. Let me give you another example:

Figure 2: Database Tables used productively

This is from one of my programs I had to implement at my work. Its whole purpose is to track student progress according to which course the student is taking. Each course has different subjects. The reason I show this here is to show you that a simple program can make use of multiple inter-connected tables. An inexperienced programmer would add all the fields into one table, which is possible, but is also wrong. This system has been running successfully for twelve years already. There is more than enough space to store the students’ personal information, as well as more than enough space for custom courses and custom subjects.

All these tables are connected by a term called relationships. If you look closely at the tables inside Figure 2, you will see that inside each table there is a reference to another table’s field. This forms a relationship between tables. A relationship becomes possible when there is a Primary key inside one table referencing a Foreign key. This is not really the main topic of today’s article, so let me not get ahead of myself (as always) again. More information on database tables can be found here.

Queries

Now that we know where and how to store data, we need to know how to access that data. This is done through a database query. You usually have to write a query to extract the needed information from the appropriate tables. I will delve deeper into this in my next article (Extracting data). More information on database queries can be found here.

Views

A View is a dynamic result set. This means that we can create a view based on a query and it will not affect any data that is stored. More information on Views can be found here.

Stored Procedures

A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won’t need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.

Functions

There are many functions inside the SQL Language. These functions can help with string manipulation, date manipulation, as well as computing Averages, just to name a few. A complete list of SQL Functions can be found here.

Now that we know what makes a database tick, let’s do a small program. This program will allow you to create a database from within your Visual Basic code.

Our Program

Start a new Visual Basic Windows Forms project. Once the form’s design is shown, add two buttons onto it.

Code

Add the following Import statement to import the SQL functionalities into Visual Basic:

Imports System.Data.SqlClient

Add the following code to create a database:

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

      Dim strCreate As String

      Dim connCreate As SqlConnection = New SqlConnection("Server=HTG;" & _
                                                          "uid=sa;pwd=;")

      strCreate = "CREATE DATABASE Students ON PRIMARY " & _
         "(NAME = Students, " & _
         " FILENAME = 'C:\DataBaseExample\Students.mdf', " & _
         " SIZE = 2MB, " & _
         " MAXSIZE = 10MB, " & _
         " FILEGROWTH = 10%) " & _
         " LOG ON " & _
         "(NAME = MyDatabase_Log, " & _
         " FILENAME = 'C:\DataBaseExample\Students.ldf', " & _
         " SIZE = 1MB, " & _
         " MAXSIZE = 5MB, " & _
         " FILEGROWTH = 10%) "

      Dim commCreate As SqlCommand = New SqlCommand(strCreate, connCreate)

      Try
         connCreate.Open()
         commCreate.ExecuteNonQuery()
         MessageBox.Show("Database has been created  successfully")
      Catch ex As Exception
         MessageBox.Show(ex.ToString())
      Finally
         If (connCreate.State = ConnectionState.Open) Then
            connCreate.Close()
         End If
      End Try


   End Sub

I opened up an SQL connection to the SQL database server I would like to work with. Then, I created a string variable hosting the SQL command that creates the actual database on the SQL Server I have opened. Afterwards, I execute the command to create the database.

For more information regarding the SQL Create Database statement, have a look here.

Altering a Database

Altering a database is much easier than creating one. Add the following code:

      Dim strAlter As String

      Dim connAlter As SqlConnection = New SqlConnection("Server=HTG;" & _
                                                         "uid=sa;pwd=;")

      strAlter = "ALTER DATABASE Students MODIFY NAME = _
         StudentReplacement COLLATE CollateStudents"

      Dim commAlter As SqlCommand = New SqlCommand(strAlter, connAlter)

      Try
         connAlter.Open()
         commAlter.ExecuteNonQuery()
         MessageBox.Show("Database has been altered successfully")
      Catch ex As Exception
         MessageBox.Show(ex.ToString())
      Finally
         If (connAlter.State = ConnectionState.Open) Then
            connAlter.Close()
         End If
      End Try

For more information on the ALTER statement, have a look here.

Conclusion

Knowledge of advanced SQL statements to improve your programs is essential to any Visual Basic developer. Until next time, good luck in your database endeavors.