Net+mssql draw process and source code _mssql

Lottery procedure:

The idea of finishing, nothing more than a button, and then a picture rotation will come out a result on the line, but this program is not the request, is the need to randomly extract users from the database, according to the specified level and number of databases, a key to all the results on the line. It also needs to be stored in the database. An exported feature is also required.

What can not be omitted is that, if the random number based on the ID to be extracted, you need to consider the problem of ID discontinuity, if all the ID is not realistic. Try to read and write the database as little as possible.

Database:

Copy Code code as follows:

CREATE TABLE [dbo]. [Users] (
[ID] [int] IDENTITY (1,1) not NULL,
[Name] [nvarchar] () not NULL,
[Phone] [nvarchar] (m) 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) O N [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 = O N) on [PRIMARY]
) on [PRIMARY]

CREATE TABLE [dbo]. [Awards] (
[ID] [int] IDENTITY (1,1) not NULL,
[Name] [nvarchar] () 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 = O N) 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 of this place is the parameter that the backstage call passes, the two variables need "," number to separate
@count int,--Remaining award size
@awards INT--ID of the award
As BEGIN
--This place defines parameters that are used within a stored procedure.
DECLARE @minid INT--Max ID
DECLARE @maxid INT--min ID
DECLARE @randnum INT--Random number temporary variable
DECLARE @exist INT--Query results

SET @minid =
(SELECT top 1 ID
From users
Order BY ID ASC)--Query min ID
SET @maxid =
(SELECT top 1 ID
From users
Order BY ID DESC)--Query Maximum ID
--set @count = 100
--set @awards = 1
--Nested statement 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

One of the three tables, a view, a stored procedure.

Background code:

Copy Code code 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 settings:

Lottery results:

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

Knowledge Points:

SQL-generates random numbers within a specified range

Copy Code code 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 value that is rounded by a specified number of digits.

RAND () Function: Generates random numbers.

SQL Loop Statement Nesting

Copy Code code as follows:

DECLARE @i int
SET @i=1 while @i<8 BEGIN IF @i<5 print spaces (4-@i) +replicate (' * ', 2*@i-1) ELSE print space (@i-4) +replicate (' * ', 15-2 *@i)
SET @i=@i + 1 End