How to Save Retrieve Barcodes to and from Database in .NET

Overview

It is possible to save a barcode as an image file using Aspose.BarCode. Developers can use this feature to generate a barcode image and then save it to some location in a network or a local machine. In some cases, it may be required to store barcodes in a database.

This article discusses two approaches to manage barcodes in databases:

• Store the information about barcodes
• Store barcode images

Each of these approaches has advantages and limitations. Developers can decide which method fulfills their requirements better.

Save and Retrieve Barcode Information

The most simple way to manage barcodes in a database is to store barcode information, such as barcode text and type. Instead of loading original barcode images, only their details are saved in a database. When a barcode needs to be displayed, this information is retrieved from the database and the corresponding barcode image is generated at runtime. This approach is illustrated by the code sample provided further.

Create Database

First, it is required to create a database using any DBMS, such as Oracle, SQL Server, MS Access, etc. In the provided examples, Microsoft SQL Server has been used to create a database. Then, a table with three columns has been inserted. In the first column, the bid denotes the Numeric Primary Key and Table Identity Column. It means that the bid column will automatically generate its primary key (unique) values. The other two columns named “code text” and “symbology” have the varchar type, meaning that they can store characters of variable length.

Save Barcode Information to Database

To implement an application that generates barcode images according to user inputs, an instance of class BarcodeGenerator needs to be created specifying the required BarcodeType in the constructor and input text to the CodeText property. To save barcode information in the database, it is needed to open a database connection using SqlConnection and insert the barcode text and type into the table by calling the ExecuteNonQuery method of SqlCommand. To improve the efficiency of this operation, it is possible to use a Parameterized SQL Query by defining @Codetext and @Symbology as parameters.

private static void StoreInDB(BarcodeGenerator generator)
{
	string connectionString = "server=localhost;database=test_db;uid=sa;pwd=123;";

	string Codetext = generator.CodeText;
	string Symbology = generator.BarcodeType.ToString();

	// Create the SQL connection.
	SqlConnection mConnection = new SqlConnection(connectionString);
          mConnection.Open();

	// Create the SQL command.
	string commandString = "INSERT INTO barcodes (CodeText,Symbology) VALUES('" + Codetext + "','" + Symbology + "')";
	SqlCommand command = new SqlCommand(commandString, mConnection);

	// Execute the command.
	command.ExecuteNonQuery();

	// Close the connection.
          mConnection.Close();
}

Retrieve Barcode Information from Database

To display the barcode image from the database, the information about this barcode has to be retrieved, and the barcode is generated at runtime accordingly. To do this, it is necessary to connect to the database and select the data for the barcode to be generated. Then, it is needed to fill the DataTable with this data using SqlDataAdapter. An object of class BarcodeGenerator is created at runtime so that its CodeText and BarcodeType properties are configured using the barcode information from DataTable.

private static BarcodeGenerator RetrieveFromDB()
{
	BarcodeGenerator barcode = null;
	string connectionString = "server=localhost;database=test_db;uid=sa;pwd=123;";

	// Create the SQL connection.
	SqlConnection mConnection = new SqlConnection(connectionString);
	mConnection.Open();

	// Create the SQL command.
	string commandString = "SELECT * FROM barcodes";
	SqlCommand command = new SqlCommand(commandString, mConnection);

	// Create the data adapter.
	SqlDataAdapter adapter = new SqlDataAdapter(command);

	// Fill the results from the database into a DataTable.
	DataTable dataTable = new DataTable();
	adapter.Fill(dataTable);

	string SymbologyType = dataTable.Rows[0]["Symbology"].ToString();
	bool isValid = EncodeTypes.Parse(SymbologyType, out BaseEncodeType encodeType);
	if(isValid)
          {
		barcode = new BarcodeGenerator(encodeType);
		barcode.CodeText = dataTable.Rows[0]["CodeText"].ToString();
	}

	// Close the connection.
	mConnection.Close();

	return barcode;
}

This method can be also implemented in Windows Forms using BarCodeControl. After dragging the BarCodeControl element to the Controls collection of Panel, the barcode image gets visible.

Advantages

This approach has the following advantages:

• Suitable for small and simple applications where only basic barcodes are generated
• More efficient as it allows storing barcode information as several text values in the database instead of saving the barcode image itself
• Efficient in terms of database memory resources
• Provides faster execution

Limitations

If it is required to generate complex barcode images by customizing their parameters, such as forecolor, background color, bar height, wide-to-narrow ratio, X-dimension, and others, extra columns need to be added to the database table to store the information about all properties related to a barcode image. In this case, the following limitations would arise:

• It takes more database memory resources
• It requires updating not only the database but also the source code to store additional information about barcode images

Save and Load Barcodes from Database

To avoid creating a large number of columns in a database table for complex barcodes, the other approach can be used. It allows saving barcode image files in the database. A barcode can be basic with just two parameters or complex with many customized properties.

Save Barcode Image to Database

To save a barcode image to the database, it is necessary to create a varbinary data type column in the database table. The image must be saved as a MemoryStream and then converted to image bytes that are stored in the database column of the varbinary type, as demonstrated in the code example given below.

private static void StoreImageInDB(BarcodeGenerator barcode)
      {
	string connectionString = "server=localhost;database=test_db;uid=sa;pwd=123;";

	MemoryStream ms = new MemoryStream();
	barcode.Save(ms, BarCodeImageFormat.Bmp);
	ms.Seek(0, SeekOrigin.Begin);
	byte[] imgBytes = ms.ToArray();

	// Create the SQL connection
	SqlConnection mConnection = new SqlConnection(connectionString);
	mConnection.Open();
	
	// Create the SQL command.
	string commandString = "INSERT INTO barcodeImages(CodeText,barcodeImage) VALUES(@codetext, @BarcodeImage)";
	SqlCommand command = new SqlCommand(commandString, mConnection);
	command.Parameters.AddWithValue("codetext", barcode.CodeText);
	command.Parameters.AddWithValue("BarcodeImage", imgBytes);

	// Execute the command.
	command.ExecuteNonQuery();
	mConnection.Close();
}

Load Barcode Image from Database

The following code sample demonstrates how to retrieve a barcode image from the database.

private static Image RetrieveImageFromDB()
      {
	string connectionString = "server=localhost;database=test_db;uid=sa;pwd=123;";

	// Create the SQL connection.
	SqlConnection mConnection = new SqlConnection(connectionString);
	mConnection.Open();

	// Create the SQL command.
	string commandString = "SELECT * FROM barcodeImages";
	SqlCommand command = new SqlCommand(commandString, mConnection);

	// Create the data adapter.
	SqlDataAdapter adapter = new SqlDataAdapter(command);

	// Fill the results from the database into a DataTable.
	DataTable dataTable = new DataTable();
	adapter.Fill(dataTable);

	MemoryStream ms = new MemoryStream((byte[])dataTable.Rows[0]["barcodeImage"]);
	Image image = Image.FromStream(ms, true);

	mConnection.Close();

	return image;
}

The following code snippet shows how to call the Save and Retrieve methods.

// For complete examples and data files, please go to https://github.com/aspose-barcode/Aspose.BarCode-for-.NET

// The path to the documents directory.
string dataDir = RunExamples.GetDataDir_TechnicalArticles();

         // Instantiate linear barcode object, Set the Code text and symbology type for the barcode
         BarcodeGenerator generator = new BarcodeGenerator(EncodeTypes.Code128);
         generator.CodeText = "1234567";

         //Save and Retrieve Barcode Information
         StoreInDB(generator);
         BarcodeGenerator barcode = RetrieveFromDB();

         // Save the retrievd Barcode in JPEG format
         barcode.Save(dataDir + "barcode_out.jpg", BarCodeImageFormat.Jpeg);

         // Save and Retrieve Barcode Image
         StoreImageInDB(generator);
         Image image = RetrieveImageFromDB();

// Save the retrieved Barcode image in JPEG format
image.Save(dataDir + "BarcodeFromDB_out.jpg", ImageFormat.Jpeg);

Conclusion

Both approaches discussed so far have their advantages and limitations briefly described in the article. In simple applications, the first approach can be applied. In applications intended to create complex barcodes, the second option is more suitable.