Last active
August 12, 2022 22:08
-
-
Save JustBarnt/90c19db8dc8d6680345669dafdeaf8e3 to your computer and use it in GitHub Desktop.
SqlBuilder and Adapter Sandbox
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment