Adding columns to database table

[Oli]

Hello all,

I'm writing a program that reads an Xml file and updates/patches a database
with the information in the Xml file, e.g. add rows and columns to database
tables, and add constraints to tables.
I decided on using DataSet and OleDbDataAdapter, and there is no problem
adding new database rows to existing database tables. Adding new columns to
an existing database table is not working, could you please read the
following code snippet and tell me what I'm doing wrong.

m_oleDbConn = new OleDbConnection (strConn);
// Create the Data Adapter.
OleDbDataAdapter daPatch = new OleDbDataAdapter ("SELECT * FROM patch",
m_oleDbConn);
OleDbCommandBuilder cbPatch = new OleDbCommandBuilder (daPatch);
m_oleDbConn.Open ();

DataSet ds = new DataSet ();
string strDbTable = "patch";
daPatch.FillSchema (ds, SchemaType.Source, strDbTable);
daPatch.Fill (ds, strDbTable);

DataTable tPatch = ds.Tables[strDbTable];
if (tPatch != null) {
// Check if the patch database table has the "newcol" column.
DataColumn colGrp = tPatch.Columns["newcol"];
if (colGrp == null) {
// Add the "newgrp" column to the patch database table.
colGrp = new DataColumn ();
colGrp.ColumnName = "newcol";
colGrp.DataType = Type.GetType ("System.Decimal");
colGrp.AllowDBNull = true;
colGrp.Caption = "newcol";
tPatch.Columns.Add (colGrp);
System.Diagnostics.Trace.WriteLine (tPatch.Columns.Count.ToString ());
}

// Add rows
DataRow dr;
for (int i = 0; i < 5; i++) {
dr = tPatch.NewRow ();
dr["col1"] = "test" + i.ToString ();
dr["newcol"] = i + 1;
tPatch.Rows.Add (dr);
}

// Merge/Update the data adapter changes with the data source.
daPatch.Update (ds, strDbTable);
//ds.Merge (tPatch);

}

m_oleDbConn.Close ();

Best regards,
Oli

 

[Miha Markic]

Hi Oli,

You can't add columns in that way.
You will have to either use database specific sql commands or ADOX...

 

[Steven Cheng[MSFT]]

Hi Oli,


Thanks for posting in the community! My name is Steven, and I'll be
assisting you on this issue.
From your description, you are wanting to use ADO.NET (especially the
OleDbClient components) to do some database manipulations. You tried add
datarows and columns in the DataSet's DataTable and update the DataSet to
the Database. However, you found that the rows could be added, but you
can't add columns through this way, yes?
If there is anything I misunderstood, please feel free to let me know.


As for this problem, here is my suggestions:
The ADO.NET components provide two mode of database manipulation. One is
use Command object to execute sql statement. Another is use DataSet to
mantain a local memory database and can be update to server as a whole
DataBase entity such as a DataTable. However, such update mode is "records
based". In other words, we can add or remove records from a certain
DataTable through this way( add some new rows in the certain DataTable).
But we are not able to modify the Database or a certain Table's
structure(such as add or delete a column of a DataTable and update it use
DataAdaptor.Update method). So as for your situation, if you add some
DataColumns in a certain DataTable in the local DataSet and update it to
server, it won't be able to modify the actual table's structure in the DBMS.

However, if you do you want use ADO.NET component to modify the data
table's structure on the db server. You may need to use the Command object
to execute sql statement just as Miha Markic has suggested. For example:
----------------------------------------
sqlConnection1.Open();

// add a column into a table
cmdText = "ALTER TABLE mytable ADD description char(200) ";
SqlCommand addColumnCmd = new SqlCommand ( cmdText, sqlConnection1 );
addColumnCmd.ExecuteNonQuery();

//delete a column from a table
cmdText = "ALTER TABLE mytable DROP COLUMN description"
SqlCommand dropColumnCmd = new SqlCommand ( cmdText, sqlConnection1 );
dropColumnCmd.ExecuteNonQuery();


sqlConnection1.Close();
------------------------------------------------------

Thus, we can actually modify the table's structure in the database server.

Please check out the preceding suggestions. If you have any questions,
please feel free to post here.



Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Oli,


Have you had a chance to view my suggestion or have you got any ideas on
this issue? If you need any further assistance, please feel free to let me
know.


Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)