How To Create Table In Asp Net C#

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.

image Dynamically Create Table in SQL Server using Asp.Net C#

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>   <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>   <asp:Button ID="btCreate" Text="Create Table" runat="server"     OnClick="CreateTableInSQLSERVER_Click" CssClass="bt" />  <br /><asp:Label ID="message" runat="server"></asp:Label>   <asp:HiddenField ID="col1" runat="server" />

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();                    $('#btCreate').click(function () {             $('.input').each(function () {                 if (this.value != '')                     values.push(this.value);             });                           $('#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         {                           string sFields = col1.Value;              int iCnt = 0;             string sColumns = "";             for (iCnt = 0; iCnt <= sFields.Split(',').Length - 1; iCnt++)             {                                   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))             {                                   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                           Dim sFields As String = col1.Value              Dim iCnt As Integer = 0             Dim sColumns As String = ""             For iCnt = 0 To sFields.Split(",").Length - 1                                    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)                                    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 Next →