Changing a DataTables Schema

  • Thread starter Thread starter Jon Brunson
  • Start date Start date
J

Jon Brunson

Is it possible to use a DataAdapter to fill a DataTable, change the
DataColumns of that DataTable (and maybe even it's name) and then commit
those changes to the database (in my case SQL Server 2000)?

Eg:

[VB.NET]

Dim dc As New SqlCommand("SELECT TOP 0 * FROM SomeTable",
SomeSqlConnection)
dc.CommandType = CommandType.Text

Dim da As New SqlDataAdapter(dc)
Dim dt As New DataTable
da.FillSchema(dt, SchemaType.Source)

dt.TableName = "NewTableName"
dt.Columns("SomeColumn").ColumnName = "NewName"
dt.Columns("AnotherColumn").DataType = GetType(String)
dt.Columns.Add("NewColumn", GetType(Integer))

da.Update(dt)


[C#]

SqlCommand dc = new SqlCommand("SELECT TOP 0 * FROM SomeTable",
SomeSqlConnection);
dc.CommandType = CommandType.Text;

SqlDataAdapter da = new SqlDataAdapter(dc);
DataTable dt = new DataTable();
da.FillSchema(dt, SchemaType.Source);

dt.TableName = "NewTableName";
dt.Columns["SomeColumn"].ColumnName = "NewName";
dt.Columns["AnotherColumn"].DataType = Type.GetType("System.String");
dt.Columns.Add("NewColumn", Type.GetType("System.Integer"));

da.Update(dt);
 
Jon,

Yes however not with the result you want.

Only the names that are not changed will be updated.

I think you want to change the database names with this what is not
possible.

I hope this helps anyway?

Cor
 
Jon,

It is possible, but you will have to change the Update, Insert, and
DeleteCommand properties to reflect the commands to perform the associated
operations on the other table that you want to update.

Also, you have to make sure that whatever changes you make in the
dataset (as far as data, not schema) have to make sense in the new table you
want to update (for example, an edit of a row needs to have a pre-existing
row).

Hope this helps.
 
So to confirm:

I *can* change the name of a table in a database by "downloading" (with
a DataAdapter) it into a DataTable, changing the TableName property, and
"uploading" it back to the database (using the same DataAdapter's
Update() method)

I *can* add new columns to said table, and have those "uploaded" into
the database as well

I *can* rename existing columns in the table, and have them renamed in
the database

I *can* change the data type of a column in the table, and have that
reflected in the database

If so, how? As the code I orginally posted does not work.
 
Jon,

I think you misunderstand. See inline:
I *can* change the name of a table in a database by "downloading" (with a
DataAdapter) it into a DataTable, changing the TableName property, and
"uploading" it back to the database (using the same DataAdapter's Update()
method)
Click to expand...

You can change the name of the data set/data table on the client side.
This has no effect on the server side. If you change the table name, then
you have to change the data adapter so that it recognizes the new table you
are trying to update. You can call Update again, but it will fail because
the table mapping is off (I believe). Also, the new columns will be
ignored. If you want to update the data into another table, then that table
must already exist, and have a schema compatable with the changes you have
made to your data table.
I *can* add new columns to said table, and have those "uploaded" into the
database as well

I *can* rename existing columns in the table, and have them renamed in the
database

I *can* change the data type of a column in the table, and have that
reflected in the database
Click to expand...

The changes you make are to the client side data set only. Any changes
you make to that do not affect the underlying DB. You will have to issue
DB-specific commands in order to modify table structures in the DB itself.
ADO.NET does not provide this for you.
 
So basically I need to do it all with "ALTER TABLE" queries?

Yes however that is very easy to do, this is OledB the only difference is
that you for that where is OleDb.OleDb have to place SQLClient.Sql and
another connectionstring.

I hope this helps?

Cor

\\\
Public Class clsUpdate
Public Sub New()
Dim conn As New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="C:\myAcces.mdb")
conn.Open()
Dim cmd As New OleDb.OleDbCommand("ALTER TABLE Persons " & _
"ADD myText text", conn)
doCmd(cmd)
conn.Close()
End Sub
Private Sub doCmd(ByVal cmd As Data.OleDb.OleDbCommand)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
If ex.ErrorCode = -2147217887 Then Exit Sub
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
End Sub
End Class
///
 
Cor said:
Yes however that is very easy to do, this is OledB the only difference is
that you for that where is OleDb.OleDb have to place SQLClient.Sql and
another connectionstring.

I hope this helps?

Cor

\\\
Public Class clsUpdate
Public Sub New()
Dim conn As New
Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="C:\myAcces.mdb")
conn.Open()
Dim cmd As New OleDb.OleDbCommand("ALTER TABLE Persons " & _
"ADD myText text", conn)
doCmd(cmd)
conn.Close()
End Sub
Private Sub doCmd(ByVal cmd As Data.OleDb.OleDbCommand)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
If ex.ErrorCode = -2147217887 Then Exit Sub
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
End Sub
End Class
///
Click to expand...

Thanks for the info. I'll go look up the ALTER TABLE syntax in the MSDN
 
Back
Top