为市场营销者数据库从sitecore读取数据

Question

我想要构建一个自定义接口(一个单独的aspx页面)来管理输入到市场营销人员(WFFM)数据库的webforms (WFFM)数据库中的数据，并且只管理一个表单中的数据。必须能够编辑数据并选择带有特定分类和分页的记录。数据库被配置为SQLite。

这是可能的，也是推荐的，还是仅仅将普通的xml保存到WFFM数据库中？我该怎么做呢？

Answer 1

这是完全可行的，尽管从WFFM获取数据的select查询有点古怪，因为所有内容都存储在一个名为" field“的大型表中，其中只有一个GUID跟踪，以便将存储的值与它们来自什么形式以及来自哪个字段联系起来。

下面是我为WFFM数据编写的Export实用程序的一部分。它根据提交的表单结果构建一个DataTable对象。不过，您可以将其调整为其他一些结构，而不需要太多的工作。

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; }
}