#眉標=Windows CE、COM、OLE DB CE
#副標=SQL Server 2000 Windows CE Edition
#大標=Windows CE資料庫程式開發技巧
#作者=文／圖	傅曦


===<反灰>=============
SELECT P.ProductID as ID
      , P.ProductName as Name
      , C.CategoryName as Category
  FROM Products P
  JOIN Categories C on C.CategoryID = P.CategoryID
==<end>==============



===<反灰>=============
private string  strFile = @"My Documents\ourProduceCo.sdf";
private string  strConn = "Data Source=" +
　　@"My Documents\ourProduceCo.sdf";
private void mitemCreateDB_Click(object sender, EventArgs e)
{
   if ( File.Exists(strFile) ) { File.Delete(strFile); }
   SqlCeEngine dbEngine = new SqlCeEngine();
   dbEngine.LocalConnectionString = strConn;
   try
   {
      dbEngine.CreateDatabase();
   }
   catch( SqlCeException exSQL )
   {
      MessageBox.Show("Unable to create database at " +
　　strFile +
　　". Reason:  " +
　　exSQL.Errors[0].Message );
   }
}
==<end>==============



===<反灰>=============
private void mitemCreateTables_Click(object sender, EventArgs e)
{
   SqlCeConnection  connDB = new SqlCeConnection();
   SqlCeCommand  cmndDB = new SqlCeCommand();
   connDB.ConnectionString = strConn;
   connDB.Open();
   cmndDB.Connection = connDB;
   
   cmndDB.CommandText =
      " CREATE TABLE Categories " +
      "  ( CategoryID integer not null " +
      "		CONSTRAINT PKCategories PRIMARY KEY " +
      "  , CategoryName nchar(20) not null " +
      "  )";
   cmndDB.ExecuteNonQuery();

   cmndDB.CommandText =
      " CREATE TABLE Products " +
      "  ( ProductID integer not null " +
      "		CONSTRAINT PKProducts PRIMARY KEY " +
      "  , ProductName nchar(20) not null " +
      "  , CategoryID integer not null " +
      "  , CONSTRAINT FKProdCat " +
      "		foreign key (CategoryID) " +
      "		references Categories(CategoryID) " +
      "  )";
   cmndDB.ExecuteNonQuery();
   connDB.Close();
}
==<end>==============
         
         
         
         
===<反灰>=============
private void mitemLoadData_Click(object sender, EventArgs e)
{
   SqlCeConnection  connDB = new SqlCeConnection();
   SqlCeCommand  cmndDB = new SqlCeCommand();

   connDB.ConnectionString = strConn;
   connDB.Open();

   cmndDB.Connection = connDB;
   cmndDB.CommandText =
      " INSERT Categories " +
      "   (CategoryID, CategoryName)" +
      "   VALUES (1, 'Franistans' )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Categories " +
      "   (CategoryID, CategoryName)" +
      "   VALUES (2, 'Widgets' )";
   cmndDB.ExecuteNonQuery();

   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (11, 'Franistans - Large', 1 )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (12, 'Franistans - Medium', 1 )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (13, 'Franistans - Small', 1 )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (21, 'Widgets - Large', 2 )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (22, 'Widgets - Medium', 2 )";
   cmndDB.ExecuteNonQuery();
   cmndDB.CommandText =
      " INSERT Products " +
      "    (ProductID, ProductName, CategoryID)" +
      "    VALUES (23, 'Widgets - Small', 2 )";
   cmndDB.ExecuteNonQuery();

   connDB.Close();
}
==<end>==============

         
         
         
         
===<反灰>=============
intVar = drdrDataReader.GetInt32(0);
//  或明顯低效的
intVar = drdrDataReader.GetValue(0);
//  或
intVar = drdrDataReader.Item["ProductID"];
==<end>==============



===<反灰>=============
strVar = drdrDataReader.GetString(1);
//  或
strVar = drdrDataReader.GetValue(1);
//  或
strVar = drdrDataReader.Item["ProductName"];
==<end>==============




===<反灰>=============
using System;
using System.IO;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;

namespace DataReader
{
   /// <summary>
   /// Summary description for FormMain
   /// </summary>
   public class FormMain : System.Windows.Forms.Form
   {
     internal System.Windows.Forms.Label lblCategoryName;
     internal System.Windows.Forms.Label lblProductName;
     internal System.Windows.Forms.Label lblProductID;
     internal System.Windows.Forms.TextBox textCategoryName;
     internal System.Windows.Forms.TextBox textProductName;
     internal System.Windows.Forms.TextBox textProductID;
     internal System.Windows.Forms.ComboBox comboKeys;

     public FormMain()
       {
         //
         // Required for Windows Form Designer support
         //
         InitializeComponent();
      }
      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         base.Dispose( disposing );
      }
      #region Windows Form Designer generated code


      /// <summary>
      /// The main entry point for the application
      /// </summary>

      static void Main()
      {
         Application.Run(new FormMain());
      }
      //  檔路徑和名稱
      private string  strFile = @"My Documents\ourProduceCo.sdf";

      //  連接字串
      private string  strConn = "Data Source=" +
                                      @"My Documents\ourProduceCo.sdf";

      // 選擇產品主鍵
      private string strGetProductIDs =
         " SELECT ProductID " +      "   FROM Products ";

      //  選擇一個產品，與分屬性關聯
      private string strGetOneProduct =
         " SELECT ProductID, ProductName, CategoryName " +
         "   FROM Products P " +
         "   JOIN Categories C on C.CategoryID = P.CategoryID " +
         "  WHERE P.ProductID = ";

      //  ComboBox載入時使用SelectIndexChanged 事件
       private bool boolLoading = true;

      private void FormMain_Load(object sender, EventArgs e)
      {
         //  顯示關閉框
         this.MinimizeBox = false;

         Application.DoEvents();

         //  確保資料庫存在
         if (! File.Exists(strFile) )
         {
            MessageBox.Show(
               "Database not found.  Run the CreateDatabase " +
               "program for this chapter first.  Then run " +
               "this program.");
         }

         // 裝載產品數值到ComboBox
         // 並選擇第一個
         LoadProductIDs();
         comboKeys.SelectedIndex = 0;
      }

      private void comboKeys_SelectedIndexChanged(object sender,
                                                            EventArgs e)
      {
         // 已經選擇一個產品數值；接收並顯示相關的產品
         if (! boolLoading )
         {
            LoadOneProduct((int)comboKeys.SelectedItem);
         }
      }

      private void textProductName_Validated(object sender,
                                                      EventArgs e)
      {
         //  在資料庫中更新這個產品行
         UpdateSelectedRow(int.Parse(textProductID.Text),
                                textProductName.Text);
      }

      private void LoadProductIDs()
      {
         //  清空ComboBox.
         comboKeys.Items.Clear();

         //  A connection, a command, and a reader
         SqlCeConnection connDB =
            new SqlCeConnection(strConn);
         SqlCeCommand cmndDB =
            new SqlCeCommand(strGetProductIDs, connDB);
         SqlCeDataReader drdrDB;

         //  打開連接
         connDB.Open();

         //  送出SQL語法並接收SqlCeReader作為結果集
         drdrDB = cmndDB.ExecuteReader();

         //  讀每行。只添加列內容作為ComboBox的項目
         //  完成時關閉reader
         while ( drdrDB.Read() )
         {
            comboKeys.Items.Add(drdrDB["ProductID"]);
         }
         drdrDB.Close();

         //  關閉連接
         connDB.Close();

         // 開始回應ComboBox的SelectedIndexChanged事件
         this.boolLoading = false;
      }

      private void LoadOneProduct( int intProductID)
      {
         //  A connection, a command, and a reader
         SqlCeConnection  connDB = new SqlCeConnection(strConn);
         SqlCommand  cmndDB = new SqlCommand(strSQL,connDB);
         SqlCeDataReader drdrDB;

         //  打開連接
         connDB.Open();

         // 設置Command物件去接收通過索引來自一個表的行
         //  然後接收reader.
         cmndDB.Connection = connDB;
         cmndDB.CommandType = CommandType.TableDirect;
         cmndDB.CommandText = "Products";
         cmndDB.IndexName = "PKProducts";
         drdrDB = cmndDB.ExecuteReader();

         //  只接收Products 表中具有ComboBox選擇的ProductID的第一行
         //  裝載欄位到表單的控制項
         //  關閉reader.
         drdrDB.Seek(DbSeekOptions.FirstEqual, intProductID);
            if( drdrDB.Read() )
            {
               LoadControlsFromRow(drdrDB);
            }
         drdrDB.Close();

         //  Close the connection.
         connDB.Close();
      }


      private void LoadControlsFromRow(  SqlCeDataReader drdrDB)
      {
         //  Transfer the column titles and the field
         //     contents of the current row from the
         //     reader to the form's controls.
         lblProductID.Text = drdrDB.GetName(0);
         textProductID.Text = drdrDB.GetValue(0).ToString();
         lblProductName.Text = drdrDB.GetName(1);
         textProductName.Text = drdrDB.GetValue(1).ToString();
         lblCategoryName.Text = drdrDB.GetName(2);
         textCategoryName.Text = drdrDB.GetValue(2).ToString();
      }

      private void UpdateSelectedRow(int intProductID,
                                           string strProductName)
      {
         //  A connection and a command
         SqlCeConnection connDB = new SqlCeConnection(strConn);
         SqlCeCommand cmndDB = new SqlCeCommand();

         //  打開連接
         connDB.Open();

         // 為選擇的產品更新產品名稱
         cmndDB.Connection = connDB;
         cmndDB.CommandText =
            " UPDATE Products " +
            " SET ProductName = " + "'" + strProductName + "'" +
            " WHERE ProductID = " + intProductID;
         cmndDB.ExecuteNonQuery();

         //  關閉連接
         connDB.Close();
      }
   }
}
==<end>==============




===<反灰>=============
private void LoadOneProduct( int intProductID)
{
   //  追加想要ProductID到SELECT語法
    string  strSQL = strGetOneProduct + intProductID;

   //  A connection, a command, and a reader
   SqlCeConnection  connDB = new SqlCeConnection(strConn);
   SqlCommand  cmndDB = new SqlCommand(strSQL,connDB);
   SqlCeDataReader drdrDB;

   // 打開連接
   connDB.Open();

   //  送出SQL語法，接收SqlCeReader 作為單行結果集
   drdrDB = cmndDB.ExecuteReader();

   //  只讀第一行。顯示它。關閉reader.
   if ( drdrDB.Read() )
   {
      LoadControlsFromRow(drdrDB);
   }
   drdrDB.Close();

   //  關閉連接
   connDB.Close();
}
==<end>==============




===<反灰>=============
private void textProductName_Validated(object sender, EventArgs e)
{
    //  在資料庫中更新這個產品名
    UpdateSelectedRow(int.Parse(textProductID.Text), textProductName.Text);
}
==<end>==============



===<反灰>=============
private void UpdateSelectedRow(int intProductID,
　　string strProductName)
{
   //  A connection and a command
   SqlCeConnection connDB = new SqlCeConnection(strConn);
   SqlCeCommand cmndDB = new SqlCeCommand();

   //  打開連接
   connDB.Open();

   //  更新選擇的產品的名稱
   cmndDB.Connection = connDB;
   cmndDB.CommandText =
      " UPDATE Products " +
      " SET ProductName = " + "'" + strProductName + "'" +
      " WHERE ProductID = " + intProductID;
   cmndDB.ExecuteNonQuery();

   //  關閉連接
   connDB.Close();
}
==<end>==============