/// <summary>
/// Throw if the database or SqlBotDataEntities table have not been created.
/// </summary>
static internal void AssertDatabaseReady()
{
var connectionString = Utils.GetAppSetting(AppSettingKeys.SqlServerConnectionString);
using (var context = new SqlBotDataContext(connectionString))
{
if (!context.Database.Exists())
{
throw new ArgumentException("The sql database defined in the connection has not been created. See https://github.com/Microsoft/BotBuilder-Azure/tree/master/CSharp");
}
if (context.Database.SqlQuery <int>(@"IF EXISTS (SELECT * FROM sys.tables WHERE name = 'SqlBotDataEntities')
SELECT 1
ELSE
SELECT 0").SingleOrDefault() != 1)
{
throw new ArgumentException("The SqlBotDataEntities table has not been created in the database. See https://github.com/Microsoft/BotBuilder-Azure/tree/master/CSharp");
}
}
}
async Task <BotData> IBotDataStore <BotData> .LoadAsync(IAddress key, BotStoreType botStoreType, CancellationToken cancellationToken)
{
using (var context = new SqlBotDataContext(_connectionString))
{
try
{
SqlBotDataEntity entity = await SqlBotDataEntity.GetSqlBotDataEntity(key, botStoreType, context);
if (entity == null)
{
// empty record ready to be saved
return(new BotData(eTag: String.Empty, data: null));
}
// return botdata
return(new BotData(entity.ETag, entity.GetData()));
}
catch (System.Data.SqlClient.SqlException err)
{
throw new HttpException((int)HttpStatusCode.InternalServerError, err.Message);
}
}
}
/// <summary>
/// Registers dependencies with the <paramref name="builder"/>.
/// </summary>
/// <param name="builder"> The container builder.</param>
protected override void Load(ContainerBuilder builder)
{
builder.RegisterType <ConnectorStore>()
.AsSelf()
.InstancePerLifetimeScope();
// if application settings indicate that bot should use the table storage,
// TableBotDataStore will be registered as underlying storage
// otherwise bot connector state service will be used.
if (ShouldUseTableStorage())
{
builder.Register(c => MakeTableBotDataStore())
.Keyed <IBotDataStore <BotData> >(Key_DataStore)
.AsSelf()
.SingleInstance();
}
else if (ShouldUseCosmosDb())
{
builder.Register(c => MakeCosmosDbBotDataStore())
.Keyed <IBotDataStore <BotData> >(Key_DataStore)
.AsSelf()
.SingleInstance();
}
else if (ShouldUseSqlServer())
{
SqlBotDataContext.AssertDatabaseReady();
builder.Register(c => MakeSqlBotDataStore())
.Keyed <IBotDataStore <BotData> >(Key_DataStore)
.AsSelf()
.SingleInstance();
}
else
{
builder.Register(c => new ConnectorStore(c.Resolve <IStateClient>()))
.Keyed <IBotDataStore <BotData> >(Key_DataStore)
.AsSelf()
.InstancePerLifetimeScope();
}
// register the data store with caching data store
// and set the consistency policy to be "Last write wins".
builder.Register(c => new CachingBotDataStore(c.ResolveKeyed <IBotDataStore <BotData> >(Key_DataStore),
CachingBotDataStoreConsistencyPolicy.LastWriteWins))
.As <IBotDataStore <BotData> >()
.AsSelf()
.InstancePerLifetimeScope();
// register the appropriate StateClient based on the state api url.
builder.Register(c =>
{
var activity = c.Resolve <IActivity>();
if (activity.ChannelId == "emulator")
{
// for emulator we should use serviceUri of the emulator for storage
return(new StateClient(new Uri(activity.ServiceUrl)));
}
MicrosoftAppCredentials.TrustServiceUrl(BotService.stateApi.Value, DateTime.MaxValue);
return(new StateClient(new Uri(BotService.stateApi.Value)));
})
.As <IStateClient>()
.InstancePerLifetimeScope();
// register the bot service serialization binder for type mapping to current assembly
builder.Register(c => new BotServiceSerializationBinder(assembly))
.AsSelf()
.As <SerializationBinder>()
.InstancePerLifetimeScope();
// register the Delegate surrogate provide to map delegate to current assembly during deserialization
builder
.Register(c => new BotServiceDelegateSurrogate(assembly))
.AsSelf()
.InstancePerLifetimeScope();
// extend surrogate providers with bot service delegate surrogate provider and register the surrogate selector
builder
.Register(c =>
{
var providers = c.ResolveKeyed <IEnumerable <Serialization.ISurrogateProvider> >(FiberModule.Key_SurrogateProvider).ToList();
// need to add the latest delegate surrogate to make sure that surrogate selector
// can deal with latest assembly
providers.Add(c.Resolve <BotServiceDelegateSurrogate>());
return(new Serialization.SurrogateSelector(providers));
})
.As <ISurrogateSelector>()
.InstancePerLifetimeScope();
// register binary formatter used for binary serialization operation
builder
.Register((c, p) => new BinaryFormatter(c.Resolve <ISurrogateSelector>(), new StreamingContext(StreamingContextStates.All, c.Resolve <IResolver>(p)))
{
AssemblyFormat = FormatterAssemblyStyle.Simple,
Binder = c.Resolve <SerializationBinder>()
})
.As <IFormatter>()
.InstancePerLifetimeScope();
}
async Task IBotDataStore <BotData> .SaveAsync(IAddress key, BotStoreType botStoreType, BotData botData, CancellationToken cancellationToken)
{
SqlBotDataEntity entity = new SqlBotDataEntity(botStoreType, key.BotId, key.ChannelId, key.ConversationId, key.UserId, botData.Data)
{
ETag = botData.ETag,
ServiceUrl = key.ServiceUrl
};
using (var context = new SqlBotDataContext(_connectionString))
{
try
{
if (string.IsNullOrEmpty(botData.ETag))
{
context.BotData.Add(entity);
}
else if (entity.ETag == "*")
{
var foundData = await SqlBotDataEntity.GetSqlBotDataEntity(key, botStoreType, context);
if (botData.Data != null)
{
if (foundData == null)
{
context.BotData.Add(entity);
}
else
{
foundData.Data = entity.Data;
foundData.ServiceUrl = entity.ServiceUrl;
}
}
else
{
if (foundData != null)
{
context.BotData.Remove(foundData);
}
}
}
else
{
var foundData = await SqlBotDataEntity.GetSqlBotDataEntity(key, botStoreType, context);
if (botData.Data != null)
{
if (foundData == null)
{
context.BotData.Add(entity);
}
else
{
foundData.Data = entity.Data;
foundData.ServiceUrl = entity.ServiceUrl;
foundData.ETag = entity.ETag;
}
}
else
{
if (foundData != null)
{
context.BotData.Remove(foundData);
}
}
}
await context.SaveChangesAsync();
}
catch (System.Data.SqlClient.SqlException err)
{
throw new HttpException((int)HttpStatusCode.InternalServerError, err.Message);
}
}
}
internal static async Task <SqlBotDataEntity> GetSqlBotDataEntity(IAddress key, BotStoreType botStoreType, SqlBotDataContext context)
{
SqlBotDataEntity entity = null;
var query = context.BotData.OrderByDescending(d => d.Timestamp);
switch (botStoreType)
{
case BotStoreType.BotConversationData:
entity = await query.FirstOrDefaultAsync(d => d.BotStoreType == botStoreType &&
d.ChannelId == key.ChannelId &&
d.ConversationId == key.ConversationId);
break;
case BotStoreType.BotUserData:
entity = await query.FirstOrDefaultAsync(d => d.BotStoreType == botStoreType &&
d.ChannelId == key.ChannelId &&
d.UserId == key.UserId);
break;
case BotStoreType.BotPrivateConversationData:
entity = await query.FirstOrDefaultAsync(d => d.BotStoreType == botStoreType &&
d.ChannelId == key.ChannelId &&
d.ConversationId == key.ConversationId &&
d.UserId == key.UserId);
break;
default:
throw new ArgumentException("Unsupported bot store type!");
}
return(entity);
}
