Print tickets in Crystal Reports, SQL Query Optimization, c#

Question

I am using crystal reports to generate some tickets based on unique id in DB. However I am now challenged to generate 2 tickets per acre in a crystal report to be printed.

Below i have the code that should send the query and fill the crystal report viewer. For over 9000 unique records and over 74000 acres this will not work. Can anyone assist or help with better method to do this.

This code runs sql server 2008 r2 out of memory:

private void button1_Click(object sender, EventArgs e)
{
    try{
        SqlConnection con = new SqlConnection("Data Source=localhost\\DEV;Initial Catalog=db;Integrated Security=True");
        var f_id = textBox2.Text;  //Unique ID Not used to generate all tickets
        var n_copies = textBox1.Text; //Number of copies default 1
        var t_per_acre = textBox3.Text; //Set default at 2 Tickets per acre
        string sql = "Select * From dbo.CParcel";
        SqlDataAdapter sda = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        sda.Fill(ds, "CParcel");
        foreach (DataRow theRow in ds.Tables["CParcel"].Rows)
        {
            decimal get_acreage = System.Convert.ToDecimal(theRow["ACREAGE"]);
            int acr = (int)(get_acreage + 0.5m);
            int t_t_per_acr = acr * System.Convert.ToInt32(t_per_acre);
            int t_t_per_acr_per_copy = t_t_per_acr * System.Convert.ToInt32(n_copies);
            for (int i = 1; i < t_t_per_acr_per_copy; i++)
            {
                sql = sql + " Union All SELECT * FROM dbo.CParcel";
            }
        }
        SqlDataAdapter tsda = new SqlDataAdapter(sql, con);
        DataSet ds2 = new DataSet();
        tsda.Fill(ds2, "CParcel");
        crystal.SetDataSource(ds2);
        crystal.SetDatabaseLogon("sa", "password");
        crystalReportViewer1.ReportSource = crystal;

    }
    catch (Exception ex)
    {
        // Print error message
        MessageBox.Show(ex.Message);
    }
}

Answer 1

Consider the following modification which is very minor and just attempts to collect a few counters.

private void button1_Click(object sender, EventArgs e)
{
    int i_xyz = 0, j_xyz = 0;   // Drew added
    try
    {
        SqlConnection con = new SqlConnection("Data Source=localhost\\DEV;Initial Catalog=db;Integrated Security=True");
        var f_id = textBox2.Text;  //Unique ID Not used to generate all tickets
        var n_copies = textBox1.Text; //Number of copies default 1
        var t_per_acre = textBox3.Text; //Set default at 2 Tickets per acre
        string sql = "Select * From dbo.CParcel";   // STARTING POINT A
        SqlDataAdapter sda = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        sda.Fill(ds, "CParcel");
        foreach (DataRow theRow in ds.Tables["CParcel"].Rows)
        {
            i_xyz++;    // Drew added
            decimal get_acreage = System.Convert.ToDecimal(theRow["ACREAGE"]);
            int acr = (int)(get_acreage + 0.5m);
            int t_t_per_acr = acr * System.Convert.ToInt32(t_per_acre);
            int t_t_per_acr_per_copy = t_t_per_acr * System.Convert.ToInt32(n_copies);
            for (int i = 1; i < t_t_per_acr_per_copy; i++)
            {
                j_xyz++;    // Drew added
                sql = sql + " Union All SELECT * FROM dbo.CParcel";
            }
        }
        // POINT B <------------------

        // STOP !!
        // RIGHT HERE what is the value of i_xyz and j_xyz ?? In particular j_xyz
        // also flush sql out to a text file and get a good look at it

        //SqlDataAdapter tsda = new SqlDataAdapter(sql, con);
        //DataSet ds2 = new DataSet();
        //tsda.Fill(ds2, "CParcel");
        //crystal.SetDataSource(ds2);
        //crystal.SetDatabaseLogon("sa", "password");
        //crystalReportViewer1.ReportSource = crystal;

    }
    catch (Exception ex)
    {
        // Print error message
        MessageBox.Show(ex.Message);
    }
}

Right after POINT B you would know with j_xyz how many times you have tacked a UNION ALL onto your sql string. I could understand if your union added some granularity of some other meaningful (and different) subset of data, like here (albeit a different db engine). But think of a union as useful in cases where such things as different data and it is tricky to get to it without a union.

But in your case you are merely doing a union all select * from the same data each time. And potentially creating a huge sql statement to do that in the process. That j_xyz variable will tell you the count of times you tack one on.

Other thoughts include don't specify select * but rather just the columns your report will need. And also following best practices with memory management by using Dispose, setting to null, or what I would do as Adam showed here with "using with resources." Using.

So, in summary, with the sql string you are concatenating, there is no wonder the routine fails.