relyky’s gists

relyky / SQL Server 2008 Trigger - Basic Practice.sql

Last active August 29, 2015 14:28

SQL Server 2008 Trigger - Basic Practice

	USE [MyLabDB]
	GO

	/**** Object: Table [dbo].[MyTable01] Script Date: 08/23/2015 22:44:37 ****/
	SET ANSI_NULLS ON
	GO

	SET QUOTED_IDENTIFIER ON
	GO

relyky / SQL2008 Trigger Code Template 1 - Simple Example.sql

Last active October 1, 2015 17:22

SQL Server 2008 Trigger Code Template

	-- =============================================
	-- Description: 簡單版，也是最常用的狀況
	-- 注意：SQL Server Trigger與Oracle DB Trigger的設計觀完全不同。
	-- SQL Server Trigger 的處理對象為一整批；而Oracle DB 是單一筆且可指定到欄位。
	-- 也就是 inserted 或 delected 是有可能是多筆的；而Oracle trigger一定是一筆。
	-- =============================================
	CREATE TRIGGER [dbo].[FooTable_Trigger]
	ON [dbo].[FooTable]
	AFTER INSERT, DELETE, UPDATE
	AS

relyky / SQL Server 2008 Cursor Example.sql

Last active April 21, 2016 09:37

T-SQL, SQL Server 2008, Local Cursor, Dynamic Cursor

	--===========================================================
	-- To count all tables rows of current database
	--===========================================================

	-- resource
	DECLARE @SqlCmd NVARCHAR(500);
	DECLARE @TableName VARCHAR(100), @SchemaName VARCHAR(50),@RowCnt BIGINT;
	DECLARE @TableRowCnt TABLE
	( TableName VARCHAR(100)
	, SchemaName VARCHAR(50)

relyky / Oracle DB Dynamic SQL Statements and Temporary Table.sql

Last active October 6, 2015 11:18

Oracle DB Dynamic SQL Statements and Temporary Table.sql

	--===========================================================
	-- To count all tables rows of current database
	-- with: Dynamic SQL Statements, Temporary Table, Cursor
	--===========================================================

	-- create temporary table
	CREATE GLOBAL TEMPORARY TABLE t_TableRowCnt (
	TableName VARCHAR2(50)
	, RowCnt NUMBER
	) ON COMMIT DELETE ROWS;

relyky / Application.DoEvents Sample.cs

Last active November 23, 2017 03:50

WinForm防止程式「沒有回應」的小技巧, Application.DoEvents, no response

	/// <summary>
	/// 使用 DoEvents 防止沒有回應
	/// </summary>
	private void button1_Click(object sender, EventArgs e)
	{
	//## 使用 DoEvents 防止沒有回應
	for (int i = 0; i <= 999999; i++)
	{
	// ProcessDataPiece[i]
	label1.Text = i.ToString();

relyky / VB6 計算中文字串長度.bas

Last active November 6, 2015 03:30

VB6 計算中文字串長度

	'參考文章：
	'http://blog.xuite.net/moonvilla/mypcnote/43694699-%E8%A8%88%E7%AE%97%E4%B8%AD%E6%96%87%E5%AD%97%E4%B8%B2%E9%95%B7%E5%BA%A6
	'http://ctnet.ctsh.mlc.edu.tw/ctpcm/Dhtml/VB_EX/vbext005.htm

	' 欲計算字串 S 的長度
	N = 0
	For I = 1 To Len(S)
	C = Asc(Mid(S, I, 1)) ' 取得第 I 個字元組的字元碼
	If C >= 0 And C < 128 Then ' 英文
	N = N + 1

relyky / CS_Singleton_ex1.cs

Last active October 17, 2018 12:30

C# Singleton Mechanism 實作練習

relyky / LoadExcel97ToDataTable.cs

Last active December 18, 2023 02:49

使用 NPOI 戴入 EXCEL 並轉存入 DataTable。

	// =============================================
	// 使用NPOI戴入EXCEL 並轉存入 DataTable
	// NPOI版本：NPOI binary 2.1.3.1
	// =============================================

	using NPOI.HSSF.UserModel;
	using NPOI.SS.UserModel;
	using System.IO;
	using System.Data;

relyky / SqlBulkCopy_upload_to_DB.cs

Last active April 26, 2017 03:47

使用 SqlBulkCopy 快速上傳 DataTable 到資料庫, upload datatable

	using System;
	using System.IO;
	using System.Data;
	using System.Data.Common;
	using System.Data.SqlClient;
	using System.Transactions;
	using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

	namespace IMPORT_EXCEL_TO_DATATABLE
	{

relyky / TransactionScope_example.cs

Created November 18, 2015 03:50

TransactionScope 範例，可依此模式設計多資料庫連線的 tow phase transaction。

	//# 建立TransactionScope
	using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
	{
	//# 建立DB連線，一個TransactionScope下可有多個DB連線。
	using (SqlConnection conn = new SqlConnection(mDB_ConnString))
	{
	conn.Open();

	//# 以下為商業邏輯
	// get current database datetime, 'yyyy-mm-dd HH:mm:ss'

Rely Ky relyky