Alexander Alexander - 11 days ago 8
C# Question

How can I Print label from database select result using loop

I've to say this first , I'm new to windows programming,

I have got a requirement to print a set of results getting from a search query. Each resulted row should be printed on individual labels . A sample result set is included in the query. What is the best way achieving this.The barcode column shown should printed as bar code and the other two columns should print above and below the label.
Normally we may have to print up to 500 labels in a single button click.

Below I've added the code I'm working with. In the page load event I'm passing the ID to load data for a specific item from database. But I need to make it automated with a selected list of items, not a single one.
Result set

public partial class PrintLabel : Form
{
string s_desc = "";
string s_date = "";
Image bCodeImage;

public PrintLabel()
{
InitializeComponent();
}
private void PrintLabel_Load(object sender, EventArgs e)
{
FillData(735);
}
private void Print_Click(object sender, EventArgs e)
{
ItemPrint();
this.Close();
}
void FillData(int ID)
{
string str = Properties.Settings.Default.ConW;
using (SqlConnection conn = new SqlConnection(str))
{
try
{
string query = "select item.ID+'-'+item.ItemLookupCode as Barcode,ExtendedDescription,GETDATE()+180 Expiry from Item where ID=" + ID + ";";
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader dtr = cmd.ExecuteReader();
if (dtr.Read())
{
GenerateBarcode(dtr[0].ToString());
s_desc = dtr[1].ToString();
s_date = dtr[2].ToString();
lblDescription.Text = s_desc;
lblExpiry.Text = s_date;
PBBarcode.Image = bCodeImage;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void GenerateBarcode(string _bCodeImage)
{
string barCode = _bCodeImage;
Bitmap bitmap = new Bitmap(barCode.Length * 60, 750);
using (Graphics grapics = Graphics.FromImage(bitmap))
{
Font ofont = new System.Drawing.Font("IDAHC39M Code 39 Barcode", 14);
PointF point = new PointF(2f, 2f);
SolidBrush black = new SolidBrush(Color.Black);
SolidBrush white = new SolidBrush(Color.White);
grapics.FillRectangle(white, 0, 0, bitmap.Width, bitmap.Height);
grapics.DrawString("*" + barCode + "*", ofont, black, point);
}
using (MemoryStream ms = new MemoryStream())
{
bitmap.Save(ms, ImageFormat.Png);
bCodeImage = bitmap;
}
}

private void ItemPrint()
{
PrintDialog printdg = new PrintDialog();
if (printdg.ShowDialog() == DialogResult.OK)
{
PrintDocument pd = new PrintDocument();
pd.PrinterSettings = printdg.PrinterSettings;
pd.PrintPage += PrintPage;
pd.Print();
pd.Dispose();
}
}
private void PrintPage(object o, PrintPageEventArgs e)
{
lblDescription.Text = s_desc;
lblExpiry.Text = s_date;
PBBarcode.InitialImage = bCodeImage;
}
}

Answer

I've achieved it using the below code, posting as it may help some one in the future.

private void PrintLabelVal()
        {
            string str = Properties.Settings.Default.con;
            SqlConnection Conn = new SqlConnection(str);


            string query = " SELECT TempLotUpdate.Quantity,Item.BinLocation,Item.ItemLookupCode," +
                            " Item.ExtendedDescription,Item.LotNumber," +
                            " Item.ExpiryDate " +
                            " FROM TempLotUpdate" +
                             " INNER JOIN Item ON TempLotUpdate.ItemId = Item.ID" +

                            " WHERE TempLotUpdate.PONumber = '" + Globals.s_PON + "'; ";
            int cnt = 0;
            int var = 0;
            SqlCommand cmd = new SqlCommand(query, Conn);
            try
            {
                Conn.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    cnt = Convert.ToInt32(dt.Rows[i][0]);
                    var = Convert.ToInt32(dt.Rows[i][4]);
                    for (var j = 0; j < cnt; j++)
                    {
                        var = Convert.ToInt32(dt.Rows[i][4]);
                        print(var); // Passing Lot ID
                    }
                }
             }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void print(int obj)
        {
            FillData(obj);
            ItemPrint();
            this.Close();
        }
        void FillData(int ID)
        {
            string str = Properties.Settings.Default.con;
            using (SqlConnection conn = new SqlConnection(str))
            {
                try
                {
                    string query = "SELECT ItemLookupCode BarCode,Description,ExpiryDate,BinLocation,Price " +
                                   " FROM Item where ID = " + ID + ";";
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(query, conn);
                    SqlDataReader dtr = cmd.ExecuteReader();
                    if (dtr.Read())
                    {

                        barCode = dtr[0].ToString();
                        s_desc = dtr[1].ToString();
                        s_date = dtr[2].ToString();
                        s_BinLoc = dtr[3].ToString();
                        s_Price = dtr[4].ToString();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void ItemPrint()
        {
            PrintDialog printdg = new PrintDialog();
            if (printdg.ShowDialog() == DialogResult.OK)
            {
                PrintDocument pd = new PrintDocument();
                pd.PrinterSettings = printdg.PrinterSettings;
                pd.PrintPage += PrintPage;
                pd.Print();
                pd.Dispose();
            }
        }
        private void PrintPage(object o, PrintPageEventArgs e)
        {
            string _desc = s_desc;
            var _g = e.Graphics;
            _g.DrawString(_desc, new Font("Arial Black", 7), Brushes.Black, 5, 8);

            string _bCode = "*"+barCode+"*";
            var _f = e.Graphics;
            _f.DrawString(_bCode, new Font("IDAHC39M Code 39 Barcode", 8), Brushes.Black, 4, 25);

            string _BinLoc = s_BinLoc;
            var _i = e.Graphics;
            _i.DrawString(_BinLoc, new Font("Arial Narrow", 7), Brushes.Black, 5, 75);

            string _date = "Exp: " + s_date.Substring(0, 10);
            var _h = e.Graphics;         
            _h.DrawString(_date, new Font("Arial Narrow", 7), Brushes.Black, 45, 75);

            string _Price = s_Price;
            var _j = e.Graphics;         
            _j.DrawString(_Price, new Font("Arial Narrow", 7), Brushes.Black, 120, 75);
        }