Skip to content

Instantly share code, notes, and snippets.

@JustBarnt

JustBarnt/sqlBuilderClass.cs

Last active August 12, 2022 22:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save JustBarnt/90c19db8dc8d6680345669dafdeaf8e3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save JustBarnt/90c19db8dc8d6680345669dafdeaf8e3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. JustBarnt revised this gist Aug 12, 2022. No changes.
  2. JustBarnt created this gist Aug 12, 2022.
    94 changes: 94 additions & 0 deletions sqlBuilderClass.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,94 @@
    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();
    }
    }