.net+mssql制作抽奖程序思路及源码
抽奖程序：
思路整理，无非就是点一个按钮，然后一个图片旋转一会就出来个结果就行了，可这个程序的要求不是这样的，是需要从数据库中随机抽取用户，根据数据库中指定的等级和人数，一键全部抽出来结果就行了。同时需要存储到数据库。还需要一个导出的功能。
不能遗漏的是，如果通过随机数根据id来抽取的话，需要考虑id不连续的问题，如果全部取出id也不现实。尽量少的去读写数据库。
数据库：
create table [dbo].[users](
[id] [int] identity(1,1) not null,
[name] [nvarchar](50) not null,
[phone] [nvarchar](50) null,
constraint [pk_table1] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
create table [dbo].[result](
[id] [int] identity(1,1) not null,
[usersid] [int] not null,
[awardsid] [int] not null,
constraint [pk_result] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
create table [dbo].[awards](
[id] [int] identity(1,1) not null,
[name] [nvarchar](50) not null,
[number] [int] not null,
constraint [pk_awards] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
create view [dbo].[view1]
as
select dbo.result.id as resultid, dbo.users.id, dbo.users.name, dbo.users.phone, dbo.awards.name as awardname
from dbo.awards inner join
dbo.result on dbo.awards.id = dbo.result.awardsid inner join
dbo.users on dbo.result.usersid = dbo.users.id
create procedure [dbo].[getranddata]
--这个地方的参数是后台调用传的参数，两个变量之间需要“,”号分开
@count int, --剩余奖项大小
@awards int --奖项的id
as begin
--这个地方定义的参数是存储过程内部用到的
declare @minid int --最大id
declare @maxid int --最小id
declare @randnum int --随机数临时变量
declare @exist int --查询结果
set @minid =
(select top 1 id
from users
order by id asc) --查询最小id
set @maxid =
(select top 1 id
from users
order by id desc) --查询最大id
--set @count = 100
--set @awards = 1
--嵌套语句begin开始，end结束
while @count>0 begin
select @randnum = round(((@maxid - @minid -1) * rand() + @minid), 0)
set @exist =
(select count(*)
from users
where id=@randnum) if @exist = 1 begin
insert into result(usersid,awardsid)
values(@randnum,
@awards)
set @count = @count - 1 end end end
其中三张表，一个视图，一个存储过程。
后台代码：
protected void button1_click(object sender, eventargs e)
{
sqlconnection sqlcon = new sqlconnection("server=.;database=test;uid=sa;pwd=123");
sqlcon.open();
sqldataadapter sqlsda = new sqldataadapter("select * from awards", sqlcon);
ds = new dataset();
sqlsda.fill(ds);
datatable dt = ds.tables[0].copy();
ds.clear();
int count = dt.rows.count;
for (int i = 0; i < count; i++)
{
sqlcommand sqlcmd = new sqlcommand("getranddata", sqlcon);
sqlparameter pcount = new sqlparameter("@count", convert.toint32(dt.rows[i]["number"]));
sqlparameter pawards = new sqlparameter("@awards", convert.toint32(dt.rows[i]["id"]));
sqlcmd.parameters.add(pcount);
sqlcmd.parameters.add(pawards);
sqlcmd.commandtype = commandtype.storedprocedure;
sqlcmd.executenonquery();
sqlsda = new sqldataadapter("select top " + convert.toint32(dt.rows[i]["number"]) + " * from view1 order by resultid desc", sqlcon);
sqlsda.fill(ds, "t" + i.tostring());
switch (i)
{
case 0:
gridview1.datasource = ds.tables["t" + i.tostring()].copy().defaultview;
gridview1.databind();
break;
case 1:
gridview2.datasource = ds.tables["t" + i.tostring()].copy().defaultview;
gridview2.databind();
break;
case 2:
gridview3.datasource = ds.tables["t" + i.tostring()].copy().defaultview;
gridview3.databind();
break;
default:
break;
}
}
sqlcon.close();
}
奖项设置：
抽奖结果：
=================================================================
知识点：
sql - 生成指定范围内的随机数
declare @result int declare @upper int declare @lower int
set @lower = 1
set @upper = 10
select @result = round(((@upper - @lower -1) * rand() + @lower), 0)
select @result
round()函数：返回按指定位数进行四舍五入的数值。
rand()函数：生成随机数。
sql循环语句嵌套
declare @i int
set @i=1 while @i<8 begin if @i<5 print space(4-@i)+replicate('*',2*@i-1) else print space(@i-4)+replicate('*',15-2*@i)
set @i=@i + 1 end