程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> .net+mssql制作抽獎程序思路及源碼

.net+mssql制作抽獎程序思路及源碼

編輯:關於SqlServer

抽獎程序:

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

不能遺漏的是,如果通過隨機數根據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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved