CODEWITHSUNDEEP
c#mysqldatabasesqlite
ielaajez
ielaajez

Reputation: 81

SQLite, create a table with a variable name in C#

I searched for an answer but the only solutions I found didn't work for SQLite. I want to create different tables in my SQLite database with the name and columns defined by the user who creates the table.

I'm working on a project where users can test their knowledge of a language by creating a list that can be tested later. The list should be saved in a database as an apart table.

Image of the UI

So I want to save each list in the database with the table name equal to the "txtNaamLijst.Text" textbox. Beside that I want the columns to have the name of "txtTaal1.Text" en "txtTaal2.Text" as defined by the user.

The script that I have written doesn't work. Scripts I found on the web did not work either. I have to write my SQLite commands in a single line because I use C#: 

protected void btnSave_Click(object sender, EventArgs e)
{
    using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=C:/Users/elias/Documents/Visual Studio 2017/WebSites/WebSite7/App_Data/overhoren.db"))
    {
        using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
        {
            string naam = txtNaamLijst.Text;
            string taal1 = txtTaal1.Text;
            string taal2 = txtTaal2.Text;
            string veld1 = txtVeld1.Text;
            string veld1v2 = txtVeld1v2.Text;
            string veld2 = txtVeld2.Text;
            string veld2v2 = txtVeld2v2.Text;
            string veld3 = txtVeld3.Text;
            string veld3v2 = txtVeld3v2.Text;
            string veld4 = txtVeld4.Text;
            string veld4v2 = txtVeld4v2.Text;
            string veld5 = txtVeld5.Text;
            string veld5v2 = txtVeld5v2.Text;
            string veld6 = txtVeld6.Text;
            string veld6v2 = txtVeld6v2.Text;

            conn.Open();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Parameters.AddWithValue("@naam", naam);
            cmd.Parameters.AddWithValue("@taal1", taal1);
            cmd.Parameters.AddWithValue("@taal2", taal2);
            cmd.Parameters.AddWithValue("@veld1", veld1);
            cmd.Parameters.AddWithValue("@veld1v2", veld1v2);
            cmd.Parameters.AddWithValue("@veld2", veld2);
            cmd.Parameters.AddWithValue("@veld2v2", veld2v2);
            cmd.Parameters.AddWithValue("@veld3", veld3);
            cmd.Parameters.AddWithValue("@veld3v2", veld3v2);
            cmd.Parameters.AddWithValue("@veld4", veld4);
            cmd.Parameters.AddWithValue("@veld4v2", veld4v2);
            cmd.Parameters.AddWithValue("@veld5", veld5);
            cmd.Parameters.AddWithValue("@veld5v2", veld5v2);
            cmd.Parameters.AddWithValue("@veld6", veld6);
            cmd.Parameters.AddWithValue("@veld6v2", veld6v2);

            cmd.CommandText = "CREATE TABLE IF NOT EXISTS @naam (@taal1 VARCHAR(50), @taal2, VARCHAR(50))";
            cmd.CommandText = "INSERT INTO @naam (@taal1, @taal2) SELECT @veld1 , @veld1v2 UNION ALL SELECT @veld2 , @veld2v2 UNION ALL SELECT @veld3 , @veld3v2 UNION ALL SELECT @veld4 , @veld4v2 UNION ALL SELECT @veld5 , @veld5v2 UNION ALL SELECT @veld6 , @veld6v2";

            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            conn.Close();`
        }
    }
}

Upvotes: 0

Views: 2830

Answers (1)

steliosbl
steliosbl

Reputation: 8921

In both your queries you use @naam as the parameter for the table name.

cmd.CommandText = "CREATE TABLE IF NOT EXISTS @naam..."
cmd.CommandText = "INSERT INTO @naam..."
cmd.Parameters.AddWithValue("@naam", naam);

Unfortunately, parameterizing table names is not possible. User Soner Gönül put it well in this answer. He has quite a bit of information but the gist of it is:

You can not parameterize your table names, column names or any other databse objects. You can only parameterize your values.

Now, it is possible to use plain old string concentration/formatting to have a variable table name, but that is NOT a good idea, as it leaves you vulnerable to SQL injection.

Upvotes: 2

Related Questions