每天進步一點點_抽獎程序

抽獎程序：

思路整理，無非就是點一個按鈕，然后一個圖片旋轉一會就出來個結果就行了，可這個程序的要求不是這樣的，是需要從數據庫中隨機抽取用戶，根據數據庫中指定的等級和人數，一鍵全部抽出來結果就行了。同時需要存儲到數據庫。還需要一個導出的功能。

不能遺漏的是，如果通過隨機數根據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]ASSELECT     dbo.result.id AS resultid, dbo.users.id, dbo.users.name, dbo.users.phone, dbo.awards.Name AS awardnameFROM         dbo.awards INNER JOIN                      dbo.result ON dbo.awards.id = dbo.result.awardsid INNER JOIN                      dbo.users ON dbo.result.usersid = dbo.users.idCREATE PROCEDURE [dbo].[getranddata] --這個地方的參數是后臺調用傳的參數，兩個變量之間需要“,”號分開@count int, --剩余獎項大小@awards int --獎項的idAS BEGIN --這個地方定義的參數是存儲過程內部用到的DECLARE @minid int --最大idDECLARE @maxid int --最小idDECLARE @randnum int --隨機數臨時變量DECLARE @exist int --查詢結果SET @minid =  (SELECT top 1 id   FROM users   ORDER BY id ASC) --查詢最小idSET @maxid =  (SELECT top 1 id   FROM users   ORDER BY id DESC) --查詢最大id--set @count = 100--set @awards = 1--嵌套語句begin開始，end結束while @count>0 BEGINSELECT @randnum = ROUND(((@maxid - @minid -1) * RAND() + @minid), 0)SET @exist =  (SELECT count(*)   FROM users   WHERE id=@randnum) IF @exist = 1 BEGININSERT INTO result(usersid,awardsid)VALUES(@randnum,       @awards)SET @count = @count - 1 END END END

其中三張表，一個視圖，一個存儲過程。

標題鏈接：http://www.49028c.com/zxlovenet/p/3713536.html

=====================================================================================

后臺代碼：

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 INTSET @Lower = 1SET @Upper = 10SELECT @Result = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)SELECT @Result

ROUND()函數：返回按指定位數進行四舍五入的數值。

RAND()函數：生成隨機數。

SQL循環語句嵌套

DECLARE @i intSET @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