C# Print multiple pages from SQL result

Question

Lets say i have a table with 20 rows, and I want to print 5 employee names on each page. Total 4 pages.

The problem with my current code is, that it tries to display every employee name on the first page. Additionally it ends in a infinite loop. This is my code:

DataTable dt = new DataTable();

private void FillDataTable()
    {
        SqlConnection sqlConnection = GetConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;

        string query =
        @"SELECT * FROM EMPLOYEES ORDER BY ID";

        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection;

        sqlConnection.Open();

        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            dt.Load(reader);
        }
        else
        {
            Console.WriteLine("No rows found.");

        }
        reader.Close();
        sqlConnection.Close();
    }

private void printDocument_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
    {         
        foreach(DataRow row in dt.Rows)
        {     
            e.Graphics.DrawString(row[4].ToString(), new Font("Arial", 12, FontStyle.Bold), Brushes.Black, e.MarginBounds.Left, y, new StringFormat());

            y += 100;

            if (itemperpage < 5)
            {
                itemperpage += 1;
                e.HasMorePages = false;
            }
            else
            {
                e.HasMorePages = true;

            }

        }                       
    }

private void printDocument_BeginPrint(object sender, System.Drawing.Printing.PrintEventArgs e)
    {
        y = 80;
        itemperpage = 0;
    }

What am I missing here? How to display only 5 employees names on each page until the SQL result table is done?

You need a break condition when you set HasMorePages to true, you need to start from the correct offset in the DataTable rows when you start the next page, and you need to reset itemperpage to zero. — ProgrammingLlama
– ProgrammingLlama, Commented Feb 27, 2017 at 14:19

Ross Bush · Accepted Answer · 2017-02-27 15:33:00Z

1

You will need to track the page count manually as the page info only has properties describing the page, not the print process. Then you can cast your DataTable as IEnumerable and use the Ling Skip() and Take() extension methods to make coding easier. Casting back to a Datatable with your reduced set will allow you to traverse with your excising code.

    using System.Linq;
    ...

    private int _CurentPageNumber = 0;

    private void printDocument_BeginPrint(object sender, System.Drawing.Printing.PrintEventArgs e)
    {
        y = 80;
        itemperpage = 0;
        _CurentPageNumber = 0;
    }

    private void printDocument_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
    {
        _CurentPageNumber++;

        int skip = (_CurentPageNumber - 1) * itemperpage;

        var query = dt.AsEnumerable().Skip(skip).Take(itemperpage);

        foreach (DataRow row in query.CopyToDataTable().Rows)
        {
            e.Graphics.DrawString(row[4].ToString(), new Font("Arial", 12, FontStyle.Bold), Brushes.Black, e.MarginBounds.Left, y, new StringFormat());
        }
    }

answered Feb 27, 2017 at 15:33

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Philipp Weigel Over a year ago

var query = dt.AsEnumerable().Skip(skip).Take(itemperpage); query is strangely null. My DataTable is surely filled with Data. How can this happen?

Ross Bush Over a year ago

Then you have something else going on. The only thing that would make the code I added return empty is if you don't reset _CurrentPageNumber to zero in between print runs or if you set itemperpage to zero. I suggest you add some breakpoints and see where it fails.

Philipp Weigel · Accepted Answer · 2017-02-28 11:37:57Z

I edited @Ross Bush Code a bit and now it seems to work.

private void printDocument_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
    {
        neturalDataSet.centersDataTable table = new neturalDataSet.centersDataTable();

        centersTableAdapter.Fill(table);

        y = 80;

        itemperpage = 0;


        _CurrentPageNumber++;

        if(_CurrentPageNumber != 1)
        {
            skip += 6;
        }

        var query = table.AsEnumerable().Skip(skip);

        foreach (DataRow row in query.CopyToDataTable().Rows)
        {
            e.Graphics.DrawString(row[0].ToString(), new Font("Arial", 12, FontStyle.Bold), Brushes.Black, e.MarginBounds.Left, y, new StringFormat());

            y += 100;

            if (itemperpage < 5)
            {
                itemperpage += 1;
                e.HasMorePages = false;

            }
            else
            {
                e.HasMorePages = true;
                return;
            }

        }

    }

Collectives™ on Stack Overflow

C# Print multiple pages from SQL result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related