Creating Database and Tables Dynamically

The code can be incorporated into either a Web Application or Windows application.

First decide the name of the database to be created. Then place all DDL and DML commands related to tables in the database into a text file.

The following is the content of SQLSCRIPT.SQL file, which contains CREATE TABLE command to create a table and a few INSERT commands.

create table subjects
( code  varchar(5) primary key,
  name  varchar(20)
);

insert into subjects values('asp','ASP.NET');
insert into subjects values('vb','VB.NET');

Write the following code to create database first and then run the script in SQLSCRIPT.SQL file.

Imports System.Data.SqlClient
Imports System.IO

Module Module1

    Sub Main()
        CreateDatabase("testdb1")
        CreateTables("testdb1", "sqlscript.sql")
    End Sub


    Private Sub CreateDatabase(ByVal DatabaseName As String)
        Dim con As New SqlConnection("uid=sa;database=master")
        con.Open()
        Dim Cmd As New SqlCommand("create database " & DatabaseName, con)
        Try
            Cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Private Sub CreateTables(ByVal dbname As String, ByVal filename As String)
        Dim con As New SqlConnection("uid=sa;database=" & dbname)
        Dim cmdstr As String
        con.Open()

        'get the content from the file
        Dim file As New StreamReader(filename)
        cmdstr = file.ReadToEnd()

        Dim cmd As New SqlCommand(cmdstr, con)

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub
End Module

In the code, we first connect to MASTER database to create a new database. Then we connect to the database that was created in the first step and run the script to execute commands in the script.

Keep Learning, P.Srikanth.