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

namespace DeviceApp
{
  public partial class frmResultSet : Form
  {
    public frmResultSet()
    {
      InitializeComponent();
    }

    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();
    }

    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;
    }
  }
}