資料庫操作常用函式大全（Sql Server 2000 oracle9i）

 

程式程式碼：
using System;
using System.Reflection;
using System.Data;
using System.Configuration;

using HZAllview.Console.Utility;
namespace HZAllview.Console.Data.Common
{
///
/// 資料庫操作的一些常用函式。
/// written by tmc
///
public abstract class AdoUtil
{

#region Factory

///
/// 根據配置檔案指定的資料驅動提供者，載入相應的程式集
///
/// DbTable物件
public static AdoUtil Create()
{
//獲得程式集路徑
string path = AdoConfig.AssemblyPath;
//類名
string className = path + ".DbUtil";

//載入程式集
Assembly assembly = Assembly.Load(path);

//建立物件的例項
object adoUtil = assembly.CreateInstance(className);
if( adoUtil is AdoUtil )
{
return adoUtil as AdoUtil;
}
else
{
throw new InvalidOperationException( className +" 沒有繼承抽象類 HZAllview.Console.Data.Common.AdoUtil " );
}

}

#endregion

#region Format Date

///
/// 格式化日期字串
///
/// param name="dateValue"日期字串/param
///
public abstract string FormatDateString(string dateValue);

///
/// 格式化日期的年份欄位
///
/// param name="dateField"日期欄位/param
///
public abstract string DatePartYear(string dateField);

///
/// 格式化日期的月份欄位
///
/// param name="dateField"日期欄位/param
///
public abstract string DatePartMonth(string dateField);

///
/// 格式化日期的日欄位
///
/// param name="dateField"日期欄位/param
///
public abstract string DateParDay(string dateField);

#endregion

#region Constraint
///
/// 獲得資料庫中所有的主鍵
///
///
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// PKConstraint　主鍵
/// KeyCol1　主鍵欄位
///
public abstract DataTable GetPKConstraint();

///
/// 獲得資料庫中所有的外來鍵
///
///
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// FKTable　外來鍵表
/// FKConstraint　外來鍵
/// KeyCol1　主鍵欄位
/// RefCol1　外來鍵欄位
///
public abstract DataTable GetFKConstraint();

///
/// Disable主鍵
///
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public abstract void DisablePKConstraint(string pkTableName,string pkConstraint);

///
/// Disable外來鍵
///
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public abstract void DisableFKConstraint(string fkTableName,string fkConstraint);

///
/// Enable主鍵
///
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public abstract void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField);

///
/// Enable外來鍵
///
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public abstract void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);

///
/// 刪除主鍵
///
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public abstract void DropPKConstraint(string pkTableName,string pkConstraint);

///
/// 刪除外來鍵
///
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public abstract void DropFKConstraint(string fkTableName,string fkConstraint);

///
/// 建立主鍵
///
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public abstract void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField);

///
/// 建立外來鍵
///
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public abstract void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);

#endregion

#region Identity
///
/// 允許將顯式值插入表的標識列中
///
/// param name="tableName"表名/param
public virtual void SetIdentityInsertOn(string tableName)
{
}

///
/// 不允許將顯式值插入表的標識列中
///
/// param name="tableName"表名/param
public virtual void SetIdentityInsertOff(string tableName)
{
}

///
/// 允許將顯式值插入表的標識列中
///
/// param name="tableName"表名/param
public virtual string GetIdentityInsertOn(string tableName)
{
return "";
}

///
/// 不允許將顯式值插入表的標識列中
///
/// param name="tableName"表名/param
public virtual string GetIdentityInsertOff(string tableName)
{
return "";
}

///
/// 獲得下一個遞增的ID。如果是ORACLE則返回下一個序列
///
/// param name="sName"遞增的欄位或序列/param
/// 下一個遞增的ID
public abstract object NextIncreaseID(string seqName);

#endregion

#region Table
///
/// 獲得資料庫中所有的表
///
/// 以DataTable返回表的名稱
public abstract DataTable GetTableNames();

///
/// 獲得資料中表的註釋
///
/// 以DataTable返回表的註釋
public abstract DataTable GetTableComments();

///
/// 獲得資料中表的註釋
///
/// 返回表的註釋
public abstract string GetTableComments(string tableName);

///
/// 格式化表名
///
/// param name="tableName"表名/param
///
public virtual string FormatTableName(string tableName)
{
return tableName;
}

#endregion

#region Field
///
/// 獲得指定表中的所有欄位名稱
///
/// param name="TableName"指定的表名/param
/// 以DataTable返回欄位的名稱
public abstract DataTable GetFieldNames(string tableName);

///
/// 獲得資料中欄位的註釋
///
/// 以DataTable返回欄位的註釋
public abstract DataTable GetFieldComments();

///
/// 獲得資料中欄位的註釋
///
/// 以DataTable返回欄位的註釋
public abstract DataTable GetFieldComments(string tableName);

///
/// 獲得資料中欄位的註釋
///
/// 返回欄位的註釋
public abstract string GetFieldComments(string tableName,string fieldName);

#endregion

#region View
///
/// 判斷是否存在對應的檢視
///
/// param name="viewName"檢視名/param
/// 存在返回true,不存在返回fasle
public abstract bool ExistView(string viewName);

///
/// 獲得資料庫中所有的使用者檢視
///
/// 以DataTable返回檢視的名稱
public abstract DataTable GetViewNames();

///
/// 獲得指定檢視的內容
///
/// param name="viewName"檢視名/param
///
public abstract string GetViewText(string viewName);

///
/// 刪除資料庫檢視
///
/// param name="viewName"檢視名/param
public abstract void DropView(string viewName);

///
/// 建立資料庫檢視
///
/// param name="viewName"檢視名/param
/// param name="viewText"檢視內容/param
public abstract void CreateView(string viewName,string viewText);

#endregion

#region Procedure
///
/// 獲得資料庫中所有的使用者儲存過程
///
/// 以DataTable返回儲存過程的名稱
public abstract DataTable GetProcedureNames();

///
/// 獲得指定儲存過程的內容
///
/// param name="procedureName"儲存過程名/param
///
public abstract string GetProcedureText(string procedureName);

///
/// 刪除資料庫儲存過程
///
/// param name="functionName"儲存過程名/param
public abstract void DropProcedure(string procedureName);

///
/// 建立資料庫儲存過程
///
/// param name="procedureName"儲存過程名/param
/// param name="procedureText"儲存過程內容/param
public abstract void CreateProcedure(string procedureName, string procedureText);

#endregion

#region Function
///
/// 獲得資料庫中所有的使用者函式
///
/// 以DataTable返回函式的名稱
public abstract DataTable GetFunctionNames();

///
/// 獲得指定函式的內容
///
/// param name="functionName"函式名/param
///
public abstract string GetFunctionText(string functionName);

///
/// 刪除資料庫函式
///
/// param name="functionName"函式名/param
public abstract void DropFunction(string functionName);

///
/// 建立資料庫函式
///
/// param name="functionName"函式名/param
/// param name="functionText"函式內容/param
public abstract void CreateFunction(string functionName, string functionText);

#endregion

#region Trigger
///
/// 返回資料庫中所有的觸發器
///
///
/// 返回的DataTable列
/// TriggerName 觸發器名稱
/// TableName 觸發器所在表名
/// /returns
public abstract DataTable GetTriggerName();

///
/// 刪除觸發器
///
/// param name="triggerName"觸發器名稱/param
public abstract void DropTrigger(string triggerName);

///
/// 建立觸發器
///
/// param name="triggerName"觸發器名稱/param
/// param name="triggerText"觸發器內容/param
public abstract void CreateTrigger(string triggerName,string triggerText);

///
/// Enable觸發器
///
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public abstract void EnableTrigger(string triggerName,string tableName);

///
/// Disable觸發器
///
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public abstract void DisableTrigger(string triggerName,string tableName);

#endregion

}
}

 

 

程式程式碼：
using System;
using System.Data;
using System.Data.OracleClient;
using HZAllview.Console.Data.Common;

namespace HZAllview.Console.Data.Oracle
{
/// summary
/// Oracle 資料庫操作的一些常用函式
/// written by tmc
/// /summary
public class DbUtil : AdoUtil
{

#region Construction
/// summary
/// 需要預設的建構函式，以便工廠能被建立
/// /summary
public DbUtil()
{

}
#endregion

#region Format Date
/// summary
/// 格式化日期字串
/// /summary
/// param name="dateValue"日期字串/param
/// returns/returns
public override string FormatDateString(string dateValue)
{
if(dateValue == null || dateValue == "")
return "''";

dateValue = dateValue.Replace("00:00:00","").Trim(); //如果不存在"小時:分:妙",則剔除"00:00:00"

//如果包含"小時:分:妙"
if(dateValue.IndexOf(":") != -1)
{
return "TO_DATE('"+dateValue+"','YYYY-MM-DD HH24:MI:SS')";

}
else
{
return "TO_DATE('"+dateValue+"','YYYY-MM-DD')";
}
}

/// summary
/// 格式化日期的年份欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DatePartYear(string dateField)
{
return " TO_CHAR("+dateField+", 'YYYY')";
}

/// summary
/// 格式化日期的月份欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DatePartMonth(string dateField)
{
return " TO_CHAR("+dateField+", 'MM')";
}

/// summary
/// 格式化日期的日欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DateParDay(string dateField)
{
return " TO_CHAR("+dateField+", 'DD')";
}

#endregion

#region Constraint

/// summary
/// 獲得資料庫中所有的主鍵
/// /summary
/// returns
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// PKConstraint　主鍵
/// KeyCol1　主鍵欄位
////returns
public override DataTable GetPKConstraint()
{
string selectCommandText = @"
select table_Name as PKTable,constraint_name as PKConstraint,'' as KeyCol1 from user_constraints where

Constraint_type='P'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料庫中所有的外來鍵
/// /summary
/// returns
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// FKTable　外來鍵表
/// FKConstraint　外來鍵
/// KeyCol1　主鍵欄位
/// RefCol1　外來鍵欄位
////returns
public override DataTable GetFKConstraint()
{
string selectCommandText = @"
select ''as PKTable,table_Name as FKTable,constraint_name as FKConstraint,'' as KeyCol1,'' as RefCol1 from

user_constraints where Constraint_type='R'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// Disable主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public override void DisablePKConstraint(string pkTableName,string pkConstraint)
{
string commandText = string.Format("alter table {0} disable constraint {1}",
pkTableName,pkConstraint);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// Disable外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public override void DisableFKConstraint(string fkTableName,string fkConstraint)
{
string commandText = string.Format("alter table {0} disable constraint {1}",
fkTableName,fkConstraint);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// Enable主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)
{
string commandText = string.Format("alter table {0} enable constraint {1}",
pkTableName,pkConstraint);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// Enable外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string

pkTableName,string pkField)
{
string commandText = string.Format("alter table {0} enable constraint {1}",
fkTableName,fkConstraint);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// 刪除主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public override void DropPKConstraint(string pkTableName,string pkConstraint)
{

}

/// summary
/// 刪除外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public override void DropFKConstraint(string fkTableName,string fkConstraint)
{

}

/// summary
/// 增加主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)
{

}

/// summary
/// 增加外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string

pkTableName,string pkField)
{

}
#endregion

#region Identity

/// summary
/// 獲得下一個遞增的ID。如果是ORACLE則返回下一個序列
/// /summary
/// param name="sName"遞增的欄位或序列/param
/// returns下一個遞增的ID/returns
public override object NextIncreaseID(string seqName)
{
string selectCommandText = string.Format("SELECT {0}.NEXTVAL FROM DUAL", seqName);
DbHelper helper = new DbHelper();
return helper.ExecuteScalar(selectCommandText);
}

#endregion

#region Table
/// summary
/// 獲得資料庫中所有的表
/// /summary
/// returns以DataTable返回表的名稱/returns
public override DataTable GetTableNames()
{
string selectCommandText = "SELECT TABLE_NAME FROM USER_TABLES";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中表的註釋
/// /summary
/// returns以DataTable返回表的註釋/returns
public override DataTable GetTableComments()
{
string selectCommandText = @"
select table_name,comments
from user_tab_comments
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中表的註釋
/// /summary
/// returns返回表的註釋/returns
public override string GetTableComments(string tableName)
{
string selectCommandText = @"
select comments
from user_tab_comments
where table_name='{0}'
";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));
return o == DBNull.Value || o == null ? "" : o.ToString();
}

#endregion

#region Field
/// summary
/// 獲得指定表中的所有欄位名稱
/// /summary
/// param name="TableName"指定的表名/param
/// returns以DataTable返回欄位的名稱/returns
public override DataTable GetFieldNames(string tableName)
{
string selectCommandText = string.Format("SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE

TABLE_NAME = '{0}'", tableName);
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns以DataTable返回欄位的註釋/returns
public override DataTable GetFieldComments()
{
string selectCommandText = @"
select table_name,column_name,
nvl(comments ,column_name) as comments
from user_col_comments
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns以DataTable返回欄位的註釋/returns
public override DataTable GetFieldComments(string tableName)
{
string selectCommandText = @"
select table_name,column_name,
nvl(comments ,column_name) as comments
from user_col_comments
where
table_name = '{0}'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(string.Format(selectCommandText,tableName));
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns返回欄位的註釋/returns
public override string GetFieldComments(string tableName,string fieldName)
{
string selectCommandText = @"
select table_name,column_name,
nvl(comments ,column_name) as comments
from user_col_comments
where
table_name = '{0}' and
column_name = '{1}'
";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));
return o == DBNull.Value || o == null ? "" : o.ToString();

}
#endregion

#region View
/// summary
/// 判斷是否存在對應的檢視
/// /summary
/// param name="viewName"檢視名/param
/// returns存在返回true,不存在返回fasle/returns
public override bool ExistView(string viewName)
{
string selectCommandText = string.Format("SELECT VIEW_NAME FROM USER_VIEWS WHERE VIEW_NAME = '{0}'",

viewName);
DbHelper helper = new DbHelper();
return helper.ExecuteScalar(selectCommandText) != null;
}

/// summary
/// 獲得資料庫中所有的使用者檢視
/// /summary
/// returns以DataTable返回檢視的名稱/returns
public override DataTable GetViewNames()
{
string selectCommandText = "SELECT VIEW_NAME as Name FROM USER_VIEWS ORDER BY VIEW_NAME";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定檢視的內容
/// /summary
/// param name="viewName"檢視名/param
/// returns/returns
public override string GetViewText(string viewName)
{
string selectCommandText = "SELECT Text FROM USER_VIEWS WHERE VIEW_NAME='"+viewName+"'";
DbHelper helper = new DbHelper();
return "create or replace view "+viewName+" as " + helper.ExecuteScalar(selectCommandText).ToString

();
}

/// summary
/// 刪除資料庫檢視
/// /summary
/// param name="viewName"檢視名/param
public override void DropView(string viewName)
{
}

/// summary
/// 建立資料庫檢視
/// /summary
/// param name="viewName"檢視名/param
/// param name="viewText"檢視內容/param
public override void CreateView(string viewName, string viewText)
{
if(viewText != null && viewText.Trim() != "")
{
DropView(viewName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(viewText);
}
}
#endregion

#region Procedure
/// summary
/// 獲得資料庫中所有的使用者儲存過程
/// /summary
/// returns以DataTable返回儲存過程的名稱/returns
public override DataTable GetProcedureNames()
{
string selectCommandText = "select object_name as Name from user_objects where

object_type='PROCEDURE' order by object_name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定儲存過程的內容
/// /summary
/// param name="procedureName"儲存過程名/param
/// returns/returns
public override string GetProcedureText(string procedureName)
{
string selectCommandText = string.Format(@"
select text from USER_SOURCE where type='PROCEDURE' and name=upper('{0}')",
procedureName);
DbHelper helper = new DbHelper();
DataTable table = helper.ExecuteDataTable(selectCommandText);
string ret = "create or replace ";
for(int i = 0; i table.Rows.Count; i++)
{
ret += table.Rows[i][0].ToString()+ " ";
}

return ret;
}

/// summary
/// 刪除資料庫儲存過程
/// /summary
/// param name="functionName"儲存過程名/param
public override void DropProcedure(string procedureName)
{

}

/// summary
/// 建立資料庫儲存過程
/// /summary
/// param name="procedureName"儲存過程名/param
/// param name="procedureText"儲存過程內容/param
public override void CreateProcedure(string procedureName, string procedureText)
{

if(procedureText != null && procedureText.Trim() != "")
{
DropProcedure(procedureName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(procedureText);
}
}

#endregion

#region Function
/// summary
/// 獲得資料庫中所有的使用者函式
/// /summary
/// returns以DataTable返回函式的名稱/returns
public override DataTable GetFunctionNames()
{
string selectCommandText = "select object_name as Name from user_objects where object_type='FUNCTION'

order by object_name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定函式的內容
/// /summary
/// param name="functionName"函式名/param
/// returns/returns
public override string GetFunctionText(string functionName)
{
string selectCommandText = string.Format(@"
select text from USER_SOURCE where type='FUNCTION' and name=upper('{0}')",
functionName);
DbHelper helper = new DbHelper();
DataTable table = helper.ExecuteDataTable(selectCommandText);
string ret = "create or replace ";
for(int i = 0; i table.Rows.Count; i++)
{
ret += table.Rows[i][0].ToString()+ " ";
}

return ret;
}

/// summary
/// 刪除資料庫函式
/// /summary
/// param name="functionName"函式名/param
public override void DropFunction(string functionName)
{
}

/// summary
/// 建立資料庫函式
/// /summary
/// param name="functionName"函式名/param
/// param name="functionText"函式內容/param
public override void CreateFunction(string functionName, string functionText)
{

if(functionText != null && functionText.Trim() != "")
{
DropFunction(functionName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(functionText);
}
}

#endregion

#region Trigger
/// summary
/// 返回資料庫中所有的觸發器
/// trigger_body　內容
/// /summary
/// returns
/// 返回的DataTable列
/// TriggerName 觸發器名稱
/// TableName 觸發器所在表名
/// /returns
public override DataTable GetTriggerName()
{
string selectCommandText = @"
select trigger_name as TriggerName,table_name as TableName from user_triggers
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 刪除觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
public override void DropTrigger(string triggerName)
{
}

/// summary
/// 建立觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="triggerText"觸發器內容/param
public override void CreateTrigger(string triggerName,string triggerText)
{

}

/// summary
/// Enable觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public override void EnableTrigger(string triggerName,string tableName)
{
string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// Disable觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public override void DisableTrigger(string triggerName,string tableName)
{
string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

#endregion

}
}

 

 

程式程式碼：
using System;
using System.Data;
using System.Configuration;
using HZAllview.Console.Data.Common;
using HZAllview.Console.Utility;

namespace HZAllview.Console.Data.SqlServer
{
/// summary
/// SQL Server 資料庫操作的一些常用函式。
/// written by tmc
/// /summary
public class DbUtil : AdoUtil
{

#region Construction
/// summary
/// 需要預設的建構函式，以便工廠能被建立
/// /summary
public DbUtil()
{
}
#endregion

#region Format Date
/// summary
/// 格式化日期字串
/// /summary
/// param name="dateValue"日期字串/param
/// returns/returns
public override string FormatDateString(string dateValue)
{
if(dateValue == null || dateValue == "")
return "''";
else
return "'"+dateValue+"'";
}

/// summary
/// 格式化日期的年份欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DatePartYear(string dateField)
{
return " DATEPART(YEAR,"+dateField+") ";
}

/// summary
/// 格式化日期的月份欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DatePartMonth(string dateField)
{
return " DATEPART(MONTH,"+dateField+") ";
}

/// summary
/// 格式化日期的日欄位
/// /summary
/// param name="dateField"日期欄位/param
/// returns/returns
public override string DateParDay(string dateField)
{
return " DATEPART(DAY,"+dateField+") ";
}

#endregion

#region Constraint
/// summary
/// 獲得資料庫中所有的主鍵
/// /summary
/// returns
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// PKConstraint　主鍵
/// KeyCol1　主鍵欄位
////returns
public override DataTable GetPKConstraint()
{
string selectCommandText = @"
select a.name as PKTable,b.name as PKConstraint,
index_col(a.name, c.indid, 1) as KeyCol1
from sysobjects a,sysobjects b,sysindexes c
where a.id=b.parent_obj and b.name=c.name and b.xtype='PK' order by a.name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料庫中所有的外來鍵
/// /summary
/// returns
/// 返回DataTable，列如下
/// PKTable 主鍵表
/// FKTable　外來鍵表
/// FKConstraint　外來鍵
/// KeyCol1　主鍵欄位
/// RefCol1　外來鍵欄位
////returns
public override DataTable GetFKConstraint()
{
string selectCommandText = @"
select PKT.name as PKTable,FKT.name as FKTable,
object_name(c.constid) as FKConstraint,
convert(nvarchar(132), col_name(c.rkeyid, c.rkey1)) as KeyCol1,
convert(nvarchar(132), col_name(c.fkeyid, c.fkey1)) as RefCol1
from sysobjects PKT,sysobjects FKT,sysreferences c
where
( c.rkeyid=object_id(PKT.name) or c.fkeyid = object_id(PKT.name))
and PKT.id = c.rkeyid and FKT.id = c.fkeyid";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// Disable主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public override void DisablePKConstraint(string pkTableName,string pkConstraint)
{
DropPKConstraint(pkTableName,pkConstraint);
}

/// summary
/// Disable外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public override void DisableFKConstraint(string fkTableName,string fkConstraint)
{
DropFKConstraint(fkTableName,fkConstraint);
}

/// summary
/// Enable主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)
{
CreatePKConstraint(pkTableName,pkConstraint,pkField);
}

/// summary
/// Enable外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)
{
CreateFKConstraint(fkTableName,fkConstraint,fkField,pkTableName,pkField);
}

/// summary
/// 刪除主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
public override void DropPKConstraint(string pkTableName,string pkConstraint)
{
string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
pkTableName,pkConstraint);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// 刪除外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
public override void DropFKConstraint(string fkTableName,string fkConstraint)
{
string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
fkTableName,fkConstraint);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// 建立主鍵
/// /summary
/// param name="pkTableName"主鍵表/param
/// param name="pkConstraint"主鍵/param
/// param name="pkField"主鍵欄位/param
public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)
{
string commandText = string.Format(@"
ALTER TABLE [{0}] ADD CONSTRAINT
{1} PRIMARY KEY CLUSTERED
(
{2}
) ON [PRIMARY]
",
pkTableName,
pkConstraint,
pkField);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// 建立外來鍵
/// /summary
/// param name="fkTableName"外來鍵表/param
/// param name="fkConstraint"外來鍵/param
/// param name="fkField"外來鍵欄位/param
/// param name="pkTableName"主鍵表/param
/// param name="pkField"主鍵欄位/param
public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)
{
string commandText = string.Format(@"
ALTER TABLE [{0}] ADD CONSTRAINT
{1} FOREIGN KEY
(
{2}
) REFERENCES [3}
(
{4}
)
",
fkTableName,
fkConstraint,
fkField,
pkTableName,
pkField);

DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);

}
#endregion

#region Identity
/// summary
/// 允許將顯式值插入表的標識列中
/// /summary
/// param name="tableName"表名/param
public override void SetIdentityInsertOn(string tableName)
{
AdoHelper helper = AdoHelper.Create();
string commandText = @"
if (IDENT_SEED('{0}') is not null)
set IDENTITY_INSERT [{0}] ON
";
helper.ExecuteNonQuery(string.Format(commandText,tableName));
}

/// summary
/// 不允許將顯式值插入表的標識列中
/// /summary
/// param name="tableName"表名/param
public override void SetIdentityInsertOff(string tableName)
{
AdoHelper helper = AdoHelper.Create();
string commandText = @"
if (IDENT_SEED('{0}') is not null)
set IDENTITY_INSERT [{0}] off
";
helper.ExecuteNonQuery(string.Format(commandText,tableName));
}

/// summary
/// 允許將顯式值插入表的標識列中
/// /summary
/// param name="tableName"表名/param
public override string GetIdentityInsertOn(string tableName)
{
string commandText = @"
if (IDENT_SEED('{0}') is not null)
set IDENTITY_INSERT [{0}] on
";
return string.Format(commandText,tableName);
}

/// summary
/// 不允許將顯式值插入表的標識列中
/// /summary
/// param name="tableName"表名/param
public override string GetIdentityInsertOff(string tableName)
{
string commandText = @"
if (IDENT_SEED('{0}') is not null)
set IDENTITY_INSERT [{0}] off
";
return string.Format(commandText,tableName);
}

/// summary
/// SQL Server 主鍵自動增長
/// /summary
/// param name="sName"遞增的欄位或序列/param
/// returns下一個遞增的ID/returns
public override object NextIncreaseID(string seqName)
{
return Guid.NewGuid().ToString();
}

#endregion

#region Table
/// summary
/// 獲得資料庫中所有的表
/// /summary
/// returns以DataTable返回表的名稱/returns
public override DataTable GetTableNames()
{
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsUserTable') = 1 order by o.name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中表的註釋
/// /summary
/// returns以DataTable返回表的註釋/returns
public override DataTable GetTableComments()
{
string selectCommandText = @"
select
s.name as table_name ,
p.value as comments
from
sysproperties p,
sysobjects s ,
sysusers u
where
p.id = s.id and
s.uid = u.uid and
u.name = 'dbo' and
p.type='3'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中表的註釋
/// /summary
/// returns返回表的註釋/returns
public override string GetTableComments(string tableName)
{
string selectCommandText = @"
select
p.value as comments
from
sysproperties p,
sysobjects s ,
sysusers u
where
p.id = s.id and
s.uid = u.uid and
u.name = 'dbo' and
p.type='3' and
s.name='{0}'
";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));
return o == DBNull.Value || o == null ? "" : o.ToString();
}

/// summary
/// 格式化表名
/// /summary
/// param name="tableName"表名/param
/// returns/returns
public override string FormatTableName(string tableName)
{
return "["+tableName+"]";
}

#endregion

#region Field
/// summary
/// 獲得指定表中的所有欄位名稱
/// /summary
/// param name="TableName"指定的表名/param
/// returns以DataTable返回欄位的名稱/returns
public override DataTable GetFieldNames(string tableName)
{
string selectCommandText = string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns以DataTable返回欄位的註釋/returns
public override DataTable GetFieldComments()
{
string selectCommandText = @"
select
s.name as table_name ,
c.name as column_name,
case
when p.value is null then c.name
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
else p.value
end as comments
from
sysproperties p,
syscolumns c ,
sysobjects s ,
sysusers u
where
p.id = c.id and
c.id = s.id and
p.smallid = c.colid and
s.uid = u.uid and
u.name = 'dbo'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns以DataTable返回欄位的註釋/returns
public override DataTable GetFieldComments(string tableName)
{
string selectCommandText = @"
select
s.name as table_name ,
c.name as column_name,
case
when p.value is null then c.name
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
else p.value
end as comments
from
sysproperties p,
syscolumns c ,
sysobjects s ,
sysusers u
where
p.id = c.id and
c.id = s.id and
p.smallid = c.colid and
s.uid = u.uid and
u.name = 'dbo' and
s.name = '{0}'
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(string.Format(selectCommandText,tableName));
}

/// summary
/// 獲得資料中欄位的註釋
/// /summary
/// returns返回欄位的註釋/returns
public override string GetFieldComments(string tableName,string fieldName)
{
string selectCommandText = @"
select
case
when p.value is null then c.name
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
else p.value
end as comments
from
sysproperties p,
syscolumns c ,
sysobjects s ,
sysusers u
where
p.id = c.id and
c.id = s.id and
p.smallid = c.colid and
s.uid = u.uid and
u.name = 'dbo' and
s.name = '{0}' and
c.name = '{1}'
";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));
return o == DBNull.Value || o == null ? "" : o.ToString();

}
#endregion

#region View
/// summary
/// 判斷是否存在對應的檢視
/// /summary
/// param name="viewName"檢視名/param
/// returns存在返回true,不存在返回fasle/returns
public override bool ExistView(string viewName)
{
string selectCommandText = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '{0}'", viewName);
DbHelper helper = new DbHelper();
return helper.ExecuteScalar(selectCommandText) != null;
}

/// summary
/// 獲得資料庫中所有的使用者檢視
/// /summary
/// returns以DataTable返回檢視的名稱/returns
public override DataTable GetViewNames()
{
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsView') = 1 order by o.name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定檢視的內容(如果已經加密，則返回空)
/// /summary
/// param name="viewName"檢視名/param
/// returns/returns
public override string GetViewText(string viewName)
{
string selectCommandText = "select c.text from dbo.syscomments c, dbo.sysobjects o where encrypted = 0 and o.id = c.id and c.id = object_id(N'[dbo].["+viewName+"]')";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(selectCommandText);
return o == DBNull.Value || o == null ? "" : o.ToString();
}

/// summary
/// 刪除資料庫檢視
/// /summary
/// param name="viewName"檢視名/param
public override void DropView(string viewName)
{
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+viewName+"]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].["+viewName+"]");
}

/// summary
/// 建立資料庫檢視
/// /summary
/// param name="viewName"檢視名/param
/// param name="viewText"檢視內容/param
public override void CreateView(string viewName, string viewText)
{
if(viewText != null && viewText.Trim() != "")
{
DropView(viewName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(viewText);
}
}
#endregion

#region Procedure
/// summary
/// 獲得資料庫中所有的使用者儲存過程
/// /summary
/// returns以DataTable返回儲存過程的名稱/returns
public override DataTable GetProcedureNames()
{
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsProcedure') = 1 order by o.name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定儲存過程的內容
/// /summary
/// param name="procedureName"儲存過程名/param
/// returns/returns
public override string GetProcedureText(string procedureName)
{
string selectCommandText = "select c.text from dbo.syscomments c, dbo.sysobjects o where encrypted = 0 and o.id = c.id and c.id = object_id(N'[dbo].["+procedureName+"]')";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(selectCommandText);
return o == DBNull.Value || o == null ? "" : o.ToString();
}

/// summary
/// 刪除資料庫儲存過程
/// /summary
/// param name="functionName"儲存過程名/param
public override void DropProcedure(string procedureName)
{
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+procedureName+"]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].["+procedureName+"]");
}

/// summary
/// 建立資料庫儲存過程
/// /summary
/// param name="procedureName"儲存過程名/param
/// param name="procedureText"儲存過程內容/param
public override void CreateProcedure(string procedureName, string procedureText)
{

if(procedureText != null && procedureText.Trim() != "")
{
DropProcedure(procedureName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(procedureText);
}
}

#endregion

#region Function
/// summary
/// 獲得資料庫中所有的使用者函式
/// /summary
/// returns以DataTable返回函式的名稱/returns
public override DataTable GetFunctionNames()
{
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsScalarFunction') = 1 order by o.name";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 獲得指定函式的內容
/// /summary
/// param name="functionName"函式名/param
/// returns/returns
public override string GetFunctionText(string functionName)
{
string selectCommandText = "select c.text from dbo.syscomments c, dbo.sysobjects o where encrypted = 0 and o.id = c.id and c.id = object_id(N'[dbo].["+functionName+"]')";
DbHelper helper = new DbHelper();
object o = helper.ExecuteScalar(selectCommandText);
return o == DBNull.Value || o == null ? "" : o.ToString();
}

/// summary
/// 刪除資料庫函式
/// /summary
/// param name="functionName"函式名/param
public override void DropFunction(string functionName)
{
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+functionName+"]') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1) drop function [dbo].["+functionName+"]");
}

/// summary
/// 建立資料庫函式
/// /summary
/// param name="functionName"函式名/param
/// param name="functionText"函式內容/param
public override void CreateFunction(string functionName, string functionText)
{

if(functionText != null && functionText.Trim() != "")
{
DropFunction(functionName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(functionText);
}
}
#endregion

#region Trigger
/// summary
/// 返回資料庫中所有的觸發器
/// /summary
/// returns
/// 返回的DataTable列
/// TriggerName 觸發器名稱
/// TableName 觸發器所在表名
/// /returns
public override DataTable GetTriggerName()
{
string selectCommandText = @"
select object_name(a.parent_obj) as TableName,a.name as TriggerName
from sysobjects a,sysobjects b
where a.parent_obj=b.id and
OBJECTPROPERTY(a.id, N'IsTrigger') = 1
order by object_name(a.parent_obj)
";
DbHelper helper = new DbHelper();
return helper.ExecuteDataTable(selectCommandText);
}

/// summary
/// 刪除觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
public override void DropTrigger(string triggerName)
{
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+triggerName+"]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger [dbo].["+triggerName+"]");
}

/// summary
/// 建立觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="triggerText"觸發器內容/param
public override void CreateTrigger(string triggerName,string triggerText)
{
if(triggerText != null && triggerText.Trim() != "")
{
DbHelper helper = new DbHelper();
DropTrigger(triggerName);
helper.ExecuteNonQuery(triggerText);
}
}

/// summary
/// Enable觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public override void EnableTrigger(string triggerName,string tableName)
{
string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

/// summary
/// Disable觸發器
/// /summary
/// param name="triggerName"觸發器名稱/param
/// param name="tableName"觸發器所在表名/param
public override void DisableTrigger(string triggerName,string tableName)
{
string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName);
DbHelper helper = new DbHelper();
helper.ExecuteNonQuery(commandText);
}

#endregion

}

}