public override void PerformDBAction()
{
if (!Connect())
{
Act.Error = "Failed to connect to Couchbase DB";
return;
}
string SQL = Act.SQL;
string keyspace = Act.Keyspace;
ValueExpression VE = new ValueExpression(Db.ProjEnvironment, Db.BusinessFlow, Db.DSList);
VE.Value = SQL;
string SQLCalculated = VE.ValueCalculated;
string bucketName = GetBucketName(SQLCalculated);
if (!ConnecttoBucket(bucketName))
{
Act.Error = "failed to connect to bucket " + bucketName;
return;
}
else
{
IQueryResult <dynamic> result = null;
try
{
switch (Action)
{
case Actions.ActDBValidation.eDBValidationType.FreeSQL:
result = clusterCB.Query <dynamic>(SQLCalculated);
for (int i = 0; i < result.Rows.Count; i++)
{
Act.ParseJSONToOutputValues(result.Rows[i].ToString(), i + 1);
}
break;
case Actions.ActDBValidation.eDBValidationType.RecordCount:
result = clusterCB.Query <dynamic>("Select Count(*) as RECORDCOUNT from `" + bucketName + "`");
Act.ParseJSONToOutputValues(result.Rows[0].ToString(), 1);
break;
case Actions.ActDBValidation.eDBValidationType.UpdateDB:
var RS1 = clusterCB.Query <dynamic>(SQLCalculated);
break;
default:
throw new Exception("Action Not SUpported");
}
}
catch (Exception e)
{
Act.Error = "Failed to execute. Error :" + e.Message;
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {e.Message}", e);
}
if (!Db.KeepConnectionOpen)
{
Disconnect();
}
}
}
void UpdateOutValues(RowSet RS, Type t = null)
{
IEnumerable <Row> Rows = RS.GetRows();
CqlColumn[] Cols = RS.Columns;
int i = 1;
foreach (Row r in Rows)
{
int j = 1;
foreach (CqlColumn col in Cols)
{
if (col.Name == "[json]")
{
dynamic value1 = r.GetValue(typeof(object), col.Name);
Act.ParseJSONToOutputValues(value1, i);
}
else
{
dynamic value = r.GetValue(typeof(object), col.Name);
try
{
if (col.TypeCode.ToString() == "Udt" || col.TypeCode.ToString() == "Set" || col.TypeCode.ToString() == "Map" || col.TypeCode.ToString() == "List")
{
if (col.TypeCode.ToString() == "Udt")
{
RetriveUDT(col, r, t, i);
}
if (col.TypeCode.ToString() == "Set")
{
RetriveSet(col, r, t, i);
}
if (col.TypeCode.ToString() == "List")
{
RetriveList(col, r, t, i);
}
if (col.TypeCode.ToString() == "Map")
{
RetriveMap(col, r, t, i);
}
}
else
{
Act.AddOrUpdateReturnParamActualWithPath(col.Name, value.ToString(), i.ToString());
}
j++;
}
catch (Exception e)
{//to return colunms with null values
if (e.Message.ToString() == "Cannot perform runtime binding on a null reference")
{
Act.AddOrUpdateReturnParamActualWithPath(col.Name, "", i.ToString());
}
}
}
}
i++;
}
}
public override void PerformDBAction()
{
Connect();
string SQL = Act.SQL;
string keyspace = Act.Keyspace;
ValueExpression VE = new ValueExpression(Db.ProjEnvironment, Db.BusinessFlow, Db.DSList);
VE.Value = SQL;
string SQLCalculated = VE.ValueCalculated;
IQueryResult <dynamic> result = null;
string bucketName = "";
try
{
switch (Action)
{
case Actions.ActDBValidation.eDBValidationType.FreeSQL:
if (!String.IsNullOrEmpty(SQLCalculated))
{
bucketName = SQLCalculated.Substring(SQLCalculated.IndexOf(" from ") + 6);
bucketName = bucketName.Substring(0, bucketName.IndexOf(" ")).Replace("`", "");
}
ConnecttoBucket(bucketName);
result = clusterCB.Query <dynamic>(SQLCalculated);
for (int i = 0; i < result.Rows.Count; i++)
{
Act.ParseJSONToOutputValues(result.Rows[i].ToString(), i + 1);
}
break;
case Actions.ActDBValidation.eDBValidationType.RecordCount:
string SQLRecord = SQLCalculated;
ConnecttoBucket(SQLCalculated);
result = clusterCB.Query <dynamic>("Select Count(*) as RECORDCOUNT from `" + SQLCalculated + "`");
Act.ParseJSONToOutputValues(result.Rows[0].ToString(), 1);
break;
case Actions.ActDBValidation.eDBValidationType.UpdateDB:
if (!String.IsNullOrEmpty(SQLCalculated))
{
bucketName = SQLCalculated.Substring(SQLCalculated.IndexOf(" from ") + 6);
bucketName = bucketName.Substring(0, bucketName.IndexOf(" ")).Replace("`", "");
}
ConnecttoBucket(bucketName);
var RS1 = clusterCB.Query <dynamic>(SQLCalculated);
break;
default:
throw new Exception("Action Not SUpported");
}
}
catch (Exception e)
{
Act.Error = "Failed to execute. Error :" + e.Message;
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {e.Message}", e);
}
if (!Db.KeepConnectionOpen)
{
Disconnect();
}
}
public override void PerformDBAction()
{
ValueExpression VE = new ValueExpression(Db.ProjEnvironment, Db.BusinessFlow, Db.DSList);
VE.Value = Act.SQL;
string SQLCalculated = VE.ValueCalculated;
string collectionName = "";
if (Action == Actions.ActDBValidation.eDBValidationType.SimpleSQLOneValue)
{
collectionName = Act.Table;
}
else
{
collectionName = GetCollectionName(SQLCalculated);
}
var DB = mMongoClient.GetDatabase(DbName);
var collection = DB.GetCollection <BsonDocument>(collectionName);
try
{
switch (Action)
{
case Actions.ActDBValidation.eDBValidationType.FreeSQL:
if (SQLCalculated.Contains("insertMany"))
{
string queryParam = GetUpdateQueryParams(SQLCalculated).ToString();
Newtonsoft.Json.Linq.JArray jsonArray = Newtonsoft.Json.Linq.JArray.Parse(queryParam);
List <BsonDocument> documents = new List <BsonDocument>();
foreach (Newtonsoft.Json.Linq.JObject obj in jsonArray.Children <Newtonsoft.Json.Linq.JObject>())
{
BsonDocument document = BsonDocument.Parse(obj.ToString());
documents.Add(document);
}
collection.InsertMany(documents);
}
else if (SQLCalculated.Contains("insertOne") || SQLCalculated.Contains("insert"))
{
BsonDocument insertDocumnet = BsonDocument.Parse(GetUpdateQueryParams(SQLCalculated));
collection.InsertOne(insertDocumnet);
}
else
{
var result = collection.Find(GetQueryParamater(SQLCalculated, "find")).
Project(Builders <BsonDocument> .Projection.Exclude("_id").Exclude(GetQueryParamater(SQLCalculated, "projection"))).
Sort(BsonDocument.Parse(GetQueryParamater(SQLCalculated, "sort"))).
Limit(Convert.ToInt32(GetQueryParamater(SQLCalculated, "limit"))).
ToList();
var obj = result.ToJson();
Act.ParseJSONToOutputValues(obj.ToString(), 1);
}
break;
case Actions.ActDBValidation.eDBValidationType.RecordCount:
var count = collection.Count(new BsonDocument());
Act.AddOrUpdateReturnParamActual("Record Count", count.ToString());
break;
case Actions.ActDBValidation.eDBValidationType.UpdateDB:
//do commit
if (Act.CommitDB_Value == true)
{
var session = mMongoClient.StartSession();
session.StartTransaction();
UpdateCollection(SQLCalculated, collection);
session.CommitTransaction();
}
else
{
UpdateCollection(SQLCalculated, collection);
}
break;
case Actions.ActDBValidation.eDBValidationType.SimpleSQLOneValue:
string col = Act.Column;
string where = Act.Where;
string filter = "";
var isNumeric = double.TryParse(where, out double n);
//Simply matches on specific column type int
//For ex where contains any int value
if (isNumeric)
{
filter = "{" + col + ":" + where + "}";
}
else
{
//Equality matches on the whole embedded document require an exact match of the specified <value> document, including the field order
//For ex where contains value = {field1:_value1,field2:_value2,field3:"_value3",...}
if (where.Contains(","))
{
filter = "{" + col + ":" + where + "}";
}
//Simply matches on specific column
//For ex where contains any string value
else
{
filter = "{" + col + ":\"" + where + "\"}";
}
}
var resultSimpleSQLOne = collection.Find(filter).Project(Builders <BsonDocument> .Projection.Exclude("_id")).ToList().ToJson();
Act.ParseJSONToOutputValues(resultSimpleSQLOne.ToString(), 1);
break;
default:
Act.Error += "Operation Type " + Action + " is not yes supported for Mongo DB";
break;
}
}
catch (Exception e)
{
Act.Error = "Failed to execute. Error :" + e.Message;
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {e.Message}", e);
}
if (!Db.KeepConnectionOpen)
{
Disconnect();
}
}