0

need to know the exact info of database and containing tables using c#. database is MS access.i want to full info of the tables in it like primary key,max length,not null of the columns in tables in ms access database,etc.. so whats the best way of doing it....

advanced thanx for any kind of help.

another issue is getschema gives me datatypes in numeric way like 130,131.. so how can i use them in create table query they give error

let me explain what i am trying to do.i want to recreate the database about which i have no information.i don't know about its size,tables,data or any thing. actually i have succeeded to an extent.what i have done is i get the db name and create it with CatalogClass and with getschema(tables) i get all the table names and create them with create table from C#.then column names with alter table.and now i have to give it constraints which are in the DB which have been provided. so,other then this method i have used is there any thing else which i am missing.any easy or better way available to do this.so, it can go faster

question is still open

vishal
  • 3
  • 2
  • 7
  • I believe you can use DAO with c#, [so here is a post](http://stackoverflow.com/questions/698839/how-to-extract-the-schema-of-an-access-mdb-database/699516#699516) on creating a schema using VBA, which may help. – Fionnuala Mar 05 '12 at 10:26
  • THANX for help remou...but i am new to all this specially never learned VBA.currently working on C#. i am using ado to dynamically create database,tables and columns.everything is working but i just need to add constraints,keys,exact datatypes to columns which i am recreating.and that info i have to take from other database.. – vishal Mar 06 '12 at 04:43
  • so i thinking is there any other easy way to get info and implement.and when getschema gives me datatypes of columns they are in numerical form like 130,5,131.so is there a method that can give me number,string,datetime... – vishal Mar 06 '12 at 04:45
  • You will have to build your own text list. You can use ADODB.DataTypeEnum to see the values or [this](http://msdn.microsoft.com/en-us/library/windows/desktop/ms675318(v=vs.85).aspx) or download ADOVBS.inc from [this page](http://forums.devx.com/archive/index.php/t-11100.html), it includes a list of ADO data types. – Fionnuala Mar 06 '12 at 09:55
  • today i was just checking datatypes returned by getschema.i found out it was giving 130 for both memo and text.because of that i was having datatype mismatch.so any other method to get datatypes from the database.so i can compare those numeric values. – vishal Mar 06 '12 at 12:45
  • i checked the links it was good.but how to differentiate that this 130 is text and not memo.because getschema returns 130 for both.and just found out that native datatype for both memo and text is 130.so i need any other way to get db provider types of columns.getschema("datatypes") gave this info.it returns supported datatypes by db.kindly help – vishal Mar 06 '12 at 12:59
  • You should not have 130 for memo (http://allenbrowne.com/ser-49.html#_ftn5) Are you sure it is a memo data type? For me, the standard DAO dbText and dbMemo return 202 and 203 with ADODB, that is adVarWChar and adLongVarWChar. How was the table created? – Fionnuala Mar 06 '12 at 13:34
  • even i am wondering.but [con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, "" + Tablename[i] + "", null});] method is giving me 130 for memo and text.database is ms access.so is there any other method to get dbtypes – vishal Mar 07 '12 at 03:51
  • This http://stackoverflow.com/questions/857942/how-can-i-get-the-field-names-of-a-database-table may be just what you are looking for. – Fionnuala Mar 07 '12 at 11:30
  • i have tried things of that thread but doesnt work. the query when i fire says IT DOESNT FIND INFORMATION_SCHEMA.MDB. – vishal Mar 07 '12 at 12:02

2 Answers2

1

I believe everything is documented at the link below, try to run it step by step with debug and then u can inspect the element and display every value you want.

http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

Primary Key: DataTable.PrimaryKey Max Length, of what? Records? DataTable.Rows.Count Columns? DataTable.Columns.Rows

David Rasuli
  • 812
  • 4
  • 15
  • 30
  • that is about datatable. i have an ms access database.and want to recreate it on other location.with the exact same constraints and keys and data. so i need all the info of the database so i can create it dynamically with all the keys and constraints.. – vishal Mar 05 '12 at 08:24
  • Ok, so if I understand correctly, U need to know the type of data for each column (for example), you can get it from DataTable.Columns - thats a collection of the table's Columns, and then you can get the information from DataColumn.DataType ,for each row. Hope it helps – David Rasuli Mar 05 '12 at 08:56
  • thanks but it gives me system types like system.int32 and all that. – vishal Mar 05 '12 at 09:01
  • but i want text,number,datetime datatypes which are used in ms access.so i can dynamically use them in alter table or create table query.. – vishal Mar 05 '12 at 09:03
  • i have explained the question a bit more kindly check it. – vishal Mar 09 '12 at 03:53
1

It appears that you are using a schema to return the field types. I have been testing, and something on these lines appears to return what you want.

ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
string cnStr;

cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\\Docs\\Test.accdb";
string ssql = "Select * From Table1 where 1=2";

cn.Open(cnStr, null, null, 0);

rs.Open(ssql, cn, ADODB.CursorTypeEnum.adOpenKeyset, 
    ADODB.LockTypeEnum.adLockOptimistic, -1);

foreach (ADODB.Field fld in rs.Fields)
{
    Console.WriteLine(fld.Type);
}
Console.Read();

rs.Close();
cn.Close();

For various types this returns:

adInteger
adVarWChar = Text
adDate
adInteger
adLongVarWChar = Memo
adVarWChar
adDate
adBoolean
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • i will try this code.but,i have explained the question a bit.kindly have a look. – vishal Mar 09 '12 at 03:48
  • vishal, by far the best way to recreate an Access database is by copying it. Access is file based. After that use DAO, or a mixture of DAO and ADO. The correct field types will be returned and you can check for hyperlink fields, field formats, look-up tables, field checkboxes and numerous other things that you will not find otherwise. You can use DAO with C#, as I said before. Access is not just a database, in fact, it is not a database at all, it is a RAD tool that often, but not always uses the Jet/ACE database. – Fionnuala Mar 09 '12 at 09:15
  • the access is on client side somewhere else and i am on server side so copying is not a way i think.and uploading takes too much time because data is too much. – vishal Mar 09 '12 at 09:56
  • @vishal "Data it too much" Access files are limited to 2GB max, if the upstream is slow, can you not schedule a one off overnight upload? – Matt Donnan Mar 10 '12 at 17:14
  • @MattDonnan this is not for only one time.i am working on an application.its task is like sync.when user clicks on sync his data from database will be synced to the server.so for first time new file(DB) is created and for next times only the updated data. – vishal Mar 10 '12 at 18:17
  • If you can interact with the database to the extent of reading types, you can use interop to copy the tables, structure only, to a new database, see http://msdn.microsoft.com/en-us/library/aa220761(v=office.11).aspx – Fionnuala Mar 10 '12 at 18:30
  • @Remou can i copy structure only tables from database from a client who is using my app to my server or any specified location on internet.if yes what should be the path for destination server/database. – vishal Mar 12 '12 at 03:28
  • You can copy the database and then transfer the copy. Your objection to copying the full database was that it was too big. – Fionnuala Mar 12 '12 at 07:32
  • currently what i done is when user clicks button then i get all the information about the database like(tablenames,primarykeys) with `getoledbschematable` and the data then i store them in `Dataset` and store them in a object then i serialize it and send it into stream and initialize webrequest.and on server side with the info i have got i recreate db and insert data. – vishal Mar 12 '12 at 07:43
  • but as you told that i can copy the structure only tables with interop so i am wondering if i can copy them on any location on the internet like on server side DB – vishal Mar 12 '12 at 07:45