using Microsoft.Data.SqlClient;

using System.Data;

namespace ScratchBoardCSharp;

public class Program
{
	static void Main(string[] args)
	{
		Database db = new Database("host", "database", "user", "pass");
		string connectionString = db.ToString();
		string query = "SELECT TOP(5) {c1}, {c2}, {c3} FROM {table}";

		DataSet data = CreateSqlCommand(connectionString, query);
		List <DbData> dbData = DataSetToList(data);
	}

	static DataSet CreateSqlCommand(string connectionString, string query)
	{
		using(SqlConnection connection = new SqlConnection(connectionString))
		{
			SqlDataAdapter adapter = new SqlDataAdapter();
			DataSet dataSet = new DataSet();
			SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

			//adapter.InsertCommand/UpdateCommand/DeleteCommand
			connection.Open();
			adapter.SelectCommand = new SqlCommand(query, connection);
			adapter.Fill(dataSet);

			//NOTE: FOR INSERT, UPDATE, or DELETE
			//Making changes to table make you changes above these two lines
			//then uncomment them.
			//builder.GetUpdateCommand()/GetDeleteCommand/GetInsertCommand
			//adapter.Update(dataset, table)

			return dataSet;
		}
	}

	static List<DbData> DataSetToList(DataSet data)
	{
		List<DbData> dbData = data.Tables[0].AsEnumerable()
			.Select(dataRow => new DbData
			{
				Title = dataRow.Field<string>("Title"),
				Quantity = dataRow.Field<decimal>("Quantity"),
				Message = dataRow.Field<string>("Message"),
				City = dataRow.Field<string>("City"),
			}).ToList();

		return list;
	}
}

public class DbData
{
	public string? Title { get; set; }
	public decimal? Quantity { get; set; }
	public string? Message { get; set; }
	public string? City { get; set; }
}


public class Database
{
	public string Host { get; set; }
	public string DatabaseName { get; set; }
	public string UserName { get; set; }
	public string Password { get; set; }

	public Database(string host, string databaseName, string user, string password)
	{
		Host = host;
		DatabaseName = databaseName;
		UserName = user;
		Password = password;
	}

	public override string ToString()
	{
		var builder = new SqlConnectionStringBuilder()
		{
			DataSource = Host,
			InitialCatalog = DatabaseName,
			UserID = UserName,
			Password = Password,
			TrustServerCertificate = true
		};

		return builder.ToString();
	}
}