Is it just me?

[element]

Seriously, I just don't get it! I have looked on the net, tutorials, bought ADO.NET books, and obviously run thousands of tests, and I am just completly lost. Here is my situation...

 

I am writing a program that basically lists files from cds, for reference. I don't want to use special db servers, dataconnection from a webserver or anything that will involve someone else having to set up a database "connection" to serve from their PC, as I quite honestly don't understand it.

 

I have used DAO before, and it's a doddle, choose your MDB and use it with objects. Also, .NET DataSets are a Godsend as they simply represent the data as it should be.

 

I want the user to basically use Access databases like DAO, so they can choose a file, and with that file they can do anything. Note, files do not require server connections to work! I started using XML but it is WAY to slow to use it for this purpose. If one cd contains about 7000 files, then it is just too much for it.

 

I have created a successful XSD template, and created a DataSet from it, which can be used thoughout the project. I also have one bound control, a ComboBox.

 

My question is this - how can I save the contents of the DataSet to an MDB? The DataAdapter won't do this! I cannot seem to find a way of creating a database FROM an existing DataSet. And once I have created it, I need to be able to open it and use the .Fill to get the data from it, manipulate it, and re-save it all again, or what?

 

I know I am confusing myself here a bit, I was just wondering if someone could shed a little light on the subject.

 

Oh, and did I mention I just wanted to use files?

 

Thanks, Tom

[Nerseus]

Your best bet is to include an empty DB with your app as .NET won't create a new Database for you. Nothing in .NET will auto-create a table for you, either. But you can run a command to create a table on the fly (initialization the first time your app runs, for instance).

 

Or, if your database will be fairly small, just use the WriteXml and ReadXml methods on the Dataset to save the data to a text file. It sounds like that's what you really want.

 

-Nerseus

[reboot]

Dim tbl As New ADOX.Table()
Dim col As New ADOX.Column()
Dim cat As New ADOX.Catalog()

'Engine Type=4 is Access 97 and a value of 5 is Access 2000
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=d:\My Documents\db10.mdb;" & _
          "Jet OLEDB:Engine Type=4;")

tbl.Name = "NewTable"
col.Name = "DateField"
col.Type = ADOX.DataTypeEnum.adDate
tbl.Columns.Append(col)
col = New ADOX.Column()

col.Name = "Address2"
col.Type = ADOX.DataTypeEnum.adVarWChar
col.DefinedSize = 20
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
tbl.Columns.Append(col)
col = New ADOX.Column()

col.Name = "Age"
col.Type = ADOX.DataTypeEnum.adInteger
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
tbl.Columns.Append(col)
cat.Tables.Append(tbl)

cat.Tables("NewTable").Columns("Address2").Properties("Jet OLEDB:Allow Zero Length").Value = True

[element]

Hi,

 

Thank you for both of your responses. I have experimented with XML and while excellent for < 1000 records, when dealing with 100,000 records it is glacially slow, and the file sizes/memory usage is enormous!

 

Anyway it seems ADOX is the way to go so I shall be investigating that further. Thank you very much for both your contributions,

 

Tom

[Derek Stone]

It's "the way to go" if you feel like adding a COM dependency to your application. I'd stick with Nerseus' answer on this one if I were you.

[element]

yes I agree that adding COM to my project is a bit erm, crap, as the .NET framework provides everything I need. Is there a way to assign a dataset to a dataadapter or will I need to generate the new database using SQL?

[Derek Stone]

If you are programming against an installed copy of Microsoft SQL Server (including MSDE) then it is relatively easy to create a new database and contained tables. Have a look at the CREATE DATABASE and CREATE TABLE statements (either online or in a T-SQL reference).

[element]

I don't honestly know anything about SQL server. All I know is, I install it and it "serves" databases that I can connect to. Great!

 

When distrubuting my program, do they need SQL server to use the database too?

[Nerseus]

If you are using SQL Server, yes, they'll need a copy of SQL Server. That's probably not what you want though unless your application is quite large (SQL Server is quite expensive).

 

You could use MSDE, which is a scaled down version of SQL Server. Or stick with Access (I thought that's what you wanted in the first place?) which is similar, though easier to setup and install for beginners. If you've never done Access or MSDE, I'd take the time to learn MSDE (how to install and distribute as well as use) - a few days worth of learning would be well worth it, I'd say.

 

-Ner