Generate QR Code And Save Into The Database

As you all know these days barcode and QR code are so popular in the industry.QR (Quick Response) Code is a type of Matrix Bar code as its name defines quick response due to this it became so popular for its fast readability and greater storage capacity when we compare to standard barcodes. The industries include product tracking, item identification, asset management mainly uses the QR code. because a single QR code can hold all the product-related information. and when you read the QR code it gives you all the related information stored in it instantly.
 
Now in this article, we see how we can Generate the QR Code in ours.net Application and save it to the database for further use.
 
This example shows you how we can generate a QR sticker for product or asset tracking applications. in this, I give a range to generate the QR codes for that first select the Asset from and to the range and then click the button Generate QR code. I assume that u know a little bit about the c# so that I skip the small things.
 
 
DLL - QRCoder
 
For this, you need to add 2 more namespace,
  1. using System.Text;    
  2. using QRCoder;    
On button click event call a function and add a below code into the function. what this function do this retrieve all the necessary information from the database corresponding to particular asset in the selected range and using for loop we generate a QR code and save the QR code to the database and then print the stickers so that we paste the stickers into the asset or product whatever your requirement  
  1.      
  2.       con = Mydatabase.MyDB.getConnection();    
  3.       string SQL = "select id,ast_id,MachineNo,invno,convert(nvarchar(11),ae.vchdate) invdate from asset";    
  4.       SqlCommand command = new SqlCommand(SQL, con);    
  5.   
  6.       con.Open();    
  7.       SqlDataAdapter da = new SqlDataAdapter(command);    
  8.       DataTable dt = new DataTable();    
  9.       da.Fill(dt);    
  10.       for (int i = 0; i < dt.Rows.Count; i++)    
  11.       {    
  12.           string astID = dt.Rows[i]["id"].ToString();    
  13.           string astCode = dt.Rows[i]["ast_id"].ToString();    
  14.           string loc = dt.Rows[i]["ast_loc"].ToString();    
  15.           string serialNo = dt.Rows[i]["MachineNo"].ToString();    
  16.           string invno = dt.Rows[i]["invno"].ToString();    
  17.           string invdate = dt.Rows[i]["invdate"].ToString();    
  18.           StringBuilder bcode1 = new StringBuilder("Ast Code : " + astCode + Environment.NewLine + "S.No : " + serialNo + Environment.NewLine + "Inv No : " + invno + Environment.NewLine + "Inv Date : " + invdate);    
  19.           saveToDatabase(bcode1, Convert.ToInt32(astID));    
  20.       }   
In the above function, we make a string using StringBuilder for which we can generate the QR code and call the function saveToDatabase that generate code and save into the database. first let's create a table that store the information like the unique id of the row, QR code hold in BRCD column, and the image of the QR code store in the IMG column of image datatype. we see this also how we can convert the QRcode to image in the below function also and the PID holds your product id for which you create QR code. 
  1. SET ANSI_NULLS ON  
  2. GO  
  3.   
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6.   
  7. CREATE TABLE [dbo].[qrunits](  
  8.     [id] [int] IDENTITY(1,1) NOT NULL,  
  9.     [brcd] [nvarchar](maxNULL,  
  10.     [img] [image] NULL,  
  11.     [cur_dt] [datetime] NULL,  
  12.     [uid] [intNULL,  
  13.     [pid] [intNULL,  
  14.  CONSTRAINT [PK_ar_qrunits] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [id] ASC  
  17. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  18. ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  19.   
  20. GO  
This script creates the table now we are going to create the QR Code, the dll we added having a QRCodeGenerator object that convert the string to QRcode and the generated QR code we converted to the image to store it in the database using memory stream and the byte array we get from this memory stream will save to db.in this also we pass the height and width of QR Code dynamically according to our requirement 
  1. private void saveToDatabase(StringBuilder bcode,int astID)      
  2.    {      
  3.        SqlConnection con = null;      
  4.        int uid = Convert.ToInt32(HttpContext.Current.Session["uid"]);      
  5.        try      
  6.        {      
  7.       
  8.       
  9.            byte[] img = null;      
  10.            string code = bcode.ToString();      
  11.            string strn = bcode.ToString(); ;      
  12.            QRCodeGenerator qrGenerator = new QRCodeGenerator();      
  13.            QRCodeGenerator.QRCode qrCode = qrGenerator.CreateQrCode(code, QRCodeGenerator.ECCLevel.Q);      
  14.            System.Web.UI.WebControls.Image imgBarCode = new System.Web.UI.WebControls.Image();      
  15.            imgBarCode.Height =50;      
  16.            imgBarCode.Width = 50;      
  17.            using (Bitmap bitMap = qrCode.GetGraphic(20))      
  18.            {      
  19.                using (MemoryStream ms = new MemoryStream())      
  20.                {      
  21.                    bitMap.Save(ms, System.Drawing.Imaging.ImageFormat.Png);      
  22.       
  23.                    img = new byte[ms.ToArray().Length];      
  24.                    img = ms.ToArray();      
  25.                }      
  26.                    
  27.            }      
  28.       
  29.                  
  30.                 
  31.            con = Mydatabase.MyDB.getConnection();      
  32.            if (con.State == ConnectionState.Open)      
  33.                con.Close();      
  34.            con.Open();      
  35.            string SQL = "if not exists(select pid from qrunits where pid=@astid) insert into qrunits(brcd,img,pid,cur_dt,uid) values(@bcode,@img,@astid,@curdt,@uid)"      
  36.  else update qrunits set brcd=@bcode,img=@img,cur_dt=@curdt,uid=@uid where pid=@astid ";      
  37.            SqlCommand command = new SqlCommand(SQL, con);      
  38.            command.Parameters.AddWithValue("@astid", astID);      
  39.            command.Parameters.AddWithValue("@bcode", strn);      
  40.            command.Parameters.AddWithValue("@img", img);      
  41.            command.Parameters.AddWithValue("@curdt", DateTime.Now.Date.ToString("MM/dd/yyyy"));      
  42.            command.Parameters.AddWithValue("@uid", uid);      
  43.            int i = command.ExecuteNonQuery();      
  44.       
  45.       
  46.        }      
  47.        catch (Exception ex)      
  48.        {      
  49.            throw new Exception("Error in Saving Record.");      
  50.        }      
  51.        finally      
  52.        {      
  53.            if (con.State == ConnectionState.Open)      
  54.                con.Close();      
  55.        }      
  56.       
  57.    }      
This is our table data after saving the image of the QR Code to the database. now we can show the QR Code anywhere in the application just pick this image data from the table and show the QR to any page any report. after saving the data we generate one report say it as a QR code Stickers that print the QR Image and the Code within the range we selected earlier. 
 
 
 
 
This is the idea about the QR Sticker report on how it looks and how to generate this report will be cover in the next article.