SET NOCOUNT ON

------------------------------------------------
-- UpdateCustomerAddress
------------------------------------------------
ALTER PROCEDURE UpdateCustomerAddress
    @CustomerId     Int,
    @Address        NVARCHAR(255) = NULL,
    @Country        NVARCHAR(40) = NULL,
    @PhoneNumber    NVARCHAR(30) = NULL,
    @Fax            NVARCHAR(30) = NULL
AS
    SET NOCOUNT ON

    -- Update an existing address record

    UPDATE Addresses
       SET Address = @Address,
           Country = @Country,
           PhoneNumber = @PhoneNumber,
           Fax = @Fax
     WHERE CustomerId = @CustomerId
       AND CustomerId IS NOT NULL

    RETURN 0
------------------------------------------------
-- duplicate index on Addresses (CustomerId)
------------------------------------------------

 

 

 

 

SET NOCOUNT

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。

语法

SET NOCOUNT { ON | OFF }

注释

当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。

即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。

当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示"nn rows affected"。

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

权限

SET NOCOUNT 权限默认授予所有用户。

示例

下例在 osql 实用工具或 SQL Server 查询分析器中执行时，可防止显示有关受影响的行数的信息。

USE pubsGO-- Display the count message.SELECT au_lname FROM authorsGOUSE pubsGO-- SET NOCOUNT to ON and no longer display the count message.SET NOCOUNT ONGOSELECT au_lname FROM authorsGO-- Reset SET NOCOUNT to OFF.SET NOCOUNT OFFGO

 

------------------------------------------------
-- UpdateCustomer
------------------------------------------------
ALTER PROCEDURE UpdateCustomer
    @PKId           INT = NULL,
    @Email          NVARCHAR(50) = NULL,
    @Password       BINARY(24) = NULL,
    @Name           NVARCHAR(40) = NULL,
    @Address        NVARCHAR(255) = NULL,
    @Country        NVARCHAR(40) = NULL,
    @PhoneNumber    NVARCHAR(30) = NULL,
    @Fax            NVARCHAR(30) = NULL
AS
    SET XACT_ABORT ON

    BEGIN TRANSACTION
        -- Update customer address by CustomerId
        EXEC UpdateCustomerAddress @PKId,
                                   @Address,
                                   @Country,
                                   @PhoneNumber,
                                   @Fax

        -- Update customers table with new customer info
        UPDATE Customers
           SET Email = @Email,
               Password = @Password,
               Name = @Name
         WHERE PKId = @PKId
   
    COMMIT TRANSACTION

    RETURN 0
------------------------------------------------
-- unique index on Customers (PKId)
------------------------------------------------

 

 

public bool UpdateCustomer(CustomerData customer)
  {
   byte [] password=(byte[])customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PASSWORD_FIELD];
   customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PASSWORD_FIELD]=CreateDbPassword(password);
   return (new rcustomer()).Update(customer);
  }

 

 

public bool Update(CustomerData customer)
  {
   DataRow row=customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0];
   bool b=Validate(row);
   if(row.GetColumnError(CustomerData.EMAIL_FIELD)==String.Empty)
   {
    CustomerData ecustomer=GetCustomerByEmail(row[CustomerData.EMAIL_FIELD].ToString());
    if(null!=ecustomer)
    {
     if(row[CustomerData.PKID_FIELD].ToString()!=ecustomer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PKID_FIELD].ToString())
     {
      row.SetColumnError(CustomerData.EMAIL_FIELD,CustomerData.EMAIL_FIELD_NOT_UNIQUE);
      row.RowError=CustomerData.INVALID_FIELDS;
      b=false;
     }
    }
   }
   if(b)
   {
    using(Customers customersAccess=new Customers())
    {
     b=customersAccess.UpdateCustomer(customer);
    }
   }
   return b;
  }

 

 

 

 

private bool Validate(DataRow customerRow)
  {
   bool a;
   customerRow.ClearErrors();
   a=IsValidEmail(customerRow);
   a&=IsValidField(customerRow,CustomerData.NAME_FIELD,40);
   a&=IsValidField(customerRow,CustomerData.ADDRESS_FIELD,255);
   a&=IsValidField(customerRow,CustomerData.COUNTRY_FIELD,40);
   a&=IsValidField(customerRow,CustomerData.PHONE_FIELD,30);
   if(!a)
   {
    customerRow.RowError=CustomerData.INVALID_FIELD;
   }
   return a;
  }

 

 

private SqlCommand GetUpdateCommand()
  {
   if ( updateCommand == null )
   {
    
    updateCommand = new SqlCommand("UpdateCustomer",new SqlConnection(strConn));
    updateCommand.CommandType = CommandType.StoredProcedure;
           
    SqlParameterCollection sqlParams = updateCommand.Parameters;
           
    sqlParams.Add(new SqlParameter(PKID_PARM, SqlDbType.Int));
    sqlParams.Add(new SqlParameter(EMAIL_PARM, SqlDbType.NVarChar, 50));
    sqlParams.Add(new SqlParameter(PASSWORD_PARM, SqlDbType.Binary, 24));
    sqlParams.Add(new SqlParameter(NAME_PARM, SqlDbType.NVarChar, 40));
    sqlParams.Add(new SqlParameter(ADDRESS_PARM, SqlDbType.NVarChar, 255));
    sqlParams.Add(new SqlParameter(COUNTRY_PARM, SqlDbType.NVarChar, 40));
    sqlParams.Add(new SqlParameter(PHONE_PARM, SqlDbType.NVarChar, 30));
    sqlParams.Add(new SqlParameter(FAX_PARM, SqlDbType.NVarChar, 30));
    
    sqlParams[PKID_PARM].SourceColumn = CustomerData.PKID_FIELD;
    sqlParams[EMAIL_PARM].SourceColumn = CustomerData.EMAIL_FIELD;
    sqlParams[PASSWORD_PARM].SourceColumn = CustomerData.PASSWORD_FIELD;
    sqlParams[NAME_PARM].SourceColumn = CustomerData.NAME_FIELD;
    sqlParams[ADDRESS_PARM].SourceColumn = CustomerData.ADDRESS_FIELD;
    sqlParams[COUNTRY_PARM].SourceColumn = CustomerData.COUNTRY_FIELD;
    sqlParams[PHONE_PARM].SourceColumn = CustomerData.PHONE_FIELD;
    sqlParams[FAX_PARM].SourceColumn = CustomerData.FAX_FIELD;
   }
           
   return updateCommand;
  }

 

 

 

 

public bool UpdateCustomer(CustomerData customer)
  {
   if ( booksadapter == null )
   {
    throw new System.ObjectDisposedException( GetType().FullName );
   }           
  
   booksadapter.UpdateCommand = GetUpdateCommand();
           
   booksadapter.Update(customer, CustomerData.CUSTOMERS_TABLE);
   
   if ( customer.HasErrors )
   {
    customer.Tables[CustomerData.CUSTOMERS_TABLE].GetErrors()[0].ClearErrors();
    return false;
   }
   else
   {
    customer.AcceptChanges();
    return true;
   }
  }

 

 

 

 

private bool SaveCustomer()
  {
   DataRow cdrow;
   bool a=false;
   byte[] bytePassword=null;
   //Trim the phone and fax numbers
   EmailTextBox.Text=EmailTextBox.Text.Trim();
   PasswordTextBox.Text=PasswordTextBox.Text;
   ConfirmPasswordTextBox.Text=ConfirmPasswordTextBox.Text;
   AcctNameTextBox.Text=AcctNameTextBox.Text.Trim();
   PhoneTextBox.Text=PhoneTextBox.Text.Trim();
   FaxTextBox.Text=FaxTextBox.Text.Trim();
   AddressTextBox.Text=AddressTextBox.Text.Trim();
   CountryTextBox.Text=CountryTextBox.Text.Trim();
   String tmpPassword=PasswordTextBox.Text;
   if(tmpPassword==ConfirmPasswordTextBox.Text)
   {
    SHA1 sha1=SHA1.Create();
    bytePassword=sha1.ComputeHash(Encoding.Unicode.GetBytes(tmpPassword));
   }
   else
   {
    tmpPassword="";//Force an error on password
   }
   foreach(IValidator val in Page.Validators)
   {
    val.Validate();
   }
   if(Page.IsValid)
   {
    if(moduleEditMode)
    {
     /* workaround: Create and Update the dataset.Ideally we would do Customer.Clone()
        here and update the row, however DataSet.Clone() is broken.Instead,
        the row before passing it to the facade layer.This ensures that the
        row is marked as dirty when the data access layer does the update call  */
     cd=new CustomerData();
     cdrow=cd.Tables[CustomerData.CUSTOMERS_TABLE].NewRow();
     cdrow[CustomerData.EMAIL_FIELD]=EmailTextBox.Text;
     cdrow[CustomerData.PASSWORD_FIELD]=bytePassword;
     cdrow[CustomerData.NAME_FIELD]=AcctNameTextBox.Text;
     cdrow[CustomerData.ADDRESS_FIELD]=AddressTextBox.Text;
     cdrow[CustomerData.COUNTRY_FIELD]=CountryTextBox.Text;
     cdrow[CustomerData.PHONE_FIELD]=PhoneTextBox.Text;
     cdrow[CustomerData.FAX_FIELD]=FaxTextBox.Text;
     //Add the row to the dataset
     cd.Tables[CustomerData.CUSTOMERS_TABLE].Rows.Add(cdrow);
     cd.AcceptChanges();
     cdrow[CustomerData.PKID_FIELD]=Customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PKID_FIELD];
     //workaround: End of wordarount
     //Save changes to the database
     a=(new CustomerSystem()).UpdateCustomer(cd);
    }
    else
    {
     a = (new CustomerSystem()).CreateCustomer(EmailTextBox.Text,
      bytePassword,
      AcctNameTextBox.Text,
      AddressTextBox.Text,
      CountryTextBox.Text,
      PhoneTextBox.Text,
      FaxTextBox.Text,
      out cd);
    }
   }
   //Store customer in the Session
   if(a && (cd!=null))
   {
    //1.update customer in session
    //2.update customer in cart
    Customer=cd;
    ShoppingCart().Customer=cd;
   }
   return a;
  }

 

 

 

 

public bool UpdateCustomer(CustomerData customer)
  {
   byte [] password=(byte[])customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PASSWORD_FIELD];
   customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PASSWORD_FIELD]=CreateDbPassword(password);
   return (new rcustomer()).Update(customer);
  }

 

 

public bool Update(CustomerData customer)
  {
   DataRow row=customer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0];
   bool b=Validate(row);
   if(row.GetColumnError(CustomerData.EMAIL_FIELD)==String.Empty)
   {
    CustomerData ecustomer=GetCustomerByEmail(row[CustomerData.EMAIL_FIELD].ToString());
    if(null!=ecustomer)
    {
     if(row[CustomerData.PKID_FIELD].ToString()!=ecustomer.Tables[CustomerData.CUSTOMERS_TABLE].Rows[0][CustomerData.PKID_FIELD].ToString())
     {
      row.SetColumnError(CustomerData.EMAIL_FIELD,CustomerData.EMAIL_FIELD_NOT_UNIQUE);
      row.RowError=CustomerData.INVALID_FIELDS;
      b=false;
     }
    }
   }
   if(b)
   {
    using(Customers customersAccess=new Customers())
    {
     b=customersAccess.UpdateCustomer(customer);
    }
   }
   return b;
  }