函数过程如下:
CREATE function xp_walkradio_article
(@username nvarchar(20))
returns table
return
(
select program_info.p_title,walkradio.w_domain_name,walkradio.p_id,walkradio.wid
from walkradio,program_info
where program_info.p_big_class=1 and program_info.program_id=walkradio.p_id and walkradio.w_username=@username
)
调用如下:
//挑选四个收藏(音乐收藏)
private void select_walkradio_yinyue(string str_parameter)
{
visitedabe MyVisiteDabe=new visitedabe();
MyVisiteDabe.procedurename="select * from wotian_things.dbo.xp_walkradio_sound('"+str_parameter+"')";
MyVisiteDabe.mydataadapter=new SqlDataAdapter(MyVisiteDabe.procedurename,MyVisiteDabe.myConnection);
MyVisiteDabe.mydataadapter.SelectCommand.CommandType=CommandType.Text;
MyVisiteDabe.OpenBase();
MyVisiteDabe.mydataset=new DataSet();
MyVisiteDabe.mydataadapter.Fill(MyVisiteDabe.mydataset,"yinyue");
datalist_yinyue.DataSource=MyVisiteDabe.mydataset.Tables["yinyue"].DefaultView;
datalist_yinyue.DataBind();
MyVisiteDabe.CloseBase();
}
第一次节目设计到函数和存贮过程
存贮过程:
CREATE PROCEDURE xp_add_walkradio_programe
@str_username nvarchar(20),
@p_id int,
@result tinyint output
as
SET XACT_ABORT ON
begin transaction
--挑选域名
declare @str_domain_name nvarchar(100)
select @str_domain_name=u_domain_name from program_info where program_id=@p_id
--添加收藏
insert into walkradio(w_username,w_domain_name,p_id)
values(@str_username,@str_domain_name,@p_id)
--更新节目听众
update user_radio_info set r_listen_count=r_listen_count+1,r_see_count=r_see_count+1
where r_domain_name=@str_domain_name
--更新节目浏览人数
update program_info set p_see_count=p_see_count+1 where program_id=@p_id
if(@@error=0)
begin
commit transaction
set @result=1
end
else
begin
rollback transaction
set @result=0
end
SET XACT_ABORT OFF
GO
函数:
CREATE function xp_check_programe_walkradio
(@username varchar(20),@int_pid int)
returns tinyint
begin
declare @int_return tinyint
if (exists(select p_id from walkradio where w_username=@username and p_id=@int_pid))
set @int_return=1
else
set @int_return=0
return @int_return
end
调用函数的过程:
private bool xp_check_programe_walkradio_1(string str_parameter,int int_parameter)
{
visitedabe MyVisiteDabe=new visitedabe();
MyVisiteDabe.procedurename="select wotian_things.dbo.xp_check_programe_walkradio('"+str_parameter+"',"+int_parameter+") as 'return_check'";
MyVisiteDabe.mysqlcommand.CommandType = CommandType.Text;
MyVisiteDabe.mysqlcommand.CommandText=MyVisiteDabe.procedurename;
MyVisiteDabe.OpenBase();
int int_return1=Convert.ToInt32(MyVisiteDabe.mysqlcommand.ExecuteScalar());
MyVisiteDabe.CloseBase();
if(int_return1==1)
{
return false;
}
else
{
return true;
}
}
调用存贮过程:
private void Button1_Click(object sender, System.EventArgs e)
{
if(xp_check_programe_walkradio_1(str_name,int_pid)==false)
{
Page.RegisterStartupScript("js","<script>alert('添加失败 你已经收藏了这个节目!')</script>");
Response.Write("<script>setTimeout('window.close()',100);</script>");
return;
}
visitedabe MyVisiteDabe=new visitedabe();
MyVisiteDabe.procedurename="xp_add_walkradio_programe";
MyVisiteDabe.mysqlparameter=MyVisiteDabe.mysqlcommand.Parameters.Add("@str_username", SqlDbType.NVarChar,20);
MyVisiteDabe.mysqlparameter.Direction=ParameterDirection.Input;
MyVisiteDabe.mysqlcommand.Parameters["@str_username"].value="/str_name;
MyVisiteDabe.mysqlparameter=MyVisiteDabe.mysqlcommand.Parameters.Add(""@p_id", SqlDbType.Int);
MyVisiteDabe.mysqlparameter.Direction=ParameterDirection.Input;
MyVisiteDabe.mysqlcommand.Parameters["@p_id"].value="/int_pid;
MyVisiteDabe.mysqlparameter=MyVisiteDabe.mysqlcommand.Parameters.Add(""@result", SqlDbType.TinyInt);
MyVisiteDabe.mysqlparameter.Direction=ParameterDirection.Output;
MyVisiteDabe.OpenBase();
MyVisiteDabe.mysqlcommand.CommandType = CommandType.StoredProcedure;
MyVisiteDabe.mysqlcommand.CommandText=MyVisiteDabe.procedurename;
MyVisiteDabe.mysqlcommand.ExecuteNonQuery();
MyVisiteDabe.CloseBase();
int int_return=Convert.ToInt16(MyVisiteDabe.mysqlcommand.Parameters["@result"].Value);
if(int_return==1)
{
Page.RegisterStartupScript("js","<script>alert('添加成功,请继续添加其他!')</script>");
Response.Write("<script>setTimeout('window.close()',100);</script>");
}
else
{
Page.RegisterStartupScript("js","<script>alert('添加失败 请确认你的权限和操作!')</script>");
}
}