Two-dimensional code as activation Code (key) function development

This week began to return to the familiar and unfamiliar unity development, a year ago from unity to COCOS2DX, this year there are many twists and turns, but also a lot of sadness, perhaps because the "heart" is undecided, and perhaps because of other, a kind of retwist feeling, once the arrogance gradually was "scattered", Accustomed to be reprimanded and educated. Face of various kinds of technology, always can not resist temptation, all want to learn again, but also helpless energy limited, coupled with the pressure of work, every day to engage in physical and mental exhaustion, in short this year of their own evaluation with "disappointment" two words to describe! But from this week began to return to unity development, the heart has a hint of joy, perhaps really for it long awaited! Last night watching the game bull on some of the bosses wrote about unity technology sharing of the article, until one o'clock still not sleepy, deeply intoxicated, forced to work the next day pressure to be reluctant to shut down the computer to sleep. Received a request from the boss of the previous company, they use unity to do the application software, there is a requirement: with the QR code as the application activation key, a QR code can activate five devices, when the number of activated devices greater than 5 the key expires. Given that the boss had been able to treat me before, I would have been personally in the mood to help him complete that demand. The client who previously did Unity's sweep code has also documented the relevant article (http://blog.csdn.net/dingxiaowei2013/article/details/25086835). Here is a simple record of the development process.

First, the design of database tables

canbeuse bool value to mark the QR code key is invalid, of course, my next T-SQL write logic is when the key failure to directly delete the key information, so the field does not make much sense, in case of need to use!

Ii. stored Procedure (T-SQL) design

-------------------Verify that the qrcode is free of stored procedures----------------------------------alter PROCEDURE Searchproc@qrcodetext nvarchar, @result bit outputasbegindeclare @count int, @selectrows intSELECT * FROM dbo. QRCODETB where qrcodetext = @qrcodetext; Set @selectrows = @ @ROWCOUNTif @selectrows > 0begin Select @count = Cast (usednu m as int) from dbo. QRCODETB where Qrcodetext = @qrcodetext, if @count < 5 beginset @count = @count + 1update dbo. Qrcodetb Set usednum = @count where Qrcodetext = @qrcodetextset @result = 1endelsebegin--update dbo. Qrcodetb Set canbeuse = 0 where qrcodetext = @qrcodetextdelete from dbo. QRCODETB where qrcodetext = @qrcodetextset @result =0endendelsebeginset @result = 0endendGO-----------------------------Perform validation-------------------------------------declare @result bitexec Searchproc ' ED81D6FF-86A3-49C1-BF40-1A05521803DC ', @result output;select @resultselect * FROM dbo. Qrcodetb--------------------------------Add a record stored procedure--------------------alter PROCEDURE Insertoneinfoproc@num INT,@Count int Outputasbeginwhile @num > 0begininsert into dbo. QRCODETB (Qrcodetext,canbeuse,usednum) VALUES (NEWID (), 1,0) Set @num = @num -1endset @count = @ @ROWCOUNTendgodeclare @ Count intEXEC insertoneinfoproc 1, @count output, select @count, select * FROM dbo. QRCODETB-------------------------Add data--------------------------------------------------INSERT INTO dbo. QRCODETB (Qrcodetext,canbeuse,usednum) VALUES (NEWID (), 1,0)------------------------- Query data------------------------------------------SELECT * FROM dbo. Qrcodetb-------------------------emptying the datasheet--------------------------------------Truncate table dbo. Qrcodetb

Stored Procedure Execution Results:

After running five consecutive times:

Shows that the key is not available. Seems to have never tried to write T-QSL, this is the first time I have tried to write such a long SQL, in the eyes of the great God this is too much of a cake ha!

Web-side design

The web is using a. NET generic handler

1.webconfig Database Information Configuration

2. Simple SqlHelper class

Using system.configuration;using system.data;using System.data.sqlclient;namespace sqlhelper{class SQLHelper { private static readonly string connectstr = configurationmanager.connectionstrings["Sqlconnstr"].        ConnectionString;            public static SqlConnection CreateConnection () {SqlConnection conn = new SqlConnection (CONNECTSTR); Conn.            Open ();        Return conn;            } public static int ExecuteNonQuery (SqlConnection conn, string sql, params sqlparameter[] parameters) { using (SqlCommand cmd = conn.                CreateCommand ()) {cmd.commandtext = SQL; Cmd.                Parameters.addrange (Parameters); return CMD.            ExecuteNonQuery ();  }} public static int ExecuteNonQuery (string sql,params sqlparameter[] parameters) {using            (SqlConnection conn = CreateConnection ()) {using (SqlCommand cmd = conn.       CreateCommand ())         {cmd.commandtext = SQL; Cmd.                    Parameters.addrange (Parameters); return CMD.                ExecuteNonQuery (); }}} public static int ExecuteNonQuery (String sql, CommandType type, params sqlparameter[] param eters) {using (SqlConnection conn = CreateConnection ()) {using (SqlCommand C MD = conn.                    CreateCommand ()) {cmd.commandtext = SQL;                    Cmd.commandtype = type; Cmd.                    Parameters.addrange (Parameters); return CMD.                ExecuteNonQuery (); }}}///<summary>///execute a stored procedure with input/output parameters////</summary>//<re turns></returns> public static string ExecuteNonQuery (string procname, int outputindex, params sqlparamet      er[] Parameters) {using (SqlConnection conn = CreateConnection ())      {using (SqlCommand cmd = conn.                    CreateCommand ()) {cmd.commandtext = procname;                    Cmd.commandtype = CommandType.StoredProcedure; Cmd.                    Parameters.addrange (Parameters); int executeeffectnum = cmd.                    ExecuteNonQuery (); Return Parameters[outputindex]. Value.tostring (); Returns the first output parameter}}} public static Object ExecuteScalar (SqlConnection conn, String sq L, params sqlparameter[] parameters) {using (SqlCommand cmd = conn.                CreateCommand ()) {cmd.commandtext = SQL; Cmd.                Parameters.addrange (Parameters); return CMD.            ExecuteScalar (); }} public static object ExecuteScalar (String sql, params sqlparameter[] parameters) {USI NG (SqlConnection conn = CreateConnection ()) {return executescalar (conn, SQL, parAmeters);        }} public static DataTable ExecuteQuery (SqlConnection conn, string sql, params sqlparameter[] parameters)            {DataTable table = new DataTable (); using (SqlCommand cmd = conn.                CreateCommand ()) {cmd.commandtext = SQL; Cmd.                Parameters.addrange (Parameters); using (SqlDataReader reader = cmd. ExecuteReader ()) {table.                Load (reader);            } return table; }} public static DataTable ExecuteQuery (String sql, params sqlparameter[] parameters) {u            Sing (SqlConnection conn = CreateConnection ()) {return ExecuteQuery (conn, SQL, parameters); }        }    }}

3. Two-dimensional code generation

Using system;using system.collections.generic;using system.linq;using system.web;using System.Web.UI;using System.web.ui.webcontrols;using sqlhelper;using system.data;using system.data.sqlclient;using Thoughtworks.qrcode.codec;using system.io;using system.text;namespace webapplication{public partial class        CreateQRCode:System.Web.UI.Page {protected void Page_Load (object sender, EventArgs e) {} <summary>///Create a two-dimensional code key///</summary>//<param name= "Sender" ></param&gt        ;            <param name= "E" ></param> protected void Craeteqrcodebtn_click (object sender, EventArgs e) {            String path = Server.MapPath ("~/images" + "//qrpngfile");            String name = "QRCode";            Create folder Directory.CreateDirectory (path); String sql = "SELECT top 1 qrcodetext from dbo.            Qrcodetb "; String qrcode = SQLHelper.SQLHelper.ExecuteScalar (sql). ToString ();           if (!string. IsNullOrEmpty (QRCode) &&!createimage (qrcode, path, name)) {Label1.Text = name + "already exists"            ; }}///<summary> Add a QR code record to the database///</summary>//<param name= "send ER "></param>//<param name=" E "></param> protected void Createuidbtn_click (object send            Er, EventArgs e) {int num = 1;                                         Sqlparameter[] Paras ={new SqlParameter ("@num", sqldbtype.int,1) {            Value=1},}; This.            Label1.Text = "Start Execution";            int rowseffect = SQLHelper.SQLHelper.ExecuteNonQuery ("Insertoneinfoproc", CommandType.StoredProcedure, paras); if (Rowseffect > 0) this.            Label1.Text = "Insert succeeded"; else this.        Label1.Text = "Insert Failed"; }        #Region generates two-dimensional code///<summary>///Generate QR Code////</summary>//<param name= "path" > Ground        Address </param>//<param name= "name" > Picture name </param>//<returns>bool</returns> public bool CreateImage (string value, string path, string name) {Qrcodeencoder Qrcodeencoder = new Q            Rcodeencoder ();            Set the background color//qrcodeencoder.qrcodebackgroundcolor = Color.FromArgb (255, 255, 0);            Set foreground color//qrcodeencoder.qrcodeforegroundcolor = Color.greenyellow; Encoding format Qrcodeencoder.qrcodeencodemode = Qrcodeencoder.encode_mode.            BYTE;            Sets the size of each QR code pixel point Qrcodeencoder.qrcodescale = 4;            QR code version//QR code allowed specifications series for the 21x21 module (version 1) ~177x177 module (version) Qrcodeencoder.qrcodeversion = 8;            Error correction level//level l :  maximum  7%  errors can be corrected;   level m :  Maximum  15%  error can beCorrecting; ? //level q :  Max  25%  error can be corrected; ? //LEVEL&NBSP;H&N bsp;:  Maximum  30%  error can be corrected; qrcodeencoder.qrcodeerrorcorrect = Qrcodeencoder.error_correction.            M            Custom two-dimensional code data String = value;            Response.Write (data);            Drawing System.Drawing.Bitmap image = Qrcodeencoder.encode (data);            System.IO.MemoryStream mstream = new System.IO.MemoryStream (); Image.            Save (Mstream, System.Drawing.Imaging.ImageFormat.Png);            Response.clearcontent ();            Response.ContentType = "Image/png";            Write pictures to page Response.BinaryWrite (Mstream.toarray ());            Path = path + "\ \" + name + ". png"; if (! File.exists (Path)) {FileStream fs = new FileStream (path, filemode.createnew, Fileaccess.readwri                TE);                BinaryWriter bw = new BinaryWriter (FS, Utf8encoding.utf8); Byte[] by = Mstream.toarray (); for (int i = 0; i < Mstream.toarray (). Length; i++) bw.                Write (By[i]); Fs.                Close ();            return true;        } else return false; } #endregion}}

4. Two-dimensional code verification

Here is the GET request method, easy to test, security best use POST request

Using system.data;using system.data.sqlclient;using system.web;namespace webapplication{//<summary>//Che Ckcode's summary description///</summary> public class Checkcode:ihttphandler {public void ProcessRequest (HTTPC Ontext context) {context.            Response.ContentType = "Text/plain"; Context. request.form["Codetext"];//post string codetext = context.  request.querystring["Codetext"]; Get if (codetext! = null) {sqlparameter[] parameters = {New Sq                Lparameter ("@qrcodetext", sqldbtype.nvarchar,50), New SqlParameter ("@result", Sqldbtype.bit),                }; Parameters[0].                Value = Codetext; PARAMETERS[1].                Direction = ParameterDirection.Output;                int outputindex = 1;                String resnum = SQLHelper.SQLHelper.ExecuteNonQuery ("Searchproc", Outputindex, parameters); Context. Response.Write (reSnum); } else {context.            Response.Write ("No input QR Code information");            }} public bool IsReusable {get {return false; }        }    }}

Web validation creates a key QR code:
Click Generate key QR code
Server-side Key verification:

When the key is used five times, the return to the client is false result, that is, the key is invalid, the colleague server is to remove the key information from the database table!

About Unity Scan Code client is not recorded, prior to the relevant records, the main use of the WWW submission form, as well as the scan code plug-in production (please see the relevant links below), the other is nothing. It's late, promise someone to go to bed earlier today, seemingly again broke his promise, sorry! It seems that these two weeks are sleeping relatively late, tomorrow weekend, a good sleep!

Related connections:

c#/. NET stored procedures: http://www.cnblogs.com/clhed/articles/1269415.html

Scan two-dimensional code client: http://blog.csdn.net/dingxiaowei2013/article/details/25086835

Unity interacts with the server (form submission): http://blog.csdn.net/dingxiaowei2013/article/details/17115489

Two-dimensional code as activation Code (key) function development