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