Tech Junkie Blog - Real World Tutorials, Happy Coding!: February 2015

Friday, February 27, 2015

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:


  1.  Go to https://www.virtualbox.org/wiki/Downloads to download the latest version of VirtualBox for your operating system.

Thursday, February 19, 2015

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";

Tuesday, February 17, 2015

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;


In SQL Server type in the following command in the Query Window:

USE Northwind;
Grant EXEC ON OBJECT::dbo.GetProducts TO "NT AUTHORITY\NETWORK SERVICE";
GO

The command above grants execution permission for user "NT AUTHORITY\NETWORK SERVICE" on the stored procedure dbo.GetProducts

dbo = owner schema
GetProducts = name of stored procedure
"NT AUTHORITY\NETWORK SERVICE" = user that IIS uses to access SQL Server

In SQL Server type in the following command in the Query Window:

USE Northwind;
Grant EXEC ON OBJECT::dbo.GetProducts TO "NT AUTHORITY\NETWORK SERVICE";
GO

The command above grants execution permission for user "NT AUTHORITY\NETWORK SERVICE" on the stored procedure dbo.GetProducts

dbo = owner schema
GetProducts = name of stored procedure
"NT AUTHORITY\NETWORK SERVICE" = user that IIS uses to access SQL Server

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'


Monday, February 16, 2015

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.

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

Sunday, February 15, 2015

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

Saturday, February 14, 2015

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.

If you look at the Employees table in the Northwind database diagram you will see that there's a relationship that links to itself

Friday, February 13, 2015

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

Thursday, February 12, 2015

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.



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:

Wednesday, February 11, 2015

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.

  1. Add a new folder call "Models" in the "Northwind" database
  2.  
     
     
     
     
     

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 = SqlDbType.NVarChar;
            productName.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(productName);

            SqlParameter supplerID = new SqlParameter("@SupplierID", 1);
            supplerID.SqlDbType = SqlDbType.Int;
            supplerID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(supplerID);

            SqlParameter categoryID = new SqlParameter("@CategoryID", 1);
            categoryID.SqlDbType = SqlDbType.Int;
            categoryID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(categoryID);

            SqlParameter quantityPerUnit = new SqlParameter("@QuantityPerUnit", "20 boxes of 12 oz.");
            quantityPerUnit.SqlDbType = SqlDbType.NVarChar;
            quantityPerUnit.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(quantityPerUnit);

            SqlParameter unitPrice = new SqlParameter("@UnitPrice", 12.99);
            unitPrice.SqlDbType = SqlDbType.Money;
            unitPrice.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitPrice);

            SqlParameter unitsInStock = new SqlParameter("@UnitsInStock", 6);
            unitsInStock.SqlDbType = SqlDbType.SmallInt;
            unitsInStock.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitsInStock);

            SqlParameter reorderLevel = new SqlParameter("@ReorderLevel", 2);
            reorderLevel.SqlDbType = SqlDbType.SmallInt;
            reorderLevel.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(reorderLevel);

            SqlParameter discontinued = new SqlParameter("@Discontinued", false);
            discontinued.SqlDbType = SqlDbType.Bit;
            discontinued.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(discontinued);

            int rowsAffected = cmd.ExecuteNonQuery();

            Response.Write(rowsAffected);
    }
In the above code you add the parameters required by the addProduct stored procedure. You specify the name, type, and value. Then add it to command object's parameters list. Then you execute the ExecuteNonQuery() method because you are not get a resultset back or a scalar value. The ExecuteNonQuery() method returns an int value, usually the rows that were affected value.




Blogs In the T-SQL Series:

Tuesday, February 10, 2015

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 procedure
EXEC dbo.addProduct @ProductName ='Teh',
 @SupplierID = DEFAULT,
 @CategoryID = DEFAULT,
 @QuantityPerUnit ='20 boxes x 12 oz.',
 @UnitPrice = 12.99,
 @UnitsInStock = 5,
 @UnitsOnOrder = 6,
 @ReorderLevel = DEFAULT,
 @Discontinued = 0

When you see a parameter with = DEFAULT it means to assign the DEFAULT value to the field, if the execution is completed successfully you should see the message.

(1 row(s) affected)




Blogs In the T-SQL Series:

Saturday, February 7, 2015

As a developer we always forget how to query for records with NULL values, no matter how many times we do it. It's just weird. Our first instinct is to write the query as such

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region = NULL

But that will not return any results. The funny thing is there's no SQL error so you think that there's no results. However if you change the query to this

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region IS NULL

You see there's plenty of records with Region IS NULL



The reverse is true if you want records that are not NULL you would not write the query like this

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region != NULL

But you want to write the query like this instead

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region IS NOT NULL



Search This Blog