Creating Table

问题:

My friend needs help but because I cannot help him. I refer here to ask your opinion.

He wants to create a new table in the database (his using vb.net, mysql). The tricky thing is that he wants to name the new table from the the input being encoded into a textbox.

Is that possible? To create and name a new table from the input in the textbox.

Hope you could help me. Sorry for my grammar.

回答1:

@njm you can create one string variable having query of "Create table <name>(field1,field2....)" In above query must be replace with your input text box.

And pass this string to mysql command variable having its own connection ... And simply execute this query...

回答2:

This code should work....

Add one button to the form for testing purpose and place the following code to its click event

Imports MySql.Data.MySqlClient

'place this in the class.............
Dim myConnectionString = My.Settings.MySQL_DBConnectionString
Dim con As New MySqlConnection(myConnectionString)

'Code on the button...........

    Dim createSql As String
    Try
        con = New MySqlConnection(myConnectionString)
        con.Open()
        Dim tblname = "anyInputName"
        createSql = "CREATE TABLE " & tblname & " (id INT(6) NOT NULL AUTO_INCREMENT,otherField TEXT NOT NULL,PRIMARY KEY (id));"
        Dim cmd As New MySqlCommand(createSql, con)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        con.Close()
    End Try

Now go experimenting.......

回答3:

Certainly it can be done.

Basically you build a string witha Create Table statement in it and then execute it.

e.g. someSql "Create Table " + EditBox1.Text + "(.....)"

BUT

Building sql from user input opens you to sql injection attacks.

Legal identifiers, certain characters can't be used, some must be escaped, the name may already be in use...

Once you have the table, how is the code going to use it, to know that there is a table called "MyTable" in the database, why it's there...

You wouldn't normally just hand out create table permissions.

What about dependencies (relations, stored procs, contraints etc)

There are several ways of dealing with this, one is to do table creation in another app, build up rules and store meta information somehow that the code can use to use the table.

Another for simpler apps, is to give the table some unique name in the db and then use the the name entered by the user as an alias for it, so Show("MyTable") gets mapped to Show("UserTable1876")

Another possibility is if the table has definite common structure and it's just user Fred's copy of it is to add another column to the table and then simply add Fred's UserID when any CRUD functions are used.

So yes it can be done, should it? Depends....