---------------------------------------------------------------
-- Scripts for DeviceApp demo
---------------------------------------------------------------

-- Using

using System.Data;
using System.Data.SqlServerCe;

-- Menu clicks

    private void menuItem1_Click(object sender, EventArgs e)
    {
      frmAddCustomer newForm = new frmAddCustomer();
      newForm.Show();
    }

    private void menuItem2_Click(object sender, EventArgs e)
    {
      frmCustomers newForm = new frmCustomers();
      newForm.Show();
    }

    private void menuItem3_Click(object sender, EventArgs e)
    {
      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));

      SqlCeEngine eng = new SqlCeEngine(connStr);
      MessageBox.Show(eng.LocalConnectionString);

      // Reclaim space in the database file
      eng.Shrink();
      MessageBox.Show("Shrink Complete!");

      // Recalculate the checksums of every page in the database file and verify that the checksums match their expected values. Call Repair if false!
      bool verify = eng.Verify();
      MessageBox.Show("Verify Complete: " + verify.ToString());

      if (!verify)
      {
        // Repair the database
        eng.Repair(connStr, RepairOption.RecoverCorruptedRows);
        MessageBox.Show("Repair Complete");

        // Recalculate the checksums of every page in the database file and verify that the checksums match their expected values. Call Repair if false!
        verify = eng.Verify();
        MessageBox.Show("Verify Complete: " + verify.ToString());
      }

      // Same as Shrink, but can change database settings
      // eng.Compact(connStr);
      // MessageBox.Show("Compact Complete!");

      SqlCeConnection conn = new SqlCeConnection(connStr);
      MessageBox.Show(conn.Database,"Database");
      MessageBox.Show(conn.ServerVersion, "ServerVersion");
      MessageBox.Show(conn.State.ToString(), "State");
      conn.Open();
      MessageBox.Show(conn.State.ToString(), "State");
      conn.Close();
    }

    private void menuItem4_Click(object sender, EventArgs e)
    {
      Application.Exit();
    }

    private void menuItem5_Click(object sender, EventArgs e)
    {
      frmResultSet newForm = new frmResultSet();
      newForm.Show();
    }


---------------------------------------------------------------
-- Add Customer

-- Using

using System.Data;
using System.Data.SqlServerCe;

-- Menu click

    private void btnSave_Click(object sender, EventArgs e)
    {
      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));
      SqlCeConnection conn = new SqlCeConnection(connStr);
      conn.Open();
      SqlCeCommand cmd = conn.CreateCommand();
      cmd.CommandText = "INSERT Customer (Name) VALUES (@Name)";
      cmd.Parameters.AddWithValue("@Name", txtCustomer.Text);
      cmd.ExecuteNonQuery();
      conn.Close();
      this.Close();
    }

---------------------------------------------------------------
-- View Customers

---------------------------------------------------------------
-- SqlCeResultSet

-- Using

using System.Data;
using System.Data.SqlServerCe;

-- Method

    private void ShowProducts()
    {
      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));

      SqlCeConnection conn = new SqlCeConnection(connStr);
      SqlCeCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT ID, Name FROM Product";
      SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd);
      DataSet ds = new DataSet("Sales");
      adp.Fill(ds, "Product");
      dataGrid1.DataSource = ds.Tables["Product"];
      conn.Close();
    }

-- Click Events

    private void button1_Click(object sender, EventArgs e)
    {
      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));

      SqlCeConnection conn = new SqlCeConnection(connStr);
      SqlCeCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT * FROM Customer";
      SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd);
      DataSet ds = new DataSet("Sales");
      adp.Fill(ds,"Customer");
      dataGrid1.DataSource = ds.Tables["Customer"];
      conn.Close();
    }


    private void button2_Click(object sender, EventArgs e)
    {
      string HTML = "";

      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));
      SqlCeConnection conn = new SqlCeConnection(connStr);
      conn.Open();

      SqlCeCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT * FROM Customer";
      SqlCeDataReader rdr = cmd.ExecuteReader();
      while (rdr.Read())
      {
        HTML = HTML + "<tr><td>" + rdr.GetString(1) + "</td></tr>";
      }
      rdr.Close();
      conn.Close();
      HTML = "<html><table>" + HTML + "</table></html>";
      webBrowser1.DocumentText = HTML;
    }


   private void button3_Click(object sender, EventArgs e)
   {
      ShowProducts();

      string HTML = "";

      string connStr = "Data Source ="
        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        + ("\\Sales.sdf;"
        + ("Password =" + "\"P@ssw0rd\";")));
      SqlCeConnection conn = new SqlCeConnection(connStr);
      conn.Open();

      SqlCeCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT * FROM Product";
      SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

      // Scroll

      rs.Read();
      HTML = HTML + "<tr><td><font size=1>Read</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.Read();
      HTML = HTML + "<tr><td><font size=1>Read</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.Read();
      HTML = HTML + "<tr><td><font size=1>Read</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.ReadPrevious();
      HTML = HTML + "<tr><td><font size=1>ReadPrevious</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.ReadFirst();
      HTML = HTML + "<tr><td><font size=1>ReadFirst</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.ReadLast();
      HTML = HTML + "<tr><td><font size=1>ReadLast</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";
      rs.ReadRelative(-5);
      HTML = HTML + "<tr><td><font size=1>ReadRelative(-5)</font></td><td><font size=1>" + rs.GetString(2) + "</font></td></tr>";

      // Done

      rs.Close();
      conn.Close();
      HTML = "<html><table>" + HTML + "</table></html>";
      webBrowser1.DocumentText = HTML;
    }
