. Net + mssql ideas and source code for creating the lottery Program

Lottery program:

You just need to click a button and rotate an image for a while to get a result. But this is not the requirement of this program. You need to randomly select users from the database, based on the specified level and number of people in the database, you can extract all the results in one click. It also needs to be stored in the database. You also need an export function.

It cannot be omitted that, if a random number is extracted based on the id, the id is not consecutive and it is unrealistic to retrieve all the IDS. Read and Write the database as few as possible.

Database:

Copy codeThe Code is as follows:
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]
-- The parameters in this region are the parameters passed by the background call. The two variables must be separated by ",".
@ Count int, -- remaining awards
@ Awards int -- award id
AS BEGIN
-- The parameters defined here are used inside the stored procedure.
DECLARE @ minid int -- maximum id
DECLARE @ maxid int -- Minimum id
DECLARE @ randnum int -- random number Temporary Variable
DECLARE @ exist int -- Query Result

SET @ minid =
(SELECT top 1 id
FROM users
Order by id ASC) -- Minimum query id
SET @ maxid =
(SELECT top 1 id
FROM users
Order by id DESC) -- query the maximum id
-- Set @ count = 100
-- Set @ awards = 1
-- Nested statement begin starts and end ends
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

There are three tables, one view and one stored procedure.

Background code:

Copy codeThe Code is as follows:
Protected void button#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 ();
}

Awards:

Lottery results:

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

Knowledge point:

SQL-generate random numbers within a specified range

Copy codeThe Code is as follows:
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 () function: returns the number rounded to the specified number of digits.

RAND () function: generate a random number.

SQL loop statement nesting

Copy codeThe Code is as follows:
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