1
using System;
2using System.Reflection;
3using System.Data;
4using System.Configuration;
5
6using HZAllview.Console.Utility;
7namespace HZAllview.Console.Data.Common
8
{
9
///
10
/// 数据库操作的一些常用函数。
11/// written by tmc
12
///
13public abstract class AdoUtil
14{
15
16#region Factory
17
18///
19/// 根据配置文件指定的数据驱动提供者，加载相应的程序集
20///
21/// DbTable对象
22public static AdoUtil Create()
23{
24//获得程序集路径
25string path = AdoConfig.AssemblyPath;
26//类名
27string className = path + ".DbUtil";
28
29//加载程序集
30Assembly assembly = Assembly.Load(path);
31
32//创建对象的实例
33object adoUtil = assembly.CreateInstance(className);
34if( adoUtil is AdoUtil )
35{
36return adoUtil as AdoUtil;
37}
38else
39{
40throw new InvalidOperationException( className +" 没有继承抽象类 HZAllview.Console.Data.Common.AdoUtil " );
41}
42
43}
44
45#endregion
46
47#region Format Date
48
49///
50/// 格式化日期字符串
51///
52/// param name="dateValue"日期字符串/param
53///
54public abstract string FormatDateString(string dateValue);
55
56///
57/// 格式化日期的年份字段
58///
59/// param name="dateField"日期字段/param
60///
61public abstract string DatePartYear(string dateField);
62
63///
64/// 格式化日期的月份字段
65///
66/// param name="dateField"日期字段/param
67///
68public abstract string DatePartMonth(string dateField);
69
70///
71/// 格式化日期的日字段
72///
73/// param name="dateField"日期字段/param
74///
75public abstract string DateParDay(string dateField);
76
77#endregion
78
79#region Constraint
80///
81/// 获得数据库中所有的主键
82///
83///
84/// 返回DataTable，列如下
85/// PKTable 主键表
86/// PKConstraint 主键
87/// KeyCol1 主键字段
88///
89public abstract DataTable GetPKConstraint();
90
91///
92/// 获得数据库中所有的外键
93///
94///
95/// 返回DataTable，列如下
96/// PKTable 主键表
97/// FKTable 外键表
98/// FKConstraint 外键
99/// KeyCol1 主键字段
100/// RefCol1 外键字段
101///
102public abstract DataTable GetFKConstraint();
103
104///
105/// Disable主键
106///
107/// param name="pkTableName"主键表/param
108/// param name="pkConstraint"主键/param
109public abstract void DisablePKConstraint(string pkTableName,string pkConstraint);
110
111///
112/// Disable外键
113///
114/// param name="fkTableName"外键表/param
115/// param name="fkConstraint"外键/param
116public abstract void DisableFKConstraint(string fkTableName,string fkConstraint);
117
118///
119/// Enable主键
120///
121/// param name="pkTableName"主键表/param
122/// param name="pkConstraint"主键/param
123/// param name="pkField"主键字段/param
124public abstract void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField);
125
126///
127/// Enable外键
128///
129/// param name="fkTableName"外键表/param
130/// param name="fkConstraint"外键/param
131/// param name="fkField"外键字段/param
132/// param name="pkTableName"主键表/param
133/// param name="pkField"主键字段/param
134public abstract void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);
135
136///
137/// 删除主键
138///
139/// param name="pkTableName"主键表/param
140/// param name="pkConstraint"主键/param
141public abstract void DropPKConstraint(string pkTableName,string pkConstraint);
142
143///
144/// 删除外键
145///
146/// param name="fkTableName"外键表/param
147/// param name="fkConstraint"外键/param
148public abstract void DropFKConstraint(string fkTableName,string fkConstraint);
149
150///
151/// 创建主键
152///
153/// param name="pkTableName"主键表/param
154/// param name="pkConstraint"主键/param
155/// param name="pkField"主键字段/param
156public abstract void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField);
157
158///
159/// 创建外键
160///
161/// param name="fkTableName"外键表/param
162/// param name="fkConstraint"外键/param
163/// param name="fkField"外键字段/param
164/// param name="pkTableName"主键表/param
165/// param name="pkField"主键字段/param
166public abstract void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);
167
168#endregion
169
170#region Identity
171///
172/// 允许将显式值插入表的标识列中
173///
174/// param name="tableName"表名/param
175public virtual void SetIdentityInsertOn(string tableName)
176{
177}
178
179///
180/// 不允许将显式值插入表的标识列中
181///
182/// param name="tableName"表名/param
183public virtual void SetIdentityInsertOff(string tableName)
184{
185}
186
187///
188/// 允许将显式值插入表的标识列中
189///
190/// param name="tableName"表名/param
191public virtual string GetIdentityInsertOn(string tableName)
192{
193return "";
194}
195
196///
197/// 不允许将显式值插入表的标识列中
198///
199/// param name="tableName"表名/param
200public virtual string GetIdentityInsertOff(string tableName)
201{
202return "";
203}
204
205///
206/// 获得下一个递增的ID。如果是ORACLE则返回下一个序列
207///
208/// param name="sName"递增的字段或序列/param
209/// 下一个递增的ID
210public abstract object NextIncreaseID(string seqName);
211
212#endregion
213
214#region Table
215///
216/// 获得数据库中所有的表
217///
218/// 以DataTable返回表的名称
219public abstract DataTable GetTableNames();
220
221///
222/// 获得数据中表的注释
223///
224/// 以DataTable返回表的注释
225public abstract DataTable GetTableComments();
226
227///
228/// 获得数据中表的注释
229///
230/// 返回表的注释
231public abstract string GetTableComments(string tableName);
232
233///
234/// 格式化表名
235///
236/// param name="tableName"表名/param
237///
238public virtual string FormatTableName(string tableName)
239{
240return tableName;
241}
242
243#endregion
244
245#region Field
246///
247/// 获得指定表中的所有字段名称
248///
249/// param name="TableName"指定的表名/param
250/// 以DataTable返回字段的名称
251public abstract DataTable GetFieldNames(string tableName);
252
253///
254/// 获得数据中字段的注释
255///
256/// 以DataTable返回字段的注释
257public abstract DataTable GetFieldComments();
258
259///
260/// 获得数据中字段的注释
261///
262/// 以DataTable返回字段的注释
263public abstract DataTable GetFieldComments(string tableName);
264
265///
266/// 获得数据中字段的注释
267///
268/// 返回字段的注释
269public abstract string GetFieldComments(string tableName,string fieldName);
270
271#endregion
272
273#region View
274///
275/// 判断是否存在对应的视图
276///
277/// param name="viewName"视图名/param
278/// 存在返回true,不存在返回fasle
279public abstract bool ExistView(string viewName);
280
281///
282/// 获得数据库中所有的用户视图
283///
284/// 以DataTable返回视图的名称
285public abstract DataTable GetViewNames();
286
287///
288/// 获得指定视图的内容
289///
290/// param name="viewName"视图名/param
291///
292public abstract string GetViewText(string viewName);
293
294///
295/// 删除数据库视图
296///
297/// param name="viewName"视图名/param
298public abstract void DropView(string viewName);
299
300///
301/// 创建数据库视图
302///
303/// param name="viewName"视图名/param
304/// param name="viewText"视图内容/param
305public abstract void CreateView(string viewName,string viewText);
306
307#endregion
308
309#region Procedure
310///
311/// 获得数据库中所有的用户存储过程
312///
313/// 以DataTable返回存储过程的名称
314public abstract DataTable GetProcedureNames();
315
316///
317/// 获得指定存储过程的内容
318///
319/// param name="procedureName"存储过程名/param
320///
321public abstract string GetProcedureText(string procedureName);
322
323///
324/// 删除数据库存储过程
325///
326/// param name="functionName"存储过程名/param
327public abstract void DropProcedure(string procedureName);
328
329///
330/// 创建数据库存储过程
331///
332/// param name="procedureName"存储过程名/param
333/// param name="procedureText"存储过程内容/param
334public abstract void CreateProcedure(string procedureName, string procedureText);
335
336#endregion
337
338#region Function
339///
340/// 获得数据库中所有的用户函数
341///
342/// 以DataTable返回函数的名称
343public abstract DataTable GetFunctionNames();
344
345///
346/// 获得指定函数的内容
347///
348/// param name="functionName"函数名/param
349///
350public abstract string GetFunctionText(string functionName);
351
352///
353/// 删除数据库函数
354///
355/// param name="functionName"函数名/param
356public abstract void DropFunction(string functionName);
357
358///
359/// 创建数据库函数
360///
361/// param name="functionName"函数名/param
362/// param name="functionText"函数内容/param
363public abstract void CreateFunction(string functionName, string functionText);
364
365#endregion
366
367#region Trigger
368///
369/// 返回数据库中所有的触发器
370///
371///
372/// 返回的DataTable列
373/// TriggerName 触发器名称
374/// TableName 触发器所在表名
375/// /returns
376public abstract DataTable GetTriggerName();
377
378///
379/// 删除触发器
380///
381/// param name="triggerName"触发器名称/param
382public abstract void DropTrigger(string triggerName);
383
384///
385/// 创建触发器
386///
387/// param name="triggerName"触发器名称/param
388/// param name="triggerText"触发器内容/param
389public abstract void CreateTrigger(string triggerName,string triggerText);
390
391///
392/// Enable触发器
393///
394/// param name="triggerName"触发器名称/param
395/// param name="tableName"触发器所在表名/param
396public abstract void EnableTrigger(string triggerName,string tableName);
397
398///
399/// Disable触发器
400///
401/// param name="triggerName"触发器名称/param
402/// param name="tableName"触发器所在表名/param
403public abstract void DisableTrigger(string triggerName,string tableName);
404
405#endregion
406
407}
408
}
409
410
411
412
413
414程序代码：
415using System;
416using System.Data;
417using System.Data.OracleClient;
418using HZAllview.Console.Data.Common;
419
420namespace HZAllview.Console.Data.Oracle
421{
422/// summary
423/// Oracle 数据库操作的一些常用函数
424/// written by tmc
425/// /summary
426public class DbUtil : AdoUtil
427{
428
429#region Construction
430/// summary
431/// 需要预设的构造函数，以便工厂能被创建
432/// /summary
433public DbUtil()
434{
435
436}
437#endregion
438
439#region Format Date
440/// summary
441/// 格式化日期字符串
442/// /summary
443/// param name="dateValue"日期字符串/param
444/// returns/returns
445public override string FormatDateString(string dateValue)
446{
447if(dateValue == null || dateValue == "")
448return "''";
449
450dateValue = dateValue.Replace("00:00:00","").Trim(); //如果不存在"小时:分:妙",则剔除"00:00:00"
451
452//如果包含"小时:分:妙"
453if(dateValue.IndexOf(":") != -1)
454{
455return "TO_DATE('"+dateValue+"','YYYY-MM-DD HH24:MI:SS')";
456
457}
458else
459{
460return "TO_DATE('"+dateValue+"','YYYY-MM-DD')";
461}
462}
463
464/// summary
465/// 格式化日期的年份字段
466/// /summary
467/// param name="dateField"日期字段/param
468/// returns/returns
469public override string DatePartYear(string dateField)
470{
471return " TO_CHAR("+dateField+", 'YYYY')";
472}
473
474/// summary
475/// 格式化日期的月份字段
476/// /summary
477/// param name="dateField"日期字段/param
478/// returns/returns
479public override string DatePartMonth(string dateField)
480{
481return " TO_CHAR("+dateField+", 'MM')";
482}
483
484/// summary
485/// 格式化日期的日字段
486/// /summary
487/// param name="dateField"日期字段/param
488/// returns/returns
489public override string DateParDay(string dateField)
490{
491return " TO_CHAR("+dateField+", 'DD')";
492}
493
494#endregion
495
496#region Constraint
497
498/// summary
499/// 获得数据库中所有的主键
500/// /summary
501/// returns
502/// 返回DataTable，列如下
503/// PKTable 主键表
504/// PKConstraint 主键
505/// KeyCol1 主键字段
506////returns
507public override DataTable GetPKConstraint()
508{
509string selectCommandText = @"
510select table_Name as PKTable,constraint_name as PKConstraint,'' as KeyCol1 from user_constraints where
511
512Constraint_type='P'
513";
514DbHelper helper = new DbHelper();
515return helper.ExecuteDataTable(selectCommandText);
516}
517
518/// summary
519/// 获得数据库中所有的外键
520/// /summary
521/// returns
522/// 返回DataTable，列如下
523/// PKTable 主键表
524/// FKTable 外键表
525/// FKConstraint 外键
526/// KeyCol1 主键字段
527/// RefCol1 外键字段
528////returns
529public override DataTable GetFKConstraint()
530{
531string selectCommandText = @"
532select ''as PKTable,table_Name as FKTable,constraint_name as FKConstraint,'' as KeyCol1,'' as RefCol1 from
533
534user_constraints where Constraint_type='R'
535";
536DbHelper helper = new DbHelper();
537return helper.ExecuteDataTable(selectCommandText);
538}
539
540/// summary
541/// Disable主键
542/// /summary
543/// param name="pkTableName"主键表/param
544/// param name="pkConstraint"主键/param
545public override void DisablePKConstraint(string pkTableName,string pkConstraint)
546{
547string commandText = string.Format("alter table {0} disable constraint {1}",
548pkTableName,pkConstraint);
549
550
551DbHelper helper = new DbHelper();
552helper.ExecuteNonQuery(commandText);
553}
554
555/// summary
556/// Disable外键
557/// /summary
558/// param name="fkTableName"外键表/param
559/// param name="fkConstraint"外键/param
560public override void DisableFKConstraint(string fkTableName,string fkConstraint)
561{
562string commandText = string.Format("alter table {0} disable constraint {1}",
563fkTableName,fkConstraint);
564
565
566DbHelper helper = new DbHelper();
567helper.ExecuteNonQuery(commandText);
568}
569
570/// summary
571/// Enable主键
572/// /summary
573/// param name="pkTableName"主键表/param
574/// param name="pkConstraint"主键/param
575/// param name="pkField"主键字段/param
576public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)
577{
578string commandText = string.Format("alter table {0} enable constraint {1}",
579pkTableName,pkConstraint);
580
581
582DbHelper helper = new DbHelper();
583helper.ExecuteNonQuery(commandText);
584}
585
586/// summary
587/// Enable外键
588/// /summary
589/// param name="fkTableName"外键表/param
590/// param name="fkConstraint"外键/param
591/// param name="fkField"外键字段/param
592/// param name="pkTableName"主键表/param
593/// param name="pkField"主键字段/param
594public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string
595
596pkTableName,string pkField)
597{
598string commandText = string.Format("alter table {0} enable constraint {1}",
599fkTableName,fkConstraint);
600
601
602DbHelper helper = new DbHelper();
603helper.ExecuteNonQuery(commandText);
604}
605
606/// summary
607/// 删除主键
608/// /summary
609/// param name="pkTableName"主键表/param
610/// param name="pkConstraint"主键/param
611public override void DropPKConstraint(string pkTableName,string pkConstraint)
612{
613
614}
615
616/// summary
617/// 删除外键
618/// /summary
619/// param name="fkTableName"外键表/param
620/// param name="fkConstraint"外键/param
621public override void DropFKConstraint(string fkTableName,string fkConstraint)
622{
623
624}
625
626/// summary
627/// 增加主键
628/// /summary
629/// param name="pkTableName"主键表/param
630/// param name="pkConstraint"主键/param
631/// param name="pkField"主键字段/param
632public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)
633{
634
635}
636
637/// summary
638/// 增加外键
639/// /summary
640/// param name="fkTableName"外键表/param
641/// param name="fkConstraint"外键/param
642/// param name="fkField"外键字段/param
643/// param name="pkTableName"主键表/param
644/// param name="pkField"主键字段/param
645public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string
646
647pkTableName,string pkField)
648{
649
650}
651#endregion
652
653#region Identity
654
655/// summary
656/// 获得下一个递增的ID。如果是ORACLE则返回下一个序列
657/// /summary
658/// param name="sName"递增的字段或序列/param
659/// returns下一个递增的ID/returns
660public override object NextIncreaseID(string seqName)
661{
662string selectCommandText = string.Format("SELECT {0}.NEXTVAL FROM DUAL", seqName);
663DbHelper helper = new DbHelper();
664return helper.ExecuteScalar(selectCommandText);
665}
666
667#endregion
668
669#region Table
670/// summary
671/// 获得数据库中所有的表
672/// /summary
673/// returns以DataTable返回表的名称/returns
674public override DataTable GetTableNames()
675{
676string selectCommandText = "SELECT TABLE_NAME FROM USER_TABLES";
677DbHelper helper = new DbHelper();
678return helper.ExecuteDataTable(selectCommandText);
679}
680
681/// summary
682/// 获得数据中表的注释
683/// /summary
684/// returns以DataTable返回表的注释/returns
685public override DataTable GetTableComments()
686{
687string selectCommandText = @"
688select table_name,comments
689from user_tab_comments
690";
691DbHelper helper = new DbHelper();
692return helper.ExecuteDataTable(selectCommandText);
693}
694
695/// summary
696/// 获得数据中表的注释
697/// /summary
698/// returns返回表的注释/returns
699public override string GetTableComments(string tableName)
700{
701string selectCommandText = @"
702select comments
703from user_tab_comments
704where table_name='{0}'
705";
706DbHelper helper = new DbHelper();
707object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));
708return o == DBNull.Value || o == null ? "" : o.ToString();
709}
710
711#endregion
712
713#region Field
714/// summary
715/// 获得指定表中的所有字段名称
716/// /summary
717/// param name="TableName"指定的表名/param
718/// returns以DataTable返回字段的名称/returns
719public override DataTable GetFieldNames(string tableName)
720{
721string selectCommandText = string.Format("SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE
722
723TABLE_NAME = '{0}'", tableName);
724DbHelper helper = new DbHelper();
725return helper.ExecuteDataTable(selectCommandText);
726}
727
728/// summary
729/// 获得数据中字段的注释
730/// /summary
731/// returns以DataTable返回字段的注释/returns
732public override DataTable GetFieldComments()
733{
734string selectCommandText = @"
735select table_name,column_name,
736nvl(comments ,column_name) as comments
737from user_col_comments
738";
739DbHelper helper = new DbHelper();
740return helper.ExecuteDataTable(selectCommandText);
741}
742
743/// summary
744/// 获得数据中字段的注释
745/// /summary
746/// returns以DataTable返回字段的注释/returns
747public override DataTable GetFieldComments(string tableName)
748{
749string selectCommandText = @"
750select table_name,column_name,
751nvl(comments ,column_name) as comments
752from user_col_comments
753where
754table_name = '{0}'
755";
756DbHelper helper = new DbHelper();
757return helper.ExecuteDataTable(string.Format(selectCommandText,tableName));
758}
759
760/// summary
761/// 获得数据中字段的注释
762/// /summary
763/// returns返回字段的注释/returns
764public override string GetFieldComments(string tableName,string fieldName)
765{
766string selectCommandText = @"
767select table_name,column_name,
768nvl(comments ,column_name) as comments
769from user_col_comments
770where
771table_name = '{0}' and
772column_name = '{1}'
773";
774DbHelper helper = new DbHelper();
775object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));
776return o == DBNull.Value || o == null ? "" : o.ToString();
777
778}
779#endregion
780
781#region View
782/// summary
783/// 判断是否存在对应的视图
784/// /summary
785/// param name="viewName"视图名/param
786/// returns存在返回true,不存在返回fasle/returns
787public override bool ExistView(string viewName)
788{
789string selectCommandText = string.Format("SELECT VIEW_NAME FROM USER_VIEWS WHERE VIEW_NAME = '{0}'",
790
791viewName);
792DbHelper helper = new DbHelper();
793return helper.ExecuteScalar(selectCommandText) != null;
794}
795
796/// summary
797/// 获得数据库中所有的用户视图
798/// /summary
799/// returns以DataTable返回视图的名称/returns
800public override DataTable GetViewNames()
801{
802string selectCommandText = "SELECT VIEW_NAME as Name FROM USER_VIEWS ORDER BY VIEW_NAME";
803DbHelper helper = new DbHelper();
804return helper.ExecuteDataTable(selectCommandText);
805}
806
807/// summary
808/// 获得指定视图的内容
809/// /summary
810/// param name="viewName"视图名/param
811/// returns/returns
812public override string GetViewText(string viewName)
813{
814string selectCommandText = "SELECT Text FROM USER_VIEWS WHERE VIEW_NAME='"+viewName+"'";
815DbHelper helper = new DbHelper();
816return "create or replace view "+viewName+" as " + helper.ExecuteScalar(selectCommandText).ToString
817
818();
819}
820
821/// summary
822/// 删除数据库视图
823/// /summary
824/// param name="viewName"视图名/param
825public override void DropView(string viewName)
826{
827}
828
829/// summary
830/// 创建数据库视图
831/// /summary
832/// param name="viewName"视图名/param
833/// param name="viewText"视图内容/param
834public override void CreateView(string viewName, string viewText)
835{
836if(viewText != null && viewText.Trim() != "")
837{
838DropView(viewName);
839DbHelper helper = new DbHelper();
840helper.ExecuteNonQuery(viewText);
841}
842}
843#endregion
844
845#region Procedure
846/// summary
847/// 获得数据库中所有的用户存储过程
848/// /summary
849/// returns以DataTable返回存储过程的名称/returns
850public override DataTable GetProcedureNames()
851{
852string selectCommandText = "select object_name as Name from user_objects where
853
854object_type='PROCEDURE' order by object_name";
855DbHelper helper = new DbHelper();
856return helper.ExecuteDataTable(selectCommandText);
857}
858
859/// summary
860/// 获得指定存储过程的内容
861/// /summary
862/// param name="procedureName"存储过程名/param
863/// returns/returns
864public override string GetProcedureText(string procedureName)
865{
866string selectCommandText = string.Format(@"
867select text from USER_SOURCE where type='PROCEDURE' and name=upper('{0}')",
868procedureName);
869DbHelper helper = new DbHelper();
870DataTable table = helper.ExecuteDataTable(selectCommandText);
871string ret = "create or replace ";
872for(int i = 0; i table.Rows.Count; i++)
873{
874ret += table.Rows[i][0].ToString()+ " ";
875}
876
877return ret;
878}
879
880/// summary
881/// 删除数据库存储过程
882/// /summary
883/// param name="functionName"存储过程名/param
884public override void DropProcedure(string procedureName)
885{
886
887}
888
889
890/// summary
891/// 创建数据库存储过程
892/// /summary
893/// param name="procedureName"存储过程名/param
894/// param name="procedureText"存储过程内容/param
895public override void CreateProcedure(string procedureName, string procedureText)
896{
897
898if(procedureText != null && procedureText.Trim() != "")
899{
900DropProcedure(procedureName);
901DbHelper helper = new DbHelper();
902helper.ExecuteNonQuery(procedureText);
903}
904}
905
906#endregion
907
908#region Function
909/// summary
910/// 获得数据库中所有的用户函数
911/// /summary
912/// returns以DataTable返回函数的名称/returns
913public override DataTable GetFunctionNames()
914{
915string selectCommandText = "select object_name as Name from user_objects where object_type='FUNCTION'
916
917order by object_name";
918DbHelper helper = new DbHelper();
919return helper.ExecuteDataTable(selectCommandText);
920}
921
922/// summary
923/// 获得指定函数的内容
924/// /summary
925/// param name="functionName"函数名/param
926/// returns/returns
927public override string GetFunctionText(string functionName)
928{
929string selectCommandText = string.Format(@"
930select text from USER_SOURCE where type='FUNCTION' and name=upper('{0}')",
931functionName);
932DbHelper helper = new DbHelper();
933DataTable table = helper.ExecuteDataTable(selectCommandText);
934string ret = "create or replace ";
935for(int i = 0; i table.Rows.Count; i++)
936{
937ret += table.Rows[i][0].ToString()+ " ";
938}
939
940return ret;
941}
942
943/// summary
944/// 删除数据库函数
945/// /summary
946/// param name="functionName"函数名/param
947public override void DropFunction(string functionName)
948{
949}
950
951/// summary
952/// 创建数据库函数
953/// /summary
954/// param name="functionName"函数名/param
955/// param name="functionText"函数内容/param
956public override void CreateFunction(string functionName, string functionText)
957{
958
959if(functionText != null && functionText.Trim() != "")
960{
961DropFunction(functionName);
962DbHelper helper = new DbHelper();
963helper.ExecuteNonQuery(functionText);
964}
965}
966
967#endregion
968
969#region Trigger
970/// summary
971/// 返回数据库中所有的触发器
972/// trigger_body 内容
973/// /summary
974/// returns
975/// 返回的DataTable列
976/// TriggerName 触发器名称
977/// TableName 触发器所在表名
978/// /returns
979public override DataTable GetTriggerName()
980{
981string selectCommandText = @"
982select trigger_name as TriggerName,table_name as TableName from user_triggers
983";
984DbHelper helper = new DbHelper();
985return helper.ExecuteDataTable(selectCommandText);
986}
987
988/// summary
989/// 删除触发器
990/// /summary
991/// param name="triggerName"触发器名称/param
992public override void DropTrigger(string triggerName)
993{
994}
995
996/// summary
997/// 创建触发器
998/// /summary
999/// param name="triggerName"触发器名称/param
1000/// param name="triggerText"触发器内容/param
1001public override void CreateTrigger(string triggerName,string triggerText)
1002{
1003
1004}
1005
1006/// summary
1007/// Enable触发器
1008/// /summary
1009/// param name="triggerName"触发器名称/param
1010/// param name="tableName"触发器所在表名/param
1011public override void EnableTrigger(string triggerName,string tableName)
1012{
1013string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName);
1014
1015
1016DbHelper helper = new DbHelper();
1017helper.ExecuteNonQuery(commandText);
1018}
1019
1020/// summary
1021/// Disable触发器
1022/// /summary
1023/// param name="triggerName"触发器名称/param
1024/// param name="tableName"触发器所在表名/param
1025public override void DisableTrigger(string triggerName,string tableName)
1026{
1027string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName);
1028
1029
1030DbHelper helper = new DbHelper();
1031helper.ExecuteNonQuery(commandText);
1032}
1033
1034#endregion
1035
1036}
1037}
1038
1039
1040
1041
1042
1043程序代码：
1044using System;
1045using System.Data;
1046using System.Configuration;
1047using HZAllview.Console.Data.Common;
1048using HZAllview.Console.Utility;
1049
1050namespace HZAllview.Console.Data.SqlServer
1051{
1052/// summary
1053/// SQL Server 数据库操作的一些常用函数。
1054/// written by tmc
1055/// /summary
1056public class DbUtil : AdoUtil
1057{
1058
1059#region Construction
1060/// summary
1061/// 需要预设的构造函数，以便工厂能被创建
1062/// /summary
1063public DbUtil()
1064{
1065}
1066#endregion
1067
1068#region Format Date
1069/// summary
1070/// 格式化日期字符串
1071/// /summary
1072/// param name="dateValue"日期字符串/param
1073/// returns/returns
1074public override string FormatDateString(string dateValue)
1075{
1076if(dateValue == null || dateValue == "")
1077return "''";
1078else
1079return "'"+dateValue+"'";
1080}
1081
1082/// summary
1083/// 格式化日期的年份字段
1084/// /summary
1085/// param name="dateField"日期字段/param
1086/// returns/returns
1087public override string DatePartYear(string dateField)
1088{
1089return " DATEPART(YEAR,"+dateField+") ";
1090}
1091
1092/// summary
1093/// 格式化日期的月份字段
1094/// /summary
1095/// param name="dateField"日期字段/param
1096/// returns/returns
1097public override string DatePartMonth(string dateField)
1098{
1099return " DATEPART(MONTH,"+dateField+") ";
1100}
1101
1102/// summary
1103/// 格式化日期的日字段
1104/// /summary
1105/// param name="dateField"日期字段/param
1106/// returns/returns
1107public override string DateParDay(string dateField)
1108{
1109return " DATEPART(DAY,"+dateField+") ";
1110}
1111
1112#endregion
1113
1114#region Constraint
1115/// summary
1116/// 获得数据库中所有的主键
1117/// /summary
1118/// returns
1119/// 返回DataTable，列如下
1120/// PKTable 主键表
1121/// PKConstraint 主键
1122/// KeyCol1 主键字段
1123////returns
1124public override DataTable GetPKConstraint()
1125{
1126string selectCommandText = @"
1127select a.name as PKTable,b.name as PKConstraint,
1128index_col(a.name, c.indid, 1) as KeyCol1
1129from sysobjects a,sysobjects b,sysindexes c
1130where a.id=b.parent_obj and b.name=c.name and b.xtype='PK' order by a.name";
1131DbHelper helper = new DbHelper();
1132return helper.ExecuteDataTable(selectCommandText);
1133}
1134
1135/// summary
1136/// 获得数据库中所有的外键
1137/// /summary
1138/// returns
1139/// 返回DataTable，列如下
1140/// PKTable 主键表
1141/// FKTable 外键表
1142/// FKConstraint 外键
1143/// KeyCol1 主键字段
1144/// RefCol1 外键字段
1145////returns
1146public override DataTable GetFKConstraint()
1147{
1148string selectCommandText = @"
1149select PKT.name as PKTable,FKT.name as FKTable,
1150object_name(c.constid) as FKConstraint,
1151convert(nvarchar(132), col_name(c.rkeyid, c.rkey1)) as KeyCol1,
1152convert(nvarchar(132), col_name(c.fkeyid, c.fkey1)) as RefCol1
1153from sysobjects PKT,sysobjects FKT,sysreferences c
1154where
1155( c.rkeyid=object_id(PKT.name) or c.fkeyid = object_id(PKT.name))
1156and PKT.id = c.rkeyid and FKT.id = c.fkeyid";
1157DbHelper helper = new DbHelper();
1158return helper.ExecuteDataTable(selectCommandText);
1159}
1160
1161/// summary
1162/// Disable主键
1163/// /summary
1164/// param name="pkTableName"主键表/param
1165/// param name="pkConstraint"主键/param
1166public override void DisablePKConstraint(string pkTableName,string pkConstraint)
1167{
1168DropPKConstraint(pkTableName,pkConstraint);
1169}
1170
1171/// summary
1172/// Disable外键
1173/// /summary
1174/// param name="fkTableName"外键表/param
1175/// param name="fkConstraint"外键/param
1176public override void DisableFKConstraint(string fkTableName,string fkConstraint)
1177{
1178DropFKConstraint(fkTableName,fkConstraint);
1179}
1180
1181/// summary
1182/// Enable主键
1183/// /summary
1184/// param name="pkTableName"主键表/param
1185/// param name="pkConstraint"主键/param
1186/// param name="pkField"主键字段/param
1187public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)
1188{
1189CreatePKConstraint(pkTableName,pkConstraint,pkField);
1190}
1191
1192/// summary
1193/// Enable外键
1194/// /summary
1195/// param name="fkTableName"外键表/param
1196/// param name="fkConstraint"外键/param
1197/// param name="fkField"外键字段/param
1198/// param name="pkTableName"主键表/param
1199/// param name="pkField"主键字段/param
1200public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)
1201{
1202CreateFKConstraint(fkTableName,fkConstraint,fkField,pkTableName,pkField);
1203}
1204
1205
1206/// summary
1207/// 删除主键
1208/// /summary
1209/// param name="pkTableName"主键表/param
1210/// param name="pkConstraint"主键/param
1211public override void DropPKConstraint(string pkTableName,string pkConstraint)
1212{
1213string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
1214pkTableName,pkConstraint);
1215DbHelper helper = new DbHelper();
1216helper.ExecuteNonQuery(commandText);
1217}
1218
1219/// summary
1220/// 删除外键
1221/// /summary
1222/// param name="fkTableName"外键表/param
1223/// param name="fkConstraint"外键/param
1224public override void DropFKConstraint(string fkTableName,string fkConstraint)
1225{
1226string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
1227fkTableName,fkConstraint);
1228DbHelper helper = new DbHelper();
1229helper.ExecuteNonQuery(commandText);
1230}
1231
1232/// summary
1233/// 创建主键
1234/// /summary
1235/// param name="pkTableName"主键表/param
1236/// param name="pkConstraint"主键/param
1237/// param name="pkField"主键字段/param
1238public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)
1239{
1240string commandText = string.Format(@"
1241ALTER TABLE [{0}] ADD CONSTRAINT
1242{1} PRIMARY KEY CLUSTERED
1243(
1244{2}
1245) ON [PRIMARY]
1246",
1247pkTableName,
1248pkConstraint,
1249pkField);
1250
1251DbHelper helper = new DbHelper();
1252helper.ExecuteNonQuery(commandText);
1253}
1254
1255/// summary
1256/// 创建外键
1257/// /summary
1258/// param name="fkTableName"外键表/param
1259/// param name="fkConstraint"外键/param
1260/// param name="fkField"外键字段/param
1261/// param name="pkTableName"主键表/param
1262/// param name="pkField"主键字段/param
1263public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)
1264{
1265string commandText = string.Format(@"
1266ALTER TABLE [{0}] ADD CONSTRAINT
1267{1} FOREIGN KEY
1268(
1269{2}
1270) REFERENCES [3}
1271(
1272{4}
1273)
1274",
1275fkTableName,
1276fkConstraint,
1277fkField,
1278pkTableName,
1279pkField);
1280
1281DbHelper helper = new DbHelper();
1282helper.ExecuteNonQuery(commandText);
1283
1284}
1285#endregion
1286
1287#region Identity
1288/// summary
1289/// 允许将显式值插入表的标识列中
1290/// /summary
1291/// param name="tableName"表名/param
1292public override void SetIdentityInsertOn(string tableName)
1293{
1294AdoHelper helper = AdoHelper.Create();
1295string commandText = @"
1296if (IDENT_SEED('{0}') is not null)
1297set IDENTITY_INSERT [{0}] ON
1298";
1299helper.ExecuteNonQuery(string.Format(commandText,tableName));
1300}
1301
1302/// summary
1303/// 不允许将显式值插入表的标识列中
1304/// /summary
1305/// param name="tableName"表名/param
1306public override void SetIdentityInsertOff(string tableName)
1307{
1308AdoHelper helper = AdoHelper.Create();
1309string commandText = @"
1310if (IDENT_SEED('{0}') is not null)
1311set IDENTITY_INSERT [{0}] off
1312";
1313helper.ExecuteNonQuery(string.Format(commandText,tableName));
1314}
1315
1316/// summary
1317/// 允许将显式值插入表的标识列中
1318/// /summary
1319/// param name="tableName"表名/param
1320public override string GetIdentityInsertOn(string tableName)
1321{
1322string commandText = @"
1323if (IDENT_SEED('{0}') is not null)
1324set IDENTITY_INSERT [{0}] on
1325";
1326return string.Format(commandText,tableName);
1327}
1328
1329/// summary
1330/// 不允许将显式值插入表的标识列中
1331/// /summary
1332/// param name="tableName"表名/param
1333public override string GetIdentityInsertOff(string tableName)
1334{
1335string commandText = @"
1336if (IDENT_SEED('{0}') is not null)
1337set IDENTITY_INSERT [{0}] off
1338";
1339return string.Format(commandText,tableName);
1340}
1341
1342/// summary
1343/// SQL Server 主键自动增长
1344/// /summary
1345/// param name="sName"递增的字段或序列/param
1346/// returns下一个递增的ID/returns
1347public override object NextIncreaseID(string seqName)
1348{
1349return Guid.NewGuid().ToString();
1350}
1351
1352#endregion
1353
1354#region Table
1355/// summary
1356/// 获得数据库中所有的表
1357/// /summary
1358/// returns以DataTable返回表的名称/returns
1359public override DataTable GetTableNames()
1360{
1361string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsUserTable') = 1 order by o.name";
1362DbHelper helper = new DbHelper();
1363return helper.ExecuteDataTable(selectCommandText);
1364}
1365
1366/// summary
1367/// 获得数据中表的注释
1368/// /summary
1369/// returns以DataTable返回表的注释/returns
1370public override DataTable GetTableComments()
1371{
1372string selectCommandText = @"
1373select
1374s.name as table_name ,
1375p.value as comments
1376from
1377sysproperties p,
1378sysobjects s ,
1379sysusers u
1380where
1381p.id = s.id and
1382s.uid = u.uid and
1383u.name = 'dbo' and
1384p.type='3'
1385";
1386DbHelper helper = new DbHelper();
1387return helper.ExecuteDataTable(selectCommandText);
1388}
1389
1390/// summary
1391/// 获得数据中表的注释
1392/// /summary
1393/// returns返回表的注释/returns
1394public override string GetTableComments(string tableName)
1395{
1396string selectCommandText = @"
1397select
1398p.value as comments
1399from
1400sysproperties p,
1401sysobjects s ,
1402sysusers u
1403where
1404p.id = s.id and
1405s.uid = u.uid and
1406u.name = 'dbo' and
1407p.type='3' and
1408s.name='{0}'
1409";
1410DbHelper helper = new DbHelper();
1411object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));
1412return o == DBNull.Value || o == null ? "" : o.ToString();
1413}
1414
1415/// summary
1416/// 格式化表名
1417/// /summary
1418/// param name="tableName"表名/param
1419/// returns/returns
1420public override string FormatTableName(string tableName)
1421{
1422return "["+tableName+"]";
1423}
1424
1425#endregion
1426
1427#region Field
1428/// summary
1429/// 获得指定表中的所有字段名称
1430/// /summary
1431/// param name="TableName"指定的表名/param
1432/// returns以DataTable返回字段的名称/returns
1433public override DataTable GetFieldNames(string tableName)
1434{
1435string selectCommandText = string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);
1436DbHelper helper = new DbHelper();
1437return helper.ExecuteDataTable(selectCommandText);
1438}
1439
1440/// summary
1441/// 获得数据中字段的注释
1442/// /summary
1443/// returns以DataTable返回字段的注释/returns
1444public override DataTable GetFieldComments()
1445{
1446string selectCommandText = @"
1447select
1448s.name as table_name ,
1449c.name as column_name,
1450case
1451when p.value is null then c.name
1452when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
1453else p.value
1454end as comments
1455from
1456sysproperties p,
1457syscolumns c ,
1458sysobjects s ,
1459sysusers u
1460where
1461p.id = c.id and
1462c.id = s.id and
1463p.smallid = c.colid and
1464s.uid = u.uid and
1465u.name = 'dbo'
1466";
1467DbHelper helper = new DbHelper();
1468return helper.ExecuteDataTable(selectCommandText);
1469}
1470
1471/// summary
1472/// 获得数据中字段的注释
1473/// /summary
1474/// returns以DataTable返回字段的注释/returns
1475public override DataTable GetFieldComments(string tableName)
1476{
1477string selectCommandText = @"
1478select
1479s.name as table_name ,
1480c.name as column_name,
1481case
1482when p.value is null then c.name
1483when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
1484else p.value
1485end as comments
1486from
1487sysproperties p,
1488syscolumns c ,
1489sysobjects s ,
1490sysusers u
1491where
1492p.id = c.id and
1493c.id = s.id and
1494p.smallid = c.colid and
1495s.uid = u.uid and
1496u.name = 'dbo' and
1497s.name = '{0}'
1498";
1499DbHelper helper = new DbHelper();
1500return helper.ExecuteDataTable(string.Format(selectCommandText,tableName));
1501}
1502
1503/// summary
1504/// 获得数据中字段的注释
1505/// /summary
1506/// returns返回字段的注释/returns
1507public override string GetFieldComments(string tableName,string fieldName)
1508{
1509string selectCommandText = @"
1510select
1511case
1512when p.value is null then c.name
1513when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name
1514else p.value
1515end as comments
1516from
1517sysproperties p,
1518syscolumns c ,
1519sysobjects s ,
1520sysusers u
1521where
1522p.id = c.id and
1523c.id = s.id and
1524p.smallid = c.colid and
1525s.uid = u.uid and
1526u.name = 'dbo' and
1527s.name = '{0}' and
1528c.name = '{1}'
1529";
1530DbHelper helper = new DbHelper();
1531object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));
1532return o == DBNull.Value || o == null ? "" : o.ToString();
1533
1534}
1535#endregion
1536
1537#region View
1538/// summary
1539/// 判断是否存在对应的视图
1540/// /summary
1541/// param name="viewName"视图名/param
1542/// returns存在返回true,不存在返回fasle/returns
1543public override bool ExistView(string viewName)
1544{
1545string selectCommandText = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '{0}'", viewName);
1546DbHelper helper = new DbHelper();
1547return helper.ExecuteScalar(selectCommandText) != null;
1548}
1549
1550/// summary
1551/// 获得数据库中所有的用户视图
1552/// /summary
1553/// returns以DataTable返回视图的名称/returns
1554public override DataTable GetViewNames()
1555{
1556string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsView') = 1 order by o.name";
1557DbHelper helper = new DbHelper();
1558return helper.ExecuteDataTable(selectCommandText);
1559}
1560
1561/// summary
1562/// 获得指定视图的内容(如果已经加密，则返回空)
1563/// /summary
1564/// param name="viewName"视图名/param
1565/// returns/returns
1566public override string GetViewText(string viewName)
1567{
1568string 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+"]')";
1569DbHelper helper = new DbHelper();
1570object o = helper.ExecuteScalar(selectCommandText);
1571return o == DBNull.Value || o == null ? "" : o.ToString();
1572}
1573
1574/// summary
1575/// 删除数据库视图
1576/// /summary
1577/// param name="viewName"视图名/param
1578public override void DropView(string viewName)
1579{
1580DbHelper helper = new DbHelper();
1581helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+viewName+"]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].["+viewName+"]");
1582}
1583
1584/// summary
1585/// 创建数据库视图
1586/// /summary
1587/// param name="viewName"视图名/param
1588/// param name="viewText"视图内容/param
1589public override void CreateView(string viewName, string viewText)
1590{
1591if(viewText != null && viewText.Trim() != "")
1592{
1593DropView(viewName);
1594DbHelper helper = new DbHelper();
1595helper.ExecuteNonQuery(viewText);
1596}
1597}
1598#endregion
1599
1600#region Procedure
1601/// summary
1602/// 获得数据库中所有的用户存储过程
1603/// /summary
1604/// returns以DataTable返回存储过程的名称/returns
1605public override DataTable GetProcedureNames()
1606{
1607string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsProcedure') = 1 order by o.name";
1608DbHelper helper = new DbHelper();
1609return helper.ExecuteDataTable(selectCommandText);
1610}
1611
1612/// summary
1613/// 获得指定存储过程的内容
1614/// /summary
1615/// param name="procedureName"存储过程名/param
1616/// returns/returns
1617public override string GetProcedureText(string procedureName)
1618{
1619string 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+"]')";
1620DbHelper helper = new DbHelper();
1621object o = helper.ExecuteScalar(selectCommandText);
1622return o == DBNull.Value || o == null ? "" : o.ToString();
1623}
1624
1625/// summary
1626/// 删除数据库存储过程
1627/// /summary
1628/// param name="functionName"存储过程名/param
1629public override void DropProcedure(string procedureName)
1630{
1631DbHelper helper = new DbHelper();
1632helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+procedureName+"]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].["+procedureName+"]");
1633}
1634
1635
1636/// summary
1637/// 创建数据库存储过程
1638/// /summary
1639/// param name="procedureName"存储过程名/param
1640/// param name="procedureText"存储过程内容/param
1641public override void CreateProcedure(string procedureName, string procedureText)
1642{
1643
1644if(procedureText != null && procedureText.Trim() != "")
1645{
1646DropProcedure(procedureName);
1647DbHelper helper = new DbHelper();
1648helper.ExecuteNonQuery(procedureText);
1649}
1650}
1651
1652#endregion
1653
1654#region Function
1655/// summary
1656/// 获得数据库中所有的用户函数
1657/// /summary
1658/// returns以DataTable返回函数的名称/returns
1659public override DataTable GetFunctionNames()
1660{
1661string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsScalarFunction') = 1 order by o.name";
1662DbHelper helper = new DbHelper();
1663return helper.ExecuteDataTable(selectCommandText);
1664}
1665
1666/// summary
1667/// 获得指定函数的内容
1668/// /summary
1669/// param name="functionName"函数名/param
1670/// returns/returns
1671public override string GetFunctionText(string functionName)
1672{
1673string 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+"]')";
1674DbHelper helper = new DbHelper();
1675object o = helper.ExecuteScalar(selectCommandText);
1676return o == DBNull.Value || o == null ? "" : o.ToString();
1677}
1678
1679/// summary
1680/// 删除数据库函数
1681/// /summary
1682/// param name="functionName"函数名/param
1683public override void DropFunction(string functionName)
1684{
1685DbHelper helper = new DbHelper();
1686helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+functionName+"]') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1) drop function [dbo].["+functionName+"]");
1687}
1688
1689/// summary
1690/// 创建数据库函数
1691/// /summary
1692/// param name="functionName"函数名/param
1693/// param name="functionText"函数内容/param
1694public override void CreateFunction(string functionName, string functionText)
1695{
1696
1697if(functionText != null && functionText.Trim() != "")
1698{
1699DropFunction(functionName);
1700DbHelper helper = new DbHelper();
1701helper.ExecuteNonQuery(functionText);
1702}
1703}
1704#endregion
1705
1706#region Trigger
1707/// summary
1708/// 返回数据库中所有的触发器
1709/// /summary
1710/// returns
1711/// 返回的DataTable列
1712/// TriggerName 触发器名称
1713/// TableName 触发器所在表名
1714/// /returns
1715public override DataTable GetTriggerName()
1716{
1717string selectCommandText = @"
1718select object_name(a.parent_obj) as TableName,a.name as TriggerName
1719from sysobjects a,sysobjects b
1720where a.parent_obj=b.id and
1721OBJECTPROPERTY(a.id, N'IsTrigger') = 1
1722order by object_name(a.parent_obj)
1723";
1724DbHelper helper = new DbHelper();
1725return helper.ExecuteDataTable(selectCommandText);
1726}
1727
1728/// summary
1729/// 删除触发器
1730/// /summary
1731/// param name="triggerName"触发器名称/param
1732public override void DropTrigger(string triggerName)
1733{
1734DbHelper helper = new DbHelper();
1735helper.ExecuteNonQuery("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["+triggerName+"]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger [dbo].["+triggerName+"]");
1736}
1737
1738/// summary
1739/// 创建触发器
1740/// /summary
1741/// param name="triggerName"触发器名称/param
1742/// param name="triggerText"触发器内容/param
1743public override void CreateTrigger(string triggerName,string triggerText)
1744{
1745if(triggerText != null && triggerText.Trim() != "")
1746{
1747DbHelper helper = new DbHelper();
1748DropTrigger(triggerName);
1749helper.ExecuteNonQuery(triggerText);
1750}
1751}
1752
1753/// summary
1754/// Enable触发器
1755/// /summary
1756/// param name="triggerName"触发器名称/param
1757/// param name="tableName"触发器所在表名/param
1758public override void EnableTrigger(string triggerName,string tableName)
1759{
1760string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName);
1761DbHelper helper = new DbHelper();
1762helper.ExecuteNonQuery(commandText);
1763}
1764
1765/// summary
1766/// Disable触发器
1767/// /summary
1768/// param name="triggerName"触发器名称/param
1769/// param name="tableName"触发器所在表名/param
1770public override void DisableTrigger(string triggerName,string tableName)
1771{
1772string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName);
1773DbHelper helper = new DbHelper();
1774helper.ExecuteNonQuery(commandText);
1775}
1776
1777#endregion
1778
1779}
1780
1781}
1782
1783
using System;2
using System.Reflection;3
using System.Data;4
using System.Configuration;5
6
using HZAllview.Console.Utility;7
namespace HZAllview.Console.Data.Common8
{
9
/// 
10
/// 数据库操作的一些常用函数。11
/// written by tmc 12
/// 13
public abstract class AdoUtil14
{ 15
16
#region Factory17
18
/// 19
/// 根据配置文件指定的数据驱动提供者，加载相应的程序集20
/// 21
/// DbTable对象22
public static AdoUtil Create()23
{24
//获得程序集路径25
string path = AdoConfig.AssemblyPath;26
//类名27
string className = path + ".DbUtil";28
29
//加载程序集30
Assembly assembly = Assembly.Load(path);31
32
//创建对象的实例33
object adoUtil = assembly.CreateInstance(className);34
if( adoUtil is AdoUtil )35
{ 36
return adoUtil as AdoUtil;37
}38
else39
{40
throw new InvalidOperationException( className +" 没有继承抽象类 HZAllview.Console.Data.Common.AdoUtil " );41
} 42
43
}44
45
#endregion 46
47
#region Format Date48
49
/// 50
/// 格式化日期字符串51
/// 52
/// param name="dateValue"日期字符串/param53
/// 54
public abstract string FormatDateString(string dateValue);55
56
/// 57
/// 格式化日期的年份字段58
/// 59
/// param name="dateField"日期字段/param60
/// 61
public abstract string DatePartYear(string dateField);62
63
/// 64
/// 格式化日期的月份字段65
/// 66
/// param name="dateField"日期字段/param67
/// 68
public abstract string DatePartMonth(string dateField);69
70
/// 71
/// 格式化日期的日字段72
/// 73
/// param name="dateField"日期字段/param74
/// 75
public abstract string DateParDay(string dateField); 76
77
#endregion 78
79
#region Constraint80
/// 81
/// 获得数据库中所有的主键82
/// 83
/// 84
/// 返回DataTable，列如下85
/// PKTable 主键表86
/// PKConstraint 主键87
/// KeyCol1 主键字段88
///89
public abstract DataTable GetPKConstraint();90
91
/// 92
/// 获得数据库中所有的外键93
/// 94
/// 95
/// 返回DataTable，列如下96
/// PKTable 主键表97
/// FKTable 外键表98
/// FKConstraint 外键99
/// KeyCol1 主键字段100
/// RefCol1 外键字段101
///102
public abstract DataTable GetFKConstraint();103
104
/// 105
/// Disable主键106
/// 107
/// param name="pkTableName"主键表/param108
/// param name="pkConstraint"主键/param109
public abstract void DisablePKConstraint(string pkTableName,string pkConstraint);110
111
/// 112
/// Disable外键113
/// 114
/// param name="fkTableName"外键表/param115
/// param name="fkConstraint"外键/param116
public abstract void DisableFKConstraint(string fkTableName,string fkConstraint);117
118
/// 119
/// Enable主键120
/// 121
/// param name="pkTableName"主键表/param122
/// param name="pkConstraint"主键/param123
/// param name="pkField"主键字段/param124
public abstract void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField);125
126
/// 127
/// Enable外键128
/// 129
/// param name="fkTableName"外键表/param130
/// param name="fkConstraint"外键/param131
/// param name="fkField"外键字段/param132
/// param name="pkTableName"主键表/param133
/// param name="pkField"主键字段/param134
public abstract void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);135
136
/// 137
/// 删除主键138
/// 139
/// param name="pkTableName"主键表/param140
/// param name="pkConstraint"主键/param141
public abstract void DropPKConstraint(string pkTableName,string pkConstraint);142
143
/// 144
/// 删除外键145
/// 146
/// param name="fkTableName"外键表/param147
/// param name="fkConstraint"外键/param148
public abstract void DropFKConstraint(string fkTableName,string fkConstraint);149
150
/// 151
/// 创建主键152
/// 153
/// param name="pkTableName"主键表/param154
/// param name="pkConstraint"主键/param155
/// param name="pkField"主键字段/param156
public abstract void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField);157
158
/// 159
/// 创建外键160
/// 161
/// param name="fkTableName"外键表/param162
/// param name="fkConstraint"外键/param163
/// param name="fkField"外键字段/param164
/// param name="pkTableName"主键表/param165
/// param name="pkField"主键字段/param166
public abstract void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField);167
168
#endregion169
170
#region Identity171
/// 172
/// 允许将显式值插入表的标识列中173
/// 174
/// param name="tableName"表名/param175
public virtual void SetIdentityInsertOn(string tableName)176
{177
}178
179
/// 180
/// 不允许将显式值插入表的标识列中181
/// 182
/// param name="tableName"表名/param183
public virtual void SetIdentityInsertOff(string tableName)184
{185
}186
187
/// 188
/// 允许将显式值插入表的标识列中189
/// 190
/// param name="tableName"表名/param191
public virtual string GetIdentityInsertOn(string tableName)192
{193
return "";194
}195
196
/// 197
/// 不允许将显式值插入表的标识列中198
/// 199
/// param name="tableName"表名/param200
public virtual string GetIdentityInsertOff(string tableName)201
{202
return "";203
}204
205
/// 206
/// 获得下一个递增的ID。如果是ORACLE则返回下一个序列207
/// 208
/// param name="sName"递增的字段或序列/param209
/// 下一个递增的ID210
public abstract object NextIncreaseID(string seqName);211
212
#endregion213
214
#region Table215
/// 216
/// 获得数据库中所有的表217
/// 218
/// 以DataTable返回表的名称219
public abstract DataTable GetTableNames();220
221
/// 222
/// 获得数据中表的注释223
/// 224
/// 以DataTable返回表的注释225
public abstract DataTable GetTableComments();226
227
/// 228
/// 获得数据中表的注释229
/// 230
/// 返回表的注释231
public abstract string GetTableComments(string tableName);232
233
/// 234
/// 格式化表名235
/// 236
/// param name="tableName"表名/param237
/// 238
public virtual string FormatTableName(string tableName)239
{240
return tableName;241
}242
243
#endregion244
245
#region Field246
/// 247
/// 获得指定表中的所有字段名称248
/// 249
/// param name="TableName"指定的表名/param250
/// 以DataTable返回字段的名称251
public abstract DataTable GetFieldNames(string tableName);252
253
/// 254
/// 获得数据中字段的注释255
/// 256
/// 以DataTable返回字段的注释257
public abstract DataTable GetFieldComments();258
259
/// 260
/// 获得数据中字段的注释261
/// 262
/// 以DataTable返回字段的注释263
public abstract DataTable GetFieldComments(string tableName);264
265
/// 266
/// 获得数据中字段的注释267
/// 268
/// 返回字段的注释269
public abstract string GetFieldComments(string tableName,string fieldName);270
271
#endregion272
273
#region View274
/// 275
/// 判断是否存在对应的视图276
/// 277
/// param name="viewName"视图名/param278
/// 存在返回true,不存在返回fasle279
public abstract bool ExistView(string viewName);280
281
/// 282
/// 获得数据库中所有的用户视图283
/// 284
/// 以DataTable返回视图的名称285
public abstract DataTable GetViewNames();286
287
/// 288
/// 获得指定视图的内容289
/// 290
/// param name="viewName"视图名/param291
/// 292
public abstract string GetViewText(string viewName);293
294
/// 295
/// 删除数据库视图296
/// 297
/// param name="viewName"视图名/param298
public abstract void DropView(string viewName);299
300
/// 301
/// 创建数据库视图302
/// 303
/// param name="viewName"视图名/param304
/// param name="viewText"视图内容/param305
public abstract void CreateView(string viewName,string viewText);306
307
#endregion308
309
#region Procedure310
/// 311
/// 获得数据库中所有的用户存储过程312
/// 313
/// 以DataTable返回存储过程的名称314
public abstract DataTable GetProcedureNames();315
316
/// 317
/// 获得指定存储过程的内容318
/// 319
/// param name="procedureName"存储过程名/param320
/// 321
public abstract string GetProcedureText(string procedureName);322
323
/// 324
/// 删除数据库存储过程325
/// 326
/// param name="functionName"存储过程名/param327
public abstract void DropProcedure(string procedureName);328
329
/// 330
/// 创建数据库存储过程331
/// 332
/// param name="procedureName"存储过程名/param333
/// param name="procedureText"存储过程内容/param334
public abstract void CreateProcedure(string procedureName, string procedureText);335
336
#endregion337
338
#region Function339
/// 340
/// 获得数据库中所有的用户函数341
/// 342
/// 以DataTable返回函数的名称343
public abstract DataTable GetFunctionNames();344
345
/// 346
/// 获得指定函数的内容347
/// 348
/// param name="functionName"函数名/param349
/// 350
public abstract string GetFunctionText(string functionName);351
352
/// 353
/// 删除数据库函数354
/// 355
/// param name="functionName"函数名/param356
public abstract void DropFunction(string functionName);357
358
/// 359
/// 创建数据库函数360
/// 361
/// param name="functionName"函数名/param362
/// param name="functionText"函数内容/param363
public abstract void CreateFunction(string functionName, string functionText);364
365
#endregion366
367
#region Trigger368
/// 369
/// 返回数据库中所有的触发器370
/// 371
/// 372
/// 返回的DataTable列373
/// TriggerName 触发器名称374
/// TableName 触发器所在表名 375
/// /returns376
public abstract DataTable GetTriggerName();377
378
/// 379
/// 删除触发器380
/// 381
/// param name="triggerName"触发器名称/param382
public abstract void DropTrigger(string triggerName);383
384
/// 385
/// 创建触发器386
/// 387
/// param name="triggerName"触发器名称/param388
/// param name="triggerText"触发器内容/param389
public abstract void CreateTrigger(string triggerName,string triggerText);390
391
/// 392
/// Enable触发器393
/// 394
/// param name="triggerName"触发器名称/param395
/// param name="tableName"触发器所在表名/param396
public abstract void EnableTrigger(string triggerName,string tableName);397
398
/// 399
/// Disable触发器400
/// 401
/// param name="triggerName"触发器名称/param402
/// param name="tableName"触发器所在表名/param403
public abstract void DisableTrigger(string triggerName,string tableName);404
405
#endregion406
407
} 408
}409
410
411
412
413
414
程序代码：415
using System;416
using System.Data;417
using System.Data.OracleClient;418
using HZAllview.Console.Data.Common;419
420
namespace HZAllview.Console.Data.Oracle421
{422
/// summary423
/// Oracle 数据库操作的一些常用函数424
/// written by tmc 425
/// /summary426
public class DbUtil : AdoUtil427
{428
429
#region Construction430
/// summary431
/// 需要预设的构造函数，以便工厂能被创建432
/// /summary433
public DbUtil()434
{435
436
}437
#endregion438
439
#region Format Date440
/// summary441
/// 格式化日期字符串442
/// /summary443
/// param name="dateValue"日期字符串/param444
/// returns/returns445
public override string FormatDateString(string dateValue)446
{447
if(dateValue == null || dateValue == "")448
return "''";449
450
dateValue = dateValue.Replace("00:00:00","").Trim(); //如果不存在"小时:分:妙",则剔除"00:00:00"451
452
//如果包含"小时:分:妙"453
if(dateValue.IndexOf(":") != -1)454
{455
return "TO_DATE('"+dateValue+"','YYYY-MM-DD HH24:MI:SS')";456
457
}458
else459
{460
return "TO_DATE('"+dateValue+"','YYYY-MM-DD')";461
}462
}463
464
/// summary465
/// 格式化日期的年份字段466
/// /summary467
/// param name="dateField"日期字段/param468
/// returns/returns469
public override string DatePartYear(string dateField)470
{471
return " TO_CHAR("+dateField+", 'YYYY')";472
}473
474
/// summary475
/// 格式化日期的月份字段476
/// /summary477
/// param name="dateField"日期字段/param478
/// returns/returns479
public override string DatePartMonth(string dateField)480
{481
return " TO_CHAR("+dateField+", 'MM')";482
}483
484
/// summary485
/// 格式化日期的日字段486
/// /summary487
/// param name="dateField"日期字段/param488
/// returns/returns489
public override string DateParDay(string dateField)490
{491
return " TO_CHAR("+dateField+", 'DD')";492
}493
494
#endregion 495
496
#region Constraint497
498
/// summary499
/// 获得数据库中所有的主键500
/// /summary501
/// returns502
/// 返回DataTable，列如下503
/// PKTable 主键表504
/// PKConstraint 主键505
/// KeyCol1 主键字段506
////returns507
public override DataTable GetPKConstraint()508
{509
string selectCommandText = @"510
select table_Name as PKTable,constraint_name as PKConstraint,'' as KeyCol1 from user_constraints where 511
512
Constraint_type='P'513
";514
DbHelper helper = new DbHelper();515
return helper.ExecuteDataTable(selectCommandText);516
}517
518
/// summary519
/// 获得数据库中所有的外键520
/// /summary521
/// returns522
/// 返回DataTable，列如下523
/// PKTable 主键表524
/// FKTable 外键表525
/// FKConstraint 外键526
/// KeyCol1 主键字段527
/// RefCol1 外键字段528
////returns529
public override DataTable GetFKConstraint()530
{531
string selectCommandText = @"532
select ''as PKTable,table_Name as FKTable,constraint_name as FKConstraint,'' as KeyCol1,'' as RefCol1 from 533
534
user_constraints where Constraint_type='R'535
"; 536
DbHelper helper = new DbHelper();537
return helper.ExecuteDataTable(selectCommandText);538
}539
540
/// summary541
/// Disable主键542
/// /summary543
/// param name="pkTableName"主键表/param544
/// param name="pkConstraint"主键/param545
public override void DisablePKConstraint(string pkTableName,string pkConstraint)546
{547
string commandText = string.Format("alter table {0} disable constraint {1}",548
pkTableName,pkConstraint); 549
550
551
DbHelper helper = new DbHelper();552
helper.ExecuteNonQuery(commandText);553
}554
555
/// summary556
/// Disable外键557
/// /summary558
/// param name="fkTableName"外键表/param559
/// param name="fkConstraint"外键/param560
public override void DisableFKConstraint(string fkTableName,string fkConstraint)561
{562
string commandText = string.Format("alter table {0} disable constraint {1}",563
fkTableName,fkConstraint); 564
565
566
DbHelper helper = new DbHelper();567
helper.ExecuteNonQuery(commandText);568
}569
570
/// summary571
/// Enable主键572
/// /summary573
/// param name="pkTableName"主键表/param574
/// param name="pkConstraint"主键/param575
/// param name="pkField"主键字段/param576
public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)577
{578
string commandText = string.Format("alter table {0} enable constraint {1}",579
pkTableName,pkConstraint); 580
581
582
DbHelper helper = new DbHelper();583
helper.ExecuteNonQuery(commandText);584
}585
586
/// summary587
/// Enable外键588
/// /summary589
/// param name="fkTableName"外键表/param590
/// param name="fkConstraint"外键/param591
/// param name="fkField"外键字段/param592
/// param name="pkTableName"主键表/param593
/// param name="pkField"主键字段/param594
public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string 595
596
pkTableName,string pkField)597
{598
string commandText = string.Format("alter table {0} enable constraint {1}",599
fkTableName,fkConstraint); 600
601
602
DbHelper helper = new DbHelper();603
helper.ExecuteNonQuery(commandText);604
}605
606
/// summary607
/// 删除主键608
/// /summary609
/// param name="pkTableName"主键表/param610
/// param name="pkConstraint"主键/param611
public override void DropPKConstraint(string pkTableName,string pkConstraint)612
{613
614
}615
616
/// summary617
/// 删除外键618
/// /summary619
/// param name="fkTableName"外键表/param620
/// param name="fkConstraint"外键/param621
public override void DropFKConstraint(string fkTableName,string fkConstraint)622
{623
624
}625
626
/// summary627
/// 增加主键628
/// /summary629
/// param name="pkTableName"主键表/param630
/// param name="pkConstraint"主键/param631
/// param name="pkField"主键字段/param632
public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)633
{634
635
}636
637
/// summary638
/// 增加外键639
/// /summary640
/// param name="fkTableName"外键表/param641
/// param name="fkConstraint"外键/param642
/// param name="fkField"外键字段/param643
/// param name="pkTableName"主键表/param644
/// param name="pkField"主键字段/param645
public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string 646
647
pkTableName,string pkField)648
{649
650
}651
#endregion652
653
#region Identity 654
655
/// summary656
/// 获得下一个递增的ID。如果是ORACLE则返回下一个序列657
/// /summary658
/// param name="sName"递增的字段或序列/param659
/// returns下一个递增的ID/returns660
public override object NextIncreaseID(string seqName)661
{662
string selectCommandText = string.Format("SELECT {0}.NEXTVAL FROM DUAL", seqName); 663
DbHelper helper = new DbHelper(); 664
return helper.ExecuteScalar(selectCommandText);665
}666
667
#endregion668
669
#region Table670
/// summary671
/// 获得数据库中所有的表672
/// /summary673
/// returns以DataTable返回表的名称/returns674
public override DataTable GetTableNames()675
{676
string selectCommandText = "SELECT TABLE_NAME FROM USER_TABLES";677
DbHelper helper = new DbHelper();678
return helper.ExecuteDataTable(selectCommandText);679
}680
681
/// summary682
/// 获得数据中表的注释683
/// /summary684
/// returns以DataTable返回表的注释/returns685
public override DataTable GetTableComments()686
{687
string selectCommandText = @"688
select table_name,comments689
from user_tab_comments690
";691
DbHelper helper = new DbHelper();692
return helper.ExecuteDataTable(selectCommandText);693
}694
695
/// summary696
/// 获得数据中表的注释697
/// /summary698
/// returns返回表的注释/returns699
public override string GetTableComments(string tableName)700
{701
string selectCommandText = @"702
select comments703
from user_tab_comments704
where table_name='{0}'705
";706
DbHelper helper = new DbHelper();707
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));708
return o == DBNull.Value || o == null ? "" : o.ToString();709
}710
711
#endregion712
713
#region Field714
/// summary715
/// 获得指定表中的所有字段名称716
/// /summary717
/// param name="TableName"指定的表名/param718
/// returns以DataTable返回字段的名称/returns719
public override DataTable GetFieldNames(string tableName)720
{721
string selectCommandText = string.Format("SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE 722
723
TABLE_NAME = '{0}'", tableName);724
DbHelper helper = new DbHelper();725
return helper.ExecuteDataTable(selectCommandText);726
}727
728
/// summary729
/// 获得数据中字段的注释730
/// /summary731
/// returns以DataTable返回字段的注释/returns732
public override DataTable GetFieldComments()733
{734
string selectCommandText = @"735
select table_name,column_name,736
nvl(comments ,column_name) as comments737
from user_col_comments 738
";739
DbHelper helper = new DbHelper();740
return helper.ExecuteDataTable(selectCommandText); 741
}742
743
/// summary744
/// 获得数据中字段的注释745
/// /summary746
/// returns以DataTable返回字段的注释/returns747
public override DataTable GetFieldComments(string tableName)748
{749
string selectCommandText = @"750
select table_name,column_name,751
nvl(comments ,column_name) as comments752
from user_col_comments 753
where 754
table_name = '{0}' 755
";756
DbHelper helper = new DbHelper();757
return helper.ExecuteDataTable(string.Format(selectCommandText,tableName)); 758
}759
760
/// summary761
/// 获得数据中字段的注释762
/// /summary763
/// returns返回字段的注释/returns764
public override string GetFieldComments(string tableName,string fieldName)765
{766
string selectCommandText = @"767
select table_name,column_name,768
nvl(comments ,column_name) as comments769
from user_col_comments 770
where 771
table_name = '{0}' and772
column_name = '{1}'773
";774
DbHelper helper = new DbHelper();775
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));776
return o == DBNull.Value || o == null ? "" : o.ToString(); 777
778
}779
#endregion780
781
#region View782
/// summary783
/// 判断是否存在对应的视图784
/// /summary785
/// param name="viewName"视图名/param786
/// returns存在返回true,不存在返回fasle/returns787
public override bool ExistView(string viewName)788
{789
string selectCommandText = string.Format("SELECT VIEW_NAME FROM USER_VIEWS WHERE VIEW_NAME = '{0}'", 790
791
viewName);792
DbHelper helper = new DbHelper();793
return helper.ExecuteScalar(selectCommandText) != null; 794
}795
796
/// summary797
/// 获得数据库中所有的用户视图798
/// /summary799
/// returns以DataTable返回视图的名称/returns800
public override DataTable GetViewNames()801
{802
string selectCommandText = "SELECT VIEW_NAME as Name FROM USER_VIEWS ORDER BY VIEW_NAME";803
DbHelper helper = new DbHelper();804
return helper.ExecuteDataTable(selectCommandText);805
}806
807
/// summary808
/// 获得指定视图的内容809
/// /summary810
/// param name="viewName"视图名/param811
/// returns/returns812
public override string GetViewText(string viewName)813
{814
string selectCommandText = "SELECT Text FROM USER_VIEWS WHERE VIEW_NAME='"+viewName+"'";815
DbHelper helper = new DbHelper();816
return "create or replace view "+viewName+" as " + helper.ExecuteScalar(selectCommandText).ToString817
818
();819
}820
821
/// summary822
/// 删除数据库视图823
/// /summary824
/// param name="viewName"视图名/param825
public override void DropView(string viewName)826
{827
}828
829
/// summary830
/// 创建数据库视图831
/// /summary832
/// param name="viewName"视图名/param833
/// param name="viewText"视图内容/param834
public override void CreateView(string viewName, string viewText)835
{836
if(viewText != null && viewText.Trim() != "")837
{838
DropView(viewName);839
DbHelper helper = new DbHelper();840
helper.ExecuteNonQuery(viewText);841
}842
}843
#endregion844
845
#region Procedure846
/// summary847
/// 获得数据库中所有的用户存储过程848
/// /summary849
/// returns以DataTable返回存储过程的名称/returns850
public override DataTable GetProcedureNames()851
{852
string selectCommandText = "select object_name as Name from user_objects where 853
854
object_type='PROCEDURE' order by object_name";855
DbHelper helper = new DbHelper();856
return helper.ExecuteDataTable(selectCommandText);857
}858
859
/// summary860
/// 获得指定存储过程的内容861
/// /summary862
/// param name="procedureName"存储过程名/param863
/// returns/returns864
public override string GetProcedureText(string procedureName)865
{866
string selectCommandText = string.Format(@"867
select text from USER_SOURCE where type='PROCEDURE' and name=upper('{0}')",868
procedureName); 869
DbHelper helper = new DbHelper();870
DataTable table = helper.ExecuteDataTable(selectCommandText);871
string ret = "create or replace ";872
for(int i = 0; i table.Rows.Count; i++)873
{874
ret += table.Rows[i][0].ToString()+ " ";875
}876
877
return ret;878
}879
880
/// summary881
/// 删除数据库存储过程882
/// /summary883
/// param name="functionName"存储过程名/param884
public override void DropProcedure(string procedureName)885
{886
887
}888
889
890
/// summary891
/// 创建数据库存储过程892
/// /summary893
/// param name="procedureName"存储过程名/param894
/// param name="procedureText"存储过程内容/param895
public override void CreateProcedure(string procedureName, string procedureText)896
{897
898
if(procedureText != null && procedureText.Trim() != "")899
{900
DropProcedure(procedureName);901
DbHelper helper = new DbHelper();902
helper.ExecuteNonQuery(procedureText);903
}904
}905
906
#endregion907
908
#region Function909
/// summary910
/// 获得数据库中所有的用户函数911
/// /summary912
/// returns以DataTable返回函数的名称/returns913
public override DataTable GetFunctionNames()914
{915
string selectCommandText = "select object_name as Name from user_objects where object_type='FUNCTION' 916
917
order by object_name";918
DbHelper helper = new DbHelper();919
return helper.ExecuteDataTable(selectCommandText);920
}921
922
/// summary923
/// 获得指定函数的内容924
/// /summary925
/// param name="functionName"函数名/param926
/// returns/returns927
public override string GetFunctionText(string functionName)928
{929
string selectCommandText = string.Format(@"930
select text from USER_SOURCE where type='FUNCTION' and name=upper('{0}')",931
functionName); 932
DbHelper helper = new DbHelper();933
DataTable table = helper.ExecuteDataTable(selectCommandText);934
string ret = "create or replace ";935
for(int i = 0; i table.Rows.Count; i++)936
{937
ret += table.Rows[i][0].ToString()+ " ";938
}939
940
return ret;941
}942
943
/// summary944
/// 删除数据库函数945
/// /summary946
/// param name="functionName"函数名/param947
public override void DropFunction(string functionName)948
{949
}950
951
/// summary952
/// 创建数据库函数953
/// /summary954
/// param name="functionName"函数名/param955
/// param name="functionText"函数内容/param956
public override void CreateFunction(string functionName, string functionText)957
{958
959
if(functionText != null && functionText.Trim() != "")960
{961
DropFunction(functionName);962
DbHelper helper = new DbHelper();963
helper.ExecuteNonQuery(functionText);964
}965
}966
967
#endregion 968
969
#region Trigger970
/// summary971
/// 返回数据库中所有的触发器972
/// trigger_body 内容973
/// /summary974
/// returns975
/// 返回的DataTable列976
/// TriggerName 触发器名称977
/// TableName 触发器所在表名 978
/// /returns979
public override DataTable GetTriggerName()980
{981
string selectCommandText = @"982
select trigger_name as TriggerName,table_name as TableName from user_triggers983
";984
DbHelper helper = new DbHelper();985
return helper.ExecuteDataTable(selectCommandText);986
}987
988
/// summary989
/// 删除触发器990
/// /summary991
/// param name="triggerName"触发器名称/param992
public override void DropTrigger(string triggerName)993
{994
}995
996
/// summary997
/// 创建触发器998
/// /summary999
/// param name="triggerName"触发器名称/param1000
/// param name="triggerText"触发器内容/param1001
public override void CreateTrigger(string triggerName,string triggerText)1002
{1003
1004
}1005
1006
/// summary1007
/// Enable触发器1008
/// /summary1009
/// param name="triggerName"触发器名称/param1010
/// param name="tableName"触发器所在表名/param1011
public override void EnableTrigger(string triggerName,string tableName)1012
{1013
string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName); 1014
1015
1016
DbHelper helper = new DbHelper();1017
helper.ExecuteNonQuery(commandText);1018
}1019
1020
/// summary1021
/// Disable触发器1022
/// /summary1023
/// param name="triggerName"触发器名称/param1024
/// param name="tableName"触发器所在表名/param1025
public override void DisableTrigger(string triggerName,string tableName)1026
{1027
string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName); 1028
1029
1030
DbHelper helper = new DbHelper();1031
helper.ExecuteNonQuery(commandText);1032
}1033
1034
#endregion1035
1036
}1037
}1038
1039
1040
1041
1042
1043
程序代码：1044
using System;1045
using System.Data;1046
using System.Configuration;1047
using HZAllview.Console.Data.Common;1048
using HZAllview.Console.Utility;1049
1050
namespace HZAllview.Console.Data.SqlServer1051
{1052
/// summary1053
/// SQL Server 数据库操作的一些常用函数。1054
/// written by tmc 1055
/// /summary1056
public class DbUtil : AdoUtil1057
{1058
1059
#region Construction1060
/// summary1061
/// 需要预设的构造函数，以便工厂能被创建1062
/// /summary1063
public DbUtil()1064
{ 1065
}1066
#endregion1067
1068
#region Format Date1069
/// summary1070
/// 格式化日期字符串1071
/// /summary1072
/// param name="dateValue"日期字符串/param1073
/// returns/returns1074
public override string FormatDateString(string dateValue)1075
{1076
if(dateValue == null || dateValue == "")1077
return "''"; 1078
else 1079
return "'"+dateValue+"'"; 1080
}1081
1082
/// summary1083
/// 格式化日期的年份字段1084
/// /summary1085
/// param name="dateField"日期字段/param1086
/// returns/returns1087
public override string DatePartYear(string dateField)1088
{1089
return " DATEPART(YEAR,"+dateField+") ";1090
}1091
1092
/// summary1093
/// 格式化日期的月份字段1094
/// /summary1095
/// param name="dateField"日期字段/param1096
/// returns/returns1097
public override string DatePartMonth(string dateField)1098
{1099
return " DATEPART(MONTH,"+dateField+") ";1100
}1101
1102
/// summary1103
/// 格式化日期的日字段1104
/// /summary1105
/// param name="dateField"日期字段/param1106
/// returns/returns1107
public override string DateParDay(string dateField)1108
{1109
return " DATEPART(DAY,"+dateField+") ";1110
}1111
1112
#endregion 1113
1114
#region Constraint1115
/// summary1116
/// 获得数据库中所有的主键1117
/// /summary1118
/// returns1119
/// 返回DataTable，列如下1120
/// PKTable 主键表1121
/// PKConstraint 主键1122
/// KeyCol1 主键字段1123
////returns1124
public override DataTable GetPKConstraint()1125
{1126
string selectCommandText = @"1127
select a.name as PKTable,b.name as PKConstraint,1128
index_col(a.name, c.indid, 1) as KeyCol1 1129
from sysobjects a,sysobjects b,sysindexes c 1130
where a.id=b.parent_obj and b.name=c.name and b.xtype='PK' order by a.name";1131
DbHelper helper = new DbHelper();1132
return helper.ExecuteDataTable(selectCommandText);1133
}1134
1135
/// summary1136
/// 获得数据库中所有的外键1137
/// /summary1138
/// returns1139
/// 返回DataTable，列如下1140
/// PKTable 主键表1141
/// FKTable 外键表1142
/// FKConstraint 外键1143
/// KeyCol1 主键字段1144
/// RefCol1 外键字段1145
////returns1146
public override DataTable GetFKConstraint()1147
{1148
string selectCommandText = @"1149
select PKT.name as PKTable,FKT.name as FKTable,1150
object_name(c.constid) as FKConstraint,1151
convert(nvarchar(132), col_name(c.rkeyid, c.rkey1)) as KeyCol1,1152
convert(nvarchar(132), col_name(c.fkeyid, c.fkey1)) as RefCol11153
from sysobjects PKT,sysobjects FKT,sysreferences c 1154
where 1155
( c.rkeyid=object_id(PKT.name) or c.fkeyid = object_id(PKT.name))1156
and PKT.id = c.rkeyid and FKT.id = c.fkeyid"; 1157
DbHelper helper = new DbHelper();1158
return helper.ExecuteDataTable(selectCommandText);1159
}1160
1161
/// summary1162
/// Disable主键1163
/// /summary1164
/// param name="pkTableName"主键表/param1165
/// param name="pkConstraint"主键/param1166
public override void DisablePKConstraint(string pkTableName,string pkConstraint)1167
{1168
DropPKConstraint(pkTableName,pkConstraint);1169
}1170
1171
/// summary1172
/// Disable外键1173
/// /summary1174
/// param name="fkTableName"外键表/param1175
/// param name="fkConstraint"外键/param1176
public override void DisableFKConstraint(string fkTableName,string fkConstraint)1177
{1178
DropFKConstraint(fkTableName,fkConstraint);1179
}1180
1181
/// summary1182
/// Enable主键1183
/// /summary1184
/// param name="pkTableName"主键表/param1185
/// param name="pkConstraint"主键/param1186
/// param name="pkField"主键字段/param1187
public override void EnablePKConstraint(string pkTableName,string pkConstraint,string pkField)1188
{1189
CreatePKConstraint(pkTableName,pkConstraint,pkField);1190
}1191
1192
/// summary1193
/// Enable外键1194
/// /summary1195
/// param name="fkTableName"外键表/param1196
/// param name="fkConstraint"外键/param1197
/// param name="fkField"外键字段/param1198
/// param name="pkTableName"主键表/param1199
/// param name="pkField"主键字段/param1200
public override void EnableFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)1201
{1202
CreateFKConstraint(fkTableName,fkConstraint,fkField,pkTableName,pkField);1203
}1204
1205
1206
/// summary1207
/// 删除主键1208
/// /summary1209
/// param name="pkTableName"主键表/param1210
/// param name="pkConstraint"主键/param1211
public override void DropPKConstraint(string pkTableName,string pkConstraint)1212
{1213
string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",1214
pkTableName,pkConstraint); 1215
DbHelper helper = new DbHelper();1216
helper.ExecuteNonQuery(commandText);1217
}1218
1219
/// summary1220
/// 删除外键1221
/// /summary1222
/// param name="fkTableName"外键表/param1223
/// param name="fkConstraint"外键/param1224
public override void DropFKConstraint(string fkTableName,string fkConstraint)1225
{1226
string commandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT {1}",1227
fkTableName,fkConstraint); 1228
DbHelper helper = new DbHelper();1229
helper.ExecuteNonQuery(commandText);1230
}1231
1232
/// summary1233
/// 创建主键1234
/// /summary1235
/// param name="pkTableName"主键表/param1236
/// param name="pkConstraint"主键/param1237
/// param name="pkField"主键字段/param1238
public override void CreatePKConstraint(string pkTableName,string pkConstraint,string pkField)1239
{1240
string commandText = string.Format(@"1241
ALTER TABLE [{0}] ADD CONSTRAINT1242
{1} PRIMARY KEY CLUSTERED 1243
(1244
{2}1245
) ON [PRIMARY]1246
",1247
pkTableName,1248
pkConstraint,1249
pkField);1250
1251
DbHelper helper = new DbHelper();1252
helper.ExecuteNonQuery(commandText); 1253
}1254
1255
/// summary1256
/// 创建外键1257
/// /summary1258
/// param name="fkTableName"外键表/param1259
/// param name="fkConstraint"外键/param1260
/// param name="fkField"外键字段/param1261
/// param name="pkTableName"主键表/param1262
/// param name="pkField"主键字段/param1263
public override void CreateFKConstraint(string fkTableName,string fkConstraint,string fkField,string pkTableName,string pkField)1264
{1265
string commandText = string.Format(@"1266
ALTER TABLE [{0}] ADD CONSTRAINT1267
{1} FOREIGN KEY1268
(1269
{2}1270
) REFERENCES [3}1271
(1272
{4}1273
)1274
",1275
fkTableName,1276
fkConstraint,1277
fkField,1278
pkTableName,1279
pkField);1280
1281
DbHelper helper = new DbHelper();1282
helper.ExecuteNonQuery(commandText);1283
1284
}1285
#endregion1286
1287
#region Identity1288
/// summary1289
/// 允许将显式值插入表的标识列中1290
/// /summary1291
/// param name="tableName"表名/param1292
public override void SetIdentityInsertOn(string tableName)1293
{1294
AdoHelper helper = AdoHelper.Create();1295
string commandText = @"1296
if (IDENT_SEED('{0}') is not null)1297
set IDENTITY_INSERT [{0}] ON 1298
";1299
helper.ExecuteNonQuery(string.Format(commandText,tableName));1300
}1301
1302
/// summary1303
/// 不允许将显式值插入表的标识列中1304
/// /summary1305
/// param name="tableName"表名/param1306
public override void SetIdentityInsertOff(string tableName)1307
{1308
AdoHelper helper = AdoHelper.Create();1309
string commandText = @"1310
if (IDENT_SEED('{0}') is not null)1311
set IDENTITY_INSERT [{0}] off 1312
";1313
helper.ExecuteNonQuery(string.Format(commandText,tableName));1314
}1315
1316
/// summary1317
/// 允许将显式值插入表的标识列中1318
/// /summary1319
/// param name="tableName"表名/param1320
public override string GetIdentityInsertOn(string tableName)1321
{ 1322
string commandText = @"1323
if (IDENT_SEED('{0}') is not null)1324
set IDENTITY_INSERT [{0}] on 1325
";1326
return string.Format(commandText,tableName);1327
}1328
1329
/// summary1330
/// 不允许将显式值插入表的标识列中1331
/// /summary1332
/// param name="tableName"表名/param1333
public override string GetIdentityInsertOff(string tableName)1334
{1335
string commandText = @"1336
if (IDENT_SEED('{0}') is not null)1337
set IDENTITY_INSERT [{0}] off 1338
";1339
return string.Format(commandText,tableName);1340
}1341
1342
/// summary1343
/// SQL Server 主键自动增长1344
/// /summary1345
/// param name="sName"递增的字段或序列/param1346
/// returns下一个递增的ID/returns1347
public override object NextIncreaseID(string seqName)1348
{1349
return Guid.NewGuid().ToString();1350
}1351
1352
#endregion1353
1354
#region Table1355
/// summary1356
/// 获得数据库中所有的表1357
/// /summary1358
/// returns以DataTable返回表的名称/returns1359
public override DataTable GetTableNames()1360
{1361
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsUserTable') = 1 order by o.name";1362
DbHelper helper = new DbHelper();1363
return helper.ExecuteDataTable(selectCommandText);1364
}1365
1366
/// summary1367
/// 获得数据中表的注释1368
/// /summary1369
/// returns以DataTable返回表的注释/returns1370
public override DataTable GetTableComments()1371
{1372
string selectCommandText = @"1373
select 1374
s.name as table_name ,1375
p.value as comments1376
from 1377
sysproperties p, 1378
sysobjects s ,1379
sysusers u 1380
where1381
p.id = s.id and 1382
s.uid = u.uid and 1383
u.name = 'dbo' and1384
p.type='3'1385
";1386
DbHelper helper = new DbHelper();1387
return helper.ExecuteDataTable(selectCommandText);1388
}1389
1390
/// summary1391
/// 获得数据中表的注释1392
/// /summary1393
/// returns返回表的注释/returns1394
public override string GetTableComments(string tableName)1395
{1396
string selectCommandText = @"1397
select 1398
p.value as comments1399
from 1400
sysproperties p, 1401
sysobjects s ,1402
sysusers u 1403
where1404
p.id = s.id and 1405
s.uid = u.uid and 1406
u.name = 'dbo' and1407
p.type='3' and1408
s.name='{0}'1409
";1410
DbHelper helper = new DbHelper();1411
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName));1412
return o == DBNull.Value || o == null ? "" : o.ToString();1413
}1414
1415
/// summary1416
/// 格式化表名1417
/// /summary1418
/// param name="tableName"表名/param1419
/// returns/returns1420
public override string FormatTableName(string tableName)1421
{1422
return "["+tableName+"]";1423
}1424
1425
#endregion1426
1427
#region Field1428
/// summary1429
/// 获得指定表中的所有字段名称1430
/// /summary1431
/// param name="TableName"指定的表名/param1432
/// returns以DataTable返回字段的名称/returns1433
public override DataTable GetFieldNames(string tableName)1434
{1435
string selectCommandText = string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName);1436
DbHelper helper = new DbHelper();1437
return helper.ExecuteDataTable(selectCommandText);1438
}1439
1440
/// summary1441
/// 获得数据中字段的注释1442
/// /summary1443
/// returns以DataTable返回字段的注释/returns1444
public override DataTable GetFieldComments()1445
{1446
string selectCommandText = @"1447
select 1448
s.name as table_name ,1449
c.name as column_name,1450
case 1451
when p.value is null then c.name1452
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name1453
else p.value1454
end as comments 1455
from 1456
sysproperties p,1457
syscolumns c ,1458
sysobjects s ,1459
sysusers u 1460
where1461
p.id = c.id and 1462
c.id = s.id and1463
p.smallid = c.colid and1464
s.uid = u.uid and 1465
u.name = 'dbo' 1466
";1467
DbHelper helper = new DbHelper();1468
return helper.ExecuteDataTable(selectCommandText); 1469
}1470
1471
/// summary1472
/// 获得数据中字段的注释1473
/// /summary1474
/// returns以DataTable返回字段的注释/returns1475
public override DataTable GetFieldComments(string tableName)1476
{1477
string selectCommandText = @"1478
select 1479
s.name as table_name ,1480
c.name as column_name,1481
case 1482
when p.value is null then c.name1483
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name1484
else p.value1485
end as comments 1486
from 1487
sysproperties p,1488
syscolumns c ,1489
sysobjects s ,1490
sysusers u 1491
where1492
p.id = c.id and 1493
c.id = s.id and1494
p.smallid = c.colid and1495
s.uid = u.uid and 1496
u.name = 'dbo' and1497
s.name = '{0}' 1498
";1499
DbHelper helper = new DbHelper();1500
return helper.ExecuteDataTable(string.Format(selectCommandText,tableName)); 1501
}1502
1503
/// summary1504
/// 获得数据中字段的注释1505
/// /summary1506
/// returns返回字段的注释/returns1507
public override string GetFieldComments(string tableName,string fieldName)1508
{1509
string selectCommandText = @"1510
select 1511
case 1512
when p.value is null then c.name1513
when RTrim(Ltrim(cast(p.value as varchar))) = '' then c.name1514
else p.value1515
end as comments 1516
from 1517
sysproperties p,1518
syscolumns c ,1519
sysobjects s ,1520
sysusers u 1521
where1522
p.id = c.id and 1523
c.id = s.id and1524
p.smallid = c.colid and1525
s.uid = u.uid and 1526
u.name = 'dbo' and1527
s.name = '{0}' and1528
c.name = '{1}'1529
";1530
DbHelper helper = new DbHelper();1531
object o = helper.ExecuteScalar(string.Format(selectCommandText,tableName,fieldName));1532
return o == DBNull.Value || o == null ? "" : o.ToString(); 1533
1534
}1535
#endregion1536
1537
#region View1538
/// summary1539
/// 判断是否存在对应的视图1540
/// /summary1541
/// param name="viewName"视图名/param1542
/// returns存在返回true,不存在返回fasle/returns1543
public override bool ExistView(string viewName)1544
{1545
string selectCommandText = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '{0}'", viewName);1546
DbHelper helper = new DbHelper();1547
return helper.ExecuteScalar(selectCommandText) != null; 1548
} 1549
1550
/// summary1551
/// 获得数据库中所有的用户视图1552
/// /summary1553
/// returns以DataTable返回视图的名称/returns1554
public override DataTable GetViewNames()1555
{1556
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsView') = 1 order by o.name";1557
DbHelper helper = new DbHelper();1558
return helper.ExecuteDataTable(selectCommandText);1559
} 1560
1561
/// summary1562
/// 获得指定视图的内容(如果已经加密，则返回空)1563
/// /summary1564
/// param name="viewName"视图名/param1565
/// returns/returns1566
public override string GetViewText(string viewName)1567
{1568
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+"]')";1569
DbHelper helper = new DbHelper();1570
object o = helper.ExecuteScalar(selectCommandText);1571
return o == DBNull.Value || o == null ? "" : o.ToString();1572
}1573
1574
/// summary1575
/// 删除数据库视图1576
/// /summary1577
/// param name="viewName"视图名/param1578
public override void DropView(string viewName)1579
{1580
DbHelper helper = new DbHelper();1581
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+"]");1582
}1583
1584
/// summary1585
/// 创建数据库视图1586
/// /summary1587
/// param name="viewName"视图名/param1588
/// param name="viewText"视图内容/param1589
public override void CreateView(string viewName, string viewText)1590
{1591
if(viewText != null && viewText.Trim() != "")1592
{1593
DropView(viewName);1594
DbHelper helper = new DbHelper();1595
helper.ExecuteNonQuery(viewText);1596
}1597
}1598
#endregion1599
1600
#region Procedure1601
/// summary1602
/// 获得数据库中所有的用户存储过程1603
/// /summary1604
/// returns以DataTable返回存储过程的名称/returns1605
public override DataTable GetProcedureNames()1606
{1607
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsProcedure') = 1 order by o.name";1608
DbHelper helper = new DbHelper();1609
return helper.ExecuteDataTable(selectCommandText);1610
}1611
1612
/// summary1613
/// 获得指定存储过程的内容1614
/// /summary1615
/// param name="procedureName"存储过程名/param1616
/// returns/returns1617
public override string GetProcedureText(string procedureName)1618
{1619
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+"]')";1620
DbHelper helper = new DbHelper();1621
object o = helper.ExecuteScalar(selectCommandText);1622
return o == DBNull.Value || o == null ? "" : o.ToString();1623
}1624
1625
/// summary1626
/// 删除数据库存储过程1627
/// /summary1628
/// param name="functionName"存储过程名/param1629
public override void DropProcedure(string procedureName)1630
{1631
DbHelper helper = new DbHelper();1632
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+"]");1633
}1634
1635
1636
/// summary1637
/// 创建数据库存储过程1638
/// /summary1639
/// param name="procedureName"存储过程名/param1640
/// param name="procedureText"存储过程内容/param1641
public override void CreateProcedure(string procedureName, string procedureText)1642
{1643
1644
if(procedureText != null && procedureText.Trim() != "")1645
{1646
DropProcedure(procedureName);1647
DbHelper helper = new DbHelper();1648
helper.ExecuteNonQuery(procedureText);1649
}1650
}1651
1652
#endregion1653
1654
#region Function1655
/// summary1656
/// 获得数据库中所有的用户函数1657
/// /summary1658
/// returns以DataTable返回函数的名称/returns1659
public override DataTable GetFunctionNames()1660
{1661
string selectCommandText = "select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsScalarFunction') = 1 order by o.name";1662
DbHelper helper = new DbHelper();1663
return helper.ExecuteDataTable(selectCommandText);1664
}1665
1666
/// summary1667
/// 获得指定函数的内容1668
/// /summary1669
/// param name="functionName"函数名/param1670
/// returns/returns1671
public override string GetFunctionText(string functionName)1672
{1673
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+"]')";1674
DbHelper helper = new DbHelper();1675
object o = helper.ExecuteScalar(selectCommandText);1676
return o == DBNull.Value || o == null ? "" : o.ToString();1677
}1678
1679
/// summary1680
/// 删除数据库函数1681
/// /summary1682
/// param name="functionName"函数名/param1683
public override void DropFunction(string functionName)1684
{1685
DbHelper helper = new DbHelper();1686
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+"]");1687
}1688
1689
/// summary1690
/// 创建数据库函数1691
/// /summary1692
/// param name="functionName"函数名/param1693
/// param name="functionText"函数内容/param1694
public override void CreateFunction(string functionName, string functionText)1695
{1696
1697
if(functionText != null && functionText.Trim() != "")1698
{1699
DropFunction(functionName);1700
DbHelper helper = new DbHelper();1701
helper.ExecuteNonQuery(functionText);1702
}1703
}1704
#endregion 1705
1706
#region Trigger1707
/// summary1708
/// 返回数据库中所有的触发器1709
/// /summary1710
/// returns1711
/// 返回的DataTable列1712
/// TriggerName 触发器名称1713
/// TableName 触发器所在表名 1714
/// /returns1715
public override DataTable GetTriggerName()1716
{1717
string selectCommandText = @"1718
select object_name(a.parent_obj) as TableName,a.name as TriggerName 1719
from sysobjects a,sysobjects b1720
where a.parent_obj=b.id and1721
OBJECTPROPERTY(a.id, N'IsTrigger') = 1 1722
order by object_name(a.parent_obj)1723
";1724
DbHelper helper = new DbHelper();1725
return helper.ExecuteDataTable(selectCommandText);1726
}1727
1728
/// summary1729
/// 删除触发器1730
/// /summary1731
/// param name="triggerName"触发器名称/param1732
public override void DropTrigger(string triggerName)1733
{1734
DbHelper helper = new DbHelper();1735
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+"]");1736
}1737
1738
/// summary1739
/// 创建触发器1740
/// /summary1741
/// param name="triggerName"触发器名称/param1742
/// param name="triggerText"触发器内容/param1743
public override void CreateTrigger(string triggerName,string triggerText)1744
{1745
if(triggerText != null && triggerText.Trim() != "")1746
{1747
DbHelper helper = new DbHelper();1748
DropTrigger(triggerName);1749
helper.ExecuteNonQuery(triggerText);1750
}1751
}1752
1753
/// summary1754
/// Enable触发器1755
/// /summary1756
/// param name="triggerName"触发器名称/param1757
/// param name="tableName"触发器所在表名/param1758
public override void EnableTrigger(string triggerName,string tableName)1759
{1760
string commandText = string.Format("alter table {0} enable trigger {1}",tableName,triggerName); 1761
DbHelper helper = new DbHelper();1762
helper.ExecuteNonQuery(commandText);1763
}1764
1765
/// summary1766
/// Disable触发器1767
/// /summary1768
/// param name="triggerName"触发器名称/param1769
/// param name="tableName"触发器所在表名/param1770
public override void DisableTrigger(string triggerName,string tableName)1771
{1772
string commandText = string.Format("alter table {0} disable trigger {1}",tableName,triggerName); 1773
DbHelper helper = new DbHelper();1774
helper.ExecuteNonQuery(commandText);1775
}1776
1777
#endregion1778
1779
}1780
1781
}1782
1783
