/// <summary>
/// Execute the Sql Querry requested buy protractor
/// </summary>
/// <param name="executableTestDataStep">the ExecutableTestData object</param>
/// <param name="testQueueId">the testQueue identifier</param>
/// <returns>result of querry in string</returns>
public ResultMessage <object> ExecuteQuery(ExecutableTestData executableTestDataStep, long testQueueId)
{
ResultMessage <TblTestQueueDto> resultTestQueue = this.testQueueService.GetById(testQueueId);
if (resultTestQueue.Item.SchedulerId.HasValue)
{
ResultMessage <TblSchedulerDto> schedulerDto = this.schedulerService.GetById(resultTestQueue.Item.SchedulerId.Value);
long environMentId = Convert.ToInt64(schedulerDto.Item.UrlId);
if (environMentId == 0)
{
environMentId = this.environmentService.GetDefaultEnvironment().Item.Id;
}
return(this.ExecuteSql(executableTestDataStep, environMentId));
}
else
{
long environMentId = Convert.ToInt64(resultTestQueue.Item.Settings.UrlId);
if (environMentId == 0)
{
environMentId = this.environmentService.GetDefaultEnvironment().Item.Id;
}
return(this.ExecuteSql(executableTestDataStep, environMentId));
}
}
/// <summary>
/// Updates the automatic increment.
/// </summary>
/// <param name="executableTestData">The executable test data.</param>
/// <param name="userId">The user identifier.</param>
/// <returns>incremented value</returns>
public async Task <ResultMessage <string> > UpdateAutoIncrement(ExecutableTestData executableTestData, long userId)
{
var result = new ResultMessage <string>();
lock (this.thisLock)
{
if (executableTestData.SharedTestDataId > 0)
{
ResultMessage <TblLnkTestDataSharedTestDataDto> lnkTestDataSharedTestdata = this.testDataSharedTestDataMapService.GetByTestDataIdAndSharedTestDataId(executableTestData.Id, executableTestData.SharedTestDataId);
if (!lnkTestDataSharedTestdata.IsError)
{
if (lnkTestDataSharedTestdata.Item.NewValue != string.Empty)
{
result.Item = this.GetAutoIncrementValue(lnkTestDataSharedTestdata.Item.NewValue);
lnkTestDataSharedTestdata.Item.NewValue = "#autoincrement#" + result.Item;
this.testDataSharedTestDataMapService.SaveOrUpdate(lnkTestDataSharedTestdata.Item, userId);
}
else
{
ResultMessage <TblSharedTestDataDto> sharedTestData = this.sharedTestDataService.GetById(executableTestData.SharedTestDataId);
if (!sharedTestData.IsError)
{
result.Item = this.GetAutoIncrementValue(sharedTestData.Item.Value);
lnkTestDataSharedTestdata.Item.NewValue = "#autoincrement#" + result.Item;
this.testDataSharedTestDataMapService.SaveOrUpdate(lnkTestDataSharedTestdata.Item, userId);
}
}
}
else
{
ResultMessage <TblSharedTestDataDto> sharedTestData = this.sharedTestDataService.GetById(executableTestData.SharedTestDataId);
if (!sharedTestData.IsError)
{
result.Item = this.GetAutoIncrementValue(sharedTestData.Item.Value);
TblLnkTestDataSharedTestDataDto testDataSharedTestData = new TblLnkTestDataSharedTestDataDto {
NewValue = "#autoincrement#" + result.Item, SharedTestDataId = sharedTestData.Item.Id, TestDataId = executableTestData.Id, ModifiedBy = userId, CreatedBy = userId
};
this.testDataSharedTestDataMapService.SaveOrUpdate(testDataSharedTestData, userId);
}
}
}
else
{
TblTestData testData = this.table.Find(x => x.Id == executableTestData.Id).FirstOrDefault();
result.Item = this.GetAutoIncrementValue(testData.Value);
testData.Value = "#autoincrement#" + result.Item;
this.table.Update(testData);
this.table.Commit();
}
}
return(result);
}
public IHttpActionResult ExecuteSqlForProtractor(ExecutableTestData executableTestData, long testQueueId)
{
var result = new ResultMessage <object>();
try
{
result = this.executeSqlForProtractorService.ExecuteQuery(executableTestData, testQueueId);
}
catch (Exception ex)
{
this.LoggerService.LogException(ex);
result.Messages.Add(new Message(null, ex.Message));
}
return(this.CreateCustomResponse(result));
}
public async Task <IHttpActionResult> UpdateAutoIncrement(ExecutableTestData executableTestData)
{
var result = new ResultMessage <string>();
try
{
result = await this.testQueueExecutableService.UpdateAutoIncrement(executableTestData, this.UserId);
}
catch (Exception ex)
{
this.LoggerService.LogException(ex);
result.Messages.Add(new Message(null, ex.Message));
}
return(this.CreateCustomResponse(result));
}
/// <summary>
/// Parse query for Sql
/// </summary>
/// <param name="executableTestDataStep">executableTestDataStep object</param>
/// <returns>Sql command object for Execute Sql</returns>
private SqlCommand ParseQueryForParameters(ExecutableTestData executableTestDataStep)
{
SqlCommand cmd = new SqlCommand();
int i = 1;
foreach (var item in executableTestDataStep.VariablesUsedInQuery)
{
string strToReplace = "{" + item.Name + "}";
string strToPut = "@Param" + i++;
if (executableTestDataStep.Value.IndexOf(strToReplace) > 0)
{
executableTestDataStep.Value = executableTestDataStep.Value.Replace(strToReplace, strToPut);
cmd.Parameters.Add(strToPut, item.Value);
}
}
cmd.CommandText = executableTestDataStep.Value;
return(cmd);
}
/// <summary>
/// Execute Sql Command on Sql Server
/// </summary>
/// <param name="executableTestDataStep">the executable data step</param>
/// <param name="environMentId">the Environment Identifier</param>
/// <returns>resul of query execution</returns>
private ResultMessage <object> ExecuteSql(ExecutableTestData executableTestDataStep, long environMentId)
{
var result = new ResultMessage <object>();
try
{
var dataBaseConnectionDto = this.dataBaseConnectionService.GetSensitiveDataByEnvironmentAndCategoryId(environMentId, executableTestDataStep.CategoryId.Value);
if (dataBaseConnectionDto.Item != null)
{
SqlConnection con = new SqlConnection();
if (dataBaseConnectionDto.Item.Authentication == (int)SqlAuthenticationType.WindowsAuthentication)
{
con.ConnectionString = "Server=" + dataBaseConnectionDto.Item.ServerName + ";Database=" + dataBaseConnectionDto.Item.DataBaseName + ";Integrated Security=SSPI;";
}
else
{
con.ConnectionString = "Server=" + dataBaseConnectionDto.Item.ServerName + ";Database=" + dataBaseConnectionDto.Item.DataBaseName + ";user id=" + dataBaseConnectionDto.Item.UserName + ";password="******";";
}
SqlCommand cmd = this.ParseQueryForParameters(executableTestDataStep);
cmd.Connection = con;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.RecordsAffected > 0)
{
string jsonString = "[{'RecordsAffected':" + dr.RecordsAffected + "}]";
object jobject = (object)JsonConvert.DeserializeObject(jsonString);
result.Item = jobject;
}
else
{
DataTable dt = new DataTable();
dt.Load(dr);
if (dt.Rows.Count > 0)
{
string jsonString = string.Empty;
DataTable dtNew = new DataTable();
dtNew = dt.AsEnumerable().Take(int.Parse(ConfigurationManager.AppSettings["ExecuteSqlRowsToTake"].ToString())).CopyToDataTable();
jsonString = JsonConvert.SerializeObject(dtNew);
object jobject = (object)JsonConvert.DeserializeObject(jsonString);
result.Item = jobject;
}
else
{
result.Item = "null";
}
}
con.Close();
}
else
{
string jsonString = "[{'ExceptionMessage':" + "No connection strung found with the mentioned category and environment id" + "}]";
object jobject = (object)JsonConvert.DeserializeObject(jsonString);
result.Item = jobject;
}
return(result);
}
catch (Exception ex)
{
string jsonString = "[{'ExceptionMessage':" + ex.Message + "}]";
object jobject = (object)JsonConvert.DeserializeObject(jsonString);
result.Item = jobject;
return(result);
}
}
