public void Adds_New_Record()
{
// How many to start with?
int initialCount = _Clients.Count();
var newClient = new Client() { FirstName = "John", LastName = "Atten", Email = "*****@*****.**" };
_Clients.Add(newClient);
int idToFind = newClient.ClientId;
_Clients = new SQLServerList<Client>(connectionStringName: _connectionStringName, tableName: "Clients", primaryKeyName: "ClientId");
var found = _Clients.FirstOrDefault(c => c.ClientId == idToFind);
Assert.True(found.Email == "*****@*****.**" && _Clients.Count > initialCount);
}
public static void Run()
{
var sw = new Stopwatch();
Console.WriteLine("Loading up tracks from Chinook...");
sw.Start();
//use the dvds db
var films = new SQLServerList <Track>("chinook", "track", "trackid");
sw.Stop();
Console.WriteLine("Loaded {0} records in {1}ms", films.Count(), sw.ElapsedMilliseconds);
}
public SQLServerList <Transaction> ReadTransactionTable()
{
Console.WriteLine(Environment.NewLine);
Console.WriteLine("READ FROM TRANSACTIONS TABLE");
var sw = new Stopwatch();
sw.Start();
var transactions = new SQLServerList <Transaction>(_connectionString, _tableName, _tablePkName);
sw.Stop();
this.LogOutput("Read", transactions.Count, sw.ElapsedMilliseconds);
return(transactions);
}
public void MemoryDataExcercises(int qtyRecords)
{
// Start from fresh:
this.DropTransctionTable();
this.CreateTransctionTable();
// Load the empty table:
var transactions = new SQLServerList <Transaction>(_connectionString, _tableName, _tablePkName);
transactions.Clear();
var sw = new Stopwatch();
sw.Start();
// Insert a bunch of records:
var data = this.getSkinnyTransactionSet(qtyRecords);
transactions.AddRange(data);
sw.Stop();
this.LogOutput("Wrote", qtyRecords, sw.ElapsedMilliseconds);
transactions = this.ReadTransactionTable();
sw.Reset();
sw.Start();
// Find a record by arbitrary field (NOT the PK):
var item = transactions.First(t => t.Identifier == "AA-9000");
sw.Stop();
this.LogOutput("Found single by field content", 1, sw.ElapsedMilliseconds);
this.LogOutput("Found item: " + item.Identifier);
sw.Reset();
sw.Start();
// Query against some criteria:
var query = from t in transactions where (t.Amount > 100 && t.Amount < 150) select t;
this.LogOutput("Read queried values from memory", query.Count(), sw.ElapsedMilliseconds);
Console.WriteLine("Queried Values:");
foreach (var trans in query)
{
this.LogOutput("Id: " + trans.TransactionId + " Comment: " + trans.Comment + " Amount: " + trans.Amount);
}
sw.Stop();
this.LogOutput("Wrote queried values out to console", query.Count(), sw.ElapsedMilliseconds);
}
public void Adds_New_Record()
{
int initialCount = _MismatchedClients.Count;
var newMonkey = new MismatchedClient() {
Last = "Jimbo",
First = "Jones",
EmailAddress = "*****@*****.**"
};
_MismatchedClients.Add(newMonkey);
int newID = newMonkey.Id;
_MismatchedClients = new SQLServerList<MismatchedClient>(_connectionStringName, "WTF");
var found = _MismatchedClients.FirstOrDefault(c => c.Id == newID);
Assert.True(found.Id == newID && _MismatchedClients.Count > initialCount);
}
public void Adds_New_Record()
{
int initialCount = _clients.Count;
var newMonkey = new Client() {
LastName = "Jimbo",
FirstName = "Jones",
Email = "*****@*****.**"
};
_clients.Add(newMonkey);
int newID = newMonkey.ClientId;
_clients = new SQLServerList<Client>(_connectionStringName, "WTF", "Client_Id");
var found = _clients.FirstOrDefault(c => c.ClientId == newID);
Assert.True(found.ClientId == newID && _clients.Count > initialCount);
}
public void WriteBulkTransactions(List <Transaction> newTransactions)
{
Console.WriteLine(Environment.NewLine);
Console.WriteLine("WRITE BULK TRANSACTIONS");
int qty = newTransactions.Count;
var sw = new Stopwatch();
sw.Start();
var transactions = new SQLServerList <Transaction>(_connectionString, _tableName, _tablePkName);
sw.Stop();
this.LogOutput("Read", transactions.Count, sw.ElapsedMilliseconds);
sw.Reset();
sw.Start();
int added = transactions.AddRange(newTransactions);
sw.Stop();
this.LogOutput("Wrote", newTransactions.Count, sw.ElapsedMilliseconds);
}
public void ClearTransactionTable()
{
Console.WriteLine(Environment.NewLine);
Console.WriteLine("CLEAR TRANSACTIONS TABLE");
var sw = new Stopwatch();
sw.Start();
var transactions = new SQLServerList <Transaction>(_connectionString, _tableName, _tablePkName);
int count = transactions.Count;
sw.Stop();
this.LogOutput("Read", count, sw.ElapsedMilliseconds);
sw.Reset();
sw.Start();
transactions.Clear();
sw.Stop();
this.LogOutput("Cleared", count, sw.ElapsedMilliseconds);
}
public void SlowWriteTransactions(List <Transaction> newTransactions)
{
Console.WriteLine(Environment.NewLine);
Console.WriteLine("WRITE LOOPED TRANSACTIONS");
var sw = new Stopwatch();
sw.Start();
int qty = newTransactions.Count;
var transactions = new SQLServerList <Transaction>(_connectionString, _tableName, _tablePkName);
sw.Stop();
this.LogOutput("Read", transactions.Count, sw.ElapsedMilliseconds);
sw.Reset();
sw.Start();
foreach (var newTransaction in newTransactions)
{
transactions.Add((Transaction)newTransaction);
}
sw.Stop();
this.LogOutput("Wrote", newTransactions.Count, sw.ElapsedMilliseconds);
}
public void PullsThingsDynamically()
{
var list = new SQLServerList<dynamic>(_connectionStringName);
var results = list.Query(@"select Artist.Name AS ArtistName, Track.Name, Track.UnitPrice
from Artist inner join
Album on Artist.ArtistId = Album.ArtistId inner join
Track on Album.AlbumId = Track.AlbumId
where (Artist.Name = @0)", "ac/dc");
Assert.True(results.Count() > 0);
}
public void Updates_Record()
{
var newMonkey = new Client() {
LastName = "Jones",
FirstName = "Davey",
Email = "*****@*****.**"
};
_clients.Add(newMonkey);
int currentCount = _clients.Count;
int newID = newMonkey.ClientId;
_clients = new SQLServerList<Client>(_connectionStringName, "WTF", "Client_Id");
var found = _clients.FirstOrDefault(c => c.ClientId == newID);
found.FirstName = "Mick";
_clients.Update(found);
Assert.True(found.ClientId == newID && _clients.Count == currentCount);
}
public SQLServerList_Column_Mapping()
{
// Set up a table with mangled column names:
this.SetUpWTFTable();
_clients = new SQLServerList<Client>(_connectionStringName, "WTF", "Client_Id");
}
public void _Loads_Empty_List_Into_Memory()
{
_transactions = new SQLServerList <Transaction>(_connectionStringName, _testTableName, _tablePkColumn);
Assert.True(_transactions != null && _transactions.Count() == 0);
}
public MassiveList()
{
// Make sure tests can run independently:
_setup.CheckSetUp();
_transactions = new SQLServerList <Transaction>(_connectionStringName, _testTableName, _tablePkColumn);
}
public SQLServerList_Attribute_Mapping()
{
// Set up a table with mangled column names:
this.SetUpWTFTable();
_MismatchedClients = new SQLServerList<MismatchedClient>(_connectionStringName, "WTF", "Client_Id");
}
public static void Run()
{
var sw = new Stopwatch();
Console.WriteLine("===========================================================");
Console.WriteLine("SQL SERVER - SOME FANCY QUERYING");
Console.WriteLine("===========================================================");
Console.WriteLine("Loading up Artists from Chinook...");
sw.Start();
var _artists = new SQLServerList<Artist>(_connectionStringName, "artist");
sw.Stop();
Console.WriteLine("\tLoaded {0} Artist records in {1} ms", _artists.Count(), sw.ElapsedMilliseconds);
Console.WriteLine("Loading up Albums from Chinook...");
sw.Reset();
sw.Start();
var _albums = new SQLServerList<Album>(_connectionStringName, "album");
sw.Stop();
Console.WriteLine("\tLoaded {0} Albums in {1} ms", _artists.Count(), sw.ElapsedMilliseconds);
Console.WriteLine("Loading up tracks from Chinook...");
sw.Reset();
sw.Start();
var _tracks = new SQLServerList<Track>(_connectionStringName, "track");
sw.Stop();
Console.WriteLine("\tLoaded {0} Tracks in {1} ms", _tracks.Count(), sw.ElapsedMilliseconds);
Console.WriteLine("Grab the record for AC/DC...");
sw.Reset();
sw.Start();
var acdc = _artists.FirstOrDefault(a => a.Name == "AC/DC");
sw.Stop();
Console.WriteLine("\tFound AC/DC from memory in {0} ms", sw.ElapsedMilliseconds);
Console.WriteLine("Find all the albums by AC/DC ...");
sw.Reset();
sw.Start();
var acdcAlbums = _albums.Where(a => a.ArtistId == acdc.ArtistId);
sw.Stop();
Console.WriteLine("\tFound All {0} AC/DC albums from memory in {1} ms", acdcAlbums.Count(), sw.ElapsedMilliseconds);
Console.WriteLine("Find all the Tracks from Albums by AC/DC ...");
sw.Reset();
sw.Start();
var acdcTracks = from t in _tracks
join a in acdcAlbums on t.AlbumId equals a.AlbumId
select t;
sw.Stop();
Console.WriteLine("\tFound All {0} tracks by ACDC using in-memory JOIN in {1} ms:", acdcTracks.Count(), sw.ElapsedMilliseconds);
foreach (var track in acdcTracks)
{
Console.WriteLine("\t-{0}", track.Name);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("===========================================================");
Console.WriteLine("SQL SERVER - BASIC CRUD OPERATIONS");
Console.WriteLine("===========================================================");
sw.Reset();
Console.WriteLine("Loading up customers from Chinook...");
sw.Start();
var customers = new SQLServerList<Customer>(_connectionStringName, "customer");
sw.Stop();
Console.WriteLine("\tLoaded {0} records in {1}ms", customers.Count(), sw.ElapsedMilliseconds);
sw.Reset();
Console.WriteLine("INSERTING a NEW Customer into Chinook...");
var newCustomer = new Customer() { LastName = "Atten", FirstName = "John", Email = "*****@*****.**" };
sw.Start();
customers.Add(newCustomer);
sw.Stop();
Console.WriteLine("\tWrote 1 record for a new count of {0} records in {1} ms", customers.Count(), sw.ElapsedMilliseconds);
sw.Reset();
Console.WriteLine("UPDATING the new Customer record in Chinook...");
newCustomer.FirstName = "Fred";
sw.Start();
customers.Update(newCustomer);
sw.Stop();
Console.WriteLine("\tUpdated 1 record for a new count of {0} records in {1} ms", customers.Count(), sw.ElapsedMilliseconds);
sw.Reset();
Console.WriteLine("DELETE the new Customer record in Chinook...");
sw.Start();
customers.Remove(newCustomer);
sw.Stop();
Console.WriteLine("\tDeleted 1 record for a new count of {0} records in {1} ms", customers.Count(), sw.ElapsedMilliseconds);
Console.WriteLine(Environment.NewLine);
Console.WriteLine("===========================================================");
Console.WriteLine("SQL SERVER - BULK INSERTS AND DELETIONS");
Console.WriteLine("===========================================================");
Console.WriteLine("Creating Test Table...");
Benchmarks.SetUpClientTable();
sw.Reset();
int INSERT_QTY = 10000;
Console.WriteLine("BULK INSERTING {0} client records in Chinook...", INSERT_QTY);
var _clients = new SQLServerList<Client>(_connectionStringName, "Clients");
var inserts = new List<Client>();
for (int i = 0; i < INSERT_QTY; i++) {
inserts.Add(new Client() { LastName = string.Format("Atten {0}", i.ToString()), FirstName = "John", Email = "*****@*****.**" });
}
sw.Start();
var inserted = _clients.AddRange(inserts);
sw.Stop();
Console.WriteLine("\tInserted {0} records in {1} ms", inserted, sw.ElapsedMilliseconds);
sw.Reset();
Console.WriteLine("Loading up Bulk inserted CLients from Chinook...");
sw.Start();
_clients = new SQLServerList<Client>(_connectionStringName, "clients");
sw.Stop();
Console.WriteLine("\tLoaded {0} records in {1}ms", _clients.Count(), sw.ElapsedMilliseconds);
sw.Reset();
Console.WriteLine("DELETING added records from Chinook...");
var toRemove = _clients.Where(x => x.Email == "*****@*****.**");
sw.Start();
int removed = _clients.RemoveSet(toRemove);
sw.Stop();
Console.WriteLine("\tDeleted {0} records in {1}ms", removed, sw.ElapsedMilliseconds);
}
// Runs before every test:
public SQLServerList_CRUD()
{
// Drops and re-creates table each time:
this.SetUpClientTable();
_Clients = new SQLServerList<Client>(connectionStringName: _connectionStringName, tableName: "Clients", primaryKeyName: "ClientId");
}
