This project is read-only.

SqlMailingListProvider

This provider uses relational database to store its data. The provider is written to be database agnostic and should support any database server with ADO.NET provider, such as Microsoft SQL Server, SQL Server Compact, Oracle or MySQL.

The configuration attributes of add elements are:
Name Description
name The logical system name of mailing list. Should not contain spaces or special characters. Is used to identify the list, to generate confirmation codes etc. Should not be changed once set up.
description The free-text friendly name of mailing list
type Type name of the mailing list provider. Currently only one provider (using SQL database) is part of the toolkit and its type name is Altairis.MailToolkit.SqlMailingListProvider, Altairis.MailToolkit
subscriptionVerificationTemplate (optional) Name of template used for subscription verification message. Defaults to ListSubscriptionVerification.
removalVerificationTemplate (optional) Name of template used for removal verification message. Defaults to ListRemovalVerification.
messageTemplate (optional) Name of template used for messages sent to list. Defaults to ListMessage.
connectionStringName Name of connection string defined in connectionStrings section. This connection string must have providerName attribute defined.
tableName (optional) Name of table used to store mailing list data. Defaults to MailingLists. See below for required table structure.
emailAddressColumn (optional) Name of table column, where e-mail address is stored. Defaults to EmailAddress.
listNameColumn (optional) Name of table column, where list name is stored. Defaults to ListName.
dateConfirmedColumn (optional) Name of table column, where date of confirmation is stored. Defaults to DateConfirmed.
ipAddressColumn (optional) Name of table column, where IP address from where confirmation came is stored. Defaults to IpAddress.
useExtendedProperties (optional) When set to true, confirmation date and IP address are stored in database. Defaults to false.


See the Configuration and Configuring list manager pages for general info about configuration.

Defining the table structure

Table and column names can be changed via the above specified configuration settings. The samples below are using default names.

Minimal configuration

The table must have two columns defined:
  • ListName - string, name of the mailing list user is subscribed to (single table can hold several mailing lists)
  • EmailAddress - string, the e-mail address being subscribed
The table can contain any other columns, as long as they are nullable or have default values. The provider would ignore these additional columns.

The following SQL script may be used to create the table on Microsoft SQL Server 2008 (the primary key is not required, but highly recommended):
CREATE TABLE MailingLists (
    ListName      nvarchar(100)  not null,
    EmailAddress  nvarchar(100)  not null
)

ALTER TABLE MailingLists ADD CONSTRAINT PK_MailingLists PRIMARY KEY CLUSTERED (ListName,EmailAddress)

Extended configuration

When useExtendedProperties is set to true in configuration, this provider stores the date when subscription request was confirmed and also IP address from which the confirmation came. This may prove handy in case of later disputes if the user really subscribed or not. In such case, the table must have at least four columns defined:
  • ListName - string, name of the mailing list user is subscribed to (single table can hold several mailing lists)
  • EmailAddress - string, the e-mail address being subscribed
  • DateConfirmed - datetime, date of confirmation
  • IpAddress - string, the IP address where confirmation came from
The table can contain any other columns, as long as they are nullable or have default values. The provider would ignore these additional columns.

The following SQL script may be used to create the table on Microsoft SQL Server 2008 (the primary key is not required, but highly recommended):
CREATE TABLE MailingLists (
    ListName      nvarchar(100)  not null,
    EmailAddress  nvarchar(100)  not null,
    DateConfirmed datetime       not null,
    IpAddress     nvarchar(39)   not null
)

ALTER TABLE MailingLists ADD CONSTRAINT PK_MailingLists PRIMARY KEY CLUSTERED (ListName,EmailAddress)

Last edited Dec 24, 2011 at 8:11 PM by altair, version 2

Comments

No comments yet.