C# .net 3.5 Compact SqlCe

Question

Ok, So i am trying to write a program for my HP Ipaq211 that I can use at work (I am a server) to take orders, as opposed to using paper. I have gotten pretty far and decided that it would be best to use a database to hold the full menu information. I created a database for drinks to start with 4 Columns {ID, Item, Price, Options} where ID is the primary Key.

I created a few concoctions that allow me to read the data into an object, and then create a list of those said objects, but all of them perform really slow (4 sec ish on the Ipaq). I have taught myself everything I know in terms of programming so bear with me, here is one of my attempts (which works but is slow and i need it to work faster!)

    public class _itemObject
    {

    public _itemObject()
    {

        ID = 0;
        _ioName = "";
        _ioPrice = "";
        _ioOptions = -1;

    }
        public _itemObject(int _next, string Tbl_Name)
        {
        try
        {

            string conSTR = "Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) +
                    "\\TestDatabase.sdf;Persist Security Info=True";
            SqlCeConnection _connection = new SqlCeConnection(conSTR);


            SqlCeCommand _cmd = new SqlCeCommand("select ID from " + Tbl_Name + " where ID ='" + _next.ToString() + "'", _connection);
            SqlCeCommand _cmd2 = new SqlCeCommand("select * from " + Tbl_Name + " where ID ='" + _next.ToString() + "'", _connection);
            SqlCeCommand _cmd3 = new SqlCeCommand("select price from " + Tbl_Name + " where ID ='" + _next.ToString() + "'", _connection);
            SqlCeCommand _cmd4 = new SqlCeCommand("select special from " + Tbl_Name + " where ID ='" + _next.ToString() + "'", _connection);



            _connection.Open();
            if (_cmd.ExecuteScalar() != null)
            {

                ID = (Convert.ToInt32(_cmd.ExecuteScalar().ToString()));
                _ioName = _cmd2.ExecuteScalar().ToString();
                _ioPrice = _cmd3.ExecuteScalar().ToString();
                _ioOptions = (Convert.ToInt32(_cmd4.ExecuteScalar().ToString()));
            }
            else
            {

            }
            _connection.Close();
        }

        finally
        {

        }
        }

this object is then added to a List<_itemObject> where I load any needed data from.

I know it is ugly but if anyone has any lessons for me I would appreciate it :)

Answer 1

The answer may differ on what is your final goal.

a) why do you need to use 4 sql commands? One command should be OK to get all information. In example:

SELECT * FROM table_name;

will report all data at once within a SqlCeDataReader.ExecuteReader() call that you can iterate to fill a list.

b) if no SQL server will be invoked later (for remote access/sync etc) and if not too much data records, you may consider switching to another data storage (ie xml (slow too) or binary file).

please provide more details if you need more help.

There are also SQLCE examples available here in stackoverflow: Local database, I need some examples and others (use search).

OK, from your comments I see you have some issues getting started?!

At http://www.codeproject.com/Articles/310378/A-Restaurant-and-Waiter-helper-app-in-WPF-and-Wind you will find a complete POS solution. You can change the waiter's code to use a local database.

...maybe adding some simple example later...