sql数据库事务/触发器/存储过程/视图/表值函数/标量值函数/系统函数案例片段

分类：数据库| 发布：camnprbubuol| 查看：476 | 发表时间：2011/1/24

01--------数据库事务
02 
03/// <summary> 
04        /// 执行多条SQL语句，实现数据库事务。 
05        /// </summary> 
06        /// <param name="SQLStringList">多条SQL语句        public static int ExecuteSqlTran(List<string> SQLStringList) 
07        { 
08            using (SqlConnection conn = new SqlConnection(connectionString)) 
09            { 
10                conn.Open(); 
11                SqlCommand cmd = new SqlCommand(); 
12                cmd.Connection = conn; 
13                SqlTransaction tx = conn.BeginTransaction(); 
14                cmd.Transaction = tx; 
15                try
16                { 
17                    int count = 0; 
18                    for (int n = 0; n < SQLStringList.Count; n++) 
19                    { 
20                        string strsql = SQLStringList[n]; 
21                        if (strsql.Trim().Length > 1) 
22                        { 
23                            cmd.CommandText = strsql; 
24                            count += cmd.ExecuteNonQuery(); 
25                        } 
26                    } 
27                    tx.Commit(); 
28                    return count; 
29                } 
30                catch
31                { 
32                    tx.Rollback(); 
33                    return 0; 
34                } 
35            } 
36        }
37</string>

01--------------触发器
02 
03set ANSI_NULLS ON
04set QUOTED_IDENTIFIER ON
05GO 
06ALTER trigger [dbo].[Trig_Insert_Camnpr] on [dbo].[U_RegisterT] after insert  ---delete 
07as
08begin
09    declare @UserID int
10    select @UserID = inserted.ID from inserted  --deleted 
11    if not exists(select I_UserID from U_Camnpr where I_UserID = @UserID) 
12        begin
13            insert into U_Camnpr(I_UserID)values(@UserID) 
14        end
15end

01-----------存储过程
02 
03/// <summary> 
04        /// 执行存储过程，不返回 
05        /// </summary> 
06        /// <param name="storedProcName">存储过程名        /// <param name="parameters">存储过程参数        public static void RunProcedureSimple(string storedProcName, IDataParameter[] parameters) 
07        { 
08            SqlConnection connection = new SqlConnection(connectionString); 
09            connection.Open(); 
10            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 
11            command.CommandType = CommandType.StoredProcedure; 
12            command.ExecuteNonQuery(); 
13        }
14 
15  
16 
17/// <summary> 
18        /// 执行存储过程，返回影响的行数        
19        /// </summary> 
20        /// <param name="storedProcName">存储过程名        /// <param name="parameters">存储过程参数        /// <param name="rowsAffected">影响的行数        /// <returns></returns> 
21        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) 
22        { 
23            using (SqlConnection connection = new SqlConnection(connectionString)) 
24            { 
25                int result; 
26                connection.Open(); 
27                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); 
28                rowsAffected = command.ExecuteNonQuery(); 
29                result = (int)command.Parameters["ReturnValue"].Value; 
30                //Connection.Close(); 
31                return result; 
32            } 
33        }

01----------视图
02 
03SELECT     dbo.CAMNPR_User_Video.ID, dbo.CAMNPR_User_Video.UserID, dbo.CAMNPR_User_Video.Title, dbo.CAMNPR_User_Video.Url, 
04                      dbo.CAMNPR_User_Video.AlbumID, dbo.CAMNPR_User_Video.IsCover, dbo.CAMNPR_User_Video.CreateTime, dbo.CAMNPR_User_Video.Hits, 
05                      dbo.CAMNPR_User_Video.Remark, dbo.CAMNPR_User_Video_Album.Competence, dbo.CAMNPR_User_Video_Album.AlbumName, 
06                      dbo.CAMNPR_User_Video_Album.LookQuestion, ’_cookie’ + CONVERT(varchar, dbo.CAMNPR_User_Video.AlbumID) AS cookie, 
07                          (SELECT     COUNT(1) AS Expr1 
08                            FROM          dbo.CAMNPR_User_Video_CommentParent 
09                            WHERE      (dbo.CAMNPR_User_Video.ID = PhotoID)) AS CommentCount 
10FROM         dbo.CAMNPR_User_Video INNER JOIN
11                      dbo.CAMNPR_User_Video_Album ON dbo.CAMNPR_User_Video.AlbumID = dbo.CAMNPR_User_Video_Album.ID

01--------------表值函数
02 
03set ANSI_NULLS ON
04set QUOTED_IDENTIFIER ON
05GO 
06ALTER function [dbo].[Split] 
07( 
08    @origStr varchar(8000),            --待拆分的字符串-- 
09    @markStr varchar(100)            --拆分标记，如’,’-- 
10) 
11returns @splittable table
12( 
13    id varchar(4000) not null,        --编号ID 
14    item varchar(2000) not null        --拆分后的字符串 
15) 
16as
17begin
18    declare @strlen int, @postion int, @start int, @sublen int, @TEMPstr varchar(200), @TEMPid int
19    select @strlen = len(@origStr), @start=1, @sublen=0, @postion=1, @TEMPstr=’’, @TEMPid=0 
20    if(right(@origStr,1) <> @markStr) 
21        begin
22            set @origStr = @origStr + @markStr 
23        end
24        while((@postion <= @strlen) and (@postion != 0)) 
25            begin
26                if(charindex(@markStr, @origStr, @postion) != 0) 
27                    begin
28                        set @sublen = charindex(@markStr, @origStr, @postion)-@postion; 
29                    end
30                else
31                    begin
32                        set @sublen = @strlen-@postion+1; 
33                    end
34                if(@postion <= @strlen) 
35                    begin
36                        set @TEMPid = @TEMPid+1; 
37                        set @TEMPstr = substring(@origStr,@postion,@sublen); 
38                        insert into @splittable(id, item)values(@TEMPid, @TEMPstr) 
39                        if(charindex(@markStr, @origStr, @postion) != 0) 
40                            begin
41                                set @postion = charindex(@markStr, @origStr, @postion) + 1 
42                            end
43                        else
44                            begin
45                                set @postion = @postion + 1 
46                            end
47                        end
48                    end
49                return
50            end

01---------------标量值函数
02 
03实例1
04 
05set ANSI_NULLS ON
06set QUOTED_IDENTIFIER ON
07GO 
08ALTER function [dbo].[GetChildArea](@MyPid varchar(50)) 
09returns nvarchar(500) 
10as
11begin
12    declare @MyPid_Sub varchar(50), @StrDealerCursor varchar(5000), @Treemap varchar(500) 
13    set @Treemap = ’’ 
14    begin
15        declare Dealer_Cursor1 cursor for select I_Name from U_Area where I_RootID = (select ID from U_Area where I_Name=convert(nvarchar(50),@MyPid)) 
16        open Dealer_Cursor1 
17 
18        fetch from Dealer_Cursor1 Into @MyPid_Sub 
19        while @@fetch_status = 0 
20        begin
21            set @Treemap=@Treemap+@MyPid_Sub + ’,’ 
22 
23            fetch NEXT from Dealer_Cursor1 into @MyPid_Sub 
24        end
25 
26        close Dealer_Cursor1 
27        deallocate Dealer_Cursor1 
28    end
29--    if len(@Treemap)>0 
30--        set @Treemap=substring(@Treemap,1,len(@Treemap)-1) 
31    set  @Treemap=@Treemap+@MyPid+’,’ 
32    return @Treemap 
33end

01实例2
02 
03set ANSI_NULLS ON
04set QUOTED_IDENTIFIER ON
05GO 
06ALTER FUNCTION [dbo].[GetRowsNameBool](@SourceSql nvarchar(500),@StrSeprate nvarchar(10),@SubstringStr nvarchar(500))    --字符串格式41,56,77,  
07RETURNS bit 
08AS 
09begin 
10    declare @indexof int 
11    declare @returnBit bit
12    declare @i int 
13    set @indexof=0 
14    set @returnBit=0 
15    set @SourceSql=rtrim(ltrim(@SourceSql))  
16    set @i=charindex(@StrSeprate,@SourceSql)  
17    while @i>=1  
18    begin         
19        set @indexof=CharIndex(left(@SourceSql,@i-1),@SubstringStr) 
20        if @indexof>0 
21        begin
22            set @returnBit=1 
23            break 
24        end
25        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)  --已经转换好的去掉  
26        set @i=charindex(@StrSeprate,@SourceSql)       --重新定位  
27    end     
28    --return @names  
29    if @SourceSql<>’’and @i>=1   --最后一个  
30    begin 
31         set @indexof=CharIndex(left(@SourceSql,@i-1),@SubstringStr) 
32    end
33    if @indexof>0 
34    begin
35        set @returnBit=1 
36    end
37    return @returnBit  
38end 
39 
40--select I_WorkArea,dbo.GetChildArea(’河南省’),dbo.GetRowsNameBool(dbo.GetChildArea(’河南省’),’,’,I_WorkArea) as ddd 
41--from U_Camnpr

01实例3
02 
03set ANSI_NULLS ON
04set QUOTED_IDENTIFIER ON
05GO 
06ALTER FUNCTION [dbo].[GetRowsNameStr](@SourceSql nvarchar(500),@StrSeprate nvarchar(10))    --字符串格式41,56,77,  
07RETURNS nvarchar(500)  
08AS 
09begin 
10    declare @names nvarchar(500)  
11    declare @i int 
12    set @names=’’ 
13    set @SourceSql=rtrim(ltrim(@SourceSql))  
14    set @i=charindex(@StrSeprate,@SourceSql)  
15    while @i>=1  
16    begin         
17        set @names=@names+(select I_Name from U_Post where ID=convert(int,left(@SourceSql,@i-1)))+’,’  
18        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)  --已经转换好的去掉  
19        set @i=charindex(@StrSeprate,@SourceSql)       --重新定位  
20    end     
21    --return @names  
22    if @SourceSql<>’’and @i>=1   --最后一个  
23    begin 
24         set @names=@names+(select I_Name from U_Post where ID=convert(int,left(@SourceSql,@i-1)))  
25    end 
26  if(right(@names,1)=’,’) --去掉后面的逗号  
27        set @names=substring(@names,1,len(@names)-1)   
28    return @names  
29end 
30 
31--select I_Name,dbo.GetRowsNameStr(’1,2,3,4,5’,’,’) as ddd from U_Post where ID<3 