sql数据库事务/触发器/存储过程/视图/表值函数/标量值函数/系统函数案例片段
--------数据库事务
///
/// 执行多条SQL语句，实现数据库事务。
///
/// 多条SQL语句 public static int ExecuteSqlTran(ListSQLStringList)
{
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
标签：SQL Server
原创文章如转载，请注明：转载自郑州网建-前端开发 http://camnpr.com/本文链接：http://camnpr.com/archives/188.html
相关文章
- 创建视图sql关联产品的三级类别(2011-1-24 10:32:41)
- (Microsoft SQL Server，附加数据库 错误: 916)解决方法(2011-1-24 10:23:50)
- sqlserver2005附加数据库失败，错误9003，解决方法.(2011-1-24 10:13:48)
- 附加数据库失败 日志文件(.ldf)与数据文件(.mdf)不匹配 sql server 错误9003(2011-1-24 10:10:29)
- NoSQL简介(2011-1-17 11:38:51)
- C#调用带参数和不带参数的存储过程实例(2011-1-17 11:16:10)
- SQLServer带输入参数/不带任何参数存储过程的返回值和return说明及ExecuteScal(2011-1-17 11:13:31)
- (switch条件语句中case指定多个值)js switch case 条件相同的列在同一个cas(2011-1-17 11:9:34)
- access表(.mdb文件)导入power designer (Sybase)生成数据模型/物理数(2010-12-29 16:48:7)
- 一对多的几个表，查询主表的信息和主表在子表中的记录条数(2010-12-23 16:43:45)
