Change table properties using C#

[Claudia Fong]

Hello,

I'm using the sql statement below to create a new table from an old one.

But I found a little problem with that.

In my old table DEP2004, I have one field's property allow zero length
is YES, but after creating the new table DEP2005, this property change
to NO. It means it won't allow zero lenght.. How can I change this
property in C#?

My database is stored in Access.

Could someone know how should I do?


Cheers!

Claudi

 

[Guest]

First off, I didn't see a SQL statement, so I don't know if there is much
help that I can be there.

Second, it doesn't sound like C# is really the issue here. If you have a
SQL statement that works in Access, then whether you use C# or VB.NET to
invoke it shouldn't matter. What code is being used to execute the SQL
statement to change the table definition?

 

[Claudia Fong]

This is the code:


SELECT * INTO DEP2005
FROM DEP2004
WHERE (1=0);



Cheers!

Claudi

 

[Bjorn Abelli]

...

In my old table DEP2004, I have one field's property allow zero length
is YES, but after creating the new table DEP2005, this property change
to NO. It means it won't allow zero lenght.. How can I change this
property in C#?

My database is stored in Access.

Could someone know how should I do?

It's a bit tricky, but this method should work.
It worked for me when I tried... ;-)

You need to use some extra dll:s, one is straightforward, adodb.dll.

The other one is a COM-dll. If you're using VS.NET, you'll find it in the
COM-list as

Microsoft ADO Ext 2.8 for DDL and security

...which will be imported to your directory as interop.adox.dll


Anyway, here's the code:


private static void SetColumnAllowZeroLength(
string cnstring, string tablename, string columnname)
{
// open connection
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(cnstring, "", "", 0 );

// Create a catalog
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.ActiveConnection = conn;

// fetch the table
ADOX.Table table = cat.Tables[tablename];

// fetch the column
ADOX.Column col = table.Columns[columnname];

// fetch the property and set it
ADOX.Property prop = col.Properties["Jet OLEDB:Allow Zero Length"];
prop.Value = true;

// finish up
col = null;
cat = null;
conn.Close();
conn = null;
}


// Bjorn A

 

[Guest]

Nothing in that particular SQL statement sets the properties in the new
table. That is to say that the execution of the SELECT statement isn't going
to create the DEP2005 table. So if you're looking for reasons that the table
property has changed, you'll have to look somewhere else.

 

[Claudia Fong]

Thanks Bjorn,

Did you try this code with more than one tables?

I need to change that property in 3 tables....


Should I put this way?
ADOX.Table table = cat.Tables[tablename1, tablename2, table3name];?

Can you help me with this again Bjorn?

Cheers!

Claudi

 

[Bjorn Abelli]

Thanks Bjorn,

Did you try this code with more than one tables?

Sure. The method I wrote simply is an example on how to change the property
on a single column in a single table from a single connection, but it can
easily be rewritten to take care of many columns at the time.

I need to change that property in 3 tables....

You really don't change it on any *table*, as just columns can have that
property... ;-)

Should I put this way?
ADOX.Table table = cat.Tables[tablename1, tablename2, table3name];?

Nope, but simply fetch each table in a separate turn:

ADOX.Table table1 = cat.Tables[tablename1];
ADOX.Table table2 = cat.Tables[tablename2];
ADOX.Table table3 = cat.Tables[tablename3];

....and from respective table fetch each column you want to change the
property on, e.g.:

ADOX.Column col1 = table1.Columns[columnname];
ADOX.Column col2 = table2.Columns[columnname];
ADOX.Column col3 = table3.Columns[columnname];

Remember to change the signature of the method to reflect what arguments you
need to do this, or you can also use literals directly:

ADOX.Table table1 = cat.Tables["MYTABLE"];
ADOX.Column col1 = table1.Columns["MYCOLUMN"];

....etc.


// Bjorn A