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
<script src="https://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" />
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 ClassSimilarly 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.