/// <summary>
/// Method to remove an extended property from a table or column.
/// </summary>
/// <param name="model"></param>
private void Remove(ExtendedPropertyModel model)
{
var connectionString = Request.Headers["connectionString"].FirstOrDefault();
HttpRequires.IsNotNull(connectionString, "Ivnalid Connection");
HttpRequires.IsNotNull(model, "Invalid Properties");
var dalResponse = _propertyDal.DeleteProperty(model, connectionString);
HttpAssert.Success(dalResponse);
}
/// <summary>
/// Helper method to validate the property model before persistance.
/// </summary>
/// <param name="model"></param>
private void ValidatePropertyModel(ExtendedPropertyModel model)
{
if (String.IsNullOrWhiteSpace(model.Name))
throw new ArgumentException("Extended Propery Model requires a Name");
else if (String.IsNullOrWhiteSpace(model.TableName))
{
throw new ArgumentException("Extended Property Model requires either a table name be specified");
}
}
/// <summary>
/// Method to delete an extended property from a sql server database object
/// </summary>
/// <param name="model">Model containing the data needed for the delete</param>
/// <param name="connectionString">Connection string for connecting to the desired database</param>
/// <returns></returns>
public DalResponseModel DeleteProperty(ExtendedPropertyModel model, string connectionString)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
var parameters = new List<SqlParameter>
{
new SqlParameter("@pPropertyName", model.Name),
new SqlParameter("@pObjectName", model.TableName),
BuildColumnParam(model.ColumnName)
};
_dalHelper.Delete(new DalHelperModel
{
CommandText = SQL_TEMPLATE_REMOVE,
Connection = conn,
Parameters = parameters
});
return new DalResponseModel { HasError = false };
}
}
catch (Exception ex)
{
return new DalResponseModel
{
Exception = ex,
HasError = true
};
}
}
/// <summary>
/// Method to add an extended property to a sql server database object
/// </summary>
/// <param name="model">Model containing the data needed to create the property</param>
/// <param name="connectionString">Connection string for connecting to the desired database</param>
/// <returns></returns>
public DalResponseModel<bool> AddProperty(ExtendedPropertyModel model, string connectionString)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
var parameters = new List<SqlParameter>()
{
new SqlParameter("@pPropertyName", model.Name),
new SqlParameter("@pPropertyValue", model.Text),
new SqlParameter("@pSchema", model.SchemaName),
new SqlParameter("@pTableName", model.TableName),
BuildColumnParam(model.ColumnName)
};
var result = _dalHelper.Insert(new DalHelperModel
{
CommandText = SQL_TEMPLATE_ADD,
Connection = conn,
Parameters = parameters
});
return new DalResponseModel<bool>
{
HasError = false,
Result = result > 0
};
}
}
catch (Exception ex)
{
return new DalResponseModel<bool>
{
Exception = ex,
HasError = true,
Result = default(bool)
};
}
}
/// <summary>
/// Method to update an extended property of a table or column
/// </summary>
/// <param name="model"></param>
private void Update(ExtendedPropertyModel model)
{
var connectionString = Request.Headers["connectionString"].FirstOrDefault();
HttpRequires.IsNotNull(connectionString, "Invalid Connection");
HttpRequires.IsNotNull(model, "Invalid Properties");
//Using the sql server system procedures I had (for add and delete). Instead of an in
// place update, the process is to first delete the existing property, and then add
// the new values. To avoid a situation where the delete succeeds but the add fails,
// we use a transaction scope.
using (var scope = new TransactionScope())
{
var deleteResp = _propertyDal.DeleteProperty(model, connectionString);
var addResp = _propertyDal.AddProperty(model, connectionString);
HttpAssert.Success(deleteResp);
HttpAssert.Success(addResp);
scope.Complete();
}
}