public ResponseModelDto DeleteRedshiftData(TableColumnDto dtoModel)
{
ResponseModelDto responsemodel = new ResponseModelDto();
if (dtoModel != null)
{
var model = mapper.Map <TableColumnModel>(dtoModel);
var qry = String.Format("Delete from {0} where ", model.TableName);
var compareColumn = "";
var itemCount = 0;
foreach (var item in model.ColumnsList)
{
if (itemCount > 0)
{
compareColumn += " AND " + item.ColumnName + "=@" + item.ColumnName;
}
else
{
compareColumn += item.ColumnName + "=@" + item.ColumnName;
}
itemCount++;
}
qry += compareColumn;
responsemodel = redshiftRepository.DeleteRedshiftData(model, qry);
}
return(responsemodel);
}
public ResponseModelDto SaveRedshiftData(TableColumnDto modelDto)
{
ResponseModelDto responsemodel = new ResponseModelDto();
var model = mapper.Map <TableColumnModel>(modelDto);
// create dynamic query RedshiftDataModel
var columnNames = "";
var columnValues = "";
foreach (var item in model.ColumnsList)
{
columnNames += item.ColumnName + ",";
if (item.DataType == "date")
{
columnValues += string.IsNullOrEmpty(item.ColumnValue) ? "null," : "@" + item.ColumnName + ",";
}
else
{
columnValues += "@" + item.ColumnName + ",";
}
}
model.ColumnValue = columnValues.TrimEnd(',');
model.ColumnName = columnNames.TrimEnd(',');
responsemodel = redshiftRepository.SaveRedshiftData(model);
return(responsemodel);
}
/// <summary>
/// Delete redshift data
/// </summary>
/// <param name="model"></param>
/// <param name="sqlquery"></param>
/// <returns></returns>
public ResponseModelDto DeleteRedshiftData(TableColumnModel model, string sqlquery)
{
ResponseModelDto responsemodel = new ResponseModelDto();
if (model.ColumnsList.Count > 0)
{
using (var conn = new NpgsqlConnection(CommonClass.GetConnectionString(_settings.ServerName, _settings.Port, _settings.UserName, _settings.Password, _settings.Database)))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
for (int i = 0; i < model.ColumnsList.Count; i++)
{
switch (model.ColumnsList[i].DataType.ToString())
{
case "date":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Date, string.IsNullOrEmpty(model.ColumnValue) ? (DateTime?)null : Convert.ToDateTime(CommonClass.ChangeDateFormat(model.ColumnsList[i].ColumnValue)));
break;
case "text":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Varchar, model.ColumnsList[i].ColumnValue);
break;
case "int":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Integer, Convert.ToInt32(model.ColumnsList[i].ColumnValue));
break;
case "bool":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Boolean, model.ColumnValue);
break;
case "float":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Double, model.ColumnValue);
break;
}
}
cmd.Connection = conn;
cmd.CommandText = sqlquery;
cmd.ExecuteReader();
}
}
responsemodel.StatusCode = "200";
responsemodel.Status = true;
responsemodel.Message = "Data successfully deleted";
}
return(responsemodel);
}
public IHttpActionResult AddResponse(ResponseModelDto modelDto)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var request = _unitOfWork.Requests.GetSingle(r => r.Id == modelDto.RequestId);
var userId = User.Identity.GetUserId();
var responseInDb = _unitOfWork.Responses.GetSingle(r => r.RequestId == modelDto.RequestId && r.UserId.Equals(userId));
if (request == null)
{
return(NotFound());
}
if (responseInDb != null)
{
DeleteResponse(responseInDb.Id);
}
else
{
ResponseModel response = Mapper.Map <ResponseModelDto, ResponseModel>(modelDto);
response.UserId = User.Identity.GetUserId();
_unitOfWork.Responses.Add(response);
_unitOfWork.Complete();
var responsesQuantity = _unitOfWork.Responses.FindBy(r => r.RequestId == modelDto.RequestId);
if (responsesQuantity.Count() >= 2)
{
responsesQuantity = responsesQuantity.Where(r => r.Accept == true);
if (responsesQuantity.Count() == 2)
{
request.Accepted = true;
var usr = _unitOfWork.Users.GetSingle(u => u.Id.Equals(request.UserId));
usr.Points += request.RequestedQuantity;
_unitOfWork.Complete();
}
}
}
return(Created("api/Requests/" + modelDto.Id, modelDto));
}
/// <summary>
/// Save redshift data
/// </summary>
/// <param name="model"></param>
public ResponseModelDto SaveRedshiftData(TableColumnModel model)
{
ResponseModelDto responsemodel = new ResponseModelDto();
var qry = String.Format("insert into {0} ({1}) values ({2})", model.TableName, model.ColumnName, model.ColumnValue);
using (var conn = new NpgsqlConnection(CommonClass.GetConnectionString(_settings.ServerName, _settings.Port, _settings.UserName, _settings.Password, _settings.Database)))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
for (int i = 0; i < model.ColumnsList.Count; i++)
{
switch (model.ColumnsList[i].DataType.ToString())
{
case "date":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Date, string.IsNullOrEmpty(model.ColumnValue) ? (DateTime?)null : Convert.ToDateTime(CommonClass.ChangeDateFormat(model.ColumnsList[i].ColumnValue)));
break;
case "text":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Varchar, model.ColumnsList[i].ColumnValue);
break;
case "int":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Integer, Convert.ToInt32(model.ColumnsList[i].ColumnValue));
break;
case "bool":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Boolean, Convert.ToBoolean(model.ColumnsList[i].ColumnValue));
break;
case "float":
cmd.Parameters.AddWithValue(model.ColumnsList[i].ColumnName, NpgsqlDbType.Double, Convert.ToDouble(model.ColumnsList[i].ColumnValue));
break;
}
}
cmd.Connection = conn;
cmd.CommandText = qry;
if (!CommonClass.VerifyQuery(qry))
{
cmd.ExecuteReader();
responsemodel.Message = "Data successfully saved";
responsemodel.StatusCode = "200";
responsemodel.Status = true;
}
else
{
responsemodel.Message = "Characters are not valid in query";
responsemodel.StatusCode = "400";
responsemodel.Status = false;
}
}
}
return(responsemodel);
}