/// <summary>
/// Save a SQL request into history for later use
/// </summary>
/// <param name="request"></param>
public ResponseModel SaveCommand(SQLRequest request)
{
if (request.Query == null)
{
return(new ResponseModel
{
Success = true
});
}
var last = GetLastCommand();
if (last != null && last.Query == request.Query)
{
return(new ResponseModel
{
Success = true
});
}
var history = new SqlCommandHistoryModel
{
Query = request.Query
};
var response = Insert(history);
return(response.SetMessage(response.Success
? T("SqlCommand_Message_CreateSuccessfully")
: T("SqlCommand_Message_CreateFailure")));
}
/// <summary>
/// Save a SQL request into history for later use
/// </summary>
/// <param name="request"></param>
public ResponseModel SaveCommand(SQLRequest request)
{
if (request.Query == null)
{
return(new ResponseModel
{
Success = true
});
}
var last = GetLastCommand();
if (last != null && last.Query == request.Query)
{
return(new ResponseModel
{
Success = true
});
}
var history = new SQLCommandHistoryModel
{
Query = request.Query
};
var response = Insert(history);
return(response.SetMessage(response.Success ?
_localizedResourceServices.T("AdminModule:::News:::Messages:::CreateSuccessfully:::Create news successfully.")
: _localizedResourceServices.T("AdminModule:::News:::Messages:::CreateFailure:::Insert news failed. Please try again later.")));
}
public List <object> RequestCheck(SQLRequest sql)
{
string[] chacked = sql.Request.Split(' ');
try {
if (Select(chacked))
{
return(Get(sql.Request));
}
else
{
using (IDbConnection db = new SqlConnection(connectionString))
{
db.Execute(sql.Request);
}
var s = GetTableName(chacked);
string z = "SELECT * FROM " + s;
return(GetData(z));
}
}
catch (Exception e)
{
return(new List <object>());
}
}
public ActionResult <List <object> > PostData([FromBody] SQLRequest SQLrequest)
{
if (SQLrequest == null)
{
return(BadRequest());
}
var response = repo.RequestCheck(SQLrequest);
return(Ok(response));
}
SQLResponse MakeQuery(SQLRequest req)
{
try
{
MySqlCommand cmd = new MySqlCommand(req.query, sqlConnection);
MySqlDataReader reader = cmd.ExecuteReader();
return(new SQLResponse(reader, req.callback));
}
catch (Exception e)
{
Debug.Log(e.Message);
return(null);
}
}
public ActionResult Index(SQLRequest request, bool exportToFile = false)
{
var executor = new SQLExecutor();
var result = executor.Execute(request);
if (exportToFile)
{
return(new FileContentResult(Encoding.UTF8.GetBytes(result.ToString()), "text/plain")
{
FileDownloadName = "QueryResult.txt"
});
}
return(View(result));
}
public async Task <InvokeResult <List <DataStreamResult> > > SqlQuery(string datastreamid, [FromBody] SQLRequest request)
{
if (request == null)
{
return(InvokeResult <List <DataStreamResult> > .FromError($"Empty SQL Request"));
}
if (String.IsNullOrEmpty(request.Query))
{
return(InvokeResult <List <DataStreamResult> > .FromError($"Empty Query SQL Request"));
}
var dataStream = await _dataStreamManager.LoadFullDataStreamConfigurationAsync(datastreamid, OrgEntityHeader, UserEntityHeader);
if (dataStream == null)
{
throw new RecordNotFoundException("DataStream", datastreamid);
}
var connectorResult = DataStreamServices.GetConnector(dataStream.Result.StreamType.Value, _adminlogger);
if (connectorResult == null)
{
return(InvokeResult <List <DataStreamResult> > .FromError($"Could not get data stream connector for: {dataStream.Result.StreamType.Value}"));
}
var connector = connectorResult.Result;
await connector.InitAsync(dataStream.Result);
return(await connector.ExecSQLAsync(request.Query, request.Parameters));
}
/// <summary>
/// Executing a SQL query against current DB
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public SQLResult Execute(SQLRequest request)
{
//save executing query into history
if (request.SaveToHistory)
{
_sqlCommandServices.SaveCommand(request);
}
//Prepare the result object based on basic information from request
var result = new SQLResult
{
ConnectionString = _connection.ConnectionString,
Query = request.Query,
ReadOnly = request.ReadOnly,
HtmlEncode = request.HtmlEncode
};
var returnData = new List <DataResult>();
var startTime = DateTime.Now.Ticks;
IDbTransaction transaction = null;
bool openConnection = false;
try
{
//open connection if needed
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
openConnection = true;
}
IDbCommand command = _connection.CreateCommand();
command.CommandText = request.Query;
command.CommandType = CommandType.Text;
//if executing in read only, we put all SQL statement into a DB transaction
//then roll back after then
if (request.ReadOnly)
{
transaction = _connection.BeginTransaction();
command.Transaction = transaction;
}
//Excuting and parse result
IDataReader reader = command.ExecuteReader();
if (reader != null)
{
do
{
DataResult dataResult = getResult(reader);
if (dataResult != null)
{
returnData.Add(dataResult);
}
} while (reader.NextResult());
result.RecordsAffected = reader.RecordsAffected;
}
}
catch (Exception ex)
{
result.Error = ex;
}
finally
{
//Roll back transaction if needed
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch { }
}
//Close connection if it was opened by us
if (openConnection)
{
try
{
_connection.Close();
}
catch { }
}
}
//Other properties for resutl object
result.Tables = GetTableNames();
long endTime = DateTime.Now.Ticks;
result.ReturnData = returnData;
result.ProcessTime = (long)new TimeSpan(endTime - startTime).TotalMilliseconds;
var defaultHistoryLength = _settingServices.GetSetting <int>(SettingNames.DefaultHistoryLength);
var defaultHistoryStart = _settingServices.GetSetting <int>(SettingNames.DefaultHistoryStart);
result.Histories = _sqlCommandServices.GetHistories(defaultHistoryStart, defaultHistoryLength);
return(result);
}
public IActionResult Post([FromBody] SQLRequest req)
{
var conn = new MySqlConnection("server=192.168.99.100;port=3306;user=root;password=root;database=employees");
var cmd = new MySqlCommand("", conn);
// var compiler = new SqlServerCompiler();
var compiler = new MySqlCompiler();
var q = new Query(req.From);
var paramCounter = 0;
foreach (var literal in req.Where)
{
var val = literal.Values[0];
q.Where(
literal.FieldName,
literal.Operator,
val.Value);
// var p = cmd.CreateParameter();
// p.Value = val.Value;
// p.ParameterName = "p" + paramCounter;
cmd.Parameters.AddWithValue("@p" + paramCounter, val.Value);
paramCounter++;
// .Value = val.Value
}
q.Limit(req.Limit);
q.Offset(req.Offset);
cmd.Parameters.AddWithValue("@p" + paramCounter, req.Limit);
paramCounter++;
if (req.Offset > 0)
{
cmd.Parameters.AddWithValue("@p" + paramCounter, req.Offset);
paramCounter++;
}
var sql = compiler.Compile(q).Sql;
Console.Write(sql);
cmd.CommandText = sql;
conn.Open();
var resp = cmd.ExecuteReader();
var responseList = new List <Dictionary <string, object> >();
while (resp.Read())
{
var rowDict = new Dictionary <string, object>();
for (var i = 0; i < resp.FieldCount; i++)
{
rowDict.Add(resp.GetName(i), resp[i]);
}
responseList.Add(rowDict);
}
conn.Close();
return(Ok(new SQLResponse()
{
ResultSet = responseList,
Next = new SQLRequest()
{
Select = req.Select,
From = req.From,
Where = req.Where,
OrderBy = req.OrderBy,
Limit = req.Limit,
Offset = req.Offset + req.Limit
},
Page = 1,
Pages = 10,
Count = 100
}));
}
