VirtualBox : Installing Oracle VM VirtualBox

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.

C# : Arrays

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

ASP.NET: Get a Single Value From a Stored Procedure Part 7

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;

SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

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

ASP.NET SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

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

SQL : INSERT SELECT

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'

ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6

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.

SQL: UNION Operator

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

SQL: FULL OUTER JOIN

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

ASP.NET T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5

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.

SQL: Self Joins, Unary Relationships, and Aliases

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

ASP.NET T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4

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

Entity Framework (Database First) Part 3: Using the Entity Framework Objects In ASP.NET Project

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.

ASP.NET T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3

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 Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

Entity Framework (Database First) Part 2: Creating Entity Model From an Existing Database Entity Framework 6.1.1

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
   
 

 

 

 

 

 

ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 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:

• 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 Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

ASP.NET T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 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 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:

• 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 Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: Querying NULL Records in SQL Server

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