tags:

views:

60

answers:

2

I have a small project in C# (Microsoft Office Access). I can read and save now. I now need to allow the user to use the new database project but structered like the working one, and also to do "save as".

Besides I need to export to a text file/CSV.

Any idea or sample?

+1  A: 

One way to create a blank DB is to try the following

    using System;
    using ADOX;

    public class CreateDB
    {
        public static void Main( string [] args )
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();

            string create =
            @"Provider=Microsoft.Jet.OLEDB.4.0;Data
            Source=C:\BlankAccessDB\MyAccessDBCreatedFromCsharp.mdb;" +
            "Jet OLEDB:Engine Type=5";

            cat.Create(create);

            cat = null;
        }
    }

Both Save and SaveAs is as easy as using SaveFileDialog to prompt the user to specify the filename and location to save the file.

Xander
Wow, I didn't know it was that easy. I've not touch Access for ages.
o.k.w
Except that, if I remember rightly, it requires a huge lump of interop code which is not fun.
Murph
A: 

The way I did this was to create a new empty access database file (this comes to about 100 KB) and then embed that file as a resource in my application. To "create" a new database is then simply a matter of extracting the resource to a file - which gives you a blank database - and then running a schema update code to create the schema you require in the blank database and then off you go.

I have a project that contains an empty database set to be embedded, a class with one method as below and, er, that's about it.

This is the code to dump the file from the embedded resource - it's not up to date, I wrote it 6 years ago but have had no need to change it:

public void CreateDatabase(string sPath)
{
    // Get the resource and, er write it out?
    System.IO.Stream DBStream;
    System.IO.StreamReader dbReader;
    System.IO.FileStream OutputStream;

    OutputStream = new FileStream(sPath, FileMode.Create);
    Assembly ass = System.Reflection.Assembly.GetAssembly(this.GetType());
    DBStream = ass.GetManifestResourceStream("SoftwareByMurph.blank.mdb");
    dbReader = new StreamReader(DBStream);
    for(int l=0;l < DBStream.Length;l++)
    {
        OutputStream.WriteByte((byte)DBStream.ReadByte());
    }
    OutputStream.Close();
}

Simple, effective and the .dll is 124 KB.

Note I use an utterly blank and empty Access file - attempting to maintain the right schema in the embedded file is going to cause it to grow (because of the way .mdb files work) and may result in shipping data - which probably shouldn't happen. The schema itself is created/updated/maintained by a separate lump of DDL (SQL) that I run from code.

Export to .CSV is moderately trivial to do by hand since you pretty much just need to iterate over the columns in a table but for a smarter approach look at FileHelpers.

Murph
tnx. I understand how to create a blank db, I am not sure I understand how to use the schema? I want the db empty from data but with the right tables, key ect.Perhaps I cn copy the empty sample db I have? got mixed up a bittnx
sadboy
Ok, the detailed explanation is, erm, a lot longer - but basically you can write SQL statements to create and modify your schema using CREATE TABLE and similar statements - this is known as DDL (http://en.wikipedia.org/wiki/Data_Definition_Language). So once you have a new, empty, database you can connect to is as you are doing already create the schema from scratch in "code". Wrap this up in nice logic, include a table that identifies the schema version and you can have a system that allows you to create or update your schema as needed.
Murph