Blogger Sandbox

Oracle VM VirtualBox is a great virtualization software, and the best part about it is that it's free. If you have a Windows operating system and you want to explorer Linux and UNIX distributions for fun, then VirtualBox is the way to go.  Here are the steps to install VirtualBox on your machine:  Go to  https://www.virtualbox.org/wiki/Downloads  to download the latest version of VirtualBox for your operating system. 2.  Double click on the .exe file that you've just downloaded 3.  Click "Next" on the intro screen 4.  Click "Next" on the "Custom Setup" screen 5.  Click next on the second "Custom Setup" screen. 6.  Click "Yes" on the "Warning: Network Interfaces" screen, don't worry your network connection will only be interrupted briefly. 7.  Click "Install" in the "Ready to Install" screen 8,  Click "Yes" on the "User Access Control" prompt from Windows 9.  The installer wi...

Arrays are fixed size elements of a type, arrays are stored next to each other in the memory. Making them very fast and efficient.  However you must know the exact size of an array when you declare an array. Declaring an array: string[] names = new string[5]; There are two ways you can assign values to an array. The first is to assign the values individually by specify the index of the array inside a square bracket. The index is the position of element in the array. Index starts with 0. names[0] = "George"; names[1] = "James"; names[2] = "Arthur"; names[3] = "Eric"; names[4] = "Jennifer"; Or you can initialize and populate the array at the same time, like the example below. string[] names = new string[]{"George","James","Arthur","Eric","Jennifer"}; You don't have to specify the size of the array if you initialize during the declaration, C# is smart enough to figure out the array siz...

Views are virtual tables that you can create that others can use without knowing the complexity of the query, but can be used like a table. Also they can provided an added security by giving developers access to a view instead of the underlying table. Views does not contain data in itself the data stays at the tables that the views are created from. Complex views can degrade performance since they contain no data the query must be processed every time. Let's say a junior developer just came on board and he doesn't really know SQL that well. You can create a view of the more complex views to work with until he gets better with his SQL. CREATE VIEW EmployeeTerritoriesDescriptions AS SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID The view above queries the employees territories using joins, ...

Sometimes you need to call a stored procedure that only returns one value. It would be overkill to use the SqlDataReader to store just one value. You can use the SqlCommand.ExecuteScalar() method instead to retrieve just one value from the database. Here is how you would call the "GetProductsAvgPrice" stored procedure in the Northwind database. 1.  First you need create a stored procedure in the SQL Server that will return just one value the average price of products in Products table in the Northwind database. Run this code in the SQL Server query editor window USE Northwind; GO CREATE PROCEDURE GetProductsAvgPrice AS SELECT AVG(UnitPrice) FROM Products; GO 2.  In your C# code file you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. Con...

The INSERT SELECT is an INSERT statement that inserts value using a SELECT statement. The following query will insert a new customer using existing record. INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName] ,[ContactName] ,[ContactTitle] ,[Address] ,[City] ,[Region] ,[PostalCode] ,[Country] ,[Phone] ,[Fax]) SELECT 'OPDS', CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE CustomerID = 'ALFKI' Since the CustomerID field cannot be null we have to assign the value 'OPDS' as the new CustomerID for the new customer. The rest of the column values are selected from the Customer with customer id 'ALFKI' Here is the new record as well as the 'ALFKI' customer for comparison.

The UNION operator combines two or more SELECT statements into one result set. The SELECT list must be the same in all queries, same columns, expressions, functions, and data types. All UNION queries can be replaced with a join query. The query below combines the Customers select statement with with the Employees statement SELECT City,Address FROM Customers UNION SELECT City,Address FROM Employees Here is the result:

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call selProductsBySupplierID.  The stored procedure takes one input parameter call @SupplierID which takes an int. Here is how you would do it in C# 1.  First you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Now call the stored procedure and output the result from the SqlDataReader using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "selProductsBySupplierID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; SqlParameter parameter = new S...

FULL OUTER JOIN is a join that returns all the results from the left hand side of the = sign and all the results of the right hand side. For example this query returns all the customers and all the orders in one result SELECT c.ContactName, o.OrderID FROM Customers c FULL OUTER JOIN Orders o ON c.CustomerID=o.CustomerID ORDER BY c.ContactName

If you look at the Employees table in the Northwind database diagram you will see that there's a relationship that links to itself And if you look the at the Employees create script you will see that the foreign key to is the ReportTo field referencing the Primary Key EmployeeID. This kind of self referencing is called a unary relationship. ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo]) REFERENCES [dbo].[Employees] ([EmployeeID]) So how do you query the employees who is manage by another employee? You can assign aliases to the same table so that you can query the same table as if it were two different tables. SELECT e.EmployeeID,(e.FirstName + ' ' + e.LastName) AS Name, (et.FirstName + ' ' + et.LastName) AS Supervisor FROM Employees e, Employees et WHERE e.ReportsTo = et.EmployeeID Here are the results: As you can see from the select list, (e.FirstName + ' ' + e.LastName) AS Name dis...

In most of your projects you will have to work with stored procedures.  As a developer most of the time you only have to concern yourself with the basic stored procedures such as the SELECT, INSERT, UPDATE, and DELETE stored procedures.  If there's a DBA then you will probably be handed a stored procedure written by the database god.  But if you are the only developer in the five mile radius you might have to get your hands dirty and roll your own stored procedure.  In this tutorial we will be creating a select stored procedure. Here is how 1.  Right click on the "Northwind" database and then select "New Query" in "Microsoft SQL Server Management Studio" 2.  A new query window will be open type in the following into the query window to create the SELECT stored procedure. USE Northwind GO CREATE PROCEDURE dbo.ProductsSuppliers AS SELECT p.ProductID, p.ProductName, p.UnitPrice, s.CompanyName AS Supplier FROM Products p INNER JOIN Suppliers s ON p.Supplie...

RIGHT JOIN works like the INNER JOIN, it just returns all the records that are on the right side of the = sign on the RIGHT JOIN clause. For example let's say you want to get a record of all customers who orders a certain product. You will use the RIGHT JOIN by query all the orders in the Orders table then linking it to the OrderDetails table and then eventually linking it to the Products table. SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName FROM Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID RIGHT JOIN Products p ON p.ProductID = od.ProductID Here are the results: You can filter the results further by adding a WHERE claus for a specific product id. SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName FROM Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID RIGHT JOIN P...

Here is how you would create a stored procedure to update an a new record into the Products table in the Northwind database. USE Northwind GO CREATE PROCEDURE dbo.updProduct( @ProductID int, @ProductName nvarchar(40), @SupplierID int = null, --default is null @CategoryID int = null, @QuantityPerUnit nvarchar(20) = null, @UnitPrice money = null, @UnitsInStock smallint = null, @UnitsOnOrder smallint = null, @ReorderLevel smallint = null, @Discontinued bit) AS UPDATE Products SET ProductName = @ProductName, SupplierID = @SupplierID, CategoryID = @CategoryID, QuantityPerUnit = @QuantityPerUnit, UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel, Discontinued = @Discontinued WHERE Products.ProductID = @ProductID GO When you see a parameter with the = null, it means the field can have a null value. Since we need ProductID is needed to update a specific record we need it in the input parameter list. The...

This is part three of our series on Entity Framework. In the last blog we went over how to create an Entity Framework model with the Northwind database. Now we are going to use that model in our ASP.NET by binding the Entity objects that have created to a GridView in our "Northwind" ASP.NET project. Usually we would put the Entity Framework model in a class library project and use it as our data access layer, but for simplicity I've decided to put in the same project as the ASP.NET pages. Below are the directions on how to use the Entity objects in our web pages. 1. Create "Default.aspx" page in the "Northwind" web project. 2. Add a GridView control to the page. 3. In the "Default.aspx.cs" file add the following using statement using Northwind.Models; Northwind.Models is the namespace of models that we just created. 4. Then in the Page_Load method write the following code. protected void Page_Load(object sender, EventArgs e) ...

The INNER JOIN functions like the WHERE clause by relating two or more tables using matching data. The difference is that the INNER JOIN is used in the FROM clause. So to the the employee's territory like the one we wrote in this blog . We would change the query into the query below to use the INNER JOIN instead of the WHERE clause. SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID With the result: To the employees who belongs to the Boston territory you would add the "AND" clause to the query, like the query below: SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID AND t.TerritoryID = 02116

To create the delete procedure type in the following code in the SQL editor window in "Microsoft SQL Server Management Studio" USE Northwind GO CREATE PROCEDURE dbo.delProduct @ProductID int AS DELETE FROM Products WHERE Products.ProductID = @ProductID GO The stored procedure only takes in one input parameter which is the ProductID, the DELETE statement needs a ProductID because if there is no WHERE clause, every record in the product in the Products table will be deleted. Make sure you backup the table before you work with a DELETE stored procedure. Here is how would execute the stored procedure EXEC dbo.delProduct 78 Blogs In the T-SQL Series: T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1 ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2 T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3 T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4 T-SQL: Stored Pro...

This is part two of our series on Entity Framework, if you would like to catch up with what we did on on part one , feel free to go over the lesson so that you can follow along. In the last part we installed Entity Framework 6.1.1 with NuGet package management tool in Visual Studio.  In this lesson we will learn to create an Entity Model using the Northwind database.  Follow the steps below. Add a new folder call "Models" in the "Northwind" database             2. Right-click on the "Models" folder and select "Add", the select "New Item"   3.   Select "Data" on the left tree menu under C#, then in the main menu select "ADO.NET Entity Model".  In the "Name" field type "NorthwindModel".  Click "Add" when you are done. 4.  On the "Entity Data Model Wizard" screen select "EF Designer from database" and then select "Next" 5. Click on the "New Connection...

In SQL the WHERE clause is the most common join you will see, it relates one or more tables together. For example you want to get the employeeis territory information in the Northwind database but you there are all in different tables. As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause: SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID FROM Employees e,Territories t, EmployeeTerritories et WHERE e.EmployeeID = et.EmployeeID AND et.TerritoryID = t.Terri...

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call addProduct.  The stored procedure takes the following input parameters. 1.  First you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Now call the stored procedure and output the result from the SqlDataReader using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "addProduct"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; SqlParameter productName = new SqlParameter("@ProductName", "Teh"); productName.SqlDbType = S...

The easiest and simplest way to explain what a subquery is to say that it's a query within a query. For example if you want to get the employee that belongs to specific territory in the Northwind database without a join, you would have to use a subquery. Like the following subquery. SELECT EmployeeID, (FirstName + ' ' + LastName) AS Name FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM EmployeeTerritories WHERE TerritoryID=01581) Things You Should Know About Subqueries: They are not the most efficient performance wise You can only retrieve a single column in the subquery, retrieving multiple columns will throw an error Another way to use subqueries is to use it with Aggregate functions like the query below, which gets the average price for the category with ID value of 1: SELECT CategoryName, (SELECT AVG(UnitPrice) FROM Products WHERE CategoryID = 1) AS AvgPrice FROM Categories WHERE CategoryID = 1

Here is how you would create a stored procedure to insert a new record into the Products table in the Northwind database. USE Northwind GO CREATE PROCEDURE dbo.addProduct( @ProductName nvarchar(40), @SupplierID int = null, --default is null @CategoryID int = null, @QuantityPerUnit nvarchar(20) = null, @UnitPrice money = null, @UnitsInStock smallint = null, @UnitsOnOrder smallint = null, @ReorderLevel smallint = null, @Discontinued bit) AS INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued) GO When you see a parameter with the = null, it means the field can have a null value. Since the ProductID is auto incremented you don't include it. The data types must match the fields in the database. Here is how you would execute the stored pro...

SQL GROUPING allows you to segregate data into groups so that you can work on it separately from the rest of the table records. Let's say you want to get the number of products in a category in the Northwind database Products table. You would write the following query: SELECT COUNT(*) NumberOfProductsByCategory FROM Products GROUP BY CategoryID The query above gives you the following results: The query gives you the number of products in each category, however it's not very useful. You don't really know what category the count is for in each record. You might want to try to change the query into something like this: SELECT CategoryID,COUNT(*) NumberOfProductsByCategory FROM Products GROUP BY CategoryID The above query is more useful the preceding one, however it only gives you the CategoryID number not the CategoryName in the Categories table. Being the perfectionist that you are you say to yourself, I can do better. "Yes, I can". I think that was a ca...