Generate QR Code And Save Into The Database

Abhishek Chadha

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.

QRCode

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.

QRcode image

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.

C#QR CodeGenerate QR CodeQR code save into DatabaseRead QR code

9 Comments

Join the conversation! Your thoughts help the community grow.

Tamilzhazhagan2y ago
Hi Abhishek could you please post the generate QR report code here
Kunal Gupta4y ago
I have a problem some i am trying to show some values save in my database in QR Code in pdf that consist of data of values taken from database. In addition to it i want to fetch some values from database in QR Code and adjust in that pdf.
- Abhishek Chadha4y ago
  Could you please help me with your exact problem
Fabrizio Leoncini4y ago
Good morning congratulations for your work, I can't understand how your tutorial works, please don't have the code to make available?Thanks a lot and congratulations Fabrizio from Italy
- Abhishek Chadha4y ago
  May I know the exact step where you stuck ?
Oliver Castro5y ago
The type name 'QRCode' does not exist in the type 'QRCodeGenerator' How to fix this? thank you!
- Abdul Aziz5y ago
  Use this code instead : QRCodeGenerator qrGenerator = new QRCodeGenerator(); QRCodeData qrCodeData = qrGenerator.CreateQrCode(subasset.AssetId,QRCodeGenerator.ECCLevel.Q);QRCode qrCode = new QRCode(qrCodeData);
mohaammed talha5y ago
I Need Design Of The Form
- Abhishek Chadha5y ago
  Its simple design, you can take anywhere from internet

Introduction

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.

For this, you need to add 2 more namespaces.

using System.Text; using QRCoder;

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)); }

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

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