Dynamically Create MySQL Table from code behind in ASP.Net using C# and VB.Net

Hi kevinf,

Refer below sample code.

HTML

<asp:TextBox ID="txtCustomerId" runat="server" Text="2" />
<asp:Button Text="Create" runat="server" OnClick="OnCreate" />

Namespaces

C#

using System.Configuration;
using MySql.Data.MySqlClient;

VB.Net

Imports System.Configuration
Imports MySql.Data.MySqlClient

Code

C#

protected void OnCreate(object sender, EventArgs e)
{
    string WkPw = txtCustomerId.Text.Trim();
    string connString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.CommandText = string.Format("CREATE Table `lessonwork{0}`(", WkPw);
        cmd.CommandText += "`id` int NOT NULL,";
        cmd.CommandText += "`FileId` int DEFAULT NULL,";
        cmd.CommandText += "`IImage` longblob,";
        cmd.CommandText += "`Ranking` int DEFAULT NULL,";
        cmd.CommandText += "`LessonNo` decimal(5,1) DEFAULT NULL,";
        cmd.CommandText += "`TopicReason` varchar(100) DEFAULT NULL,";
        cmd.CommandText += "`IImagePath` varchar(250) DEFAULT NULL,";
        cmd.CommandText += "PRIMARY KEY(`id`),";
        cmd.CommandText += "KEY `ClusteredIndex-" + DateTime.Now.ToString("yyyyMMddHHmmdss") + "` (`ID`));";
        using (MySqlConnection conn = new MySqlConnection(connString))
        {
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteScalar();
            conn.Close();
        }
    }
}

VB.Net

Protected Sub OnCreate(sender As Object, e As EventArgs)
    Dim WkPw As String = txtCustomerId.Text.Trim()
    Dim connString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using cmd As MySqlCommand = New MySqlCommand()
        cmd.CommandText = String.Format("CREATE Table `lessonwork{0}`(", WkPw)
        cmd.CommandText += "`id` int NOT NULL,"
        cmd.CommandText += "`FileId` int DEFAULT NULL,"
        cmd.CommandText += "`IImage` longblob,"
        cmd.CommandText += "`Ranking` int DEFAULT NULL,"
        cmd.CommandText += "`LessonNo` decimal(5,1) DEFAULT NULL,"
        cmd.CommandText += "`TopicReason` varchar(100) DEFAULT NULL,"
        cmd.CommandText += "`IImagePath` varchar(250) DEFAULT NULL,"
        cmd.CommandText += "PRIMARY KEY(`id`),"
        cmd.CommandText += "KEY `ClusteredIndex-" & DateTime.Now.ToString("yyyyMMddHHmmdss") & "` (`ID`));"
        Using conn As MySqlConnection = New MySqlConnection(connString)
            cmd.Connection = conn
            conn.Open()
            cmd.ExecuteScalar()
            conn.Close()
        End Using
    End Using
End Sub

Screenshot