ว่างเว้นกันไปถึงสองอาทิตย์ที่เดียว กว่าผมจะมีเวลามานั่งเขียนบทความได้อีกครั้ง หลังจากที่ผมก็ทำนี่ ทำนู่น พาแม่ไปเที่ยวบ้าง พาตัวเองเที่ยวบ้าง คราวนี้ก็ถึงคิวของอุดมการณ์บ้างแล้วละ เหอๆ

หลังจากที่ในบทความคราวที่แล้ว เราได้พูดถึงการวางโครงสร้างของ Application ขั้นต้นกันไปแล้ว ถ้าลองมาทบทวนกันอีกครั้ง เราได้ทำการ…

• สร้างคลาส Session เพื่อใช้ในการเก็บตัวแปร และค่าต่างๆ ของ Application ไว้ เพื่อสะดวกต่อการเรียกใช้งานจากที่ไหนก็ได้ ใน Application และยังทำให้โปรแกรมเราสามารถ Hibernate ได้อีกด้วย
• สร้างคลาส Tracing เพื่อเก็บ Setting ของระดับของการแสดงข้อความ Trace ออกมา โดยผมสร้างเป็น Enum ไว้ เป็น Flags ต่างๆ

หรือนั่นก็คือ เรามีพื้นฐานที่แข็งแรงสำหรับความปวดหัวขั้นต่อไปแล้ว นั่นก็คือ การใช้งาน ฐานข้อมูล SQL Server Compact Edition และความน่าเชื่อถือของ Application ของเราในการเก็บข้อมูล เรียกใช้ข้อมูล รวมไปถึงการ Sync ข้อมูลกับฐานข้อมูลหลัก ซึ่งอาจจะอยู่ใน Server ที่อุปกรณ์ Windows Mobile อาจจะไม่สามารถเข้าถึงได้ตลอดเวลาอีกด้วย

การสร้างฐานข้อมูล

ในการสร้างฐานข้อมูลสำหรับ SqlCe สามารถทำได้โดยตรงจาก Visual Studio ครับ โดยการ Add File เข้ามา

หลังจากนี้ Visual Studio จะถามให้ตั้งค่าเกี่ยวกับ DataSet ตอนนี้ให้ข้ามไปก่อน โดยการกด  Cancel หลังจากสร้างเรียบร้อย ก็จะพบว่า ฐานข้อมูลมาปรากฏอยู่ใน Server Explorer โดยอัตโนมัติครับ

จากนั้น ก็สามารถคลิ๊กขวาที่ Tables แล้วเลือก Create Table เพื่อสร้างตารางตามที่ต้องการได้เลย

สำหรับการตั้งค่าให้ Field เป็น Auto Number ก็ทำได้โดยการเปลี่ยนชนิดเป็น int และ เลือก Identity เป็น True พร้อมทั้งตั้งค่า Unique และ Primary Key เป็น Yes ได้เลยครับ

แต่ถ้าต้องการแค่จะทดลอง สามารถใช้ไฟล์ Northwind.sdf ที่อยู่ใน C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Samples ได้ครับ (ถ้าเครื่อง 32-bit ก็ไม่ต้องมี (x86) นะครับ)

แล้วจะใช้ยังไง?

แต่ก่อนที่คุณจะสามารถใช้งานฐานข้อมูลได้ ก็คงจะต้องมีคลาสสำหรับการติดต่อฐานข้อมูลเสียก่อน สำหรับผู้ใช้งาน Windows Form หรือ WPF บน Desktop ก็แทบจะสบายใจกับจุดนี้ได้เลย เพราะด้วยเทคโนโลยีใหม่ๆ ในปัจจุบัน อย่างเช่น LINQ to SQL หรือ Entity Framework แม้แต่ทางฝั่ง Open Source ก็ยังมี nHibernate ให้โหลดเซฟข้อมูลเข้าฐานข้อมูลได้ โดยแทบไม่ต้องเขียนโค๊ดเลยซักบรรทัด

แต่น่าเสียดาย ที่ของเรายังไม่มีครับ ก็เลยยังจะต้องมีคลาสติดต่อฐานข้อมูลอยู่ ผมขอแนะนำคลาสต่อฐานข้อมูลในลักษณะที่ผมใช้อยู่เป็นประจำ ก่อนที่จะเปลี่ยนมาเล่น LINQ to SQL และ Entity Framework เลยก็แล้วกันครับ

/* CoreSharp.NET DataLayer.cs License * Copyright 2009 Jirawat P. - CoreSharp.net * ========================================= * THIS LIBRARY IS PROVIDED AS-IS WITHOUT ANY WARRANTY. * * You may use and modify this library to 'legally' create * any application that has legimate purpose, * only requirement is that this license block must * remains in the source file. * * If you did not agree to the terms above, * please do not use this library. * * Author of this file takes no responsibility of the * outcome from the use of this file. */ using System; using System.Collections.Generic; using System.Data.Common; using System.Diagnostics; using System.Text; using CoreSharp.Lib; namespace CoreSharp.Data { /// <summary> /// Database Abstraction /// </summary> public abstract class DataLayer<T> where T : DbConnection, new() { /// <summary> /// Override this to return valid connection string /// </summary> public abstract string ConnectionString { get; } private DbCommand _ParameterFactory = (new T()).CreateCommand(); /// <summary> /// Creates the parameter. /// </summary> /// <param name="name">The name.</param> /// <param name="value">The value.</param> /// <returns></returns> public DbParameter CreateParameter( string name, object value ) { var param = _ParameterFactory.CreateParameter(); param.ParameterName = name; param.Value = value; return param; } /// <summary> /// Create new Connection /// </summary> /// <returns></returns> protected virtual DbConnection GetConnection() { T conn = new T(); conn.ConnectionString = this.ConnectionString; try { conn.Open(); Trace.WriteLineIf(Tracing.Verbose, "Connection Open", "DataLayer"); } catch (Exception ex) { Trace.WriteLineIf(Tracing.Exception, "Cannot Connect, " + ex.Message, "DataLayer"); throw new Exception("Cannot Open Database Connection: " + ex.Message, ex); } return conn; } /// <summary> /// Retreive the parameters /// </summary> /// <param name="cmd">The cmd</param> /// <returns></returns> private string HarvestParameters(DbCommand cmd) { StringBuilder sb = new StringBuilder(); foreach (DbParameter item in cmd.Parameters) { sb.AppendLine(string.Format("{0}='{1}'", item.ParameterName, item.Value)); } return sb.ToString(); } /// <summary> /// Execute stored procedure /// </summary> /// <param name="command"></param> /// <param name="param"></param> public IEnumerable<DbDataReader> ExecuteReader(string command, IEnumerable<DbParameter> parameters) { DbConnection conn = this.GetConnection(); using (DbCommand cmd = conn.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = command; if (parameters != null) { foreach (DbParameter param in parameters) { cmd.Parameters.Add(param); } } DbDataReader reader = null; try { reader = cmd.ExecuteReader(); } catch (Exception ex) { throw new Exception( string.Format("Cannot ExecuteReader, Reason:{2}\r\n\tCommand:{0}\r\n\tParameters:{1}", cmd.CommandText, this.HarvestParameters(cmd), ex.Message), ex); } using (reader) { while (reader.Read()) { yield return reader; } reader.Close(); } // end using reader } } /// <summary> /// Executes the query. /// </summary> /// <typeparam name="V"></typeparam> /// <param name="command">The command.</param> /// <param name="parameters">The parameters.</param> /// <returns></returns> public IEnumerable<ISupportDataLayer> ExecuteQuery<TResult>(string command, IEnumerable<DbParameter> parameters) where TResult : ISupportDataLayer, new() { foreach (var reader in this.ExecuteReader( command, parameters )) { TResult result = new TResult(); result.Initialize(reader); yield return result; } } /// <summary> /// Execute the command /// </summary> /// <param name="command"></param> /// <param name="param"></param> public int ExecuteNonQuery(string command, IEnumerable<DbParameter> parameters) { DbConnection conn = this.GetConnection(); using (DbCommand cmd = conn.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = command; if (parameters != null) { foreach (DbParameter param in parameters) { cmd.Parameters.Add(param); } } try { return cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception( string.Format("Cannot ExecuteNonQuery, Reason:{2}\r\n\tCommand:{0}\r\n\tParameters:{1}", cmd.CommandText, this.HarvestParameters(cmd), ex.Message ), ex ); } } //end using cmd } /// <summary> /// Execute stored procedure /// </summary> /// <param name="command"></param> /// <param name="param"></param> public TResult ExecuteScalar<TResult>(string command, IEnumerable<DbParameter> parameters) { using (DbConnection conn = this.GetConnection()) { using (DbCommand cmd = conn.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = command; if (parameters != null) { foreach (DbParameter param in parameters) { cmd.Parameters.Add(param); } } try { return (TResult)cmd.ExecuteScalar(); } catch (Exception ex) { throw new Exception( string.Format("Cannot ExecuteScalar, Reason:{2}\r\n\tCommand:{0}\r\n\tParameters:{1}", cmd.CommandText, this.HarvestParameters(cmd), ex.Message ), ex); } } //end using cmd } // end using connection } } }

จุดน่าสนใจ

• ผมทำการ New Connection ใหม่ทุกครั้ง เพื่อที่จะใช้ความสามารถ Connection Pooling ที่ตัว Database อาจจะรองรับครับ (อย่าลืมว่า คลาสนี่้ใช้ System.Data.Common)
• ในการ Return ค่า ผมไม่ได้ใช้การอ่านค่าแล้วคืนออกไป แต่เป็นการ “Yield Return” ตัว Data Reader ออกไปแทน นั่นก็หมายความว่า คุณสามารถอ่านค่าจาก Database ได้โดยตรง โดยไม่ต้องพึ่ง DataSet หรือคลาสอื่น ช่วยให้ประหยัดแรมมากขึ้น ในการอ่านเพื่อ Initialize ค่าของ Data Class หรือพวก Middle Tier Class ใน Memory
• ผมเลือกที่จะไม่ใช้ SqlCeResultSet เพราะว่ามันเป็นเรื่องเฉพาะของ SQL Server Compact Edition ครับ ถ้าครั้งหน้า เกิดเราต้องการจะเปลี่ยนเป็น SQLite หรือเกิดมีฐานข้อมูลแบบอื่นขึ้นมาที่สนับสนุนมาตรฐาน ADO.NET การแปลงโค๊ด (จำเป็นต้องแปลงเองเนื่องจาก Compact Framework ไม่มี Provider Factory ในSystem.Data.Common) จะทำได้ง่ายกว่า
• ฟังก์ชั่น CreateParameter จำเป็นต้องใช้ Trick เล็กน้อย โดยการ New T ขึ้นมา แล้วเก็บ Command ไว้ เนื่องจากเราไม่มี Provider Factory เราเลยต้องใช้วิธีนี้เพื่อเอา Parameter ที่ตรงกับเทคโนโลยีฐานข้อมูลที่เราใช้ออกมาครับ
• เวลาเกิด Exception คลาสนี้จะมีการเก็บ Command ที่ทำให้เกิด Exception และ Parameter ทั้งหมด ที่ถูกส่งเข้าม ซึ่งจะมีประโยชน์มากๆ ตอน Debug ครับ

DbConnection, DbCommand?

ถ้าหากนี่คือครั้งแรกของการใช้ ADO.NET การโยนโค๊ดนี้ให้ไปเลย ก็คงอาจจะเป็นการทำร้ายผู้อ่านมากเกินไป เพราะเดี๋ยวก็จะไม่เข้าใจการทำงานที่แท้จริงของมัน และจะต้องพึ่งพาอาศัยโค๊ดนี้ของผมตลอดไป ก่อนอื่นเลย ขอย้ำอีกครั้งว่า การใช้งานฐานข้อมูลสำหรับยุคนี้ คือ LINQ to SQL หรือ Entity Framework ครับ ไม่ใช่การต่อแบบดิบๆ แบบนี้ แต่เนื่องจากใน Windows Mobile ยังไม่มีความสามารถเหล่านั้นให้ใช้ จึงเป็นโอกาสที่ดี ที่เราได้จะได้เรียนรู้ว่า ในเบื้องหลังของ LINQ2SQL, Entity Framework มันมีการทำงานอย่างไร และความดิบนี่ละครับ คือเสน่ห์ของ Windows Mobile มันยังมีพื้นที่เหลือให้เราเล่นอะไรอีกเยอะครับ

สำหรับขึ้นตอนการติดต่อฐานข้อมูล จะเป็นดังนี้เสมอครับ

• สร้าง Instance ของ XConnection
• ใส่ค่า Connection String
• สั่งคำสั่ง Open ของ XConnection เพื่อเริ่มการติดต่อฐานข้อมูล
• สร้าง Instance ของคลาส XCommand
• ตั้งค่าคำสั่ง SQL ที่ต้องการ
• เพิ่ม Parameter ลงในคำสั่ง และตั้งค่าของ Parameter นั้น
• เริ่มการทำงาน โดย
• เรียกใช้คำสั่ง ExecuteReader สำหรับคำสั่ง SELECT
• เรียกใช้คำสั่ง ExecuteNonQuery สำหรับคำสั่งอื่นๆ
• เรียกดูค่าของ Parameter ชนิด Out/Inout ในกรณีที่เป็น Stored Procedure
• สั่งปิด Connection
• สั่ง Dispose

สำหรับสาเหตุที่ใช้คลาส DbXXX นั้น ก็เพื่อที่ว่า คลาสติดต่อฐานข้อมูลนี้ จะได้ใช้งานได้กับทุกฐานข้อมูลครับ ซึ่งปัจจุบันก็มีอยู่ด้วยกันสองทางเลือกคือ Sql Compact และ SQLite

การใช้งานสำหรับโปรเจคของคุณ

แต่สังเกตว่าคลาสที่ผมให้ไปนั้น เป็น Abstract ครับ ไม่สามารถทำไปใช้งานได้จริง (อ้าว) การจะนำไปใช้งาน จะต้องสร้างคลาสที่สืบทอดจากคลาสนี้เพิ่ม และกำหนดชนิดของฐานข้อมูลที่คุณต้องการใช้ และเพิ่มฟังก์ชั่นที่คุณต้องการ โดยอาจจะเป็นฟังก์ชั่นที่ไปเรียก Stored Procedure อีกทีหนึ่ง สำหรับ Desktop แต่เนื่องจากเราไม่มี SP ใน SqlCe ฟังก์ชั่นที่เราจะเขียนขึ้น ก็คงจะเป็นฟังก์ชั่นที่รันคำสั่ง SQL นั่นเอง

using System.IO; using System.Collections.Generic; using System.Data.Common; using System.Data.SqlServerCe; using CoreSharp.Data; namespace Project.Data { /// <summary> /// /// </summary> public class NorthwindData : DataLayer<SqlCeConnection> { public static readonly SqlCeParameter[] EmptyParameter = new SqlCeParameter[0]; private string _ConnectionString; public override string ConnectionString { get { if (_ConnectionString == null) { _ConnectionString = "Data Source=" + Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Northwind.sdf"; } return _ConnectionString; } } #region Singleton Pattern private NorthwindData() { } private static NorthwindData _Default; ///<summary> /// Get the default instance of SqlCeDataLayer ///</summary> public static NorthwindData Default { get { if ( _Default == null ) { _Default = new NorthwindData(); } return _Default; } } #endregion /// <summary> /// Gets total categories. /// </summary> /// <returns></returns> public static int GetTotalCategories() { return NorthwindData.Default.ExecuteScalar<int>("SELECT Count(*) FROM Categories", NorthwindData.EmptyParameter); } /// <summary> /// Gets total categories. /// </summary> /// <returns></returns> public static IEnumerable<DbDataReader> GetProductByCategory(int id) { return NorthwindData.Default.ExecuteReader("SELECT * FROM Products WHERE [Category ID] = @id", new DbParameter[] { NorthwindData.Default.CreateParameter("@id", id ) }); } } }

จุดน่าสนใจ

• ในคลาสนี้ ฟังก์ชั่นที่ใช้ในการติดต่อฐานข้อมูลจะเป็น Static ทั้งหมด เพื่อความสะดวกในการเรียกใช้ เพราะว่าฟังก์ชั่นพวกนี้ ไม่มีการเก็บ State (เช่น ไม่จำเป็นต้องเรียก GetCategories ก่อน ถึงจะเรียก GetProductByCategoryId ได้ เพราะนั่นเป็นหน้าที่ของคนเรียก)
• คลาสนี้เป็น Singleton จริงๆ คือ ไม่สามารถ New NorthwindData ได้ เนื่องจาก Constructor เป็น Private
• เราสามารถหาไดเรกทอรีของโปรเจคที่รันอยู่ได้ โค๊ดยาวหน่อย ดูได้จากใน get ConnectionString ครับ
• เราสามารถ “เลือก” Inherit คลาสแม่โดยเลือก Type Parameter ได้ อย่างเช่นคราวนี้ NorthwindData เลือก Inherit คลาส DataLayer แต่เป็นคลาส DataLayer ที่เป็น SqlCeConnection หรือใช้ SqlCe นั่นเอง ถ้าครั้งหน้า ต้องการใช้ SQLite ก็เพียงเปลี่ยนตรงจุดนี้ เท่านั้น (เช่นกัน เนื่องจากใน Compact Framework ไม่มี Provider Factory เลยต้องใช้การแก้โค๊ด)

การใช้งานเพื่ออ่านข้อมูล

ในการอ่านข้อมูล ทางที่ดี และเร็วที่สุดก็คือการใช้งานฐานข้อมูลโดยตรงผ่านคำสั่งของ Data Layer ครับ แน่นอนว่า สิ่งที่เสียไปคือ เราไม่สามารถใช้ Tool ในการ สร้าง Dataset ให้เองได้โดยอัตโนมัติ และอาจจะต้องใช้เวลาในการพัฒนานานขึ้นกว่าเดิม แต่สิ่งที่ได้กลับคืนมาคือความสามารถในการควบคุมสิ่งต่างๆ ได้อย่างใจนึก โดยที่เราไม่ต้องทำความเข้าใจ และยืนยันว่า การทำงานของ DataSet นั้น ตรงตามความต้องการของเราหรือไม่

การเรียกใช้โดยตรง

การเรียกใช้โดยตรง ก็สามารถทำได้ง่ายๆ ตามนี้ครับ

foreach (var item in NorthwindData.GetProductByCategory(1)) { this.listbox1.Items.Add(item.GetString(3))); }

การสร้างคลาส Middle Tier (Business Logic/Business Layer)

ถ้าต้องการสร้างคลาสแบบ Middle Tier เราจะต้องทำการแก้ไข Data Layer ของเราเล็กน้อยครับ นั่นก็คือ

• สร้าง Interface เพื่อกำหนด Function สำหรับให้ DataLayer สร้าง Instance ของคลาส Middle Tier จาก DataLayer ได้ โดยที่ไม่ต้องรู้จักกับคลาสนั้น
• เพิ่มฟังก์ชั่น Execute อีกแบบ ที่มีชนิดเป็น Generic ที่เรียกใช้ ExecuteReader และ Yield Return คลาสที่ Implement Interface นั้นออกมาแทน
• สร้างคลาส Middle Tier ที่ Implement ISupportDataLayer และใส่โค๊ด

หรืออีกทางหนึ่งที่ทำได้คือ แก้ไขฟังก์ชั่น จากที่เคย Return เป็น IEnumerable<DbDataReader> เป็น IEnumerable<Product> แทน แล้วทำการ Initialize ค่าในนั้น

อย่างไรก็ตาม การสร้างโค๊ด Middle Tier เอง ก็อาจจะมีความเหลื่อมล้ำกันอยู่ ระหว่างให้ Visual Studio Gen คลาส DataSet ให้เอง เพื่อจะได้ไม่ต้องสร้างคลาสนี้ หรือเลือกที่จะ Implement คลาสพวกนี้เอง ซึ่งอาจจะเป็นเรื่องที่ตัดสินใจได้ลำบากพอสมควร แต่สำหรับผมแล้ว ผมเลือกที่จะสร้างคลาสนี้เองครับ โดยผมมี Tool สำหรับสร้างคลาสนี้ของผมเองเลยทีเดียว เพื่อที่ว่า คลาส Middle Tier นี้ จะได้ไม่ถูกผูกติดกับเทคโนโลยีใดเทคโนโลยีหนึ่ง และยังเปิดช่องทางให้เราสามารถใช้คลาสนี้ ในการส่งผ่านข้อมูลด้วย Web Service, REST ในอนาคตได้อีกด้วย

รายละเอียดการสร้าง Middle Tier นี้ ผมเก็บไว้ให้คิดเป็นการบ้านครับ แต่ถ้าต้องการคำแนะนำ สามารถมาโพสถามได้ในฟอรั่มเสมอครับ

ยังเหลืออะไรให้ลองเล่นอีก?

จริงๆ แล้วโค๊ดที่ผมเสนอให้ ยังมีข้อบกพร่องอยู่สองจุดครับ

• ในการ Execute Non Query ผมยังไม่ได้เปิด Transaction สาเหตุที่ต้องใช้ Transaction ก็เพราะว่า การเปลี่ยนแปลงกับฐานข้อมูลผ่าน Transaction จะเป็นการทำงานในหน่วยความจำ แล้วจึงค่อยเขียนลงไปทีเดียว ตอนสั่ง Commit จึงทำให้การทำงานเป็นไปได้เร็วกว่าการค่อยๆ เขียนทีละเรคคอร์ดมากครับ
• SQL CE นั้น ไม่มีระบบ Connection Pooling ทำให้การเปิด-ปิด ฐานข้อมูลบ่อยๆ นั้นค่อนข้างช้า ผมลองหาในเน็ตดู มีคนเสนอวิธีไว้บ้างแล้วเหมือนกัน ลองเอามาประยุกต์ใช้ดูได้ครับ

ส่งท้าย

เอาละครับ ผมได้นำเสนอวิธีการติดต่อฐานข้อมูลแบบเร็วที่สุดเท่าที่จะทำได้จากฝั่ง .NET ไปให้แล้ว ในขั้นต่อไป ก็คือการเลือกใช้ Index และ Key รวมไปถึงวิธีการทำ Caching ที่เหมาะสม เพื่อเพิ่มประสิทธิภาพให้สูงยิ่งขึ้นไปอีก สำหรับในโพสต่อไป ผมจะพูดถึงการนำเอาข้อมูลมาแสดงบน UI ด้วยการใช้ Data Binding ครับ ซึ่งจะนำเสนออีกทางเลือกหนึ่่งในการติดต่อฐานข้อมูลให้่อ่านกันด้วย คอยติดตามด้วยนะครับ