/// /// 执行多条SQL语句,实现数据库事务。 /// /// 多条SQL语句 public static int ExecuteSqlTran(List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } }
--------------触发器
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER trigger [dbo].[Trig_Insert_Camnpr] on [dbo].[U_RegisterT] after insert ---delete as begin declare @UserID int select @UserID = inserted.ID from inserted --deleted if not exists(select I_UserID from U_Camnpr where I_UserID = @UserID) begin insert into U_Camnpr(I_UserID)values(@UserID) end end
/// /// 执行存储过程,返回影响的行数 /// /// 存储过程名 /// 存储过程参数 /// 影响的行数 /// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } }
----------视图
SELECT dbo.CAMNPR_User_Video.ID, dbo.CAMNPR_User_Video.UserID, dbo.CAMNPR_User_Video.Title, dbo.CAMNPR_User_Video.Url, dbo.CAMNPR_User_Video.AlbumID, dbo.CAMNPR_User_Video.IsCover, dbo.CAMNPR_User_Video.CreateTime, dbo.CAMNPR_User_Video.Hits, dbo.CAMNPR_User_Video.Remark, dbo.CAMNPR_User_Video_Album.Competence, dbo.CAMNPR_User_Video_Album.AlbumName, dbo.CAMNPR_User_Video_Album.LookQuestion, ’_cookie’ + CONVERT(varchar, dbo.CAMNPR_User_Video.AlbumID) AS cookie, (SELECT COUNT(1) AS Expr1 FROM dbo.CAMNPR_User_Video_CommentParent WHERE (dbo.CAMNPR_User_Video.ID = PhotoID)) AS CommentCount FROM dbo.CAMNPR_User_Video INNER JOIN dbo.CAMNPR_User_Video_Album ON dbo.CAMNPR_User_Video.AlbumID = dbo.CAMNPR_User_Video_Album.ID
--------------表值函数
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER function [dbo].[Split] ( @origStr varchar(8000), --待拆分的字符串-- @markStr varchar(100) --拆分标记,如’,’-- ) returns @splittable table ( id varchar(4000) not null, --编号ID item varchar(2000) not null --拆分后的字符串 ) as begin declare @strlen int, @postion int, @start int, @sublen int, @TEMPstr varchar(200), @TEMPid int select @strlen = len(@origStr), @start=1, @sublen=0, @postion=1, @TEMPstr=’’, @TEMPid=0 if(right(@origStr,1) <> @markStr) begin set @origStr = @origStr + @markStr end while((@postion <= @strlen) and (@postion != 0)) begin if(charindex(@markStr, @origStr, @postion) != 0) begin set @sublen = charindex(@markStr, @origStr, @postion)-@postion; end else begin set @sublen = @strlen-@postion+1; end if(@postion <= @strlen) begin set @TEMPid = @TEMPid+1; set @TEMPstr = substring(@origStr,@postion,@sublen); insert into @splittable(id, item)values(@TEMPid, @TEMPstr) if(charindex(@markStr, @origStr, @postion) != 0) begin set @postion = charindex(@markStr, @origStr, @postion) + 1 end else begin set @postion = @postion + 1 end end end return end
---------------标量值函数
实例1
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER function [dbo].[GetChildArea](@MyPid varchar(50)) returns nvarchar(500) as begin declare @MyPid_Sub varchar(50), @StrDealerCursor varchar(5000), @Treemap varchar(500) set @Treemap = ’’ begin 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)) open Dealer_Cursor1
fetch from Dealer_Cursor1 Into @MyPid_Sub while @@fetch_status = 0 begin set @Treemap=@Treemap+@MyPid_Sub + ’,’
fetch NEXT from Dealer_Cursor1 into @MyPid_Sub end
close Dealer_Cursor1 deallocate Dealer_Cursor1 end -- if len(@Treemap)>0 -- set @Treemap=substring(@Treemap,1,len(@Treemap)-1) set @Treemap=@Treemap+@MyPid+’,’ return @Treemap end
实例2
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetRowsNameBool](@SourceSql nvarchar(500),@StrSeprate nvarchar(10),@SubstringStr nvarchar(500)) --字符串格式41,56,77, RETURNS bit AS begin declare @indexof int declare @returnBit bit declare @i int set @indexof=0 set @returnBit=0 set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin set @indexof=CharIndex(left(@SourceSql,@i-1),@SubstringStr) if @indexof>0 begin set @returnBit=1 break end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) --已经转换好的去掉 set @i=charindex(@StrSeprate,@SourceSql) --重新定位 end --return @names if @SourceSql<>’’and @i>=1 --最后一个 begin set @indexof=CharIndex(left(@SourceSql,@i-1),@SubstringStr) end if @indexof>0 begin set @returnBit=1 end return @returnBit end
--select I_WorkArea,dbo.GetChildArea(’河南省’),dbo.GetRowsNameBool(dbo.GetChildArea(’河南省’),’,’,I_WorkArea) as ddd --from U_Camnpr
实例3
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetRowsNameStr](@SourceSql nvarchar(500),@StrSeprate nvarchar(10)) --字符串格式41,56,77, RETURNS nvarchar(500) AS begin declare @names nvarchar(500) declare @i int set @names=’’ set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin set @names=@names+(select I_Name from U_Post where ID=convert(int,left(@SourceSql,@i-1)))+’,’ set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) --已经转换好的去掉 set @i=charindex(@StrSeprate,@SourceSql) --重新定位 end --return @names if @SourceSql<>’’and @i>=1 --最后一个 begin set @names=@names+(select I_Name from U_Post where ID=convert(int,left(@SourceSql,@i-1))) end if(right(@names,1)=’,’) --去掉后面的逗号 set @names=substring(@names,1,len(@names)-1) return @names end
--select I_Name,dbo.GetRowsNameStr(’1,2,3,4,5’,’,’) as ddd from U_Post where ID<3