. Net + MSSQL production lottery program ideas and source code

Time:2021-11-23

Lottery procedure:

Sorting out ideas is nothing more than clicking a button, and then rotating a picture for a while to get a result. However, the requirements of this program are not like this. Users need to be randomly selected from the database, and all the results can be extracted with one key according to the level and number of people specified in the database. At the same time, it needs to be stored in the database. You also need an export function.

What cannot be omitted is that if the ID is extracted by random numbers, the problem of ID discontinuity needs to be considered. If all IDs are extracted, it is not realistic. Read and write the database as little 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 parameter in this place is the parameter passed by the background call, and the “,” sign is required to separate the two variables
@Count int, — remaining award size
@Awards int — ID of the award
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) — Query minimum ID
SET @maxid =
  (SELECT top 1 id
   FROM users
   Order by ID DESC) — Query maximum ID
–set @count = 100
–set @awards = 1
–The nested statement begins with begin and ends with end
while @count>0 BEGIN
SELECT @randnum = ROUND(((@maxid – @minid -1) * RAND() + @minid), 0)
SET @exist =
  (SELECT count(*)
   FROM users
   WHERE [email protected]) IF @exist = 1 BEGIN
INSERT 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:

Copy codeThe 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; 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();
}

Award setting:

. Net + MSSQL production lottery program ideas and source code

Draw results:

. Net + MSSQL production lottery program ideas and source code

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

Knowledge points:

SQL – generates a random number within the 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 a numeric value rounded to the specified number of digits.

Rand() function: generates a random number.

SQL circular statement nesting

Copy codeThe code is as follows:
DECLARE @i int
SET @i=1 while @i<8 BEGIN IF @i<5 print space([email protected])+REPLICATE(‘*’,2*@i-1) ELSE print space(@i-4)+REPLICATE(‘*’,15-2*@i)
SET @[email protected] + 1 END