Generate QR Code And Save Into The Database

Introduction

As you all know, these days, barcodes and QR codes 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 compared to standard barcodes. The industries include product tracking, item identification, and asset management, which 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.

For this, you need to add 2 more namespaces.

using System.Text;
using QRCoder;

On the button, click event call a function and add the below code into the function. what this function does is retrieve all the necessary information from the database corresponding to a particular asset in the selected range, and using a for loop, we generate a QR code 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.

con = Mydatabase.MyDB.getConnection();
string SQL = "select id, ast_id, MachineNo, invno, convert(nvarchar(11), ae.vchdate) invdate from asset";
SqlCommand command = new SqlCommand(SQL, con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
    string astID = dt.Rows[i]["id"].ToString();
    string astCode = dt.Rows[i]["ast_id"].ToString();
    string loc = dt.Rows[i]["ast_loc"].ToString();
    string serialNo = dt.Rows[i]["MachineNo"].ToString();
    string invno = dt.Rows[i]["invno"].ToString();
    string invdate = dt.Rows[i]["invdate"].ToString();
    StringBuilder bcode1 = new StringBuilder("Ast Code: " + astCode + Environment.NewLine +
        "S.No: " + serialNo + Environment.NewLine +
        "Inv No: " + invno + Environment.NewLine +
        "Inv Date: " + invdate);
    saveToDatabase(bcode1, Convert.ToInt32(astID));
}

In the above function, we make a string using StringBuilder for which we can generate the QR code and call the function saveToDatabase that generates the code and save it into the database. first let's create a table that stores the information like the unique ID of the row, the QR code held in the BRCD column, and the image of the QR code stored in the IMG column of the image datatype. we see how we can convert the QRcode to an image in the below function and the PID holds your product ID for which you create the QR code.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[qrunits]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [brcd] [nvarchar](max) NULL,
    [img] [image] NULL,
    [cur_dt] [datetime] NULL,
    [uid] [int] NULL,
    [pid] [int] NULL,
    CONSTRAINT [PK_ar_qrunits] 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] TEXTIMAGE_ON [PRIMARY]

GO

This script creates the table. Now we are going to create the QR Code; the dll we added has aQRCodeGenerator object that converts 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.

private void saveToDatabase(StringBuilder bcode, int astID)
{
    SqlConnection con = null;
    int uid = Convert.ToInt32(HttpContext.Current.Session["uid"]);
    try
    {
        byte[] img = null;
        string code = bcode.ToString();
        string strn = bcode.ToString();
        QRCodeGenerator qrGenerator = new QRCodeGenerator();
        QRCodeGenerator.QRCode qrCode = qrGenerator.CreateQrCode(code, QRCodeGenerator.ECCLevel.Q);
        System.Web.UI.WebControls.Image imgBarCode = new System.Web.UI.WebControls.Image();
        imgBarCode.Height = 50;
        imgBarCode.Width = 50;
        using (Bitmap bitMap = qrCode.GetGraphic(20))
        {
            using (MemoryStream ms = new MemoryStream())
            {
                bitMap.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                img = new byte[ms.ToArray().Length];
                img = ms.ToArray();
            }
        }
        con = Mydatabase.MyDB.getConnection();
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        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) else update qrunits set brcd=@bcode,img=@img,cur_dt=@curdt,uid=@uid where pid=@astid";
        SqlCommand command = new SqlCommand(SQL, con);
        command.Parameters.AddWithValue("@astid", astID);
        command.Parameters.AddWithValue("@bcode", strn);
        command.Parameters.AddWithValue("@img", img);
        command.Parameters.AddWithValue("@curdt", DateTime.Now.Date.ToString("MM/dd/yyyy"));
        command.Parameters.AddWithValue("@uid", uid);
        int i = command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception("Error in Saving Record.");
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
    }
}

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 of 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 covered in the next article.