コード例 #1
0
ファイル: Program.cs プロジェクト: JustinDudley/CSharp2Sql
        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();
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        //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();
        }
コード例 #4
0
        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();
        }
コード例 #5
0
        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();
        }
コード例 #6
0
ファイル: Program.cs プロジェクト: JeremyPlott/CSharpToSQL
        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();
        }
コード例 #7
0
ファイル: Program.cs プロジェクト: JustinDudley/LINQster
        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();
        }
コード例 #8
0
ファイル: Program.cs プロジェクト: JeremyPlott/LINQIntro
        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();
        }
コード例 #9
0
        public ActionResult Index()
        {
            List <Vendors> allVendors = Vendors.GetAll();

            return(View(allVendors));
        }