#眉標= SQL 2005
#副標= SQL 2005嘗鮮導覽
#大標=體驗SQLCLR的威力
#作者=文／李明儒


-----box-----
程式1
[SqlProcedure]
public static void SendMessage(
	String userName, String message)
{
	Process p = new Process();
	p.StartInfo.FileName = "NET.EXE";
	p.StartInfo.Arguments =
		string.Format(
		"SEND \"{0}\" \"{1}\"",
		userName.Replace("\"","\"\""),
		message.Replace("\"", "\"\""));
	//以下的設定是為了取得執行結果
	p.StartInfo.UseShellExecute 
		= false;
	p.StartInfo.RedirectStandardOutput 
		= true;
	p.StartInfo.CreateNoWindow = true;
	p.Start();
	//SqlContext.Pipe是對外溝通的管道
	SqlContext.Pipe.Send(
		p.StandardOutput.ReadToEnd());
}
-----end-----

-----box-----
程式2
[SqlProcedure]
public static void ListFilesSP(
	SqlString path)
{
	//建立要傳回Table的Schema資料
	//包含檔案名稱, 大小與建立時間
	SqlMetaData[] colSchema = 
		new SqlMetaData[3] {
		    new SqlMetaData("FileName", 
					SqlDbType.NVarChar, 
					1024),
		    new SqlMetaData("FileSize", 
					SqlDbType.Int),
		    new SqlMetaData(
					"CreatedDate", 
					SqlDbType.DateTime) 
		};
	SqlMetaData tblSchema = 
		new SqlMetaData("row", 
			SqlDbType.Row, colSchema);
	SqlDataRecord rec = 
		SqlContext.Connection.
		CreateRecord(tblSchema);
	//為求簡化, 不做檔案存取的例外管理
	FileInfo[] fileInfos = 
		new DirectoryInfo(
		path.ToString()).GetFiles();
	//開始傳回ResultSet, false參數表示
	//先傳回Schema不傳回資料
	SqlContext.Pipe.SendResultsStart(
		rec, false);
	foreach (FileInfo file in fileInfos)
	{
		//逐一填入檔案名稱, 大小, 
		//建立時間
		rec.SetSqlString(0, 							file.Name);
		rec.SetSqlInt32(1, 
			(SqlInt32) file.Length);
		rec.SetSqlDateTime(2, 
			file.CreationTime);
		//送回一列結果
		SqlContext.Pipe.
			SendResultsRow(rec);
	}
	//所有結果傳送完畢
	SqlContext.Pipe.SendResultsEnd();
}

-----end-----


-----box-----
程式3
//SqlFunction特徵值中, 一併宣告Table
//Schema
[SqlFunction(
	TableDefinition = 
	"FileName nvarchar(255), FileSize int, CreationTime datetime")
]
public static ISqlReader ListFiles(
	SqlString path)
{
	return (ISqlReader) 
		new FileListSqlReader(
				path.ToString()
			);
}
//另外實作一個SqlReader類別
public class FileListSqlReader : 
	ISqlReader
{
	private FileInfo[] _fileInfos;
	private int _index = -1;
	private int _fileCount = 0;

	public FileListSqlReader(
		string path)
	{
//(為求精簡, 此處不做檔案IO的例外捕捉)
		//取得檔案資訊陣列
		_fileInfos = new 
	DirectoryInfo(path).GetFiles();
		_fileCount = _fileInfos.Length;
	}
	public int FieldCount //傳回欄位數
	{
		get { return 3; }
	}
	public SqlMetaData GetSqlMetaData(
		int FieldNo) //傳回各欄位型別
	{
		switch (FieldNo)
		{
			case 0:
				return new SqlMetaData(
					"FileName", 
					SqlDbType.NVarChar,
					255);
			case 1:
				return new SqlMetaData(
					"FileSize", 
					SqlDbType.Int);
			case 2:
				return new SqlMetaData(
					"CreatedDateTime", 
					SqlDbType.DateTime);
		}
		return null;
	}
	public bool Read()
	{
		if (_index < _fileCount - 1)
		{
			_index++;
			return true;
		}
		else
			return false;
	}
	//檔案名稱的型態是字元
	//如有多個欄位均是字元, 可由i判別
	//分別處理
	public SqlChars GetSqlChars(int i)
	{
		return new SqlChars(
			_fileInfos[_index].Name);
	}
	//如讀取日期, 傳回檔案建立時間
	public SqlDateTime GetSqlDateTime(
		int i) 
	{
		return new SqlDateTime(
			_fileInfos[_index].
			CreationTime);
	}
	//如讀取整數, 傳回檔案大小
	public SqlInt32 GetSqlInt32(int i) 
	{
		return (SqlInt32) 
			_fileInfos[_index].Length;
	}

	//即使沒用到, 也要實作一堆ISqlReader
	//介面要求的Method
	public SqlBinary GetSqlBinary(int i) 
	{ 
		throw new 
			NotImplementedException(); 
	}
	public SqlBoolean GetSqlBoolean(int i) { throw new NotImplementedException(); }
	public SqlByte GetSqlByte(int i) { throw new NotImplementedException(); }
	public SqlInt16 GetSqlInt16(int i) { throw new NotImplementedException(); }
	public bool IsClosed { get { return false; } }
	public void Close() { }
	public bool IsDBNull(int i) { return false; }
	… <<< 過於冗長，以下略 >>>…
}
-----end-----


-----box-----
程式4
//共用的寫檔函數
private static void writeLog(
	string message)
{
	using (StreamWriter sw = 
		new StreamWriter(
			@"C:\TaskMdfy.log", true))
	{
		sw.WriteLine(
		"-------------------------");
		sw.WriteLine(DateTime.Now.
			ToString(
			"yyyy-MM-dd HH:mm:ss"));
		sw.WriteLine(
			"by "+SqlContext.
			WindowsIdentity.Name);
		sw.WriteLine(message);
		sw.Close();
	}
}
//SqlTrigger特徵值要宣告對那個Table的
//那些動作掛上Trigger
[SqlTrigger (Name ="TaskModificationLog", 
	Target ="TaskList", 
	Event ="FOR INSERT,DELETE,UPDATE")]
public static void TaskModificationLog()
{
	SqlTriggerContext ctx = 
		SqlContext.TriggerContext;
	//在SQLCLR中要作資料庫操作, 主要使用
	//System.Data.SqlServer.SqlCommand
	//它與我們平常在用的SqlClient命名空
	//間版本幾乎一樣, 不用另外再學習
	SqlCommand cmd = 
		SqlContext.CreateCommand();
	bool[] colUpdated;
	SqlDataRecord recInsert, recDelete;
	//利用TriggerAction屬性來判別是何種
	//動作期間
	switch (ctx.TriggerAction)
	{
	case TriggerAction.Insert:
		cmd.CommandText = 
			"SELECT * FROM INSERTED";
		recInsert = cmd.ExecuteRow();
		writeLog(
			string.Format(
			"新增TaskId={0}\nTitle={1}\nOwner={2}",
			recInsert.GetInt32(0),
			recInsert.GetString(1),
			recInsert.GetString(2)
			)
		);
		break;
	case TriggerAction.Delete:
		cmd.CommandText = 
			"SELECT taskId FROM DELETED";
		recDelete = cmd.ExecuteRow();
		writeLog(
			string.Format(
			"刪除TaskId={0}",
			recDelete.GetInt32(0)
			)
		);
		break;
	case TriggerAction.Update:
		cmd.CommandText = 
			"SELECT * FROM INSERTED";
		recInsert = cmd.ExecuteRow();
		cmd.CommandText = 
			"SELECT * FROM DELETED";
		recDelete = cmd.ExecuteRow();
		colUpdated = ctx.ColumnsUpdated;
		StringBuilder sb = 
			new StringBuilder();
		sb.Append("變更TaskId=" + 
			recDelete.GetInt32(0) + 
			"的內容\n");
		for (int i=0; 
			i<colUpdated.Length; 
			i++) 
		{
			if (colUpdated[i])
				sb.AppendFormat(
		"更新{0}由'{1}'修改為'{2}'\n",
				recDelete.GetName(i),
				recDelete[i].ToString(),
				recInsert[i].ToString()
				);
		}
		writeLog(sb.ToString());
		break;
	}
}
-----end-----


-----box-----
程式5 
//由於SQL無法自動處理String或
//StringBuilder的序列化, 
//所以要自己寫序列化的邏輯
[Serializable]
[SqlUserDefinedAggregate(
	Format.UserDefined, 
	MaxByteSize=8000)]
public struct Top5 : IBinarySerialize
{
	private StringBuilder sb;
	private int counter;

	public void Init()
	{
		sb = new StringBuilder();
		counter = 0;
	}

	public void Accumulate(
		SqlString Value)
	{
		//沒有值或大於5筆時不處理
		if (counter > 5 || 
			Value.IsNull) return; 
		//如有第六筆顯示...
		if (counter == 5)
			sb.Append(", ...");
		else
		{
			if (counter > 0) 
				sb.Append(",");
			sb.Append(Value);
		}
		counter++;
	}

	public void Merge(Top5 Group)
	{
		sb.Append(Group.sb);
	}

	public SqlString Terminate()
	{
		if (sb ！= null)
			return sb.ToString();
		else
			return new SqlString("");
	}

	#region persistence
	//自行處理序列化的程序
	public void Read(BinaryReader r)
	{
		sb = new StringBuilder(
			r.ReadString());
	}
	public void Write(BinaryWriter w)
	{
		w.Write(
			sb.ToString());
	}
	#endregion
}
-----end-----

 

-----box-----
程式6
//SQL的自動序列化不能處理字串
//所以要實作IBinarySerilize介面
[Serializable]
[SqlUserDefinedType(
	Format.UserDefined, 
	MaxByteSize=20, 
	IsByteOrdered=true)]
public struct CivicId : 
	INullable, IBinarySerialize
{
	private string _civicId;
	private bool _null;

	public override string ToString()
	{
		return _civicId;
	}

	public bool IsNull
	{
		get
		{
			return _null;
		}
	}

	public static CivicId Null
	{
		get
		{
			CivicId h = new CivicId();
			h._null = true;
			return h;
		}
	}

	public static CivicId Parse(
		SqlString s)
	{
		if (s.IsNull)
			return Null;
		CivicId u = new CivicId();
		if (isValidCivicId(s.ToString()))
		{
			u._civicId = s.ToString().
				ToUpper();
			u._null = false;
		} 
		else
			throw new 
				ApplicationException(
				"無效的身分證字號");
		return u;
	}

	//身份證字號檢查號碼驗算
	private static bool 
		isValidCivicId(string civicId) 
	{
		//身份證第一碼對應到的數字
		int[] alpha2Num = new int[] { 10, 11, 12, 13, 14, 15, 16, 17, 34, 18, 19, 20, 21, 22, 35, 23, 24, 25, 26, 27, 28, 29, 32, 30, 31, 33 };
		civicId = civicId.ToUpper();
		if (！Regex.IsMatch(
			civicId,"[A-Z][12][0-9]{8}"
			)) 
			return false;
		//計算檢查號碼
		int a = alpha2Num[
			civicId.ToCharArray()[0]-65
			];
		int sum = a/10+(a % 10)*9;
		for (int i=1;i<=8;i++) 
			sum += int.Parse(
				civicId.Substring(i,1)
				)*(9-i);
		sum += int.Parse(civicId.
			Substring(9,1));
		if ((sum % 10)！=0) 
			return false;
		return true;
	}

	public SqlString Gender
	{
		get
		{
			if (！_null)
				return 
	(_civicId.Substring(1, 1) == "1") 
					？"男" : "女";
			else
				return "N/A";
		}
	}

	#region 自訂序列化
	public void Write(
		System.IO.BinaryWriter w)
	{
		w.Write(_civicId);
	}
	public void Read(
		System.IO.BinaryReader r)
	{
		string s = r.ReadString();
		if (isValidCivicId(s))
		{
			this._civicId = s;
			this._null = false;
		}
		else this._null = true;
	}
	#endregion
}
-----end-----