IDataSource

public interface IDataSource
{
	string QueryKey { get; }
	DataSourceCapabilities Capabilities { get; }
	Task<DataSourceItemsResult> GetAllAsync(Paging pagingInfo, SortInfo sortingInfo, Filter filterInfo, Dictionary<string, object> parameters);
	Task<DataSourceItem> GetSingleAsync(string identifier);
	IEnumerable<string> ValidateSettings();
}

public record SqlField : DataSourceField
	{
		[EditorBrowsable]
		public bool KeyField { get; set; }
	}

	[DisplayName("SQL Data source")]
	[Description("Can execute an SQL select query on the database specified by the given database connection string")]
	public class SQLQueryDataSource : IDataSource
	{
		private readonly IConfiguration _configuration;

		public string QueryKey => (SQLQuery ?? "").ToGuid().ToString();

		public DataSourceCapabilities Capabilities => DataSourceCapabilities.ReturnList;

		[EditorBrowsable]
		[DisplayName("Query")]
		[Description("SQL query to execute on the database")]
		[EditorAttribute(PropertyEditors.TextEditor, "")]
		public string SQLQuery { get; set; }

		[EditorBrowsable]
		[DisplayName("Connection string name")]
		[Description("Specify the name of the connection string to use.  Make sure that the DB connection string has been specified in the appsettings.config file.")]
		public string ConnectionStringName { get; set; }

		[EditorBrowsable]
		[DisplayName("Fields")]
		[Description("Define fields and types")]
		public List<SqlField> Fields { get; set; }

		public SQLQueryDataSource(IConfiguration configuration)
		{
			_configuration = configuration;
		}

		public async Task<DataSourceItemsResult> GetAllAsync(Paging pagingInfo, SortInfo sortingInfo, Filter filterInfo, Dictionary<string, object> parameters)
		{
			var connectionString = _configuration.GetConnectionString(ConnectionStringName);

			SqlCommand cmd = new SqlCommand(SQLQuery);

			using (var dt = ExecuteRawSQL(cmd, connectionString))
			{
				DataSourceItemsResult result = new DataSourceItemsResult();

				List<DataSourceItem> items = new List<DataSourceItem>();

				var identifierField = Fields != null ? Fields.Where(x => x.KeyField).FirstOrDefault()?.Name : "";

				if (dt.Rows.Count > 0)
				{
					if (Fields == null || (Fields != null && !Fields.Any()))
					{
						Fields = new List<SqlField>();
						for (int colIdx = 0; colIdx < dt.Columns.Count; colIdx++)
						{
							Fields.Add(new SqlField() { FieldType = DataSourceFieldType.String, KeyField = false, Name = dt.Columns[colIdx].ToString(), Title = dt.Columns[colIdx].ToString() });
						}
					}

					for (int rowIdx = 0; rowIdx < dt.Rows.Count; rowIdx++)
					{
						var row = dt.Rows[rowIdx];

						DataSourceItem item = new DataSourceItem()
						{
							Values = new Dictionary<string, object>()
						};

						for (int colIdx = 0; colIdx < dt.Columns.Count; colIdx++)
						{
							item.Values.Add(dt.Columns[colIdx].ToString(), row[colIdx].ToString());
						}

						if (!string.IsNullOrEmpty(identifierField))
						{
							item.Identifier = row[identifierField] != null ? row[identifierField].ToString() : "";
						}

						items.Add(item);
					}
				}

				result.Items = items;
				result.TotalItems = items.Count;
				result.Fields = Fields;

				return result;
			}
		}

		public Task<DataSourceItem> GetSingleAsync(string identifier)
		{
			return null;
		}

		public IEnumerable<string> ValidateSettings()
		{
			List<string> messages = new List<string>();

			if (string.IsNullOrEmpty(ConnectionStringName))
			{
				messages.Add("No connection string has been specified!");
			}
			else
			{
				if (string.IsNullOrEmpty(_configuration.GetConnectionString(ConnectionStringName)))
				{
					messages.Add($"The specified connection string ({ConnectionStringName}) was not found or is not set!");
				}
			}

			if (string.IsNullOrEmpty(SQLQuery))
			{
				messages.Add("No SQL Query has been specified!");
			}
			else
			{
				// Naive validation ... make more secure maybe?
				if (!SQLQuery.ToLower().StartsWith("select") || SQLQuery.ToLower().Contains(" insert ") || SQLQuery.ToLower().Contains(" update ") || SQLQuery.ToLower().Contains(" delete "))
				{
					messages.Add("Only select statements are supported!");
				}
			}

			return messages;
		}

		private DataTable ExecuteRawSQL(SqlCommand command, string connectionString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				command.Connection = connection;

				DataTable table = new DataTable("Table");

				using (SqlDataAdapter adapter = new SqlDataAdapter(command))
				{
					adapter.Fill(table);
				}
				return table;
			}
		}

public void ConfigureServices(IServiceCollection services, IConfiguration configuration)
{
	services.AddNamed<IDataSource, HTTPDataSource>("HTTPDataSource", ServiceLifetime.Transient);
}