In this article I will explain how to create and drop (delete) SQL Server Table programmatically using ADO.Net, C# and VB.Net.
The Create and Drop script will be dynamically generated and the Table will be respectively created or deleted in SQL Server database.
 
 
Connection String
You will need to define a connection string to the SQL Server database in which you need to create or drop (delete) Table programmatically.
<connectionStrings>
    <add name="constr" connectionString="Data Source=.\SQL2008R2;Initial Catalog=Samples;Integrated Security=true"/>
</connectionStrings>
 
 
HTML Markup
The following HTML Markup consists of two Buttons, one for creating while other for dropping (deleting) the SQL Server Table programmatically.
<asp:Button ID = "btnCreateTable" Text="Create Table" runat="server" OnClick = "CreateTable" />
<asp:Button ID = "btnDropTable" Text="Drop Table" runat="server" OnClick = "DropTable" />
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
 
Create SQL Server Table programmatically using C# and VB.Net
The following event handler creates the SQL Server Table programmatically. The SQL script to create the Table is dynamically built and the executed using ADO.Net.
Note: Here CustomerId is an Identity (Auto increment) column and is also specified as Primary key.
The Script first checks whether a Table with specified name exists in the database, if it does not exists then the Table is created.
C#
protected void CreateTable(object sender, EventArgs e)
{
    string query = "IF OBJECT_ID('dbo.CustomersTest', 'U') IS NULL ";
    query += "BEGIN ";
    query += "CREATE TABLE [dbo].[CustomersTest](";
    query += "[CustomerId] INT IDENTITY(1,1) NOT NULL CONSTRAINT pkCustomerId PRIMARY KEY,";
    query += "[Name] VARCHAR(100) NOT NULL,";
    query += "[Country] VARCHAR(50) NOT NULL";
    query += ")";
    query += " END";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
 
VB.Net
Protected Sub CreateTable(sender As Object, e As EventArgs)
    Dim query As String = "IF OBJECT_ID('dbo.CustomersTest', 'U') IS NULL "
    query += "BEGIN "
    query += "CREATE TABLE [dbo].[CustomersTest]("
    query += "[CustomerId] INT IDENTITY(1,1) NOT NULL CONSTRAINT pkCustomerId PRIMARY KEY,"
    query += "[Name] VARCHAR(100) NOT NULL,"
    query += "[Country] VARCHAR(50) NOT NULL"
    query += ")"
    query += " END"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query)
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub
 
 
Drop SQL Server Table programmatically using C# and VB.Net
The following event handler drops (deletes) the SQL Server Table programmatically. The SQL script to drop (delete) the Table is dynamically built and the executed using ADO.Net.
The Script first checks whether a Table with specified name exists in the database, if it exists then the Table is created.
C#
protected void DropTable(object sender, EventArgs e)
{
    string query = "IF OBJECT_ID('dbo.CustomersTest', 'U') IS NOT NULL ";
    query += "BEGIN ";
    query += "DROP TABLE dbo.CustomersTest ";
    query += "END";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
 
VB.Net
Protected Sub DropTable(sender As Object, e As EventArgs)
    Dim query As String = "IF OBJECT_ID('dbo.CustomersTest', 'U') IS NOT NULL "
    query += "BEGIN "
    query += "DROP TABLE dbo.CustomersTest "
    query += "END"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query)
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub
 
 
Screenshot
Create and Drop SQL Server Table programmatically using ADO.Net, C# and VB.Net
 
 
Downloads
Download Code