/// <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>
/// 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 add an extended property to a table or table column
/// </summary>
/// <param name="model"></param>
private void Add(ExtendedPropertyModel model)
{
var connectionString = Request.Headers["connectionString"].FirstOrDefault();
HttpRequires.IsNotNull(connectionString, "Invalid Connection");
HttpRequires.IsNotNull(model, "Invalid Properties");
var response = _propertyDal.AddProperty(model, connectionString);
HttpAssert.Success(response);
}
/// <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();
}
}
/// <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)
});
}
}
public IHttpActionResult AddProperty([FromBody] ExtendedPropertyModel propertyModel)
{
try
{
var connectionString = Request.Headers.GetValues("connectionString").FirstOrDefault();
HttpRequires.IsNotNull(connectionString, "Invalid Connection");
HttpRequires.IsNotNull(propertyModel, "Invalid Properties");
ValidatePropertyModel(propertyModel);
var response = _propertyDal.AddProperty(propertyModel, connectionString);
HttpAssert.Success(response);
return(Ok());
}
catch (ArgumentException ex)
{
return(BadRequest(ex.Message));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public IHttpActionResult UpdateProperty([FromBody] ExtendedPropertyModel propertyModel)
{
try
{
var connectionString = Request.Headers.GetValues("connectionString").FirstOrDefault();
HttpRequires.IsNotNull(connectionString, "Invalid Connection");
HttpRequires.IsNotNull(propertyModel, "Invalid Properties");
ValidatePropertyModel(propertyModel);
DalResponseModel deletionResponse, addResponse;
//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())
{
deletionResponse = _propertyDal.DeleteProperty(propertyModel, connectionString);
addResponse = _propertyDal.AddProperty(propertyModel, connectionString);
HttpAssert.Success(deletionResponse);
HttpAssert.Success(addResponse);
scope.Complete();
}
return(Ok());
}
catch (ArgumentException ex)
{
return(BadRequest(ex.Message));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
