MiloDCooper
asked on
How do I update a DataSource with new columns?
I am adding columns to a DataTable like so:
DataColumn newColumn = new DataColumn("NEW_COLUMN", typeof(bool));
newColumn.DefaultValue = false;
newColumn.AllowDBNull = false;
Table.Columns.Add(newColum n);
According to what I see in my UI, this works just fine. The DataGridView that I have bound to my DataTable Table gets updated with a new column as expected.
Later, I update the DataSource thusly:
adapter.Update(Table);
adapter is an SqlDataAdapter.
When I close my application then look at my DataTable again, the columns that I added are not there. Likewise, any columns that I removed are still there.
The only changes that persist are the addition and deletion of rows, and the changing of data in any cells of columns that were neither added nor deleted.
I suspect that the adding of columns is strictly an ALTER operation, not an INSERT, DELETE, or UPDATE operation, and I'm trying to find out how I can add ALTER functionality to my adapter.
Or maybe I'm on the wrong track entirely.
Any suggestions, out there?
DataColumn newColumn = new DataColumn("NEW_COLUMN", typeof(bool));
newColumn.DefaultValue = false;
newColumn.AllowDBNull = false;
Table.Columns.Add(newColum
According to what I see in my UI, this works just fine. The DataGridView that I have bound to my DataTable Table gets updated with a new column as expected.
Later, I update the DataSource thusly:
adapter.Update(Table);
adapter is an SqlDataAdapter.
When I close my application then look at my DataTable again, the columns that I added are not there. Likewise, any columns that I removed are still there.
The only changes that persist are the addition and deletion of rows, and the changing of data in any cells of columns that were neither added nor deleted.
I suspect that the adding of columns is strictly an ALTER operation, not an INSERT, DELETE, or UPDATE operation, and I'm trying to find out how I can add ALTER functionality to my adapter.
Or maybe I'm on the wrong track entirely.
Any suggestions, out there?
AcceptChanges is to accept the modifications done on the Rows of the DataTable. It doesn't actually create/remove column from the DB Table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The SqlDataAdapter can not create/delete columns. It's used for sending and retrieving data. You will have to do this with custom SqlCommand queryies or like Novice_Novice says with SMO.
ASKER
OK so I'm looking into the SMO stuff, as advised by Novice_Novice.
The code I've got currently is:
SqlConnection connection = new SqlConnection(CONNECTION_S TRING);
Server server = new Server(new ServerConnection(connectio n));
Database db = server.Databases["my_db"];
Table tbl = db.Tables["my_table"];
Column newColumn = new Column(tbl, "NEW_COLUMN", DataType.Bit);
newColumn.Nullable = false;
tbl.Columns.Add(newColumn) ;
... after which I do a complete refresh on my original DataTable (i.e. reconnect to server and fill the DataTable again), whereupon I am still seeing no change to the table.
I set a breakpoint just after tbl.Columns.Add(newColumn) , and tbl.Columns does indeed show NEW_COLUMN.
I tried tbl.Alter() and tbl.Refresh(), the first throws an exception and the second is seemingly inert.
Ideas?
The code I've got currently is:
SqlConnection connection = new SqlConnection(CONNECTION_S
Server server = new Server(new ServerConnection(connectio
Database db = server.Databases["my_db"];
Table tbl = db.Tables["my_table"];
Column newColumn = new Column(tbl, "NEW_COLUMN", DataType.Bit);
newColumn.Nullable = false;
tbl.Columns.Add(newColumn)
... after which I do a complete refresh on my original DataTable (i.e. reconnect to server and fill the DataTable again), whereupon I am still seeing no change to the table.
I set a breakpoint just after tbl.Columns.Add(newColumn)
I tried tbl.Alter() and tbl.Refresh(), the first throws an exception and the second is seemingly inert.
Ideas?
ASKER
P.S. The exception thrown by tbl.Alter() is "Alter failed for Table 'dbo.my_table'."
ASKER
Got it working with the following code:
SqlConnection connection = new SqlConnection(CONNECTION_S TRING);
Server server = new Server(new ServerConnection(connectio n));
Database db = server.Databases["my_db"];
Table tbl = db.Tables["my_table"];
Column newColumn = new Column(tbl, "NEW_COLUMN", DataType.Bit);
newColumn.Nullable = false;
newColumn.AddDefaultConstr aint();
newColumn.DefaultConstrain t.Text = "''";
tbl.Columns.Add(newColumn) ;
tbl.Alter();
SqlConnection connection = new SqlConnection(CONNECTION_S
Server server = new Server(new ServerConnection(connectio
Database db = server.Databases["my_db"];
Table tbl = db.Tables["my_table"];
Column newColumn = new Column(tbl, "NEW_COLUMN", DataType.Bit);
newColumn.Nullable = false;
newColumn.AddDefaultConstr
newColumn.DefaultConstrain
tbl.Columns.Add(newColumn)
tbl.Alter();
DataTableName.AcceptChange
after making modifications in the DataTable.
Hope it helps.