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

kevinf
 
on Jun 22, 2022 11:47 PM
Sample_137254.zip
183 Views

How do you dynamically create a mysql table with Id as the primary key and also the clustered index using VB.net 

Dim cmd as New MySqlCommand()
cmd.CommandText = "CREATE Table 'lessonwork" & WkPw & "'("
cmd.CommandText &= "id int CONSTRAINT NOT NULL,"
cmd.CommandText &= "FileId int DEFAULT NULL,"
cmd.CommandText &= "Topic varchar(50)DEFAULT NULL,"
cmd.CommandText &= "IImage longblob,"
cmd.CommandText &= "UseImage tinyint(1) DEFAULT NULL,"
cmd.CommandText &= "PrintOk tinyint(1) DEFAULT NULL,"
cmd.CommandText &= "ImageDesc char(100) DEFAULT NULL,"
cmd.CommandText &= "ImageHead longblob,"
cmd.CommandText &= "PRIMARY KEY(id));"
cmd.CommandText &= "ClusteredIndex-" & Now.ToString("yyyyMMddHHmmdss") & "(ID));"
Dim conn As New MySqlConnection(ConnString)
conn.Open() 'Opens the connection
cmd.Connection = conn 'Instructs the cmd object to use conn as its connection when executing
cmd.ExecuteScalar()

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 24, 2022 04:13 AM
on Jun 24, 2022 04:13 AM

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