MsSqlDatabase.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Waher.Runtime.Threading;
using Waher.Script.Abstraction.Elements;
using Waher.Script.Model;
 
namespace Waher.Script.Data.Model
{
    public class MsSqlDatabase : ExternalDatabase
    {
        private readonly Dictionary<string, MsSqlStoredProcedure> procedures = new Dictionary<string, MsSqlStoredProcedure>();
        private readonly MultiReadSingleWriteObject synchObject;
        private SqlConnection connection;
 
        public MsSqlDatabase(SqlConnection Connection)
        {
            this.synchObject = new MultiReadSingleWriteObject(this);
            this.connection = Connection;
        }
 
        public override void Dispose()
        {
            this.connection?.Close();
            this.connection?.Dispose();
            this.connection = null;
        }
 
        public override async Task<IElement> ExecuteSqlStatement(string Statement)
        {
            using (SqlCommand Command = this.connection.CreateCommand())
            {
                Command.CommandType = CommandType.Text;
                Command.CommandText = Statement;
                SqlDataReader Reader = await Command.ExecuteReaderAsync();
 
                return await Reader.ParseAndClose();
            }
        }
 
        public override Task<IElement> GetSchema(string Name)
        {
            DataTable Table = this.connection.GetSchema(Name);
            return Task.FromResult<IElement>(Table.ToMatrix());
        }
 
        public override async Task<ILambdaExpression> GetProcedure(string Name)
        {
            await this.synchObject.BeginWrite();
            try
            {
                if (this.procedures.TryGetValue(Name, out MsSqlStoredProcedure Result))
                    return Result;
 
                SqlCommand Command = this.connection.CreateCommand();
                Command.CommandType = CommandType.StoredProcedure;
                Command.CommandText = this.connection.Database + "." + Name;
 
                SqlCommandBuilder.DeriveParameters(Command);
 
                Result = new MsSqlStoredProcedure(Command);
                this.procedures[Name] = Result;
 
                return Result;
            }
            finally
            {
                await this.synchObject.EndWrite();
            }
        }
    }
}