数据库操作常用函数大全（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

}

}