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

[Daniel Bello Urizarri]

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

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.

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Daniel Bello Urizarri]

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

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)