从营销人员数据库的 sitecore 网络表单中读取数据

这是完全可行的，尽管从 WFFM 获取数据的选择查询有点时髦，因为所有内容都松散地存储在一个名为"field"的巨大表中，只有一串 GUID 将存储的值与它们来自哪种形式和哪个字段相关联。

下面提供的是我为 WFFM 数据编写的"导出到 Excel"实用程序的一部分。它从提交的表单结果生成数据表对象。不过，您可以将其适应其他结构，而无需太多工作。

public string connectionStringWFFM = "user id=sitecore_admin;password=xxx;Data Source=SitecoreDBServer.com;Database=Sitecore_WebForms";
protected DataTable BuildDataTable(Item formItem)
{
    List<FormResult> formResults = FormResults(formItem.ID.Guid);
    List<Field> distinctFields = DistinctFields(formItem.ID.Guid);
    var dt = new DataTable();
    dt.Columns.Add("Submission_DateTime", typeof (string));
    foreach (Field field in distinctFields)
    {
        var dataColumn = new DataColumn("_" + field.id.ToString("N"), typeof (string));
        dataColumn.Caption = field.name.Replace(" ", "_");
        dt.Columns.Add(dataColumn);
    }
    foreach (FormResult formResult in formResults)
    {
        var connection = new SqlConnection();
        connection.ConnectionString = connectionStringWFFM;
        var command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "select fieldid, value from field where formid=@formid order by fieldid";
        command.Parameters.Add("@formid", SqlDbType.UniqueIdentifier).Value = formResult.id;
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        DataRow dataRow = dt.NewRow();
        dataRow["Submission_DateTime"] = formResult.timestamp.ToString("MM/dd/yyyy HH:mm:ss");
        while (reader.Read())
        {
            dataRow["_" + reader.GetGuid(0).ToString("N")] = reader.GetValue(1).ToString().Replace("<item>", "").Replace("</item>", "");
        }
        dt.Rows.Add(dataRow);
        reader.Close();
        connection.Close();
    }
    return dt;
}
public List<Field> DistinctFields(Guid formitemid)
{
    var connection = new SqlConnection();
    connection.ConnectionString = connectionStringWFFM;
    var command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "select distinct fieldid from field where formid in (select id from form where formitemid=@formitemid) order by fieldid";
    command.Parameters.Add("@formitemid", SqlDbType.UniqueIdentifier).Value = formitemid;
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    var results = new List<Field>();
    int count = 0;
    while (reader.Read())
    {
        var field = new Field();
        field.id = reader.GetGuid(0);
        Database database = Factory.GetDatabase("master");
        Item i = database.GetItem(new ID(field.id));
        if (i != null && i.DisplayName != null)
        {
            field.name = i.DisplayName;
        }
        else
        {
            field.name = "Field" + count;
        }
        results.Add(field);
        count += 1;
    }
    reader.Close();
    connection.Close();
    return results;
}
public List<FormResult> FormResults(Guid formitemid)
{
    var connection = new SqlConnection();
    connection.ConnectionString = connectionStringWFFM;
    var command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "select id, timestamp from form where formitemid=@formitemid";
    command.Parameters.Add("@formitemid", SqlDbType.UniqueIdentifier).Value = formitemid;
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    var results = new List<FormResult>();
    while (reader.Read())
    {
        var result = new FormResult();
        result.id = reader.GetGuid(0);
        result.timestamp = reader.GetDateTime(1);
        results.Add(result);
    }
    reader.Close();
    connection.Close();
    return results;
}
public class FormResult
{
    public Guid id { get; set; }
    public DateTime timestamp { get; set; }
}
public class Field
{
    public Guid id { get; set; }
    public string name { get; set; }
}