Programmatically Create a Table in SQL Server using Asp.Net C# and Vb.Net

If you have worked with SQL Server Database, then I am sure you know how to create a table, add columns, keys etc. In SQL Server, either you use a Create Statement to create a table or you might use the Management Studio. Web developers using Asp.Net will find this article useful, since I am sharing an example on how to create a Table dynamically in SQL Server using Asp.Net C# and Vb.Net

You can execute any SQL Server statement programmatically using ADO.Net methods and properties. This feature comes in handy when you want your users to dynamically create tables, add columns etc. Of course, your users do not see what happens behind the scene. Only you, as a developer, know this.

Usually, this feature is available to Admins in an application, be it e-commerce or any app that requires dynamic features.

The Markup

On my web page I have three textboxes and a button control. I also have a hidden field, which will store values extracted from the textboxes (See the Script). The code behind procedure will get table and columns names from the hidden field.

imageDynamically Create Table in SQL Server using Asp.Net C#

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>

    <%-- ADD TEXTBOXES FOR THE TABLE NAME AND FEW COLUMNS. --%>
    <asp:TextBox ID="tbTable" placeholder="Enter Table Name" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbCol1" CssClass="input" placeholder="Enter Column Name" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbCol2" CssClass="input" placeholder="Enter Column Name" runat="server"></asp:TextBox>

    <%--BUTTON TO CALL CODE BEHIND PROCEDURE TO THE CREATE THE TABLE. --%>
    <asp:Button ID="btCreate" Text="Create Table" runat="server"
        OnClick="CreateTableInSQLSERVER_Click" CssClass="bt" />

    <br /><asp:Label ID="message" runat="server"></asp:Label>

    <%--A HIDDEN TO HOLD THE COLUMN NAMES. --%>
    <asp:HiddenField ID="col1" runat="server" />
</div>
The Script

Since I am creating the SQL Server table programmatically, the textboxes will provide me with the column names. The jQuery script will help me extract the values from the textboxes and store it in a hidden field.

<script>
    $(document).ready(function () {
        BindControls();
    });

    function BindControls() {
        var values = new Array();

        // GET VALUES FROM THE TEXTBOXES (FOR COLUMNS).

        $('#btCreate').click(function () {
            $('.input').each(function () {
                if (this.value != '')
                    values.push(this.value);
            });

            // ASSIGN VALUES TO THE HIDDEN FIELD.
            $('#col1').val(values);
        });
    }
</script>
Code Behind (C#)
using System;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    public void CreateTableInSQLSERVER_Click(object sender, EventArgs e)
    {
        string sConnString = "Data Source=DNA;Persist Security Info=False;" +
           "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";

        message.Attributes.Add("style", "border:none;font:14px Verdana;");
        message.Text = "";

        try
        {
            // EXTRACT VALUES (FOR THE COLUMNS) FROM THE HIDDEN FIELD.

            string sFields = col1.Value;

            int iCnt = 0;
            string sColumns = "";
            for (iCnt = 0; iCnt <= sFields.Split(',').Length - 1; iCnt++)
            {
                // CREATE COLUMNS AND ASSIGN DataTypes.
                // (YOU CAN PASS THE DataTypes TOO. SIMPLY ADD A DROPDOWN 
                // LIST NEXT TO EACH TEXTBOX FOR COLUMNS, WITH PRE-DEFINED TYPES.)

                if (string.IsNullOrEmpty(sColumns))
                {
                    sColumns = "[" + sFields.Split(',')[iCnt].Replace(" ", "") + "] VARCHAR (100)";
                }
                else
                {
                    sColumns = sColumns + ", [" + sFields.Split(',')[iCnt].Replace(" ", "") + "] VARCHAR (100)";
                }
            }

            using (SqlConnection con = new SqlConnection(sConnString))
            {
                // CREATE TABLE STRUCTURE USING THE COLUMNS AND TABLE NAME.

                string sQuery = null;
                sQuery = "IF OBJECT_ID('dbo." + tbTable.Text.Replace(" ", "_") + "', 'U') IS NULL " +
                    "BEGIN " +
                    "CREATE TABLE [dbo].[" + tbTable.Text.Replace(" ", "_") + "](" +
                    "[" + tbTable.Text.Replace(" ", "_") + "_ID" + "] INT IDENTITY(1,1) NOT NULL CONSTRAINT pk" +
                        tbTable.Text.Replace(" ", "_") + "_ID" + " PRIMARY KEY, " +
                    "[CreateDate] DATETIME, " + 
                        sColumns + ")" +
                    " END";

                using (SqlCommand cmd = new SqlCommand(sQuery))
                {
                    cmd.Connection = con;
                    con.Open();

                    cmd.ExecuteNonQuery();
                    con.Close();

                    message.Text = "Table created successfuly.";
                    message.ForeColor = System.Drawing.Color.Green;
                }
            }
        }
        catch (Exception ex)
        {
            message.Text = "There was an error.";
            message.ForeColor = System.Drawing.Color.Green;
        }
        finally
        { }

    }
}
Code Behind (Vb.Net)
Option Explicit On
Imports System.Data.SqlClient

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Sub CreateTableInSQLSERVER_Click(ByVal sender As Object, ByVal e As EventArgs)

        message.Attributes.Add("style", "border:none;font:14px Verdana;")
        message.Text = ""

        Dim sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"

        Try
            ' EXTRACT VALUES (FOR THE COLUMNS) FROM THE HIDDEN FIELD.

            Dim sFields As String = col1.Value

            Dim iCnt As Integer = 0
            Dim sColumns As String = ""
            For iCnt = 0 To sFields.Split(",").Length - 1

                ' CREATE COLUMNS AND ASSIGN DataTypes.
                ' (YOU CAN PASS THE DataTypes TOO. SIMPLY ADD A DROPDOWN 
                ' LIST NEXT TO EACH TEXTBOX FOR COLUMNS, WITH PRE-DEFINED TYPES.)

                If Trim(sColumns) = "" Then
                    sColumns = "[" & Replace(sFields.Split(",")(iCnt), " ", "") & "] VARCHAR (100)"
                Else
                    sColumns = sColumns & ", [" & Replace(sFields.Split(",")(iCnt), " ", "") & "] VARCHAR (100)"
                End If
            Next

            Using con As SqlConnection = New SqlConnection(sConnString)

                ' CREATE TABLE STRUCTURE USING THE COLUMNS AND TABLE NAME.

                Dim sQuery As String
                sQuery = "IF OBJECT_ID('dbo." & Replace(Trim(tbTable.Text), " ", "_") & "', 'U') IS NULL " & _
                    "BEGIN " & _
                    "CREATE TABLE [dbo].[" & Replace(Trim(tbTable.Text), " ", "_") & "](" & _
                    "[" & Replace(Trim(tbTable.Text), " ", "_") & "_ID" & "] INT IDENTITY(1,1) NOT NULL CONSTRAINT pk" & _
                        Replace(Trim(tbTable.Text), " ", "_") & "_ID" & " PRIMARY KEY, " & _
                    "[CreateDate] DATETIME, " & _
                    sColumns & _
                    ")" & _
                    " END"

                Using cmd As SqlCommand = New SqlCommand(sQuery)
                    With cmd
                        .Connection = con
                        con.Open()

                        cmd.ExecuteNonQuery()
                        con.Close()

                        message.Text = "Table created successfuly."
                        message.ForeColor = Drawing.Color.Green
                    End With
                End Using
            End Using
        Catch ex As Exception
            message.Text = "There was an error."
            message.ForeColor = Drawing.Color.Red
        Finally
            '
        End Try
    End Sub
End Class

Similarly you can Drop the table by using SQL Server Drop Table Table_Name statement. In-addition, using a similar method you can create Views, Procedures and other SQL Server Objects programmatically.

That’s it. Thanks for reading.

Previous - How to Convert Date From “dd/MM/yyyy” Format to “MM/dd/yyyy” in Asp.Net C# and Vb.NetNext - Extract Data using Asp.Net Web API C# and jQuery Ajax and Display the Data with Paging



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!