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("SEL ECT TOP 0 * FROM SomeTable",
SomeSqlConnecti on)
dc.CommandType = CommandType.Tex t

Dim da As New SqlDataAdapter( dc)
Dim dt As New DataTable
da.FillSchema(d t, SchemaType.Sour ce)

dt.TableName = "NewTableNa me"
dt.Columns("Som eColumn").Colum nName = "NewName"
dt.Columns("Ano therColumn").Da taType = GetType(String)
dt.Columns.Add( "NewColumn" , GetType(Integer ))

da.Update(dt)


[C#]

SqlCommand dc = new SqlCommand("SEL ECT TOP 0 * FROM SomeTable",
SomeSqlConnecti on);
dc.CommandType = CommandType.Tex t;

SqlDataAdapter da = new SqlDataAdapter( dc);
DataTable dt = new DataTable();
da.FillSchema(d t, SchemaType.Sour ce);

dt.TableName = "NewTableNa me";
dt.Columns["SomeColumn "].ColumnName = "NewName";
dt.Columns["AnotherCol umn"].DataType = Type.GetType("S ystem.String");
dt.Columns.Add( "NewColumn" , Type.GetType("S ystem.Integer") );

da.Update(dt);

[Cor Ligthert]

Re: Changing a DataTables Schema

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

Re: Changing a DataTables Schema

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

"Jon Brunson" <JonBrunson@NOS PAMinnovationso ftwareDOTcoPERI ODuk> wrote in
message news:%23T29CtRh EHA.3992@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> 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("SEL ECT TOP 0 * FROM SomeTable",
> SomeSqlConnecti on)
> dc.CommandType = CommandType.Tex t
>
> Dim da As New SqlDataAdapter( dc)
> Dim dt As New DataTable
> da.FillSchema(d t, SchemaType.Sour ce)
>
> dt.TableName = "NewTableNa me"
> dt.Columns("Som eColumn").Colum nName = "NewName"
> dt.Columns("Ano therColumn").Da taType = GetType(String)
> dt.Columns.Add( "NewColumn" , GetType(Integer ))
>
> da.Update(dt)
>
>
> [C#]
>
> SqlCommand dc = new SqlCommand("SEL ECT TOP 0 * FROM SomeTable",
> SomeSqlConnecti on);
> dc.CommandType = CommandType.Tex t;
>
> SqlDataAdapter da = new SqlDataAdapter( dc);
> DataTable dt = new DataTable();
> da.FillSchema(d t, SchemaType.Sour ce);
>
> dt.TableName = "NewTableNa me";
> dt.Columns["SomeColumn "].ColumnName = "NewName";
> dt.Columns["AnotherCol umn"].DataType = Type.GetType("S ystem.String");
> dt.Columns.Add( "NewColumn" , Type.GetType("S ystem.Integer") );
>
> da.Update(dt);[/color]

[Jon Brunson]

Re: Changing a DataTables Schema

So to confirm:

I *can* change the name of a table in a database by "downloadin g" (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:
[color=blue]
> 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.
>
>[/color]

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

Re: Changing a DataTables Schema

Jon,

I think you misunderstand. See inline:
[color=blue]
> I *can* change the name of a table in a database by "downloadin g" (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)[/color]

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.
[color=blue]
>
> 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[/color]

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.co m
[color=blue]
>
> If so, how? As the code I orginally posted does not work.
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>[color=green]
>> 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.
>>[/color][/color]

[Jon Brunson]

Re: Changing a DataTables Schema

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

Nicholas Paldino [.NET/C# MVP] wrote:
[color=blue]
> Jon,
>
> I think you misunderstand. See inline:
>
>[color=green]
>>I *can* change the name of a table in a database by "downloadin g" (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)[/color]
>
>
> 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.
>
>[color=green]
>>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[/color]
>
>
> 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.
>[/color]

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

Re: Changing a DataTables Schema

Jon,

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

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

"Jon Brunson" <JonBrunson@NOS PAMinnovationso ftwareDOTcoPERI ODuk> wrote in
message news:eGtx7lShEH A.3664@TK2MSFTN GP11.phx.gbl...[color=blue]
> So basically I need to do it all with "ALTER TABLE" queries?
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>[color=green]
>> Jon,
>>
>> I think you misunderstand. See inline:
>>
>>[color=darkred]
>>>I *can* change the name of a table in a database by "downloadin g" (with a
>>>DataAdapte r) it into a DataTable, changing the TableName property, and
>>>"uploading " it back to the database (using the same DataAdapter's
>>>Update() method)[/color]
>>
>>
>> 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.
>>
>>[color=darkred]
>>>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[/color]
>>
>>
>> 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.
>>[/color][/color]

[Cor Ligthert]

Re: Changing a DataTables Schema

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

I hope this helps?

Cor

\\\
Public Class clsUpdate
Public Sub New()
Dim conn As New
Data.OleDb.OleD bConnection("Pr ovider=Microsof t.Jet.OLEDB.4.0 ;Data
Source="C:\myAc ces.mdb")
conn.Open()
Dim cmd As New OleDb.OleDbComm and("ALTER TABLE Persons " & _
"ADD myText text", conn)
doCmd(cmd)
conn.Close()
End Sub
Private Sub doCmd(ByVal cmd As Data.OleDb.OleD bCommand)
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
If ex.ErrorCode = -2147217887 Then Exit Sub
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
End Sub
End Class
///

[Jon Brunson]

Re: Changing a DataTables Schema

Nicholas Paldino [.NET/C# MVP] wrote:
[color=blue]
> Jon,
>
> Yes, or use a library like ADOX (through COM interop).
>[/color]

Thanks for your help

[Jon Brunson]

Re: Changing a DataTables Schema

Cor Ligthert wrote:
[color=blue][color=green]
>>So basically I need to do it all with "ALTER TABLE" queries?[/color]
>
>
> 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 connectionstrin g.
>
> I hope this helps?
>
> Cor
>
> \\\
> Public Class clsUpdate
> Public Sub New()
> Dim conn As New
> Data.OleDb.OleD bConnection("Pr ovider=Microsof t.Jet.OLEDB.4.0 ;Data
> Source="C:\myAc ces.mdb")
> conn.Open()
> Dim cmd As New OleDb.OleDbComm and("ALTER TABLE Persons " & _
> "ADD myText text", conn)
> doCmd(cmd)
> conn.Close()
> End Sub
> Private Sub doCmd(ByVal cmd As Data.OleDb.OleD bCommand)
> Try
> cmd.ExecuteNonQ uery()
> Catch ex As OleDb.OleDbExce ption
> If ex.ErrorCode = -2147217887 Then Exit Sub
> MessageBox.Show (ex.Message, "OleDbException ")
> Exit Sub
> Catch ex As Exception
> MessageBox.Show (ex.Message, "GeneralExcepti on")
> Exit Sub
> End Try
> End Sub
> End Class
> ///
>
>[/color]

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