ADO.NET #3: The Lightwight Model #2: Modify Data

   

    บทนี้เรามาดูกันต่อเรื่องของการทำการปรับปรุงข้อมูล การทำงานในเรื่องของฐานข้อมูลนั้น ไม่หนีการทำงาน 4 ลักษณะดังนี้

    เมื่อบทที่แล้วนั้น เราได้เรียนรู้การดูข้อมูลไปแล้ว วันนี้เรามาเรียนรู้กันต่อถึงการทำงานอีก 3 ลักษณะที่เหลือ แต่ต้องมาทำความเข้าใจกันก่อนว่า ADODataReader และ SQLDataReader นั้นการทำงานคือการที่ดึงเอาผลลัพธ์ที่ SQL Server หรือ DataSource อื่นส่งมาให้ ส่งต่อให้กับโปรแกรมของเราเลย เราจึงไม่สามารถปรับปรุงข้อมูลที่รับได้โดยตรง ดังนั้น เราจึงต้องใช้วิธีส่งคำสั่ง SQL กลับไปถ้าเราต้องการที่จะปรับปรุงข้อมูล

    บางคนอาจคิดว่าวิธีนี้ยุ่งยาก มันก็จริงครับ แต่การที่เราส่งคำสั่งที่ใช้ในการปรับปรุงข้อมูลได้แก่ INSERT, UPDATE, DELETE ไปให้กับ SQL Server โดยตรงนั้น ถือว่าเป็นวิธีที่มีประสิทธิภาพที่สุด ซึ่งต่างกับ DataSource บางประเภท ถ้า DataSource นั้น(เช่น dBase) ไม่สามารถรองรับคำสั่ง SQL ได้ เวลาสั่งงานเป็นตำสั่ง SQL ตัวของ OLEDB ต้องทำการแปลงก่อนหนึ่งชั้น ทำให้โปรแกรมอาจจะทำงานช้ากว่า ที่เราจะใช้เรื่องของ Client Cursor (เราจะได้เรียนในบทต่อไป) เอาเป็นหลักการนี้นะครับ ถ้า DataSource นั้นเข้าใจคำสั่ง SQL ได้โดยตรง เช่น Oracle, MSSQL เป็นต้น การส่งคำสั่งเป็น SQL เลยจะได้ประสิทธิภาพสูงสุด แต่ถ้า DataSource นั้นไม่รองรับคำสั่ง SQL การส่งคำสั่ง SQL ไปอาจจะทำให้การทำงานช้าลง

    ในบทนี้ผมเน้นการใช้งานกับ Microsoft SQL Server เท่านั้น โดยที่ผมจะเขียนโปรแกรมดึงข้อมูลทุกรายการจากตาราง authors ที่อยู่ใน pubs ไปเก็บไว้ใน Table anames ที่อยู่ pubs เช่นเดียวกัน โดยที่ผมจะลอกไปเฉพาะ ชื่อและนามสกุลเท่านั้น

    ต้องมาคุยกันก่อนว่า ผมไม่ได้สอนคำสั่ง SQL ผมถือว่าพื้นฐานการใช้งานคำสั่ง SQL นั้นทุกคนต้องใช้เป็นอยู่แล้ว ผมเน้นถึง ADO.NET เท่านั้นครับ

    เราลองดู Code กัน

authors.cs

 
using System;
using System.Text;
using System.Data.SQL;
class Hello
{ 	
	static SQLConnection CNRead, CNWrite;
	
	static SQLDataReader ReadData(string strSQL)
	{
		SQLDataReader DR;
		SQLCommand CMMRead;
		CNRead = new SQLConnection("localhost", "sa", "", "pubs"); 		
		CMMRead = new SQLCommand("SELECT * FROM authors", CNRead);
		CNRead.Open();
		CMMRead.Execute(out DR);
		return DR;
	}
		 	
	public static SQLCommand ConnectForWrite()
	{
		SQLCommand CMMWrite;
		CNWrite = new SQLConnection("localhost", "sa", "", "pubs");
		CNWrite.Open();
		CMMWrite = new SQLCommand();
		CMMWrite.ActiveConnection = CNWrite;
		return CMMWrite;
	}
	
	static void CloseConnections()
	{
		CNRead.Close();
		CNRead = null;
		CNWrite.Close();
		CNWrite = null;	
	}
	
	public static void Main()
	{ 	
		SQLException ee = null;
		string strSQL; 	
	
		SQLDataReader DR = ReadData("SELECT au_lname, au_fname FROM authors");
		SQLCommand CMMWrite = ConnectForWrite();			
		strSQL = "IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('aname')) DROP TABLE aname";
		CMMWrite.CommandText = strSQL;
		CMMWrite.ExecuteNonQuery();

		strSQL = "CREATE TABLE aname (fname varchar(40), lname varchar(40) )";
		CMMWrite.CommandText = strSQL;
		CMMWrite.ExecuteNonQuery();	

		CNWrite.BeginTransaction();
		try {
			while (DR.Read()) {
				strSQL = string.Format("INSERT INTO aname (fname, lname) VALUES ('{0}', '{1}')", 
						DR["au_fname"].ToString(), 
						DR["au_lname"].ToString()
				);
				CMMWrite.CommandText = strSQL;
				CMMWrite.ExecuteNonQuery();
			}
		}
		catch (SQLException e) {
			ee = e;
		}
		
		if (ee == null) { 
			CNWrite.CommitTransaction();
			Console.WriteLine("Done");
		} else {
			CNWrite.RollbackTransaction();
			Console.WriteLine("Command: {0}", strSQL);
			Console.WriteLine("Error {0}: {1}", ee.Number, ee.Message);
		}
		DR.Close();
		CMMWrite = null;
		CloseConnections();
	}
}

เวลา Compile คุณต้องใช้ csc /r:system.dll /r:system.data.dll authors.cs

 สิ่งที่น่าสนใจในโปรแกรม

DOS Prompt
C:\CS>authors
Command: INSERT INTO aname(fname, lname) VALUES ('Michael', 'O'Leary')
Error 170: Line 1: Incorrect syntax near 'Leary'

C:\CS>_

    จะเห็นได้ว่าคำสั่งที่เราส่งไปนั้นผิด Syntax เราสามารถดักได้โดย try-catch block ทำให้เราสามารถจัดการเมื่อมี Error เกิดขึ้น ข้อผิดพลาดที่เกิดจากใน ADO.NET นั้นจะชื่อว่า SQLException ซึ่งภายในจะมีตัวเลข Error กำกับพร้อมกับคำอธิบาย เช่นใน ที่นี่เกิด Error หมายเลข 170 ซึ่งก็คือ Syntax Error นั่นเอง

    นอกเหนือจากนั้นผมยังแสดงให้ดูว่าเมื่อเกิด Error ขึ้น มันจะเกิดการ RollBack ของข้อมูลทำให้รายการที่ถูก Insert ก่อนหน้านั้นหายหมด คุณลองไป SELECT ข้อมูลดูได้ จะเห็นแค่มี Table เปล่าๆ ไม่มีข้อมูล

    แต่ Table aname ยังคงมีอยู่เพราะในโปรแกรมมัน เริ่ม BeginTransaction หลังจากการสร้าง Table เสร็จแล้ว

    ทางแก้ปัญหาของ Error ข้างบน ทำได้โดย ถ้าตรงไหนมี ' อยู่ให้เพิ่มเข้าไปอีกตัว เช่น ถ้าต้องการใส่ O'Leary ให้เพิ่มเป็น O''Leary เป็นต้น ดังนั้นผมจะใช้ Regular Expression คำสั่ง Replace มาแปลง ให้ แก้ โปรแกรมดังนี้ครับ

จาก

strSQL = string.Format("INSERT INTO aname (fname, lname) VALUES ('{0}', '{1}')", 
		DR["au_fname"].ToString(), 
		DR["au_lname"].ToString()
);

เป็น

strSQL = string.Format("INSERT INTO aname (fname, lname) VALUES ('{0}', '{1}')", 
		Regex.Replace(DR["au_fname"].ToString(), @"'", @"''"), 
		Regex.Replace(DR["au_lname"].ToString(), @"'", @"''")
);

 แต่การที่คุณสามารถใช้ Regular Expression ได้นั้น คุณต้องเพิ่ม

using System.Text.RegularExpressions;

และเวลาคอมไพล์โปรแกรม ต้อง Compile ด้วย

csc /r:system.dll /r:system.data.dll /r:system.text.regularexpressions.dll authors.cs