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 programmatically in SQL Server using C# or 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 Admin users in an application, be it e-commerce or any app that requires dynamic features.

The Markup
<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

Well that’s it. Similarly you Drop the table by using the Drop Table Table_Name statement, create Views, Procedures and other SQL Server Objects programmatically. Thanks for reading.

Related Posts:

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+
comments powered by Disqus

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