Create and Drop SQL Server Table pro-grammatically using ADO.Net, C#

Description:-

In this example we explain that how to create and drop table of SQL Server programmatically using ADO .Net. Or how to create runtime SQL Server table dynamically from the asp.net using C#.

Sometime we have requirement like when new user is register in our site then we create table with its name in SQL Server for the upcoming process using asp.net in C#.

So create and drop table from SQL Server through asp.net script (code) is described below

 Code:-

//Create the Employee Table Dynamically

    protected void CreateDynamicTable(object sender, EventArgs e)

    {

        string query = "IF OBJECT_ID('dbo.Employee', 'U') IS NULL "; //check condition if already exists or not

        query += "BEGIN ";

        query += "CREATE TABLE [dbo].[Employee](";

        query += "[EmoId] INT IDENTITY(1,1) NOT NULL CONSTRAINT pkEmpId PRIMARY KEY,";

        query += "[Name] VARCHAR(100) NOT NULL,";

        query += "[Country] VARCHAR(50) NOT NULL";

        query += ")";

        query += " END";

        string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString; //fetch connection string from web.config file

        using (SqlConnection con = new SqlConnection(constring))

        {

            using (SqlCommand cmd = new SqlCommand(query))

            {

                cmd.Connection = con;

                con.Open();

                cmd.ExecuteNonQuery();

                con.Close();

            }

        }

    }

    //Drop the Dynamically created Employee Table

    protected void DropDynamicTable(object sender, EventArgs e)

    {

        string query = "IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL ";

        query += "BEGIN ";

        query += "DROP TABLE dbo.Employee ";

        query += "END";

        string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString; //fetch connection string from web.config file

        using (SqlConnection con = new SqlConnection(constring))

        {

            using (SqlCommand cmd = new SqlCommand(query))

            {

                cmd.Connection = con;

                con.Open();

                cmd.ExecuteNonQuery();

                con.Close();

            }

        }

    }