Print Multiple Pages from SqlServer Database using C# PrintDocuments

Question

i have a database table having more then 300 records i want to print it using PrintDocument/PrintPreview but the problem is only one page is showing in printPreview the remaining records are missing my Code is below please help me.

    private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
         int valueYPos=80;
         int valueXPos=20
         string classId=1;
            con.SqlQuery("select [RollNo],[Name] from [dbo].[StudentRegistration] where [ClassId]='" 
            + classId + "'");
            con.ExQuery();
            foreach (DataRow item in con.ExQuery().Rows)
            {
                e.Graphics.DrawString("RollNo" +item[0].ToString(), new Font("Arial", 20, 
                        FontStyle.Bold), Brushes.Black, new Point(valueXPos, valueYPos));
                e.Graphics.DrawString("RollNo" +item[1].ToString(), new Font("Arial", 20, 
                     FontStyle.Bold), Brushes.Black, new Point(valueXPos+20, valueYPos));
                valueYPos +=20;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            PrintPreviewDialog pPD = new PrintPreviewDialog();
            pPD.Document = printDocument1;
            pPD.ShowDialog();
        }

Answer 1

For print preview dialog see this answer Showing Print Preview in C#

First of all you can not access data like that because it will try to access data when it try to print each page.

            private int pageNo = 1;

            private static void Print_Invoice(object sender, PrintPageEventArgs e)
            {
                int valueYPos=80;
                int valueXPos=20
                string classId=1;
                con.SqlQuery("select [RollNo],[Name] from [dbo].[StudentRegistration] where [ClassId]='"+ classId + "'");
                con.ExQuery();

                switch (pageNo)
                {
                    case 1:
                        //Print some Data
                        e.HasMorePages = true;
                        break;
                    case 2:
                       //Print some Data
                       e.HasMorePages = true;
                       break;
                    case 3:
                       //Print some Data
                       e.HasMorePages = false;
                       break;
                    default:
                       e.HasMorePages = false;
                       break;
                }

                pageNo++;
                
            }

Hope resolved your problem

Answer 2

I would not try to execute your query in the PrintPage event like that. Move the execution of the query into the BeginPrint event and perform cleanup in the EndPrint event. Use a SqlDataReader that you can reference and iterate over inside your PrintPage handler. Something like this, although you will obviously need to handle headers, footers, margins, columns, etc.. This code will also properly re-execute the query should the user click the print button from the PrintPreviewDialog.

private SqlConnection conn;
private SqlCommand cmd;
private SqlDataReader reader;

private void button1_Click(object sender, EventArgs e)
{
    PrintDocument pDoc = new PrintDocument();
    pDoc.BeginPrint += new PrintEventHandler(this.BeginPrint);
    pDoc.EndPrint += new PrintEventHandler(this.EndPrint);
    pDoc.PrintPage += new PrintPageEventHandler(this.PrintPage);
            
    PrintPreviewDialog pPD = new PrintPreviewDialog();
    pPD.Document = pDoc;
    pPD.ShowDialog();
}

private void BeginPrint(object sender, PrintEventArgs e)
{
    const string sql = "SELECT [Name] FROM [StudentRegistration]";

    SqlConnection conn = new SqlConnection(ConnStr);
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    conn.Open();
    reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
}   

private void EndPrint(object sender, PrintEventArgs e)
{
    reader.Close();
    cmd.Dispose();
    conn.Dispose(); 
}

private void PrintPage(object sender, PrintPageEventArgs e)
{
    Font myFont = new Font("Arial", 20, FontStyle.Bold);
    int rowHt = myFont.Height();
    int valueYPos = 80;
    int valueXPos = 20;
    
    bool pageDone = false;
    bool moreData = false;

    while (!pageDone)
    {
        moreData = reader.Read();
        pageDone = !moreData;
        if (moreData)
        {
            e.Graphics.DrawString(reader.GetString(0), myFont, Brushes.Black, valueXPos, valueYPos);
            valueYPos += rowHt;
            
            // Is there room to print another row on this page?
            if (valueYPos + rowHt > (e.PageBounds.Height))
                pageDone = true;
        }
    }

    e.HasMorePages = moreData;
}