Clone or copy table.. Schema Information.. Generic ado.net connection

Hi:

Im writing a table designer through an ado.net provider. Im using a generic
ado.net provider, so i cant use database specific data (sysobjects or
something like that). I can get some information using
DataReader.GetSchemaTable(), but is not enoug. I need, for instace, the
types the database uses to fill a combobox, so the user can choose and i can
create my "create table" and "alter table sentences".

At the moment im using some rare practices like:

When the user changes the data type or the size of the type for a column, I
have to create an auxiliar table, to send the data, drop the current table
and then create a new table with the schema selected by the user, update
that table, and drop the auxiliar one. (I don't like that, because if the
connection fails, there will remain some garbage tables on the database).

Another rare thing im doing is to execute many IDbCommand.Prepare () to
"create" tables with all the data base data types i know, (int, blob, char..
45! in total) to discriminate which of them the current database supports..
:-b

As you can see, i need real solutions and ideas. Is there some article, or
source code available? Do you have any suggestions?

Please help

William
Wed Jan 21 14:07:36 CST 2004

Daniel: Take a look at OleDbConnection etc .GetOleDbSchemaTable method...
http://www.knowdotnet.com/articles/getoledbschematable.html

IF it's got to be generic, you are going to have to use what ADO.NET can
give you or you can consider creating an object that can query the system
catalogs of most of the major vendors depending on how it's called.


"Daniel Bello Urizarri" <dburizarri@hotmail.com> wrote in message
news:uPiTSrD4DHA.2432@TK2MSFTNGP09.phx.gbl...
> Hi:
>
> Im writing a table designer through an ado.net provider. Im using a
generic
> ado.net provider, so i cant use database specific data (sysobjects or
> something like that). I can get some information using
> DataReader.GetSchemaTable(), but is not enoug. I need, for instace, the
> types the database uses to fill a combobox, so the user can choose and i
can
> create my "create table" and "alter table sentences".
>
> At the moment im using some rare practices like:
>
> When the user changes the data type or the size of the type for a column,
I
> have to create an auxiliar table, to send the data, drop the current table
> and then create a new table with the schema selected by the user, update
> that table, and drop the auxiliar one. (I don't like that, because if the
> connection fails, there will remain some garbage tables on the database).
>
> Another rare thing im doing is to execute many IDbCommand.Prepare () to
> "create" tables with all the data base data types i know, (int, blob,
char..
> 45! in total) to discriminate which of them the current database
supports..
> :-b
>
> As you can see, i need real solutions and ideas. Is there some article, or
> source code available? Do you have any suggestions?
>
> Please help
>
>
>
>

Paul
Thu Jan 22 07:38:39 CST 2004

On Wed, 21 Jan 2004 11:22:31 -0500, "Daniel Bello Urizarri" <dburizarri@hotmail.com> wrote:

¤ Hi:
¤
¤ Im writing a table designer through an ado.net provider. Im using a generic
¤ ado.net provider, so i cant use database specific data (sysobjects or
¤ something like that). I can get some information using
¤ DataReader.GetSchemaTable(), but is not enoug. I need, for instace, the
¤ types the database uses to fill a combobox, so the user can choose and i can
¤ create my "create table" and "alter table sentences".
¤
¤ At the moment im using some rare practices like:
¤
¤ When the user changes the data type or the size of the type for a column, I
¤ have to create an auxiliar table, to send the data, drop the current table
¤ and then create a new table with the schema selected by the user, update
¤ that table, and drop the auxiliar one. (I don't like that, because if the
¤ connection fails, there will remain some garbage tables on the database).

You shouldn't have to create a temporary table to do this. Create the new column, run an Update
query to populate it with the contents of the current column, drop the current column and then
rename the new column to that of the column you just dropped.

¤
¤ Another rare thing im doing is to execute many IDbCommand.Prepare () to
¤ "create" tables with all the data base data types i know, (int, blob, char..
¤ 45! in total) to discriminate which of them the current database supports..

For some features I'm afraid it's going to be different depending upon the database implementation.
I'm not aware of any method to handle this generically or without considering the database type you
are working with, in code.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Daniel
Thu Jan 22 08:56:54 CST 2004

Thanks Paul.

You wrote:

"You shouldn't have to create a temporary table to do this. Create the new
column, run an Update query to populate it with the contents of the current
column, drop the current column and then rename the new column to that of
the column you just dropped."


How can i do that? i tried:

ALTER TABLE tablename RENAME oldFieldName TO newFieldName

and it does not work with a Microsoft Access database. I have not found any
other way to do that. Is there any?

Paul
Thu Jan 22 09:10:36 CST 2004

On Thu, 22 Jan 2004 09:56:54 -0500, "Daniel Bello Urizarri" <dburizarri@hotmail.com> wrote:

¤ Thanks Paul.
¤
¤ You wrote:
¤
¤ "You shouldn't have to create a temporary table to do this. Create the new
¤ column, run an Update query to populate it with the contents of the current
¤ column, drop the current column and then rename the new column to that of
¤ the column you just dropped."
¤
¤
¤ How can i do that? i tried:
¤
¤ ALTER TABLE tablename RENAME oldFieldName TO newFieldName
¤
¤ and it does not work with a Microsoft Access database. I have not found any
¤ other way to do that. Is there any?
¤

Access DDL doesn't support it. I've used either DAO or ADOX (Microsoft ADO Ext 2.x for DDL and
Security). Below is an example that uses ADO and ADOX:

Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.oledb.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

cnn.Execute "ALTER TABLE Table2 ADD COLUMN NewField Double"
cnn.Execute "UPDATE Table2 SET Table2.NewField = [Table2].[Field3]"
cnn.Execute "ALTER TABLE Table2 DROP COLUMN Field3"

Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = cnn

cat.Tables("Table2").Columns("NewField").NAME = "Field3"

Set cat = Nothing
cnn.Close
Set cnn = Nothing

As I mentioned, the different implementations of DDL, SQL, data types, etc can cause some problems.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)