CODEWITHSUNDEEP
c#sqlvisual-studio-2012arraylist
user2862361
user2862361

Reputation: 1

How to create a table in sql with fields from an arraylist in visual studio c#

I want to create an SQL table through visual studio c# and have its fields come from an arraylist. My current code looks like this.

    private void Create_Click(object sender, EventArgs e)
    {
        try
        {
            ArrayList flds = new ArrayList();
            flds.Add("test1");
            flds.Add("test2");
            conn.Open();
            cmd = new SqlCommand("CREATE TABLE [" + Tbname.Text + "] ([id] INT NULL, [name] CHAR (20) NULL, [" + flds + "] CHAR (20) NULL)", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

UPDATE: The reason Im using an arraylist is, i need the users to be able to create tables through the app (i know its not good practice) and the amount of fields may vary. (the app gets field names from users, stores them in an arraylist or any other list that'll work, then creates a table in the DB

Thanks, Rifath

Upvotes: 0

Views: 1281

Answers (2)

user2868932
user2868932

Reputation: 11

I disagree with the previous answer because the code provided is generating multiple CREATE TABLE statements with a single column and will fail on the second iteration since the table already exists.

You can use this line to see why your original code didn't work:

System.Diagnostics.Debug.WriteLine("CREATE TABLE [" + Tbname.Text + "] ([id] INT NULL, [name] CHAR (20) NULL, [" + flds + "] CHAR (20) NULL)");

You'll see from the output that you were constructing your SQL incorrectly:

CREATE TABLE [TABLE_NAME] ([id] INT NULL, [name] CHAR (20) NULL, [test1,test2] CHAR (20) NULL)

It should be:

CREATE TABLE [TABLE_NAME] ([id] INT NULL, [name] CHAR (20) NULL, [test1] CHAR (20) NULL, [test2] CHAR (20) NULL)

Here are some ways to do what you need to do. To use these examples you'll need to import the Linq namespace if it's not already:

using System.Linq;

To modify your existing code as little as possible this should work:

private void Create_Click(object sender, EventArgs e)
{
    try
    {
        ArrayList flds = new ArrayList();
        flds.Add("test1");
        flds.Add("test2");

        var columnDef = string.Join(",", flds.Select(fld => string.Format("[{0}] CHAR (20) NULL", fld)));

        conn.Open();
        cmd = new SqlCommand("CREATE TABLE [" + Tbname.Text + "] ([id] INT NULL, [name] CHAR (20) NULL, " + columnDef + ")", conn);
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

If you wanted to make some changes for readability, you could do something like:

var flds = new [] { "test1", "test2" };
...
var rawSql = "CREATE TABLE [{0}] ([id] INT NULL, [name] CHAR (20) NULL, {1})";
var rawColumnDef = "[{0}] CHAR (20) NULL";

var columnDef = string.Join(",", flds.Select(fld => string.Format(rawColumnDef, fld)));
var sql = string.Format(rawSql, Tbname.Text, columnDef);
...
cmd = new SqlCommand(sql, conn);
...

And then, just because I love the one-liner:

...
var cmd = new SqlCommand(string.Format("CREATE TABLE [{0}] ([id] INT NULL, [name] CHAR (20) NULL, {1})", 
                                       Tbname.Text, 
                                       string.Join(",", flds.Select(fld => string.Format("[{0}] CHAR (20) NULL", fld)))), conn);
...

I agree that it is generally bad practice to create tables like this, so I would recommend you consider the security implications of this approach within the context of your application;

Upvotes: 0

Steven Mays
Steven Mays

Reputation: 365

I don't quite understand what you're trying to do, and I can't comment because I don't have enough points. But I think you will have to use a FOREACH on your array...

Just FYI... Creating a table through your application is usually a HUGE no-no. You generally want to insert data into a preexisting table.

Should look something like this...

private void Create_Click(object sender, EventArgs e)
{
    try
    {
        ArrayList flds = new ArrayList();
        flds.Add("test1");
        flds.Add("test2");
        conn.Open();
Foreach Item in flds
{
        cmd = new SqlCommand("CREATE TABLE [" + Tbname.Text + "] ([id] INT NULL, [name] CHAR (20) NULL, [" + Item + "] CHAR (20) NULL)", conn);
        cmd.ExecuteNonQuery();
}
        conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Upvotes: 1

Related Questions