void RunVendorsTest()
{
Connection conn = new Connection(@"localhost\sqlexpress", "PrsDb");
conn.Open();
Vendors.Connection = conn;
//GETALL
var vendors = Vendors.GetAll();
foreach (var ven in vendors)
{
Console.WriteLine(ven);
}
//GetByPk
Vendors vendor = Vendors.GetByPk(2); //assignment AND method call
Console.WriteLine($"Vendor line deleted? {Vendors.Delete(3)}");
//UPDATE
Vendors vendorV = Vendors.GetByPk(4);
vendorV.Name = "Doggie Bone";
vendorV.State = "SD";
bool success = Vendors.Update(vendorV);
Console.WriteLine($" {(success ? "Update successful" : "Update failed")}");
//INSERT
Vendors vendorA = new Vendors(); // accesses default constructor in Vendors
vendorA.Code = "MAGC";
vendorA.Name = "Magic R Us";
vendorA.Address = "111 Elm";
vendorA.City = "Columbus";
vendorA.State = "OH";
vendorA.Zip = "33321";
vendorA.Phone = "555-444-3333";
vendorA.Email = "*****@*****.**";
Console.WriteLine($"Vendor insertion successful? {Vendors.Insert(vendorA)}");
//GETBYCODE
Vendors vendster = Vendors.GetByCode("WOOF");
Console.WriteLine(vendster);
conn.Close();
}
public void GetAll_ReturnsAllVendorsObjects_VendorList()
{
string name01 = "Dill Rye the Sandwich Guy";
string name02 = "The Sand-Witch";
Vendors newVendor1 = new Vendors(name01);
Vendors newVendor2 = new Vendors(name02);
List <Vendors> newList = new List <Vendors> {
newVendor1, newVendor2
};
List <Vendors> result = Vendors.GetAll();
CollectionAssert.AreEqual(newList, result);
}
//var p = Products.GetByPK(1);
//p.PartNbr = "WEFk4";
//p.Name = "Echo 2";
//var success = Products.Update(p);
void RunVendorsTest()
{
var conn = new Connection(@"localhost\sqlexpress", "PrsDB");
conn.Open();
Vendors.Connection = conn;
var vendors = Vendors.GetAll();
foreach (var v in vendors)
{
Console.WriteLine(v.Name);
}
//GET VENDOR BY PK
//var vendor = Vendors.GetByPk(3);
//Debug.WriteLine(vendor);
////DELETE VENDOR
//var success = Vendors.Delete(18);
//var vendor1 = Vendors.GetByPk(18);
//Debug.WriteLine(vendor1);
////INSERT VENDOR
//var newvendor = new Vendors();
//newvendor.Code = "6666";
//newvendor.Name = "Amazon";
//newvendor.Address = "48673 Amazon Ln";
//newvendor.City = "Seattle";
//newvendor.State = "WA";
//newvendor.Zip = "87645";
//newvendor.Phone = "235-605-2363";
//newvendor.Email = "*****@*****.**";
//var success = Vendors.Insert(newvendor);
//////UPDATE VENDOR
//var vendorAmazon = Vendors.GetByPk(14);
//vendorAmazon.Code = "7444";
//vendorAmazon.Phone = "394-309-8888";
//var success = Vendors.Update(vendorAmazon);
conn.Close();
}
void RunVendorsTest()
{
var conn = new Connection(@"localhost\sqlexpress", "PRS");
conn.Open();
Vendors.Connection = conn;
var vendors = Vendors.GetAll();
foreach (var v in vendors)
{
Console.WriteLine(v.Name);
}
var vendor = Vendors.GetByPk(3);
Debug.WriteLine(vendor);
var rip = Vendors.Delete(3);
var vendorabc = Vendors.GetByPk(1);
vendorabc.Code = "ABC00";
var success = Vendors.Update(vendorabc);
var newvendor = new Vendors();
newvendor.Code = "AHGFT";
newvendor.Name = "Macys";
newvendor.Address = "3475 dfdsf st";
newvendor.City = "Chicago";
newvendor.State = "Il";
newvendor.Zip = "55555";
newvendor.Phone = "234-456-1234";
newvendor.Email = "*****@*****.**";
success = Vendors.Insert(newvendor);
conn.Close();
}
void RunVendorsTest()
{
var conn = new Connection(@"localhost\sqlexpress", "PrsDb7");
conn.Open();
Vendors.Connection = conn;
// Insert
var vendor = new Vendors {
Code = "TARG", Name = "Target",
Address = "123 Any St.", City = "Minneapolis", State = "MN", Zip = "12345",
Phone = null, Email = null
};
var success = Vendors.Insert(vendor);
Console.WriteLine($"Insert worked: {success}");
Console.WriteLine("======================================");
var amazon = Vendors.GetByPk(3);
Console.WriteLine($"Vendor 3 found: {amazon}");
var notfound = Vendors.GetByPk(333);
Console.WriteLine(notfound?.ToString() ?? "Vendor 333 not found");
Console.WriteLine("======================================");
var vendors = Vendors.GetAll();
foreach (var v in vendors)
{
Console.WriteLine(v.Name);
}
conn.Close();
}
void RunVendorTest()
{
var conn = new Connection(@"localhost\sqlexpress", "PRSdb");
conn.Open();
Vendors.Connection = conn;
var vendidtest = Vendors.GetByPK(2);
var success = Vendors.Delete(4);
var vendors = Vendors.GetAll();
foreach (var v in vendors)
{
Console.WriteLine(v.Name);
}
//var newvendora = new Vendors();
//newvendora.Address = "bridge4";
//newvendora.City = "City";
//newvendora.Code = "BGHTS";
//newvendora.Email = "emaiiil.email";
//newvendora.Name = "Big Hats";
//newvendora.Phone = "484-562-4636";
//newvendora.State = "NM";
//newvendora.Zip = "43532";
//success = Vendors.Insert(newvendora);
foreach (var v in vendors)
{
Console.WriteLine(v.Name);
}
conn.Close();
}
void Run()
{
// need a connection:
var Connection = new Connection("localhost\\sqlexpress", "PrsDb"); // Instead of @, using double backslash "\\" in this project, just for fun // "conn" in other project?
Connection.Open();
Users.Connection = Connection;
Vendors.Connection = Connection;
Products.Connection = Connection;
Console.WriteLine("THIS IS A BIG DEAL: EXTENSION METHODS. THE PRINT STATEMENT IS IN THE **OTHER CLASS** " +
" \nNOW DON'T HAVE TO DO THE WORDY *FOREACH* BLOCK EVERY SINGLE TIME \n");
var products = Products.GetAll();
products.Print(); // generic List class DOES NOT have a print! The print we're calling is the static extension method
Console.WriteLine("\n THAT WAS A PRINT METHOD CALLED FROM ANOTHER CLASS. IMPRESSIVE, NO?? \n\n");
Users myUser = Users.GetByPk(4);
myUser.PrintFullName();
Console.WriteLine("-and THAT is an extension method I created myself. It prints one user's full name. \n\n");
//JOIN STMT in LINQ.
//Note word "equal" spelled out, not = sign
var prods = from p in Products.GetAll()
join v in Vendors.GetAll()
on p.VendorId equals v.Id
select new { // this part is diff from sql
Product = p.Name,
Price = p.Price,
Vendor = v.Name
};
foreach (var p in prods)
{
Console.WriteLine($"{p.Product} priced at {p.Price} is from {p.Vendor}");
}
// can link sql arrays to arrays already in my program , with join views
// joining a sql Db with in-memory collection (in this case a short list of (2) states: Washington state, and outer space
var states = new State[] {
new State()
{
Code = "WA", Name = "Washington"
},
new State()
{
Code = "XX", Name = "Outer Space"
}
};
var vendorsWithState = from v in Vendors.GetAll()
join s in states
on v.State equals s.Code
select new {
Vendor = v.Name,
State = s.Name
}; //.ToList(); --can Tack this on, bring something back to a regular list. Very useful.
foreach (var v in vendorsWithState)
{
Console.WriteLine($"Vendor {v.Vendor} is in state {v.State}");
}
// can chain
// Eexist tons of string methods. Look at all the available methods in intellisense
//some string methods, that we can apply to our query:
// str.Contains("2") --any user or vendor whose (name) contains the character "2";
// str.StartsWith("ABC")
// ******* HERE ARE A BUNCH OF SUCCESSFUL EXAMPLES OF QUERY SYNTAX *******
//method syntax/ query syntax hybrid //get total of prices of all products
var totalAllProducts = (from p in Products.GetAll()
select p).Sum(p => p.Price);
Console.WriteLine($"Total all prices is {totalAllProducts}");
//100% method syntax: //get total of prices of all products
var priceTotal = Products.GetAll().Sum(p => p.Price);
Console.WriteLine($"Total: {priceTotal}");
// Remember: This syntax is not just for sql. It is for Collections and fixed arrays, too
List <string> threeWords = new List <string> {
"bar", "Near", "cannibal"
};
var words = from wrd in threeWords
where wrd.Contains("ar")
orderby wrd descending
select wrd[0];
foreach (var word in words)
{
Console.WriteLine(word); //output N b
}
double[] myDoubles = new double[] { 7.7, 9, 100, -8.975 };
var someDoubles = from dub in myDoubles
select dub;
foreach (var doubl in someDoubles)
{
Console.WriteLine(doubl);
}
var vndors = from v in Vendors.GetAll()
where v.Code == "MSMS"
select v.Name;
foreach (var ladder in vndors)
{
Console.WriteLine(ladder);
}
var vendors = from v in Vendors.GetAll()
orderby v.Name
select v.Name;
foreach (var ven in vendors)
{
Console.WriteLine(ven);
}
Console.WriteLine();
var reviewers = from u in Users.GetAll()
where u.IsReviewer
select u.Lastname;
foreach (var reviewer in reviewers)
{
Console.WriteLine(reviewer);
}
var twoletterses = from u in Users.GetAll()
where u.Phone.Length == 12
select u.Firstname;
foreach (var two in twoletterses)
{
Console.WriteLine(two);
}
Console.WriteLine();
var aaas = from u in Users.GetAll()
where u.Firstname.Contains("a")
select u.Firstname;
foreach (var aaa in aaas)
{
Console.WriteLine(aaa);
}
// Types out Margaret's info, because she is my only admininstrator
var admins = from u in Users.GetAll()
where u.IsAdmin
select u;
foreach (var admin in admins)
{
Console.WriteLine(admin);
}
// Types out "Geoff Hoyle is a nice guy"
var users = from u in Users.GetAll()
where u.Username.Equals("threelegs") // .Equals(), C# in general, recommended way to compare two strings
select u;
foreach (var usr in users)
{
Console.WriteLine($"{usr.Firstname} {usr.Lastname} is a nice guy");
}
var vendPersons = from v in Vendors.GetAll()
where v.Email.StartsWith("w") // .Equals(), C# in general, recommended way to compare two strings
select v;
foreach (var usr in users)
{
Console.WriteLine($"{usr.Firstname} {usr.Lastname} is a nice guy");
}
// linq query syntax
admins = from u in Users.GetAll()
where u.Username.Equals("threelegs")
select u;
foreach (var user in admins)
{
Console.WriteLine($"{user.Firstname} {user.Lastname} is an admin");
}
Connection.Close();
}
void Run()
{
var connection = new Connection(@"localhost\sqlexpress", "PRSdb");
connection.Open();
Users.Connection = connection;
Vendors.Connection = connection;
Products.Connection = connection;
var statecodes = new State[] { //Example of SQL only having state codes, and outputting a different string using C# only
new State()
{
Code = "WA", Name = "Washington"
},
new State()
{
Code = "XX", Name = "Outer Space"
}
};
var vendorswithstates = from vend in Vendors.GetAll()
join sta in statecodes
on vend.State equals sta.Code
select new {
Vendor = vend.Name, State = sta.Name
};
foreach (var vs in vendorswithstates)
{
Console.WriteLine($"Vendor {vs.Vendor} is in {vs.State}");
}
var admins = from usr in Users.GetAll()
where (bool)usr.IsAdmin
select usr;
var userName = from usr in Users.GetAll()
where usr.Username.Equals("NotAGiraffe") // .Equals is recommended over == for comparing strings.
//where usr.Username.Contains("Giraffe")
select usr;
var reviewers = from usr in Users.GetAll()
where (bool)usr.IsReviewer
select usr;
var vendors = from vend in Vendors.GetAll()
orderby vend.Name
select vend;
var venCode = from vend in Vendors.GetAll()
where vend.Code.Equals("DNRUS")
select vend;
var priceSum = (from prod in Products.GetAll()
select prod.Price).Sum();
//Products.GetAll().Sum(prod => prod.Price) || alternative method with lambda. More efficient for program performance?
//Products.GetAll().Where(prod => prod.Price < 50)
//Products.GetAll().Where(prod => prod.Price < 50).Sum(prod => prod.Price) // linq expressions can be chained together
var products = from prod in Products.GetAll()
join vend in Vendors.GetAll()
on prod.VendorId equals vend.Id
select new {
Product = prod.Name, //name of column with data to populate
Price = prod.Price,
Vendor = vend.Name
};
foreach (var user in userName)
{
Console.WriteLine($"Found user {user.Username} || {user}");
}
foreach (var user in admins)
{
Console.WriteLine($"{user.Firstname} {user.Lastname} is an admin");
}
foreach (var user in reviewers)
{
Console.WriteLine($"{user.Firstname} {user.Lastname} is a reviewer");
}
Console.WriteLine();
foreach (var ven in vendors)
{
Console.WriteLine($"{ven} is a vendor");
}
foreach (var ven in venCode)
{
Console.WriteLine($"{ven} is a vendor");
}
Console.WriteLine($"The sum of all product prices is ${priceSum}");
Console.WriteLine();
foreach (var prod in products)
{
Console.WriteLine($"{prod.Product} priced at ${prod.Price} is from {prod.Vendor}");
}
connection.Close();
}
public ActionResult Index()
{
List <Vendors> allVendors = Vendors.GetAll();
return(View(allVendors));
}