CustomerInfo.aspx
------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomerInfo.aspx.cs" Inherits="CustomerInfo" %>
-------------------------------------
CustomerInfo.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using DataAccess;
using System.Web.Configuration;
public partial class CustomerInfo : System.Web.UI.Page
{
string connectionString = WebConfigurationManager.ConnectionStrings["NorthwindISC559"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetFormMode();
}
}
#region " Click event handlers for page controls "
//If Cancel Then Redirect To Customer List Page
protected void btnCancel_Click(object sender, EventArgs e)
{
Response.Redirect("CustomerList.aspx");
}
#endregion
#region " Click event handlers for grid views "
#endregion
#region " Local methods "
public void SetFormMode()
{
string PageMode = Request.QueryString["mode"].ToString();
/* Please Uncomment These
if (PageMode == "add")
{
txtCustomerID.Text = "";
txtLastName.Text = "";
txtPhone.Text = "";
}
else if (PageMode == "edit")
{
DataAccess.daUser objUser = new daUser();
DataTable dtCustomerInfo = objUser.GetCustomerInfo((int)Session["CustomerID"], connectionString);
txtCustomerID.Text = dtCustomerInfo.Rows[0]["CustomerID"].ToString();
txtName.Text = dtCustomerInfo.Rows[0]["LastName"].ToString();
txtDescription.Text = dtCustomerInfo.Rows[0]["Phone"].ToString();
}
*/
}
#endregion
#region " Local functions "
#endregion
#region " Local utilities "
#endregion
protected void btnSave_Click(object sender, EventArgs e)
{
//Please Uncomment These
//string PageMode = Request.QueryString["mode"].ToString();
//if (PageMode == "add")
//{
// DataAccess.daUser objUser = new daUser();
// objUser.InsertCustomer(txtCustomerId.Text,txtLastName.Text,txtPhone.Text, connectionString);
//}
//else if (PageMode == "edit")
//{
// DataAccess.daUser objUser = new daUser();
// objUser.UpdateRole(txtCustomerId.Text, txtLastName.Text, txtPhone.Text, connectionString);
//}
}
}
// ========
// new code
// ===========
Use Northwind
--DROP PROC GetCustomerInfo
CREATE PROC GetCustomerInfo
@CustomerID NVARCHAR(5)
AS
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, UserID
FROM Customers
WHERE CustomerID = @CustomerID
--Test Case
DECLARE @CustomerID AS NVARCHAR(5)
SET @CustomerID = 'ALFKI'
EXEC GetCustomerInfo @CustomerID
--DROP PROC InsertCustomer
CREATE PROC InsertCustomer
@CustomerID nvarchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
AS
INSERT INTO tblUser
(Login,
Password,
UserType)
VALUES
(@ContactName + '@Northwind.com',
'9',
'C')
DECLARE @UserID int
SET @UserID = @@IDENTITY
INSERT INTO Customers
([CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[Phone],
[Fax],
[UserID])
VALUES
(@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax,
@UserID)
GO
--Test Case
DECLARE @CustomerID nvarchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
SET @CustomerID = 'AAAAA'
SET @CompanyName = 'Test'
SET @ContactName = 'TestContact'
SET @ContactTitle = 'TestType'
SET @Address = 'Test Address'
SET @City = 'Testville'
SET @Region = 'TestArea'
SET @PostalCode = '99999'
SET @Country = 'USA'
SET @Phone = '251-251-2555'
SET @Fax = '251-251-2444'
EXEC InsertCustomer @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax
--select test
select * from Customers
--DROP PROC UpdateCustomer
CREATE PROC UpdateCustomer
@CustomerID nvarchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
AS
UPDATE Customers
SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName,
[ContactTitle] = @ContactTitle,
[Address] = @Address,
[City] = @City,
[Region] = @Region,
[PostalCode] = @PostalCode,
[Country] = @Country,
[Phone] = @Phone,
[Fax] = @Fax
WHERE CustomerID = @CustomerID
GO
--Test Case
DECLARE @CustomerID nvarchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
SET @CustomerID = 'AAAAA'
SET @CompanyName = 'RealCompany'
SET @ContactName = 'TestContact1'
SET @ContactTitle = 'TestType'
SET @Address = 'Test Address'
SET @City = 'Testville'
SET @Region = 'TestArea'
SET @PostalCode = '99999'
SET @Country = 'Canada'
SET @Phone = '251-251-2555'
SET @Fax = '251-251-2444'
EXEC UpdateCustomer @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax
--select test
select * from customers
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
namespace DataAccess
{
class daCustomer
{
#region " Public properties "
public static bool pTransactionSuccessful;
public bool TransactionSuccessful()
{
return pTransactionSuccessful;
}
public static string pErrorMessage;
public string ErrorMessage()
{
return pErrorMessage;
}
public static int pErrorNumber;
public int ErrorNumber()
{
return pErrorNumber;
}
public static int pErrorClass;
public int ErrorClass()
{
return pErrorClass;
}
public static int pErrorState;
public int ErrorState()
{
return pErrorState;
}
public static int pErrorLineNumber;
public int ErrorLineNumber()
{
return pErrorLineNumber;
}
public static bool pIsFound;
public bool IsFound()
{
return pIsFound;
}
#endregion
#region " Read methods "
public DataTable GetCustomerList(string LastName, string ConnectionString)
{
// Set up parameters in parameter array
SqlParameter[] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@LastName", SqlDbType.VarChar);
arParms[0].Value = LastName;
pTransactionSuccessful = true;
DataTable dtCustomerList = new DataTable("CustomerList");
try
{
//Change the name of the procedure
DataSet dsCustomerList = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "GetCustomerList", arParms);
dtCustomerList = dsCustomerList.Tables[0];
}
catch (SqlException ReadError)
{
pErrorMessage = ReadError.Message.ToString();
pErrorNumber = ReadError.Number;
pErrorClass = ReadError.Class;
pErrorState = ReadError.State;
pErrorLineNumber = ReadError.LineNumber;
pTransactionSuccessful = false;
}
return dtCustomerList;
}
public DataTable GetCustomerList(string ConnectionString)
{
// Set up parameters in parameter array
//SqlParameter[] arParms = new SqlParameter[1];
//arParms[0] = new SqlParameter("@LastName", SqlDbType.VarChar);
//arParms[0].Value = LastName;
pTransactionSuccessful = true;
DataTable dtCustomerList = new DataTable("CustomerList");
try
{
//Change the name of the procedure
DataSet dsCustomerList = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "GetCustomerList");
dtCustomerList = dsCustomerList.Tables[0];
}
catch (SqlException ReadError)
{
pErrorMessage = ReadError.Message.ToString();
pErrorNumber = ReadError.Number;
pErrorClass = ReadError.Class;
pErrorState = ReadError.State;
pErrorLineNumber = ReadError.LineNumber;
pTransactionSuccessful = false;
}
return dtCustomerList;
}
#endregion
#region " Insert methods "
#endregion
#region " Update methods "
#endregion
#region " Delete methods "
public void DeleteCustomer(string CustomerID, string ConnectionString)
{
// Set up parameters in parameter array
SqlParameter[] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@CustomerID", SqlDbType.NChar);
arParms[0].Value = CustomerID;
//Example of an output paramter
// arParms[n] = new SqlParameter("@Paramtern", SqlDbType.Bit);
// arParms[n].Direction = ParameterDirection.Output;
//Remember to adjust the array dimension when adding or subtracting elements.
pTransactionSuccessful = true;
try
{
//Need to change the procedure name
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "DeleteCustomer", arParms);
}
catch (SqlException DeleteError)
{
pErrorMessage = DeleteError.Message.ToString();
pErrorNumber = DeleteError.Number;
pErrorClass = DeleteError.Class;
pErrorState = DeleteError.State;
pErrorLineNumber = DeleteError.LineNumber;
pTransactionSuccessful = false;
}
//If using output paramters
//pSomeParameter = (bool)arParms[n].Value;
}
#endregion
}
}
public void UpdateCustomerInfo(string CustomerID, string CompanyName, string ContactName, string ContactTitle, string Address, string City, string Region, string PostalCode, string Country, string Phone, string Fax, string ConnectionString)
{
// Set up parameters in parameter array
SqlParameter[] arParms = new SqlParameter[11];
arParms[0] = new SqlParameter("@CustomerID", SqlDbType.NVarChar);
arParms[0].Value = CustomerID;
arParms[1] = new SqlParameter("@CompanyName", SqlDbType.NVarChar);
arParms[1].Value = CompanyName;
arParms[2] = new SqlParameter("@ContactName", SqlDbType.NVarChar);
arParms[2].Value = ContactName;
arParms[3] = new SqlParameter("@ContactTitle", SqlDbType.NVarChar);
arParms[3].Value = ContactTitle;
arParms[4] = new SqlParameter("@Address", SqlDbType.NVarChar);
arParms[4].Value = Address;
arParms[5] = new SqlParameter("@City", SqlDbType.NVarChar);
arParms[5].Value = City;
arParms[6] = new SqlParameter("@Region", SqlDbType.NVarChar);
arParms[6].Value = Region;
arParms[7] = new SqlParameter("@PostalCode", SqlDbType.NVarChar);
arParms[7].Value = PostalCode;
arParms[8] = new SqlParameter("@Country", SqlDbType.NVarChar);
arParms[8].Value = Country;
arParms[9] = new SqlParameter("@Phone", SqlDbType.NVarChar);
arParms[9].Value = Phone;
arParms[10] = new SqlParameter("@Fax", SqlDbType.NVarChar);
arParms[10].Value = Fax;
//Example of an output paramter
//arParms[n] = new SqlParameter("@Paramtern", SqlDbType.Bit);
//arParms[n].Direction = ParameterDirection.Output;
//Remember to adjust the array dimension when adding or subtracting elements.
pTransactionSuccessful = true;
try
{
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "UpdateCustomer", arParms);
}
catch (SqlException InsertError)
{
pErrorMessage = InsertError.Message.ToString();
pErrorNumber = InsertError.Number;
pErrorClass = InsertError.Class;
pErrorState = InsertError.State;
pErrorLineNumber = InsertError.LineNumber;
pTransactionSuccessful = false;
}
//If using output paramters
//pSomeParameter = (bool)arParms[n].Value;
}
public void InsertCustomerInfo(string CustomerID, string CompanyName, string ContactName, string ContactTitle, string Address, string City, string Region, string PostalCode, string Country, string Phone, string Fax, string ConnectionString)
{
// Set up parameters in parameter array
SqlParameter[] arParms = new SqlParameter[11];
arParms[0] = new SqlParameter("@CustomerID", SqlDbType.NVarChar);
arParms[0].Value = CustomerID;
arParms[1] = new SqlParameter("@CompanyName", SqlDbType.NVarChar);
arParms[1].Value = CompanyName;
arParms[2] = new SqlParameter("@ContactName", SqlDbType.NVarChar);
arParms[2].Value = ContactName;
arParms[3] = new SqlParameter("@ContactTitle", SqlDbType.NVarChar);
arParms[3].Value = ContactTitle;
arParms[4] = new SqlParameter("@Address", SqlDbType.NVarChar);
arParms[4].Value = Address;
arParms[5] = new SqlParameter("@City", SqlDbType.NVarChar);
arParms[5].Value = City;
arParms[6] = new SqlParameter("@Region", SqlDbType.NVarChar);
arParms[6].Value = Region;
arParms[7] = new SqlParameter("@PostalCode", SqlDbType.NVarChar);
arParms[7].Value = PostalCode;
arParms[8] = new SqlParameter("@Country", SqlDbType.NVarChar);
arParms[8].Value = Country;
arParms[9] = new SqlParameter("@Phone", SqlDbType.NVarChar);
arParms[9].Value = Phone;
arParms[10] = new SqlParameter("@Fax", SqlDbType.NVarChar);
arParms[10].Value = Fax;
//Example of an output paramter
// arParms[n] = new SqlParameter("@Paramtern", SqlDbType.Bit);
// arParms[n].Direction = ParameterDirection.Output;
//Remember to adjust the array dimension when adding or subtracting elements.
pTransactionSuccessful = true;
try
{
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "InsertCustomer", arParms);
}
catch (SqlException InsertError)
{
pErrorMessage = InsertError.Message.ToString();
pErrorNumber = InsertError.Number;
pErrorClass = InsertError.Class;
pErrorState = InsertError.State;
pErrorLineNumber = InsertError.LineNumber;
pTransactionSuccessful = false;
}
//If using output paramters
//pSomeParameter = (bool)arParms[n].Value;
}
public DataTable GetCustomerInfo(string CustomerID, string ConnectionString)
{
// Set up parameters in parameter array
SqlParameter[] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@CustomerID", SqlDbType.NVarChar);
arParms[0].Value = CustomerID;
pTransactionSuccessful = true;
DataTable dtSomeInfo = new DataTable("Customers");
try
{
DataSet dsSomeInfo = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "GetCustomerInfo", arParms);
dtSomeInfo = dsSomeInfo.Tables[0];
}
catch (SqlException ReadError)
{
pErrorMessage = ReadError.Message.ToString();
pErrorNumber = ReadError.Number;
pErrorClass = ReadError.Class;
pErrorState = ReadError.State;
pErrorLineNumber = ReadError.LineNumber;
pTransactionSuccessful = false;
}
return dtSomeInfo;
}
/****************************************************/
/* SP_GetCustomerList @LastName*/
/* By Joyce Jones, Danuta Lemma, and Xiguang Zhao */
/****************************************************/
select *
from customers;
select *
from tblProfile;
select CompanyName
from Customers
where CompanyName LIKE 'Bon%'
/***************/
--DROP PROC GetCustomerList
CREATE PROC GetCustomerList
@LastName NVARCHAR(50)
AS
BEGIN
IF @LastName IS NULL
BEGIN
SELECT CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax, UserID
FROM Customers
END
ELSE
BEGIN
SELECT CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, UserID
FROM Customers
WHERE CompanyName LIKE '%'+ @LastName+'%'
END
END
/* Test Case: LastName is NULL */
BEGIN
DECLARE @LastName NVARCHAR(50)
SET @LastName = null
EXEC GetCustomerList @LastName
END
/* Test Case: Suppose LastName is 'ANTON' */
BEGIN
DECLARE @LastName NVARCHAR(50)
SET @LastName = 'ANTON'
EXEC GetCustomerList @LastName
END
/****************************************************/
/* SP_DeleteCustomer @CustomerID */
/* By Joyce Jones, Danuta Lemma, and Xiguang Zhao */
/****************************************************/
select *
from customers;
select *
from tblUser;
/* Insert a row into Table tblUser and Table Customers, respectively, for deleting test later */
Insert tblUser(Login, Password,UserType)
Values('ABC','CB', 'C')
Insert Customers (CustomerID, CompanyName, UserID)
Values( '1', 'ASD', 102)
/************/
--DROP PROC DeleteCustomer
CREATE PROC DeleteCustomer
@CustomerID NVARCHAR(50)
AS
BEGIN
IF @CustomerID IS NOT NULL
BEGIN
DELETE from Customers WHERE CustomerID = @CustomerID;
END
END
/* Test Case: delete a row that does not exist */
BEGIN
DECLARE @CustomerID NVARCHAR(50)
SET @CustomerID = '2'
EXEC DeleteCustomer @CustomerID
END
/* Test Case: delete a row which the CustomerID is '1'*/
BEGIN
DECLARE @CustomerID NVARCHAR(50)
SET @CustomerID = '1'
EXEC DeleteCustomer @CustomerID
END