Changing a DataTables Schema

[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);

 

[Cor Ligthert]

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

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[Jon Brunson]

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.

 

[Nicholas Paldino [.NET/C# MVP]]

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)

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

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.

 

[Nicholas Paldino [.NET/C# MVP]]

Jon,

Yes, or use a library like ADOX (through COM interop).

 

[Jon Brunson]

So basically I need to do it all with "ALTER TABLE" queries?

 

[Cor Ligthert]

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
///

 

[Jon Brunson]

Jon,

Yes, or use a library like ADOX (through COM interop).

Thanks for your help

 

[Jon Brunson]

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
///

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