Chapter 3:(Part-II) Creating Database and Tables in MSSQL by VB.NET
Introduction to MSSQL Database Creation in VB.NET
In today’s data-driven world, databases play a crucial role in storing and managing information efficiently. One of the popular relational database management systems is Microsoft SQL Server (MSSQL), and VB.NET, a widely used programming language, provides a seamless way to create and manipulate databases. In this article, we will delve into the process of creating an MSSQL database in VB.NET with code example, exploring the steps, best practices, and importance of database creation.
Importance of Database Creation in VB.NET
Database creation is a fundamental step in building any application that requires data storage and retrieval. It lays the foundation for organizing and managing data in a structured manner, enabling efficient data retrieval and manipulation. Properly designed databases ensure data integrity, consistency, and security, thereby preventing data loss or unauthorized access. In the context of VB.NET, creating an MSSQL database provides a solid backend for storing and managing data in desktop applications, web applications, or other software solutions.
Defining Database Tables and Fields
After creating the database, the next step is to define the tables and fields within the database. Tables represent entities in the application, and fields define the attributes or properties of those entities. VB.NET provides classes and methods, such as SqlDataAdapter and DataTable, to define and manipulate database tables and fields programmatically.
Setting Primary Keys and Relationships
Defining primary keys and relationships is a crucial step in database creation, as it ensures data integrity and consistency. Primary keys uniquely identify each record in a table, and relationships establish links between tables based on common fields.
Generate a database in Microsoft SQL Server (MSSQL) at runtime
Now let’s delve in our lesson step by step. First of all , we will create function in vb.net that generate a database in MSSQL by the name of “modernschools” at runtime.
Here’s a brief explanation of the steps involved in generating a database in MSSQL using VB.NET:
1: Establish a connection to the MSSQL server: You would need to create a connection to the MSSQL server using the appropriate connection string, which includes the server name, credentials, and other connection properties.
2: Create a SQL query to generate the database: You would need to create a SQL query that specifies the database name, along with any other settings such as file locations, collation, and other database options.
3: Execute the SQL query: You would need to execute the SQL query using a SqlCommand object, which allows you to send SQL commands to the server. This would create the database in MSSQL with the specified settings.
First, we will include the following namespace to the top of our MDIParent form.
Imports System.Data.SqlClient
Now make the following function, and call this function within theForm_Load method:
Public Function GenerateDatabase() As Boolean
Dim connectionString As String = "Data Source=(local);Initial Catalog=master;Integrated Security=True"
Dim query As String = "CREATE DATABASE modernschools"
Using connection As SqlConnection = New SqlConnection(connectionString)
Using command As SqlCommand = New SqlCommand(query, connection)
Try
connection.Open()
command.ExecuteNonQuery()
connection.Close()
Return True
Catch ex As Exception
' Handle any exceptions that may occur during database creation
' and return false indicating failure
Return False
End Try
End Using
End Using
End Function
Private Sub MDIParent1_Load(sender As Object, e As EventArgs) Handles Me.Load
GenerateDatabase()
End Sub
In this example, the GenerateDatabase function creates a database named “modernschools” in the “master” database of the local SQL Server using the specified connection string. It uses a SqlConnection object to establish a connection to the SQL Server, and a SqlCommand object to execute the SQL query that creates the database. If the database creation is successful, the function returns True, otherwise it returns False.
You should note here that we have kept ourself silent in the exception and have not made any message to the user because the user will load the form again and again and on any next loading, our fucntion will execute the ” Catch ex As Exception ” as the database has already been created. So our message will make our user confused.
Okay, Let’s check the MSSQL Server to see if our database has been created?
Yes. That’s great our function has successfully created the database for us. You should note that we will add more and more functions in this way in the future to create tables and fields and will call these functions in Form_Load.
Now we will create a table by the name of “std_registration” within the “modernschools” and will also create all the necessary fields in this table for registering a student like name, father’s name, address,gender,remarks,class, section, date_of_birth,date_of_admission,date_of_withdrawal etc.
To create a table named “std_registration” within the “modernschools” database in MSSQL using VB.NET, we would need to follow similar steps as in creating a database: establish a connection to the database, create a SQL query to create the table, and execute the query using a SqlCommand object.
Here’s an example code snippet in VB.NET that demonstrates how you can create a function to create the “std_registration” table in the “modernschools” database:
Public Function CreateTable() As Boolean
Dim connectionString As String = "Data Source=(local);Initial Catalog=modernschools;Integrated Security=True"
Dim query As String = "CREATE TABLE std_registration (id INT PRIMARY KEY IDENTITY, name NVARCHAR(50), father_name NVARCHAR(50), address NVARCHAR(100), gender NVARCHAR(10), remarks NVARCHAR(100), class NVARCHAR(20), section NVARCHAR(10), date_of_birth DATE, date_of_admission DATE, date_of_withdrawal DATE)"
Using connection As SqlConnection = New SqlConnection(connectionString)
Using command As SqlCommand = New SqlCommand(query, connection)
Try
connection.Open()
command.ExecuteNonQuery()
connection.Close()
Return True
Catch ex As Exception
' Handle any exceptions that may occur during table creation
' and return false indicating failure
Return False
End Try
End Using
End Using
End Function
WE will call our this new function in the form Load as:
Private Sub MDIParent1_Load(sender As Object, e As EventArgs) Handles Me.Load
GenerateDatabase()
CreateTable()
End Sub
Now, let’s run our application to execute the code in the Form_Load. That’s okay! Let’s Expand the database “modernschools”, expand tables to see if the table has been created. Right click on the table “std_registration” and click Design in the pop up menu to see that all our required fields have been created successfully.
That’s great! we have completed our Part-II of this chapter. In Part-III , we will design our child Form to bind fields, view data and handle the data within this table “std_registration”.
