How Can I Insert New Column Into A Database Table Using SqlDataAdapter and DataTable?

Question

In my .NET program, I want to modify database table's structure by inserting new column. But I do NOT want to do it by running "ALTER TABLE ....." command on database by ADO.NET. I want to do it by adding new column to my datatable. So when I update the associated tableAdapter, physical table structure is going to change.

The code, which I expected to work was like this:

        SqlCommand cmd = new SqlCommand("SELECT * FROM EMPLOYEE", conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        dt.Columns.Add(new DataColumn("BirthDate"));
        adapter.Update(dt);

But it did not.

If it is possible in any way, how can I do it?

Answer 1

You cannot do this.

The .NET datatable is only a representation of the on-disk database structures - there's no functionality in the ADO.NET DataTable code to support generating or updating the underlying tables when you change the structure of your DataTable.

You will have to use ALTER TABLE ..... SQL statement to achieve what you're looking for - no way around that, I'm afraid.