using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Dapper;
using Newtonsoft.Json;
// https://www.youtube.com/watch?v=ayp3tHEkRc0 // Simple Dapper
// https://www.youtube.com/watch?v=eKkh5Xm0OlU // Advaned Dapper
// https://stackoverflow.com/questions/9218847/how-do-i-handle-database-connections-with-dapper-in-net
namespace TopNugetPackages
{
#region DB table schema model
public class CyrptoPairModel
{
public int id { get; set; }
public string LocalProd { get; set; }
public string RemoteProd { get; set; }
public double Multiplier { get; set; }
}
public class OrderIdMapModel
{
public int id { get; set; }
public string prod_id { get; set; }
public string acc_no { get; set; }
public string acc_ord_no { get; set; }
public string client_ord_id { get; set; }
public string order_id { get; set; }
public string ext_ord_no { get; set; }
public int last_req_no { get; set; }
public DateTime update_time { get; set; }
}
public class PhoneModel
{
public int Id { get; set; }
public string PhoneNumber { get; set; }
}
public class PersonModel
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public PhoneModel CellPhone { get; set; }
}
#endregion
public class MainDapperDriver
{
// connection string
private readonly string connstr = @"DataSource=..\..\..\Resources\Database.db;Version=3;";
public MainDapperDriver()
{ }
public void CreateTable()
{
using (var conn = new SQLiteConnection(connstr))
{
conn.Execute(
@"CREATE TABLE IF NOT EXISTS Customer (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName varchar(100) not null,
LastName varchar(100) not null,
DateOfBirth datetime not null
)");
}
}
public void DropTable()
{
using (var conn = new SQLiteConnection(connstr))
{
conn.Execute("DROP TABLE IF EXISTS Customer");
}
}
// demonstration to load info from db
private List<CyrptoPairModel> LoadCryptoPair()
{
using (var conn = new SQLiteConnection(connstr))
{
var resultset = conn.Query<CyrptoPairModel>("SELECT * FROM ExchangeCryptoPair", new DynamicParameters());
return resultset.ToList();
}
}
// demonstration to load info from db
private List<OrderIdMapModel> LoadOrderIdMap()
{
using (var conn = new SQLiteConnection(connstr))
{
var resultset = conn.Query<OrderIdMapModel>("SELECT * FROM OrderIdMap_TBL", new DynamicParameters());
return resultset.ToList();
}
}
// demonstration to update data in DB
private void UpdateOrderIdMap(OrderIdMapModel orderModel)
{
using (var conn = new SQLiteConnection(connstr))
{
conn.Execute("INSERT INTO OrderIdMap_TBL (id, prod_id, acc_no, last_req_no) " +
"VALUES (@id, @prod_id, @acc_no, @last_req_no)", orderModel);
}
}
private List<PersonModel> MapMultipleObject()
{
using (var conn = new SQLiteConnection(connstr))
{
var sql = "SELECT pe.*, ph.* FROM Person pe JOIN Phone ph ON pe.Id = ph.Id";
var resultset = conn.Query<PersonModel, PhoneModel, PersonModel>(sql,
(person, phone) => { person.CellPhone = phone; return person; });
return resultset.ToList();
}
}
private List<PersonModel> MapMultipleObjectWithParam(int id)
{
using (var conn = new SQLiteConnection(connstr))
{
var p = new { Id = id };
var sql = "SELECT pe.*, ph.* FROM Person pe JOIN Phone ph ON pe.Id = ph.Id WHERE pe.Id = @Id";
var resultset = conn.Query<PersonModel, PhoneModel, PersonModel>(sql,
(person, phone) => { person.CellPhone = phone; return person; }, p);
return resultset.ToList();
}
}
private (List<PersonModel>, List<PhoneModel>) MapMultipleSets()
{
using (var conn = new SQLiteConnection(connstr))
{
var sql = @"SELECT * FROM Person;
SELECT * FROM Phone;";
List<PersonModel> people = null;
List<PhoneModel> phones = null;
using (var lists = conn.QueryMultiple(sql))
{
people = lists.Read<PersonModel>().ToList();
phones = lists.Read<PhoneModel>().ToList();
}
return (people, phones);
}
}
private (List<PersonModel>, List<PhoneModel>) MapMultipleSetsWithParam(string name, string phonenumber)
{
using (var conn = new SQLiteConnection(connstr))
{
var p = new { FirstName = name, PhoneNumber = phonenumber };
var sql = @"SELECT * FROM Person WHERE FirstName = @FirstName;
SELECT * FROM Phone WHERE PhoneNumber = @PhoneNumber";
List<PersonModel> people = null;
List<PhoneModel> phones = null;
using (var lists = conn.QueryMultiple(sql, p))
{
people = lists.Read<PersonModel>().ToList();
phones = lists.Read<PhoneModel>().ToList();
}
return (people, phones);
}
}
// Does NOT work because ID was not created as an Identity
private int GetLastInsertedId(string first, string last)
{
using (var conn = new SQLiteConnection(connstr))
{
var p = new DynamicParameters();
p.Add("@Id", 10, DbType.Int32, ParameterDirection.Output);
p.Add("@FirstName", first);
p.Add("@LastName", last);
var sql = $@"INSERT INTO Person (FirstName, LastName) VALUES
(@FirstName, @LastName);
SELECT @Id = @@IDENTITY";
conn.Execute(sql, p);
var newId = p.Get<int>("@Id");
return newId;
}
}
//// Assume the following stored precedure exist
//CREATE PROCEDURE [spPerson_InsertSet]
// @people BasicUDT readonly
//AS
//BEGIN
// INSERT INTO Person(FirstName, LastName)
// SELECT[FirstName], [LastName]
// FROM @people;
//END
// SQLite doesnt support Stored Procedure, here we assume it does
// Method does not work as is.
private int InsertDataSetWithStoredProcedure()
{
using (var conn = new SQLiteConnection(connstr))
{
var dataset = GetDataSets();
var p = new
{
people = dataset.AsTableValuedParameter("BasicUDT")
};
return conn.Execute("spPerson_InsertSet", p, commandType: CommandType.StoredProcedure);
}
}
private DataTable GetDataSets()
{
var output = new DataTable();
output.Columns.Add("FirstName", typeof(string));
output.Columns.Add("LastName", typeof(string));
output.Rows.Add("Name1", "123456");
output.Rows.Add("Name2", "123456");
output.Rows.Add("Name3", "123456");
output.Rows.Add("Name4", "123456");
output.Rows.Add("Name5", "123456");
output.Rows.Add("Name6", "123456");
output.Rows.Add("Name7", "123456");
output.Rows.Add("Name8", "123456");
return output;
}
private void DisplayObjectInJson(object obj)
{
var json = JsonConvert.SerializeObject(obj, Formatting.Indented);
Console.WriteLine(json);
}
// Main driver of Dapper application
public void DoIt()
{
//First, select data from DB and display it
DisplayObjectInJson(LoadCryptoPair());
DisplayObjectInJson(LoadOrderIdMap());
DisplayObjectInJson(MapMultipleObject());
DisplayObjectInJson(MapMultipleObjectWithParam(1));
DisplayObjectInJson(MapMultipleSets());
DisplayObjectInJson(MapMultipleSetsWithParam("TIM", "88888888"));
//GetLastInsertedId("NEW", "NAME");
//// prepare new data to add into db
//var oid = new OrderIdMapModel
//{
// id = 10,
// prod_id = "NEW_PROD",
// acc_no = "TEST_1",
// last_req_no = 101
//};
//// Update data into DB and display again with new data added
//UpdateOrderIdMap(oid);
//DisplayObjectInJson(LoadOrderIdMap());
// Calls store precedure to do mass insert at once
//InsertDataSetWithStoredProcedure();
//DisplayObjectInJson(MapMultipleObject());
// Create and delete table
CreateTable();
DropTable();
}
}
}