思路整理，無(wú)非就是點(diǎn)一個(gè)按鈕，然后一個(gè)圖片旋轉(zhuǎn)一會(huì)就出來(lái)個(gè)結(jié)果就行了，可這個(gè)程序的要求不是這樣的，是需要從數(shù)據(jù)庫(kù)中隨機(jī)抽取用戶，根據(jù)數(shù)據(jù)庫(kù)中指定的等級(jí)和人數(shù)，一鍵全部抽出來(lái)結(jié)果就行了。同時(shí)需要存儲(chǔ)到數(shù)據(jù)庫(kù)。還需要一個(gè)導(dǎo)出的功能。
不能遺漏的是，如果通過(guò)隨機(jī)數(shù)根據(jù)id來(lái)抽取的話，需要考慮id不連續(xù)的問(wèn)題，如果全部取出id也不現(xiàn)實(shí)。盡量少的去讀寫(xiě)數(shù)據(jù)庫(kù)。
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]
--這個(gè)地方的參數(shù)是后臺(tái)調(diào)用傳的參數(shù)，兩個(gè)變量之間需要“,”號(hào)分開(kāi)
@count int, --剩余獎(jiǎng)項(xiàng)大小
@awards int --獎(jiǎng)項(xiàng)的id
AS BEGIN
--這個(gè)地方定義的參數(shù)是存儲(chǔ)過(guò)程內(nèi)部用到的
DECLARE @minid int --最大id
DECLARE @maxid int --最小id
DECLARE @randnum int --隨機(jī)數(shù)臨時(shí)變量
DECLARE @exist int --查詢結(jié)果
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
--嵌套語(yǔ)句begin開(kāi)始，end結(jié)束
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();
}
