Talking about a shoebox type of database here.
Would like to make a nice interface in C# for it.
Typically something around 50 records or so. (e.g. my wine cellar;) )
To use a genuine SQL DB seems to me like a bit of an overkill.
Could use a flat file for it, but the burden of all the maintenance needed:(, only if I absolutely must.
So I thought I could use a DataSet for it, looked on the net and here, but nothing usefull.
To set up a DataSet is not that much of a problem, but storing it on a hard disk or as a resource (in the application?) is. How should I do that?
Or should I leave this idea because of a no no.
Any suggestions are more than welcome.

Edited 7 Years Ago by ddanbe: n/a

Comments
That is the approach I use for some personal apps :)

Sure. That is actually what I would recommend for a local database if you didn't want to use Access, SQL, etc. Just be aware of the limitiations! The only real problems with doing this is:
1) When you serialize the dataset it writes the entire contents to hard disk again so it is a little more IO than necessary. However if this is a small database then it doesn't really matter.
2) Related to #1 -- If writing to disk fails and you don't catch it you could lose all of your data. This is easy enough to check:

If your database were stored in "C:\database.db"

try
{
dataset.SaveToFile(C:\database.tmp);
File.Move(C:\database.db, C:\database.del)
File.Move(C:\database.tmp, C:\database.db)
File.Delete(C:\database.del)
}

In the above psuedo code you would write the update DB to disk, move the existing DB, move the new db in place, then remove the last db. Now regardless of what point it fails you're pretty much guaranteed to have at least one copy of the database left.

Now to serialize the DataSet:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmDataSetSerialize : Form
  {
    public frmDataSetSerialize()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      DataSet ds = new DataSet();
      ds.EnforceConstraints = true; //enforce locally since there is no remote db server

      #region setup beer table in a limited scope
      {
        DataTable dtBeer = new DataTable();
        dtBeer.TableName = "Beer"; //Very important to set the table name when serializing datatables
        dtBeer.Columns.Add(new DataColumn("Brand", typeof(string)));
        dtBeer.Columns.Add(new DataColumn("SOH", typeof(int))); //"SOH" - Stock on hand
        dtBeer.Columns.Add(new DataColumn("OrderQty", typeof(int))); //amt to pick up next time you're at the store. This could make your list :)
        dtBeer.Columns["Brand"].Unique = true;

        DataRow row = dtBeer.NewRow();
        row["Brand"] = "Bud Light";
        row["SOH"] = 5;
        row["OrderQty"] = 0;
        dtBeer.Rows.Add(row);

        row = dtBeer.NewRow();
        //This stuff is $8 for 4 bottles at my local store. I'm sure you can get it much
        //cheaper as it is a belgian beer, a lot closer to you!
        row["Brand"] = "Allagash White"; 
        row["SOH"] = 8;
        row["OrderQty"] = int.MaxValue;
        dtBeer.Rows.Add(row);

        ds.Tables.Add(dtBeer);
      }
      #endregion

      #region setup wine
      {
        DataTable dtWine = new DataTable();
        dtWine.TableName = "Wine"; 
        dtWine.Columns.Add(new DataColumn("Brand", typeof(string)));
        dtWine.Columns.Add(new DataColumn("SOH", typeof(int))); 
        dtWine.Columns.Add(new DataColumn("OrderQty", typeof(int)));
        dtWine.Columns.Add(new DataColumn("Location", typeof(string)));
        dtWine.Columns["Brand"].Unique = true;

        DataRow row = dtWine.NewRow();
        row["Brand"] = "Wine1"; //i'm a beer guy
        row["SOH"] = 5;
        row["OrderQty"] = 0;
        row["Location"] = "Basement Cellar, Rack #1, Third row from the bottom";
        dtWine.Rows.Add(row);

        row = dtWine.NewRow();
        row["Brand"] = "Win2";
        row["SOH"] = 8;
        row["OrderQty"] = 0;
        row["Location"] = "Attic collection, on top of the exhaust fan";
        dtWine.Rows.Add(row);

        ds.Tables.Add(dtWine);
      }
      #endregion

      #region save the dataset to disk
      ds.WriteXml(@"C:\database.xml", XmlWriteMode.WriteSchema); //You have to write the schema or it will fail when loading
      #endregion
    }

    private void button2_Click(object sender, EventArgs e)
    {
      DataSet ds = new DataSet();
      ds.ReadXml(@"C:\database.xml", XmlReadMode.Auto);
      int beerCount = ds.Tables["Beer"].Rows.Count;
      int wineCount = ds.Tables["Wine"].Rows.Count;
      System.Diagnostics.Debugger.Break();
    }
  }
}

The important part here is setting the DataTable.TableName and the XmlWriteMode.WriteSchema. If you don't set the tablename it will fail when serializing, and if you don't write the schema it will fail when deserializing.

[edit]
Oh you could also use a typed dataset with the designer. I have never made a DataSet by hand that wasn't hooked up to a database but in theory it should work. I hate the dataset designer though. Everything I think that it makes easy it actually makes it 500x harder down the road</rant>. Try it and see if you like it I suppose :P
[/edit]

[edit2]
>> To set up a DataSet is not that much of a problem, but storing it on a hard disk or as a resource (in the application?) is. How should I do that?

I would just have an application setting in your Properties for "Database Location" and when the app starts have it load that file. If the database doesn't exist then have it create the database from code and it will save when the application exits.

Embedding a DB schema in resources is problematic because then you need to update it each time you change the database, plus its a lot easier to just have the code create one as needed.
[/edit2]

Edited 7 Years Ago by sknake: n/a

Comments
Great! Keep it on.
Unsurpassable!

Scott, you suprise me every time. This is way beyond what I expected!!!!!!
I believe I can work it out now, thanks for the code and the hilarious comments.(Liked that very much:) )

BTW I I'm a beer lover too. Allagash White seems to be a beer brewed in your country in the belgian style, I never heard about it.

This question has already been answered. Start a new discussion instead.