Shulou(Shulou.com)05/31 Report--

This issue of the content of the editor will bring you about. Net + mssql production lottery program ideas and how to write the source code, the article is rich in content and professional analysis and description for you, after reading this article, I hope you can get something.

Lucky draw procedure:

The way of thinking is nothing more than clicking a button, and then a picture will be rotated for a while to get a result, but the requirement of this program is not like this. Users need to be randomly selected from the database, according to the specified level and number of people in the database. One click to extract all the results on the line. At the same time, it needs to be stored in the database. You also need an export function.

What can't be omitted is that if you use random numbers to extract according to id, you need to consider the problem of id discontinuity, and it's not realistic to take out all the id. Read and write the database as little as possible.

Database:

The code is as follows: CREATE TABLE [dbo]. [users] ([id] [int] IDENTITY (1Magne1) 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 (1Power1) 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 (1Power1) 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.id

CREATE PROCEDURE [dbo]. [getranddata]-- the parameter in this place is the parameter passed by the background call, and a separate sign is required between the two variables @ count int,-- the size of the remaining prize @ awards int-- the idAS BEGIN of the award-- the parameter defined in this place is DECLARE @ minid int used inside the stored procedure-- maximum idDECLARE @ maxid int-- minimum idDECLARE @ randnum int-- random number temporary variable DECLARE @ exist int-- query result.

SET @ minid = (SELECT top 1 id FROM users ORDER BY id ASC)-- query minimum idSET @ maxid = (SELECT top 1 id FROM users ORDER BY id DESC)-- query maximum id--set @ count = 100--set @ awards = 1 Muir-start of nested statement begin End ends 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

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

Background code:

The code is as follows: 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; iTunes +) {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 ();}

Award setting:

Lucky draw result:

=

Knowledge points:

SQL-generate random numbers within a specified range

The code is as follows: DECLARE @ Result INT DECLARE @ Upper INT DECLARE @ Lower INTSET @ Lower = 1SET @ Upper = 10SELECT @ Result = ROUND ((@ Upper-@ Lower-1) * RAND () + @ Lower), 0) SELECT @ Result

The ROUND () function returns a value that is rounded by the specified number of digits.

The RAND () function generates a random number.

Nesting of SQL loop statements

The code is as follows: DECLARE @ I intSET @ iTun1 while @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.