Changing a DataTables Schema

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
Wed Aug 18 07:15:51 CDT 2004

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
Wed Aug 18 07:41:18 CDT 2004

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.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

"Jon Brunson" <JonBrunson@NOSPAMinnovationsoftwareDOTcoPERIODuk> wrote in
message news:%23T29CtRhEHA.3992@TK2MSFTNGP11.phx.gbl...
> 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
Wed Aug 18 08:31:15 CDT 2004

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] wrote:

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

Nicholas
Wed Aug 18 08:36:13 CDT 2004

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]
- mvp@spam.guard.caspershouse.com

>
> If so, how? As the code I orginally posted does not work.
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>
>> 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
Wed Aug 18 08:45:40 CDT 2004

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

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

> 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
Wed Aug 18 08:45:30 CDT 2004

Jon,

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

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

"Jon Brunson" <JonBrunson@NOSPAMinnovationsoftwareDOTcoPERIODuk> wrote in
message news:eGtx7lShEHA.3664@TK2MSFTNGP11.phx.gbl...
> So basically I need to do it all with "ALTER TABLE" queries?
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>
>> 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.
>>

Cor
Wed Aug 18 08:51:23 CDT 2004

> 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
Wed Aug 18 09:06:53 CDT 2004

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

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

Thanks for your help

Jon
Wed Aug 18 09:07:18 CDT 2004

Cor Ligthert wrote:

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

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