Thursday, May 5, 2011

BindingList save to database

     /// <summary>
      /// Saves the customer properties back to the database.
      /// </summary>
      public override Boolean SaveItem()
      {
        Boolean success = false;

        // If the entity state is added, need to get the CustomerId back.
        bool isOutput = EntityState == EntityStateType.Added ? true : false;

        // Pass the properties back to the DAC
        // This would not be needed if the base class had code to use Reflection to build
        // the parameters from the object properties.
        int retVal = Dac.ExecuteNonQuery("CustomerSave_sp",
                Dac.Parameter(cnCustomerID, CustomerId, isOutput),
                Dac.Parameter(cnLastName, LastName),
                Dac.Parameter(cnFirstName, FirstName),
                Dac.Parameter(cnAddress, Address),
                Dac.Parameter(cnCity, City),
                Dac.Parameter(cnState, State),
                Dac.Parameter(cnZip, Zip),
                Dac.Parameter(cnPhone, Phone),
                Dac.Parameter(cnLastUpdateUser, System.Environment.UserName),
                Dac.Parameter(cnRowState, EntityState.ToString()));

        // If it was an add, update the product ID
        if (EntityState == EntityStateType.Added)
          CustomerId = retVal;

        // Reset the entity's state
        this.SetEntityState(EntityStateType.Unchanged);

        // Assume success
        success = true;

        return success;
      }
The DAC component is my data access layer. The ExecuteNonQuery method looks like this:
    /// <summary> 
   /// Executes a stored procedure that does not return a dataTable 
/// and returns the
/// first output parameter. 
/// </summary>     /// <param name="storedProcedureName">Name of the stored procedure 
/// to execute</param>
/// <param name="arrParam">Parameters required by the stored
/// procedure</param>
/// <returns>First output parameter</returns> 
public static int ExecuteNonQuery(string storedProcedureName, params SqlParameter[] arrParam) 
 {
       int retVal=0;
       SqlParameter firstOutputParameter = null;
       DataTable dt = new DataTable();
        // Open the connection 
       using (SqlConnection cnn = new SqlConnection(Properties.SettingsDefault.PTConnectionString)) 
       {
         cnn.Open();
           // Define the command
          using (SqlCommand cmd= new SqlCommand() )
         {
           cmd.Connection = cnn;
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = storedProcedureName;
            // Handle the parameters 
           if (arrParam != null)
           {
             foreach (SqlParameter param in arrParam)
             {
               cmd.Parameters.Add(param);
               if (firstOutputParameter == null && param.Direction==ParameterDirection.Output 
                                                             && param.SqlDbType == SqlDbType.Int)
               {
                 firstOutputParameter = param;
               }
             }
           }
            // Execute the stored procedure 
           cmd.ExecuteNonQuery();
            // Return the first output parameter value 
           if (firstOutputParameter != null)
             retVal = (int)firstOutputParameter.Value;
         }
       }
       return retVal;
     }  

No comments :

Post a Comment