MailMerge from SQL database

GemBox.Document
1

Hey, tell me please.

I want to get some information from database, create docx with tables inside and do mailmerge option.

In my case it doesn’t work. Could you tell me where the bug is?

static List<Table1Row> GetTable1Data(string connStr)
{
    var rows = new List<Table1Row>();
 
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand("SELECT [ATTORNEY_NAME] AS Attorney,[ATTORNEY_RATE] AS Rate,[RANK] AS Title " +
        "                               FROM [Custom].[dbo].[ Rates] ", 
                                            conn))
    {
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                rows.Add(new Table1Row
                {
                    Attorney = reader["Attorney"].ToString(),
                    Rate = reader["Rate"].ToString(),
                    Title = reader["Title"].ToString()
                });
            }
        }
    }
 
    return rows;
}
public class Table1Row
{
    public string Attorney { get; set; }
    public string Rate { get; set; }
    public string Title { get; set; }
}
 
public class SourceData
{
    public List<Table1Row> Table1 { get; set; }
}

Used this code to populate the collection.

var source = new SourceData
{
    Table1 = GetTable1Data(connectionString)
};

And used this code to display it in the document:

Paragraph parrrr = new Paragraph(dc);
Section_ExhibitB.Blocks.Add(parrrr);
 
var table = new Table(dc, 2, 2);
table.TableFormat.PreferredWidth = new TableWidth(100, TableWidthUnit.Percentage);
 
table.Rows[0].Cells[0].Blocks.Add(
    new Paragraph(dc,
        new Field(dc, FieldType.MergeField, "RangeStart:Table1"),
        new Field(dc, FieldType.MergeField, "Attorney")));
 
table.Rows[0].Cells[1].Blocks.Add(
    new Paragraph(dc,
        new Field(dc, FieldType.MergeField, "Rate"))
    {
        ParagraphFormat =
        {
            LeftIndentation = 350,
            //RightIndentation = 150,
            //SpecialIndentation = 30
        }
    });
 
table.Rows[1].Cells[1].Blocks.Add(
    new Paragraph(dc,
        new Field(dc, FieldType.MergeField, "Title"),
        new Field(dc, FieldType.MergeField, "RangeEnd:Table1"))
    {
        ParagraphFormat =
        {
            //LeftIndentation = 0,
            RightIndentation = 0,
            //SpecialIndentation = 30
        }
    });
 
Section_ExhibitB.Blocks.Add(table);
 
dc.MailMerge.Execute(source);
2

Hi,

I was unable to reproduce your issue.
I tried the code using the following data source:

static List<Table1Row> GetTable1Data()
{
    return new List<Table1Row>()
    {
        new Table1Row
        {
            Attorney = "John Doe",
            Rate = "$200/hr",
            Title = "Senior Attorney"
        },
        new Table1Row
        {
            Attorney = "Jane Smith",
            Rate = "$180/hr",
            Title = "Junior Attorney"
        },
        new Table1Row
        {
            Attorney = "Alice Johnson",
            Rate = "$220/hr",
            Title = "Lead Attorney"
        },
    };
}

And I got the following result:

result-document
result-document822×358 5.5 KB

Is it possible that your Section object was not added to DocumentModel.Sections collection?
In other words, do you have something like this in your code:

var Section_ExhibitB = new Section(dc);
dc.Sections.Add(Section_ExhibitB);

If that’s not your problem, can you please send us a small Visual Studio project that reproduces your issue so we can investigate it?

Also, just as an FYI, instead of this:

table.Rows[0].Cells[1].Blocks.Add(
    new Paragraph(dc,
        new Field(dc, FieldType.MergeField, "Rate"))
    {
        ParagraphFormat =
        {
            LeftIndentation = 350,
            //RightIndentation = 150,
            //SpecialIndentation = 30
        }
    });

You might want to try this:

table.Rows[0].Cells[1].Blocks.Add(
    new Paragraph(dc,
        new Field(dc, FieldType.MergeField, "Rate"))
    {
        ParagraphFormat =
        {
            Alignment = HorizontalAlignment.Right
        }
    });

Regards,
Mario