The variables used in the program are given below:
- connectionStringBuilder: an instance of the SqlConnectionStringBuilder class used to create a connection string to the productDB
database . - connectionString: a string that holds the connection string to the database.
- connection: an instance of the SqlConnection class used to connect to the database.
- query: a LINQ query that selects all the products in the database and sorts them by units on hand in ascending order.
- minUnitsOnHand: an integer variable that holds the minimum units on hand specified by the user.
- hasMin: a boolean variable that indicates whether a minimum number of units on hand was specified by the user.
- maxUnitsOnHand: an integer variable that holds the maximum units on hand specified by the user.
- hasMax: a boolean variable that indicates whether a maximum number of units on hand was specified by the user.
The methods used in the program are as follows:
- The SqlConnectionStringBuilder constructor is used to create a connection string to the database.
- The SqlConnection constructor, which creates a new SqlConnection object that can be used to connect to the database.
- The SqlCommand constructor creates a new SqlCommand object that can be used to execute a query against the database.
- The ExecuteReader method of the SqlCommand class is used to execute the query and return a SqlDataReader object that can be used to read the results.
- The Where method of the IQueryable interface is used to filter the data based on a specified condition.
- The TryParse method of the int class is used to convert user input from a string to an integer.
- The Console.WriteLine method, which is used to display information to the user in the console window.
- The Console.ReadLine method, which is used to read user input from the console window.
Program Description:
To write an application that connects to productDB database.
Steps to create an application that connects to a database, sorts, and searches data using LINQ and SQL:
- Create a new C# console application project in Visual Studio.
- Add a reference to the System.Data.SqlClient and System.Linq namespaces.
- Create a connection string to the productDB database using SqlConnectionStringBuilder.
- Create a SqlConnection object and open the connection to the database.
- Create a LINQ query to select all the products in the database and sort them by units on hand in ascending order. You can use the OrderBy method in LINQ to sort the data.
- Execute the query using the SqlCommand object and read the data using the SqlDataReader object.
- Display the data to the user.
- Prompt the user to enter a minimum and/or maximum number of units on hand.
- Modify the LINQ query to filter the products based on the user's input. You can use the Where method in LINQ to filter the data.
- Execute the modified query and display the filtered data to the user.
- Close the SqlDataReader and SqlConnection objects.
Program:
using System; using System.Data.SqlClient; using System.Linq; namespace ProductUnitsOnHandSearch { class Program { static void Main(string[] args) { // Create connection string to the productDB database var connectionStringBuilder = new SqlConnectionStringBuilder { DataSource = "your_server_name", // Replace with your actual database name InitialCatalog = "productDB", IntegratedSecurity = true }; string connectionString = connectionStringBuilder.ToString(); // Create SqlConnection object and open connection using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Create LINQ query to select all products and sort by units on hand in ascending order var query = from p in db.Products orderby p.UnitsOnHand ascending select p; // Execute query and read data using (SqlCommand command = new SqlCommand(query.ToString(), connection)) { using (SqlDataReader reader = command.ExecuteReader()) { // Display all products Console.WriteLine("All Products:"); Console.WriteLine("--------------"); while (reader.Read()) { Console.WriteLine("{0}: {1} units on hand", reader["ProductName"], reader["UnitsOnHand"]); } Console.WriteLine(); } } // Prompt user for minimum and maximum units on hand Console.Write("Enter minimum units on hand (or leave blank for no minimum): "); string minInput = Console.ReadLine(); int minUnitsOnHand; bool hasMin = int.TryParse(minInput, out minUnitsOnHand); Console.Write("Enter maximum units on hand (or leave blank for no maximum): "); string maxInput = Console.ReadLine(); int maxUnitsOnHand; bool hasMax = int.TryParse(maxInput, out maxUnitsOnHand); // Modify query to filter by units on hand based on user input if (hasMin) { query = query.Where(p => p.UnitsOnHand >= minUnitsOnHand); } if (hasMax) { query = query.Where(p => p.UnitsOnHand <= maxUnitsOnHand); } // Execute modified query and display filtered products using (SqlCommand command = new SqlCommand(query.ToString(), connection)) { using (SqlDataReader reader = command.ExecuteReader()) { Console.WriteLine("Filtered Products:"); Console.WriteLine("------------------"); while (reader.Read()) { Console.WriteLine("{0}: {1} units on hand", reader["ProductName"], reader["UnitsOnHand"]); } } } } Console.ReadLine(); } } }
Explanation:
The program is a console application that connects to a database called productDB and displays information about the products in the database. The program uses LINQ and SQL to interact with the database and allows the user to search for products with more units on hand than a specified amount or fewer units on hand than a specified amount.
When the program starts, it creates a connection string to the productDB database and opens a connection to the database using the SqlConnection and SqlCommand classes. The program then executes a LINQ query to select all the products in the database and sorts them by units on hand in ascending order.
The program then prompts the user to enter a minimum unit on hand and a maximum unit on hand. If the user enters a valid integer for the minimum or maximum units on hand, the program filters the results using the Where method of the IQueryable interface. If the user does not enter a value for the minimum or maximum units on hand, the program skips that filter.
Finally, the program loops through the results and displays the product ID, name, and units on hand for each product that matches the user's search criteria. If no products match the criteria, the program displays a message indicating that no results were found. The user can then exit the program or search for products again.
SAMPLE OUTPUT:
Enter minimum units on hand (press enter to skip): 50 Enter maximum units on hand (press enter to skip): 100 Products with units on hand between 50 and 100: ---------------------------------------------- Product ID: 2 Name: Rechargeable Lamp Units on hand: 75 Product ID: 4 Name: Smart watch Units on hand: 80 Do you want to search again? (Y/N) y Enter minimum units on hand (press enter to skip): Enter maximum units on hand (press enter to skip): 200 Products with units on hand between 0 and 200: ---------------------------------------------- Product ID: 1 Name: Wireless Earbuds Units on hand: 120 Product ID: 3 Name:Head phones Units on hand: 150 Do you want to search again? (Y/N) n Press any key to exit...
Want to see the full answer?
Check out a sample textbook solutionChapter 14 Solutions
Starting Out With Visual C# (5th Edition)
- relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: Read a Book id provided by user at run time and store it in a variable. Fetch the Author and title of the book having the entered id and store them inside two variables Display the fetched book author and title.arrow_forwardSBN Title Author 12345678 The Hobbit J.R.R. Tolkien 45678912 DaVinci Code Dan Brown Your student ID DBS311 Your Name use the following statement to Write a PL/SQL Function that accepts 4 parameters, 2 strings representing students names, and 2 integers representing the marks they received in DBS311. The function will determine which student had the higher mark and return the name of the student. If the marks were the same, then return the word "same" and return "error" if an error occurs or the calculation can not be determined.arrow_forwardPlease Help Correct This Error // Create Horse tablepublic static void createTable(Connection conn) {// SQL Statement For Creating TableString sql = "CREATE TABLE HORSE ("+ " ID INTEGER PRIMARY KEY,"+ " NAME TEXT,"+ " BREED TEXT,"+ " HEIGHT REAL,"+ " BIRTHDATE TEXT"+ ")"; try {// Execute DDL StatementStatement statement = conn.createStatement();statement.execute(sql);} catch (SQLException ex) {System.out.println(ex.getMessage());} } 2:Unit test 0 / 3 Test createTable() creates Horse table Test feedback Horse table does not existarrow_forward
- In Visual Basic, a double data type would be comparable to what SQL data type.arrow_forward**In SQL** Write a SELECT statement that returns these columns: InstructorDept The DepartmentName column from the Departments table for a related instructor LastName The LastName column from the Instructors table FirstName The FirstName column from the Instructors table CourseDescription The CourseDescription column from the Courses table CourseDept The DepartmentName column from the Departments table for a related instructor Return one row for each course that’s in a different department than the department of the instructor assigned to teach that course. (Hint: You will need to join the Departments table to both the Instructors table and the Courses table, which will require you to use table aliases to distinguish the two tables.)arrow_forwardAssume that a database has a table named Stock, with the following columns:Column Name TypeTrading_Symbol nchar(10)Company_Name nchar(25)Num_Shares intPurchase_Price moneySelling_Price money Write a Select statement that returns the Trading_Symbol column and the Num_Shares column only from the rows where Selling_Price is greater than Purchase_Price and Num_Shares is greater than 100. The results should be sorted by the Num_Shares column in ascending order.arrow_forward
- 20 - final question Assuming you have designed a PhoneBook application; O Form1 name Insert Delete export clear All surname phone number load Data Close Update Туре The phoneBook connects to database called phonebook created in SQLExpress. When "clear All" button is clicked all data in textboxes is cleared, data grid (dataGridView1) is cleared and type combobox (comboBox1) should not have any selection. In order to clear combobocx selection the correct statement is: a. comboBox1. SelectedValue = -1; b. comboBox1. Enabled = false; C. comboBox1. Equals = -1arrow_forwardDatabase: CUSTOMER (CustomerID, LastName, FIrstName, Phone, EmailAddress) PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID) PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, Item Number, RetailPrice) ITEM (ItemNumber, ItemDesciption, Cost, ArtistLastName, ArtistFirstName) Write an SQL statement to show which cutomers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, IvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName,. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order. Note that in Microsoft Access this require multiple queries. From Database Concepts 9th Ed. (Kroenke)arrow_forwardThe Save Transaction button depicted in the screen attached is used to save relevant data to the sales table and the salesdetails tables from the depicted schema. When this button is clicked it calls the saveTransaction() function that is within the PosDAO class, it passes to this function an ArrayList of salesdetails object, this list contains the data entered into the jTable which is the products and qty being sold.Write the saveTransaction function. You are to loop through the items and get the total sales, next you are to insert the current date and the total sales into the sales table. Reminder that the sales table SalesNumber field is set to AUTO-INCREMENT, hence the reason for only entering the total sales and current date in sales table.arrow_forward
- J SHORTAND NOTATION FOR RELATIONAL SQL TABLES Notation Example Meaning Underlined A or A, B The attribute(s) is (are) a primary key Superscript name of relation AR or AR, BR The attribute(s) is (are) a foreign key referencing relation R As an example, the schema R(A, B, C, D, ES) S(F, G, H) corresponds to the following SQL tables: CREATE TABLE R ( A <any SQL type>, B <any SQL type>, C <any SQL type>, D <any SQL type>, E <any SQL type>, PRIMARY KEY(A), FOREIGN KEY (E) REFERENCES S(F) ); CREATE TABLE S ( F <any SQL type>, G <any SQL type>, H <any SQL type>, PRIMARY KEY(F)) EXERCISE Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards generating them [the used notation is explained above]. SHOPPINGTRANSACTION (TransId, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreIdSTORE) CREDITCARD (CardNbr, CardTypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit)…arrow_forwardSection B- EXCEPTION HANDLING TASK 1 Write a PL/SQL block to retrieve employees from the ENEW table based on their salary. If there are multiple employees earning the same salary (i.e. returns more than one row), handle the exception with the appropriate handler and print the message "More than one employee with the same salary". If there are no employees earning that salary, handle the exception with the appropriate handler and display the message "No employees with this salary". If there is only one employee with this salary, then print that employee's name, hiredate and salary (predefined server exception problem).arrow_forwardINFO 2303 Database Programming Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update.arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education