Esempio n. 1
0
    private static void CreatingNewDataBase(northwindEntities context)
    {
        SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
        string nativeSQLQuery = "CREATE DATABASE MyDatabase ON PRIMARY " +
        "(NAME = MyDatabase_Data, " +
        "FILENAME = 'C:\\MyDatabaseData.mdf', " +
        "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
        "LOG ON (NAME = MyDatabase_Log, " +
        "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
        "SIZE = 1MB, " +
        "MAXSIZE = 5MB, " +
        "FILEGROWTH = 10%)";
        context.Database.SqlQuery<string>(nativeSQLQuery);

        //SqlCommand myCommand = new SqlCommand(nativeSQLQuery, myConn);
        //try
        //{
        //    myConn.Open();
        //    myCommand.ExecuteNonQuery();
        //}
        //finally
        //{
        //    if (myConn.State == ConnectionState.Open)
        //    {
        //        myConn.Close();
        //    }
        //}
    }
Esempio n. 2
0
 static void Main()
 {
     northwindEntities context = new northwindEntities();
     InsertCustomer(context);
     UpdateCustomer(context);
     DeleteCustomer(context);
 }
Esempio n. 3
0
 static void DeleteCustomer(northwindEntities context)
 {
     Console.WriteLine("Enter Customer ID");
     string id = Console.ReadLine();
     Customer customer = context.Customers.First(x => x.CustomerID == id);
     context.Customers.Remove(customer);
     context.SaveChanges();
 }
Esempio n. 4
0
 static void Main()
 {
     northwindEntities context = new northwindEntities();
     using (context)
     {
         CreatingNewDataBase(context);
     }
 }
Esempio n. 5
0
 static void Main()
 {
     northwindEntities context = new northwindEntities();
     using (context)
     {
         var supplierIncome = findSupplierIncome(context);
         Console.WriteLine("Supplier {0}'s income is {1}", "ss", supplierIncome);
     }
 }
Esempio n. 6
0
 static void Main()
 {
     northwindEntities context = new northwindEntities();
     using (context)
     {
         foreach (var territory in context.Territories)
         {
             Console.WriteLine(territory.TerritoryDescription);
         }
     }
 }
Esempio n. 7
0
    static void UpdateCustomer(northwindEntities context)
    {
        Console.WriteLine("Enter Customer ID");
        string id = Console.ReadLine();
        Customer customer = context.Customers.First(x => x.CustomerID == id);
        Console.WriteLine("Enter Column name");
        string column = Console.ReadLine();
        Console.WriteLine("Enter Column value");
        string value = Console.ReadLine();
        switch (column)
        {
            case "CompanyName":
                customer.CompanyName = value;
                break;
            case "ContactName":
                customer.ContactName = value;
                break;
            case "ContactTitle":
                customer.ContactTitle = value;
                break;
            case "Address":
                customer.Address = value;
                break;
            case "City":
                customer.City = value;
                break;
            case "Region":
                customer.Region = value;
                break;
            case "PostalCode":
                customer.PostalCode = value;
                break;
            case "Country":
                customer.Country = value;
                break;
            case "Phone":
                customer.Phone = value;
                break;
            case "Fax":
                customer.Fax = value;
                break;
        }

        context.SaveChanges();
    }
Esempio n. 8
0
    static void Main()
    {
        using (northwindEntities firstContext = new northwindEntities())
        {
            using (northwindEntities secondContext = new northwindEntities())
            {
                Customer customerFirstContext = firstContext.Customers.Find("CHOPS");
                customerFirstContext.Region = "SW";

                Customer customerSecondContext = secondContext.Customers.Find("CHOPS");
                customerSecondContext.Region = "SSWW";

                firstContext.SaveChanges();
                secondContext.SaveChanges();
            }
        }

        Console.WriteLine("Changes successfully made!");
    }
Esempio n. 9
0
    static void Main()
    {
        northwindEntities context = new northwindEntities();
        using (context)
        {
            string nativeSQLQuery =
                "SELECT DISTINCT c.CompanyName " +
                "FROM dbo.Customers c " +
                "JOIN dbo.Orders o " +
                "ON c.CustomerID = o.CustomerID " +
                "WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'";
            var companies = context.Database.SqlQuery<string>(nativeSQLQuery);

            foreach (var company in companies)
            {
                Console.WriteLine(company);
            }
        }
    }
Esempio n. 10
0
    static void Main()
    {
        northwindEntities context = new northwindEntities();
        using (context)
        {
            Console.WriteLine("Enter Region name");
            string region = Console.ReadLine();
            Console.WriteLine("Enter Start date in format dd.MM.yyyy");
            DateTime startDate =
                DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
            Console.WriteLine("Enter End date in format dd.MM.yyyy");
            DateTime endDate =
                DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
            var sales = context.Orders.Select(x => x).
                Where(x => x.ShipRegion == region &&
                    x.OrderDate > startDate && x.OrderDate < endDate);

            Console.WriteLine(sales.Count());
        }
    }
Esempio n. 11
0
 static void InsertCustomer(northwindEntities context)
 {
     using (context)
     {
         Console.WriteLine("Enter Company name");
         string company = Console.ReadLine();
         Console.WriteLine("Enter Contact name");
         string contact = Console.ReadLine();
         Console.WriteLine("Enter Contact title");
         string title = Console.ReadLine();
         Console.WriteLine("Enter Address");
         string address = Console.ReadLine();
         Console.WriteLine("Enter City");
         string city = Console.ReadLine();
         Console.WriteLine("Enter Region");
         string region = Console.ReadLine();
         Console.WriteLine("Enter PostalCode");
         string code = Console.ReadLine();
         Console.WriteLine("Enter Country");
         string country = Console.ReadLine();
         Console.WriteLine("Enter Phone");
         string phone = Console.ReadLine();
         Console.WriteLine("Enter Fax");
         string fax = Console.ReadLine();
         Customer customer = new Customer
         {
             CompanyName = company,
             ContactName = contact,
             ContactTitle = title,
             Address = address,
             City = city,
             Region = region,
             PostalCode = code,
             Country = country,
             Phone = phone,
             Fax = fax,
         };
         context.Customers.Add(customer);
         context.SaveChanges();
     }
 }
Esempio n. 12
0
    static void Main()
    {
        northwindEntities context = new northwindEntities();
        using (context)
        {
            var orders = context.Customers.
                Join(context.Orders,
                (c => c.CustomerID), (o => o.CustomerID), (c, o) =>
                    new
                    {
                        Customer = c.CompanyName,
                        OrderDate = o.OrderDate,
                        Country = o.ShipCountry
                    }).
                    Select(x => x).
                    Where(x => x.OrderDate.Value.Year == 1997 && x.Country == "Canada");
            var companies = orders.Select(x => x.Customer).Distinct();

            foreach (var company in companies)
            {
                Console.WriteLine(company);
            }
        }
    }
Esempio n. 13
0
 static IEnumerable<int> findSupplierIncome(northwindEntities context)
 {
     Console.WriteLine("Enter Company Name");
     string name = Console.ReadLine();
     Console.WriteLine("Enter Start Date in format dd.MM.yyyy");
     DateTime startDate =
         DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
     Console.WriteLine("Enter End Date in format dd.MM.yyyy");
     DateTime endDate =
         DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
     string nativeSQLQuery = string.Format(
         "select sum(od.UnitPrice * Quantity) as SupplierIncome " +
         "from dbo.Suppliers as s " +
         "inner join dbo.Products as p " +
         "on p.SupplierID = s.SupplierID " +
         "inner join dbo.[Order Details] as od " +
         "on od.ProductID = p.ProductID " +
         "inner join dbo.Orders as o " +
         "on o.OrderID = od.OrderID " +
         "where s.CompanyName = {0} and o.OrderDate > {1} and o.OrderDate > {2}",
         name, startDate, endDate);
     var income = context.Database.SqlQuery<int>(nativeSQLQuery);
     return income;
 }
Esempio n. 14
0
    static void Main()
    {
        TransactionScope scope = new TransactionScope();
        using (scope)
        {
            northwindEntities context = new northwindEntities();
            using (context)
            {
                Console.WriteLine("Enter Customer ID");
                string customer = Console.ReadLine();
                Console.WriteLine("Enter Employee ID");
                int employee = int.Parse(Console.ReadLine());
                Console.WriteLine("Enter Order Date in format dd.MM.yyyy");
                DateTime orderDate =
                    DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
                Console.WriteLine("Enter Required Date in format dd.MM.yyyy");
                DateTime requiredDate =
                    DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
                Console.WriteLine("Enter Shipped Date in format dd.MM.yyyy");
                DateTime shippedDate =
                    DateTime.ParseExact(Console.ReadLine(), "dd.MM.yyyy", new CultureInfo("en-US"));
                Console.WriteLine("Enter Sip Via");
                int via = int.Parse(Console.ReadLine());
                Console.WriteLine("Enter Freight");
                decimal freight = decimal.Parse(Console.ReadLine());
                Console.WriteLine("Enter Ship Name");
                string name = Console.ReadLine();
                Console.WriteLine("Enter Ship Address");
                string address = Console.ReadLine();
                Console.WriteLine("Enter Ship City");
                string city = Console.ReadLine();
                Console.WriteLine("Enter Ship Region");
                string region = Console.ReadLine();
                Console.WriteLine("Enter Ship Postal Code");
                string code = Console.ReadLine();
                Console.WriteLine("Enter Ship Country");
                string country = Console.ReadLine();
                Order order = new Order
                {
                    CustomerID = customer,
                    EmployeeID = employee,
                    OrderDate = orderDate,
                    RequiredDate = requiredDate,
                    ShippedDate = shippedDate,
                    ShipVia = via,
                    Freight = freight,
                    ShipName = name,
                    ShipAddress = address,
                    ShipCity = city,
                    ShipRegion = region,
                    ShipPostalCode = code,
                    ShipCountry = country
                };

                context.Orders.Add(order);
                context.SaveChanges();
            }

            scope.Complete();
        }
    }