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

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






 --------数据库事务

///
/// 执行多条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 void RunProcedureSimple(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}



///
/// 执行存储过程,返回影响的行数
///

/// 存储过程名 /// 存储过程参数 /// 影响的行数 ///
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

365据说看到好文章不转的人,服务器容易宕机
原创文章如转载,请注明:转载自郑州网建-前端开发 http://camnpr.com/
本文链接:http://camnpr.com/archives/188.html