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

namespace CSApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private ULConnection Conn;
        private int[] ids;

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                String ConnString = "dbf=\\Program Files\\CSApp\\CSApp.udb";
                Conn = new ULConnection(ConnString);
                Conn.Open();
                Conn.DatabaseID = 1;
                RefreshListBox();
            }
            catch (System.Exception t)
            {
                MessageBox.Show("Exception: " + t.Message);
            }
        }
        private void RefreshListBox()
        {
            try
            {
                long NumRows;
                int I = 0;
                lbNames.Items.Clear();
                using (ULCommand cmd = Conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ID, Name FROM Names";
                    using (ULDataReader dr = cmd.ExecuteReader())
                    {
                        dr.MoveBeforeFirst();
                        NumRows = dr.RowCount;
                        ids = new int[NumRows];
                        while (dr.MoveNext())
                        {
                            lbNames.Items.Add(
                            dr.GetString(1));
                            ids[i] = dr.GetInt32(0);
                            I++;
                        }
                    }
                    txtName.Text = " ";
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(
                "Exception in RefreshListBox: " + err.Message);
            }
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            try
            {
                long RowsInserted;
                using (ULCommand cmd = Conn.CreateCommand())
                {
                    cmd.CommandText =
                        "INSERT INTO Names(name) VALUES (?)";
                    cmd.Parameters.Add("", txtName.Text);
                    RowsInserted = cmd.ExecuteNonQuery();
                }
                RefreshListBox();
            }
            catch (Exception err)
            {
                MessageBox.Show("Exception: " + err.Message);
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                long RowsUpdated;
                int updateID = ids[lbNames.SelectedIndex];
                using (ULCommand cmd = Conn.CreateCommand())
                {
                    cmd.CommandText =
                        "UPDATE Names SET name = ? WHERE id = ?";
                    cmd.Parameters.Add("", txtName.Text);
                    cmd.Parameters.Add("", updateID);
                    RowsUpdated = cmd.ExecuteNonQuery();
                }
                RefreshListBox();
            }
            catch (Exception err)
            {
                MessageBox.Show(
                    "Exception: " + err.Message);
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                long RowsDeleted;
                int deleteID = ids[lbNames.SelectedIndex];
                using (ULCommand cmd = Conn.CreateCommand())
                {
                    cmd.CommandText =
                        "DELETE From Names WHERE id = ?";
                    cmd.Parameters.Add("", deleteID);
                    RowsDeleted = cmd.ExecuteNonQuery();
                }
                RefreshListBox();
            }
            catch (Exception err)
            {
                MessageBox.Show("Exception: " + err.Message);
            }
        }
    }
}