?
Solved

How do I update a DataSource with new columns?

Posted on 2010-09-15
7
Medium Priority
?
367 Views
Last Modified: 2012-08-14
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(newColumn);

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?
0
Comment
Question by:MiloDCooper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Expert Comment

by:Kaushal Arora
ID: 33679750
try using the command

DataTableName.AcceptChanges();

after making modifications in the DataTable.

Hope it helps.
0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33679784
AcceptChanges is to accept the modifications done on the Rows of the DataTable. It doesn't actually create/remove column from the DB Table.
0
 
LVL 8

Accepted Solution

by:
Gururaj Badam earned 500 total points
ID: 33679808
Using SMO may help generate table creations script to be used against the Database or you may use SQLCommand and write your own creation script

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:existenz2
ID: 33679825
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.
0
 

Author Comment

by:MiloDCooper
ID: 33680088
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_STRING);
      Server server = new Server(new ServerConnection(connection));
      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?
0
 

Author Comment

by:MiloDCooper
ID: 33680136
P.S. The exception thrown by tbl.Alter() is "Alter failed for Table 'dbo.my_table'."
0
 

Author Comment

by:MiloDCooper
ID: 33680175
Got it working with the following code:

      SqlConnection connection = new SqlConnection(CONNECTION_STRING);
      Server server = new Server(new ServerConnection(connection));
      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.AddDefaultConstraint();
      newColumn.DefaultConstraint.Text = "''";
      tbl.Columns.Add(newColumn);
      tbl.Alter();
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question